How many Excel rows can be uploaded to a data table at a time

[Madhan00084]

1.How many Excel rows can be uploaded to a data table at a time, Is there any constraints and if so please mention maximum row count.

2. When i am exporting worksheet.Cells.MaxDataRow and worksheet.Cells.MaxDataColumn from worksheet of excel to data table, i am getting three addtional empty rows at the bottom. Please recommend the possible to avoid it.

Please see the below piece of code.

ds.Tables.Add(worksheet.Cells.ExportDataTableAsString(4, 0, worksheet.Cells.MaxDataRow + 1, worksheet.Cells.MaxDataColumn+1, true));

eg: total no of Datarows are 23 and i am getting 26 rows in datatable which includes

three as empty rows.

3. Maximum no. of worksheets that can be uploaded at a time.

[Amjad_Sahi]

Hi,

1) There is no limit at all for exporting Excel sheet to fill datatable, it depends on your resources.

2) There might be some logical errors in your code. e.g I can see one of them.

Please see the updated code below, it should export valid rows only:

ds.Tables.Add(worksheet.Cells.ExportDataTableAsString(4, 0, worksheet.Cells.MaxDataRow + 1 - 4, worksheet.Cells.MaxDataColumn+1 - 0, true));

3) Again there is no limit put forth by Aspose.Cells component. It works the same way Ms Excel.

Thank you.

[Madhan00084]

Thanks for the reply. It was fast :-)

We have attached a sample excel worksheet for you kind reference. Please see attchment. File Name: Sample Worksheet.png

We have few header rows which we doesn't require to export into datatable. Our data starts from 4 row and ends at 13 row. So, when we export we need data from row 4 till row 13. Instead we are getting from row 4 to row 16.

We have also tried with your code, but we should have mentioned about the headers in the first place. Sorry about that.

[Amjad_Sahi]

Hi,

See the lines of code:

int sRow = 4; //fifth row

int sCol = 0; //first column

//Note: MaxDataRow gives you 12 as the last row index which contains data.

ds.Tables.Add(worksheet.Cells.ExportDataTableAsString(sRow, sCol, worksheet.Cells.MaxDataRow + 1 - sRow, worksheet.Cells.MaxDataColumn+1 - sCol, false)); //false here specifies that you are not exporting the first row as column names for your table.

As I told you earlier, if you are exporting data from the fifth row (row index = 4 (zero based)), you have to subtract that index from your MaxDataRow index. The reason is simple, the second and the third parameter and fourth parameter of ExportDataTableAsString() method (that you are using) specify the total number of rows that you are exporting, so, if you simplies the expression, you will get i.e.:

worksheet.Cells.MaxDataRow + 1 - sRow --> 12 +1-4 = 9 --> so you are exporting 9 rows strarting from 5th row (row index = 4)… i.e. from 5th row to 13 rows in your sheet.

Hope, you understand it now.

Thank you.