Repository Public
1"""Shared helpers of the reference-fixture generators of FunctionsNumeric.23Usage (from this directory):45 /opt/homebrew/bin/python3 gen_libreoffice.py # LibreOffice-recalculated references6 /opt/homebrew/bin/python3 gen_scipy.py # scipy / mpmath references78Each generator writes JSON fixtures into ../Fixtures. A fixture holds the data sheets, and a list of9cases {f: formula, e: expected, kind: num|text|bool|err|complex, tol, abs}. The Swift tests load the10sheets into the engine, evaluate every formula on an extra sheet named "Calc" and compare.11"""12import json13import os14import re15import shutil16import subprocess17import tempfile1819import openpyxl2021HERE = os.path.dirname(os.path.abspath(__file__))22FIXTURES = os.path.normpath(os.path.join(HERE, "..", "Fixtures"))23SOFFICE = "/opt/homebrew/bin/soffice"24PROFILE = "file:///tmp/lo_profile_numeric"2526# Functions that must carry the _xlfn. prefix in an .xlsx file (Excel 2010+ additions).27XLFN = set("""28ACOT ACOTH AGGREGATE ARABIC BASE BETA.DIST BETA.INV BINOM.DIST BINOM.DIST.RANGE BINOM.INV BITAND BITLSHIFT BITOR29BITRSHIFT BITXOR CEILING.MATH CEILING.PRECISE CHISQ.DIST CHISQ.DIST.RT CHISQ.INV CHISQ.INV.RT CHISQ.TEST COMBINA30CONFIDENCE.NORM CONFIDENCE.T COT COTH COVARIANCE.P COVARIANCE.S CSC CSCH DECIMAL ERF.PRECISE ERFC.PRECISE31EXPON.DIST F.DIST F.DIST.RT F.INV F.INV.RT F.TEST FLOOR.MATH FLOOR.PRECISE FORECAST.LINEAR FORMULATEXT GAMMA32GAMMA.DIST GAMMA.INV GAMMALN.PRECISE GAUSS HYPGEOM.DIST IFNA ISFORMULA LOGNORM.DIST LOGNORM.INV33MAXIFS MINIFS MODE.MULT MODE.SNGL MUNIT NEGBINOM.DIST NORM.DIST NORM.INV NORM.S.DIST NORM.S.INV PERCENTILE.EXC34PERCENTILE.INC PERCENTRANK.EXC PERCENTRANK.INC PERMUTATIONA PHI POISSON.DIST QUARTILE.EXC QUARTILE.INC RANK.AVG35RANK.EQ SEC SECH SHEET SHEETS SKEW.P STDEV.P STDEV.S T.DIST T.DIST.2T T.DIST.RT T.INV T.INV.2T T.TEST VAR.P VAR.S36WEIBULL.DIST XOR Z.TEST RANDARRAY37""".split())3839ERRORS = {"#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#N/A"}40_NAME = re.compile(r"(?<![A-Za-z0-9_.\"])([A-Z][A-Z0-9]*(?:\.[A-Z0-9]+)*)\(")414243def to_xlsx_formula(f):44 """Adds the _xlfn. prefixes Excel/LibreOffice expect in files (string literals are left alone)."""45 out = []46 for i, part in enumerate(f.split('"')):47 if i % 2 == 0:48 part = _NAME.sub(lambda m: ("_xlfn." + m.group(1) if m.group(1) in XLFN else m.group(1)) + "(", part)49 out.append(part)50 return '"'.join(out)515253def ensure_profile_locale():54 """Forces the en-US locale in the private LibreOffice profile: with a decimal-comma system55 locale LibreOffice would not read "7.5" inside criteria strings as a number."""56 root = PROFILE.replace("file://", "")57 path = os.path.join(root, "user", "registrymodifications.xcu")58 items = "".join(59 f'<item oor:path="/org.openoffice.Setup/L10N"><prop oor:name="{n}" oor:op="fuse"><value>en-US</value></prop></item>\n'60 for n in ("ooSetupSystemLocale", "ooLocale"))61 if not os.path.exists(path):62 os.makedirs(os.path.dirname(path), exist_ok=True)63 with open(path, "w") as fh:64 fh.write('<?xml version="1.0" encoding="UTF-8"?>\n<oor:items xmlns:oor="http://openoffice.org/2001/registry" '65 'xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">\n'66 + items + "</oor:items>\n")67 return68 text = open(path).read()69 if 'ooSetupSystemLocale" oor:op="fuse"><value>en-US' not in text:70 with open(path, "w") as fh:71 fh.write(text.replace("</oor:items>", items + "</oor:items>"))727374def recalc(sheets, formulas):75 """Writes the data sheets and one formula per row on a "Calc" sheet, recalculates with76 LibreOffice headless and returns the computed values."""77 ensure_profile_locale()78 wb = openpyxl.Workbook()79 wb.remove(wb.active)80 for sh in sheets:81 ws = wb.create_sheet(sh["name"])82 for a1, v in sh["cells"].items():83 if v is not None:84 ws[a1] = v85 for a1, f in sh.get("formulas", {}).items():86 ws[a1] = to_xlsx_formula(f)87 for r in sh.get("hiddenRows", []):88 ws.row_dimensions[r].hidden = True89 ws = wb.create_sheet("Calc")90 for i, f in enumerate(formulas):91 ws.cell(row=i + 1, column=1, value=to_xlsx_formula(f))92 tmp = tempfile.mkdtemp(prefix="fn_lo_")93 try:94 src = os.path.join(tmp, "in.xlsx")95 out = os.path.join(tmp, "out")96 os.makedirs(out)97 wb.save(src)98 subprocess.run([SOFFICE, "-env:UserInstallation=" + PROFILE, "--headless", "--convert-to", "xlsx",99 "--outdir", out, src], check=True, capture_output=True, timeout=300)100 res = openpyxl.load_workbook(os.path.join(out, "in.xlsx"), data_only=True)["Calc"]101 return [res.cell(row=i + 1, column=1).value for i in range(len(formulas))]102 finally:103 shutil.rmtree(tmp, ignore_errors=True)104105106def classify(v):107 if isinstance(v, bool):108 return "bool"109 if isinstance(v, (int, float)):110 return "num"111 if isinstance(v, str) and (v in ERRORS or v.startswith("Err:") or v.startswith("#")):112 return "err"113 return "text"114115116def write_fixture(name, source, sheets, cases):117 os.makedirs(FIXTURES, exist_ok=True)118 path = os.path.join(FIXTURES, name + ".json")119 with open(path, "w") as fh:120 json.dump({"name": name, "source": source, "sheets": sheets, "cases": cases}, fh, indent=1, ensure_ascii=False)121 print(f"{name}: {len(cases)} cases -> {os.path.relpath(path, HERE)}")122123124def libreoffice_fixture(name, sheets, specs, overrides=None):125 """specs: list of formulas or (formula, options) with options among tol, abs, kind.126 overrides: {formula: (excel_value, note)} for documented LibreOffice/Excel differences."""127 overrides = overrides or {}128 norm = [(s, {}) if isinstance(s, str) else (s[0], dict(s[1])) for s in specs]129 seen = set()130 uniq = []131 for f, o in norm:132 if f not in seen:133 seen.add(f)134 uniq.append((f, o))135 values = recalc(sheets, [f for f, _ in uniq])136 cases, dropped = [], []137 for (f, o), v in zip(uniq, values):138 note = None139 if f in overrides:140 v, note = overrides[f]141 if v is None:142 v = ""143 if isinstance(v, str) and v == "#NAME?":144 dropped.append(f)145 continue146 kind = o.pop("kind", None) or classify(v)147 if classify(v) == "err":148 kind = "err"149 o.pop("tol", None)150 o.pop("abs", None)151 case = {"f": f, "e": v, "kind": kind}152 case.update(o)153 if note:154 case["note"] = note155 if kind == "err":156 case["exact"] = True157 cases.append(case)158 if dropped:159 print(f" {name}: dropped (unknown to LibreOffice): {dropped}")160 write_fixture(name, "libreoffice", sheets, cases)161 return cases162