Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at the New York ColdFusion User Group (Jul. 2009) with: Sean Schroeder and Matt Levine
Ben Nadel at the New York ColdFusion User Group (Jul. 2009) with: Sean Schroeder@brinteractive ) and Matt Levine@brinteractive )

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 ) />

<cfset LOCAL.BoldWeight = JavaCast( "short", 400 ) />


<cfif (
Len( VARIABLES.Instance.CSS[ "color" ] ) AND
StructKeyExists( VARIABLES.POIColors, VARIABLES.Instance.CSS[ "color" ] )

<cfset LOCAL.Color =
JavaCast( "short", CreateObject(
"org.apache.poi.hssf.util.HSSFColor$#UCase( VARIABLES.Instance.CSS[ 'color' ] )#"
).GetIndex() ) />
<cfset LOCAL.Color = JavaCast( "short", 0 ) />

<cfif Len( VARIABLES.Instance.CSS[ "font-family" ] )>

<cfset LOCAL.FontName =
JavaCast( "string", VARIABLES.Instance.CSS[ "font-family" ] ) />


<cfswitch expression="#VARIABLES.Instance.CSS[ 'font-style' ]#">

<cfcase value="italic">
<cfset LOCAL.Italic = JavaCast( "boolean", true ) />

<cfset LOCAL.Italic = JavaCast( "boolean", false ) />


<cfset LOCAL.MatchingFont = ARGUMENTS.WorkBook.findFont(
JavaCast( "short", 200 ),
JavaCast( "boolean", false ),
JavaCast( "short", 0 ),
JavaCast( "byte", 0 )
) />

<cfif StructKeyExists(LOCAL, "MatchingFont")>
<cfset LOCAL.Font = LOCAL.MatchingFont />
<cfset LOCAL.Font = ARGUMENTS.WorkBook.CreateFont() />

I hope that's helpful.


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.



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


I am having the same issue in my xls (2010) i.e. getting an error message saying "No more fonts may be applied".

Do I need to run a Macro/Module with the code above to resolve the issue?

I have tried everything from Microsoft's website (changed registry, ran a macro to turn-off Autoscaling, even deleted all charts, etc.) but still keep getting this error.;en-us;215573

Any guidance you can provide would be very helpful?