For many years, whenever one of my web applications needed to output tabular data I’d rely on CSV format. This does the trick for most data, as Excel and most other spreadsheet programs will open CSV’s without a problem. What happens when you want to get a little fancier though? Maybe include some formatting on columns, or even embed images?

In that case CSV doesn’t cut it anymore.  I had to investigate some methods of generating a valid XLS file directly with PHP.

Pack those Bytes!

After Googling for a few minutes I came across a few crude binary functions that would generate a valid XLS file (code below)

<?php
function xlsBOF() {
   return pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
}
 
function xlsEOF() {
   return pack("ss", 0x0A, 0x00);
}
 
function xlsWriteNumber($row, $col, $value) {
   $ret = pack("sssss", 0x203, 14, $row, $col, 0x0);
   $ret .= pack("d", $value);
 
   return $ret;
}
 
function xlsWriteLabel($row, $col, $value ) {
   $ret = pack("ssssss", 0x204, 8 + strlen($value), $row, $col, 0x0, strlen($value));
   $ret .= $value;
 
   return $ret;
}
 
$output = xlsBOF();
$output .= xlsWriteLabel(0, 0, "First Column");
$output .= xlsWriteLabel(0, 1, "Second Column");
$output .= xlsWriteLabel(0, 2, "Third Column");
$output .= xlsWriteNumber(1, 0, 10);
$output .= xlsWriteNumber(1, 1, 20);
$output .= xlsWriteNumber(1, 2, 30);
$output .= xlsEOF();
 
file_put_contents("test.xls", $output);

Now this code works fine, but doesn’t give us any advantages over the CSV format.

After doing a bit more reading I discovered that Excel will interpret HTML table data directly. While a neat trick, inevitably there will be some limitations doing this. Would it work with images? Multiple worksheets? Probably not.

PEAR to the rescue!

Finally I found a PEAR library which seems to be what I need. Spread_Excel_Writer. Relying on an OLE object provided by another PEAR module, this bit of code seems to make tasks like creating multiple worksheets, formatting columns, even adding formulas and images a snap.

Runners Up

Also worth mentioning is a package called xlsLib I found. There is recent activity on the package, but it is not a direct PHP solution… so I’ve left it for now.

Have you had any luck with different packages for doing complex Excel tasks in PHP?