"Too many different cell formats" Error

Aspose.Cells Product Family
1

I have upgraded to the latest hotfix, but still recieve this error.

“Too many different cell formats”

when opening an excel file after it is generated. This error happend AFTER upgrading to the latest version of the component. I need to get this bug addressed as soon as possible, as its holding up a release for us.

2

Could you please upload your generated file here? If you don’t want to make it public, please send it to excel@aspose.com.

MS Excel has a limitation of different formats settings. The number of different styles is about 500. Which versoin of your previous Aspose.Excel.dll? In previous version, extra format settings are discarded. In the latest hotfix, they are written to the file. So this error occurs.

Could you please post your code here? I will check if there are some work arounds.

3

We’re running into the same issue with the “too many different cell formats” because of the 500 style limit. The reason is that we do formatted as previously recommend Cell-By-Cell even though a lot of the format could be done via the Range. The problem is that we can’t assume in our code that the Range.Style object is X so we have to do formatting via the Cell-By-Cell method.

I noticed a previous thread that said you were considering supporting the same Range.Style behavior that Excel provides through its API so that we don’t have to push a style onto every cell in the range but can manipulate one or two different aspects of Style of all the cells.

For example, you have a tabluar output of data that is color striped horizontially and has different column formats ie Right v. Left Alignment for the content. The only way really is to do the Cell-By-Cell at present without trying to engineer a mechanism that would do Style management.

Our actually output is only 10-20 worksheets but because its a lot of this tabular formatted data it surpasses the 500 style limit you menitoned.

Love to hear any thoughts on this.

4

You can set formats of thousands of cells with Cell-By-Cell method. Aspose.Excel can merge same same cells formats.

For example,

cells[0, 0].Style.Font.Color = Color.Red;
cells[0, 0].Style.HorizontalAlignment = TextAlignmentType.Left;


cells[0, 1].Style.Font.Color = Color.Red;
cells[0, 1].Style.HorizontalAlignment = TextAlignmentType.Right;


cells[1, 0].Style.Font.Color = Color.Red;
cells[1, 0].Style.HorizontalAlignment = TextAlignmentType.Left;


cells[1, 1].Style.Font.Color = Color.Red;
cells[1, 1].Style.HorizontalAlignment = TextAlignmentType.Right;

In this case, only 2 format related records are written to the Excel file.

However, if you use different settings more than 500, this error may occur.

Could you please post your sample code and upload the generated file here? I will check if this problem is a bug or you have to try to remove some format settings.

Thank you.

5

Here is the code that generates the report. I am ONLY applying 2 distinct styles to the worksheet. The latest upgrade in the Aspose.Excel.dll is taking this same excact code that used to work and somehow making it apply separate styles for each cell (thereby passing the 500 style limit).






 
  protected void generateReport_Click(object sender, EventArgs e) {
   BindData();
   DataTable report = (DataTable) grid.DataSource;
 
   if (report.Rows.Count > 0) {
    Excel excel = connectionsAcademy.reports.Report.getExcelObject(Page);
    setHeaderStyle(excel);
    Worksheet sheet = excel.Worksheets[0];
    sheet.Name = "Asset Aware Report";
   
    byte col = 0;   
 
    // Build the column headers
    sheet.Cells[0, col].PutValue("Asset ID");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    sheet.Cells[0, col].PutValue("Serial Number");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    sheet.Cells[0, col].PutValue("Modified");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    sheet.Cells[0, col].PutValue("History");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    sheet.Cells[0, col].PutValue("Modifier");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    sheet.Cells[0, col].PutValue("Status");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    sheet.Cells[0, col].PutValue("Category");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    sheet.Cells[0, col].PutValue("Sub-Category");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    sheet.Cells[0, col].PutValue("Manufacturer");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    sheet.Cells[0, col].PutValue("Model");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    sheet.Cells[0, col].PutValue("Vendor");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    sheet.Cells[0, col].PutValue("Location");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    sheet.Cells[0, col].PutValue("Location ID");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    sheet.Cells[0, col].PutValue("Location Type");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    sheet.Cells[0, col].PutValue("Owner");
    sheet.Cells[0, col].Style = headerStyle;
    sheet.AutoFitColumn(col++);
 
    col = 0;
    int row = 1;
 
    foreach (DataRow reportRow in report.Rows) {
     col = 0;
     sheet.Cells[row, col++].PutValue(reportRow["id"]);
     sheet.Cells[row, col++].PutValue(reportRow["serialNumber"]);
 
     sheet.Cells[row, col].Style.Custom = "mm/dd/yyyy";
     sheet.Cells[row, col++].PutValue(reportRow["updated"]);
 
     sheet.Cells[row, col++].PutValue(reportRow["latestHistory"]);
     sheet.Cells[row, col++].PutValue(reportRow["modifier"]);
     sheet.Cells[row, col++].PutValue(reportRow["idAssetStatus"]);
     sheet.Cells[row, col++].PutValue(reportRow["mainCategoryName"]);
     sheet.Cells[row, col++].PutValue(reportRow["subCategoryName"]);
     sheet.Cells[row, col++].PutValue(reportRow["assetManufacturerName"]);     
     sheet.Cells[row, col++].PutValue(reportRow["assetModelName"]);          
     sheet.Cells[row, col++].PutValue(reportRow["assetVendorName"]);
     sheet.Cells[row, col++].PutValue(reportRow["locationName"]);
     sheet.Cells[row, col++].PutValue(reportRow["idLocation"]);
     sheet.Cells[row, col++].PutValue(reportRow["idLocationType"]);
     sheet.Cells[row, col++].PutValue(reportRow["ownerName"]);
     row++;
    }
  
    string filename = connectionsAcademy.reports.Report.createReportFileName("assetAwareReportGen");
    excel.Save(ca.User.personalTemporaryDirectory + filename, SaveType.Default, FileFormatType.Default, Context.Response); 
    downloadReport.NavigateUrl = ca.User.personalTemporaryUrl + Context.Server.UrlPathEncode(filename);
    downloadReport.Visible = true;
   }
  }
 
  private void setHeaderStyle(Excel excel) {
   int styleIndex = excel.Styles.Add();
   headerStyle = excel.Styles[styleIndex];
   headerStyle.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
   headerStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;
   headerStyle.Font.IsBold = true;
   headerStyle.Name = "headerStyle";
  }


 		

 
 

            


            

              
              
              
            


            

                
                
              


          

          

            

              

              


              
                  
                  
              6
              
            

            

              
The problem was the custom format string.  The latest aspose component seems to treat it as x different styles, rather than 1.  It works fine if I changed that to:







sheet.Cells[row, col].Style.Number = 14;


 		





            


            

              
              
              
            


            

                
                
              


          

          

            

              

              


              
                  
                  
              7
              
            

            

              
Thanks for your help. I will check and fixe this problem ASAP.

            


            

              
              
              
            


            

                
                
              


          

          

            

              

              


              
                  
                  
              8
              
            

            

              
Hello,

We are getting the same error as well.  We have done our initial development with version 2.7.4.  Last week, we purchased the licence so we attempted to put in the most recent DLL, and got the To Many format errors.  During the cours of our development, we attemted to use the newer DLL’s as they became available, but always ran into a problem with something breaking.

So I have to ask, do you have some method of regression testing that you use prior to releasing a fix?

Please don’t take it as criticisme.  I beleive that you are the only company that offers this level of  quick turnaroud whenever a bug is submitted to you.  But if you do not have some form of regression testing to at least catch the major issues, this means that we have to incorporate a more stringent regression testing into our development process.

If you wish to use us as your test bed, would it be possible to have an addition put in at the download page, where those who download and test it can report if they had any issues with it?  Or in this forum have a field dedicated to the version number so that we can quickly identofy which version of the product the issue refers to.

Thanks

            


            

              
              
              
            


            

                
                
              


          

          

            

              

              


              
                  
                  
              9
              
            

            

              
Sorry for this bug. We will fix it within about 1-2 days.

Sure we will do regression testing before releasing. But I think our test cases are not enough. This case will be added to our test cases to avoid it happening again.

In recent several versions we did many internal optimization, that introduced some bugs. We will do more testing to assure the quality.