How to handle "Maximum number of extended formats exceeded" exception in WinForms?
Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files.
Why does the Maximum number of extended formats exception is thrown?
Microsoft Excel 97-2003 version supports only 4095 formats. Syncfusion Excel library also does the same and when the number of formats created exceeds the count, Maximum number of extended formats exceeded exception is thrown.
How to overcome this exception?
An Excel workbook will have 21 extended formats by default, on creation. To work with more than 4095 formats, you need to set the Excel workbook version to a version greater than Excel 97-2003.
C#
//Set the workbook version to avoid exception workbook.Version = ExcelVersion.Excel2007;
VB.NET
'Set the workbook version to avoid exception workbook.Version = ExcelVersion.Excel2007
By default, Syncfusion Essential XlsIO considers the workbook version as Excel97to2003.
The following complete code snippet explains how to overcome the Maximum number of extended formats exceeded exception.
C#
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Instantiate the Excel application object
IApplication application = excelEngine.Excel;
//Create a new Excel workbook
IWorkbook workbook = application.Workbooks.Create(1);
//Get the first worksheet in workbook into IWorksheet
IWorksheet worksheet = workbook.Worksheets[0];
//set the workbook version to avoid exception
workbook.Version = ExcelVersion.Excel2007;
//Initialize a variable for row index
int rowIndex = 1;
//Set background color using RGB values
for (int g = 0; g <= 50; g++)
{
for (int b = 0; b <= 100; b++)
{
//Extended format will be created for every unique color
worksheet[rowIndex, 1].CellStyle.Color = Color.FromArgb(255, g, b);
rowIndex++;
}
}
//Save the Excel workbook
workbook.SaveAs("Output.xlsx");
}
VB.NET
Using excelEngine As ExcelEngine = New ExcelEngine()
'Instantiate the Excel application object
Dim application As IApplication = excelEngine.Excel
'Create a new Excel workbook
Dim workbook As IWorkbook = application.Workbooks.Create(1)
'Get the first worksheet in the workbook into IWorksheet
Dim worksheet As IWorksheet = workbook.Worksheets(0)
'Set the workbook version to avoid exception
workbook.Version = ExcelVersion.Excel2007
'Initialize a variable for row index
Dim rowIndex As Integer = 1
'Set background color using RGB values
For g As Integer = 0 To 50 Step 1
For b As Integer = 0 To 100 Step 1
'Extended format will be created for every unique color
worksheet(rowIndex, 1).CellStyle.Color = Color.FromArgb(255, g, b)
rowIndex = rowIndex + 1
Next
Next
'Save the Excel workbook
workbook.SaveAs("Output.xlsx")
End Using
A complete working sample to create an Excel workbook with extended formats without facing Maximum number of extended formats exceeded exception can be downloaded from Exception-Handling.zip.
Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheet or workbook, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to worksheet using Template Markers, and most importantly PDF and Image conversions etc., with code examples.
Click here to explore the rich set of Syncfusion Excel (XlsIO) library features.
Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer the link to learn about generating and registering Syncfusion license key in your application to use the components without trail message.
