Macro: how to loop over existing rows?

[Hi-Angel]

In LO Calc, I want for every row to process its cells, and modify them as needed. My problem is: I can’t figure out how do loop over rows. Suppose I have the following code:

#!python
import uno

# run libreoffice as:
# soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

def connect_to_lo():
    # get the uno component context from the PyUNO runtime
    localContext = uno.getComponentContext()
    resolver = localContext.ServiceManager.createInstanceWithContext(
        "com.sun.star.bridge.UnoUrlResolver", localContext )
    # connect to the running office
    ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
    smgr = ctx.ServiceManager
    desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
    return desktop.getCurrentComponent()

model = connect_to_lo()

Now, if I execute

print(model.CurrentController.ActiveSheet.Rows.Count)

I get 1048576. Wow! That’s a lot of rows for completely empty document! I guess Calc pre-creates rows (which is correct), and for some reason all of them counts as candidates for enumeration (which is definitely incorrect, number of rows strictly speaking should go to infinity, and none of them should be candidates). If I do for row in model.CurrentController.ActiveSheet.Rows: cycle, it really tries all of these pre-created empty rows.

Does anybody know a way to only loop over rows that really exist?

[anon73440385]

1048576 rows always exist and there are always 1024 columns (per sheet). I assume your question should read: How to determine programatically the number of the last row containing any data?

[Hi-Angel]

@anon73440385 well, yeah. I tried writing a code that simply loops over rows until the first row with all (or at least one) empty columns, but I haven’t even managed to find a way to extract cells from a given row. I can get a row, I can get a column from the row, but… I can’t find how to refer to individual cells in the row to implement such check upd: oh, yeah, and a column I can get from a row, for some reason, is not a cell, i.e. I can’t access its content.

[Hi-Angel]

Turns out: rows and columns in UNO are implemented as a CellRange. So question breaks to: 1. “How to loop over cells in a range?”, and 2. “How to make range of only existing cells”. Answers:

1. Ranges has a function getCellByPosition(), which accepts position relative to the range. So e.g. if you have a my_range that globally starts at {1, 1}, then by calling my_range.getCellByPosition(0,0) you’d access the cell that is in global coordinates at {1, 1}.
2. You can create cursor, which specifically has functions to navigate “used area”. With this, marking the “used” rectangle of the spreadsheet comes down to just a few lines of code, see function getUsedArea() below. Possible gotcha: if the first column is empty, then it won’t be in the range — keep that in mind for accessing cells with indices.

For reference, below is an example code in Python that connects to running on port 2002 instance of LO Calc, and then iterates over “used” rows, and prints contents of every leftmost cell.

#!python
import uno

# run libreoffice as:
# soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

def connectToLO():
    localContext = uno.getComponentContext()
    resolver = localContext.ServiceManager.createInstanceWithContext(
                                    "com.sun.star.bridge.UnoUrlResolver", localContext )
    ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
    smgr = ctx.ServiceManager
    desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
    return desktop.getCurrentComponent()

def getUsedArea(sheet):
    cursor = sheet.createCursor()
    cursor.gotoEndOfUsedArea(False)
    cursor.gotoStartOfUsedArea(True)
    return cursor

# applies f to every used row in the spreadsheet
def iterRows(sheet, f):
    used_range = getUsedArea(sheet)
    for row in used_range.Rows:
        f(row)

model        = connectToLO()
active_sheet = model.CurrentController.ActiveSheet
iterRows(active_sheet,
        lambda row: print(row.getCellByPosition(0, 0).String))