xlsx/sum-formula

Metadata

XPath assertions

Render assertions

Generator source

scripts/gen_sum_formula.py

#!/usr/bin/env python3
"""Generate ``fixtures/xlsx/sum-formula.xlsx``.

A minimal one-sheet workbook with A1..A3 = 1, 2, 3 and A4 holding
'=SUM(A1:A3)'. Designed to satisfy the assertions in
``features/xlsx/sum-formula.json``.
"""

from __future__ import annotations

from pathlib import Path

from xlsx import Workbook

import datetime as _dt
_REPRODUCIBLE_DT = _dt.datetime(2000, 1, 1, 0, 0, 0)

_REPO_ROOT = Path(__file__).resolve().parent.parent
_OUT = _REPO_ROOT / "fixtures" / "xlsx" / "sum-formula.xlsx"


def main() -> int:
    wb = Workbook()
    ws = wb.active
    ws["A1"] = 1
    ws["A2"] = 2
    ws["A3"] = 3
    ws["A4"] = "=SUM(A1:A3)"
    _OUT.parent.mkdir(parents=True, exist_ok=True)
    wb.save(_OUT, zip_date_time=_REPRODUCIBLE_DT)
    print(_OUT)
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

Manifest (expanded)

{
  "$schema": "../manifest.schema.json",
  "id": "xlsx/sum-formula",
  "title": "SUM formula",
  "format": "xlsx",
  "category": "formulas",
  "summary": "A workbook with A1..A3 containing 1, 2, 3 and A4 carrying '=SUM(A1:A3)' emits a <c r='A4'><f>SUM(A1:A3)</f></c> in the sheet part — the cached value may be absent or empty when the writer does not evaluate formulas.",
  "spec": {
    "source": "ecma-376-5-part-1",
    "clause": "18.17",
    "element": "f",
    "rnc_reference": "spec/ecma-376-5/part-1/rnc/SpreadsheetML.rnc",
    "xsd_reference": "spec/ecma-376-5/part-1/xsd/sml.xsd",
    "notes": "ECMA-376 Part 1 clause 18.17 specifies formula syntax; clause 18.3.1.40 (<f>) defines the cell-formula element. A formula cell is a <c> whose first child is <f> containing the A1-style formula text (no leading '=' — the '=' is a Python-level convenience in openpyxl-family APIs). A <v> sibling may hold the cached computed value; writers that do not evaluate formulas emit <v/> or omit it. Excel re-computes on open so the absence of a cached value is spec-valid. python-xlsx emits <f>SUM(A1:A3)</f><v/>."
  },
  "fixtures": {
    "machine": "xlsx/sum-formula",
    "office": "xlsx/sum-formula"
  },
  "generator": {
    "python": "scripts/gen_sum_formula.py"
  },
  "assertions": [
    {
      "id": "sum-formula-present",
      "part": "xl/worksheets/sheet1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:c[@r='A4']/x:f[normalize-space()='SUM(A1:A3)']",
      "must": "exist",
      "description": "Cell A4 must carry a <f> child whose text, after whitespace normalisation, is 'SUM(A1:A3)' — the formula body without the leading '='."
    }
  ],
  "render_assertions": [
    {
      "id": "sum-formula-rendered",
      "kind": "css_selector",
      "selector": "section.xlsx td, section.xlsx th",
      "must": "match-text",
      "value": "^(6(?:\\.0+)?|=?SUM\\(A1:A3\\))$",
      "description": "The rendered sheet must contain a cell showing either the evaluated result (6) or the formula text (=SUM(A1:A3) or SUM(A1:A3)) if the renderer does not evaluate formulas."
    },
    {
      "id": "sheet-count",
      "kind": "css_selector",
      "selector": "section.xlsx",
      "must": "equal-count",
      "count": 1,
      "description": "Exactly one rendered sheet section. Catches renderers that emit zero sheets (hard failure) or duplicate a sheet."
    }
  ]
}