Skip to main content
Ben Nadel at Endless Sunshine 2017 (Portland, OR) with: Bryan Stanley and Brian Blocker
Ben Nadel at Endless Sunshine 2017 (Portland, OR) with: Bryan Stanley Brian Blocker

No More New Fonts May Be Applied In This Workbook

By
Published in Comments (6)

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

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

15,880 Comments

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

15,880 Comments

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

1 Comments

@Ezra,

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.

http://support.microsoft.com/default.aspx?scid=kb;en-us;215573

Any guidance you can provide would be very helpful?

Thanks.
Manish

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel