CakePHP – Export data to a .xls file

October 4, 2009 ykyuen 128 Comments

We have talked about how to read data from an excel file in previous articles.
CakePHP – php-excel-reader
CakePHP – Read data from an excel file into an array

Now i would like to create a .xls in CakePHP. i found the following article in CakePHP Bakery talking about the xls helper. i tried to follow the article but i dun know y it doesn’t work.
Excel xls helper

So i modified the helper class as follow.
xls.php

<?php
  /**
   * This xls helper is based on the one at 
   * http://bakery.cakephp.org/articles/view/excel-xls-helper
   * 
   * The difference compared with the original one is this helper
   * actually creates an xml which is openable in Microsoft Excel.
   * 
   * Written by Yuen Ying Kit @ ykyuen.wordpress.com
   *
   */ 
  class XlsHelper extends AppHelper {
    /**
     * set the header of the http response.
     *
     * @param unknown_type $filename
     */
    function setHeader($filename) {
      header("Pragma: public");
      header("Expires: 0");
      header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
      header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
      header("Content-Type: application/force-download");
      header("Content-Type: application/download");;
      //header("Content-Disposition: inline; filename=\"".$filename.".xls\"");
      // Name the file to .xlsx to solve the excel/openoffice file opening problem
      header("Content-Disposition: inline; filename=\"".$filename.".xlsx\"");
    }
      
    /**
     * add the xml header for the .xls file.
     *
     */
    function addXmlHeader() {
      echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
      echo "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\n";
      echo "          xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\n";
      echo "          xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\n";
      echo "          xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n";
      return;
    }
      
    /**
     * add the worksheet name for the .xls.
     * it has to be added otherwise the xml format is incomplete.
     *
     * @param unknown_type $workSheetName
     */
    function setWorkSheetName($workSheetName) {
      echo "\t<Worksheet ss:Name=\"".$workSheetName."\">\n";
      echo "\t\t<Table>\n";
      return;
    }
      
    /**
     * add the footer to the end of xml.
     * it has to be added otherwise the xml format is incomplete.
     *
     */
    function addXmlFooter() {
      echo "\t\t</Table>\n";
      echo "\t</Worksheet>\n";
      echo "</Workbook>\n";
      return;
    }
      
    /**
     * move to the next row in the .xls.
     * must be used with closeRow() in pair.
     *
     */
    function openRow() {
      echo "\t\t\t<Row>\n";
      return;
    }
      
    /**
     * end the row in the .xls.
     * must be used with openRow() in pair.
     *
     */
    function closeRow() {
      echo "\t\t\t</Row>\n";
      return;
    }
    
    /**
     * Write the content of a cell in number format
     *
     * @param unknown_type $Value
     */
    function writeNumber($Value) {
      if (is_null($Value)) {
        echo "\t\t\t\t<Cell><Data ss:Type=\"String\"> </Data></Cell>\n";
      } else {
        echo "\t\t\t\t<Cell><Data ss:Type=\"Number\">".$Value."</Data></Cell>\n";
      }
        return;
    }
      
    /**
     * Write the content of a cell in string format
     *
     * @param unknown_type $Value
     */
    function writeString($Value) {
      echo "\t\t\t\t<Cell><Data ss:Type=\"String\">".$Value."</Data></Cell>\n";
      return;
    }
  }
?>

Follow the steps below to implement the export feature.

1. Put the above xls.php in ~/app/views/helper

2. Include the helper class in the controller

var $helpers = array('xls');

3. Add a function in the controller for the view which export the xls

function export() {        
  $data = $this->Model->find('all');
  $this->set('models', $data);
}

4. Create the corresponding view

<?php
  /**
   * Export all member records in .xls format
   * with the help of the xlsHelper
   */

  //declare the xls helper
  $xls= new xlsHelper();

  //input the export file name
  $xls->setHeader('Model_'.date('Y_m_d'));

  $xls->addXmlHeader();
  $xls->setWorkSheetName('Model');
  
  //1st row for columns name
  $xls->openRow();
  $xls->writeString('NumberField1');
  $xls->writeString('StringField2');
  $xls->writeString('StringField3');
  $xls->writeString('NumberField4');
  $xls->closeRow();
  
  //rows for data
  foreach ($models as $model):
    $xls->openRow();
    $xls->writeNumber($model['Model']['number_field_1']);
    $xls->writeString($model['Model']['string_field_2']);
    $xls->writeString($model['Model']['string_field_3']);
    $xls->writeNumber($model['Model']['number_field_4']);
    $xls->closeRow();
  endforeach;
 
  $xls->addXmlFooter();
  exit();
?>

5. Open the view url in browser and then u will be prompted to download a .xls

Done =)

* Update @ 2010-05-27: Thanks Stebu. the OpenOffice problem is solved. Please refer to the post CakePHP – Open an xml Formatted .xls in OpenOffice

