Iterating over SF_CalcReference Ranges using ScriptForge and Python

[jdkolassa]

I am a JavaScript developer learning Python, and to this end I’d like to craft a budget analysis tool inside a Python macro for LibreOffice calc. In the end product, I’ll be receiving a CSV file from an online finances app, and then I’ll use my Python script to iterate over the data, pick out the pieces I want to look at, and make a fancy chart and/or graph to tell me what I need at a glance. (Amazingly, my app doesn’t seem to have that. Weird…) I’ve been doing small code challenges/tests to work on each part of my project, and I’ve currently run into what I hope is a minor roadblock.

My thinking is that I can import the CSV into a Calc spreadsheet, then iterate over the first row (so “Sheet1.A1:K1” or whatever the last cell would be), and when I find the column I want, based on text inside the current cell, then I can take that entire column and start doing my work on it. The problem that I’ve run into is that Calc ranges are apparently not iterable. For instance, I’m using the following code to test my understanding of Python iterables:

from scriptforge import CreateScriptService

svc_ui = CreateScriptService('UI')
this_window = svc_ui.ActiveWindow
cDoc = svc_ui.GetDocument(this_window)

def looptest():
    testRange = cDoc.Range("$Sheet1.A1:K1")
    rangeType = type(testRange)
    cDoc.setValue('Sheet1.A5', str(rangeType))
    cDoc.setValue('Sheet1.A7', str(logging.debug(testRange)))

    for cell in testRange:
        cDoc.setValue(cell, "stuff")

When I do this, I receive the following error:

com.sun.star.uno.RuntimeException: Error during invoking function secondtest in module file:///C:/Users/Arcturus/AppData/Roaming/LibreOffice/4/user/Scripts/python/Experiments/CreateAndInsertCalc.py (<class ‘TypeError’>: ‘SF_CalcReference’ object is not iterable
File “C:\Program Files\LibreOffice\program\pythonscript.py”, line 915, in invoke
ret = self.func( *args )
File “C:\Users\Arcturus\AppData\Roaming\LibreOffice\4\user\Scripts\python\Experiments\CreateAndInsertCalc.py”, line 27, in secondtest
for cell in testRange:
)

(FWIW, str(rangeType) gives me <class 'scriptforge.SFDocuments.SF_CalcReference'> and logging.debug(testRange) gives me None but that’s probably due to my lack of Python understanding rather than a bug or error.)

Seems to me the issue is that ‘SF_CalcReference’ object is not iterable. Okay, but then what? I’d assume you would have to loop over cells in a range to perform many operations or tasks with Calc. Is there a specific method to do this with ranges? I’ve looked in the ScriptForge.Calc service and the ScriptForge.Array service but haven’t seen what I’d think would do it. I’ve also looked at the LibreOffice Module Wizards page, but so far I am too dumb to get anything out of it. Am I looking in the wrong place? Do I need to be examining the other services for the right method? Is this indeed due to a fundamental misunderstanding of Python (in which case, I need to hit the books again)? Is this something where I need to use LibreOffice Basic to accomplish this task?

I am of course going to continue searching for answers, but wanted to drop this question here in case there was something I was obviously missing.

Of course, it may be that this is just a terrible idea from the get-go, and I should not even be approaching LibreOffice Python (or LibreOffice itself!) for this project. If that’s the case, any suggestions would be appreciated – just that, since I am studying Python, I would like Python based ideas.

[rafaelhlima]

Indeed SF_CalcReference is not iterable, because it’s actually an object returned by Basic to a Python script.

Here’s a sample code for what I think you’re trying to implement:

from scriptforge import CreateScriptService

ui = CreateScriptService("UI")
bas = CreateScriptService("Basic")

# Here "lookup_range" is your full databse (including the header column)
def get_column_by_key(doc, lookup_range, key):
    # Looks up in the first row to see if any cell matches the "key"
    num_cols = doc.Width(lookup_range)
    num_rows = doc.Height(lookup_range)
    # First cell in the lookup range
    first_cell = doc.Offset(lookup_range, width=1, height=1)
    # Iterates over all cells in the fisrt row
    for i in range(num_cols):
        current_cell = doc.Offset(first_cell, 0, i)
        if doc.getValue(current_cell) == key:
            # Gets the address of the entire column
            result_col = doc.Offset(current_cell, height=num_rows)
            # Returns the found address (string) and the list with values
            return result_col, doc.getValue(result_col)
    # If it gets here, then the key was not found
    return None

# This is a test that calls the get_column_by_key function
def test_lookup_function(args=None):
    doc = ui.GetDocument(ui.ActiveWindow)
    s_range, values = get_column_by_key(doc, "Sheet1.A1:C8", "Some value")
    bas.MsgBox(f"{s_range}\n{values}")

g_exportedScripts = (test_lookup_function, )

The example above implements a general function get_column_by_key where you can provide a Calc range address (as string) and a key (numeric or string). The function returns the address of the column that matches the key as well as a list with the values in the column.

Then the test_lookup_function is a sample to show how the previous function can be called. It passes a lookup range “Sheet1.A1:C8” and uses the first row (Sheet1.A1:C1) as the range to look up for a value matching the key.

[karolus]

I thought SF was invented to make things easier

def main(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    source_range = doc.Sheets["Sheet1"]["A1:C8"]
    data = {key: column for key, *column in 
            zip(*sourcerange.DataArray)}
    print(data.get("some Value", None))

The essence is the fact that it took me longer to understand your code than to write the counterexample.

[rafaelhlima]

I simply created a step-by-step example of how the OP can achieve his goals using ScriptForge. We’re not on a race to write the same script with the smallest number of lines of code.

I don’t see why you are always so negative about ScriptForge.

[jdkolassa]

Thank you. This helps me understand how ScriptForge sits in the LibreOffice ecosystem and how the parts connect. When I was first reading about scripting with LibreOffice, the literature seemed to indicate that ScriptForge was intended to simplify Python scripting, so that’s where I started. Looking at this thread, I may need to adjust some assumptions and do some more research. But both of you have been very helpful.

[karolus]

just as I feared, beginners start “guessing” in SF code will of course fail grandly, and then they ask here……
you dont need SF especially with python.

def test(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    crange = doc.Sheets["Sheet1"]["A1:K1"]
    print(type(crange)) # <class> 'pyuno'
    crange.DataArray = [["stuff"] * len(crange.DataArray[0])]