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:
$workbook->close();
header("Content-Type: application/x-msexcel; name="example.xls"");
header("Content-Disposition: inline; filename="example.xls"");
$fh=fopen($fname, "rb");
fpassthru($fh);
unlink($fname);
...and there you go, automatically generated Excel spreadsheets through PHP.