Background Python script for Calc?

[astein]

I want to write a background script that modify a Calc spreadsheet - in Python. It needs to maintain a state between invocations. But what I find out I cannot do that with a macro (LibreOffice macros launches a new Python environment each time -i.e. no state). (Explainer: I need to map between external ID and the row number in Calc).

In particular I looked at ScriptForge calc functions - providing the API for Calc.

Is there any way to have a long running Python script that runs in the background inside LibreOffice ?

If not, how could I cache my data (inside the LibreOffice process) - to have a quick key/value lookup from Python ? (basically a large key/value table, string => int)

Since there could come big chunks of data I’d really like this in-process way of manipulating the spreadsheet - as opposed to external TCP/socket alternative.

Thanks for pointers.

[karolus]

You talk around it quite a lot without describing the actual problem in concrete terms.
Maybe you should use a real database instead calc.

[astein]

I think I have explained it. I maintain an interactive spreadsheet. At times, I want to import (modify) lines/cells in this data based on delta files that are produced in a directory (by another app).

The other app does not know the line numbers in the Calc document (and they may change). However there is a shared ID column that both sides know. So the issue is to go from this external ID to a line number, and then insert the modified data in the right cell.

[joshua4]

Have you researched looking at LO not as the starting place where macros live, but as an API called from Python or Java? You can start at https://api.libreoffice.org/. I’m not a Python programmer, but the docs seem pretty clear that you can start a Java program and let it control LO. Not saying there aren’t limitations, but then the run is based on your Java process. (Or, Python).

[Ratslinger]

@astein

I looked at ScriptForge calc functions

I have also and have found many negative issues with it although I found one use for it with Python. Stick with uno API.

Edit: converted to comment.

Still don’t understand the process. On the surface the DB seems a good possibility and think the “other app” could write this. Can always import data into Calc from Base (using whatever DB).

@astein

interactive spreadsheet

is also a bit confusing. Is it receiving and sending data? If so it opens new questions.

[Villeroy]

@astein

If not, how could I cache my data (inside the LibreOffice process) - to have a quick key/value lookup from Python ? (basically a large key/value table, string => int)

A most simple relational database does that for millions of entries without a single line of Python code.

[astein]

Well, I do this in Calc for the simple reason that I need interactive spreadsheet functionality.
If I just needed to silently store my data it would be another question.

[Villeroy]

You don’t know Base.

[astein]

No I haven’t really worked directly with Base.

Are you saying that it can do interactive editing, with automatically updated formulas/relations between values - much like Calc ?

I am looking at Calc for a reason, as I want those interactive features, and to be able to set relationships between columns (and also sheets) that are automatically updated.

[Wanderer]

@astein

Are you saying that it can do interactive editing, with automatically updated formulas/relations between values -

There are forms to do interactive editing and through queries you may calculate derived values.

But you may also just drag a query from base to a table in calc, so the data is acessible in Calc. This database-area can be updateted with new data quickly.

If I read your question right all you want to store is a table for a VLookUp of lines, something a a database can also do quickly - in SQL you may need something like

UPDATE table SET value = expression WHERE condition

[astein]

It is true, I could implement a cache using Base.

[Villeroy]

Base can display lookup data in a subform with a click on the parent form. A subform shows all data related to the the parent form’s currently selected record set.
Setting up a filtered form based on entered criteria is another option.
PowerFilter.odb (155.1 KB)

[astein]

A few days later now…, after experimenting with LibreOffice scripting possibilities. (My original question was written on my first or second day exploring LibreOffice scripting).

When exploring listeners and event handling I found out that such scripts provide what I was asking for - the possibility of maintaining a state. For example, the script:

dbl_cnt = 0
def OnDoubleClick( oEvent ):
  global dbl_cnt
  dbl_cnt += 1
  print( "OnDoubleClick: " + str(dbl_cnt) ) 

will print increasing numbers (while as a directly invoked macro it would keep reinitializing the global variable). (I learnt this trying out sheet events).

So this was really the simplest, most efficient solution: to collect my logic in one script file, that is event powered and maintains state.

For the answers questioning my use of Calc for this - or doing this at all - those were not directions I had use of - or had asked for.

I learn a lot from forum posts… but this time experimenting and learning was what gave the result.