Linux.com

Home Linux Community Community Blogs Servers Creating XLS file on the fly from a PHP application

Creating XLS file on the fly from a PHP application

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

 

Comments

Subscribe to Comments Feed

Upcoming Linux Foundation Courses

  1. LFD320 Linux Kernel Internals and Debugging
    03 Nov » 07 Nov - Virtual
    Details
  2. LFS416 Linux Security
    03 Nov » 06 Nov - Virtual
    Details
  3. LFS426 Linux Performance Tuning
    10 Nov » 13 Nov - Virtual
    Details

View All Upcoming Courses


Who we are ?

The Linux Foundation is a non-profit consortium dedicated to the growth of Linux.

More About the foundation...

Frequent Questions

Join / Linux Training / Board