Home     Products      Support      Corporate     Sign In 
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.

Related Links
OfficeWriter Home Page
OfficeWriter Enterprise Edition
Latest OfficeWriter News
OfficeWriter: Programmatic Runtime Control

Created : 12/1/2002 12:00:00 AM (last modified : 5/7/2001 4:32:00 PM)
Rate this article!
 
Comments



Copyright 2010 © SoftArtisans, Inc. All Rights Reserved.

Site Map     |     Privacy Policy     |     Contact Us