How to Handle Maximum Number of Extended Formats Exceeded in WinForms?
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® 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.
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 forums, Support Tickets, or feedback portal. We are always happy to assist you!