Creating Excel Files Faster With ColdFusion And POI Custom Tag Updates
Posted August 1, 2008 at 8:38 AM
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.
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Newer Post
iPhone Wallpaper Template For Fireworks / Photoshop (Saucy)
Older Post
OOPhoto - Embrace Failure Or Optimize My Domain?
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.
