Ask Your Question
0

Is there a way to manipulate Calc document data programmatically?

asked 2018-06-16 11:22:56 +0100

Semisonic gravatar image

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!

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
1

answered 2018-06-16 12:29:43 +0100

Lupp gravatar image

updated 2018-06-16 12:44:28 +0100

  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/a... (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. ...
edit flag offensive delete link more
0

answered 2018-06-16 17:42:44 +0100

Jim K gravatar image

Python is one of the best languages for working with LibreOffice. A tutorial is at http://christopher5106.github.io/offi....

Other links:

edit flag offensive delete link more
0

answered 2018-06-20 07:36:57 +0100

Xoristzatziki gravatar image

updated 2018-06-20 07:39:13 +0100

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-06-16 11:22:56 +0100

Seen: 393 times

Last updated: Jun 20 '18