Introducing PEAR::Spreadsheet_Excel_Writer

Let's face it -- although you and I may be preoccupied with XML markup, tabbed browsing, editors like Emacs and VI, and minimizing the use of system resources, the rest of the computer-using world is happily chugging away with the likes of Microsoft Office. And although they may be moderately impressed by the amazing things you can do with an HTML table, when it comes to dealing with numbers, Excel is pretty much a standard.

More to the point, Excel is widely used by those dealing with finance and money. In other words, the Accounts department that's failed to pay your bill on time is probably using it. Make the accountants' lives easier, and they might return the favour...

Wouldn't it be great if you could provide your customers access to downloadable numerical data in the form of an Excel spreadsheet? The good news is that you can, with PEAR::Spreadsheet_Excel_Writer.

"Impossible!" you cry. "Excel uses some proprietary Microsoft file format. This can't be done!"

Yes, it can. Spreadsheet_Excel_Writer generates the "real thing", complete with Excel functions, formatting and all. No, we're not talking generating comma separated files here, or using the COM extension (or any other extension, for that matter). This is written in pure PHP and will work just as well from a UNIX-based Web server as it does from a Windows-based server -- and there's no need to bug your hosting provider. In short, PEAR::Spreadsheet_Excel_Writer, with additional magic from PEAR::OLE, "understands" Microsofts Excel file formats.

Let's take a moment to tip our hats to Xavier Noguer, who's done an amazing job in bringing all this to PHP, with help from Mika Tuupola for Spreadsheet_Excel_Writer.

Now, without further ado, and armed with full knowledge of PEAR's package manager, which you installed successfully last month (right?), let's start by downloading the libraries. Open up your command prompt and type:

$ pear install OLE
$ pear install Spreadsheet_Excel_Writer

That's it. We're ready for action!

Important Note! I used PEAR::OLE version 0.5 and PEAR::Spreadsheet_Excel_Writer version 0.7 for the examples in this article. Be warned that things may change with future releases.

Finding your Way Around

To kick things off, let's generate a very simple spreadsheet.

<?php
// Include PEAR::Spreadsheet_Excel_Writer
require_once "Spreadsheet/Excel/Writer.php";

// Create an instance
$xls =& new Spreadsheet_Excel_Writer();

// Send HTTP headers to tell the browser what's coming
$xls->send("test.xls");

// Add a worksheet to the file, returning an object to add data to
$sheet =& $xls->addWorksheet('Binary Count');

// Write some numbers
for ( $i=0;$i<11;$i++ ) {
 // Use PHP's decbin() function to convert integer to binary
 $sheet->write($i,0,decbin($i));
}

// Finish the spreadsheet, dumping it to the browser
$xls->close();
?>

Filename: example_1.php

Point your browser to the script and, assuming it knows about Excel (or OpenOffice Calc), up pops a spreadsheet, containing the numbers 0 to 10 as binary.

Storing Files

The spreadsheet is dynamically rendered in this case -- nothing is stored on the server. If you want to generate a file instead, you can cut out the processing required to generate a sheet that hasn't changed simply by passing the constructor a legal path and filename, and avoiding sending the HTTP headers, like so:

<?php
// Has a spreadsheet been created?
if ( !file_exists('sheets/binary.xls') ) {

 // Include PEAR::Spreadsheet_Excel_Writer
 require_once "Spreadsheet/Excel/Writer.php";
 
 // Create an instance, passing the filename to create
 $xls =& new Spreadsheet_Excel_Writer('sheets/binary.xls');
 
 // Add a worksheet to the file, returning an object to add data to
 $sheet =& $xls->addWorksheet('Binary Count');
 
 // Write some numbers
 for ( $i=0;$i<11;$i++ ) {
   // Use PHP's decbin() function to convert integer to binary
   $sheet->write($i,0,decbin($i));
 }
 
 // Finish the spreadsheet, dumping it to the browser
 $xls->close();
}
?>

Your spreadsheet is ready for download here

Filename: example_2.php

If you're using a UNIX-based system, remember to modify the permissions of the directory in which you're storing the spreadsheet, so PHP can write to it.

API Overview

OK, we're done with the basics. To get the most out of PEAR::Spreadsheet_Excel_Writer, you need to know a little bit more about the API, though. The API documentation that is available on the PEAR Website is out of date right now (it's grown a lot, it seems, since that version of the documentation was generated). Thankfully, the authors have, for the most part, added inline documentation to the code, so you can make your own API docs by downloading phpDocumentor and pointing it at a directory that contains all the
Spreadsheet_Excel_Writer source code. If you need help getting started with phpDocumentor (and will excuse the sales pitch), it's discussed in Volume 2: Applications of The PHP Anthology.

The main class that you'll always begin work with, Spreadsheet_Excel_Writer, represents the point of access to all other classes in the library. It provides two important factory methods (which are actually defined in the parent class Spreadsheet_Excel_Writer_Workbook:

• addWorksheet() - returns an instance of Spreadsheet_Excel_Writer_Worksheet. A large part of the work is done with instances of this class (as above), allowing you to write to the cells of a single sheet (an Excel spreadsheet is a Workbook containing one or more Worksheets).
• addFormat() - returns an instance of Spreadsheet_Excel_Writer_Format, which is used to add the visual formatting of cells in a Worksheet.

The library contains three other classes of which you should be aware, although you may not find yourself having to work with them directly:

• Spreadsheet_Excel_Writer_Validator makes it possible to add cell validation rules. Right now, there's basically no documentation for this class. It seems to be experimental code, so I'll be avoiding it here. Basically, it appears to provide the ability to perform basic validation on data entered into an Excel cell by an end user. More complex rules, such as validating against a list of cells, can be implemented by extending the class. The Spreadsheet_Excel_Writer_Workbook class provides the method addValidator() to create an instance of the validation while the Spreadsheet_Excel_Writer_Worksheet allows validators to be assigned to cells with the setValidation() method.
• Spreadsheet_Excel_Writer_Parser, which is a parser for Excel spreadsheet functions that allows you to check whether a function is valid Excel syntax. This may help you trap errors when adding functions to the spreadsheet within PHP.
• Finally, Spreadsheet_Excel_Writer_BIFFwriter is used to generate the Binary File Format for storing Excel files. If you're interested in Excel hacking, it may be interesting to study what it's doing but, otherwise, the library hides you from this class completely, so you don't need to worry about it.

(From: sitepoint)