10 May 2011

A first attempt at the PHP Excel extension

We needed a somewhat more advanced Excel file generation toolset for one of our customers and decided to take Ilia Alshanetsky's Excel extension for PHP for a spin. 

The extension exposes LibXL's power. LibXL allows for generating and editing XLS files, and extracting data from them. There are some code samples available on their site and a list of documented functions and classes. The php-excel extension exposes nearly all of the C API. A sample document on libxl's site shows off some of the features (such as support for formulas, formatting of cells and content, cell merges, grouping of rows, support for different data formats for the cell content, ...).


First, we're going to download and install all the necessary stuff for compiling and testing the extension. You can download a recent version of LibXL from here. These are the steps you can follow when on Debian or Ubuntu:

cd /src wget tar zxfv libxl.tar.gz cd libxl-3.1.0/

When compiling we need to symlink this (inside the libxl-3.1.0 dir):

ln -s include_c include
cd /src wget --no-check-certificate tar jxvf php-excel-0.9.0.tar.bz2 cd excel-0.9.0/

Now first check if you have the necessary build tools installed, if not (again, for Debian or Ubuntu):

apt-get install build-essential php5-dev

At this point, we are ready to compile the extension. Just follow these steps (inside /src/excel-0.9.0/) for 32 bit systems:

phpize ./configure --with-excel=../libxl-3.1.0/ --with-libdir=../libxl-3.1.0/lib make make test sudo make install

For 64 bit systems:

phpize ./configure --with-excel=../libxl-3.1.0/ --with-libdir=../libxl-3.1.0/lib64 make make test sudo make install

As an alternative, you could clone the Github repo and compile using that code (make sure you have git installed. If not, just sudo apt-get install git-core). Compiling using that code gave us the same error as php-excel-0.9.1.tar.bz2 however. 

As a result, you should have inside /usr/lib/php5/20090626/. If not, something went wrong. Now create the file /etc/php5/conf.d/excel.ini with the following content:

If everything went fine, you now have the Excel extension enabled. Double check like this:

jonas@chacmool:~/src/excel-0.9.0$ php -m | grep excel excel

Poking around

You can use Reflection to produce some info on the extension:

Will output quite some info. To get the classes made available by the extension:

getClasses(); foreach($classes as $class) { echo $class->getName().PHP_EOL; }

This will list:

  • ExcelBook
  • ExcelSheet
  • ExcelFormat
  • ExcelFont

If you had a peek at LibXL's documentation, you will notice the resemblance with the Book, Sheet, Format and Font classes.

You can examine each of them further by exploring each class:

