Creating Excel Files With Coldfusion
Today I had to generate a raw dump of data into an Excel file. Nothing new here. However, while coding/testing, I was frustrated by the fact I had little control on how my file looked. Normally, when I have to create an Excel file with Coldfusion, I just make up a table in HTML and send it to Microsoft with the handy little cfcontent tag. However, there's only so much you can do with Excel's limited CSS support. Looking for a better solution, I stumbled upon these neat little articles:
Using Apache's POI-HSSF Java library, you can create native Excel binary data and access many sheet and cell properties you wouldn't have dreamed possible using the old method. Now, things such as consistent cell styles, dependable cell masks, multiple-sheet workbooks, freezed panes, images and graphics are within reach.
There is a downside to this approach, however. As you will see in the articles, you will often need to use several method calls on a same cell to achieve the desired results. This can lead to redundancy and bloated code. That's why I'm currently working on a wrapper for the library. I won't go into too many details just yet, but it will make editing (and even reading!) excel files much easier. I'll post updates as I go, so stay tuned!
Who changed the channel?
#1. Open Excel and design a "template" spreadsheet formatted exactly how I want it, including headers and footers, colors and shading, fonts, etc. When done, save the spreadsheet in Excel's XML format.
#2. I can now take that XML file and wrap CFML around it, putting the appropriate MIME header tags in place:
<cfcontent TYPE="application/msexcel"/>
<cfheader name="content-disposition" value="attachment;filename=sales_review.xls"/>
<cfheader name="Pragma" value="no-cache"/>
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
#3. Search the file for the tag "ss:ExpandedRowCount" and delete it and its value. (Example: ss:ExpandedRowCount="20") This just stores the number of rows in the table, something we don't want to have to mess with. By removing it Excel will automatically calculate the rows upon loading.
#4. Replace the fake data you used when making the template with cfoutput results. Put a cfquery loop or cfloop around the appropriate row(s).
Done. Perfectly formatted dynamic Excel spreadsheets using code us CF-heads can easily grok. Note that this also works great for putting multiple worksheets into a single Excel workbook.
It looks like a really nice and easy way to have your report fit within a template, but (from what I understand) I feel like you lose a bit of flexibility (which might not be a problem, depending on the project ;) ).
Where's the fun in that? :P
I'll definitely try to put some time aside later today to look at yours and Ben's solutions. Thanks for the tip!