Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2014 (Bloomington, MN) with:

No More New Fonts May Be Applied In This Workbook

By Ben Nadel on
Tags: ColdFusion

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.

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.