I need to find out how many rows are defined in a spreadsheet sheet. I am writing a program to read the contents of a sheet into an array and I need to know when to stop reading. I want to be able to have blank rows in the sheet, and I don’t want to have to use a special code to indicate the end. If I just keep reading rows, it runs for a very long time and probably ends with an exception (I don’t have the patience to find out).
The Excel spreadsheet SDK (actually Apache POI) seems to have a couple of functions that return the value I need: getLastRowNum() (which (I think) gives the last row that has anything written into it), and getPhysicalNumberOfRows() (which gives the last row that has data or is initialized with a format or something).
I’ve spent an hour looking through the SDK documentation and poking around with MRI but haven’t found a clue. Any suggestions?
Thank you!
-jimc
SheetCount = ThisComponent.Sheets(0).RangeAddress.EndRow +1
Thanks, but that returns the maximum number of rows in the spreadsheet, which is 1048575.
What I need is the maximum row number of the last row that’s been used in the spreadsheet; “used” here means that has ever had something done to it: data added, cells formatted, row height adjusted, etc. In the spreadsheet I am using, that number is 445.
I know that something in Calc knows this, because it is used in sizing the scrollbar at the right of the view window. Maybe it’s used internally but not made publicly available. I’m not ready to start trying to look at the source code to see how it works, though… 
-jimc
Function getUsedRange(oSheet)
REM used data area of a sheet
Dim oCursor1, oCursor2, addr
oCursor1 = oSheet.createCursorByRange(oSheet)
oCursor1.gotoStart()
addr = oCursor1.getRangeAddress()
oCursor2 = oSheet.createCursorByRange(oSheet)
oCursor2.gotoEnd()
addr.EndColumn = oCursor2.RangeAddress.EndColumn
addr.EndRow = oCursor2.RangeAddress.EndRow
getUsedRange = oSheet.getCellRangeByPosition(addr.StartColumn, addr.StartRow, addr.EndColumn, addr.EndRow)
End Function
Function getUsedRange2(oSheet)
REM include formattings
Dim oCursor
oCursor = oSheet.createCursor()
oCursor.gotoStartOfUsedArea(False)
oCursor.gotoEndOfUsedArea(True)
getUsedRange2 = oCursor
End Function
Function getCurrentRegion(oRange)
REM adjacent non-blanks around a given cell or range
Dim oCursor
oCursor = oRange.getSpreadSheet.createCursorByRange(oRange)
oCursor.collapseToCurrentRegion
getCurrentRegion = oCursor
End FunctionThanks for everyone’s suggestions. Using them I was able to get the value I needed. It took some fancy footwork with casting (as is usually the case with the C# interface to the SDK), but here is a code snippet that works:
// get the last used spreadsheet row (zero-based)
XSheetCellCursor XSCC = XSS.createCursor();
((XUsedAreaCursor)XSCC).gotoEndOfUsedArea(true);
XCellRangeAddressable XCRA = (XCellRangeAddressable)XSCC;
CellRangeAddress CRA = XCRA.getRangeAddress();
int nrlst = CRA.EndRow;
Console.WriteLine("nrlst="+nrlst);
I’m tuning up my program to get it to work the way I want, and to find and kill any lingering bugs.
I was thinking of posting a trimmed down version of my program, but decided that that would be a lot of work for not much value. Rather I’ll post the working version of the program with a trimmed down data set, and a detailed description of what it does and how it works (plus lots of comments in the code of course). It should be a simple matter for others to copy what they need out of it while ignoring what is specific to my application.
Thanks again to all!
-jimc
Don’t know in Apache POI (but getLastRowNum() sounds fitting), with UNO API it would be something like this
Function getLastUsedDataRow()
Sheet = ThisComponent.CurrentController.ActiveSheet
CellCur = Sheet.createCursor()
CellCur.gotoEndOfUsedArea(False)
getLastUsedDataRow = CellCur.RangeAddress.EndRow
End Function
Sub Main
print getLastUsedDataRow() + 1
End Sub
See
With Python:
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.CurrentController.ActiveSheet
cursor = sheet.createCursor()
cursor.gotoEndOfUsedArea(True)
print(cursor.RangeAddress.EndRow)