Some Excellent Manipulation

An interesting little job came up yesterday which involved formatting data on an excel spreadsheet, we have some lists which have to look pretty but are edited frequently and we were having to spend a lot of time ensuring that these lists had a reliable and consistent format. Lots of ideas spring to mind for a job like this and the temptation is to go for yet another little database application but in this case it really felt like it would be overkill.

The solution which appears to have legs is to create a rather niffty excel parser using a couple of useful PHP add ons. For those of you who don't know what a parser is the definition on wikipedia is "the process of analyzing a sequence of tokens to determine its grammatical structure", in lay mans terms think of it a s a digester of documents. You push one in one end and it reads it, digests it and magically supplies a result, or in this case a completely reformatted spreadsheet. This will allow us to keep our data in a very simple unformatted spreadsheet, but by running them through our new system, we can have a nicely formatted consistent look ready to print in a click. Its all summed up nicely by another of my random dips into Google images, this time for the word "parse" see image right.

So if anyone ever has a need of such a beast or, more likely, if in 6 months time I have forgotten what I did and need a reference, the 2 places to go are PEAR for the excel spreadsheet writer add on and Source forge for the Excel Reader add on. When these are installed and working individually there is no reason why they cannot be used in the same PHP script in a push-me pull-you sort of fashion. It only took a couple of hours to get a basic system running and you can even allow the user to specify some parameters with their spreadsheet. So for example you pass a raw sheet of data, a title, a font and a relative font size and the parser running through the writer will apply sizing's and fonts in defined ways to different columns of data, it will even specify margins and printing areas so the document is completely ready to roll.

Keep tuning in for the definitive guide to installing Nagios on Centos without 'going postal' later this week.