SaveExcel export only the first 65.536 rows

Posted by: luigggye-fp on 8 September 2017, 2:39 pm EST

  • Posted 8 September 2017, 2:39 pm 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


  • Replied 8 September 2017, 2:39 pm EST

    This question is answered here


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

  • Replied 8 September 2017, 2:39 pm 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.

  • Replied 8 September 2017, 2:39 pm 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.
  • Replied 8 September 2017, 2:39 pm 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    

  • Replied 8 September 2017, 2:39 pm 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
  • Replied 8 September 2017, 2:39 pm 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?
  • Replied 8 September 2017, 2:39 pm 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
  • Replied 8 September 2017, 2:39 pm 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());
                }

     

     

  • Replied 8 September 2017, 2:39 pm 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 
  • Replied 8 September 2017, 2:39 pm 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.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels