Repository Public
1"""Recalculates formulas with headless LibreOffice and returns typed results.23LibreOffice runs with a private profile forced to the en-US locale and "always recalculate on load".4Results are read from the raw sheet XML so numbers are never converted to dates.5"""6import os7import re8import shutil9import subprocess10import tempfile11import zipfile12import xml.etree.ElementTree as ET1314import openpyxl1516SOFFICE = os.environ.get("SOFFICE", "/opt/homebrew/bin/soffice")17NS = {"m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"}1819# functions that need the _xlfn. prefix in an xlsx file20XLFN = ["TEXTBEFORE", "TEXTAFTER", "TEXTSPLIT", "ENCODEURL", "UNICHAR", "UNICODE", "NUMBERVALUE", "TEXTJOIN", "CONCAT",21 "ISOWEEKNUM", "DAYS"]22XLFN_RE = re.compile(r"(?<![A-Za-z0-9_.])(" + "|".join(XLFN) + r")\(")2324REGISTRY = """<?xml version="1.0" encoding="UTF-8"?>25<oor:items xmlns:oor="http://openoffice.org/2001/registry" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">26<item oor:path="/org.openoffice.Setup/L10N"><prop oor:name="ooSetupSystemLocale" oor:op="fuse"><value>en-US</value></prop></item>27<item oor:path="/org.openoffice.Setup/L10N"><prop oor:name="ooLocale" oor:op="fuse"><value>en-US</value></prop></item>28<item oor:path="/org.openoffice.Office.Linguistic/General"><prop oor:name="UILocale" oor:op="fuse"><value>en-US</value></prop></item>29<item oor:path="/org.openoffice.Office.Calc/Formula/Load"><prop oor:name="OOXMLRecalcMode" oor:op="fuse"><value>0</value></prop></item>30<item oor:path="/org.openoffice.Office.Calc/Formula/Load"><prop oor:name="ODFRecalcMode" oor:op="fuse"><value>0</value></prop></item>31</oor:items>32"""333435def to_file_formula(formula):36 return "=" + XLFN_RE.sub(lambda m: "_xlfn." + m.group(1) + "(", formula)373839def recalc(formulas, profile="/tmp/lo_profile_functions"):40 """formulas: list of Excel formulas without '='. Returns a list of (type, value):41 ('n', float) | ('s', str) | ('b', bool) | ('e', '#VALUE!')."""42 work = tempfile.mkdtemp(prefix="orix_lo_")43 try:44 src = os.path.join(work, "in.xlsx")45 wb = openpyxl.Workbook()46 ws = wb.active47 ws.title = "Sheet1"48 for r, f in enumerate(formulas, start=1):49 ws.cell(row=r, column=1, value=to_file_formula(f))50 wb.save(src)51 user = os.path.join(profile, "user")52 os.makedirs(user, exist_ok=True)53 with open(os.path.join(user, "registrymodifications.xcu"), "w") as fh:54 fh.write(REGISTRY)55 outdir = os.path.join(work, "out")56 subprocess.run([SOFFICE, f"-env:UserInstallation=file://{profile}", "--headless", "--norestore",57 "--convert-to", "xlsx:Calc MS Excel 2007 XML", "--outdir", outdir, src],58 check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL, timeout=900)59 with zipfile.ZipFile(os.path.join(outdir, "in.xlsx")) as z:60 shared = []61 if "xl/sharedStrings.xml" in z.namelist():62 root = ET.fromstring(z.read("xl/sharedStrings.xml"))63 for si in root.findall("m:si", NS):64 shared.append("".join(t.text or "" for t in si.iter("{%s}t" % NS["m"])))65 sheet = ET.fromstring(z.read("xl/worksheets/sheet1.xml"))66 results = {}67 for c in sheet.iter("{%s}c" % NS["m"]):68 ref = c.get("r")69 if not ref.startswith("A"):70 continue71 row = int(ref[1:])72 t = c.get("t", "n")73 v = c.find("m:v", NS)74 text = v.text if v is not None and v.text is not None else ""75 if t == "s":76 results[row] = ("s", shared[int(text)])77 elif t in ("str", "inlineStr"):78 results[row] = ("s", text)79 elif t == "b":80 results[row] = ("b", text == "1")81 elif t == "e":82 results[row] = ("e", text)83 elif text == "":84 results[row] = ("s", "")85 else:86 results[row] = ("n", float(text))87 return [results.get(i, ("s", "")) for i in range(1, len(formulas) + 1)]88 finally:89 shutil.rmtree(work, ignore_errors=True)90