Creating Excel Files Faster With ColdFusion And POI Custom Tag Updates
Posted August 1, 2008 at 8:38 AM by Ben Nadel
I was using my POI Utility ColdFusion custom tags to create true Excel reports for a client. Some of the reports that they are running were really long (thousands of rows) and the reports were just dying. I had to do something, so I went into the POI custom tags and totally reengineered the way they work. Rather than creating a new instance of the CSSRule.cfc ColdFusion component for every single cell.cfm, I am now creating only one instance of it and caching it in the document.cfm tag. Instead of it housing the CSS properties for a cell, I rebuilt it to be merely act as a utility object to which CSS "struct"s are passed. The drastic decrease in CreateObject() calls was enough to give a good performance boost. In addition to that, I have also cut out as many method calls as I possibly could. On large reports, even calling a simple method could add seconds to the processing time. I am now relying on StructAppend() to take care of must of the cascading logic of "cascading" style sheets; this has also proved to be a good performance boost.
Since I was in there, I have fixed the last few outstanding ColdFusion MX7 compatibility issues. I have also moved the above performance updates to the CFMX 7 version as well. I hope that doesn't create new CFMX 7 bugs (I ran it on a CF8 box and at least it didn't error out for syntax reasons).
The updated code is available from my POIUtility.cfc Project page.
Reader Comments
Just 15 minutes ago a client asked me for just this thing due asap. 15 minutes later it's already done and ready for their use. thanks Ben! Good stuff.
Awesome stuff :)
Nice work. A very useful utility, and now even better! :)
@Adam,
Thanks man. Glad you like.
can you add the related blog entries to the project page?
Thanks for the information, nice site
Hi Ben,
There is a minor bug in CFMX7.
Invalid token '{' found on line 144 at column 48.
File: Document.cfm
Original Line 144:
<cfset VARIABLES.CellAliases = {} />
Change to:
<cfset VARIABLES.CellAliases = StructNew() />
The perfomance is very good !! :)
Thanks Ben.
@Plutarco,
Oh man, good catch. The problem with running this on a ColdFusion 8 box is that I can only guess that i have caught all the "Errors". Thanks for pointing this out.
@Plutarco,
I have updated the most recent download to include this fix. Again, thanks for the catch.
i am trying to export large data from sql query to excel sheet using coldfusion but i am having full of memory error so i used another way using COM objects but i am having the error that cannot open the excell and activate it because complex object cannot be converted to simple object. any help please?
@Ali,
The complext to simple object error usually means that you have an incorrect variable reference somewhere.
I have a client that can not upload an excel file from a Mac... but when he uploads from a PC it runs fine... Do you know anything about this from before?
@Summer,
Are you talking about the same exact same Excel files? Or do you just mean a file created on Mac vs. a file created on a PC?
@Ben
I believe the file is created on Mac... i will confirm and get back.
Happy Holidays/New year!!!
the File is originally created on MAC and uploaded from the same.
also here are some more details... the browsers used on MAC are Firefox and Safari and the Excel verion is either 97 or 95.
@Summer,
Hmm, I am not sure. If this file works on Windows and not on Macs, I am not sure there is anything that I can do.
When creating an Excel file, is there any way to prompt the user as to where to save the file?
This works great and that would REALLY be the icing on the cake.
Thanks!
Thanks so much this great POI CFC. It's been extremely useful for exporting data in a usable format.
I've been using custom tags to insert data into an excel template but I've found that you aren't able to edit a particular cell without overwriting the entire row.
Is it possible to leave content in some cells in a row intact while inserting data into others?
