No More New Fonts May Be Applied In This Workbook
Posted April 21, 2008 at 8:48 AM
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.
Comments (4) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Content Is Not Allowed In Prolog - ColdFusion XML And The Byte-Order-Mark (BOM)
You Cannot Submit Non-Rendered Forms
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.
Posted by Ezra Parker on Apr 21, 2008 at 12:59 PM
@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!
Posted by Ben Nadel on Apr 21, 2008 at 2:33 PM
@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).
Posted by Ben Nadel on Apr 21, 2008 at 3:28 PM
Why google will defeat yahoo?
find the answer! full article can be read on
http://webhostingnews2008.blogspot.com
Posted by jasaplus on May 2, 2008 at 9:35 PM
