Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rocks (SOTR) 2011 (Edinburgh) with:

Creating Excel Files Faster With ColdFusion And POI Custom Tag Updates

By Ben Nadel on
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.




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.

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

@Plutarco,

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

Reply to this Comment

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?

Reply to this Comment

@Ali,

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

Reply to this Comment

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?

Reply to this Comment

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

Reply to this Comment

@Ben

I believe the file is created on Mac... i will confirm and get back.

Happy Holidays/New year!!!

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

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!

Reply to this Comment

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?

Reply to this Comment

Hi Ben, great tag..been using in projects for a long time now. However Im migrating some sites onto CF10 and Im getting a not found error for org.apache.poi.poifs.filesystem.POIFSFileSystem. Has anyone else had the same issues?

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.