Generate XLS Spreadsheet files with PHPThe .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 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. Gordon Skidds on Nov 14th, 2011 3:06 pm said: Question, the example worked just fine and my replacement arrays did also. However, the miniute i pass $_POST or $_SESSION option parameters to the php file from another php file, the workbook is all messed up. Any clue as to why? Even a simple test of "if(IsChecked($_POST['opts'],'A')) {....." will completely demolish the table data. Appreciate any thoughts. Thanks. Ravendra on Nov 23rd, 2011 5:39 pm said: Hi any one please help me on php excel Writer. I'm using Spreadsheet_Excel_Writer class to generate excel file but it generate garbage data in excel file if I include any other file in current file. If I include only writer class file not other file then it works fine. Please help me. I want to include some setting file before generating excel file. Thanks, pawan | Related Articles php 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 |
©2012, Zedwood.com
zedwood.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to amazon.com