No More New Fonts May Be Applied In This Workbook
Posted April 21, 2008 at 8:48 AM by Ben Nadel
Matthew Abbott raised this issue to me a while back and I just haven't had enough time to look into it. Apparently, with my POI ColdFusion Custom Tags, when you have a Microsoft Excel workbook of any legitimate size, Microsoft Excel throws this error when you open the resultant file:
No More New Fonts May Be Applied In This Workbook
This goes to show you that when you are building functionality it is important to mimic real world use cases; I never got this error because my test files were always very small. This morning, when adding some Template functionality, I was using several hundred rows of test data and finally got the error that Matthew was describing. According to the Microsoft documentation, this problem is being caused because I am applying formatting to each individual cell:
This problem occurs when the workbook contains more than approximately 4,000 different combinations of cell formats. A combination is defined as a unique set of formatting elements that are applied to a cell. A combination includes all font formatting (for example: typeface, font size, italic, bold, and underline), borders (for example: location, weight, and color), cell patterns, number formatting, alignment, and cell protection.
NOTE: If two or more cells share exactly the same formatting, they use one formatting combination. However, if there are any differences in formatting between the cells, each cell uses a different combination.
Right now, I am applying styles to every cell, regardless of whether or not any additional styles (over the default style) have been set. Clearly, this needs to be rethought. I need to apply styles to the Excel cells in more of a shared framework - I need to be able to create global Cell Style objects that are then shared by more cells. This will be a bit of complicated task. I will probably have to create global Cell Style objects based on the class tags, then only override those when absolutely necessary. This means moving my date/number formatting into the CSS declaration of the classes.... Hopefully, more to come soon.
Reader Comments
Hi, Ben. I had just downloaded your POI tags this last week to try out (very cool, by the way), and ran into this issue. I believe I have figured out a workaround, which is to use the findFont method to see if the formatting you wish to use already exists, instead of calling CreateFont each time.
Here is some quick-and-dirty code I slapped into POICSSRule.cfc to get it working, which I haven't had time to refactor -- replace lines 86-87 with (I'm escaping angle brackets, so I hope this comes through OK):
<cfswitch expression="#VARIABLES.Instance.CSS[ 'font-weight' ]#">
<cfcase value="bold,600,700,800,900" delimiters=",">
<cfset LOCAL.BoldWeight = JavaCast( "short", 700 ) />
</cfcase>
<cfdefaultcase>
<cfset LOCAL.BoldWeight = JavaCast( "short", 400 ) />
</cfdefaultcase>
</cfswitch>
<cfif (
Len( VARIABLES.Instance.CSS[ "color" ] ) AND
StructKeyExists( VARIABLES.POIColors, VARIABLES.Instance.CSS[ "color" ] )
)>
<cfset LOCAL.Color =
JavaCast( "short", CreateObject(
"java",
"org.apache.poi.hssf.util.HSSFColor$#UCase( VARIABLES.Instance.CSS[ 'color' ] )#"
).GetIndex() ) />
<cfelse>
<cfset LOCAL.Color = JavaCast( "short", 0 ) />
</cfif>
<cfif Len( VARIABLES.Instance.CSS[ "font-family" ] )>
<cfset LOCAL.FontName =
JavaCast( "string", VARIABLES.Instance.CSS[ "font-family" ] ) />
</cfif>
<cfswitch expression="#VARIABLES.Instance.CSS[ 'font-style' ]#">
<cfcase value="italic">
<cfset LOCAL.Italic = JavaCast( "boolean", true ) />
</cfcase>
<cfdefaultcase>
<cfset LOCAL.Italic = JavaCast( "boolean", false ) />
</cfdefaultcase>
</cfswitch>
<cfset LOCAL.MatchingFont = ARGUMENTS.WorkBook.findFont(
LOCAL.BoldWeight,
LOCAL.Color,
JavaCast( "short", 200 ),
LOCAL.FontName,
LOCAL.Italic,
JavaCast( "boolean", false ),
JavaCast( "short", 0 ),
JavaCast( "byte", 0 )
) />
<cfif StructKeyExists(LOCAL, "MatchingFont")>
<cfset LOCAL.Font = LOCAL.MatchingFont />
<cfelse>
<cfset LOCAL.Font = ARGUMENTS.WorkBook.CreateFont() />
</cfif>
I hope that's helpful.
@Ezra,
That looks cool; however, my concern is that from the Microsoft documentation, it looked like this problem extended beyond just actual Font usage into "Style" usage including things like borders.
I am gonna try to re-evaluate how styles are being use as a whole. Then, if that doesn't go so smoothly, I will look into implementing your workout.
Thanks!
@Ezra,
I may have been too quick to jump to conclusions. I just took out the FONT stuff (commented it out) but left in all the cell styles and I am not getting any errors.
Looks like you're the man now, dog (my best Sean Connery impression).
Why google will defeat yahoo?
find the answer! full article can be read on
http://webhostingnews2008.blogspot.com
@All,
This has been fixed this morning. You can grab the latest download here:
http://www.bennadel.com/projects/poi-utility.htm
I am now caching all the cell styles based on the Hash of all their CSS rules and combo of date/number format. Thanks for your advices.
