Office Reports in PHP
Every so often you get posed with a problem needing a quick fix. This was the case in a project we did for the Iowa Department of Agriculture and Land Stewardship when they needed us to produce Office-based reports...you know, spreadsheets, word documents, etc using PHP. Some of you Windows users probably have access to fancy COM libraries and .NET equivalents that you paid for and could have used but we were running PHP under Linux so no COM and the .NET implementations at the time weren't Mono compatible or only worked with Mono under Windows. So what is a brother to do? Use Flexy!
If you are a PHP developer and you don't know what Flexy is, it's probably because you either a) don't feel template engines have a place in PHP or b) you use an alternative to Flexy such as Smarty. For those of you who fit into category a) you can safely tune out now. Your mind has been forever altered and you are probably incapable of being convinced otherwise. However for those of you in the b) camp, there is a slick way to employ your template library.
What makes all this possible, however, is the fact that documents created in Office 2003 and later can be saved in XML format. I'll spare you the format wars between Open Document and Office Open XML, it's an important battle especially for us in the public sector but I'll save that for another day of blogging.
So armed with a version of Office that can save documents in XML format you do the following:
- Create a template of your document. For just just jam in dummied up data.
- Save your document in .xml format.
- Modify the XML as needed. For example if you have an Excel spreadsheet that loops through data then inject your {for:} Flexy-clause. Another example would be to replace today's date with something like {todaysDate} or {getCurrentDate()}. Don't forget to save your work.
- Create your PHP file that instantiates your template engine, give it the template you created in the step above and then perform the method/member substitutions as needed.
Done.
Well sort of. The above covers the conceptual part of generating the document when, if you know Flexy or Smarty you should get. But you do, after all, have to output the XML in a format that the browser will recognize as a word document. That small bit of code follows:
// Prepare your data. This is specific to how your template engine works. Below is a sample using
// flexy
$someLetter = new StdClass();
$someLetter->currentDate = "1/22/2008";
$someLetter->to = "Tony Bibbs"
$someLetter->streetAddress = "111 1st St"
$someLetter->city = "Des Moines"
$someLetter->state = "Iowa"
$someLetter->zip = "50323"
// Tell the browser what's about to be sent over
header("Pragma: public");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false);
header("Content-Transfer-Encoding: binary");
header("Content-Type: application/msword");
header("Content-Disposition: attachment; filename=someLetter.doc");
// Compile the template from Flexy markup (thtml) to PHP code
$this->flexyHandle->compile('letters'. DIRECTORY_SEPARATOR. 'SomeLetter.thtml');
// Give the template engine our letter data so it can inject the data and output to browser
$this->flexyHandle->outputObject($someLetter);
There, now you are done. I don't pretend this is at all original. I'm sure somebody out there in PHP-land has been doing something similar but what I loved about this solution at the time we came up with it is that is made use of our pre-existing template engine (read: no new technology to learn) and that it was relatively fast. While this is far from a reporting tool, it shows you can create fairly complex reports in a short amount of time without needing to drop any loot on something like ActiveReports, Microsoft's SQL Reporting Services or Crystal Reports. Don't get me wrong, a quality reporting tool has it's place (I should put a plugin for open source alternatives like Birt) but as I always convey to the developers on my team try not to produce a million dollar fix to a fifty-cent problem. In our case doing so saves tax dollars.
