Problem with Row.Index

Aspose.Cells Product Family
1

Hi,

I am trying to find the last used row in a worksheet. With the attached spreadsheet, I tried the following code:

---------------------
Dim Excel As New Aspose.Cells.Workbook
Dim Worksheet As Aspose.Cells.Worksheet
Dim NextRow As Integer = -1

Excel.Open(“Sample.xls”)
Worksheet = Excel.Worksheets(0)

For i As Integer = 0 To Worksheet.Cells.Rows.Count - 1
MsgBox("Index: " & Worksheet.Cells.Rows(i).Index)
If Worksheet.Cells.Rows(i).Index > NextRow Then NextRow = Worksheet.Cells.Rows(i).Index
Next
NextRow += 1

Worksheet.Cells(NextRow, 0).PutValue(“Test”)
Excel.Save(“Test.xls”, Aspose.Cells.FileFormatType.Excel97To2003)
--------------------------

This gives the row indexes as 0 to 18

However, if I change the ‘For’ to a ‘For Each’ as in the following sample, then it works correctly and returns 0 to 16, 23, 24

---------------------------
Dim Excel As New Aspose.Cells.Workbook
Dim Worksheet As Aspose.Cells.Worksheet
Dim NextRow As Integer = -1

Excel.Open(“Sample.xls”)
Worksheet = Excel.Worksheets(0)

For Each r As Aspose.Cells.Row In Worksheet.Cells.Rows
MsgBox("Index: " & r.Index)
If r.Index > NextRow Then NextRow = r.Index
Next
NextRow += 1

Worksheet.Cells(NextRow, 0).PutValue(“Test”)
Excel.Save(“Test.xls”, Aspose.Cells.FileFormatType.Excel97To2003)
------------------------

So whilst there is a workaround, I am concerned that it seems to be inconsistant as I would have assumed both methods would work in the same way and I don’t want to find that with a later version my code breaks because something has changed with this.

2

I should add that I am using .Net 1.1 and Aspose.Cells 4.9.0.0

3

Hi,

Please try Worksheet.Cells.MaxRow to get the farthest row index with formatting and style and use Worksheet.Cells.MaxDataRow to get the farthest row index containing data instead of using Worksheet.Cells.Rows.Count.

Thank You & Best Regards,