Ben Nadel © 2008.
All content is the property of Ben Nadel and Kinky Solutions.
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.
Comments (4) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Ask Ben: Dynamically Executing ColdFusion Application.cfc Instances
HotFix For "The ColdFusion Mail Spool Encountered An Invalid Spool File In The Spool Directory"
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
Posted by Jim Papaleo on Aug 6, 2008 at 10:19 AM
@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.
Posted by Ben Nadel on Aug 6, 2008 at 10:25 AM
Ok, the fix to the bleed problem is to declare a type of "@" or "General" for the data cell.
Posted by Jim Papaleo on Aug 6, 2008 at 11:00 AM
@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.
Posted by Ben Nadel on Aug 6, 2008 at 11:03 AM