Create Excel 2007 Spreadsheets using PHP

For the quickest implementation time in terms of churning out Excel 2007 spreadsheets in your PHP-based project, look no further than the brilliant PHPExcel project!

Built around Microsoft’s OpenXML standard and PHP, PHPExcel is a brilliant little spreadsheet engine that can read and write from a variety of different file formats like Excel 2007, PDF and HTML, with the added bonus of being able to even generate the binary format used for Excel 2003 spreadsheets.

Installing PHPExcel is a simple matter of downloading the zip file and unzipping all of the class files to a location which you then reference in your script file. A simple hello world example which generates an Excel 2003 spreadsheet is as follows:

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=example.xls");
header('Cache-Control: max-age=0');

require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Example');
$objPHPExcel->getActiveSheet()->setCellValue('A1','Hello World');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

And that’s really it. The system allows you to manipulate spreadsheet meta data like author, title and description, etc., work with multiple worksheets containing different fonts, font styles, cell borders, fills, gradients and even the ability to add in images into your Excel workbooks!

Taken from the project site’s feature list, PHPExcel is able to:

• Create an in-memory spreadsheet representation
• Set spreadsheet meta data (author, title, description, …)
• Add worksheets to spreadsheet
• Add data and formulas to individual cells
• Merge cells
• Protect ranges of cells with a password
• Supports setting cell width and height
• Supports different fonts and font styles
• Supports formatting, styles, cell borders, fills, gradients, …
• Supports hyperlinks
• Supports different data types for individual cells
• Supports cell text wrapping
• Supports conditional formatting
• Supports column auto-sizing
• Supports rich-text strings
• Supports autofilter
• Supports “freezing” cell panes
• Supports cell-level security
• Supports workbook-level security
• Supports worksheet-level protection
• Group rows/columns
• Cell data validation
• Insert/remove rows/columns
• Named ranges
• Worksheet references
• Calculate formula values
• Add comments to a cell
• Add images to your spreadsheet
• Set image styles
  • Positioning
  • Rotation
  • Shadow
• Set printing options
  • Header
  • Footer
  • Page margins
  • Paper size
  • Orientation
  • Row and column breaks
  • Repeat rows at header / columns at left
  • Print area
• Output your spreadsheet object to different file formats
  • Excel 2007 (spreadsheetML)
  • BIFF8 (Excel 97 and higher)
  • PHPExcel Serialized Spreadsheet
  • CSV (Comma Separated Values)
  • HTML
  • PDF
• Read different file formats into your spreadsheet object
  • Excel 2007 (spreadsheetML)
  • BIFF5 (Excel 5.0 / Excel 95), BIFF8 (Excel 97 and higher)
  • PHPExcel Serialized Spreadsheet
  • Excel 2003 XML format
  • Symbolic Link (SYLK)
  • CSV (Comma Separated Values)

Looks like the only missing is the ability to generate graphs! (But if you place in your graphs as already generated images… well then I guess you might not really miss it after all! :P)

Extremely easy to use, fairly good and comprehensive documentation and a huge amount of functionality, generating Microsoft Excel spreadsheets with PHP has just become easy! :)

Related Link: http://phpexcel.codeplex.com/