Rows of DataTable is out of range

Aspose.Cells Product Family
1

Hello,


I am currently working on an application that inserts in excess of 350000 records into a workseet. I am trying to save it, using ImportDataTable(dataTable,True,0,0), in the FileFormatType of Excel2007Xlsx and getting a Rows of DataTable is out of range error.
Below is my test code. Would really appreciate any kind of help.

Public Sub TestAspose()
Dim excel As New Aspose.Cells.Workbook()
Dim worksheet As Aspose.Cells.Worksheet = excel.Worksheets(0)
Dim cells As Aspose.Cells.Cells = worksheet.Cells
Dim dt As New DataTable(“Products”)
dt.Columns.Add(“Col_ID”, GetType(Int32))
dt.Columns.Add(“Col_Name”, GetType(String))
For x As Integer = 0 To 104550
Dim dr As DataRow = dt.NewRow()
dr(“Col_ID”) = x
dr(“Col_Name”) = “Data” + x.ToString()
dt.Rows.Add(dr)
Next
'cells.ImportDataTable(dt, True, “A1”)
'cells.ImportDataTable(dt, True, 0, 0, True)
cells.ImportDataTable(dt, True, 0, 0)
excel.Save(“C:\test\out_rowstest.xlsx”, Aspose.Cells.FileFormatType.Excel2007Xlsx)
End Sub
2

Hi,

Thanks for your posting and using Aspose.Cells.

I have tested your code with the latest version: Aspose.Cells for .NET v8.7.2.4 and it worked fine. I have also attached the output excel file generated with your code for your reference.

Please use SaveFormat.Xlsx as shown in the red color below in Workbook.Save() method.

VB.NET
Dim excel As New Aspose.Cells.Workbook()
Dim worksheet As Aspose.Cells.Worksheet = excel.Worksheets(0)
Dim cells As Aspose.Cells.Cells = worksheet.Cells
Dim dt As New DataTable(“Products”)
dt.Columns.Add(“Col_ID”, GetType(Int32))
dt.Columns.Add(“Col_Name”, GetType(String))
For x As Integer = 0 To 104550
Dim dr As DataRow = dt.NewRow()
dr(“Col_ID”) = x
dr(“Col_Name”) = “Data” + x.ToString()
dt.Rows.Add(dr)
Next
'cells.ImportDataTable(dt, True, “A1”)
'cells.ImportDataTable(dt, True, 0, 0, True)
cells.ImportDataTable(dt, True, 0, 0)
excel.Save(“out_rowstest.xlsx”, SaveFormat.Xlsx)

3

Thanks a lot for the quick response. The code does work if I use the latest version of Apsose Cells. But our company holds the license for ver 4.4 as of now. Do you know if there is anyway of getting this to work with that version?

4

Hi,


As you are using some older version (v4.4.x) of the product, so I am afraid, we might not help you much. Neither we can evaluate your issue or fix the issue (if found) in the older version that you are using. I suspect, it might be a limitation or bug (on ImportDataTable method’s part) in the older version that you are using, so you might not be able to add more than 65k records in a worksheet while importing DataTable. We are sorry but there is no alternative or workaround but to upgrade to latest versions (e.g v8.7.2.x) of the product which works pretty well for your long list of data to be pasted via Cells.ImportDataTable() method.

Thank you.
5

Thanks for your help.

6

Hi,

Thanks for using Aspose.Cells.

Will you please give a try to your code with the following modification with your older version and see if it works for you.

Changes are highlighted in red color.

VB.NET
Public Sub TestAspose()
Dim excel As New Aspose.Cells.Workbook(FileFormatType.Xlsx)
Dim worksheet As Aspose.Cells.Worksheet = excel.Worksheets(0)
Dim cells As Aspose.Cells.Cells = worksheet.Cells
Dim dt As New DataTable(“Products”)
dt.Columns.Add(“Col_ID”, GetType(Int32))
dt.Columns.Add(“Col_Name”, GetType(String))
For x As Integer = 0 To 104550
Dim dr As DataRow = dt.NewRow()
dr(“Col_ID”) = x
dr(“Col_Name”) = “Data” + x.ToString()
dt.Rows.Add(dr)
Next
'cells.ImportDataTable(dt, True, “A1”)
'cells.ImportDataTable(dt, True, 0, 0, True)
cells.ImportDataTable(dt, True, 0, 0)
excel.Save(“C:\test\out_rowstest.xlsx”, Aspose.Cells.FileFormatType.Excel2007Xlsx)
End Sub