Automate LibreOffice Calc from Python with UNO

openpyxl and XlsxWriter write files; they do not run a spreadsheet. They cannot recalculate formulas, they do not adjust references when rows are inserted or deleted, and they cannot print to PDF. On Windows, those jobs go to Excel through COM. On a Linux server, or anywhere without an Excel licence, LibreOffice can do them: it ships a full spreadsheet engine that Python can drive through UNO, LibreOffice's component interface. The soffice --convert-to command covers one-shot conversions; UNO is for everything in between — open a workbook, change it with the engine's own rules, recalculate, save and export. This page builds a small, reusable session helper and uses it on a real task. It belongs to Handling Excel File Formats and Conversions.

Prerequisites

LibreOffice must be installed (soffice on the PATH). The uno module that talks to it comes with LibreOffice, not from PyPI, and it is built for one specific Python:

• Linux: install your distribution's python3-uno package; it works with the system python3 (/usr/bin/python3). A virtual environment only sees it if created with python3 -m venv --system-site-packages.
• Windows and macOS: use the Python bundled with LibreOffice (program\python.exe on Windows, LibreOffice.app/Contents/Resources/python on macOS).

A quick check is python3 -c "import uno"; an ImportError means you are running a Python that cannot see LibreOffice's module. budget.xlsx is a five-line budget with a SUM total and a share calculated from it.

Step 1 — Start soffice and connect

LibreOffice runs as a separate process that listens for UNO connections. A named pipe avoids picking a TCP port, and a private profile directory keeps the job from clashing with a desktop LibreOffice or another job:

import contextlib
import os
import shutil
import subprocess
import tempfile
import time
import uuid

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

def props(**kwargs):
    out = []
    for name, value in kwargs.items():
        p = PropertyValue()
        p.Name, p.Value = name, value
        out.append(p)
    return tuple(out)

@contextlib.contextmanager
def calc_session(timeout=30):
    pipe = f"calc_{uuid.uuid4().hex}"
    profile = tempfile.mkdtemp(prefix="lo-profile-")
    proc = subprocess.Popen([
        "soffice", "--headless", "--invisible", "--nologo", "--norestore",
        f"-env:UserInstallation={uno.systemPathToFileUrl(profile)}",
        f"--accept=pipe,name={pipe};urp;",
    ])
    local = uno.getComponentContext()
    resolver = local.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", local)
    deadline = time.monotonic() + timeout
    while True:
        try:
            ctx = resolver.resolve(f"uno:pipe,name={pipe};urp;StarOffice.ComponentContext")
            break
        except Exception:
            if time.monotonic() > deadline or proc.poll() is not None:
                proc.kill()
                raise RuntimeError("LibreOffice did not start")
            time.sleep(0.3)
    desktop = ctx.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", ctx)
    try:
        yield desktop
    finally:
        try:
            desktop.terminate()
        except Exception:
            pass                       # the bridge drops as soffice exits
        try:
            proc.wait(timeout=timeout)
        except subprocess.TimeoutExpired:
            proc.kill()
        shutil.rmtree(profile, ignore_errors=True)

def open_doc(desktop, path):
    url = uno.systemPathToFileUrl(os.path.abspath(path))
    return desktop.loadComponentFromURL(url, "_blank", 0, props(Hidden=True))

def save_as(doc, path, filter_name):
    doc.storeToURL(uno.systemPathToFileUrl(os.path.abspath(path)), props(FilterName=filter_name))

The helper starts soffice headless, retries the connection until the process is ready (startup takes a second or two), and guarantees that LibreOffice is shut down — and its temporary profile removed — even if the job fails half way — a leftover soffice process holds the profile lock and makes the next run hang. desktop.terminate() usually tears down the connection while the call is still in flight, so the exception it may raise is expected and ignored.

Step 2 — Delete a row the way Excel would

The budget has a line that should go, "Obsolete licence" on row 3. With openpyxl, delete_rows moves the cells but leaves =SUM(B2:B5) untouched, so the total ends up including the empty row below — and =B5/B6 points at the wrong cells, as shown in Delete Empty Rows and Columns with openpyxl. LibreOffice's engine adjusts every reference, exactly as Excel does when you delete a row by hand:

from lo_session import calc_session, open_doc, save_as

with calc_session() as desktop:
    doc = open_doc(desktop, "budget.xlsx")
    sheet = doc.Sheets.getByName("Budget")
    total = sheet.getCellRangeByName("B6")
    print("before:", total.getFormula(), "=", total.getValue())

    for row in range(sheet.getRows().getCount()):
        if sheet.getCellByPosition(0, row).getString() == "Obsolete licence":
            sheet.getRows().removeByIndex(row, 1)       # zero-based row index, count
            break

    total = sheet.getCellRangeByName("B5")
    share = sheet.getCellRangeByName("B6")
    print("after: ", total.getFormula(), "=", total.getValue(), "|", share.getFormula())

    sheet.getCellRangeByName("B4").setValue(1200)       # travel budget raised
    doc.calculateAll()
    print("recalculated total:", total.getValue(), "| travel share:", round(share.getValue(), 4))

    save_as(doc, "budget_updated.xlsx", "Calc Office Open XML")
    save_as(doc, "budget_updated.pdf", "calc_pdf_Export")
    doc.close(True)

