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:
(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.
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)
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.
Khalil Khalaf on Nov 28th, 2010 2:50 pm said:
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
Daniel Zukowski on Apr 7th, 2011 1:06 pm said:
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?
Faheem Merchant on Jun 22nd, 2011 11:17 pm said:
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.
Calculate Script Duration in PHP and...
Generate CSV Spreadsheet with PHP
Generate PDFs with PHP
Generate XLS Spreadsheet files with ...
How to properly escape inline javasc...
HTML Table Row Highlight
PHP - Resize an Image with GD
PHP Calculate Duration of MP3
PHP Create Zip file
PHP mail Function With Attachments