I'm fairly sure that just about every reporting project ever done using ColdFusion probably started out with an Excel spreadsheet. This spreadsheet probably has lookup values all over the place. zero documentation. logic that couldn't have possibly been planned out in advance. Am I right? A nightmare.

Recently I've been staring down such a project. 36 columns. each calculated using some whacked out formula based on values in any of 30 worksheets in the same file. I had a choice: either re-create the master worksheet using a lot of queries/looping/conditional logic on the DB and in CF (seriously nasty) that would be a maintenance headache OR use the spreadsheet as it was given to me as a template.

I recalled reading Ben Nadel's blog. specifically this entry about creating Excel XML files and figured "what the hey?" I'll keep the source spreadsheet intact and just strap on my own backend data with the right formulas to pull and populate each row.

At the very least. it would save me a TON of time on layout. and styling with the added benefit of providing the logic right there in the file for the end user (a very skilled Excel jockey) to review. Within a matter of hours I had a sample working. enough to give me an idea of what the rest of the project would take. Passed on the estimate (that came in at half of what I had originally expected) to the client and plowed forward.

My coworker Matt had created a little codegen tool to spit out all of the supporting worksheets from our SQL Server database. All I had to do was stitch the first sheet (the summary one) on to this faux backend of supporting data sheets and recalibrate the formulas to look in the right sheet. row and column. For 90% of the data it was simple. for the remaining 10% I had to do some really fun SQL work but I'm still coming out ahead on the project.

Just to give you an idea of the end result. the generated XML file readable by Excel is 8MB! Saving the XML version in the ".xlsx" file format reduced the size to 1MB.

Thankfully. this thing should only need to be run once a year. I could have spent far more time generating just the lead sheet without the backend sheets - significantly more than the 30 seconds it takes the end user to copy just that sheet's values out to another Excel file for distribution.

The only reason I'm writing now is because I finished it. but in doing so exposed some holes in their existing (people-powered) logic for generating this data before. The likelihood of bad calculations just went way down. but in doing so the "fudge factor" disappeared too. I wonder how you build that back in...

Anyway. I highly recommend this method when you want to kick back a lot of data to Excel without using POI or HTML. The XML syntax isn't that hard to understand. but the tradeoff is definitely in file size. Weigh the cost of development time against pushing bits (and the frequency with which you push them). Come to think of it. I should serve it back as a zip file. That'll save some serious download time.

If you're stuck. read Ben's blog. He's done some great research on using XML with Excel and recently updated his POI CFC for doing the same stuff. He's really great about posting code samples too (unlike yours truly...the spreadsheet isn't something I can share).