POIUtility.cfc - "Too Many Fonts / Formats" Issue Fixed

Posted May 29, 2008 at 10:06 AM by Ben Nadel

Tags: ColdFusion

A number of people have brought this issue up to me (I would thank you, but I simply am not organized with my emails, sorry). Basically, because the POI Utility custom tags were creating new CellStyle objects for each cell in the Excel sheet, Microsoft Excel was going over the number of formats that it could use. Apparently there is some upper limit, like a thousand individual formats of something, before it can no longer apply styles to new cells.

To fix this issues, I am now caching the cell styles based on a Hash of all the properties in the CSS plus the date/number formatting for that cell. That way, two cells that have the same CSS and number format (for example) will actually share the same CellStyle instance. This slows down the process since each style has to have its CSS properties hashed in alphabetical order, but at least it fixes the "Too Many Fonts" issue. I will try to work on optimizing it a bit, but this should do for now.

The new code can be downloaded from the POIUtility.cfc Project page.




Reader Comments

Jim
Aug 6, 2008 at 10:19 AM // reply »
6 Comments
33 Points

Ben, Thanks for the update.

I've been playing with the June version and I just upgraded to the August version (CF7) and I noticed that the text portion of the excel sheet is now taking on the formating of the class title or header. In your example (write_custom_tags_template.cfm - the green back ground and white font bleed into the data rows that aren't formated by numeric or data. It worked fine in the June version.

Also, what are the possible date formats? I've tried mm/dd/yyyy and other and get an invalid format error?

thanks, Jim


Ben Nadel
Aug 6, 2008 at 10:25 AM // reply »
11,241 Comments
75,368 Points

@Jim,

That is odd. I haven't noticed this bleeding. Could you possibly take a screenshot of the Excel file opened and send it to me ... ben/bennadel/com.

Yeah, the date formats are a bit limited. If you open up the readme.txt file, you will see the list of possible date and number formats. There doesn't seem to be a good mm/dd/yyyy one unfortunately.


Jim
Aug 6, 2008 at 11:00 AM // reply »
6 Comments
33 Points

Ok, the fix to the bleed problem is to declare a type of "@" or "General" for the data cell.


Ben Nadel
Aug 6, 2008 at 11:03 AM // reply »
11,241 Comments
75,368 Points

@Jim,

That might be a fix, but something is going wrong in the code probably. I will dig around, see what I can find out.


Post A Comment

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.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:
Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>







  • Help Wanted - Find Your Next ColdFusion Job
Ben Nadel's Company - Epicenter Consulting Recent Blog Comments
Phillip Senn
May 22, 2013 at 12:44 PM
Ask Ben: Query Loop Inside CFScript Tags
In cf10, if you call a function that has: local.result = {}; local.result.msg = ""; local.svc = new query(); local.svc.setSQL("SELECT * FROM..."); local.obj = local.svc.exe ... read »
Dan G. Switzer, II
May 22, 2013 at 12:29 PM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Ben: What version of Java are you using? Also, did you test users.id to see what Java reports as the data type? I wonder if it's not a Java primitive data type, but getting returned as something ... read »
Ben Nadel
May 22, 2013 at 11:47 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Dana, Awesome - so it looks like this bug was fixed in ColdFusion 10. Thanks so much for double-checking that. ... read »
DanaK
May 22, 2013 at 11:37 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
When I c&p and run on cf10, I get: Selected User IDs: 1,4 User 1 selected: YES - YES User 2 selected: NO - NO User 3 selected: NO - NO User 4 selected: YES - YES User 5 selected: NO - ... read »
Ben Nadel
May 22, 2013 at 11:27 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Tom, Good thought, but no dice. Both of these still exhibit the same behavior: users.id[ users.currentRow ] users[ "id" ][ users.currentRow ] It's just something whacky happening with ... read »
Tom Eldredge
May 22, 2013 at 11:07 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
Could your problem be that "users.id" is actually an ARRAY, not a single value? Perhaps try it again with "users.id[1]" (I only have CF8 here at work). ... read »
mrsean2k
May 22, 2013 at 7:52 AM
Nested Views, Routing, And Deep Linking With AngularJS
Hi, Just a quick thank you. As it happens, for my own purposes, the pending ui-router work being done in native angular is likely the one I'll adopt, but your exploration, code and documentation of ... read »
Animesh Dutta
May 22, 2013 at 4:43 AM
How Do You Use The ColdFusion CFParam Tag?
'<cfparam>' or 'isDefined()and <cfset>' performs the same task.Is there any difference? ... read »
InVision App - Prototyping Made Beautiful With Prototyping Tools