I want to use LO calc to gather some calculation results and do some visualizing and reporting with it. I obtain my results from remote services/machines using python. In (so far) one case I get a sparsely populated matrix as a result. In order to not mix results, I must clear the corresponding LO calc cells, before populating them with the most recent result. Clearing cells does not really work the way I want it to. I tried several approaches. I can illustrate the problem best using Libre Office Basic:
The/My canonical way to clear a square of cells is
ThisComponent.Sheets(0).GetCellRangeByName("A1:Z25").ClearContents(7)
When I put the following in a procedure
sub ClearField
ThisComponent.Sheets(0).GetCellRangeByName("A1:Z25").ClearContents(7)
End sub
I can clear all fields by running the procedure manually, going through the Extras->Macros->... menu. It would seem the canonical approach works. However, it seems to not work when called indirectly. With a procedure like this:
sub DoWork
ClearField
End sub
calling DoWork manually will not clear the cells. Ultimately I want to call ClearField inside a Libre Office Basic Function, just before I call the Python script that fetches the new results.
With functions I can’t make it work at all. With
Function DoSomeWork
ThisComponent.Sheets(0).GetCellRangeByName("A1:Z25").ClearContents(7)
DoSomeWork = "foobar"
End Function
I would like to write =DoSomeWork() as the definition for some random cell. As a result I expect the cells A1 through Z25 to be empty and the random cell containing foobar. When I try this, the random cell indeed contains foobar but the cell range is NOT(!) cleared.
Why this strange behaviour and how can I call ClearField before fetching and rendering new data?