Is there a way to manipulate Calc document data programmatically?

[Semisonic]

In a nutshell, I want to make a Calc table that processes the data I enter into a pre-defined set of columns, matches it against a pre-existing list of data fields and produces various statistical values, based on what was entered.

Basically, a typical usage scenario which can be achieved using formulas only. Formulas were my previous approach, and, while it worked, they were hard to write properly and looked ugly.

I want to implement all the data processing logic programmatically. That is, I want an API with methods like getCellValue(row, column) and setCellValue(row, column) and hooks like onCellValueUpdated(row,column) - the rest I’d code myself. Preferably in Python or C++, but any other language would do.

Is there some way to do it? And, if there is, could you please point me to the right direction? Thanks!

[Lupp]

1. Of course there is an API. On the other hand its paradigm is rather uncommon (Interfaces, Services mainly)
2. LibreOffice (as its predecessors) comes with Basic which
   2.a offers a few shortcuts concerning the access to LibO and document objects,
   2.b is an ordinary Basic dialect, and is not capable of speeding up things.
   More specific:
3. Data analysis based on access to single cells is likely to be highly inefficient.
   3.a You often can speed up things working with the .Data (specialized on numeric data) or the .DataArray property of complete ranges.
   3.a You may use compiled routines from a loadable library for time-critical processing if you know the details.
4. You can control LibO from any program developed in a General Purpose IDE, open / create / run documents…
   4.a I never did it “in production”. You may get better advice by someone being more experienced with this.
   4.b Just an example essay on the subject: https://www.freepascal.org/~michael/articles/openoffice1/openoffice.pdf (You should be able to replace Delphi with FreePascal/Lazarus e.g.)
5. Going to the extreme your Calc document will “only” be an IO-device for your data and a kind of batch-processor for your external programs.
   Back to Calc itself:
6. Having imported fresh data by Paste Special... e.g. you can trigger a script using the Content chamged event of the sheet. …

[jimk]

Python is one of the best languages for working with LibreOffice. A tutorial is at Interface-oriented programming in OpenOffice / LibreOffice : automate your office tasks with Python Macros.

Other links:

• Transfer from Basic to Python - Apache OpenOffice Wiki
• https://extensions.libreoffice.org/extensions/apso-alternative-script-organizer-for-python

[Xoristzatziki1]

For the moment the only way to use macros as user functions is the BASIC. These functions-macros receive the “current cell” and can be used to further process and return a “result” to that cell.

You can replace all previous formulas with a single function-BASICmacro (or more of them if processing is very diverted).

And, of course, these BASIC macros can call a python script embeded in the document, if needed.