Creating Excel Files Faster With ColdFusion And POI Custom Tag Updates

Posted August 1, 2008 at 8:38 AM

Tags: ColdFusion

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




Learning ColdFusion 9 - ColdFusion 9 tutorials, samples, examples, demos

Reader Comments

Aug 1, 2008 at 9:17 AM // reply »
32 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.


Aug 1, 2008 at 9:46 AM // reply »
6,515 Comments

Awesome stuff :)


Aug 1, 2008 at 1:55 PM // reply »
19 Comments

Nice work. A very useful utility, and now even better! :)


Aug 1, 2008 at 1:56 PM // reply »
6,515 Comments

@Adam,

Thanks man. Glad you like.


Aug 1, 2008 at 10:33 PM // reply »
18 Comments

can you add the related blog entries to the project page?


Aug 4, 2008 at 7:37 AM // reply »
2 Comments

Thanks for the information, nice site


Aug 11, 2008 at 12:54 PM // reply »
2 Comments

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.


Aug 11, 2008 at 7:38 PM // reply »
6,515 Comments

@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.


Aug 11, 2008 at 7:41 PM // reply »
6,515 Comments

@Plutarco,

I have updated the most recent download to include this fix. Again, thanks for the catch.


ali
Jul 3, 2009 at 1:51 AM // reply »
1 Comments

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?


Jul 3, 2009 at 8:33 AM // reply »
6,515 Comments

@Ali,

The complext to simple object error usually means that you have an incorrect variable reference somewhere.


Post Comment  |  Ask Ben

Recent Blog Comments
Nov 20, 2009 at 5:38 PM
Learning ColdFusion 8: CFImage Part I - Reading And Writing Images
Hi Ben, Great article. I've been looking around to see if ColdFusion image engine can programatically create the following "wrap around" effect: http://www.creativepro.com/article/photoshop-s-she ... read »
Nov 20, 2009 at 5:35 PM
Maintaining ColdFusion Sessions Across SMS Text Message Requests Without Cookies
@Dave: I talked to Gert he suggested: <cfhttp method="get" url="http://{some cf website}" result="stuff" addtoken="yes" /> Note the addition of cfhttp attribute addtoken. That should persist y ... read »
Nov 20, 2009 at 5:23 PM
Maintaining ColdFusion Sessions Across SMS Text Message Requests Without Cookies
@Todd, Ahh, gotcha, yeah that makes sense. ... read »
Nov 20, 2009 at 5:17 PM
Maintaining ColdFusion Sessions Across SMS Text Message Requests Without Cookies
Ben, sorry if I didn't make this clear. You can make it work like that if you want, just put <cfset session.foo = 1> (and <cfset application.foo = 1>) in your OnRequestStart() and it reve ... read »
Nov 20, 2009 at 5:07 PM
Maintaining ColdFusion Sessions Across SMS Text Message Requests Without Cookies
@Todd, I have seen tidbits about the way Railo handles session. I can understand that it lazy-loads sessions, but I also think that I might make some things more complicated. For example, often tim ... read »
Nov 20, 2009 at 4:53 PM
Maintaining ColdFusion Sessions Across SMS Text Message Requests Without Cookies
Ben, you can ramp up the security by turning on J2EE session which gives you a third set of numbers other than CFID/CFTOKEN. There's a reason why ACF put this in place (other than just session replic ... read »
Nov 20, 2009 at 4:52 PM
Maintaining ColdFusion Sessions Across SMS Text Message Requests Without Cookies
Case in point, Ben, you may not be aware of this, but in Railo - OnApplicationStart() & OnSessionStart() act differently than in ACF. ACF does: OnApplicationStart (1st hit) OnSessionStart (1st and e ... read »
Nov 20, 2009 at 4:46 PM
Maintaining ColdFusion Sessions Across SMS Text Message Requests Without Cookies
@Todd, That's understandable. I am not sure if this really leaves any more security holes than the fact that using old cookie-based CFID / CFTOKEN values will create a new session using the old CFI ... read »