xlsx/cell-date

Metadata

XPath assertions

Render assertions

Generator source

scripts/gen_cell_date.py

#!/usr/bin/env python3
"""Generate ``fixtures/xlsx/cell-date.xlsx``.

A minimal one-sheet workbook with cell A1 carrying the date
2026-05-04, stored as a 1900-based serial number with format code
'yyyy-mm-dd' registered as a custom <numFmt> in the styles part.
Designed to satisfy the assertions in ``features/xlsx/cell-date.json``.
"""

from __future__ import annotations

import datetime
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" / "cell-date.xlsx"


def main() -> int:
    wb = Workbook()
    ws = wb.active
    ws["A1"] = datetime.date(2026, 5, 4)
    ws["A1"].number_format = "yyyy-mm-dd"
    _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/cell-date",
  "title": "Date-formatted cell",
  "format": "xlsx",
  "category": "cell-data",
  "summary": "A workbook with A1 set to a Python datetime.date stores the 1900-based date serial as the cell's raw <v> value and defines a custom numFmt whose formatCode is 'yyyy-mm-dd', wired to A1 via <cellXfs>/<xf numFmtId=.../>.",
  "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": "Dates in SpreadsheetML are stored as serial numbers — days since the epoch (1900-01-00 in Excel's default 1900 date system) with time as the fractional part — never as ISO strings inside <v>. The visible '2026-05-04' is produced by applying a numFmt with formatCode='yyyy-mm-dd' at render time. 'yyyy-mm-dd' is NOT one of ECMA-376's built-in format IDs, so compliant writers emit a custom <numFmt numFmtId='N' formatCode='yyyy-mm-dd'/> (N typically 164+) and reference it from <cellXfs>/<xf>. The exact serial for 2026-05-04 is 46146 under Excel's day-count convention. Because the stored <v> value is implementation-dependent and brittle to assert against, the principal assertion here targets the styles part's formatCode; the serial check is present but loose (any numeric value)."
  },
  "fixtures": {
    "machine": "xlsx/cell-date",
    "office": "xlsx/cell-date"
  },
  "generator": {
    "python": "scripts/gen_cell_date.py"
  },
  "assertions": [
    {
      "id": "date-numfmt-formatcode-present",
      "part": "xl/styles.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:numFmts/x:numFmt[@formatCode='yyyy-mm-dd']",
      "must": "exist",
      "description": "The styles part must declare a custom <numFmt> with formatCode='yyyy-mm-dd'. This is the canonical authoring-side signal that the cell is intended to render as an ISO-8601 date."
    },
    {
      "id": "date-cell-has-numeric-serial",
      "part": "xl/worksheets/sheet1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:c[@r='A1']/x:v[number(normalize-space()) > 0]",
      "must": "exist",
      "description": "The A1 cell must carry a positive numeric <v> — a date serial, not an ISO string. The exact serial is implementation-dependent (python-xlsx emits 46146 for 2026-05-04 under the 1900 date system), so this assertion only requires that <v> parses as a positive number."
    }
  ],
  "render_assertions": [
    {
      "id": "cell-date-text-present",
      "kind": "css_selector",
      "selector": "section.xlsx td, section.xlsx th",
      "must": "match-text",
      "value": "^(\\d{4}-\\d{2}-\\d{2}|4\\d{4}(?:\\.0+)?)$",
      "description": "The rendered sheet must contain a cell showing either an ISO-shaped date (2026-05-04 etc.) or the raw five-digit serial (46xxx) if the renderer does not apply numFmt transforms."
    },
    {
      "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."
    }
  ]
}