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 NCDevCon 2011 (Raleigh, NC) with: Anthony Mineo

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

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

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

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

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

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.