ColdFusion Excel Output Misses Mark
Friday, April 15, 2005 at 11:39AM 7 Comments I've been messing around with the ColdFusion MX 7 Report Builder, and it continues to blow me away. Macromedia knocked it out of the park with this feature, and it has the potential to significantly enhance the utility of our Web applications.
However, I am disappointed with the Excel output feature. Macromedia got a lot of pressure from beta testers to include the Microsoft Excel output format for reports; in fact, some other nice enhancements to ColdFusion were apparently tabled in order to provide Excel as an output option.
But the implementation of the Excel output feature completely misses the mark. Not only are charts and images unavailable to the Excel files, but when you open the results of a report output as Excel, you get something like this:
It ain't that pretty at all. All these scrunched up rows and columns approximate the same nicely printed output that you get when you output to PDF or FlashPaper. But why are developers asked to export to Excel in the first place? It's not strictly for printing; it's because clients need to manipulate and do calculations on the data in Excel, which is not easy with an Excel file that looks like this.
There have been and continue to be other options for exporting query data to Excel in ColdFusion. I've used cfcontent to deliver standard HTML and ColdFusion as Excel, and it's worked fine, since you have granular control of the contents and formats of each table cell (even though Excel doesn't recognize CSS and this method relies on the client having a fairly recent version of Excel).
It's clear to me that developers wanted Macromedia to provide a consistent, easy-to-implement method to output query data to Excel to allow users to manipulate the data. With the implementation provided by ColdFusion MX 7, you just get a mess of a file that happens to print well.
Reader Comments (7)
I ran into the same issue. I have to make 2 different reports (with the same query): 1 for PDF printing and 1 for excel exporting!!! Is there any better solution?
Not as far as I know. I've come to think of exporting to Excel as an altogether different function from reporting. The query2excel Custom Tag is pretty good for taking a query object and sending it out as an Excel file. At the CFReporting presentation at MAX this year, there was some acknowledgment from Macromedia that the Excel output leaves much to be desired. In the meantime, we're stuck with creating one report for printing, and another function entirely for exporting to Excel.
How do I format CFMX7 report with alternative row colors? I don't think we can set background color in DETAIL section?
Tuyen - You can't set the background color for the detail section, but you can achieve the same effect using a shape object. In the detail section, use the rectangle tool to draw a rectangle that spans the width of the row and will act as the row background. Set the rectangle's color to what the alternating row color should be (#EEEEEE or whatever).
Then, set the rectangle's PrintWhen property to an expression like query.currentRow mod 2 eq 0 (it may be report.currentRow, I can't remember), and send the rectangle to back. This will print the rectangle as the background color for alternating rows. This only works if one of your row colors is white, which it usually is. Hope this helps.
THANK YOU! It worked perfectly. I couldn't do this feature in Crystal Report 10!
I am interested in what was said about alternating row colours. I'd like to be able to dynamically adjust the colour of the rectangle. Is this possible? Thanks for your help.
Michael - The rectangle's color is a property, which I believe can be set as a ColdFusion expression; if so, its value could be dynamic.
At a minimum, you could stack several rectangles of differing colors on top of each other, using the PrintWhen property of each to determine which color to display based on a dynamic expression.