xlsx/number-format-builtin

Parameter axes

• numfmt (40 values): id0, id1, id2, id3, id4, id5, id6, id7, id8, id9, id10, id11, id12, id13, id14, id15, id16, id17, id18, id19, id20, id21, id22, id37, id38, id39, id40, id41, id42, id43, id44, id45, id46, id47, id48, id49, id50, id51, id52, id53

Spec notes

Generator source

scripts/gen_number_format_builtin.py — runs with --arg_template --num-fmt-id {numfmt.num_fmt_id} --out fixtures/xlsx/number-format-builtin--{numfmt.id}.xlsx

#!/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 (parent, unexpanded)

{
  "$schema": "../manifest.schema.json",
  "id": "xlsx/number-format-builtin",
  "kind": "parameterised",
  "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"
  },
  "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"
  },
  "parameters": {
    "numfmt": [
      {
        "id": "id0",
        "num_fmt_id": 0,
        "format_code": "General"
      },
      {
        "id": "id1",
        "num_fmt_id": 1,
        "format_code": "0"
      },
      {
        "id": "id2",
        "num_fmt_id": 2,
        "format_code": "0.00"
      },
      {
        "id": "id3",
        "num_fmt_id": 3,
        "format_code": "#,##0"
      },
      {
        "id": "id4",
        "num_fmt_id": 4,
        "format_code": "#,##0.00"
      },
      {
        "id": "id5",
        "num_fmt_id": 5,
        "format_code": "\"$\"#,##0_);(\"$\"#,##0)"
      },
      {
        "id": "id6",
        "num_fmt_id": 6,
        "format_code": "\"$\"#,##0_);[Red](\"$\"#,##0)"
      },
      {
        "id": "id7",
        "num_fmt_id": 7,
        "format_code": "\"$\"#,##0.00_);(\"$\"#,##0.00)"
      },
      {
        "id": "id8",
        "num_fmt_id": 8,
        "format_code": "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)"
      },
      {
        "id": "id9",
        "num_fmt_id": 9,
        "format_code": "0%"
      },
      {
        "id": "id10",
        "num_fmt_id": 10,
        "format_code": "0.00%"
      },
      {
        "id": "id11",
        "num_fmt_id": 11,
        "format_code": "0.00E+00"
      },
      {
        "id": "id12",
        "num_fmt_id": 12,
        "format_code": "# ?/?"
      },
      {
        "id": "id13",
        "num_fmt_id": 13,
        "format_code": "# ??/??"
      },
      {
        "id": "id14",
        "num_fmt_id": 14,
        "format_code": "mm-dd-yy"
      },
      {
        "id": "id15",
        "num_fmt_id": 15,
        "format_code": "d-mmm-yy"
      },
      {
        "id": "id16",
        "num_fmt_id": 16,
        "format_code": "d-mmm"
      },
      {
        "id": "id17",
        "num_fmt_id": 17,
        "format_code": "mmm-yy"
      },
      {
        "id": "id18",
        "num_fmt_id": 18,
        "format_code": "h:mm AM/PM"
      },
      {
        "id": "id19",
        "num_fmt_id": 19,
        "format_code": "h:mm:ss AM/PM"
      },
      {
        "id": "id20",
        "num_fmt_id": 20,
        "format_code": "h:mm"
      },
      {
        "id": "id21",
        "num_fmt_id": 21,
        "format_code": "h:mm:ss"
      },
      {
        "id": "id22",
        "num_fmt_id": 22,
        "format_code": "m/d/yy h:mm"
      },
      {
        "id": "id37",
        "num_fmt_id": 37,
        "format_code": "#,##0_);(#,##0)"
      },
      {
        "id": "id38",
        "num_fmt_id": 38,
        "format_code": "#,##0_);[Red](#,##0)"
      },
      {
        "id": "id39",
        "num_fmt_id": 39,
        "format_code": "#,##0.00_);(#,##0.00)"
      },
      {
        "id": "id40",
        "num_fmt_id": 40,
        "format_code": "#,##0.00_);[Red](#,##0.00)"
      },
      {
        "id": "id41",
        "num_fmt_id": 41,
        "format_code": "_(* #,##0_);_(* \\(#,##0\\);_(* \"-\"_);_(@_)"
      },
      {
        "id": "id42",
        "num_fmt_id": 42,
        "format_code": "_(\"$\"* #,##0_);_(\"$\"* \\(#,##0\\);_(\"$\"* \"-\"_);_(@_)"
      },
      {
        "id": "id43",
        "num_fmt_id": 43,
        "format_code": "_(* #,##0.00_);_(* \\(#,##0.00\\);_(* \"-\"??_);_(@_)"
      },
      {
        "id": "id44",
        "num_fmt_id": 44,
        "format_code": "_(\"$\"* #,##0.00_)_(\"$\"* \\(#,##0.00\\)_(\"$\"* \"-\"??_)_(@_)"
      },
      {
        "id": "id45",
        "num_fmt_id": 45,
        "format_code": "mm:ss"
      },
      {
        "id": "id46",
        "num_fmt_id": 46,
        "format_code": "[h]:mm:ss"
      },
      {
        "id": "id47",
        "num_fmt_id": 47,
        "format_code": "mmss.0"
      },
      {
        "id": "id48",
        "num_fmt_id": 48,
        "format_code": "##0.0E+0"
      },
      {
        "id": "id49",
        "num_fmt_id": 49,
        "format_code": "@"
      },
      {
        "id": "id50",
        "num_fmt_id": 50,
        "format_code": "[$-411]ge.m.d"
      },
      {
        "id": "id51",
        "num_fmt_id": 51,
        "format_code": "[$-411]ggge\"年\"m\"月\"d\"日\""
      },
      {
        "id": "id52",
        "num_fmt_id": 52,
        "format_code": "yyyy\"年\"m\"月\""
      },
      {
        "id": "id53",
        "num_fmt_id": 53,
        "format_code": "m\"月\"d\"日\""
      }
    ]
  },
  "assertions_template": [
    {
      "id": "numfmt-{numfmt.id}-referenced",
      "part": "xl/styles.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:cellXfs/x:xf[@numFmtId='{numfmt.num_fmt_id}']",
      "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_template": [
    {
      "id": "numfmt-{numfmt.id}-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-{numfmt.id}",
      "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)."
    }
  ]
}