xlsx/data-validation-type

Parameter axes

• type (7 values): whole, decimal, list, date, time, text-length, custom

Spec notes

Generator source

scripts/gen_data_validation_type.py — runs with --arg_template --type {type.val} --formula1 "{type.formula1}" --formula2 "{type.formula2}" --out fixtures/xlsx/data-validation-type--{type.id}.xlsx

#!/usr/bin/env python3
"""Generate ``fixtures/xlsx/data-validation-type--<id>.xlsx`` fixtures.

Parameterised family exercising the seven ``dataValidation`` ``@type``
values that python-xlsx supports via ``xlsx.worksheet.datavalidation.
DataValidation`` — ``whole``, ``decimal``, ``list``, ``date``, ``time``,
``textLength``, and ``custom``.

Each invocation writes a one-sheet workbook with ``A1`` populated, a
single ``DataValidation`` of the requested ``@type`` (with the requested
``formula1`` and optional ``formula2`` operand strings), and that
validation applied to ``A1:A10``. python-xlsx takes the formula strings
opaquely — the fixture preserves whatever shape the caller supplies. The
manifest at ``features/xlsx/data-validation-type.json`` drives the
parameter expansion.
"""

from __future__ import annotations

import argparse
import datetime as _dt
from pathlib import Path

from xlsx import Workbook
from xlsx.worksheet.datavalidation import DataValidation

_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(
        "--type",
        required=True,
        help="ST_DataValidationType value (whole|decimal|list|date|time|textLength|custom).",
    )
    parser.add_argument("--formula1", required=True, help="Primary operand expression.")
    parser.add_argument(
        "--formula2",
        default="",
        help="Secondary operand expression (empty string to omit).",
    )
    parser.add_argument("--out", required=True, help="Output .xlsx path.")
    args = parser.parse_args()

    out_path = Path(args.out)
    if not out_path.is_absolute():
        out_path = _REPO_ROOT / out_path
    out_path.parent.mkdir(parents=True, exist_ok=True)

    wb = Workbook()
    ws = wb.active
    ws["A1"] = "validated cell"

    dv_kwargs: dict[str, str] = {
        "type": args.type,
        "formula1": args.formula1,
    }
    if args.formula2:
        dv_kwargs["formula2"] = args.formula2
    dv = DataValidation(**dv_kwargs)
    dv.add("A1:A10")
    ws.add_data_validation(dv)

    wb.save(out_path, zip_date_time=_REPRODUCIBLE_DT)
    print(out_path)
    return 0


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

Manifest (parent, unexpanded)

{
  "$schema": "../manifest.schema.json",
  "id": "xlsx/data-validation-type",
  "kind": "parameterised",
  "title": "Data validation type",
  "format": "xlsx",
  "category": "cell-data",
  "summary": "A worksheet declares a single <dataValidation> over A1:A10 whose @type takes each of the seven ST_DataValidationType values supported by python-xlsx: whole, decimal, list, date, time, textLength, and custom.",
  "spec": {
    "source": "ecma-376-5-part-1",
    "clause": "18.3.1.32",
    "element": "dataValidation",
    "rnc_reference": "spec/ecma-376-5/part-1/rnc/SpreadsheetML.rnc",
    "xsd_reference": "spec/ecma-376-5/part-1/xsd/sml.xsd",
    "notes": "Data validation rules live inside <dataValidations> on a worksheet. Each <dataValidation> carries an @type drawn from ST_DataValidationType (ECMA-376 Part 1 18.18.20) — whole, decimal, list, date, time, textLength, or custom — plus optional <formula1> and <formula2> children that carry the operand expressions. python-xlsx's DataValidation accepts formula1/formula2 as opaque strings, so date and time formulas are passed through as ISO-style literal strings; Excel itself coerces these at open time. The 'custom' type semantically takes only formula1 (a predicate expression); we omit formula2 for that case. SpreadsheetML parts use a default namespace with no prefix; XPath binds it as 'x'."
  },
  "fixtures": {
    "machine": "xlsx/data-validation-type"
  },
  "generator": {
    "python": "scripts/gen_data_validation_type.py",
    "arg_template": "--type {type.val} --formula1 \"{type.formula1}\" --formula2 \"{type.formula2}\" --out fixtures/xlsx/data-validation-type--{type.id}.xlsx"
  },
  "parameters": {
    "type": [
      {
        "id": "whole",
        "val": "whole",
        "formula1": "1",
        "formula2": "100"
      },
      {
        "id": "decimal",
        "val": "decimal",
        "formula1": "0.0",
        "formula2": "1.0"
      },
      {
        "id": "list",
        "val": "list",
        "formula1": "\"A,B,C\"",
        "formula2": ""
      },
      {
        "id": "date",
        "val": "date",
        "formula1": "2020-01-01",
        "formula2": "2030-12-31"
      },
      {
        "id": "time",
        "val": "time",
        "formula1": "00:00:00",
        "formula2": "12:00:00"
      },
      {
        "id": "text-length",
        "val": "textLength",
        "formula1": "1",
        "formula2": "10"
      },
      {
        "id": "custom",
        "val": "custom",
        "formula1": "ISNUMBER(A1)",
        "formula2": ""
      }
    ]
  },
  "assertions_template": [
    {
      "id": "validation-type-is-{type.id}",
      "part": "xl/worksheets/sheet1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:dataValidations/x:dataValidation/@type",
      "must": "equal",
      "value": "{type.val}",
      "description": "The worksheet must contain a <dataValidations>/<dataValidation> whose @type equals the expected ST_DataValidationType enum value."
    }
  ]
}