Create Excel files with PHP

1 Star2 Star3 Star4 Star5 Star (15 votes, average: 3.80 out of 5)
Loading ... Loading ...

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

  • Tejas

    Great…
    I have one question.
    Can I create multiple individual Excel files using Header() in PHP? After creating each excel file, ask me to download that output file..
    Is this possible?? If so, please help me.
    Thanks!….

  • Dharm

    Its good for simple excle file and work fine. But we cant customise/ butify the excle file like bold, background color etc.

  • bhavya

    This is a nice

  • http://aol.com Ray B Patel

    Very childish writing style.

  • http://none Steven

    I have a question. I understood how to fillthe excel-file’s worksheet with data. Is there any possibility to send VBA code lines to this excel file? I want the feature, that the exportet excel file is delivered with some makros. Thanks in advance, Steven.

  • Tulga

    It’s not perfect way.

  • http://www.whtl.co.in Rajeev

    Hi

    Can you please explain how the path in “xlsfile://tmp” is to be specified exactly with an example?

    Thanks

  • test

    this is s atest

  • http://n/a biirdo

    Having problems with the numbers being sent to the spreadsheet. The numbers appear with a ‘ character at the start of each value. This means no calculations can be done in the spreadsheet. Any ideas how to remove the leading ‘ character please?

  • http://nowebsite Lieve

    Hey!!!

    This snap, is crap, does not work!!!

Si prega di attivare i Javascript! / Please turn on Javascript!
Javaskripta ko calu karem! / Bitte schalten Sie Javascript!
S'il vous plaît activer Javascript! / Por favor, active Javascript!
Qing dakai JavaScript / Qing dakai JavaScriptBetter Stop Adblock