xlsx

1#!/usr/bin/env python32"""Recalculate a workbook's formulas headlessly with LibreOffice.3 4openpyxl never computes formulas. This script shells out to `soffice`5(LibreOffice) to open the workbook, recalculate, and re-save it, so6cached formula results become available to `xlsx_read.py --data-only`7and `--formulas`.8 9Behavior:10  * soffice on PATH: converts the file to .xlsx in a temp dir (which11    recalculates all formulas) and replaces the original (or writes12    --out). Prints {"recalculated": true, ...} and exits 0.13  * soffice absent: prints {"recalculated": false, "reason": ...} with14    installation guidance and STILL exits 0 — callers can branch on the15    JSON instead of the exit code.16 17Note: LibreOffice recalculates .xlsx on load per its default18calculation settings; conversion re-saves with fresh cached values.19 20Usage:21  xlsx_recalc.py book.xlsx22  xlsx_recalc.py book.xlsx --out recalced.xlsx23  xlsx_recalc.py book.xlsx --timeout 12024"""25from __future__ import annotations26 27import argparse28import json29import shutil30import subprocess31import sys32import tempfile33from pathlib import Path34 35 36def count_cached(path):37    """Number of formula cells with a cached value present."""38    from openpyxl import load_workbook39    wb_f = load_workbook(path, data_only=False)40    wb_v = load_workbook(path, data_only=True)41    formulas = cached = 042    for name in wb_f.sheetnames:43        ws_f, ws_v = wb_f[name], wb_v[name]44        for row in ws_f.iter_rows():45            for cell in row:46                if isinstance(cell.value, str) and cell.value.startswith("="):47                    formulas += 148                    if ws_v[cell.coordinate].value is not None:49                        cached += 150    return formulas, cached51 52 53def main(argv=None):54    ap = argparse.ArgumentParser(55        description="Recalculate .xlsx formulas headlessly via LibreOffice.")56    ap.add_argument("file", help="path to .xlsx file")57    ap.add_argument("--out", help="output path (default: replace input)")58    ap.add_argument("--timeout", type=int, default=180,59                    help="seconds to wait for soffice (default 180)")60    args = ap.parse_args(argv)61 62    src = Path(args.file).resolve()63    if not src.exists():64        print(json.dumps({"ok": False, "error": f"no such file: {src}"}),65              file=sys.stderr)66        return 167 68    soffice = shutil.which("soffice")69    if not soffice:70        print(json.dumps({71            "ok": True, "recalculated": False,72            "reason": "LibreOffice (soffice) not found on PATH",73            "guidance": "Install LibreOffice (e.g. `apt install "74                        "libreoffice-calc` or `brew install --cask "75                        "libreoffice`), or open the file in Excel/"76                        "LibreOffice once and re-save it.",77        }, ensure_ascii=False))78        return 079 80    with tempfile.TemporaryDirectory() as tmp:81        proc = subprocess.run(82            [soffice, "--headless", "--calc", "--convert-to", "xlsx:Calc "83             "MS Excel 2007 XML", "--outdir", tmp, str(src)],84            capture_output=True, text=True, encoding="utf-8",85            timeout=args.timeout,86            env={"HOME": tmp, "PATH": Path(soffice).parent.as_posix()87                 + ":/usr/bin:/bin"})88        produced = Path(tmp) / (src.stem + ".xlsx")89        if proc.returncode != 0 or not produced.exists():90            print(json.dumps({"ok": False,91                              "error": "soffice conversion failed",92                              "stderr": proc.stderr.strip()[-500:]}),93                  file=sys.stderr)94            return 195        formulas, cached = count_cached(produced)96        dest = Path(args.out).resolve() if args.out else src97        shutil.copyfile(produced, dest)98 99    print(json.dumps({100        "ok": True, "recalculated": True, "output": str(dest),101        "formula_cells": formulas, "with_cached_values": cached,102    }, ensure_ascii=False))103    return 0104 105 106if __name__ == "__main__":107    try:108        sys.exit(main())109    except Exception as exc:  # noqa: BLE001110        print(json.dumps({"ok": False, "error": str(exc)}), file=sys.stderr)111        sys.exit(1)112