Repository Public
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