xlsx/number-format-builtin--id5

Metadata

XPath assertions

Render assertions

Generator source

scripts/gen_number_format_builtin.py

#!/usr/bin/env python3
"""Generate a ``fixtures/xlsx/number-format-builtin--id<N>.xlsx`` fixture.

Parameterised generator for the ``xlsx/number-format-builtin`` manifest.
Writes a minimal one-sheet workbook with cell A1 set to a numeric value
and its ``number_format`` drawn from ``BUILTIN_FORMATS[num_fmt_id]``.
Excel treats these codes as built-in: the resulting workbook references
the numFmtId from ``<cellXfs>/<xf>`` without emitting an explicit
``<numFmt>`` entry in ``<numFmts>``.
"""

from __future__ import annotations

import argparse
import datetime as _dt
from pathlib import Path

from xlsx import Workbook
from xlsx.styles.numbers import BUILTIN_FORMATS

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


def _write(num_fmt_id: int, out: Path) -> None:
    if num_fmt_id not in BUILTIN_FORMATS:
        raise ValueError(
            f"num_fmt_id {num_fmt_id} is not a built-in format; "
            f"valid ids: {sorted(BUILTIN_FORMATS)}"
        )
    wb = Workbook()
    ws = wb.active
    ws["A1"] = 42.5
    ws["A1"].number_format = BUILTIN_FORMATS[num_fmt_id]
    out.parent.mkdir(parents=True, exist_ok=True)
    wb.save(out, zip_date_time=_REPRODUCIBLE_DT)


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument(
        "--num-fmt-id",
        type=int,
        required=True,
        help="Built-in numFmtId (key in xlsx.styles.numbers.BUILTIN_FORMATS)",
    )
    parser.add_argument(
        "--out",
        required=True,
        help="Output fixture path (relative to repo root or absolute)",
    )
    args = parser.parse_args()

    out_path = Path(args.out)
    if not out_path.is_absolute():
        out_path = _REPO_ROOT / out_path
    _write(args.num_fmt_id, out_path)
    print(out_path)
    return 0


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

Manifest (expanded)

{
  "$schema": "../manifest.schema.json",
  "id": "xlsx/number-format-builtin--id5",
  "kind": "literal",
  "title": "Built-in number-format IDs",
  "format": "xlsx",
  "category": "cell-formatting",
  "summary": "Every ECMA-376 built-in numFmtId (id < 164) used on a cell produces a <cellXfs>/<xf> that references that id, with no explicit <numFmt> entry emitted in <numFmts>.",
  "spec": {
    "source": "ecma-376-5-part-1",
    "clause": "18.8.30",
    "element": "numFmt",
    "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.8.30 enumerates a fixed table of built-in numFmtIds (0='General', 1='0', 2='0.00', 9='0%', 10='0.00%', 14='mm-dd-yy', 37='#,##0_);(#,##0)', ...). The built-in ids reserved by the spec do NOT appear as explicit <numFmt formatCode=.../> entries in xl/styles.xml's <numFmts> — compliant writers omit them and just reference the id from <cellXfs>/<xf>. Only CUSTOM formats (id >= 164) produce a <numFmt> entry. This family parameterises over every id in python-xlsx's xlsx.styles.numbers.BUILTIN_FORMATS (45 entries spanning 0-22, 37-49, and the ja-JP locale block 50-58). Each expanded case targets a distinct fixture xlsx/number-format-builtin--id<N>.xlsx generated with ws['A1'].number_format = BUILTIN_FORMATS[id]. The sole assertion checks that a <xf> in <cellXfs> references the expected numFmtId. SpreadsheetML parts use a default namespace with no prefix; XPath needs an explicit binding (here 'x'). Note: the ja-JP locale block 50-58 contains duplicate format codes per spec. Four groups: (50, 57), (51, 54, 58), (52, 55), (53, 56). Since python-xlsx 2026.05.x (commit bc27371) BUILTIN_FORMATS_REVERSE picks the FIRST id for each duplicate code, so ids 50, 51, 52, 53 round-trip to themselves while the higher-numbered duplicates (54-58) are unreachable via round-trip. This family deliberately includes only the canonical (lowest-id) members of each duplicate group. Higher-id duplicates are inherently un-round-trippable given first-wins semantics and are excluded rather than authored as known-failures."
  },
  "fixtures": {
    "machine": "xlsx/number-format-builtin--id5"
  },
  "generator": {
    "python": "scripts/gen_number_format_builtin.py",
    "arg_template": "--num-fmt-id {numfmt.num_fmt_id} --out fixtures/xlsx/number-format-builtin--{numfmt.id}.xlsx"
  },
  "_expansion": {
    "parent_id": "xlsx/number-format-builtin",
    "bindings": {
      "numfmt": "id5"
    }
  },
  "assertions": [
    {
      "id": "numfmt-id5-referenced",
      "part": "xl/styles.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:cellXfs/x:xf[@numFmtId='5']",
      "must": "exist",
      "description": "A <xf> in <cellXfs> must reference the expected built-in numFmtId. Built-in ids (< 164) are implicit: no <numFmt> entry is required or expected."
    }
  ],
  "render_assertions": [
    {
      "id": "numfmt-id5-cell-present",
      "kind": "css_selector",
      "selector": "section.xlsx td",
      "must": "exist",
      "description": "The rendered sheet must contain at least one cell; renderer-specific numFmt application is out of scope for this family."
    },
    {
      "id": "sheet-count-id5",
      "kind": "css_selector",
      "selector": "section.xlsx",
      "must": "equal-count",
      "count": 1,
      "description": "Exactly one rendered sheet section per case. Catches renderers that emit zero sheets (hard failure)."
    }
  ]
}