ExcelWriter.1 error '80020009': Number of formats has exceeded Excel's limit

Home Products Support Corporate

Support Knowledge Base, Article 192

Product

ExcelWriter

Version

1.x

Title

ExcelWriter.1 error '80020009': Number of formats has exceeded Excel's limit

Problem

When using ExcelWriter to format a native Excel file "on the fly" with server-side ASP code, it is important to have a clear understanding of the repercussions of choosing to employ "Cells.Format" versus "Cells.Style".

MS Excel contains an upper limit of 4096 on the total combined number of unique formats and styles available to an individual Excel File. This "cap" of 4096 includes 16 built-in and 6 default formats. This further reduces to 4074 the available number of total combined formats and styles available to the developer formatting an Excel file with ExcelWriter.

Therefore, a common error that developers using ExcelWriter may experience is, as follows:

SoftArtisans.ExcelWriter.1 error '80020009'
Number of formats has exceeded Excel's limit

This is most commonly caused by the use of looping to apply ".format"(s) to multiple cells in a worksheet, as follow:

<% '--- Convert Recordset output to Spreadsheet input and '--- Import the data in one step, pasting from cell (2,1) with the headers appearing in row 2 FirstRow = 2 FirstCol = 1 NumRows = cells.CopyFromRecordset(oRs, True, FirstRow, FirstCol) '--- Format, on a cell by cell basis, all rows in column 1 to "text" and all rows in column 2 to "currency" for r = FirstRow +1 to NumRows +2 ws.cells(r, FirstCol).Format.Number = 49 'this value formats a cell as text ws.cells(r, FirstCol +1).Format.Number = 7 'this value formats a cell as currency next %>
By employing this formatting method, you will use up the pool of available formats quickly since each cell counts as an individual instance of a format.

Solution

MS Excel currently offers built-in optimization. This optimization makes a comparison of included formats and removes identical format objects. ExcelWriter, version 1.x does not yet include this same optimization technique.
(Optimization is being considered for addition to ExcelWriter an upcoming version).

Therefore, it is our recommendation to use ".Style" in place of ".Format" wherever possible when applying formats to more than a single cell. A single "custom Style" can be created once and applied to a entire row or column of cells. This counts as a single instance of formatting when considered toward the total available pool of formats/styles.

Here is a reworking of the above sample to employ a custom "TextStyle" and "CurrencyStyle" to format multiple cells:

<%
'--- Convert Recordset output to Spreadsheet input
'--- Import the data in one step, pasting from cell (2,1) with the headers appearing in row 2

FirstRow = 2
FirstCol = 1

NumRows = cells.CopyFromRecordset(oRs, True, FirstRow, FirstCol)

'--- Create a custom Font to be used in formatting the worksheet	
 	Set Font1 = XLW.CreateFont
 		Font1.name = "Times New Roman"
 		Font1.size = 10
	
'--- Create a custom "TextStyle" which will format cells as "text"
	Set TextStyle = XLW.CreateStyle
		TextStyle.Number = 49
		TextStyle.Font = Font1

'--- Create a custom "CurrencytStyle" which will format cells as "Currency"
	Set  = XLW.CreateStyle
		CurrencyStyle.Number = 7
		CurrencyStyle.Font = Font1
		
'--- Use looping to apply the custom styles to all rows in column 1 and column 2 
        For r = FirstRow +1 to NumRows +2
	       ws.cells(r, FirstCol).Style =  TextStyle 'this value formats a cell as text
	       ws.cells(r, FirstCol +1).Style = CurrencyStyle 'this value formats a cell as currency
        Next
%>

Please note: If you change any properties of your custom Style after making application of this style, all cells formatted with this style in your worksheet will be reworked accordingly.