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.
I should add that I am using .Net 1.1 and Aspose.Cells 4.9.0.0
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Please try Worksheet.Cells.MaxRow to get 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,