SPB Git

Repository Public

lo_harness.py 8.5 KB 191 lines python

1"""Shared harness for the reference-fixture generators.23A fixture is one worksheet of typed data plus a list of formulas. The formulas are written with4openpyxl, recalculated headlessly by LibreOffice (private profile) and the computed values are read5back. Generators may attach an independently computed Python value ("py") to a case; the Swift test6then checks the engine against both.78Run with /opt/homebrew/bin/python3 (needs openpyxl; numpy/scipy for the financial generator).9"""10import json11import os12import re13import shutil14import subprocess15import tempfile1617import openpyxl18from openpyxl.utils import get_column_letter19from openpyxl.worksheet.formula import ArrayFormula2021SOFFICE = "/opt/homebrew/bin/soffice"22PROFILE = "file:///tmp/lo_profile_lookup"23FIXTURES = os.path.join(os.path.dirname(os.path.abspath(__file__)), "..", "Fixtures")2425XLFN = ["XLOOKUP", "XMATCH", "SORTBY", "UNIQUE", "SEQUENCE", "TAKE", "DROP", "CHOOSEROWS", "CHOOSECOLS", "EXPAND",26        "HSTACK", "VSTACK", "TOCOL", "TOROW", "WRAPROWS", "WRAPCOLS", "PDURATION", "RRI"]27XLWS = ["FILTER", "SORT"]2829BLOCK_ROWS, BLOCK_COLS = 12, 12       # largest array result a case may produce30FIRST_COL = 40                       # case cells start in column AN; data must stay left of it31ERRORS = {"#N/A", "#VALUE!", "#REF!", "#DIV/0!", "#NUM!", "#NAME?", "#NULL!"}323334def xlsx_formula(f):35    """Adds the _xlfn prefixes Excel's file format requires for newer functions."""36    for name in XLFN:37        f = re.sub(r"(?<![A-Za-z_.])%s\(" % name, "_xlfn.%s(" % name, f)38    for name in XLWS:39        f = re.sub(r"(?<![A-Za-z_.])%s\(" % name, "_xlfn._xlws.%s(" % name, f)40    return f414243class Fixture:44    def __init__(self, name, description, scalar=False):45        self.scalar = scalar          # scalar fixtures: one plain formula cell per case (fast, thousands of cases)46        self.name = name47        self.description = description48        self.data = {}        # "A1" -> number | str | bool ; str starting with "=" is a formula (e.g. "=NA()")49        self.cases = []5051    def set(self, a1, value):52        self.data[a1] = value5354    def column(self, col, values, start=1):55        for i, v in enumerate(values):56            if v is not None:57                self.data["%s%d" % (col, start + i)] = v5859    def row(self, row, values, start_col=1):60        for i, v in enumerate(values):61            if v is not None:62                self.data["%s%d" % (get_column_letter(start_col + i), row)] = v6364    def case(self, formula, **meta):65        """meta: py=<independent value>, tol=<relative tolerance>, lo=False (skip LibreOffice), note=..."""66        c = {"f": formula}67        c.update(meta)68        self.cases.append(c)69        return c7071    # -- LibreOffice round trip ------------------------------------------------------------72    def _recalculate(self):73        wb = openpyxl.Workbook()74        ws = wb.active75        ws.title = "Sheet1"76        for a1, v in self.data.items():77            ws[a1] = v78        lo_cases = [c for c in self.cases if c.get("lo", True)]79        for i, c in enumerate(lo_cases):80            r0 = 1 + i * (BLOCK_ROWS + 1)81            f = xlsx_formula(c["f"])82            if self.scalar:83                ws.cell(1 + i, FIRST_COL + 2, f)84                continue85            ws.cell(r0, FIRST_COL, "=ROWS(%s)" % f[1:])86            ws.cell(r0, FIRST_COL + 1, "=COLUMNS(%s)" % f[1:])87            ws.cell(r0, FIRST_COL + 2, f)       # plain (non-array) evaluation, used for scalar results88            top = "%s%d" % (get_column_letter(FIRST_COL + 4), r0)89            bottom = "%s%d" % (get_column_letter(FIRST_COL + 3 + BLOCK_COLS), r0 + BLOCK_ROWS - 1)90            ws[top] = ArrayFormula("%s:%s" % (top, bottom), f)91        tmp = tempfile.mkdtemp(prefix="fl_fix_")92        src = os.path.join(tmp, self.name + ".xlsx")93        wb.save(src)94        out = os.path.join(tmp, "out")95        subprocess.run([SOFFICE, "-env:UserInstallation=" + PROFILE, "--headless", "--convert-to", "xlsx", "--outdir", out, src],96                       check=True, capture_output=True, timeout=600)97        ws2 = openpyxl.load_workbook(os.path.join(out, self.name + ".xlsx"), data_only=True).active98        for i, c in enumerate(lo_cases):99            if self.scalar:100                c["expect"] = ws2.cell(1 + i, FIRST_COL + 2).value101                continue102            r0 = 1 + i * (BLOCK_ROWS + 1)103            rows, cols = ws2.cell(r0, FIRST_COL).value, ws2.cell(r0, FIRST_COL + 1).value104            scalar = ws2.cell(r0, FIRST_COL + 2).value105            if "shape" in c:106                rows, cols = c.pop("shape")107            if not isinstance(rows, (int, float)) or not isinstance(cols, (int, float)) or (rows == 1 and cols == 1):108                c["expect"] = scalar109            else:110                rows, cols = int(rows), int(cols)111                assert rows <= BLOCK_ROWS and cols <= BLOCK_COLS, "result too large for %s" % c["f"]112                items = [ws2.cell(r0 + r, FIRST_COL + 4 + k).value for r in range(rows) for k in range(cols)]113                c["expect"] = {"rows": rows, "cols": cols, "items": items}114        shutil.rmtree(tmp, ignore_errors=True)115116    def write(self, recalc=True):117        if recalc and any(c.get("lo", True) for c in self.cases):118            self._recalculate()119        for c in self.cases:120            if not c.get("lo", True):121                c["source"] = "python"122                assert "py" in c, "case without any reference: " + c["f"]123                c.setdefault("expect", c["py"])124            else:125                c["source"] = "libreoffice+python" if "py" in c else "libreoffice"126                # LibreOffice has no #CALC!/#NUM! in xlsx output: when the model only says "an error", keep it generic127                if c.get("py") == "#VALUE!" and is_error(c["expect"]):128                    c["expect"] = "#VALUE!"129                # LibreOffice's iterative solvers sometimes give up where a root exists: keep the Python reference only130                # (or stop on a non-root): the Python root, certified by a sign change, is then the only reference131                if isinstance(c.get("py"), (int, float)) and c.get("lo_may_fail", False) and (132                        is_error(c["expect"]) or not close(c["expect"], c["py"], c.get("lotol", c.get("tol", 1e-9)))):133                    c["source"] = "python (LibreOffice returned %s)" % c["expect"]134                    c["expect"] = c["py"]135                # documented LibreOffice deviations (reason given by the generator): Python reference only136                if "lo_dev" in c:137                    reason = c.pop("lo_dev")138                    if isinstance(c.get("py"), (int, float)) and not close(c["expect"], c["py"], c.get("lotol", c.get("tol", 1e-9))):139                        c["source"] = "python (LibreOffice deviates: %s)" % reason140                        c["lo_value"] = c["expect"]141                        c["expect"] = c["py"]142            c.pop("lo_may_fail", None)143            c.pop("lo_dev", None)144            c.pop("lo", None)145        doc = {"name": self.name, "description": self.description, "data": self.data, "cases": self.cases}146        path = os.path.join(FIXTURES, self.name + ".json")147        with open(path, "w") as fh:148            json.dump(doc, fh, indent=1, ensure_ascii=False)149        print("%s: %d cases" % (self.name, len(self.cases)))150        return doc151152153def grid(rows, cols, items):154    return {"rows": rows, "cols": cols, "items": list(items)}155156157def is_error(v):158    return isinstance(v, str) and v in ERRORS159160161def close(a, b, tol=1e-9):162    if isinstance(a, bool) or isinstance(b, bool):163        return a == b164    if isinstance(a, (int, float)) and isinstance(b, (int, float)):165        return abs(a - b) <= tol * max(1.0, abs(a), abs(b))166    if is_error(a) and is_error(b):167        return True168    if a in (None, "") and b in (None, ""):169        return True170    return a == b171172173def report_disagreements(doc, tol=1e-9):174    """Prints cases whose LibreOffice and Python references differ; returns them."""175    bad = []176    for c in doc["cases"]:177        if "py" not in c or c["source"] == "python":178            continue179        e, p = c["expect"], c["py"]180        if isinstance(e, dict) != isinstance(p, dict):181            ok = False182        elif isinstance(e, dict):183            ok = e["rows"] == p["rows"] and e["cols"] == p["cols"] and all(184                close(x, y, c.get("tol", tol)) or (x in (None, 0, "") and y in (None, 0, "")) for x, y in zip(e["items"], p["items"]))185        else:186            ok = close(e, p, c.get("lotol", c.get("tol", tol)))187        if not ok:188            bad.append(c)189            print("  LO/Python disagree: %s\n     LO=%r\n     PY=%r" % (c["f"], e, p))190    return bad191
Files main
Menu

Theme