xlsx/array-formula--matrix-array-formula

Metadata

XPath assertions

Render assertions

No render assertions declared.

Generator source

scripts/gen_array_formula.py

#!/usr/bin/env python3
"""Generate ``fixtures/xlsx/array-formula--<case>.xlsx`` fixtures.

Parameterised generator for the ``xlsx/array-formula`` manifest. Two
cases exercise the two shapes python-xlsx models via
:class:`xlsx.packaging.metadata.ArrayFormula`:

- ``row-array-formula`` — Excel-365 dynamic-array anchor
  ``=SEQUENCE(5)`` in A1, spilling A1:A5 (is_dynamic=True, cm='1' marker
  on the anchor cell).
- ``matrix-array-formula`` — legacy Ctrl-Shift-Enter CSE form
  ``{=A1:A5*B1:B5}`` in C1, covering C1:C5 (is_dynamic=False, no cm
  marker).

Both emit a cell whose <f t='array' ref='<spill>'> child carries the
expected spill range.
"""

from __future__ import annotations

import argparse
import datetime as _dt
from pathlib import Path

from xlsx import Workbook
from xlsx.packaging.metadata import ArrayFormula

_REPRODUCIBLE_DT = _dt.datetime(2000, 1, 1, 0, 0, 0)
_REPO_ROOT = Path(__file__).resolve().parent.parent


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--case", required=True,
                        choices=("row-array-formula",
                                 "matrix-array-formula"))
    parser.add_argument("--out", required=True)
    args = parser.parse_args()

    out_path = Path(args.out)
    if not out_path.is_absolute():
        out_path = _REPO_ROOT / out_path

    wb = Workbook()
    ws = wb.active
    ws.title = "Sheet1"

    if args.case == "row-array-formula":
        # Excel-365 dynamic-array anchor.  SEQUENCE is a dynamic-array
        # function; python-xlsx rewrites it as _xlfn.SEQUENCE to hint
        # to pre-365 readers that the formula yields an array.
        ws["A1"] = ArrayFormula(formula="=SEQUENCE(5)",
                                spill_range="A1:A5",
                                is_dynamic=True)
    else:
        # Legacy CSE array:  {=A1:A5*B1:B5} over C1:C5.  Seed the two
        # operand columns with plain numbers so the fixture is also a
        # meaningful round-trip for a renderer that evaluates formulas.
        for i in range(1, 6):
            ws.cell(row=i, column=1, value=i)
            ws.cell(row=i, column=2, value=i * 2)
        ws["C1"] = ArrayFormula(formula="=A1:A5*B1:B5",
                                spill_range="C1:C5",
                                is_dynamic=False)

    out_path.parent.mkdir(parents=True, exist_ok=True)
    wb.save(out_path, zip_date_time=_REPRODUCIBLE_DT)
    print(out_path)
    return 0


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

Manifest (expanded)

{
  "$schema": "../manifest.schema.json",
  "id": "xlsx/array-formula--matrix-array-formula",
  "kind": "literal",
  "title": "Array formulas — dynamic-array spill and legacy CSE",
  "format": "xlsx",
  "category": "formulas",
  "summary": "A worksheet carries a cell whose <f> element has @t='array' and an @ref covering a spill range. Two cases exercise the Excel 365 dynamic-array anchor (=SEQUENCE(5) spilling A1:A5) and the legacy Ctrl-Shift-Enter CSE array form (={A1:A5*B1:B5} over C1:C5).",
  "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 cell formula carrying an array result uses <f t='array' ref='<spill-range>'>, where @ref is the cell-range the array spills into. python-xlsx models both shapes via xlsx.packaging.metadata.ArrayFormula: is_dynamic=True (default) produces an Excel-365 dynamic-array anchor with the <c cm='1'> marker, and is_dynamic=False produces a traditional CSE array without the cm marker. Both shapes emit identical <f t='array' @ref> structure; the presence of cm='1' on the parent cell is the differentiator. SEQUENCE is a dynamic-array-only function (it's serialised as _xlfn.SEQUENCE to preserve compatibility with pre-365 readers). SpreadsheetML parts use a default namespace with no prefix; XPath binds 'x'."
  },
  "fixtures": {
    "machine": "xlsx/array-formula--matrix-array-formula"
  },
  "generator": {
    "python": "scripts/gen_array_formula.py",
    "arg_template": "--case {case.id} --out fixtures/xlsx/array-formula--{case.id}.xlsx"
  },
  "_expansion": {
    "parent_id": "xlsx/array-formula",
    "bindings": {
      "case": "matrix-array-formula"
    }
  },
  "assertions": [
    {
      "id": "array-formula-ref-matrix-array-formula",
      "part": "xl/worksheets/sheet1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:c[@r='C1']/x:f[@t='array']/@ref",
      "must": "equal",
      "value": "C1:C5",
      "description": "The array-formula anchor cell's <f t='array'> element must carry @ref equal to the expected spill range."
    }
  ]
}