King Foo

King Foo

You are in:

King Foo > Blog > PHP > A first attempt at the PHP Excel extension

Blog

A first attempt at the PHP Excel extension

Posted by
/ / 9 Comments

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, …).

Install

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 http://libxl.com/download/libxl.tar.gz
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

You can clone the code from Github or download a recent snapshot: http://github.com/downloads/iliaal/php_excel/php-excel-0.9.0.tar.bz2. There is a more recent file (php-excel-0.9.1.tar.bz2), but we couldn’t compile the extension using that snapshot…


cd /src
wget --no-check-certificate http://github.com/downloads/iliaal/php_excel/php-excel-0.9.0.tar.bz2
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 excel.so inside /usr/lib/php5/20090626/. If not, something went wrong. Now create the file /etc/php5/conf.d/excel.ini with the following content:


extension=excel.so

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:


<?php
$reflect = new ReflectionExtension('excel');
echo $reflect;

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


<?php
$reflect = new ReflectionExtension('excel');
$classes = $reflect->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:


<?php
$reflect = new ReflectionExtension('excel');

$classes = $reflect->getClasses();
foreach($classes as $class) {
    echo '<h5>'.$class->getName().'</h5>'.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 ) 

Full output for the four classes is available here.

Finally, some code

simple example


<?php
$doc = new ExcelBook();

$sheet = $doc->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');

And here’s the output:

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


<?php
$doc = new ExcelBook();

$font = $doc->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');

And here’s the output for the second example:

.

The invoice example

As an additional exercise, have a look at this invoice example we created using the example code at http://www.libxl.com/invoice.html (also check out the image of the result there).:


<?php
$doc = new ExcelBook();
if ($doc) {
    $boldFont = $doc->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');
}

To have a better understanding of what happens, have another look at the info retrieved using reflection. In this listing we added class constants. Especially for the ExcelFormat class, it’s helpful.

Here is the resulting Excel file and this is what it looks like:

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 (http://www.phpexcel.net/) 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!

  1. 17/03/2014

    Girish

    Once the extension is compiled it needs to be enabled in the php.ini file.
    extension=excel.so
    Restart apache server once the line is added.

  2. 12/03/2013

    Gawan

    Very good instructions, many thanks. But the parameters for library- and include-directory changed. The correct configure-command for 64bit-systems should be now like this:
    ./configure –with-libxl-incdir=../libxl-3.4.1.4/ –with-libxl-libdir=../libxl-3.4.1.4/lib64/

  3. 05/09/2012

    Justinas

    php-excel 0.9.6
    libxl libxl-3.3.1

    ./configure –with-excel=../libxl-3.3.1/ –with-libdir=../libxl-3.3.1/lib
    checking for excel includes… not found
    configure: error: Please reinstall the excel distribution

    doesn’t find includes

    • 05/09/2012

      Justinas

      compiles with 0.9.6 and libxml 3.3.1:

      ./configure –with-excel –with-libxl-libdir=../libxl-3.3.1/lib –with-libxl-incdir=../libxl-3.3.1/include_c

  4. 12/08/2011

    Tamlyn

    Thanks for the link to libxl 3.1.0. I’m compiling php_excel 0.9.0 on a virtual 64 bit Ubuntu server. I get a clean configure and make but 17 of the tests fail. I tried using it anyway and it works for some things but at other times Apache segfaults.

    We’re considering making a really simple command line wrapper for LibXL and bypassing php_excel all together.

    • 09/05/2014

      Martin

      I know this is comment to really old discussion, but I had very same problem and found this blog post as first result i Google.

      For you who have 17 failing tests, usually the reason is that license is not entered – so do not get afraid of this.

  5. 09/08/2011

    jonas

    I contacted the LibXL guys and they sent me a download link for 3.1.0 (http://www.libxl.com/download/libxl-3.1.0.tar.gz).

    Trying again results in this:

    root@uhu:/usr/local/src/excel-0.9.5# ./configure --with-excel=../libxl-3.2.1 --with-libdir=../libxl-3.2.1/lib
    ....
    configure: error: Please reinstall the excel distribution
    root@uhu:/usr/local/src/excel-0.9.5# ./configure --with-excel=../libxl-3.1.0 --with-libdir=../libxl-3.1.0/lib
    ....
    configure: error: Please reinstall the excel distribution


    root@uhu:/usr/local/src/excel-0.9.1# ./configure --with-excel=../libxl-3.2.1 --with-libdir=../libxl-3.2.1/lib
    ....
    configure: error: Please reinstall the excel distribution
    root@uhu:/usr/local/src/excel-0.9.1# ./configure --with-excel=../libxl-3.1.0 --with-libdir=../libxl-3.1.0/lib
    ....
    configure: error: Please reinstall the excel distribution

    Doing the same for excel-0.9.0 however gives a clean configure output. I’ll dig around a bit to see if this can be fixed.

  6. 08/08/2011

    jonas

    @tamlyn , since it was a while ago, I compiled the different versions again. The link to http://libxl.com/download/libxl.tar.gz leads to version 3.2.1 by now and I still get compile erors like you. I contact LibXL to see if I can get an older version to re-test it.

    Compiling php-excel 0.9.0 with 3.2.1 gives me new errors on a 32bit machine. I’m gonna try it on a 64bit machine later on.

  7. 06/08/2011

    Tamlyn

    Thanks for these helpful instructions. It worked with 0.9.0 but I’m having problems compiling 0.9.1 and 0.9.5 too. I get “configure: error: Please reinstall the excel distribution” during the configure step. Was that the error you got?

Leave a comment

Please wrap all source codes with [code][/code] tags.