Generate Excel spreadsheets from your database
This article is also available in the following languages:
With CakePHP you can easily create views from database models. At times, however, you need to output your database model as a spreadsheet file. In this article I'll show how you can use PEAR's Spreadsheet_Excel_Writer class to generate an Excel spreadsheet with CakePHP.
For this article I'll assume that you need a basic output: one or more sheets with each one row of field names (header) and multiple rows of data. I have adapted PEAR's class to let it function without the other PEAR libraries. You can download my version at http://webdesign.janenanneriet.nl/excel_writer.zip. You also need PEAR's OLE class, which I have also adapted (download at http://webdesign.janenanneriet.nl/ole.zip).
That should result in a nice Excel file. Of course you can use many more options (like including graphics and hyperlinks, adding borders, colors, etc.). For more information you can check the documentation of PEAR's Spreadsheet_Excel_Writer class (http://pear.php.net/package/Spreadsheet_Excel_Writer/docs).
Preparation
Vendor classes
Extract the two zip files to your vendors directory. You might want to change the error handling in these files if you want your user to generate Excel files on-the-fly. I have namely substituted the PEAR Error class with PHP's native trigger_error() function. You can change all calls to trigger_error() to use Cake's Error class.Helper
In order to generate the Excel file, we'll use a Helper class. This idea is also used in the article on PDF generation (http://bakery.cakephp.org/articles/view/218). The helper facilitates in the creation of several default cell formatting parameters (text, date, datetime, numbers and money). It sets the default properties for text style and layout (font, size, bold, etc.) and facilitates in converting dates and times to the Excel format. The helper file should be called excel.php.Helper Class:
<?php
set_time_limit(10); // Set maximum execution time to 10 seconds.
error_reporting(E_ALL ^E_NOTICE); // Notice errors break the Excel file format.
define('TEXT_FORMAT', 0);
define('NUM_FORMAT', 1);
define('MONEY_FORMAT', 2);
define('DATE_FORMAT', 3);
define('TIME_FORMAT', 4);
define('DATETIME_FORMAT', 5);
vendor('excel_writer/ExcelWriter');
vendor('ole/OleRoot');
vendor('ole/OleFile');
class excelHelper extends ExcelWriter {
var $workbook;
var $worksheets = array();
var $formats = array();
var $font = 'Arial';
var $size = 10;
var $align = 'left';
var $valign = 'vcenter';
var $bold = 0;
var $italic = 0;
/**
* Creates the necessary objects and a temporary Excel file. Sets the
* directory for temporary file creation and sets the version to
* Excel 97 (support UTF-8).
*
* @param string $filename Name of the downloadable file
*/
function excelHelper($filename = 'data.xls') {
$this->workbook =& new ExcelWriter();
$this->workbook->setTempDir(TMP."cache");
$this->workbook->setVersion(8); // Set workbook to Excel 97 (for UTF-8 support)
}
/**
* Returns an array with parameters that can be used to create an XF
* formatting array. Default values are overridden when specified in
* the parameter array.
*
* @param array $params Array with parameters to define formatting.
* Keys refer to the style properties.
* @return string Formatting array.
*/
function _getFormatArray($params = NULL) {
$temp = array('font' => $this->font,
'size' => $this->size,
'bold' => $this->bold,
'italic' => $this->italic,
'align' => $this->align,
'valign' => $this->valign);
if(isset($params)) {
foreach($params as $key => $value) {
$temp[$key] = $value;
}
}
return $temp;
}
/**
* Returns the Julian date of the Windows Excel epoch (1 Jan 1900).
*
* @return integer Julian date
*/
function _GetExcelEpoch() {
return GregorianToJD(1,1,1900); // Windows Excel epoch
}
/**
* Initializes the default formats that can be used. After changing
* the default properties this method has to be called to initialize
* the formatting arrays.
*/
function initFormats() {
// initialize default formats:
$text = $this->_getFormatArray();
$text['textwrap'] = 1;
$text['numformat'] = '@';
$this->formats[TEXT_FORMAT] =& $this->workbook->addformat($text);
$num = $this->_getFormatArray();
$num['align'] = 'right';
$this->formats[NUM_FORMAT] =& $this->workbook->addformat($num);
$num['numformat'] = '[$EUR-413] #,##0.00;[$EUR-413] #,##0.00-';
$this->formats[MONEY_FORMAT] =& $this->workbook->addformat($num);
$num['numformat'] = 'dd-mm-yyyy';
$this->formats[DATE_FORMAT] =& $this->workbook->addformat($num);
$num['numformat'] = 'hh:mm:ss';
$this->formats[TIME_FORMAT] =& $this->workbook->addformat($num);
$num['numformat'] = 'dd-mm-yyyy hh:mm:ss';
$this->formats[DATETIME_FORMAT] =& $this->workbook->addformat($num);
}
/**
* Creates a worksheet in the Excel file, sets its encoding to UTF-8
* and returns a reference to the worksheet.
*
* @param string $name Name of the worksheet.
* @return object Worksheet object.
*/
function &AddWorksheet($name = NULL) {
$this->worksheets[] =& $this->workbook->addWorksheet($name);
$this->worksheets[count($this->worksheets) - 1]->setInputEncoding('UTF-8');
return $this->worksheets[count($this->worksheets) - 1];
}
/**
* Adds a formatting array to the Excel workbook and returns the index
* of the array.
*
* @param array $params Array with parameters to define formatting.
* Keys refer to the style properties.
* @return integer Index of the formatting array.
*/
function AddFormat($params) {
$this->formats[] =& $this->workbook->addformat($this->_getFormatArray($params));
return (count($this->formats) - 1);
}
/**
* Adds a color to the color palette of the workbook.
*
* @param integer $index Index on the color palette. Existing colors
* will automatically be overridden.
* @param mixed $color Can be an array of Red, Green and Blue values
* or a hexadecimal representation of the color.
* @return integer The palette index for the custom color.
*/
function setColor($index, $color) {
if(!is_array($color)) {
$temp = str_split($color, 2);
$color[] = hexdec($temp[0]);
$color[] = hexdec($temp[1]);
$color[] = hexdec($temp[2]);
}
return $this->workbook->setCustomColor($index, $color[0], $color[1], $color[2]);
}
/**
* Converts a MySQL Datetime field value to Excel datetime values.
*
* @param string $datetime MySQL datetime (dd-mm-yyyy hh:mm:ss)
* @param float Excel datetime value.
*/
function MysqlDatetimeToExcel($datetime) {
$tmp = explode(" ", $datetime);
$date = explode("-", $tmp[0]);
if(isset($tmp[1])) $time = explode(":", $tmp[1]);
$date1 = GregorianToJD($date[1],$date[2],$date[0]);
$epoch = $this->_GetExcelEpoch();
$frac = (($time[0] * 60 * 60) + ($time[1] * 60) + $time[2])/(24*60*60);
return ($date1 - $epoch + 2 + $frac);
}
/**
* Converts a UNIX timestamp value to Excel datetime values.
*
* @param int $timestamp UNIX timestamp
* @param float Excel datetime value.
*/
function TimestampToExcel($timestamp) {
return $this->MysqlDatetimeToExcel(date("d-m-Y H:i:s", $timestamp));
}
/**
* Writes a $token (string, number, array, link, formula etc.) to the
* specified row and column on the specified worksheet.
*
* @param object $worksheet Reference to the worksheet
* @param integer $row Row number (starting at zero; A1 is 0,0)
* @param integer $col Column number (starting at zero)
* @param mixed $token Data to write to the worksheet
* @param integer $format Index of the formatting array to use
* @return boolean False when an error occurs, otherwise True.
*/
function write(&$worksheet, $row, $col, $token, $format = 0) {
return $worksheet->write($row, $col, $token, $this->formats[$format]);
}
/**
* Sends the temporary Excel file as a string to the render engine
* and clears all objects.
*/
function OutputFile() {
$this->workbook->Close();
echo file_get_contents($this->workbook->filename, "rb");
}
}
?>
Layout
You have to create a layout file (excel.thtml) that supports the output of non-HTML files.View Template:
<?php
(empty($type)) ? $type = 'applications' : $type = $type;
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=slf2007-".$type.".xls");
header("Pragma: no-cache");
header("Expires: 0");
?>
<?php echo $content_for_layout ?>
Application logic
Controller action
In your controller you have to retreive your model data and set it in a variable (e.g. $data), for use in your view. Then you should render the view with the layout 'excel'. Don't forget to list your helper in the $helpers array of your controller.Controller Class:
<?php
...
var $helpers = array('Html', 'Form', 'Time', 'Excel');
...
$data = array();
$data['sheet1'] = $this->Model->findAll(NULL, NULL, 'id ASC', NULL, 1, 2);
...
$this->set('data', $data);
$this->render('action', 'excel');
?>
View
Finally in the view you can use your helper and format your data. You can always use straight library calls by using $excel->worksheet->... or $excel->workbook->...View Template:
<?php
/* Define fieldnames: */
$fieldnames= array('fieldname1', 'fieldname2', etc.);
/* Set default font styles: */
$excel->font = 'Tahoma';
$excel->size = 8;
$excel->initFormats(); // initialize default formats
/* Add style for heading: */
$heading_format = $excel->AddFormat(array('bold' => 1, 'align' => 'center'));
/* Change TIME_FORMAT: */
$excel->formats[TIME_FORMAT]->setNumFormat('hh:mm'); // direct library call
/* Create Excel sheets: */
$sheet1 =& $excel->AddWorksheet('Sheet Name');
/* Define layout of worksheet for applications: */
$sheet1->setColumn(0, 0, 5);
$sheet1->setColumn(7, 10, 8);
$sheet1->setColumn(0, 28, 18);
$sheet1->freezePanes(array(1, 1)); // Freeze sheet at 1st row and 1st column
/* Write headings: */
$excel->write($sheet1, 0, 0, $fieldnames, $heading_format);
/* Write data for applications: */
foreach($data['sheet1'] as $key => $value) {
$i = 0;
foreach($data['sheet1'][$key]['Model'] as $fieldname => $fieldvalue) {
if($fieldname =='birthdate') {
$excel->write($sheet1, $key+1, $i, $excel->MysqlDatetimeToExcel($fieldvalue), DATE_FORMAT);
}
elseif($fieldname == 'created') {
$excel->write($sheet1, $key+1, $i, $excel->MysqlDatetimeToExcel($fieldvalue), DATETIME_FORMAT);
}
elseif($fieldname == 'finances' || $fieldname == 'expenses'){
$excel->write($sheet1, $key+1, $i, $fieldvalue, MONEY_FORMAT);
}
else {
$excel->write($sheet1, $key+1, $i, $fieldvalue);
}
$i++;
}
}
/* Output temporary file to the browser: */
$excel->OutputFile();
?>
That should result in a nice Excel file. Of course you can use many more options (like including graphics and hyperlinks, adding borders, colors, etc.). For more information you can check the documentation of PEAR's Spreadsheet_Excel_Writer class (http://pear.php.net/package/Spreadsheet_Excel_Writer/docs).
Warning!
On *NIX systems you should take care that you don't leave any spaces after the closing PHP-tags. This will result in the "headers already sent" error.
Andrea B.
Excel Spreadsheets
well, even though this tutorial did not work for me, i finally figured it is Mysql Targetted. My App is powered by a Postgres DB. I guess I finally give up. TCPDF too just is not portable enough when it comes to pulling from database. I guess i just gotta figure out a way.
Good job though. i do hope this work will be improved upon towork seamlessly with Postgres and other dbms.
check it out
http://www.dnamique.com/cakephp-export-data-to-excel-the-easy-way/
I have problem with this helper.
In output I get sth like this:
��ࡱ�����������������;���� �������������������������������������������������������������������������������������������������������������
I found out what it could be:
1) problem with utf encoding
2) problem with cake (my version 1.2)
3) html code was sent to output
In your controller function turn off debug code if it isn't already since the extra stuff will break the Excel format:
function summary() {
Configure::write('debug', 0);
in helpers/excel.php replace the vendor function with App::import and change the case of the path:
App::import('Vendor', 'excel_writer/excelwriter');App::import('Vendor', 'ole/oleroot');
App::import('Vendor', 'ole/olefile');
Add the $helpers array to the var declarations:
var $workbook;var $worksheets = array();
var $formats = array();
var $helpers = array();
Finally, there was an extra space at the end of the file that was causing the headers already sent error so I deleted that. Now I'm able to get Excel output - thank you so much for posting this article.
When I go to /[APP]/[MODEL]/generateExcel/ then I get
Fatal error: Call to undefined function vendor() in C:\[root]\[app]\views\helpers\excel.php on line 12
I am new at cakephp and I think i am just messing something. Can any of you give some light here?
In pear SpreadSheet/Excel/Writer class parse format parameter arrya at Workbook->addFormat($param) by using Spreadsheet_Excel_Writer_Format constructer with it's methodname. Code is that:
Format.php:298
// Set properties passed to Spreadsheet_Excel_Writer_Workbook::addFormat()
foreach ($properties as $property => $value)
{
if (method_exists($this, 'set'.ucwords($property))) {
$method_name = 'set'.ucwords($property);
$this->$method_name($value);
}
}
So,excelHelper class should use 'fontfamily' but not 'font'. :
function _getFormatArray($params = NULL) {
$temp = array('fontfamily' => $this->font,
'size' => $this->size,
'bold' => $this->bold,
'italic' => $this->italic,
'align' => $this->align,
'valign' => $this->valign);
http://webdesign.janenanneriet.nl/ole.zip
Both links are dead, shows a white page. Nothing to download
Please fix.
Both links are dead, shows a white page. Nothing to download
Please fix.
I found links in this thread:
http://n2.nabble.com/from-database-to-excel-(Missing-files-in-the-bakery)-td1098216.html
Direct link:
http://dev.eimermusic.com/cake_excel_writer/
I use this excel-helper and it's really a great thing.
To get higher readability on large documents, I want to have one row gray and the next in white alternating.
My problem is, how can I change background-color of my column ??
the "addFormat"-method does not accept a parameter called BgColor like descripted in the pear-doc.
Is my version of ExcelWriter just old (it's from the link in the comments) or am I just to stupid to find the way to do this.
Greetz
Piecko
I just installed a full PEAR and used the installer to get the packages OLE and Spreadsheet_Excel_Writer.
After that I had to delete most of the helper methods, because they didn't work with the current version of Spreadsheet_Excel_Writer( write(), initFormats()).
I included a "require_once = 'Spreadsheet/Excel/Writer.php'".
After some work I can change the Backgroundcolor of my cells by calling
$format_red =& $excel->workbook->addFormat();
$format_red->setBgColor('red');
$format_red->setPattern(18);
The important thing is the pattern, just setting the color will set the pattern to 1, which most time is not the color you want. Like in Pear-Docs to read, there are 18 patterns for one color and I suggest to use this example from the doc to find the right one:
<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet('testing colors and patterns');
$worksheet->setRow(1, 30);
$worksheet->setRow(2, 30);
$worksheet->setRow(3, 30);
// valid patterns are 0 to 18
for ($i = 0; $i <= 18; $i++)
{
// green in different patterns
$another_format1 =& $workbook->addFormat();
$another_format1->setBgColor(13);
$another_format1->setPattern($i);
$worksheet->write(1, $i, "pattern $i", $another_format1);
// red in different patterns
$another_format2 =& $workbook->addFormat();
$another_format2->setFgColor('red');
$another_format2->setPattern($i);
$worksheet->write(2, $i, "pattern $i", $another_format2);
// mixed red and green according to pattern
$another_format3 =& $workbook->addFormat();
$another_format3->setBgColor('green');
$another_format3->setFgColor('red');
$another_format3->setPattern($i);
$worksheet->write(3, $i, "pattern $i", $another_format3);
}
$workbook->send('setPattern.xls');
$workbook->close();
?>
There are only 8 colors you can type by name, easiest thing is to add a custom color
$workbook->setCustomColor(12,192,192,192);First param is the index you use for setBgColor()-method, the next three params are the rgb-color values. Index can be set from 8 to 56.
I hope I can help someone with this little tutorial.
They're both broken.
Really appreciate,
thanks
Update: Google before you think!
found them at http://n2.nabble.com/from-database-to-excel-(Missing-files-in-the-bakery)-td1098216.html
I'm trying to get it to work in Cake 1.2, I had set Config::Debug to 0. So Excel displays an spreadsheet with everything in it, but following error: "Data error: Maybe data got lost"
When I set debug to 2, I'm getting following debug message:
Notice (8): Undefined property: ExcelWriter::$filename in [APP/views/helpers/excel.php, line 216]
and following error message:
Warning (2): file_get_contents(): Filename cannot be empty in [APP/views/helpers/excel.php, line 216]
The problem seems to have something to do with the temp file, in the original ExcelWriter class saves the filename, but the ExcelHelper (which extends the ExcelWriter) seems to miss that (if I understodd it right):
function ExcelWriter($filename = '') {and ExcelHelper extends ExcelWriter$this->_filename = $filename;
$this->ExcelWorkbook($filename);
}
function ExcelHelper($filename = 'data.xls') {
$this->workbook =& new ExcelWriter();
$this->workbook->setTempDir(TMP."cache");
$this->workbook->setVersion(8); // Set workbook to Excel 97 (for UTF-8 support)
}
Any ideas how that should be solved or what's wrong, the code is too confusing for me, and I've no idea about excel code ....
I've used that source:
thx
Gaius
I would like to just use the default Pear library, but am concerned that other websites won't have patched files. Therefore I think that having the modified files originally given in the tutorial would be great. That way, one can be assured that the excel portion would work regardless of the Pear library provided.
Just what I've been looking for, but I need to _import_ excel (if it's possible at all) :(. Any luck finding the files linked to in this article?
Desperately need this for a project I'm working on
Thanks
Would appreciate, thank you.
Tyler
Firstly thanks a lot for this lovely tutorial .... found it very helpful.
I am not getting any errors. The .xls file is being generated but it contains garbage values. Some symbols are appearing rather that text. The only text that is appearing is the field headers. Even they are totally out of order. What could be the reason for this ? It will be great if you could help!
Hi,
I was with this problem for a few days, but today I found the solution, in my case the reason was the encoding in the function 'writeStringBIFF8' of the file ExcelWorksheet.php(vendors directory).
I have this problem what's the solution? what's correctly code for the encoding in the function 'writeStringBIFF8' of the file ExcelWorksheet.php(vendors directory)?
I don't understand why my output code has this character:
��ࡱ�����������������;���� ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������� ���
��� ��� ���
Please fix.
Thanks
i've got a problem to generate my excel , when i click to generate a new widows is open with the binary of the file Excel in this windows.
If in function :
function OutputFile() {
$this->workbook->Close();
echo file_get_contents($this->workbook->filename, "rb");
}
if i create a file who is the content of file_get_contents($this->workbook->filename, "rb") , the excel file is generate Without problems...
An idea about he problem ? (in core.php debugs are '0' of course ).
ps : in controller what is the reference of action in :
$this->render('action', 'excel');
1) It is important that debug is on 0 in core.php
2) You have to change in excel.php this line :
class excelHelper extends ExcelWriter
by this one :
class excelHelper extends Helper
Hope this can help :)
Controller Class:
<?phpvar $helpers = array('Html', 'Form' , 'Ajax', 'Javascript', 'Time', 'Excel');
?>
On my controller. What seems to be the problem here?
and other weird errors, what could i do to make it work? i followed carefully the guidelines. i am running cakephp 1.2.x (beta)
This is by no means a critisism of this script which looks great, it would just save someone some time if they don't have mb_string installed.
I know I could probably hack around it but it all takes time :(
The script will also work (even if u don't have mb_string function) when you use iconv function. Any way, at least one of those two functions must be installed.
You are right, sorry forgot to mention iconv as well.
The host does not have iconv installed either, so still no go :(
View Template:
<?php echo $content_for_layout ?>
View Template:
<?php echo $content_for_layout ?>
<?php
(empty($type)) ? $type = 'applications' : $type = $type;
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=slf2007-".$type.".xls");
header("Pragma: no-cache");
header("Expires: 0");
?>
��à ¡±á;þÿ þÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
!"#þÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ »
ÌABä=¼%r8X"1Èÿ�Arial1Èÿ�Arial1Èÿ�Arial1Èÿ�Arial1Èÿ�Arial1È �Arial1 ÿ�Tahoma1 ÿ¼Tahoma ¤@ -¥([$EUR-413] #,##0.00;[$EUR-413] #,##0.00- ¦
dd-mm-yyyy
§hh:mm ¨dd-mm-yyyy hh:mm:ssà õÿ À à õÿ À à õÿ À à õÿ À à õÿ À à õÿ À à õÿ À à õÿ À à õÿ À à õÿ À à õÿ À à õÿ À à õÿ À à õÿ À à õÿ À à À à À à ¤ À à À à ¥ À à ¦ À à § À à ¨ À à À “€ÿ’â8ÿÿÿÿÿÿÿÿÿÿÿÿ€€€€€€€€€ÀÀÀ€€€™™ÿ™3fÿÿÌÌÿÿffÿ€€fÌÌÌÿ€ÿÿÿÿÿÿ€€€€€ÿÌÿÌÿÿÌÿÌÿÿ™™Ìÿÿ™ÌÌ™ÿÿÌ™3fÿ3ÌÌ™ÌÿÌÿ™ÿfff™–––3f3™f333™3™3f33™333…<Informacion Lanchaüpidrut direccionnombresdad0000-00-00 00:00:00
»
ÌAU} ¸}
¸} ¸*+‚��ƒ„&è?'è?(ð?)ð?¡"dXXà ?à ? ý
:(
any idea ?
Comments are closed for articles over a year old