Repository Public
1#!/usr/bin/env python32"""Regenerates the function catalogue of docs/compatibility/formulas.md.34Inputs (all inside the repository):5 docs/compatibility/function-registry.json dump of FunctionRegistry.standard (see below)6 Packages/FunctionsNumeric/COMPATIBILITY.md per-function validation table7 Packages/FunctionsText/COMPATIBILITY.md "Known deviations" bullets8 Packages/FunctionsText/Tests/FunctionsTextTests/Fixtures/function_references.json (per-case source tags)9 Packages/FunctionsLookup/COMPATIBILITY.md per-group validation tables1011Output: the text between the markers12 <!-- BEGIN GENERATED CATALOGUE --> ... <!-- END GENERATED CATALOGUE -->13of docs/compatibility/formulas.md is replaced. Nothing else in that file is touched.1415function-registry.json is produced by a throw-away Swift executable that depends on16Packages/SheetEngine, SheetCore and the three Functions* packages:1718 ORIXEngine.bootstrap()19 // origin of each name: register each library into a fresh FunctionRegistry()20 // CoreBuiltins.register(into:), NumericFunctions.register(into:), TextFunctions.register(into:),21 // LookupFunctions.register(into:) + FinancialFunctions.register(into:)22 // then encode, for every spec of FunctionRegistry.standard.all:23 // name, category.rawValue, signature, summary, minArgs, maxArgs, isVolatile, packages24 // plus registryCount, ORIXEngine.functionCount, ORIXEngine.specialForms,25 // ORIXEngine.functionCountsByCategory(), perPackage counts.2627Build it outside the repository (swift build --scratch-path /tmp/...) and redirect its28standard output to docs/compatibility/function-registry.json, then run this script:2930 python3 docs/compatibility/make_formula_catalogue.py [--check]31"""32from __future__ import annotations3334import collections35import json36import re37import sys38from pathlib import Path3940ROOT = Path(__file__).resolve().parents[2]41HERE = Path(__file__).resolve().parent42TARGET = HERE / "formulas.md"43BEGIN, END = "<!-- BEGIN GENERATED CATALOGUE -->", "<!-- END GENERATED CATALOGUE -->"4445CATEGORY_ORDER = ["Math", "Trigonometry", "Statistical", "Financial", "Logical", "Text", "Date & Time",46 "Lookup & Reference", "Dynamic Array", "Database", "Engineering", "Information"]4748# Notes on the 24 functions registered by SheetCore itself (Packages/SheetCore/Sources/SheetCore/Engine/Builtins.swift).49# Each one was read in the source; they have no package COMPATIBILITY document.50CORE_NOTES = {51 "SUM": "Neumaier compensated summation.",52 "AVERAGE": "Compensated sum divided by the count; no numbers gives #DIV/0!.",53 "MIN": "No number at all gives 0.",54 "MAX": "No number at all gives 0.",55 "INDEX": "Reference form returns a reference. A 4th argument (area_num) is accepted and ignored: there are no multi-area references.",56 "INDIRECT": "A1 style only: `a1` = FALSE (R1C1) returns #REF!. Accepts a cell, range, defined name or structured reference.",57 "OFFSET": "Result outside the grid is #REF!.",58 "NOW": "One timestamp per recalculation pass (`EvalContext.now`).",59 "TODAY": "One timestamp per recalculation pass (`EvalContext.now`).",60 "RAND": "`SystemRandomNumberGenerator`; not seedable.",61 "ROW": "A multi-row reference returns a column array.",62 "COLUMN": "A multi-column reference returns a row array.",63}646566def cell(text: str) -> str:67 return text.replace("|", "\\|").replace("\n", " ").strip()686970def numeric_table() -> dict[str, tuple[str, str]]:71 out = {}72 for line in (ROOT / "Packages/FunctionsNumeric/COMPATIBILITY.md").read_text(encoding="utf-8").splitlines():73 match = re.match(r"\| `([A-Z0-9.]+)` \|([^|]*)\|([^|]*)\|([^|]*)\|([^|]*)\|([^|]*)\|\s*$", line)74 if not match:75 continue76 name, _, lo, independent, hand, remark = (part.strip() for part in match.groups())77 parts = []78 if lo != "–":79 parts.append(f"LibreOffice {lo}")80 if independent != "–":81 parts.append(independent)82 if hand != "–":83 parts.append(f"hand-written {hand}")84 out[name] = (", ".join(parts) if parts else "none listed", remark)85 return out868788def text_info(text_names: set[str]) -> dict[str, tuple[str, str]]:89 cases = json.loads((ROOT / "Packages/FunctionsText/Tests/FunctionsTextTests/Fixtures/function_references.json").read_text(encoding="utf-8"))90 counts: dict[str, collections.Counter] = collections.defaultdict(collections.Counter)91 for case in cases:92 counts[case["fn"]][case["src"]] += 193 labels = {"lo": "LibreOffice", "lo+py": "LibreOffice+Python", "py": "Python", "excel": "Excel-documented value"}94 document = (ROOT / "Packages/FunctionsText/COMPATIBILITY.md").read_text(encoding="utf-8")95 section = document.split("## Known deviations from Excel", 1)[1]96 bullets = [re.sub(r"\s+", " ", b).strip() for b in re.split(r"\n- ", "\n" + section.strip()) if b.strip()]97 notes: dict[str, list[str]] = collections.defaultdict(list)98 for bullet in bullets:99 if bullet.startswith("No result is validated"):100 continue101 tokens = set(re.findall(r"[A-Z][A-Z0-9.]+", bullet))102 hit = {name for name in text_names if name in tokens}103 if "REGEX" in tokens:104 hit |= {name for name in text_names if name.startswith("REGEX")}105 if bullet.startswith("Date text is parsed"):106 hit |= {"DATEVALUE", "TIMEVALUE", "VALUE"}107 for name in hit:108 notes[name].append(bullet)109 out = {}110 for name in text_names:111 c = counts.get(name)112 validation = ", ".join(f"{labels[k]} {c[k]}" for k in ("lo", "lo+py", "py", "excel") if c and c[k]) or "none listed"113 out[name] = (validation, " ".join(notes.get(name, [])))114 return out115116117def lookup_info(lookup_names: set[str]) -> dict[str, tuple[str, str]]:118 document = (ROOT / "Packages/FunctionsLookup/COMPATIBILITY.md").read_text(encoding="utf-8")119 out: dict[str, tuple[str, str]] = {}120 for line in document.splitlines():121 if not line.startswith("|") or line.startswith("|---"):122 continue123 cells = [c.strip() for c in line.strip().strip("|").split("|")]124 names = [n.strip() for n in re.split(r",|/", cells[0])]125 if not names or not all(n in lookup_names for n in names):126 continue127 group = "" if len(names) == 1 else f" (row shared by {', '.join(names)})"128 if len(cells) == 3: # lookup & dynamic-array tables: function | validation | notes129 for name in names:130 note = "" if cells[2] in {"—", "-"} else cells[2]131 out[name] = (cells[1] + group, note)132 elif len(cells) == 6: # financial table: function | XL | LO+PY | PY (LO≠) | other | notes133 def split(value: str, index: int) -> str:134 parts = [p.strip() for p in value.split("/")]135 return parts[index] if len(parts) == len(names) else value136 for index, name in enumerate(names):137 xl, lopy, py, other = (split(cells[k], index) for k in (1, 2, 3, 4))138 parts = []139 if xl and xl != "—":140 parts.append(f"Excel-computed data {xl}")141 if lopy:142 parts.append(f"LibreOffice+Python {lopy}")143 if py and py != "0":144 parts.append(f"Python only (LibreOffice differs) {py}")145 if other:146 parts.append(other)147 note = "" if cells[5] in {"—", "-"} else cells[5]148 out[name] = (", ".join(parts), note + (group if note and group else ""))149 missing = lookup_names - set(out)150 if missing:151 raise SystemExit(f"FunctionsLookup/COMPATIBILITY.md has no row for: {sorted(missing)}")152 return out153154155def build() -> str:156 registry = json.loads((HERE / "function-registry.json").read_text(encoding="utf-8"))157 functions = registry["functions"]158 by_package: dict[str, set[str]] = collections.defaultdict(set)159 for f in functions:160 if len(f["packages"]) != 1:161 raise SystemExit(f"{f['name']}: expected exactly one origin package, got {f['packages']}")162 by_package[f["packages"][0]].add(f["name"])163 numeric = numeric_table()164 if set(numeric) != by_package["FunctionsNumeric"]:165 raise SystemExit("FunctionsNumeric table and registry differ: "166 f"{sorted(set(numeric) ^ by_package['FunctionsNumeric'])}")167 text = text_info(by_package["FunctionsText"])168 lookup = lookup_info(by_package["FunctionsLookup"])169170 lines: list[str] = []171 lines.append(f"Registry dump: {registry['registryCount']} registered functions + {len(registry['specialForms'])} special forms "172 f"= {registry['functionCount']} (`ORIXEngine.functionCount`).")173 lines.append("")174 lines.append("| Origin package | Registered functions |")175 lines.append("|---|---:|")176 for package in ("SheetCore", "FunctionsNumeric", "FunctionsText", "FunctionsLookup"):177 lines.append(f"| `{package}` | {registry['perPackage'][package]} |")178 lines.append("")179 lines.append("Source: `docs/compatibility/function-registry.json` (each library registered into a fresh `FunctionRegistry`).")180 lines.append("")181 grouped: dict[str, list[dict]] = collections.defaultdict(list)182 for f in functions:183 grouped[f["category"]].append(f)184 unknown = set(grouped) - set(CATEGORY_ORDER)185 if unknown:186 raise SystemExit(f"unknown categories {unknown}")187 for category in CATEGORY_ORDER:188 rows = sorted(grouped.get(category, []), key=lambda f: f["name"])189 lines.append(f"### {category} ({len(rows)} registered)")190 lines.append("")191 lines.append("| Function | Signature | Summary | Package | Validation (reference cases) | Notes and deviations |")192 lines.append("|---|---|---|---|---|---|")193 for f in rows:194 package = f["packages"][0]195 if package == "SheetCore":196 validation, note = "SheetCore unit tests (`CoreTests.swift`); no external reference corpus", CORE_NOTES.get(f["name"], "")197 elif package == "FunctionsNumeric":198 validation, note = numeric[f["name"]]199 elif package == "FunctionsText":200 validation, note = text[f["name"]]201 else:202 validation, note = lookup[f["name"]]203 if f["volatile"]:204 note = ("**Volatile.** " + note).strip()205 lines.append(f"| `{f['name']}` | `{cell(f['signature'])}` | {cell(f['summary'])} | {package.replace('Functions', '')} | "206 f"{cell(validation)} | {cell(note)} |")207 lines.append("")208 lines.append("Source: `function-registry.json` (name, signature, summary, volatility); validation and notes from "209 "the `COMPATIBILITY.md` of the origin package (FunctionsText: counts from `function_references.json` by `src` tag).")210 lines.append("")211 return "\n".join(lines).rstrip() + "\n"212213214def main() -> int:215 generated = build()216 text = TARGET.read_text(encoding="utf-8")217 if BEGIN not in text or END not in text:218 raise SystemExit(f"{TARGET} lacks the {BEGIN} / {END} markers")219 head, rest = text.split(BEGIN, 1)220 _, tail = rest.split(END, 1)221 updated = head + BEGIN + "\n" + generated + END + tail222 if "--check" in sys.argv[1:]:223 if updated != text:224 print("formulas.md catalogue is out of date")225 return 1226 print("formulas.md catalogue is up to date")227 return 0228 TARGET.write_text(updated, encoding="utf-8")229 print(f"wrote catalogue: {generated.count(chr(10))} lines")230 return 0231232233if __name__ == "__main__":234 sys.exit(main())235