getClasses(); foreach($classes as $class) { echo '
'.PHP_EOL; $methods = $class->getMethods(); foreach ($methods as $method) { $methodname = $method->getName(); if (substr($methodname, 0, 2) !== '__') { echo '' . $methodname . '( '; $parameters = array(); $params = $method->getParameters(); foreach($params as $params) { $parameters[] = '$'. (string) $params->getName(); } echo implode(", ", $parameters); echo ' )
'; } } }

Using reflection, you can squeeze out more info on the available methods, their parameters and return values. For the Excelbook class, the output looks like this:

ExcelBook addFont( $font ) addFormat( $format ) getAllFormats( ) getError( ) loadFile( $filename ) load( $data ) save( $filename ) getSheet( $sheet ) addSheet( $name ) copySheet( $name, $sheet_number ) deleteSheet( $sheet ) sheetCount( ) activeSheet( $sheet ) getCustomFormat( $id ) addCustomFormat( $format ) packDate( $timestamp ) unpackDate( $date ) getActiveSheet( ) setActiveSheet( $sheet ) getDefaultFont( ) setDefaultFont( $font, $font_size ) setLocale( ) addPictureFromFile( $filename ) addPictureFromString( $data ) rgbMode( ) setRGBMode( $mode ) colorPack( $r, $g, $b ) colorUnpack( $color )

Finally, some code

simple example

addSheet("foobar"); #write by cell - we skip the first row since libxl will output a demo message $sheet->write(1,0,'Company'); $sheet->write(1,1,'King Foo'); #write by row $data = array('Nieuwlandlaan', 16, 'B135', 3200, 'Aarschot'); $sheet->writeRow(2, $data); $doc->save('example1.xls');
output excel

Limitations of the free version

The PHP Excel extension we are testing gives access to the LibXL library, which unfortunately is commercial. You can experiment with the extension without a LibXL license, but your Excel file will have a banner in the first row of each spreadsheet. You also will be limited to read only 100 cells. A single developer license costs around 200 USD (more info here), which is certainly justifiable for most projects that require XLS creation that goes a bit further.

Whenever you decide to purchase a license, you can give your license id and license key as argument to the ExcelBook class on instantiation, like this:

$doc = new ExcelBook('Company or Your Name Here', 'linux-some-uid-here'); ...

Search for the line containing 'proto ExcelBook ExcelBook::__construct([string license_name, string license_key [, bool excel_2007 = false]])here The code suggest that excel.license_name and excel.license_key could be using in PHP's INI file, but we couldn't get this to work...

A more advanced example:

addFont(); $font->bold(true); $font->name('courier'); $format = $doc->addFormat(); $format->setFont($font); $sheet = $doc->addSheet("foobar"); $sheet->write(1, 0, 'Company', $format); $font2 = $doc->addFont(); $font2->name("Verdana"); $font2->size(14); $font2->strike(true); $font2->italics(true); $font2->mode(ExcelFont::SUBSCRIPT); $font2->underline(ExcelFont::UNDERLINE_SINGLE); $font2->color(ExcelFormat::COLOR_PALEBLUE); $format2 = $doc->addFormat(); $format2->setFont($font2); $sheet->write(2, 0, 'King Foo', $format2); $doc->save('example2.xls');
output excel
addFont(); $boldFont->bold(true); $titleFont = $doc->addFont(); $titleFont->name('Arial Black'); $titleFont->size(16); $titleFormat = $doc->addFormat(); $titleFormat->setFont($titleFont); $headerFormat = $doc->addFormat(); $headerFormat->horizontalAlign(ExcelFormat::ALIGNH_CENTER); $headerFormat->borderStyle(ExcelFormat::BORDERSTYLE_THIN); $headerFormat->setFont($boldFont); $headerFormat->fillPattern(ExcelFormat::FILLPATTERN_SOLID); $headerFormat->patternForegroundColor(ExcelFormat::COLOR_TAN); $descriptionFormat = $doc->addFormat(); $descriptionFormat->borderLeftStyle(ExcelFormat::BORDERSTYLE_THIN); $amountFormat = $doc->addFormat(); $amountFormat->numberFormat(ExcelFormat::NUMFORMAT_CURRENCY_NEGBRA); $amountFormat->borderLeftStyle(ExcelFormat::BORDERSTYLE_THIN); $amountFormat->borderRightStyle(ExcelFormat::BORDERSTYLE_THIN); $totalLabelFormat = $doc->addFormat(); $totalLabelFormat->borderTopStyle(ExcelFormat::BORDERSTYLE_THIN); $totalLabelFormat->horizontalAlign(ExcelFormat::ALIGNH_RIGHT); $totalLabelFormat->setFont($boldFont); $totalFormat = $doc->addFormat(); $totalFormat->numberFormat(ExcelFormat::NUMFORMAT_CURRENCY_NEGBRA); $totalFormat->borderStyle(ExcelFormat::BORDERSTYLE_THIN); $totalFormat->setFont($boldFont); $totalFormat->fillPattern(ExcelFormat::FILLPATTERN_SOLID); $totalFormat->patternForegroundColor(ExcelFormat::COLOR_YELLOW); $signatureFormat = $doc->addFormat(); $signatureFormat->horizontalAlign(ExcelFormat::ALIGNH_CENTER); $signatureFormat->borderTopStyle(ExcelFormat::BORDERSTYLE_THIN); $sheet = $doc->addSheet("invoice"); if ($sheet) { $sheet->write(2, 1, 'Invoice No. 3568', $titleFormat); $sheet->write(4, 1, 'Name: Ilia Alshanetsky'); $sheet->write(5, 1, 'Address: San Ramon, CA 94583 USA'); $sheet->write(7, 1, "Description", $headerFormat); $sheet->write(7, 2, "Amount", $headerFormat); $sheet->write(8, 1, "Ball-Point Pens", $descriptionFormat); $sheet->write(8, 2, 85, $amountFormat); $sheet->write(9, 1, "T-Shirts", $descriptionFormat); $sheet->write(9, 2, 150, $amountFormat); $sheet->write(10, 1, "Tea cups", $descriptionFormat); $sheet->write(10, 2, 45, $amountFormat); $sheet->write(11, 1, "Total:", $totalLabelFormat); $sheet->write(11, 2, 280, $totalFormat); $sheet->write(14, 2, "Signature", $signatureFormat); $sheet->setColWidth(1, 1, 40); $sheet->setColWidth(2, 2, 15); //this one added to give the title some space $sheet->setRowHeight(2, 25); } $doc->save('invoice.xls'); }
output excel

That's about it. This extension offers an interesting solution for anyone who wants to produce XLS files using the power of LibXL. You can experiment a bit further yourself of course. Don't forget to take a peek at the API documentation on the LibXL website to see what features are available. And of course there are alternatives, some may better suit your needs, some won't. Have a look at some of your options here:

  • PEAR's Spreadsheet_Excel_Writer
  • php-excel, a PHP based class, haven't tested it yet
  • mimicking Excel's XHTML output and counting on a decent import result
  • phpexcel ( seems a very interesting solution at first sight. It is implemented using Microsofts OpenXML spec, thus able to produce Office 2007 formats and upwards (which are XML based. So, no Office 2003 XLS files). Another major difference: it is written in PHP entirely and this probably results in a performance penalty compared to the C-based XLS extension. A benchmark between these two may be a good topic for a blog post :)

Hope you liked this post, credits for the info and code go to Hans too. Cheers!