Articles in this section
Category / Section

How to Handle Maximum Number of Extended Formats Exceeded in WinForms?

3 mins read

Syncfusion® Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Additionally, it 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 adheres to this limitation, and when the number of formats created exceeds this count, the 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 worksheets data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheets or workbooks, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to worksheets using Template Markers, and most importantly PDF and Image conversions etc., with code examples.

Click here to explore the rich set of Syncfusion&reg; Excel (XlsIO) library features.

Note:

Starting with v16.2.0.x, if you reference Syncfusion&reg; 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&reg; license key in your application to use the components without trail message.


Conclusion

I hope you enjoyed learning about how to Handle Maximum Number of Extended Formats Exceeded in WinForms.

You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our UG documentation and online demos to understand how to manipulate data in Excel documents.

If you are an existing user, you can access our latest components from the License and Downloads page. For new users, you can try our 30-day free trial to check out XlsIO and other Syncfusion components.

If you have any queries or require clarification, please let us know in the comments below or contact us through our support forumsSupport Tickets, or feedback portal. We are always happy to assist you!

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please  to leave a comment
Access denied
Access denied