POIUtility.cfc - "Too Many Fonts / Formats" Issue Fixed
Posted May 29, 2008 at 10:06 AM by Ben Nadel
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
@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.
Ok, the fix to the bleed problem is to declare a type of "@" or "General" for the data cell.
@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.
