Generate XLS Spreadsheet files with PHP


Did you want to generate Excel 2007 .xlsx files with PHP instead? This document only supports Excel 5.0 xls generation.

The .xls binary file format is documented at Microsoft Office Format Docs, however it is a 1000+ page doc, and would take a lot of time to implement it from scratch. .XLS files have used the BIFF (Binary Interchange File Format) from Excel 2.0 (1987) until Excel 11 (Office 2003). There have been major changes to the biff format, and its history is documented at the openoffice excel file format pdf.

As I looked into .xls generators in php and c++, most I found created spreadsheets in early excel (biff2-4) formats. I wanted code that generates an .xls in the most modern non-xml format, complete with modern formatting. I found one of these, at the PHP Extension and Application Repository (PEAR) called Spreadsheet_Excel_Writer. It is actually a port of the Spreadsheet::WriteExcel Perl module, and supports BIFF5, but it supports BMP images and enough formatting for most people's needs.

It has several pear dependencies:
* Spreadsheet_Excel_Writer
* PEAR
* OLE
(it might require a console package too if you are trying to put it together yourself)

I put it all together in a nice standalone package for php5 below. Note I did not officially install pear, just put a bunch of php files together, and fixed the include path errors.

The core of the .xls generation takes place in Writer.php, and the 2MB of PHP code in the PEAR, OLE and Spreadsheet_Excel_Writer classes, and here is sample code that uses functions from the Spreadsheet_Excel_Writer package.
<?php
//-----------------------------------------------------------------------------
//documentation on the spreadsheet package is at:
//http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.php
//-----------------------------------------------------------------------------
chdir('phpxls');
require_once 'Writer.php';
chdir('..');
 
$sheet1 =  array(
  array('eventid','eventtitle'       ,'datetime'           ,'description'      ,'notes'      ),
  array('5'      ,'Education Seminar','2010-05-12 08:00:00','Increase your WPM',''           ),
  array('6'      ,'Work Party'       ,'2010-05-13 15:30:00','Boss\'s Bday'     ,'bring tacos'),
  array('7'      ,'Conference Call'  ,'2010-05-14 11:00:00','access code x4321',''           ),
  array('8'      ,'Day Off'          ,'2010-05-15'         ,'Go to Home Depot' ,''           ),
);
 
$sheet2 =  array(
  array('eventid','funny_name'   ),
  array('32'      ,'Adam Baum'    ),
  array('33'      ,'Anne Teak'    ),
  array('34'      ,'Ali Katt'     ),
  array('35'      ,'Anita Bath'   ),  
  array('36'      ,'April Schauer'),
  array('37'      ,'Bill Board'   ),
);
 
$workbook = new Spreadsheet_Excel_Writer();
 
$format_und =& $workbook->addFormat();
$format_und->setBottom(2);//thick
$format_und->setBold();
$format_und->setColor('black');
$format_und->setFontFamily('Arial');
$format_und->setSize(8);
 
$format_reg =& $workbook->addFormat();
$format_reg->setColor('black');
$format_reg->setFontFamily('Arial');
$format_reg->setSize(8);
 
$arr = array(
      'Calendar'=>$sheet1,
      'Names'   =>$sheet2,
      );
 
foreach($arr as $wbname=>$rows)
{
    $rowcount = count($rows);
    $colcount = count($rows[0]);
 
    $worksheet =& $workbook->addWorksheet($wbname);
 
    $worksheet->setColumn(0,0, 6.14);//setColumn(startcol,endcol,float)
    $worksheet->setColumn(1,3,15.00);
    $worksheet->setColumn(4,4, 8.00);
 
    for( $j=0; $j<$rowcount; $j++ )
    {
        for($i=0; $i<$colcount;$i++)
        {
            $fmt  =& $format_reg;
            if ($j==0)
                $fmt =& $format_und;
 
            if (isset($rows[$j][$i]))
            {
                $data=$rows[$j][$i];
                $worksheet->write($j, $i, $data, $fmt);
            }
        }
    }
}
 
$workbook->send('test.xls');
$workbook->close();
 
?>


More sample code, and documentation for worksheet and workbook classes at:

Documentation - spreadsheet-excel-writer
Download test.xls (generated on the fly)
Download demo.zip (416 KB)

raymanexe on 2008-11-28 01:07:00
i'm currently using this exporting script. It works fine in http:// but when i uploaded it in https:// servers it won't generate the file. can you help me fix this problem?

Bob Ster on 2010-04-22 14:58:27
Finally, an all-in-one package that works as it should in one easy step! Much appreciated!

Ahmed Sharif on 2010-06-02 08:05:43
When I tried to access demo.php it shown following error:

"Parse error: syntax error, unexpected T_STATIC, expecting T_OLD_FUNCTION or T_FUNCTION or T_VAR or '}' in /usr/local/apache1/htdocs/demo/phpxls/Writer.php on line 84"

What should I do?
I am using php4.3,apache on RHEL4.

Steffan on 2010-09-13 16:26:42
This is the best I found for now, but what if I want to READ an xsl, or add image that overlap the cells at x,y pos?

gilesmc on 2010-10-01 15:41:52
Hi Steffan,
I use php-excel-reader to read Excel docs. You can find it here: http://code.google.com/p/php-excel-reader/source/browse/trunk/excel_reader2.php?r=5
Can't help with your image problem though, sorry!

GowriPHP on 2010-11-23 12:54:36
its nice code, but in server its not working fine for me

prema on 2010-11-24 10:27:35
Hi i have used this code its working in local but not working in server. am getting error like unable to read file
file size is zero. what is the solution for this?

Khalil Khalaf on 2010-11-28 21:50:52
I Read a .REP file which is a COBOL report and it's character-set is Arabic ASMO-708 which is equivalent to iso-8859-6, both of them are exists on outputEncoding function, but when I open the Excel file I got symbols, as the excel opened it on Arabic Windows (UTF-8).
Please I need help, I really liked this library
Regards

Scott M. Sanders on 2011-01-20 15:58:04
You should update to at least 0.9.2 from here: http://pear.php.net/package/Spreadsheet_Excel_Writer

It fixes an issue where setVersion(8) and setInputEncoding() is anything but default.

Daniel Zukowski on 2011-04-07 19:06:25
This package came is super handy on one of my recent projects that required a web form to be ported to an .xls file attached to an email. It works great when opening the file directly in Excel, however the file cannot be opened in Google Spreadsheets, giving the error: "The uploaded spreadsheet format is from an unsupported version."

Have you encountered this? Is there a way to adjust the file headers and spoof a different version of excel to try to make it digestible by Google?

Subodh Kumar on 2011-04-08 12:59:18
How can i add html in xls
Like it is coming from database in link like
Manage Admin

plz help

Ankit on 2011-05-18 07:15:03
hi
I have a multiple spreadsheet xls file. I have to append this file, I mean, I have to write values in some specific cells. Can you please help me?

Website laten maken on 2011-06-07 13:15:31
Wonderful Tutorial I ever seen, the step by step really help any newbies.. thanks....

Faheem Merchant on 2011-06-23 05:17:11
Phenomenal work, this script works well out of the box. Only things to mention are that if you want different column sizes for each spreadsheet you need to put the setColumn values into an array. And also for some reason if you try to include an external file using the require function, the Excel writer is unable to write correctly.

Nasir on 2011-08-09 02:28:56
Great work

Shezie on 2014-01-23 01:29:47
And now almost 6 years later, this is a lifesaver!!! Thank you good sir, it works perfectly!