1 July 2009 View Comments

Saving Data into Excel the Easy Way using PHP

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:
Excel Stream Writer

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

Reblog this post [with Zemanta]

Tags: ,

Trackbacks/Pingbacks.

  1. Vinu Thomas’ Blog: Saving Data into Excel the Easy Way using PHP | DreamNest - Technology | Web | Net - 03. Jul, 2009

    [...] Thomas has a new post to his blog looking at using the MS-Excel Stream Handler class to push your data out to Excel (in [...]

Leave a Reply

blog comments powered by Disqus