* Update @ 2011-10-14: Named the file to .xlsx instead of .xls in order to fixed the excel/openoffice problem.Thanks Erwan.

128 thoughts on “CakePHP – Export data to a .xls file”

Nik Chankov says:

October 18, 2009 at 00:58

Thanks, I just needed the XLS creation in my project so I will try your approach 🙂

LikeLike

Reply
1. ykyuen says:
  
  October 18, 2009 at 01:14
  
  You are welcome. Hope this help =)
  
  LikeLike
  
  Reply
Pieter says:

December 26, 2009 at 06:50

Hi,

Thanks, I just implemented it and created an excel sheet. That works fine. However the excel sheet can’t be opened in MS excel, it tells me that the sheet has errors. Which version of MS excel do I need??

LikeLike

Reply
1. ykyuen says:
  
  December 26, 2009 at 11:50
  
  i can open it using ms office 2003. actually the exported .xls is an xml file rather than a traditional .xls. so what version of ms office are u using? can u try to open it using office 2003?
  
  LikeLike
  
  Reply
  1. Pieter says:
    
    December 27, 2009 at 06:55
    
    Thanks again, I am using ms office 2003, I find out that something goes wrong with the UTF 8 coding, special characters in my database are wrong interpreted by excel.
    
    LikeLike
  2. Sebastian says:
    
    January 21, 2010 at 16:58
    
    Hello,
    fist, this helper is great! 🙂
    But i have the same problem as Pieter. I also can’t open all generated .xls files, some work, some not.
    I changed the charset of MySQL to utf8 but the problem is still the same.
    Someone has a solution for this?
    
    Thanks a lot!
    
    LikeLike
  3. ykyuen says:
    
    January 21, 2010 at 17:13
    
    those xls generated by this helper cannot be opened by open office.
    
    so can u open it in ms office?
    
    actually what this helper does is to generate an xml which can be parsed by ms office. so if that is encoding problem, i guess u can verify it by opening the xls using text editor and check if it has decode problem.
    
    LikeLike
  4. Sebastian says:
    
    January 21, 2010 at 17:43
    
    I don’t want to use Open Office. I can’t open the file with MS-Office. So i have to set MySQL character set also to UTF8?
    And btw, i also use Apache web-server.
    These character-sets make my crazy…
    Can someone tell me what i have to do?
    
    LikeLike
  5. ykyuen says:
    
    January 21, 2010 at 17:58
    
    i suggest u exporting a .xls with hardcoded data so u dun need to read data from the database. this can exclude the database encoding factor.
    
    if that works. that means u have to change the database encoding.
    
    LikeLike
  6. Sebastian says:
    
    January 21, 2010 at 19:20
    
    Well, i can’t verify this in that way because some outputs work and some not. but i dont know why.
    furthermore, i changed the database to utf-8 and i added this etry : AddDefaultCharset UTF-8
    in httpd.conf from apache.
    
    dont work 😦
    
    LikeLike
  7. Sebastian says:
    
    January 21, 2010 at 21:26
    
    Ohh sorry,
    it’s solved, i didn’t saw a simple fault of mine. I wanted to write a string (description in my table) with the writeNumber() function…
    But thanks for your quick answers!!! 🙂
    and again, your xls-helper is great! 🙂
    
    LikeLike
  8. ykyuen says:
    
    January 21, 2010 at 23:34
    
    you are welcome.
    hope this help =)
    
    LikeLike
Francis says:

January 14, 2010 at 11:43

Hi, I cant open it with openoffice.. is there a fix for this?

LikeLike

Reply
1. ykyuen says:
  
  January 14, 2010 at 13:10
  
  What open office version are u using?
  i find a post about this problem
  http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=8759
  
  Rename to export .xls to .xml and try to open it again.
  
  i tried it using openoffice 3.0 in opensuse, but this does not work.
  can u?
  
  LikeLike
  
  Reply
Berlin says:

May 27, 2010 at 12:21

Guys do you know how to Export my post to a xls file from wordpress..

Tahnk you

LikeLike

Reply
1. ykyuen says:
  
  May 27, 2010 at 14:06
  
  what is the purpose to export the your wordpress posts to an .xls? u want to back up your wordpress site?
  
  if you are a wordpress.com user, u can export the posts to .xml in the admin page. but it seems that the images of the posts are excluded.
  
  LikeLike
  
  Reply
  1. Berlin says:
    
    May 27, 2010 at 23:49
    
    No i don’t want to Backup my WP…
    
    I just need to have the POST, USER, DATE, EMAIL so i can have this info.
    
    To keep track of my clients and then print it to a normal paper.
    
    LikeLike
  2. ykyuen says:
    
    May 28, 2010 at 00:14
    
    then i think u can try the export function provided by wordpress.com.
    
    or u own ur own wordpress blog? then u can try to find some export plugin.
    
    =)
    
    LikeLike