SPB Git

Repository Public

common.py 6.8 KB 162 lines python

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
Files main
Menu

Theme