Nov 12 2007
PHP to Excel
I was tasked with exporting report to Microsoft Excel files. There are a number of ways to do this. One of the easiest I have found it to create a tab delimited file and give it an .xls extension. You can also create a table using html and give it an .xls extension for similar results. The main issue with doing this is that it is not really an excel file so you may get an error like this: 
So, not wanted to re-invent the wheel, I went searching for a good php class I could use. I ended up selected this one. What I liked most about it was that I could just pass in a multi-dimensional array (which my data was already in) and it would create a Excel file from that.
Well, soon I got complaints that number fields were being stored as strings so it was back to the drawing board. I then came across this port of a popular Perl package. It had a lot more features I could use and it wrote numbers as numbers. I wrote a simple function that converted an array to an Excel file. I’ll post it here for your enjoyment.
$test = array(
array (
'Text' => 'abc',
'Number' => 123,
'Date' => '28345.345'
),
array (
'Text' => 'def',
'Number' => 456,
'Date' => '8/8/1977 7:00 AM'
),
array (
'Text' => 'ghi',
'Number' => 789,
'Date' => '1/1/2007'
)
);
echo '
'; print_r($test);
Array2Xls($test, 'test.xls', 'Report');
function Array2Xls($array, $filename, $worksheetname = 'Sheet1') {
require_once "php_writeexcel-0.3.0/class.writeexcel_workbook.inc.php";
require_once "php_writeexcel-0.3.0/class.writeexcel_worksheet.inc.php";
$workbook = &new writeexcel_workbook($filename);
$worksheet =& $workbook->addworksheet($worksheetname);
$heading =& $workbook->addformat(array('align' => 'center', 'bold' => 1, 'bg_color' => 'black', 'color' => 'white'));
$date_format =& $workbook->addformat(array(num_format => 'mm/dd/yyyy hh:mm:ss AM/PM'));
$worksheet->set_column(0, 3, 15);
// heading
$i = 0;
$h = array();
foreach(array_keys($array[0]) as $val) {
$worksheet->write(0, $i++, $val, $heading);
array_push($h, $val);
}
// values
for ($i=0; $i
There is still one issue I have yet to resolve and that is concerning dates. I’ve been searching for a PHP script that can convert Unix timestamps or date strings to Excel date numbers. I gave up on it figured I will implement it when or if the request comes in to do so 
It’s that time of year again. My contract is up on 
