Creating XLS file on the fly from a PHP application

By
Andrea Benini
-
663

Here’s a quick and dirty solution for a common problem, if you’ve a web application used by common Windows users and you publish data on it sometimes you receive this kind of question:

“Are your information exportable into an XLS (Excel) file ?”

For example I’ve an accounting application exporting some data on a web page, the user just wants to download an Excel file or open it directly from the web page, there’s no rocket science here, this is just what I’ve done, let’s roll some php code:

Create an header section (adapted from http://www.php.net/manual/en/function.header.php):

$export_file = "my_name.xls";
ob_end_clean();
ini_set('zlib.output_compression','Off');
  
header('Pragma: public');
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");                 // Date in the past  
header('Last-Modified: '.gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: no-store, no-cache, must-revalidate');     // HTTP/1.1
header('Cache-Control: pre-check=0, post-check=0, max-age=0');    // HTTP/1.1
header("Pragma: no-cache");
header("Expires: 0");
header('Content-Transfer-Encoding: none');
header('Content-Type: application/vnd.ms-excel;');                // This should work for IE & Opera
header("Content-type: application/x-msexcel");                    // This should work for the rest
header('Content-Disposition: attachment; filename="'.basename($export_file).'"');

Put your data in a string (of course init it first):

$sBuffer = "";

Then start adding your data in a loop

for (...your loop statement goes here...) {
    $sBuffer .= "column1column2column3column4
";
}

Example above creates 4 columns with your raw data, quite easy to adapt to your own needs. Then finally output your data with this:

echo($sBuffer);

put this final part after the header section expressed above and that’s it ! nothing spectacular I mean but it works fine with every computer with an office automation suite. When the user press your button it gets an XLS sheet and he can open it with Office or OpenOffice as well, it doesn’t matter client or server operating system, this trick follows just Excel v1 and v2 specs (very acient but still working).

Please don’t deal with ActiveX, proprietary grids or closed source solutions, this is just what you need to keep it simple

 

Hope it helps
Glad to hear your comments

 

Andrea (Ben) Benini

RELATED ARTICLESMORE FROM AUTHOR