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. In particular the reference pages on cell number format strings here and here were most useful when tweaking the output.
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.
Tags: Microsoft Excel, PHP, programming, spreadsheet, spreadsheet generation, xls
