VT’s Tech Blog

If you’re looking for an easy way to output your data from a PHP script into a Excel file, you’ve got to check out this script MS-Excel Stream Handler which I came across at the PHP Classes site. What this script essentially does is to implement a stream handler interface to write Microsoft Excel spreadsheet files.

All you have to do to output data into an Excel file is to create an associative array with the data to be exported. For example if you wanted an excel sheet in this format:

all you need to do is to create an array like this:

$output = array(
    array("Serial" => "1", "Item" => "Cables", "Stock" => "100", "Cost per unit" => "$2" ),
    array("Serial" => "2", "Item" => "Phones", "Stock" => "40", "Cost per unit" => "$23" ),
    array("Serial" => "3", "Item" => "Keypads", "Stock" => "5", "Cost per unit" => "$4" ),
    array("Serial" => "4", "Item" => "Helmets", "Stock" => "54", "Cost per unit" => "$42" ),
);

Now to output this into an Excel file, all you need to do is to use the excel.php from Ignatius’s script and open a xlsfile stream to output the data. Here’s a script which creates the Excel file from the array above and allows the user to download the file to their system:

require "excel.php";
//open Excel Stream to output
$export_file = "xlsfile://tmp/example.xls";
 
$fp = fopen($export_file, "wb");
$output = array(
    array("Serial" => "1", "Item" => "Cables", "Stock" => "100", "Cost per unit" => "$2" ),
    array("Serial" => "2", "Item" => "Phones", "Stock" => "40", "Cost per unit" => "$23" ),
    array("Serial" => "3", "Item" => "Keypads", "Stock" => "5", "Cost per unit" => "$4" ),
    array("Serial" => "4", "Item" => "Helmets", "Stock" => "54", "Cost per unit" => "$42" ),
);
//write the array to Excel
fwrite($fp, serialize($output));
fclose($fp);
//set headers to download Excel sheet
header ("Cache-Control: no-cache, must-revalidate");
header ("Pragma: no-cache");
header ("Content-type: application/vnd.ms-excel");
header ("Content-Disposition: attachment; filename=\"" . basename($export_file) . "\"" );
 
readfile($export_file);

This script allows you to quickly export any data which you can form into an array quickly into an Excel sheet. If you’re looking for an Excel export with advanced features like formatting the cells in the sheet, adding colors or the likes of those, you’re out of luck with this script.

You can get this script and more information about it from the PHP Classes site:
http://www.phpclasses.org/browse/package/1919.html

I just came across this cool article which shows you how to use Excel to run the business logic of a PHP application. Maarten shows you how to use Excel to calculate the cost of a Car and get these results to show in PHP using the PHPExcel library. Under the covers, the Excel formula is parsed into the corresponding PHP code and excuted.

Here’s a scenario: You are working in a company which sells “dream cars”. For every model, the company has created an Excel spreadsheet which is used to calculate the car’s price based on customer preferences. These spreadsheets are updated frequently in order to reflect the car manufacturer’s pricing schemes.

Your manager asks you to create a small website which accepts some input fields (Does the customer want automatic transmission? What colour should the car be painted? Does the customer want leather seats? Does the customer want sports suspension?). Based on these questions, the car’s price should be calculated. Make sure all prices on the website are in sync with this Excel sheet!

Link to Maarten’s Article: Reuse Excel business logic with PHPExcel