Planet CakePHP

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 "\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\n";
echo "\t\t\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\n";
echo "\t\n";
echo "\n";
return;
}

/**
* move to the next row in the .xls.
* must be used with closeRow() in pair.
*
*/
function openRow() {
echo "\t\t\t\n";
return;
}

/**
* end the row in the .xls.
* must be used with openRow() in pair.
*
*/
function closeRow() {
echo "\t\t\t\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 \n";
} else {
echo "\t\t\t\t".$Value."\n";
}
return;
}

/**
* Write the content of a cell in string format
*
* @param unknown_type $Value
*/
function writeString($Value) {
echo "\t\t\t\t".$Value."\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
*/

//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.
Posted in CakePHP Tagged: CakePHP