PHP – Writing to Excel File via PEAR on Zend Framework

Two weeks ago, I was developing a web based application that saves data into an Excel file. It will create a new Excel file, add header rows in bold text, sets the column width and saves the data beneath the header.

The application is written in Zend Framework. I thought Zend Framework has its own library on writing to Excel, unfortunately, there is none. So I go for PEAR.

PEAR has a package called Spreadsheet_Excel_Writer that can save Excel file without the need of COM components.

*Note: This PEAR package is not maintained anymore so if you want to use it, do it at your own risk.

Spreadsheet_Excel_Writer requires OLE 0.5 or newer and works on PHP 4.1.0 or higher. Of course it need PEAR to make it work.

Making PEAR work with Zend Framework

PEAR and Zend Framework share some similarities with naming conventions, that is why it is not that hard to integrate PEAR to your application. As long as PEAR is included in your include path, wherever it is, it will work with ZF.

I choose not to use PEAR as system wide library. Instead, I placed the whole PEAR directory beside Zend directory under library. You also need to autoload it via Bootstrap.php. My directory structure looks like below:

Autoload PEAR at Bootstrap.php:

$autoloader->registerNamespace(‘PEAR_’);

The Code

I have created a class that writes data into an Excel file. For the sake of simplicity, I will rather write the straightforward codes. You can actually view these on the PEAR manual.

//write the full path if you want
$fileName = 'test.xls';
$workBook = new Spreadsheet_Excel_Writer($fileName);

//add a style/format and return its reference
$formatBold =& $workBook->addFormat();
$formatBold->setBold();

//create a worksheet and return a reference
$workSheet =& $workBook->addWorksheet($lotNumber);

//write header
$workSheet->write(0, 0, 'No.', $formatBold);
$workSheet->write(0, 1, 'LOT NUMBER', $formatBold);
$workSheet->write(0, 2, 'SCAN NUMBER', $formatBold);

//set column widths
$workSheet->setColumn(1, 1, 16);
$workSheet->setColumn(2, 2, 14);

//your data is either from the database or from any other source
foreach ($data as $key => $row)
{
	//compute column position
	$x = $key + 1;
	//write now
	$workSheet->write($x, 0, $row['Seq_No']);
	$workSheet->write($x, 1, $row['Lot_Number']);
	$workSheet->writeString($x, 2, $row['Scan_Number']);
}
//closing the workbook will save the excel file to the server
$workBook->close();