xlsx/shared-formula

Metadata

XPath assertions

Render assertions

Generator source

scripts/gen_shared_formula.py

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

Column A supplies operand values (1..5). Column B hosts a shared-formula
group anchored at B1: the anchor carries t='shared', ref='B1:B5', si='0'
and formula text 'A1+1'; the four sibling cells B2..B5 reference the
same group via t='shared' si='0' with no ref.

python-xlsx does not have a first-class ``SharedFormula`` helper, but
``Worksheet.formula_attributes`` is a public dict keyed by cell
coordinate whose values are emitted verbatim as attributes on the
cell's ``<f>`` element by the writer. Setting that dict lets us author
shared formulas without direct XML manipulation.
"""

from __future__ import annotations

import datetime as _dt
from pathlib import Path

from xlsx import Workbook

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

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


def main() -> int:
    wb = Workbook()
    ws = wb.active

    # Operands
    for row, value in enumerate((1, 2, 3, 4, 5), start=1):
        ws.cell(row=row, column=1, value=value)

    # Anchor + siblings for the shared-formula group
    ws["B1"] = "=A1+1"
    ws.formula_attributes["B1"] = {"t": "shared", "ref": "B1:B5", "si": "0"}
    for coord in ("B2", "B3", "B4", "B5"):
        ws[coord] = "=A1+1"
        ws.formula_attributes[coord] = {"t": "shared", "si": "0"}

    _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/shared-formula",
  "title": "Shared formula across a range",
  "format": "xlsx",
  "category": "formulas",
  "summary": "Column B hosts a shared formula '=A1+1' anchored at B1 and shared across B1:B5; the anchor <f> carries t='shared', ref='B1:B5', si='0' and the four sibling cells carry t='shared' si='0' with no ref.",
  "spec": {
    "source": "ecma-376-5-part-1",
    "clause": "18.3.1.40",
    "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": "A shared-formula group is declared once on the 'master' cell (t='shared', ref='B1:B5', si='0') and each participating sibling cell references the group by its @si index (t='shared', si='0') with no @ref and typically no formula text. Excel synthesises the translated formula for each cell on load. SpreadsheetML parts use a default namespace with no prefix; XPath needs an explicit binding (here 'x')."
  },
  "fixtures": {
    "machine": "xlsx/shared-formula"
  },
  "generator": {
    "python": "scripts/gen_shared_formula.py"
  },
  "assertions": [
    {
      "id": "shared-formula-anchor",
      "part": "xl/worksheets/sheet1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:c[@r='B1']/x:f[@t='shared' and @ref='B1:B5' and @si='0' and normalize-space()='A1+1']",
      "must": "exist",
      "description": "The anchor cell B1 must carry an <f> with t='shared', ref='B1:B5', si='0' and formula text 'A1+1'."
    },
    {
      "id": "shared-formula-sibling",
      "part": "xl/worksheets/sheet1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:c[@r='B3']/x:f[@t='shared' and @si='0' and not(@ref)]",
      "must": "exist",
      "description": "A sibling cell (B3) must reference the shared group via t='shared' si='0' with no @ref."
    }
  ],
  "render_assertions": [
    {
      "id": "shared-formula-values-present",
      "kind": "css_selector",
      "selector": "section.xlsx td, section.xlsx th",
      "must": "match-text",
      "value": "(?:=A1\\+1|\\d+)",
      "description": "The rendered sheet should show either the literal formula text '=A1+1' or the computed numeric result (depending on the renderer's showFormulas mode). Shared-formula translation across siblings is a renderer implementation detail; this assertion is loose on purpose."
    }
  ]
}