This post originated from an RSS feed registered with PHP Buzz
by Alan Knowles.
Original Post: Generating excel, again.
Feed Title: Smoking toooo much PHP
Feed URL: http://www.akbkhome.com/blog.php/RSS.xml
Feed Description: More than just a blog :)
For a change, I've taken break from bashing internals, and got back to real work. (More on DBDO later this week hopefully)
One of my on-going projects, that has been dragging on longer than I would of liked is a shipping management application. I think it's mentioned in the archives, but for anyone who missed it, it is a mid sized XUL application which deals primarily with the management of a trading companies shipping requirements. I originally outsourced the main development, and have been tidying up and refining the code as we near final deployment (which as usual has taken longer than expected.)
This week I sat down and focused on the last major part of the project, reporting. Almost all the requirements for reporting include the ability to download an excel file of the data. So previously I had been making heavy use of PEAR's Spreadsheet_Excel_Writer. In using it, I had gone through various stages of evolution
Writing raw Excel_Writer code in PHP, This however becomes very tedious, is not amazingly readable, kind of breaks the seperation of display/computation. And tends to be less flexible over a long period of time.
Using a gnumeric as a template and using XML_Tree to merge data with it and output via Spreadsheet_Excel_Writer, again this helped in terms of enabling a simpler API for spreadsheet writing, and moving some of the layout/look and feel into the Gnumeric template. But the code for doing this was not quite as elegant as I would have liked.
Using Javascript to read HTML tables and create a CSV file, that is sent to the server, and back again as text/csv mimetype (forcing the browser to open it in excel/openoffice etc.). Which was nice from an architectural point of view, by lacked any formating.
And finally this week. Using javascript to generate a Spreadsheet_Excel_Writer specific XML file (by mixing a XML template file and the HTML content of the page), sending it to the server, and then letting PHP use the DOM extension and simple iteration with Spreadsheet_Excel_Writer to generate the page.
This weeks solution while not quite complete has a number of key advantages, some of which appeared after I started using it.
No display level code goes into the Action->Data manipulation stage (we just store the data ready for the template engine/ template to render)
It is possible to visualize the data prior to it ending up in the excel file.
hence debugging the data output and finding issues is a lot quicker
More code reuse,
the library for XML to Excel is simple to reuse,
the code for extracting the data from the html and generating XML is simple enough for copy & paste. and maybe possible to create a js library eventually.
It offers infinate possibilities for formating, and changing layout.
Less memory intensive, the data retrieval/storage and excel file create are broken up into two seperate processes.
The extended entry includes a few more details....
Previous Topic
