Create Excel 2007 Spreadsheets using PHP CodeUnit 10 APR 2010

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/");
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->getActiveSheet()->setCellValue('A1','Hello World');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

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:

Related Posts:

About Craig Lotter

Software developer, husband and dad to two little girls. Writer behind An Exploring South African. I don't have time for myself any more.

  • sri aruna

    can you please suggest me in keeping workbook level security.

  • I'm sorry, but I don't usually need to deal with workbook security, so my knowledge on the subject is pretty thin.

  • Rekha Talwar

    good script….but when i tery to run the above script on loop then its create a excel file with some junk values. Actually i need to create excel workbook with multiple sheets and number of the sheets can vary. It can be one sheet or can be 10 or more depending upon a condtion.
    Please help me how would i use the above code to generate multiple excel sheets at run time using PHP?

  • Tolga Hayıt

    great work!

  • Tolga Hayıt

    How to set Page margins?