SaveExcel export only the first 65.536 rows

SaveExcel export only the first 65.536 rows

Posted by: luigggye-fp on 8 September 2017, 5:39 am EST

• 

  luigggye-fp

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 8 September 2017, 5:39 am EST

  Hi
  
  
  
  I am using the following code in order to export a spread sheet to an Excel file.
  
  

  FP.SaveExcel(FilePath, FarPoint.Excel.ExcelSaveFlags.NoFormulas Or FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders)

  But only the first 65.536 are exported due to the Excel limit.
  
  
  
  My spread sheet have 150.000 rows loaded from a dataset.
  
  
  
  There is a way to place the first 65.536 rows into to the "first sheet" of the excel book, the second 65.536 rows into the "second sheet" of the excel book, etc.?
  
  
  
  In other words, there is any way that the function FP.SaveExcel, creates a new worksheet into the Excel Workbook for every 65.536 lines?

  Thanks in advance,

  Luigggye
  
  

  
  
  

• 

  paul1960

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:39 am EST

  This question is answered here

  http://www.clubfarpoint.com/Forums/forums/thread/33461.aspx

• 

  anastasia-c-virden-ctr

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:39 am EST

  Thanks for your response, I understood.

  In my case, I have this code:

                      Dim Builder As New SqlConnectionStringBuilder()
  
  
  
                      Builder.DataSource = TableCFG_MACROS.Rows(0).Item("SERVER_NAME")
  
  
  
                      If TableCFG_MACROS.Rows(0).Item("AUTHENTICATION") = "True" Then
  
                          Builder.IntegratedSecurity = True
  
                      Else
  
                          Builder.IntegratedSecurity = False
  
                          Builder.UserID = TableCFG_MACROS.Rows(0).Item("LOGIN")
  
                          Builder.Password = TableCFG_MACROS.Rows(0).Item("PASSWORD")
  
                      End If
  
  
  
                      Builder.InitialCatalog = TableCFG_MACROS.Rows(0).Item("CONNECT_TO_DATABASE")
  
  
  
                      Using Connection As New SqlConnection(Builder.ConnectionString)
  
  
  
                          Connection.Open()
  
  
  
                          Dim cmd As SqlCommand = Connection.CreateCommand
  
  
  
                          cmd.CommandTimeout = 30
  
                          cmd.CommandText = TableCFG_MACROS.Rows(0).Item("SQL")
  
  
  
                          Dim Adapter = New SqlDataAdapter(cmd)
  
  
  
                          TheDataset = New DataSet()
  
  
  
                          Adapter.Fill(TheDataset, "QueryResult")
  
  
  
                           FP.DataSource = TheDataset.Tables("QueryResult")
  
  
  
                      End Using
  
  
  
  

  As you can see, I use   FP.DataSource = TheDataset.Tables("QueryResult")    to load the spread sheet.
  
  
  
  When the query returns less than 65.536 rows, all is OK.
  
  
  
  But when the query returns more than 65.536 rows, I have problem to export all the rows to excel.
  
  
  
  As you know, I can not modify the line    FP.DataSource = TheDataset.Tables("QueryResult"),   (I loose the entire data loaded in the spread sheet).
  
  
  
  So, how can I do to "move" rows from the bound sheet to another sheet in the same spread control ?
  
  
  
  or
  
  
  
  How can I do to load 65.536 rows in every new sheet using the bound process? (FP.DataSource = ) in order to be ready to export to excel without "move data between sheets" ?
  
  
  
  Thanks in advance,

  Luigggye    
  
  

• 

  deepaksharma14

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:39 am EST

  Hello,

  I have created a sample application where, if rows are more than 65536 then the extra rows are inserted in a new sheet and then the spread is exported to excel once the export is complete the extra sheet is removed from spread and rows are again inserted at the position where they were removed from.

  Please have a look at the attached sample.

   

   Thanks,

   

  2011/06/ExporttoExcelWithExtraRows.zip
• 

  tackmeister

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:39 am EST

  I have the same problem and has split output to multiple sheets. However, I am using the 64-bit version of Excel, yet the export still limits me to the 65536 rows limit. Is there in the works to allow for export to 64-bit Excel where over a million rows are allowed?
  
  

