home   articles   tags   browse code   

Generate XLS Spreadsheet files with PHP


 

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 requires several packages:
* 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. 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)
 

Tags: php
 
raymanexe on Nov 27th, 2008 6:07 pm said:
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 Apr 22nd, 2010 8:58 am said:
Finally, an all-in-one package that works as it should in one easy step! Much appreciated!
 

Ahmed Sharif on Jun 2nd, 2010 2:05 am said:
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.
 

Bookmark this...
 
Comment on this...
 


 

 



Related Articles
 

php
 

home  |  privacy policy  |  terms of use  |  contact  


©2010, Zedwood Digital