This is a helper for building multi-sheets Excel documents in CakePHP. The hepler was inspired from Yuen Ying’s blog post, but it’s extended to support multiple sheets.
The helper:
<?php
/**
* This xls helper is based on the one at
* http://ykyuen.wordpress.com/2009/10/04/cakephp-export-data-to-a-xls-file/
* actually creates an xml which is openable in Microsoft Excel.
* Extended by Nik Chankov
* Originally 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("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"");
}
/**
* Return the workbook
* @param string $content content of the wrkbook
* @return string
*/
function workbook($content) {
$ret = '<?xml version="1.0" encoding="UTF-8"?>'."\n";
$ret .= '<?mso-application progid="Excel.Sheet"?>'."\n";
$ret .= '<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">
'."\n";
$ret .= '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">'."\n";
$ret .= "\t".'<Created>'.date('Y').'-'.date('m').'-'.date('d').'T'.date('H').':'.date('i').':'.date('s').'Z</Created>'."\n";
$ret .= "\t".'<Company>Your company name</Company>'."\n";
$ret .= "\t".'<Version>1.00</Version>'."\n";
$ret .= '</DocumentProperties>'."\n";
$ret .= '<Styles>'."\n";
$ret .= '<Style ss:ID="Default" ss:Name="Normal">'."\n";
$ret .= '<Alignment ss:Vertical="Bottom"/>'."\n";
$ret .= '<Borders/>'."\n";
$ret .= '<Font/>'."\n";
$ret .= '<Interior/>'."\n";
$ret .= '<NumberFormat/>'."\n";
$ret .= '<Protection/>'."\n";
$ret .= '</Style>'."\n";
$ret .= '<Style ss:ID="s23">'."\n";
$ret .= '<Font x:Family="Swiss" ss:Bold="1"/>'."\n";
$ret .= '</Style>'."\n";
$ret .= '</Styles>'."\n";
$ret .= $content;
$ret .= '</Workbook>'."\n";
return $ret;
}
/**
* Add worksheet
* @param string $name name of the Worksheet
* @param string $content content of the worlsheet
* @return string
*/
function worksheet($name, $content) {
$ret = "\t".'<Worksheet ss:Name="'.$name.'">'."\n";
$ret .= "\t\t".'<Table>'."\n";
$ret .= $content;
$ret .= "\t\t".'</Table>'."\n";
$ret .= "\t".'</Worksheet>'."\n";
return $ret;
}
/**
* Create a row
* @param string $content content of the worlsheet
* @return string
*/
function row($content) {
$ret = "\t\t\t".'<Row>'."\n";
$ret .= $content;
$ret .= "\t\t\t".'</Row>'."\n";
return $ret;
}
/**
* Create a cell
* @param mixed $value value of the cell
* @param string $type type of the cell. For now it can be either String or Number
* @return string
*/
function cell($value, $type = 'String') {
if (is_null($value)) {
$ret = "\t\t\t\t".'<Cell><Data ss:Type="String"> </Data></Cell>'."\n";
} else {
$ret = "\t\t\t\t".'<Cell><Data ss:Type="'.$type.'">'.$value.'</Data></Cell>'."\n";
}
return $ret;
}
/**
* Create header cell with bold text
* The function is the same as this->cell, but bolded
* @param mixed $value value of the cell
* @param string $type type of the cell. For now it can be either String or Number
* @return string
*/
function hcell($value, $type = 'String') {
if (is_null($value)) {
$ret = "\t\t\t\t".'<Cell ss:StyleID="s23"><Data ss:Type="String"> </Data></Cell>'."\n";
} else {
$ret = "\t\t\t\t".'<Cell ss:StyleID="s23"><Data ss:Type="'.$type.'">'.$value.'</Data></Cell>'."\n";
}
return $ret;
}
}
?>
/**
* This xls helper is based on the one at
* http://ykyuen.wordpress.com/2009/10/04/cakephp-export-data-to-a-xls-file/
* actually creates an xml which is openable in Microsoft Excel.
* Extended by Nik Chankov
* Originally 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("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"");
}
/**
* Return the workbook
* @param string $content content of the wrkbook
* @return string
*/
function workbook($content) {
$ret = '<?xml version="1.0" encoding="UTF-8"?>'."\n";
$ret .= '<?mso-application progid="Excel.Sheet"?>'."\n";
$ret .= '<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">
'."\n";
$ret .= '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">'."\n";
$ret .= "\t".'<Created>'.date('Y').'-'.date('m').'-'.date('d').'T'.date('H').':'.date('i').':'.date('s').'Z</Created>'."\n";
$ret .= "\t".'<Company>Your company name</Company>'."\n";
$ret .= "\t".'<Version>1.00</Version>'."\n";
$ret .= '</DocumentProperties>'."\n";
$ret .= '<Styles>'."\n";
$ret .= '<Style ss:ID="Default" ss:Name="Normal">'."\n";
$ret .= '<Alignment ss:Vertical="Bottom"/>'."\n";
$ret .= '<Borders/>'."\n";
$ret .= '<Font/>'."\n";
$ret .= '<Interior/>'."\n";
$ret .= '<NumberFormat/>'."\n";
$ret .= '<Protection/>'."\n";
$ret .= '</Style>'."\n";
$ret .= '<Style ss:ID="s23">'."\n";
$ret .= '<Font x:Family="Swiss" ss:Bold="1"/>'."\n";
$ret .= '</Style>'."\n";
$ret .= '</Styles>'."\n";
$ret .= $content;
$ret .= '</Workbook>'."\n";
return $ret;
}
/**
* Add worksheet
* @param string $name name of the Worksheet
* @param string $content content of the worlsheet
* @return string
*/
function worksheet($name, $content) {
$ret = "\t".'<Worksheet ss:Name="'.$name.'">'."\n";
$ret .= "\t\t".'<Table>'."\n";
$ret .= $content;
$ret .= "\t\t".'</Table>'."\n";
$ret .= "\t".'</Worksheet>'."\n";
return $ret;
}
/**
* Create a row
* @param string $content content of the worlsheet
* @return string
*/
function row($content) {
$ret = "\t\t\t".'<Row>'."\n";
$ret .= $content;
$ret .= "\t\t\t".'</Row>'."\n";
return $ret;
}
/**
* Create a cell
* @param mixed $value value of the cell
* @param string $type type of the cell. For now it can be either String or Number
* @return string
*/
function cell($value, $type = 'String') {
if (is_null($value)) {
$ret = "\t\t\t\t".'<Cell><Data ss:Type="String"> </Data></Cell>'."\n";
} else {
$ret = "\t\t\t\t".'<Cell><Data ss:Type="'.$type.'">'.$value.'</Data></Cell>'."\n";
}
return $ret;
}
/**
* Create header cell with bold text
* The function is the same as this->cell, but bolded
* @param mixed $value value of the cell
* @param string $type type of the cell. For now it can be either String or Number
* @return string
*/
function hcell($value, $type = 'String') {
if (is_null($value)) {
$ret = "\t\t\t\t".'<Cell ss:StyleID="s23"><Data ss:Type="String"> </Data></Cell>'."\n";
} else {
$ret = "\t\t\t\t".'<Cell ss:StyleID="s23"><Data ss:Type="'.$type.'">'.$value.'</Data></Cell>'."\n";
}
return $ret;
}
}
?>
How to use it.
The helper instead of printing the result on the screen can be used also to store the result in a file. The basic usage can be following:
$worksheets = '';
$rows = '';
foreach($data as $key=>$row){
$cells = '';
foreach($row as $cell){
//creates a cell
$cells .= $xls->Cell($cell);
}
//creates a row
$rows .= $xls->row($cells);
}
//Creates stylesheet named First Sheet
$worksheets .= $xls->worksheet('First Sheet', $rows);
//Creates stylesheet named Second Sheet
$worksheets .= $xls->worksheet('Second Sheet', $rows);
// If you print the output to the screen set the proper headers
$xls->setHeader('myExcel.xls');
echo $xls->workbook($worksheets);
//Alternatively you can store the content into file
file_put_contents('path-to-storage/myExcel.xls', $worksheets);
$rows = '';
foreach($data as $key=>$row){
$cells = '';
foreach($row as $cell){
//creates a cell
$cells .= $xls->Cell($cell);
}
//creates a row
$rows .= $xls->row($cells);
}
//Creates stylesheet named First Sheet
$worksheets .= $xls->worksheet('First Sheet', $rows);
//Creates stylesheet named Second Sheet
$worksheets .= $xls->worksheet('Second Sheet', $rows);
// If you print the output to the screen set the proper headers
$xls->setHeader('myExcel.xls');
echo $xls->workbook($worksheets);
//Alternatively you can store the content into file
file_put_contents('path-to-storage/myExcel.xls', $worksheets);
There is only one problem: The file is treated properly from Excel, but it’s not working in Open Office. Open Office cannot recognize the Excel XML file format.
Open Office can indeed handle Excel XML (At least version 3.1, though I think 3.0 did as well). The trick is to change the file extension to .xml — open the xml file in OO and it will import it correctly. I’m not sure if it’ll work with the helper code, but I regularly open .xlsx files.
Steven, I’ve just checked but unfortunately this file couldn’t be opened with OpenOffice. 🙁
I agree, i couldn’t open this file with open office either…
Pingback: CakePHP : signets remarquables du 23/11/2009 au 25/11/2009 | Cherry on the...
You have shared useful information for creating multiple Excel sheets in cake PHP. Guys will take it as an advantage.
You have done a great jobs regarding multiple sheet in cake PHP, it will be helpful for us
thank you so much..
i think it’s very useful for my toko online.
Pingback: Tutorial and resources Dealing with xls files on CakePHP | blogfreakz.com
I’m impressed, I have to say. Really rarely do I come across a blog that’s both informative and entertaining, and let me tell you, you’ve hit the nail on the head. Your blog is outstanding; the issue is something that not enough people are talking intelligently about. I am really happy that I stumbled across this in my search for something relating to it.
Pingback: Tutorials and resources Dealing with xls files on CakePHP | codeManiac - Snippets, Templates, API and the best developer content