CakePHP – Export data to a .xls file

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.

Advertisements

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

  1. 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??

    Like

    1. 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?

      Like

      1. 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.

        Like

      2. 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!

        Like

      3. 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.

        Like

      4. 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? :/

        Like

      5. 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.

        Like

      6. 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 😦

        Like

      7. 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! 🙂

        Like

    1. 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.

      Like

      1. 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.

        Like

      2. 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.

        =)

        Like

  2. thanks!!!!

    i insert the following line between lines 34 and 35 of the xls.php:

    echo ” xmlns:o=\”urn:schemas-microsoft-com:office:office\”\n”;

    and i changed the “.xls” entension (line 25) into “.xml”

    it works perfecty. i just have to tell “open with” and then “excel” or “open office”

    best wishes!!!
    stebu

    Like

    1. this solutions doens’t work for me =( still can’t open at Open Office. and in MSOffice it prompts a message telling me the file has been save with different bla bla bla but i click yes and everything is perfect.

      watdo now ?

      Like

      1. Hi Fabio,

        can you open an xml formatted .xls which has the following content?

        <?xml version="1.0" encoding="UTF-8"?>
        <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
        	xmlns:o="urn:schemas-microsoft-com:office:office"
        	xmlns:x="urn:schemas-microsoft-com:office:excel"
        	xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
        	xmlns:html="http://www.w3.org/TR/REC-html40">
        	<Worksheet ss:Name="Sheet 1">
        		<Table>
        			<Row>
        				<Cell><Data ss:Type="String">Column 1</Data></Cell>
        				<Cell><Data ss:Type="String">Column 2</Data></Cell>
        				<Cell><Data ss:Type="String">Column 3</Data></Cell>
        			</Row>
        			<Row>
        				<Cell><Data ss:Type="Number">123</Data></Cell>
        				<Cell><Data ss:Type="String">ABC</Data></Cell>
        				<Cell><Data ss:Type="String">DEF</Data></Cell>
        			</Row>
        			<Row>
        				<Cell><Data ss:Type="Number">456</Data></Cell>
        				<Cell><Data ss:Type="String">GHI</Data></Cell>
        				<Cell><Data ss:Type="String">JKL</Data></Cell>
        			</Row>
        			<Row>
        				<Cell><Data ss:Type="Number">789</Data></Cell>
        				<Cell><Data ss:Type="String">MNO</Data></Cell>
        				<Cell><Data ss:Type="String">PQR</Data></Cell>
        			</Row>
        		</Table>
        	</Worksheet>
        </Workbook>
        

        Kit

        Like

  3. ykyuen :
    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.
    =)

    I just need a plugin that exports the information from POST, DATE, USER, EMAIL

    And i own my wp

    Like

      1. I don’t want to import..

        I just need the information from the USER, DATE, EMAIL and POST in to a file… Excel or PDF don’t matter.

        Because my client needs this info from WP, so he can print them

        Nothing else…

        Like

      2. oic. you can extract the data directly from the wordpress database as u own the WP.

        or if you only need the recent data, then u can try parsing the RSS feed from you site and convert it to what event u want. but i think u may need to customized the RSS feed content such that it contains what u want.

        Like

      3. do u have the database access? if yes, then u can just connect to it and use the select SQL to retrieve the data u need.

        Like

  4. Is anyone else experiencing problems with the current version of the Chrome browser (5.0.375.99)? For some reason, I get incomplete downloads of the files I’m generating. When using various versions of IE and Firefox, the same defect is not observed. Something in the http headers perhaps?

    Like

      1. IE8, it works really well on firefox and chrome – and normally i wouldn’t mind it not working in IE8. Just the people using my project mainly use IE8…

        Like

  5. Hey, thanks for the links. I found the problem. On line 25 it should say:
    header(“Content-Disposition: attachment; filename=\””.$filename.”.xml\””);

    its the attachment rather than inline that fixes it for IE8 🙂

    Like

  6. hi, the code is just running flawless. Thanks for the post. Just one thing, Is it possible to put a background color or any other style in the writeString function.
    Thank you in advance

    Like

      1. Hi kit,
        Thanks for the link. It served my purpose.
        Your code is running fantastically with these styles.
        Thanks again.

        Like

  7. It’s good, except if i write the type of data is date, I can write date by function writeString, but in excel file, it not in date type format

    Like

    1. I think you can try to add a function say writeDate with ss:Type=”DateTime”.

      According to the MSDN
      For date/time cells, when the user does a Web query to an XML Spreadsheet document and specifies no HTML formatting, date formatting (whatever is specified in the XML Spreadsheet document being queried) will still be applied to cells with ss:Type=”DateTime”.

      Reference: XML in Excel and the Spreadsheet Component

      Like

  8. Hi all,

    I’ve got this code running and it works well, except I’m stuck on how to go about adding formatting to the outputted spreadsheet. For example, I want to add bold headers to each column, and no matter how I try to alter the XML code, the text just shows up normal. Can anyone offer tips or pointers on this?

    I basically would like to know how to alter this line: echo “\t\t\t\t”.$Value.”\n”;

    in order to make the output bold.

    Thanks!

    Like

    1. Add the following function in xls.php

      function writeBoldString($Value) {
        echo "\t\t\t\t<Cell><Data ss:Type=\"String\" ss:Bold=\"1\">".$Value."</Data></Cell>\n";
        return;
      }
      

       

      And the write the column header as follow

      ...
      //1st row for columns name
      $xls->openRow();
      $xls->writeBoldString('NumberField1');
      $xls->writeBoldString('StringField2');
      $xls->writeBoldString('StringField3');
      $xls->writeBoldString('NumberField4');
      $xls->closeRow();
      ...
      

       

      Is it ok?

      Like

      1. Hi ykyuen,

        Thanks for the response! Unfortunately, that didn’t work. When I tried opening up the Excel file it generated, it said “Problems came up in the following areas during load: Table” and the file couldn’t be opened up.

        Thanks again for your help.

        Like

      2. Try this one.

        function writeBoldString($Value) {
          echo "\t\t\t\t<Cell><Data ss:StyleID=\"bold\" ss:Type=\"String\">".$Value."</Data></Cell>\n";
          return;
        }
        

         

        Then in the header

          	    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";
        	        echo "          <Styles>\n";
        	        echo "                   <Style ss:ID=\"bold\">\n";
        	        echo "                            <Font ss:Bold=\"1\"/>\n";
        	        echo "                    </Style>\n";
        	        echo "          </Styles>\n";
        	        return;
        	    }
        

         

        The output file should have similar content as follow

        <?xml version="1.0"?>
        <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
         xmlns:o="urn:schemas-microsoft-com:office:office"
         xmlns:x="urn:schemas-microsoft-com:office:excel"
         xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
         xmlns:html="http://www.w3.org/TR/REC-html40">
         <Styles>
          <Style ss:ID="s62">
           <Font ss:Bold="1"/>
          </Style>
         </Styles>
         <Worksheet ss:Name="Sheet1">
          <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="1" x:FullColumns="1"
           x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="16.5">
           <Column ss:AutoFitWidth="0" ss:Width="211.5"/>
           <Row>
            <Cell ss:StyleID="s62"><Data ss:Type="String">First Name</Data></Cell>
            <Cell><Data ss:Type="String">Last Name</Data></Cell>
            <Cell><Data ss:Type="String">Phone Number</Data></Cell>
           </Row>
          </Table>
         </Worksheet>
        </Workbook>
        

         

        hope this time works =P

        Like

  9. Thanks again for your reply! That didn’t quite work either, but I was able to tweak it enough this time to get it working. The following is what worked for me:

    function writeBoldString($Value) {
      echo "\t\t\t\t".$Value."\n";
      return;
    }
    

    Like

    1. I am a bit confused, dun you wanna bold the first row of text? how come you can bold it just by removing the <Cell> and <Data> in the write string function?

      anyway, good to know that you have solve the problem, i edited your comment for highlighting the code. =)

      Like

    1. if you want to highlight the syntax, wrap the code with the following tag
      [sourcecode language=php]
      <your code here>
      [/sourcecode]

      Like

  10. This is the code that worked for me:

    function writeBoldString($Value) {
      			echo "\t\t\t\t<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">".$Value."</Data></Cell>\n";
      			return;
    		}
    

    Sorry, in my earlier post some of the key XML got stripped out of the post because it wasn’t formatted as sourcecode.

    Thanks again for all your help!

    Like

  11. Hey. It works great.
    But there is a small problem.
    I am using an export / import module in my website, and unfortunately because the export is only an xml it cannot be imported with your excel_reader class.
    So in fact i really need to generate a real excel. Or do you recommend to read it as xml and not as xls?

    TNX

    Like

    1. Although the exported file has the .xls extension, actually it is just an xml which could be read by MS Office Excel.

      For import and export functions, i suggest you could just define your own xml schema for the exported data instead of using the approach in this article. This is becoz there is no need for ppl to read the exported file in MS Office Excel.

      Moreover, there should be much more existing php libraries for you to parse the xml. So the implementation should be much easier.

      Just a suggestion, hope it could help. =D

      Kit

      Like

  12. Hi, i have 1 problem.. In a table i got in 1 column a long string more than 25 length, and this xls.php, cant write this string in excel outputted file. 😦
    What should i do?

    Like

  13. Thank you for this good work.
    You can simplify excel/openoffice problem using new Office extension, which is .xlsx (xml without macro), that can be open natively both in Excel and OpenOffice
    Cheers

    Like

  14. Hi,
    Thanks for your helper.
    Will you please let me know how can i send this generated .xlsx file as an attachment to an email[using SwiftMailer or Email component], directly when call to the controller’s method (like export in your above example))..?

    Like

  15. Hi, my name Andy..
    I am using your helper in my project and i am getting an error Notice (8): Undefined variable: xls

    will u please sort it out why i am getting this error as i have followed ur all the steps..
    Please reply..

    Like

      1. hii,

        i am also getting the same error as Andy on line no 8 of view file.
        So to solve it i have created the object of xlsHelper class in the same view file by writing this code.
        $xls= new xlsHelper();

        But now the error occurred while opening that xlsx file. it saying that file format or file extension is not valid and verify that file has not been corrupted.

        i m new to cakephp so kindly help me taking that into consideration..
        plss…

        Like

      2. Thanks for your rply..

        i tried the way you said but still its not opening with excel. It gives error like found unreadable content.

        if i am saving my file with .xls format then it showing all the xml formatted data in the excel sheet with xml tags also. So now how to store only one cell data in to a single cell of excel sheet??

        Like

      3. No.. still its not working..
        i have given extension .xlsx in my coding so if i change it to .pdf then how it can become PDF file?? i don’t think that this is the case in my coding. i m wondering why this code is not working

        but now my work of exporting data to excel is done as i have created one helper class in cakephp using php coding.. so now its working fine.. 😀

        thanks for your reply

        Like

  16. Hello
    Im having some trouble working with this, i got to the part of opening the file, but all the information is displayed on one line. The code im using is below, i dont see anything wrong with it….so im asking for a little help:

    Thx in advance.

    create('Equipo');
    $conexion=mysql_connect("localhost","root","") or die("Problemas en la conexion");
    mysql_select_db("test",$conexion) or die("Problemas en la selección de la base de datos");
    $query = sprintf("SELECT nombre FROM storages WHERE id_stg = "
      .mysql_real_escape_string($this->data['Equipo']['id_almacen']));
    $result = mysql_query($query);
    $nombre = mysql_fetch_assoc($result);
    $id_almacen=$this->data['Equipo']['id_almacen'];
    $query = sprintf('SELECT *,(cantidad+en_reparacion) AS total
      FROM     equipos
      LEFT JOIN   storages
      ON     equipos.id_almacen = storages.id_stg 
      WHERE id_almacen 
      = "%s"',mysql_real_escape_string($id_almacen));
    $query.=" ORDER BY ubicacion";		
    //----------------------
    //echo $query; die();
    //----------------------
    $result = mysql_query($query);
    $xls->setHeader('Auditoria Almacen:'.$nombre['nombre']);
    $xls->addXmlHeader();
    $xls->setWorkSheetName($nombre['nombre']);
    //1st row for columns name
    $xls->openRow();
    $xls->writeString('Equipo/Central');
    $xls->writeString('Part Number');
    $xls->writeString('Descripcion');
    $xls->writeString('Di');
    $xls->writeString('REP');
    $xls->writeString('T');
    $xls->writeString('Ubicacion');
    $xls->writeString('De');
    $xls->writeString('NOTA');
    $xls->closeRow();
    //rows for data
    while ($row = mysql_fetch_assoc($result)) {
      $xls->openRow();
      $xls->writeString($row['equipo_central']);
      $xls->writeString($row['part_number']);
      $xls->writeString($row['descripcion']);
      $xls->writeNumber($row['cantidad']);
      $xls->closeRow();
    }   
    $xls->addXmlFooter();
    exit();
    

    Like

    1. Can you open it with text editor? you should be able to get an xml format file. is it a valid xml?

      and which program is used for opening the file?

      it would be great if you could send me the created file or post it here.

      Like

  17. Hi

    I don’t want it to prompt for download the excel. I want to save the file at a predefined destination directory.

    Please help me out how to implement the saving the file functionality.

    Thanks in advance.

    Like

  18. Hello,
    This is really helpful and easy to use…great 🙂
    but i am trying to export two table data into one excel file…So, this will work ?
    Thanks

    Like

    1. Update the xls.php

      ...
      /**
       * 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;
      }
      
      /**
       * close the worksheet.
       * it has to be added otherwise the xml format is incomplete.
       *
       */
      function closeWorkSheet() {
        echo "\t\t</Table>\n";
        echo "\t</Worksheet>\n";
        return;
      }
       
      /**
       * add the footer to the end of xml.
       * it has to be added otherwise the xml format is incomplete.
       *
       */
      function addXmlFooter() {
        echo "</Workbook>\n";
        return;
      }
      ...
      

       

      So when you write the file in view. try sth like

      <?php
        /**
         * Export all member records in .xls format
         * with the help of the xlsHelper
         */
      
        //input the export file name
        $xls->setHeader('Model_'.date('Y_m_d'));
         
        $xls->addXmlHeader();
      
        //1st worksheet
        $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;
        
        //close this worksheet
        $xls->closeWorkSheet();
      
        //2nd worksheet
        $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;
        
        //close this worksheet
        $xls->closeWorkSheet();
      
        $xls->addXmlFooter();
        exit();
      ?> 
      

      Like

  19. I have problem.
    Error: Call to a member function setHeader() on a non-object
    File: D:\xampp\htdocs\bravelets\app\View\Orders\orderexport.ctp
    Line: 8

    Like

  20. Hello ; I have the same problem :

    Error: Call to a member function setHeader() on a non-object
    File: C:\xampp\htdocs\scer\View\Clientes\export.ctp
    Line: 8

    class ClientesController extends AppController {
    
    var $helpers = array('xls');
    
     
     function export() {       
        $data = $this->Cliente->find('all');
        $this->set('clientes', $data);
    }
    
    ...
    

    My helper class is called xlsHelper.php

    thank You in Advance!!

    Like

    1. O, i think i missed a line of code.

      could u try

      <?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'));
          
          // Continue ...
      

      Does it solve the problem?

      Like

      1. Hi thank You !!!,
        The problem was solved, I use the following line.

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

        the new problem is that my file can not be opened by Excel , any ideas ???

        <?xml version="1.0" encoding="UTF-8"?>
        <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                  xmlns:x="urn:schemas-microsoft-com:office:excel"
                  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
                  xmlns:html="http://www.w3.org/TR/REC-html40">
            <Worksheet ss:Name="Cliente">
                <Table>
                    <Row>
                        <Cell><Data ss:Type="String">nombre</Data></Cell>
                        <Cell><Data ss:Type="String">primerApellido</Data></Cell>
                        <Cell><Data ss:Type="String">segundoApellido</Data></Cell>
                        <Cell><Data ss:Type="String">telefonoPrincipal</Data></Cell>
                    </Row>
                    <Row>
                        <Cell><Data ss:Type="Number">Manuel</Data></Cell>
                        <Cell><Data ss:Type="String">Gonzales</Data></Cell>
                        <Cell><Data ss:Type="String">Zeledon</Data></Cell>
                        <Cell><Data ss:Type="Number">401980835</Data></Cell>
                    </Row>
                    <Row>
                        <Cell><Data ss:Type="Number">Carlos</Data></Cell>
                        <Cell><Data ss:Type="String">Sanchez</Data></Cell>
                        <Cell><Data ss:Type="String">Gonzales</Data></Cell>
                        <Cell><Data ss:Type="Number">232342342</Data></Cell>
                    </Row>
                </Table>
            </Worksheet>
        </Workbook>
        

        Like

    1. Ya, i can open that file in LibreOffice. Sorry that i don’t have an excel to test it. How about you rename the file to .xml and try to import it in excel?

      Like

      1. Thanks for your help and time, the documents open correctly in LibreOffice, Microsoft Office did not work in my case.

        Like

      1. Hey!! , I found the problem: Type Number and value String

        <Data <cell> Ss:Type="Number"> Carlos </ Data> </ Cell>
        
        

        Like

  21. Hello,

    I try this helper, but the file is opend in Excel and Libre office with XML tag. How can i deal with that?

    My helper

    <?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.".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:o=\"urn:schemas-microsoft-com:office:office\"\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;
        }
      }
    ?>
    

    My 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('ModelBis_'.date('Y_m_d'));
       
      $xls->addXmlHeader();
      $xls->setWorkSheetName('ModelBis');
    
      $decodFields = json_decode($fields);
      
      //Configure Colonnes Header
      //1st row for columns name
      $xls->openRow();  
      foreach ($decodFields as $db) {
      	$display = $availableFields[$db];
      	$xls->writeString($display['name']);
      }
      $xls->closeRow();
    
      foreach ($datas as $data):
      	$xls->openRow();
    	foreach ($decodFields as $db) :
    		$db = split('\.', $db);
    	  
    		if (strncmp($db[1], "fg_", 3) == 0){
    			if(isset($data[$db[0]][$db[1]]) ){
    				// si le champ public ou valid vaut 1
    				if( isset($data[$db[0]][$db[1]]) && ( $data[$db[0]][$db[1]] == 3)){
    					$xls->writeString('oui');
    				}
    			}else {
    					$xls->writeString('oui');
    				//$xls->writeString($data[$db[0]][$db[1]]);
    			}
    		}else if (strncmp($db[1], "dt_", 3) == 0) {
    			if ($data[$db[0]][$db[1]] == ''){
    					$xls->writeString('oui');
    				//$xls->writeString('');
    			} else{
    					$xls->writeString('oui');
    				//$xls->writeString(date('d/m/Y', strtotime(($data[$db[0]][$db[1]]))));
    			}
    		}else{
    					$xls->writeString('oui');
    			//$xls->writeString($data[$db[0]][$db[1]]);
    		}
    	endforeach;
    	$xls->closeRow();	  
      endforeach;
      
      $xls->addXmlFooter();
      exit();
    ?> 
    

    Generated data :

    <?xml version="1.0" encoding="UTF-8"?>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    		xmlns:x="urn:schemas-microsoft-com:office:excel"
    		xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    		xmlns:html="http://www.w3.org/TR/REC-html40">
    	<Worksheet ss:Name="ModelBis">
    		<Table>
    			<Row>
    				<Cell><Data ss:Type="String">Nom Patient</Data></Cell>
    				<Cell><Data ss:Type="String">Prénom Patient</Data></Cell>
    				<Cell><Data ss:Type="String">Code Patient</Data></Cell>
    			</Row>
    			<Row>
    				<Cell><Data ss:Type="String">oui</Data></Cell>
    				<Cell><Data ss:Type="String">oui</Data></Cell>
    				<Cell><Data ss:Type="String">oui</Data></Cell>
    			</Row>
    			<Row>
    				<Cell><Data ss:Type="String">oui</Data></Cell>
    				<Cell><Data ss:Type="String">oui</Data></Cell>
    				<Cell><Data ss:Type="String">oui</Data></Cell>
    			</Row>
    			<Row>
    				<Cell><Data ss:Type="String">oui</Data></Cell>
    				<Cell><Data ss:Type="String">oui</Data></Cell>
    				<Cell><Data ss:Type="String">oui</Data></Cell>
    			</Row>
    		</Table>
    	</Worksheet>
    </Workbook>
    

    Excel 2007 and Libre Office opend file with xml tags

    Thank you

    Like

  22. Thank you for your response.

    I’m using Libre Office 3.5 on Ubuntu 12.04 and cake Php 1.3.
    Please how do you open the file, because when i try to open i always prompt a windows to select encoding, get the HTML tags

    Thank you again for your response.

    Best

    Like

    1. i think maybe you could try to find upgrade your LibreOffice and try again.

      And there is not proper way to insert image in comment, you can try to host the image on other webserver and link in using the img html tag.

      Like

  23. I upgrade my LibreOffice (On ubuntu 12.04) to version 4.2.0.4, but there is no change. It can’t open xxx.xls or xxx.xlsx files.

    On windows, the xxx.xls file is opend by Microssof Office 2007, but it is not the case for xxx.xlsx file.

    I Would try to install LibreOffice 4.2 on Windows if it can change something.

    Thank you again for your availability.

    Best

    Like

  24. Hi
    I need to create multiple sheets in the excel.In sheet1 i want to show clients information and second sheet i want to show all contact details of every clients.Can you please help?

    Thanx in advance 🙂

    Like

    1. You can update the XlsHelper and try to construct the xml like the following.

      <?xml version="1.0"?>
      <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
       xmlns:o="urn:schemas-microsoft-com:office:office"
       xmlns:x="urn:schemas-microsoft-com:office:excel"
       xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
       xmlns:html="http://www.w3.org/TR/REC-html40">
       <Styles>
        <Style ss:ID="s62">
         <Font ss:Bold="1"/>
        </Style>
       </Styles>
       <Worksheet ss:Name="Sheet1">
        <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="1" x:FullColumns="1"
         x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="16.5">
         <Column ss:AutoFitWidth="0" ss:Width="211.5"/>
         <Row>
          <Cell ss:StyleID="s62"><Data ss:Type="String">First Name</Data></Cell>
          <Cell><Data ss:Type="String">Last Name</Data></Cell>
          <Cell><Data ss:Type="String">Phone Number</Data></Cell>
         </Row>
        </Table>
       </Worksheet>
       <Worksheet ss:Name="Sheet2">
        <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="1" x:FullColumns="1"
         x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="16.5">
         <Column ss:AutoFitWidth="0" ss:Width="211.5"/>
         <Row>
          <Cell ss:StyleID="s62"><Data ss:Type="String">First Name</Data></Cell>
          <Cell><Data ss:Type="String">Last Name</Data></Cell>
          <Cell><Data ss:Type="String">Phone Number</Data></Cell>
         </Row>
        </Table>
       </Worksheet>
      </Workbook>
      

      Like

  25. Hi, I used your code in my CakePHP project. But I am getting error in xlsx file after download.

    Warning (4096): Argument 1 passed to Helper::__construct() must be an instance of View, none given, called in /var/www/html/gss/app/View/Assignments/dd_result.ctp on line 26 and defined [CORE/Cake/View/Helper.php, line 173]Code Context
    *@paramarray$settingsConfigurationsettingsforthehelper.
    */
    publicfunction__construct(View$View,$settings=array()){
    Helper::__construct() - CORE/Cake/View/Helper.php, line 173
    include - APP/View/Assignments/dd_result.ctp, line 26
    View::_evaluate() - CORE/Cake/View/View.php, line 948
    View::_render() - CORE/Cake/View/View.php, line 910
    View::render() - CORE/Cake/View/View.php, line 471
    Controller::render() - CORE/Cake/Controller/Controller.php, line 954
    Dispatcher::_invoke() - CORE/Cake/Routing/Dispatcher.php, line 198
    Dispatcher::dispatch() - CORE/Cake/Routing/Dispatcher.php, line 165
    [main] - APP/webroot/index.php, line 108
    
    Notice (8): Undefined variable: View [CORE/Cake/View/Helper.php, line 174]Code Context
    */
    publicfunction__construct(View$View,$settings=array()){
    $this->_View=$View;
    $settings = array()
    Helper::__construct() - CORE/Cake/View/Helper.php, line 174
    include - APP/View/Assignments/dd_result.ctp, line 26
    View::_evaluate() - CORE/Cake/View/View.php, line 948
    View::_render() - CORE/Cake/View/View.php, line 910
    View::render() - CORE/Cake/View/View.php, line 471
    Controller::render() - CORE/Cake/Controller/Controller.php, line 954
    Dispatcher::_invoke() - CORE/Cake/Routing/Dispatcher.php, line 198
    Dispatcher::dispatch() - CORE/Cake/Routing/Dispatcher.php, line 165
    [main] - APP/webroot/index.php, line 108
    
    Notice (8): Undefined variable: View [CORE/Cake/View/Helper.php, line 175]Code Context
    publicfunction__construct(View$View,$settings=array()){
    $this->_View=$View;
    $this->request=$View->request;
    $settings = array()
    Helper::__construct() - CORE/Cake/View/Helper.php, line 175
    include - APP/View/Assignments/dd_result.ctp, line 26
    View::_evaluate() - CORE/Cake/View/View.php, line 948
    View::_render() - CORE/Cake/View/View.php, line 910
    View::render() - CORE/Cake/View/View.php, line 471
    Controller::render() - CORE/Cake/Controller/Controller.php, line 954
    Dispatcher::_invoke() - CORE/Cake/Routing/Dispatcher.php, line 198
    Dispatcher::dispatch() - CORE/Cake/Routing/Dispatcher.php, line 165
    [main] - APP/webroot/index.php, line 108
    
    Notice (8): Trying to get property of non-object [CORE/Cake/View/Helper.php, line 175]Code Context
    publicfunction__construct(View$View,$settings=array()){
    $this->_View=$View;
    $this->request=$View->request;
    $settings = array()
    Helper::__construct() - CORE/Cake/View/Helper.php, line 175
    include - APP/View/Assignments/dd_result.ctp, line 26
    View::_evaluate() - CORE/Cake/View/View.php, line 948
    View::_render() - CORE/Cake/View/View.php, line 910
    View::render() - CORE/Cake/View/View.php, line 471
    Controller::render() - CORE/Cake/Controller/Controller.php, line 954
    Dispatcher::_invoke() - CORE/Cake/Routing/Dispatcher.php, line 198
    Dispatcher::dispatch() - CORE/Cake/Routing/Dispatcher.php, line 165
    [main] - APP/webroot/index.php, line 108
    

    Like

  26. Hi
    Thanks for your help.
    I successfully implemented the export feature in my application.
    But now suddenly it started throwing error as “General Error,General input/output error”.
    I am using LibraOffice.
    Thanks in advance 🙂

    Saurabh

    Like

  27. Hi ykyuen
    I am unable to open the exported file in excel 2007,though it works fine in LibraOffice and excel 2003.
    Please suggest.

    Thank You
    Saurabh

    Like

  28. Hi I used your helper for exporting data, great job :). I just have a small problem when I try to open it with excel 2010 i get the worning

    “The file your trying to open, .xls, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the files. Do you want to open the file now?”

    and if I click yes, the file is opened and the data is displayed correctly .
    Any help to make this warning disappear?
    Thanks in advance

    Like

      1. yes, I opened it with a text editor and it looks ok. I’m not an expert in this but maybe it has to do with the fact that it’s xml and not binary (maybe we need to use pack function)?

        Like

      2. Try this header

        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");;
        header("Content-Disposition: attachment;filename=test.xls ");
        header("Content-Transfer-Encoding: binary ");
        

         

        Reference: Error when opening an Excel file: “The file you are trying to open, ‘.xls.xls’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file…”

        Like

      3. I tried this and it still shows the same warning.
        I added some extra tags that have to do with worksheet properties, styles … etc. After that if i change the file extension to xml, it opens in excel normally with no warnings. Any advice?

        Like

  29. Unfortunately, I don’t have excel 2007 to try this.
    And editing the registry doesn’t seem to be a solution. I need the users to be able to open the file without getting warnings/errors. I was looking for something I could do on the server side that would solve this.

    Like

    1. You have to handle it in php before writing the data to the $xls.

      sth like

      // Get the datetime
      $datetime = strtotime($model['Model']['date_field']);
      
      // Get the date
      $date_only = date("Y-m-d", $datetime);
      
      // Get the time
      $time_only = date("H:i:s", $datetime);
      
      // Save to the $xls
      $xls->writeString($date_only);
      $xls->writeString($time_only);
      

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s