We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

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.

Note:

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.

 

2X faster development

The ultimate WinForms UI toolkit to boost your development speed.
ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile