XLSheet: Maximum number of rows exceeded (65.536)

XLSheet: Maximum number of rows exceeded (65.536)

Posted by: topran on 21 January 2020, 10:25 am EST

• 

  topran

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 21 January 2020, 10:25 am EST

  Hello,
  
  I am using C1XLBook to fill values in a XLSheet.
  
  With:

                  For Each obj As String In list
                      row = row + 1
                      sheet(row, 0).Value = obj
                  Next
  

  If the row over 65536, I get this error message:
  
  Maximum number of rows exceeded (65.536). bei C1.C1Excel.XLRowCollection.Insert(Int32 index, XLRow row)
  
  bei C1.C1Excel.XLSheet.get_Item(Int32 rowIndex, Int32 colIndex)

  I think it is the same problem as here:
  
  https://www.grapecity.com/forums/winforms-edition/flexpreview-export-to-exce#original-post

  Is this correct?
  
  Best wishes
  
  Jennifer

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 22 January 2020, 1:52 am EST

  Hi Jennifer,

  By default the C1XLBook object treats the excel as 2003 excel file format if not specified and 2003 excel format has restriction of 256 columns and 65536 rows.

  To solve this you need to first save the C1XLBook object to a OpenXML format i.e. .xlsx and then load it back and start adding rows over 65536. Code snippet is given below :

  int i;
  c1XLBook1.Save(@"..\..\demo.xlsx");
  c1XLBook1.Load(@"..\..\demo.xlsx");
  C1.C1Excel.XLSheet sheet = c1XLBook1.Sheets[0];
  for (i = 0; i <= 66000; i++)
  {
   sheet[i, 0].Value = (i + 1) * 10;
   sheet[i, 1].Value = (i + 1) * 100;
   sheet[i, 2].Value = (i + 1) * 1000;
  }
  

  Please go through the attached sample demonstrating the same.

  Regards,
  
  Prabhat Sharma.
  
  ExcelDemo.zip

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 23 January 2020, 4:23 am EST

  Hello Prabhat Sharma,

  thanks for your answer it works fine.

  Best regards
  
  Jennifer

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 14 December 2020, 11:49 am EST

  Hello
  
  for everyone with the same problem, I’ve found a good solution.
  
  Uses the property: ```
  
  CompatibilityMode = CompatibilityMode.Excel2007