Skip to main content
Ben Nadel at the New York ColdFusion User Group (Feb. 2008) with: Ray Camden
Ben Nadel at the New York ColdFusion User Group (Feb. 2008) with: Ray Camden

Creating Excel Files Faster With ColdFusion And POI Custom Tag Updates

By
Published in — Comments (21)

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.

Short link: https://bennadel.com/1305

Reader Comments

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

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

15,902 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.

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?

1 Comments

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?

15,902 Comments

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

2 Comments

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.

15,902 Comments

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

1 Comments

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!

1 Comments

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?

3 Comments

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?

Oh my chickens, this post is old!
I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
— Ben Nadel