Excel Spreadsheets in PHP using PEAR

Posted on December 7, 2007 by Elliott Brueggeman12 Comments »

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.

Comments

Kalyan on 27 Apr 2008 at 6:34 pm

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
JNettik on 26 Sep 2008 at 9:13 am

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.
yuri on 08 Oct 2008 at 2:59 pm

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.
Visi on 15 Oct 2008 at 10:14 pm

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.?
raymanexe on 26 Nov 2008 at 5:59 pm

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
raymanexe on 26 Nov 2008 at 6:01 pm

reply on Visi

Hi I encountoured that problem also and managed to fix that. just add this code to your script

$workbook->setVersion(8);
raymanexe on 26 Nov 2008 at 6:04 pm

@ 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();
?>
Anto on 30 Dec 2008 at 4:37 am

Hi Ray,

Is there any way to save workbook object into a file *.xls (at server or client side) other than using

//Sending HTTP headers
$workbook->send(’Search_Export.xls’);

Regards
Anto
raymanexe on 23 Mar 2009 at 9:04 pm

Hi Anto,
I haven’t tried it yet but i think you can. here’s the code on how to do it.
Ex.
$workbook = new Spreadsheet_Excel_Writer(’exportfile.xls’);

Look to this website for more info.
http://www.jigishthakar.com/2008/10/28/spreadsheet-excel-writer/
bijita on 29 Apr 2009 at 11:24 pm

vvery useful article!!
thanks to the devloper!!
Madhura on 09 Mar 2010 at 4:21 am

Can anyone Please help me in downloading .xls using amfphp?
Merge Excel Files on 04 Aug 2010 at 4:49 pm

Now, how can anyone help me convert a bunch of .xls files to .csv format – pretty standard set of files. THanks a bunch.