Background
Generating spreadsheets in PHP is a powerful tool. Natively, PHP can generate .csv spreadsheets with little fuss by sending a header to the browser to generate csv output. This article is not about .csv files, but rather generating Excel Spreadsheets with PHP.
Benefits of using actual Excel format (.xls) files:
- Standard spreadsheet format for most offices
- Rich formatting can be saved in the file
- Less confusion for casual computer users
PEAR Setup
To generate Excel files we will be using the PEAR package Spreadsheet_Excel_Writer. PEAR stands for the PHP Extension and Application Repository and contains useful tools and functions that you can use in your PHP scripts. PEAR is closely tied to PHP development, and PHP can be configured to use PEAR easily. For installation instructions see: http://pear.php.net/manual/en/installation.php.
If you are like me and you have your site running on a shared host where you do not have the luxury to install and change whatever you want, you can still use PEAR in your site. There are instructions in the above installation page for installing it on a shared host.
For testing purposes, I have bundled the necessary Spreadsheet_Excel_Writer and OLE package PEAR files into a zip file so you can extract it to your web root and create Excel spreadsheets easily without the full installation of PEAR. I changed all include() paths so that they will work with a different directory structure than the default PEAR installation. Please note that this is not an official PEAR code release and should only be used for testing purposes. Download the testing package here.
Creating the Excel file with PHP
Using this package is relatively easy, once you understand the flow of how it works. The package provides many, many formatting functions. See the list of formatting releated functions in the package’s official documentation: http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.php.
Below is a code example that generates a fairly simple spreadsheet. Click here to execute the code.
<?php /*This require call will be different depending on how your PEAR *is installed. The below call works with the zipped test PEAR *files I have provided if your script is in the same folder as *the zipped PEAR folder. *Normally, If you have PEAR installed correctly, you would *use the call: require_once('Spreadsheet/Excel/Writer.php'); */ require_once('PEAR/Spreadsheet_Excel_Writer/Writer.php'); /* Data to be inserted into excel in an array of arrays * Each array is the avg monthly temp of a different city */ $data = array ( array ( "City" => "Seattle", "Jan" => 46, "Feb" => 50, "Mar" => 53, "Apr" => 58, "May" => 64, "Jun" => 70, "Jul" => 75, "Aug" => 76, "Sep" => 70, "Oct" => 60, "Nov" => 51, "Dec" => 46 ), array ( "City" => "New York", "Jan" => 39, "Feb" => 42, "Mar" => 50, "Apr" => 60, "May" => 71, "Jun" => 79, "Jul" => 85, "Aug" => 83, "Sep" => 76, "Oct" => 64, "Nov" => 54, "Dec" => 44 ), array ( "City" => "San Francisco", "Jan" => 56, "Feb" => 59, "Mar" => 61, "Apr" => 64, "May" => 67, "Jun" => 70, "Jul" => 71, "Aug" => 72, "Sep" => 73, "Oct" => 70, "Nov" => 62, "Dec" => 56 ) ); $sheetTitle = "Monthly Average High Temperature By City"; /*We know the keys of each sub-array are the same, so *extract them from the first sub-array and set them *to be our column titles */ $columnTitles = array_keys($data[0]); $numColumns = count($columnTitles); //Creating a workbook $workbook = new Spreadsheet_Excel_Writer(); //Sending HTTP headers $workbook->send('Search_Export.xls'); //Creating a worksheet $worksheet=&$workbook->addWorksheet('Export'); $worksheet->setLandscape(); //set all columns same width $columnWidth = 10; $worksheet->setColumn (0, $numColumns, $columnWidth); //Setup different styles $sheetTitleFormat =& $workbook->addFormat(array('bold'=>1, 'size'=>10)); $columnTitleFormat =& $workbook->addFormat(array('bold'=>1, 'top'=>1, 'bottom'=>1 , 'size'=>9)); $regularFormat =& $workbook->addFormat(array('size'=>9, 'align'=>'left', 'textWrap'=>1)); /*Speadsheet writer is in format y,x (row, column) * column1 | column2 | column3 * (0,0) (0,1) (0,2) */ $column = 0; $row = 0; //Write sheet title in upper left cell $worksheet->write($row, $column, $sheetTitle, $sheetTitleFormat); //Write column titles two rows beneath sheet title $row += 2; foreach ($columnTitles as $title) { $worksheet->write($row, $column, $title, $columnTitleFormat); $column++; } //Write each datapoint to the sheet starting one row beneath $row++; foreach ($data as $subArrayKey => $subArray) { //Loop through each row $column = 0; foreach ($subArray as $value) { //Loop through each row's columns $worksheet->write($row, $column, $value, $regularFormat); $column++; } $row++; } $workbook->close(); ?>
Notes
There has not been any further development on this PEAR package since 2006, and there is a long user-submitted bug list on their site. Most of the bugs are related to the more complicated functionality possible with this package. To be careful, I would not make any generated spreadsheet too complicated in a production level application. I have also heard that very large spreadsheets (2mb+) can crash your script, so you may want to set a limit on the number of rows you are going to write.
This is really helpful.But i am quering the database and also trying to set up a link so that User can download the Spreadsheeet on their computer. My first problem was how to retrieve my data from the databse and save it in an array. Secondly, i could not use write method as well as landScape method. It will help if you can clear me up on those issues. Thanks!
Kalyan
Nice, exactly what I was looking for. I can easily adapt this idea to pull data from mySQL and end up with a formatted Excel workbook. Much better than the csv solution. My hosted environment has very little PEAR support, so thanks for including the files I needed to get this up and running in minutes.
You saved my life!!!
Thanks, well put together…and you just helped me implement this with AMFPHP with an array comming from a Flex UI.
Hi,
Really cool solution for creating Excel reports.
I have a issues in writing string of length greater then 255 characters in the cell as it is truncateing the string.
How can I write the string of more than 255 character length. Any solution to this.?
I’m using the code for this export to excel. it works fine in our test server but when i upload to a secure server (https://) it doesn’t work. any ideas how to fix this? thanks
reply on Visi
Hi I encountoured that problem also and managed to fix that. just add this code to your script
$workbook->setVersion(8);
@ Kalyan
Heres a sample code on how to export excel from a database
<?php
/*This require call will be different depending on how your PEAR
*is installed. The below call works with the zipped test PEAR
*files I have provided if your script is in the same folder as
*the zipped PEAR folder.
*Normally, If you have PEAR installed correctly, you would
*use the call: require_once(’Spreadsheet/Excel/Writer.php’); */
require_once(’dbconn.php’);
require_once(’PEAR/Spreadsheet_Excel_Writer/Writer.php’);
/* Data to be inserted into excel in an array of arrays
* Each array is the avg monthly temp of a different city */
$sql = “SELECT * FROM customers”;
$result = mysql_query($sql) or die(mysql_error());
$ctr = 0;
$ctr2 = 0;
while($rs = mysql_fetch_assoc($result)):
for($i = 0; $i send(’customers.xls’);
//Creating a worksheet
$worksheet=&$workbook->addWorksheet(’Export’);
$worksheet->setLandscape();
//set all columns same width
$columnWidth = 10;
$worksheet->setColumn (0, $numColumns, $columnWidth);
//Setup different styles
$sheetTitleFormat =& $workbook->addFormat(array(’bold’=>1,
’size’=>10));
$columnTitleFormat =& $workbook->addFormat(array(’bold’=>1,
‘top’=>1,
‘bottom’=>1 ,
’size’=>9));
$regularFormat =& $workbook->addFormat(array(’size’=>9,
‘align’=>’left’,
‘textWrap’=>1));
/*Speadsheet writer is in format y,x (row, column)
* column1 | column2 | column3
* (0,0) (0,1) (0,2) */
$column = 0;
$row = 0;
//Write sheet title in upper left cell
$worksheet->write($row, $column, $sheetTitle, $sheetTitleFormat);
//Write column titles two rows beneath sheet title
$row += 2;
foreach ($columnTitles as $title) {
$worksheet->write($row, $column, $title, $columnTitleFormat);
$column++;
}
//Write each datapoint to the sheet starting one row beneath
$row++;
foreach ($data as $subArrayKey => $subArray) {
//Loop through each row
$column = 0;
foreach ($subArray as $value) {
//Loop through each row’s columns
$worksheet->write($row, $column, $value, $regularFormat);
$column++;
}
$row++;
}
$workbook->close();
?>