Skip to main content
Ben Nadel at InVision In Real Life (IRL) 2019 (Phoenix, AZ) with: Adam DiCarlo
Ben Nadel at InVision In Real Life (IRL) 2019 (Phoenix, AZ) with: Adam DiCarlo

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

By
Published in Comments (4)

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

6 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

15,880 Comments

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

15,880 Comments

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

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