In many companies, business logic resides in Excel. This business logic is sometimes created by business analysts and sometimes by business users who want to automate parts of their everyday job using Excel. [...] Did you know you can use PHPExcel to take advantage of the Excel-based business logic without having to worry about duplicate business logic?

He creates a quick example of a script that can take in an Excal file and pull it into a PHPExcel object, ready for manipulation. He fills in values for the already defined fields (like "carColor" or "leatherSeats") and uses the getCalculatedValue method to perform the action on the cell. The output is dropped into a variable that can be echoed out or used later on in the PHP script.

When it comes to playing nice with data in different formats, PHP's pedigree is hard to beat. Not only does the language make it a breeze to deal with SQL result sets and XML files, but it comes with extensions to deal with formats as diverse as Ogg/Vorbis audio files, ZIP archives and EXIF headers. So it should come as no surprise that PHP can also read and write Microsoft Excel spreadsheets, albeit with a little help from PEAR.

After grabbing the different parts needed (the PHP-ExcelReader package and the Spreadsheet_Excel_Writer PEAR package, he shows how to create a simple spreadsheet with just numeric information in it. For something a bit more interesting, he goes the other way and shows spreadsheet data as an HTML table.

Other examples included as well are things like: pushing spreadsheet data into a database, working with formulas and styling it to your liking.

Sifting through the repository is also a challenge; a basic category system is in place, but it's hard to tell what you want when you don't know what's available. Here are some gems from the PEAR repository that you could really find useful.

The five that made his list are:

• Service_Akismet
• HTTP_Request
• Archive_Tar
• Spreadsheet_Excel_Writer
• XML_Parser

Given the prevelance of PHP, Excel and PDF it's fortunate there exists class libraries for the generation of PDF documents and Excel spreadsheets using PHP.

They start with the PDF generation and show the installation process for ClibPDF and an example of creating a document on the fly. Their example sets up a title, some content text and a table of information. Creating the Excel spreadsheet is even simpler with the Spreadsheet_Excel_Writer - installation via a PEAR channel and defining the worksheet, fonts and content are quick and easy function calls.

There is a nice tool for reading Microsoft Excel files called PHPExcelReader. It works with .xls files up to Excel version 2003, which are based on the BIFF format (later versions use OOXML). It is written in native PHP and does not require any third-party libraries or the MS Office package.

He includes an example of it in use - a loop that runs through the cells of a spreadsheet and outputs each of them (going across the row via an inner for loop as well).

I'm on this project at work where we need to create an excel file and send it via email or ftp to a recipient. The normal answer to this is to create a comma separated file and name it .xls and excel will work it out. However, that only works if you open it directly through an http request and are able to set the http headers correctly.

To get around these limitations, he found two solutions (thanks Google!) in PEAR's Spreadsheet_Excel_Writer and an MS-Excel Stream Handler class. He includes code that gives an example of another solution, one that packs data into a binary string and returns it as a part of the Excel document.

There are two main ways to build Excel, Word, and PowerPoint documents using PHP. The first is by using the COM library (only if you are using a Windows server) and the other is by using a more standardized approach such as HTML or CSV.

All three examples use the COM method and make basic structures to save out. They also include a screenshot (and description) showing where you can find the list of functions you can call to the COM object for each application.

He uses the Spreadsheet_Excel_Writer package to create the file without the need for getting COM in the mix. The package makes it simple to take in a dataset and write it out to a workbook - complete with column headers.

He shows two ways to save the file as well - creating a local file (on the web server) and pushing it out to the browser.

It's a simple introduction, but it's a starting place - especially for those with a project plan that requires it.]]> Tue, 28 Mar 2006 07:01:46 -0600