<cfspreadsheet> and "...maximum number of fonts was exceeded" error in Excel 2007

[Chris_Peters]

A client and I are both experiencing the same error message upon opening a spreadsheet generated by ColdFusion 9.0.0 (Windows deployment). The workbook generated is fairly complex, with a summary tab and about 25 other tabs.

Here is the exact error message that pops up in Excel 2003 and 2007:

Some text formatting may have changed in this file because the maximum number of fonts was exceeded. It may help to close other documents and try again.

This only happens when the amount of data stored in the document is larger (though I can't tell you exactly how large the document needs to be in order to start triggering this error). I am not doing any formatting with fonts, which is why this error is confusing to me. There are a couple columns that I am formatting into different data formats on each tab, things like this:

<cffunction name="formatEventSheet" hint="Formats a given row in a spreadsheet and returns the spreadsheet object.">
    <cfargument name="spreadsheet" hint="Spreadsheet object to manipulate. Active sheet must be set to sheet to modify.">
    <cfset var loc = StructNew()>
    <!--- Currency formatting --->
    <cfset loc.currencyFormat = StructNew()>
    <cfset loc.currencyFormat.dataFormat = "($##,####0.00);($##,####0.00)">
    <cfset SpreadsheetFormatColumn(arguments.spreadsheet, loc.currencyFormat, 5)>
    <cfreturn arguments.spreadsheet>
</cffunction>

<cffunction name="formatEventSummarySheet" hint="Formats a given row in a spreadsheet and returns the spreadsheet object.">
    <cfargument name="spreadsheet" hint="Spreadsheet object to reference. Active sheet must be set to sheet to modify.">
    <cfset var loc = StructNew()>
    <!--- Currency formatting --->
    <cfset loc.currencyFormat = StructNew()>
    <cfset loc.currencyFormat.dataFormat = "($##,####0.00);($##,####0.00)">
    <cfset SpreadsheetFormatColumn(arguments.spreadsheet, loc.currencyFormat, 4)>
    <cfset SpreadsheetFormatColumn(arguments.spreadsheet, loc.currencyFormat, 6)>
    <cfset SpreadsheetFormatColumn(arguments.spreadsheet, loc.currencyFormat, 8)>
    <cfset SpreadsheetFormatColumn(arguments.spreadsheet, loc.currencyFormat, 10)>
    <cfset SpreadsheetFormatColumn(arguments.spreadsheet, loc.currencyFormat, 12)>
    <cfset SpreadsheetFormatColumn(arguments.spreadsheet, loc.currencyFormat, 14)>
    <cfset SpreadsheetFormatColumn(arguments.spreadsheet, loc.currencyFormat, 16)>
    <cfreturn arguments.spreadsheet>
</cffunction>

I can post some more code if need be (there is a lot of it), but I was wondering if anyone has run across this in general and what they did to fix it.

Message was edited by: Chris Peters - Added syntax highlighting.

[Deb7 · Accepted solution]

Here's some information taken from a post on the experts-exchange site. It sounds like it might be applicable in your case...

This error is generated when you have maxed out the internal formatting tables.

Here are some notes on minimizing the use of formatting table entries...

A  common misconception is that formatting any range of contiguous cells  at one time results in smaller workbooks. This is, for the most part,  not true. The only time Excel conserves  workbook size is when a column of cells is formatted from a starting  cell to the bottom of the worksheet. The starting cell can be on any row  but the last cell must be on the last row of the worksheet. Formatting  multiple contiguous columns to the bottom of the worksheet produces the  same result as formatting each column individually.

Note that  when formatting horizontal borders in a column, do not set the bottom  border as doing so will require as much file size as if each cell in the  column were formatted separately. Only set the inside horizontal  border.

A quick test illustrates this behavior. Create two new  workbooks. In the first, select cells A2:A65536, set the background  color, and save. In the second, select cells A2:A65535, set the  background color, and save. Using Windows File Explorer, look at the  files sizes of the two workbooks. Note that the first workbook is about  12 KB in size while the second is over 2 MB.

Formatting columns  of cells in this manner has another benefit: the used range is  unaffected. In other words, if cells A2:A65535 are formatted then the  used range is set to A2:A65525. However, if cells A2:A65536 are  formatted, the used range is unaffected. Note that this is not true in  the row or horizontal direction. In other words, a row formatted to the  rightmost column IV will reset the used range to include column IV. Also  note that this was fixed in Excel 2003 and rows behave like columns in 2003 with regard to the used range.

Another  interesting aspect of formatting columns to the bottom of the worksheet  is that the workbook file size benefit realized is not adversely  affected by reformatting individual cells within the larger range. For  example, if cells A2:A65536 are formatted one way and cell A1000 is then  formatted another way, the workbook's file size continues to be small.  This is even true if cell A65536 is cleared of all formatting. Note that  the unformatted cells consume space because they are exceptions to the  first formatting and so benefits realized by formatting to the end of  the worksheet are eroded as more and more cells are set to other formats  or cleared of formats.

[Chris_Peters]

Thanks so much, Deb. I will look into correcting my code based on this answer. I figured that if I were setting the formatting to the entire column that it would be a less expensive operation, but I guess I was wrong!

[Guest]

We're having the same issue here as we're trying to use spreadsheetFormatColumns() - Chris, have you had any luck? If we do, we'll post here.

[Chris_Peters]

This appears to have resolved the issue. Thanks, Deb!

Note to CFers out there. In order to fix this issue, we upgraded to CF 9.0.1 (which is free to anyone with a license for 9.0.0). Then I used the SpreadsheetFormatCellRange() function to format just the cells that needed formatted.

Your mileage may vary depending on how much data needs to go in the spreadsheet. For example, one needed to be pretty large in an app I was doing, and I had to revert to passing back an HTML table with a MIME type of application/excel. It looks like these spreadsheet functions and tags have a far way to go before they can cover "serious" data crunching.