PHP Excel Extension

First of all, why? Our business requirements at work require us to frequently to generate Excel versions of the reports from the system and occasionally import data from Excel. For last few years we've done this via php classes; Spreadsheet Excel Writer and Reader. They do the job, but have severe limitations, one them being speed, and the other the output that supports only BIFF5. Unfortunately quite a few things don't grok BIFF5, such as Blackerries, Numbers from Apple's iWork, iPhones, etc… In instances when this poses an issue we've mitigated the problem by up-converting to BIFF7 via Gnumeric's command line tool, at a further speed loss. A workable, but a sub-optimal solution.

As we are doing more & more Excel output generation, this became a bigger and bigger problem. At first we've tried solving the problem via a newer PHP based Excel library, PHPExcel. Unfortunately, it is massive beast, that is not only slower than the old Spreadsheet Excel Writer/Reader, but memory hog too. Without caching, 1 Excel cell takes 10kb(!!!!) of memory and with caching a mere 1kb of memory. Trying to generate a 500-600 cell spreadsheet easily spikes one X5560 @ 2.80GHz core a few seconds, even after we've spent some time tweaking the library.

So, I turned to Google and found LibXL, which is a small, C++ (with C, C++ interfaces) library that promised really fast Excel reading & writing. The library natively generates BIFF 7/8 format, which can be opened all devices and Excel readers I've tried it on and is available for Linux, Mac and Windows. Unfortunately, the library is not Open Source, it is a closed source commercial product and while I'd prefer to use an Open Source solution, it is what it is. Fortunately the licensing cost is fairly minimal and the promised speed was quite amazing and after a few small test cases I was sold. Hence the 1.5 week process of writing PHP interface to it with one hand .

The end result is a PHP extension that offers a complete, object oriented interface to the library that allows you to do just about anything with Excel. I've also introduced a few helper functions to streamline reading/writing from Excel files. The speed and memory usage are pretty damn impressive, on my debug build of PHP, it can generate 10,000 cells in 0.03 seconds with a 262kb complete PHP memory usage, not just the code. Sample script is below:


$sT = microtime(1);

$x = new ExcelBook();
$s = $x->addSheet("Sheet 1");

for ($i = 0; $i < 1000; $i++) {
for ($j = 0; $j < 10; $j++) {
$s->write($i, $j, ($i * $j));
}
}

$x->save("bench.xls");

$eT = microtime(1);

var_dump(($eT - $sT), memory_get_usage(1), memory_get_peak_usage(1));


The extension is fully object oriented, and includes some 50 tests that demonstrate all of the available functionality, I've also added arginfo information, so you can see interrogate all of the available functionality via Reflection extension. Outside of that there is no documentation yet, I'll try to get to it, when I can better use my right hand (hopefully soon).

So if you need to work with Excel files, definitely try this little extension, maybe it can help you as well. If you have any feedback on bugs, or suggested features I please let me know...

One thing I should note, is that the underlying library currently does not support Office's XML format.