ASPOSE: How to correctly get Total Rows in Worksheet

Question

Currently using Aspose for .NET in a C# WebService. I am currently having issues getting the correct Total Rows using worksheet.Cells.Rows.Count It is showing that I have around 230 rows but inside the file, I only have 14 rows. I am guessing because I reuse my Excel file and deleted some rows by highlighting the rows then pressing the delete key.

When I recreate a brand new Excel file, then copy-paste the 14 values in the new file. The worksheet.Cells.Rows.Count works correctly.

Would this be a bug in the library or am I deleting the values in Excel file wrongly or I am using a wrong count property?

Am I missing something?

TIA

Amjad Sahi · Accepted Answer · 2022-04-08T18:51:03.957

2

Well, RowCollection/ColumnCollection.Count attribute would give you total number of rows/columns which are initialized, so it won't be always reliable to get total number of data rows/cols. I think you should use Worksheet.Cells.MaxDataRow and Worksheet.Cells.MaxDataColumn properties to get the farthest (last) row/column indices (0 - based), see the sample code segment for your reference: e.g Sample code:

    Workbook workbook = new Workbook("Book1.xlsx");
     //Get the farthest (last) row's index (zero-based) which contains data.
     int lastRowIndex = workbook.Worksheets[0].Cells.MaxDataRow;

     //Get the farthest (last) column's index (zero-based) which contains data.
     int lastColIndex = workbook.Worksheets[0].Cells.MaxDataColumn;
......

Hope, this helps a bit.

PS. I am working as Support developer/ Evangelist at Aspose.

edited Apr 08 '22 at 18:51

answered May 26 '20 at 19:29

Amjad Sahi

1,813
1
10
15

Thanks Amjad! That worked for me! I did a workaround before your answer which was to check the first null/whitespace that I encountered, then break the looping of rows. But your solution is a much better way.Glad to know that it is easy to reach an Aspose expert here. Cheers! – dantz May 27 '20 at 23:10
@amjad-sahi I believe you have an error in your lastRowIndex = line, as Cells() is invalid, but Cells works (and would match the lastColIndex = line). – DJFriar Apr 08 '22 at 15:46
1

@DJFriar you are spot on, I have corrected the typo/mistake in the sample code of the reply. – Amjad Sahi Apr 08 '22 at 18:52

score 0 · Answer 2 · answered Nov 29 '22 at 08:12

I used this but get wrong column count:

var dataTable = worksheet.Cells.ExportDataTable(0, 0,worksheet.Cells.Rows.Count, worksheet.Cells.Columns.Count, options);

Instead of worksheet.Cells.Rows.Count and worksheet.Cells.Rows.Count use these : worksheet.Cells.MaxRow + 1 and worksheet.Cells.MaxColumn + 1

This works correctly :

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

ASPOSE: How to correctly get Total Rows in Worksheet

2 Answers2