|
| Product |
| ExcelWriter |
| Version |
| 6 and above |
| Title |
| Error: "Failed to get Style interface" when calling the ExcelApplication.Save() method. |
| Problem |
When using ExcelWriter in ASP.NET, the following error is thrown:
Error Details:
Failed to get Style interface; Failed to create new CellStyleModel;
Too many styles defined in this file.
Attempting to reference style 4297; only 4096 are allowed."
When too many Styles are created or set using the ExcelApplication object, a "Too many styles defined in this file" error will be thrown by ExcelWriter. Excel has a limit of 4096 styles in a spreadsheet and this error will be thrown if that number is exceeded. This is common when a user sets Style properties individually for each Cell, rather than using a shared Style. |
| Solution |
When the user sets the Style properties of the cell individually, e.g. Cell.Style.Font.Bold = True, a new Style will be created for the cell including the existing style settings plus any new changes. When the number of total cells whose styles are set this way exceeds 4096, an exception is thrown. To keep from exceeding this limit, define styles using the Style object. When a particular style is needed, assign the value of the cells, area, range, column, or row equal to a defined style.
Examples:
Create a Style
|
[C#]
|
[VB.NET]
|
|
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Create();
//Create a new style representing bold red 12pt Arial font
Style myStyle = wb.CreateStyle();
myStyle.Font.Bold = true;
myStyle.Font.Color = wb.Palette.GetClosestColor(System.Drawing.Color.Red);
myStyle.Font.Size = 12;
myStyle.Font.Name = "Arial";
Dim xla as new ExcelApplication()
Dim wb as Workbook = xla.Create()
'Create a new style represenenting bold red 12pt Arial font
Dim myStyle as Style = wb.CreateStyle()
myStyle.Font.Bold = true
myStyle.Font.Color = wb.Palette.GetClosestColor(System.Drawing.Color.Red)
myStyle.Font.Size = 12
myStyle.Font.Name = "Arial"
|
Setting a style for a Cell
When you need to assign a Style to a single cell, use the Cell.Style property:
|
[C#]
|
[VB.NET]
|
|
Worksheet ws = wb.Worksheets[0];
Cell myCell = ws.Cells[0,0];
Cell.Style = myStyle;
Dim ws as Worksheet = wb.Worksheets(0)
Cell myCell = ws.Cells(0,0)
Cell.Style = myStyle
|
Setting Style for a Range or Area
For larger groups of cells that do not take up entire rows or columns, use the Area.SetStyle() or Range.SetStyle() methods. These methods are not ideal for when you wish to assign a style to an entire column or row because the underlying code assigns the Style to each cell, which can affect performance if there are a large number of cells.
|
[C#]
|
[VB.NET]
|
|
Area myArea = ws.CreateArea(0,0,10,10);
myArea.SetStyle(myStyle);
Dim myArea as Area = ws.CreateArea(0,0,10,10)
myArea.SetStyle(myStyle)
|
Setting a Style for a Row or Column
If you are planning to set a style for an entire row or column, the most efficient way to do it is by using the ColumnProperties.Style and RowProperties.Style properties:
|
[C#]
|
[VB.NET]
|
|
ColumnProperties myColProps = ws.GetColumnProperties(0);
myColProps.Style = myStyle;
Dim myColProps as ColumnProperties = ws.GetColumnProperties(0)
myColProps.Style = myStyle
|
|
| Created : 10/12/2007 1:50:44 PM (last modified : 10/12/2007 4:03:00 PM) |
|
