Aspose.Cells cannot shift nonblank cell off the worksheet in import data table method

Aspose.Cells Product Family
1

Workbook workbook = new Workbook();

workbook.LoadData(fileName);

Worksheet worksheet = workbook.Worksheets[0];

DataTable dataTable = new DataTable();

dataTable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);

workbook.Worksheets[0].Cells.ImportDataTable(dataTable, true, "A1");

dataTable.Rows[0].Delete();

dataTable.AcceptChanges();

In the above code im getting error when i import more than 30000 rows .please advice us how to proceed.

workbook.Worksheets[0].Cells.ImportDataTable(dataTable, true, "A1"); this line exactly throws an error and if the rows count is 30000 then it works fine but if its more than 30000 rows then this error (Aspose.Cells cannot shift nonblank cell off the worksheet) will throw.kinldy suggest me.we are using aspose 4.0 version.

2

Hi,


May be there is not enough space in your worksheet (of your XLS file) to paste more than 30,000 records. Anyways, kindly give us your template file here, we will check your issue soon.

Thank you.
3

I have attached the excel sheet which we are using and its not the problem with space issue in excel.We should permit excel 2003 formats only not 2010.

Does import data table not Support more than 30k rows in excel?

Please help us to resolve the issue ASAP.

Thanks

Deepan.

4

Hi,


Yes, the ImportDataTable() method does support to import more than 30K records. Please download and try the latest version: Aspose.Cells for .NET v7.3.1.1 I have tested your scenario with the following code and it works fine.

Sample code:

LoadOptions loadOptions = new LoadOptions(LoadFormat.Excel97To2003);
loadOptions.LoadDataOnly = true;
Workbook workbook = new Workbook(“e:\test2\BUG_3552_HEN_COS_OUTPUT_VARIANT1.xls”, loadOptions);
Worksheet worksheet = workbook.Worksheets[0];
DataTable dataTable = new DataTable();
dataTable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
workbook.Worksheets[0].Cells.ImportDataTable(dataTable, true, “A1”);


workbook.Save(“e:\test2\output1BUG_3552_HEN_COS_OUTPUT_VARIANT1.xls”);


One thing should be clear, in XLS file format, MS Excel only allows 65536 records in a single worksheet. So accordingly to your code, you fill a datatable based on about 35K records from your sheet, and then you import the datatable into the same worksheet. Now, here when you use the line of code:
workbook.Worksheets[0].Cells.ImportDataTable(dataTable, true, “A1”);

It will first insert 35K number of blank rows at A1 cell then import the datatable that starts filling from A1 cell, consequently your existing records/data will be shifted downwards, so may be in your older version (that you are using), since the worksheet can have only 65536 records, so your data might be overflown to go beyond 65536 rows, so you are getting exception. You should either delete the existing records in the sheet first when you are importing data table to the worksheet or use:
workbook.Worksheets[0].Cells.ImportDataTable(dataTable, true, 0,0, false); // Here false (bool insertRows) refers to there will be no blank rows insertion first as it will overwrite the existing data.

Thank you.

5

Hi,

Thanks a lot ,Now it is working perfectly for me.

6

Hi,


Good to know that your issue is resolved now.

Thank you.