/usr/bin/python3 update_budget.py

Before the deletion the total reads =SUM(B2:B5) = 21,860. After removing row 3 the formula has become =SUM(B2:B4) and the share =B4/B5 — LibreOffice rewrote both references. Raising the travel budget and calling calculateAll() updates the total and the share, and the document is saved as .xlsx (filter Calc Office Open XML) and exported as PDF (filter calc_pdf_Export) from the same open document.

Step 3 — Check the saved workbook

The output is an ordinary .xlsx, so the verification can run in any Python, including a virtual environment without uno:

from openpyxl import load_workbook

formulas = load_workbook("budget_updated.xlsx")["Budget"]
values = load_workbook("budget_updated.xlsx", data_only=True)["Budget"]
for row in range(2, 7):
    print(formulas.cell(row, 1).value, "|", formulas.cell(row, 2).value, "|", values.cell(row, 2).value)

The formulas read =SUM(B2:B4) and =B4/B5, and the data_only load sees real cached results — 21,800 and roughly 5.5% — because LibreOffice calculated and stored them. That is the property that makes a UNO pass useful at the end of a pipeline: files written by openpyxl have no cached values, so other tools read their formulas as empty; after a LibreOffice round trip they read numbers. Recalculation on its own is covered in Recalculate Excel Formulas Without Excel in Python.

Step 4 — Useful calls for report jobs

The Calc API is large; a handful of calls cover most report automation:

getDataArray and setDataArray move a whole range in one call and are much faster than cell-by-cell access over the bridge, which pays a round trip per call. Sheet and column indexes are zero-based, unlike Excel's 1-based numbering.

Step 5 — Run jobs safely in parallel

Each calc_session() starts its own soffice with its own profile directory and pipe name, so two jobs on the same machine do not collide. Without a separate profile, a second LibreOffice started with the default profile hands its work to the first instance and exits, and a script waiting for its own process gets confused. Keep sessions short — open, edit, save, close — rather than holding one LibreOffice open for hours; long-lived instances accumulate memory and a crash takes every pending document with it. On servers that run many conversions, a queue with one or two workers is more reliable than starting dozens of instances at once.

Performance notes

Starting LibreOffice costs one to two seconds, and the bridge adds a round trip to every call, so the slow pattern is a session per file with thousands of single-cell calls. Batch work inside one session — open several workbooks in turn before terminating — and move data in blocks with getDataArray/setDataArray. In the example above the whole run, including startup, two saves and shutdown, takes a few seconds; the spreadsheet work itself is a small fraction of that.

Common pitfalls

• ImportError: No module named 'uno'. You are running a Python that cannot see LibreOffice's module; use the system Python with python3-uno, or LibreOffice's bundled Python.
• Hanging on start. A stale soffice holding the profile lock; use a fresh profile directory per session and always shut down in a finally.
• File paths. UNO wants file:// URLs; convert with uno.systemPathToFileUrl(os.path.abspath(path)).
• Saving over the input. storeToURL writes a copy; use a new name and keep the original until the output is checked.
• Expecting Excel-only features. Some newer Excel functions and Power Query connections are not supported by LibreOffice; test the workbooks you depend on.

Conclusion

When a job needs a real spreadsheet engine — reference-aware row edits, recalculation with stored results, PDF export — but Excel is not available, drive LibreOffice Calc through UNO: start a headless soffice on a private pipe and profile, connect, open the workbook hidden, edit it with the Calc API, call calculateAll(), save with storeToURL to .xlsx and PDF, and always terminate the process. Run it with a Python that can import uno, and keep each session short.

Frequently asked questions

How do I control LibreOffice Calc from Python? Start soffice in headless mode with an --accept argument, connect with uno.getComponentContext() and a UnoUrlResolver, get the Desktop service, and open documents with loadComponentFromURL. Cells, sheets and saving are then available through the Calc API.

Why can't my virtual environment import uno? The uno module is installed by LibreOffice or the python3-uno package for one specific interpreter, not from PyPI. Use that interpreter, or create the virtual environment with --system-site-packages so it can see the system package.

Does LibreOffice update formulas when rows are deleted through UNO? Yes. removeByIndex on sheet.getRows() deletes rows through the spreadsheet engine, which rewrites references such as SUM(B2:B5) to SUM(B2:B4), just as Excel does. openpyxl does not.

How do I save an xlsx and a PDF from LibreOffice in Python? Call doc.storeToURL with a file URL and a FilterName property: "Calc Office Open XML" for .xlsx and "calc_pdf_Export" for PDF.

Related

• Up one level: Handling Excel File Formats and Conversions — converting and transforming workbooks.
• Convert Excel to PDF on Linux with LibreOffice — the one-command conversion route.
• Automating Excel with COM and pywin32 — the same kind of control over Excel on Windows.
• Insert and Delete Rows and Columns with openpyxl — what changes without a spreadsheet engine.