Automation (Calc)

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen

I love automating stuff, and with LibreOffice (Calc specifically right now), that's quite possible.

Methods

Macros

Macros are scripting possibilities, built-in in the GUI of an app, like in Microsoft Office or in LibreOffice. They are mostly intended for automating simple repetetive tasks without requiring much programming skills - or any programming skills at all.

For all intends and purposes, when speaking about LibreOffice, macros and LibreOffice Basic are the same thing. As soon as they seem to deviate from one another, it's not a macro anymore. E.g.: A claim that a macro can be programmed in JavaScript, isn't a macro to me anymore, as it actually requires UNO and a bunch of stuff outside the GUI to do its magic.

See the section on LibreOffice Basic for more.

LibreOffice Basic

LibreOffice Basic is LibreOffice's built-in language, akin to VBA - Visual Basic for Applications. However, as VBA seems quite a complete language, LibreOffice Basic seems to have various limitations - I decided not to use it:

  • Not headless: LibreOffice Basic is not intended to run in headless mode: You more-or-less always need at least a bit of interaction with the GUI to do stuff - And that's exactly what I want to avoid
  • Limited? Around 2018, I gave LibreOffice a seriour try, but found it hard to use. Specifically, I wanted to unmerge merged cells in Calc and I had the impression that it isn't possible: Un-merged cells (Calc)
  • Difficult: I found it difficult to use the interface
  • New language? I would rather not learn a new niche programming language, especially if I could do this in e.g., Python.

soffice

soffice is the command line interface for LibreOffice. The soffice command allows users to perform various operations on LibreOffice documents without opening the graphical user interface (GUI). This command is useful for automating tasks such as document conversion, printing, and macro execution.

It's quite useful and easy to deploy, but probably also quite limited.

UNO

Universal Network Objects (UNO) is an object model, like COM and CORBA. Maybe akin to 'remote object orientation'. This is the LibreOffice API

UNO objects can be programmed against, with any language that supports the LibreOffice API. An impression:

  • LibreOffice Basic - Not headless
  • Python
  • C++
  • Java
  • PHP - Needs some extra jiggling
  • Not Bash - Lacks capabilities to directly interact with the LibreOffice API.

UNO is likely the most comprehensible way to interact with LibreOffice and its files - If you can't automate it in UNO, you probably can't automate it at all

LibreOffice SDK

The LibreOffice SDK provides tools, documentation, and examples to develop applications or extensions using C++ or Java (any other languages?).

File import scripts & sites

  • The file format of Calc files is open-source and the content of such a file is actually XML. Python and probably a bunch of other languages, have libraries to read these files - This might be quite an easy approach, especially since I'm not so sure that UNO is my friend
  • There are also sites which can do this job: Drag-&-drop the Calc file and download the resulting SQL code to recreate a database with all the content imported from the Calc file
  • This can also be done more efficiently: Use a Python script to read a Calc file and directly write it into a database.

Example: Adding content to a Calc file

  • Adding content (e.g., a cell with some content) in a Calc file, isn't really possible through soffice: It would require a macro that would subsequently be executed through soffice. It would require creating the macro, probably through the Calc GUI and that's what I would like to avoid
  • Doing this through UNO + Python, is a much cleaner approach.

Example: Unmerging cells in a Calc file

This was the pièce de resistance when I tried out LibreOffice Basic in 2018: Can I unmerge merged cells in an automated way? The conclusion was, that I couldn't. ChatGPT thinks that in Python/UNO, this wouldn't be that hard:

import uno
from com.sun.star.sheet import XSheetCellRange
from com.sun.star.beans import PropertyValue

def connect_to_libreoffice():
    # Connect to the running LibreOffice instance
    local_context = uno.getComponentContext()
    resolver = local_context.ServiceManager.createInstanceWithContext(
        "com.sun.star.bridge.UnoUrlResolver", local_context)
    context = resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
    return context

def unmerge_cells(file_url, sheet_index, cell_range):
    context = connect_to_libreoffice()
    smgr = context.ServiceManager

    # Load the desktop
    desktop = smgr.createInstanceWithContext("com.sun.star.frame.Desktop", context)

    # Load the Calc document
    properties = (PropertyValue("Hidden", 0, True, 0),)
    document = desktop.loadComponentFromURL(file_url, "_blank", 0, properties)

    # Access the specified sheet
    sheets = document.getSheets()
    sheet = sheets.getByIndex(sheet_index)

    # Access the specified cell range
    cell_range_obj = sheet.getCellRangeByName(cell_range)
    cell_range_obj.splitRange()  # Unmerge the cells

    # Save and close the document
    document.store()
    document.close(True)

if __name__ == "__main__":
    import sys
    if len(sys.argv) != 4:
        print("Usage: unmerge_cells.py <file_url> <sheet_index> <cell_range>")
        sys.exit(1)

    file_url = sys.argv[1]
    sheet_index = int(sys.argv[2])
    cell_range = sys.argv[3]

    unmerge_cells(file_url, sheet_index, cell_range)

I didn't test it. Would be a good test of UNO's capabilities.

See also