Try this:
----- xls helper (views/helpers/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\"");
}
/**
* 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;
}
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 "\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\">".addslashes($Value)."</Data></Cell>\n";
}
return;
}
/**
* Write the content of a cell in string format
*
* @param unknown_type $Value
*/
function writeString($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=\"String\">".str_replace("\r\n","
",utf8_encode($Value))."</Data></Cell>\n";
}
return;
}
function writeColumn($header) {
$i = 2;
foreach($header as $h) {
echo "\t\t<Column ss:Index=\"".$i."\" ss:AutoFitWidth=\"0\"
ss:Width=\"110\"/> ";
$i++;
}
}
}
?>
----- xls helper -----
----- view (views/controller-name/action-name.php -----
<?php
$blacklist = array();
/**
* Export all records in .xls format
* with the help of the xlsHelper
*/
//input the export file name
$xls->setHeader('File_'.date('Y_m_d'));
$xls->addXmlHeader();
$xls->setWorkSheetName('Worksheet');
//1st row for columns name
$xls->writeColumn($header);
$xls->openRow();
foreach($header as $h) {
if (!in_array($h,$blacklist)) {
$xls->writeString($h);
}
}
$xls->closeRow();
//rows for data
foreach ($data as $row):
$xls->openRow();
foreach($row["Model"] as $field => $value) {
if (!in_array($field,$blacklist)) {
$xls->writeString($value);
}
}
$xls->closeRow();
endforeach;
$xls->closeWorkSheet();
$xls->addXmlFooter();
exit();
?>
----- view -----
In your controller you add the helper:
var $helpers = array('Html', 'Form','Javascript','xls');
And the action to export:
function export_to_excel() {
$this->layout = null;
$data = $this->Model->find("all");
foreach($data[0]["Model"] as $field => $value) {
$header[] = $field;
}
$this->set('data', $data);
$this->set('header',$header);
}
And there you go...
A functional export to native excel (xml 2003+) helper in cake php...
hope this help you...
--
Renato Freire
[email protected]
On Fri, May 13, 2011 at 3:01 AM, abhimanyu bv <[email protected]> wrote:
> @bujanga,
>
> following the steps in the link you provided, output is rendered as table
> not as excel cells. I want to render data as excel.
>
> --
> Our newest site for the community: CakePHP Video Tutorials
> http://tv.cakephp.org
> Check out the new CakePHP Questions site http://ask.cakephp.org and help
> others with their CakePHP related questions.
>
>
> To unsubscribe from this group, send email to
> [email protected] For more options, visit this group
> at http://groups.google.com/group/cake-php
>
--
Our newest site for the community: CakePHP Video Tutorials
http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others
with their CakePHP related questions.
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php
