All Rows Not Being Added From DataTable

Aspose.Cells Product Family
1

Here is my code (.NET C#):
worksheet.Cells.ImportDataTable(ds.Tables[0], true, “A1”);
workbook.Save(HttpContext.Current.Response, “PoliceActivity.xls”, ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));

The datatable contains 80,000 rows. When the spreadsheet loads, the sheet only contains 60,000 rows. 20,000 rows are being chopped off. Is there a limit on rows that can be added to a worksheet?

I am using version 16.12.0.0

2

@cspurlock,

Thanks for providing us code segment and details.

Well, this is due to the fact that for XLS (MS Excel 97 - 2003) file format, you can only have 65536 rows and 265 columns in a worksheet. This is an older format, so you should not use it rather you should use XLSX file format which is an advanced file format (MS Excel 2007 - 2013/2016). For XLSX file format, you can have 1048576 rows and 16384 columns in a single worksheet.
FYI:
XLS
65536x256 (till column IV)

XLSX
1048576x16384 (till column XFD)

In short, you should use XLSX file format, so kindly change your line of code to:
e.g
Sample code:

........
workbook.Save(HttpContext.Current.Response, “PoliceActivity.xlsx”, ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
HttpContext.Current.Response.End();
..........

it should fix your issue.

Let us know if you still find any issue.