Pondering Output to Excel

Lots of you have been asking for a print output to Excel (this has come up in discussions at user groups, in e-mail threads, and in the comments to my blog entry regarding RTF files). I've been giving this one some thought, and honestly, I don't believe that Excel makes sense as a "print" format. I am not saying that I don't see a need for programmatic Excel spreadsheet generation, I just don't know how a spreadsheet would map to a printable output.

As I see it (and as I've been demonstrating for the past three weeks), there are two distinct needs when it comes to printing from within ColdFusion apps:

1) Printing web pages: essentially compensating for the pathetic printing support in most browsers, allowing for content (text, images, alignment, tables, etc.) to be printed so that pages and breaks and wrapping all work properly. Being able to create PDF, FlashPaper, or RTF versions of web pages makes lots of sense. The intent is to generate something that is printable, and those all are. But this type of printing makes absolutely no sense as an Excel spreadsheet, there is no way to map freeform content to Excel rows and cells in any way that would actually be useful or relevant.

2) Reporting: data driven template based reports that display data in a structured format, supporting bands, running calculations, charting, and more. Supporting the same print formats makes sense too. Unlike the printing scenario described above, the type of data presented in reports is a bit more structured and so it may indeed be a fit for Excel spreadsheets. But, I still don't quite understand how it could all work. Spreadsheets are 2 dimensional data views (essentially data grids, with a possible 3rd dimension if tabs are used), and reports really are not grids at all. After all, if you were to indent a total in your report, what spreadsheet cell would you want it in? And if you used a sum function would you expect the value or an Excel sum function to be embedded? And if you used nested bands with headers and footers, what would those map to? What would rows line up to? There is way too much potential ambiguity here, and the reality of it would likely be that the generated spreadsheet would never be exactly what you intended. To generate accurate spreadsheet content you would need to either a) explicitly put content in specific cells (using APIs for explicit cell manipulation), or b) be able to generate a grid type view with enough control so that it may be intelligently mapped to cells. And honestly, both seem like a lot of trouble for not much upside. Especially as you can already use other techniques to generate Excel content, including generating CSV content, and generating HTML tables which Excel can process properly (mapping HTML table cells to Excel spreadsheet cells, once you have done the work of determining exactly what to place in each cell, Excel can handle the importing quite nicely).

I may be way off base here, but it seems that the use case for Excel is very different from that of PDF or even RTF. The latter are great as print formats, I buy that. But Excel is not a print format at all, it is a tool used for data analysis, and that may result in charting and reporting, but not necessarily so. Or, put differently, data is not sent to Excel merely to be able to print it, that would make little sense. Data is sent to Excel to be manipulated, to perform calculations, to generate charts, and more. This may ultimately be printed, but the primary objective is the data manipulation that Excel offers, not the fact that it can print.

As such, I think that if ColdFusion were to generate Excel content then the interface to it should not be printing or reporting at all. It sounds like users want an "export" feature, a way to take a database query and export it to some other useful format, like Excel (or even XML). I can see a tag like this:

Or maybe functions like:

Both interfaces make sense (and have pros and cons), and both would allow you to simply generate Excel content (perhaps with column names as the top row, and then data in specific cells). Maybe we could even allow additional functionality to better manipulate specific cells (to set values, add formatting or functions, and more), we'd need to think that one through in more detail.

The more I think of it, the more I agree that Excel spreadsheet generation makes a lot of sense, and the more I feel that the use case and usage patterns require a slightly different way of thinking.

Your thoughts?