Create Excel files with PHP

dangtruong

Author lv1 (3900/5000)

14,724 views

6 Comments

(11 votes, average: 3.73 out of 5)

Have you ever thought of creating excel files from your web site? Excel is a very useful format for lots of purposes. For example, exporting stats, member lists to hand over to economy department etc.

But how can we create a simple excel file with a header and two different columns?
Of course you can sit down and read the excel specifications, and do it your self…
Or the smarter way, download classes from phpclasses.org made by Ignatius Teo.

You use these classes to generate your excel files. Pretty simple, but very effective.
Start with downloading (registrate for free to download), here http://www.phpclasses.org/browse/package/1919.html .
Unzip and upload the file named excel.php to your server.

I am going to generate a simple excel file that will contain Name and IQ in two columns :-)
The file will look something like this (Hopefully):
First name IQ
Mattias 250
Tony 100
Peter 100
Edvard 100

(These numbers are just estimates… ;-)

Create the php file that will create the excel files for you.
Lets call it: generate.php

Start with:

[source:php]

require_once “excel.php”;
$filename = “theFile.xls”;Wrox Press

[/source]

This section will create and save the excel file on the server.
The file will be saved in the directory tmp, as theFile.xls.
The code is pretty self explaining I think. don’t bother with small details, just use it as a tool!

[source:php]

$export_file = “xlsfile://tmp/”.$filename;
$fp = fopen($export_file, “wb”);
if (!is_resource($fp))
{
die(”Cannot open $export_file”);
}

// typically this will be generated/read from a database table
$assoc = array(
array(”First name” => “Mattias”, “IQ” => 250,
array(”First name” => “Tony”, “IQ” => 100,
array(”First name” => “Peter”, “IQ” => 100,
array(”First name” => “Edvard”, “IQ” => 100);

fwrite($fp, serialize($assoc));
fclose($fp);

[/source]

This section is for opening the file directly for the surfer in his browser.
It’s then up to the user to choose if he/she wants to open or save the file.
If you only want to save files on the server, just remove this part from the file.

[source:php]

header (”Expires: Mon, 26 Jul 1997 05:00:00 GMT”);
header (”Last-Modified: ” . gmdate(”D,d M YH:i:s”) . ” GMT”);
header (”Cache-Control: no-cache, must-revalidate”);
header (”Pragma: no-cache”);
header (”Content-type: application/x-msexcel”);
header (”Content-Disposition: attachment; filename=\”" . $filename . “\”" );
header (”Content-Description: PHP/INTERBASE Generated Data” );
readfile($export_file);
exit;

[/source]

If you run this code you will get two things:

1. An excel file in /var/ on your server
2. This file opened to the user that surf your site

This is the whole tutorial, showing you that it is not that hard to create an excel file or open it to the user.

Copyright @ Gary Logsdon