• 

  deepaksharma14

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:39 am EST

  Hello,

  I try to export 80000 rows in one spread sheet xlsx format and it got exported with no issues. I used the following code to export to excel 2007,

  FpSpread1.SaveExcel("C:\longexcel.xlsx", FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat)
  
  

  I do not have a 64 bit Excel to test this with. I tested it with 32 bit Excel 2007. I have attached the exported excel file, please check and let me know if you are also facing this issue with Excel 2007 32 bit.
  
  

   

  Thanks,

   

   

   

   

   

  2011/06/longexcel.zip
• 

  tackmeister

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:39 am EST

  Did not work. I have 178000 rows. Here's the error.

   

  System.Exception: System.ApplicationException: Error saving Excel file ---> System.OverflowException: Value was either too large or too small for a UInt16.
  
     at System.Convert.ToUInt16(Int32 value)
  
     at as.a(Int16 A_0, ArrayList A_1, ArrayList A_2)
  
     at as.a(Int16 A_0, MemoryStream A_1, ExcelSaveFlags A_2)
  
     at as.a(Stream A_0, ExcelSaveFlags A_1)
  
     at FarPoint.Excel.ExcelFileHandler.a(String A_0, ExcelSaveFlags A_1, String A_2)
  
     at FarPoint.Excel.ExcelFileHandler.Save(String fileName, ExcelSaveFlags saveFlags, String password)
  
     at ci.a(String A_0, ExcelSaveFlags A_1, String A_2)
  
     at c9.a(String A_0, Stream A_1, ExcelSaveFlags A_2, ExcelWarningList A_3, String A_4)
  
     --- End of inner exception stack trace ---
  
     at c9.a(String A_0, Stream A_1, ExcelSaveFlags A_2, ExcelWarningList A_3, String A_4)
  
     at c9.a(String A_0, ExcelSaveFlags A_1, ExcelWarningList A_2, String A_3)
  
     at FarPoint.Win.Spread.FpSpread.SaveExcel(String fileName, ExcelSaveFlags saveFlags, String password)

   It is coded to only allow 2^16 rows.
  
  

• 

  amitpal

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:39 am EST

  I just tested this on 64 bit machine with 64 bit office and was able to export Spread which contains more than 1,00,000 rows. I would like to see the code you are using as well as more information regarding the architecture of the machine.

• 

  tackmeister

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:39 am EST

  Architecture: Win 64-bit Ultimate, MS Office 2010
  
  

             try
  
              {
  
                  saveFileDialog.FileName = "Excel Export.xls";
  
                  saveFileDialog.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*"; ;
  
                  saveFileDialog.FilterIndex = 1;
  
                  if (saveFileDialog.ShowDialog(this) == DialogResult.OK)
  
                  {
  
                      string fileName = this.saveFileDialog.FileName;
  
                      if (!fileName.EndsWith(".xls"))
  
                          fileName += ".xls";
  
                      fpSpread1.SaveExcel(fileName, FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders);
  
                      MessageBox.Show(this, "Excel file saved successfully.", "File",
  
                                      MessageBoxButtons.OK);
  
                  }
  
              }
  
              catch (Exception ex)
  
              {
  
                  throw new Exception(ex.ToString());
  
              }

   

   

• 

  tackmeister

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:39 am EST

  With the above, I also tried the UseOOXMLFormat option and changing extension to .xlsx. No effect. Please provide your snippet that can export 1 million rows to 64-bit Office 
  
  

• 

  reeva

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:39 am EST

  Hello,

  I am able to replicate the issue at my end and have reported it as a bug in spread and the bug number for the same is #99922138 which will be fixed in our future maintenace build of Spread.

  Thanks.
  
  

• 

  kithsiri

  • Post Options:
  • Link
    Copy

  Posted 20 December 2019, 1:41 am EST

  Does anyone have the solution with Spread 6 . I am using VB6. I am struggling to export to excel over 65536 rows.?

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 22 December 2019, 5:36 pm EST

  Hi,

  This has been answered here:
  
  https://www.grapecity.com/forums/spread-winforms/spread-6-with-vb6--excel-6

  Thanks.