Creating Excel .xls spreadsheets with PHP

A web-based statistics system I'm developing for a client needs the facility to dynamically generate Excel spreadsheets. The system runs on a Linux platform with a typical Apache, MySQL and PHP install and is hosted on a virtual server at a commercial hosting company so access to anything Microsoft or COM-like wasn't an option and I only had a basic PHP install available. Most web searches come up with solutions that rely on PEAR (such as the Spreadsheet_Excel_Writer module) but my hosts' PHP didn't support PEAR and they wouldn't let me install it.

Finally I came across Johann Hanne's port of the Perl Spreadsheet::WriteExcel module on his website and it's working brilliantly without any additional dependencies! Although his documentation is a little thin on the ground, he does provide some example scripts to get you going and as it's a direct port, you can still refer to the original Perl documentation.

Creating a simple spreadsheet in PHP is as simple as importing the writeexcel classes and setting up a new workbook and worksheet:

require_once "class.writeexcel_workbook.inc.php";
require_once "class.writeexcel_worksheet.inc.php";

$fname = tempnam("/tmp", "simple.xls");
$workbook = &new writeexcel_workbook($fname);
$worksheet = &$workbook->addworksheet();

...then use the write($row, $column, $token) function to put values into cells:

# Write some text
$worksheet->write(0, 0,  "Hi Excel!");
# Write some numbers
$worksheet->write(1, 0,  3);

...and tidy up and return it, in this example through the browser, but you could always copy the file from the /tmp folder to somewhere more permanent and do what you want with it:

header("Content-Type: application/x-msexcel; name="example.xls"");
header("Content-Disposition: inline; filename="example.xls"");
$fh=fopen($fname, "rb");

...and there you go, automatically generated Excel spreadsheets through PHP.

Share this: