Maximum number of extended formats exceeded

[Riccardo Ricaboni]

Hi,

We've upgraded our tool based on Excel 2010 (xlt template) and syncfusion xlsio to one of the latest release. Previously with a very old release of sycnfusion and Excel 2003, we had no particular problems, right now, if we select more than about 120 row to be charged in our excel template we received an error but we cannot understand why. It always happen when we check what kind of data (retrieved by a sql db) we are managing (numeric, text, datetime) to set the cellStyle.NumberFormat of the cell.

Could you help please?

THE ERROR IS:

System.ApplicationException: Maximum number of extended formats exceeded.   at Syncfusion.XlsIO.Implementation.Collections.ExtendedFormatsCollection.Add(ExtendedFormatImpl format)   at Syncfusion.XlsIO.Implementation.WorkbookImpl.RegisterExtFormat(ExtendedFormatImpl format, Boolean forceAdd)   at Syncfusion.XlsIO.Implementation.CellStyle.EndUpdate()   at Syncfusion.XlsIO.Implementation.ExtendedFormatWrapper.set_NumberFormat(String value)   at CMS_UpdateMassivo.CallerUpdateMassivo.SetCellValue(Object p, IRange iRange)   at CMS_UpdateMassivo.CallerUpdateMassivo.FillExcelSheet()   at CMS_UpdateMassivo.CallerUpdateMassivo.PrepareExcel()   at CMS_UpdateMassivo.CallerUpdateMassivo.CallUpdateProcedure(String args)

OUR METHOD THAT GOES ALWAYS IN ERROR :

private static void SetCellValue(object p, IRange iRange)

        {

            //if (p.ToString() == "1-2" || p.ToString() == "1-1")

            //{

            //   // int a = 1;

            //}

            try

            {

                if (p is DateTime)

                {

                    iRange.Value2 = ((DateTime)p); //.ToString("dd/MM/yyyy");

                    iRange.CellStyle.NumberFormat = "dd/MM/yyyy";

                }

                else if (p is decimal || p is int || IsNumeric(p))

                {

                    iRange.Value2 = p;

                    iRange.CellStyle.NumberFormat = "General";

                }

                else

                {

                    iRange.CellStyle.NumberFormat = "General";

                    iRange.Value2 = "'" + p.ToString();

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.ToString() + "-" + p.ToString());

               

            

            }

        }

[Rajesh A]

Hi,

Thank you for using Syncfusion Product’s.

We are not able to reproduce the above reported issue from our side, with the revised code you have mentioned. Kindly update us a simplified sample of this or else a video screen shot of the codes and its execution. We are requesting you to create a new direct-trac incident and Request for issue in that. We need to know your version Details (Previous and Current version). This helps us to investigate further on the issue.

Direct-Trac Link: Login

Please let us know if you require any further clarifications.

Thanks,

Rajesh. A

[Rajesh A]

Hi,

We would like to share some information on this exception. As MS Excel, XlsIO supports maximum of 4075 style for MS Excel 2003 file format and 64000 styles for MS Excel 2007 and 2010 file formats. If the styles exceed this limit then XlsIO throws above reported exception.

XlsIO behavior of Style creation:

In our XlsIO, Modifying the style properties (Font, cell backcolor, fore color, etc..,) in the existing style will create the new style. We have explained this scenario in the below code snippet. We have workaround this below scenario and attached the workaround in the below link.

Enclosing the below code snippet in loop with the different style properties will reproduce the issue.

IWorksheet sheet = workbook.Worksheets[0];

// creates the new style with the existing style setting

// and applies the given font. Then the new style

//index is assigned to the cell range.

sheet["A1"].CellStyle.Font.FontName = "Verdana";

// creates the new style with the existing style setting

//(which is created in above step)

// and applies the given font. Then the new style

//index is assigned to the cell range.

sheet["A2"].CellStyle.Color = System.Drawing.Color.Red;

 

Workaround for the above scenario:

Create the custom style using workbook’s style collection with the required style settings and assign the style name to all cells in the range.

Code Snippet:

IWorksheet sheet = workbook.Worksheets[0];

//Creates the single style, This also increases the performance.

IStyle style= workbook.Styles.Add("CustomHeadingStyle");

style.Font.FontName = "Verdana";

style.Color = System.Drawing.Color.Red;

sheet["A1"].CellStyleName = "CustomHeadingStyle";

Workaround: Sample

Please make use of the above workaround and if still this issue reproduces then send us the input template and simplified issue reproducing sample. It will help us to further investigation on the issue.

Please let me know if you have any clarification,

Thanks and Regards,

Rajesh A.