xlsx/conditional-format-rule--cell-is-equal

Metadata

XPath assertions

Render assertions

No render assertions declared.

Generator source

scripts/gen_conditional_format_rule.py

#!/usr/bin/env python3
"""Generate ``fixtures/xlsx/conditional-format-rule--<kind>.xlsx`` fixtures.

Parameterised family exercising the 15 ``cfRule`` ``@type`` values that
python-xlsx supports via its public API — ``cellIs``, ``containsText``,
``notContainsText``, ``beginsWith``, ``endsWith``, ``duplicateValues``,
``uniqueValues``, ``top10`` (both "top" and "bottom" variants),
``expression``, ``colorScale``, and ``dataBar``.

Each invocation writes a one-sheet workbook with A1:A10 populated with
the integers 1..10 and a single conditional formatting rule applied to
that range. The rule-building logic is dispatched on ``--kind``. See
``features/xlsx/conditional-format-rule.json`` for the manifest that
drives the parameter expansion.
"""

from __future__ import annotations

import argparse
import datetime as _dt
from pathlib import Path

from xlsx import Workbook
from xlsx.styles import PatternFill
from xlsx.formatting.rule import (
    CellIsRule,
    ColorScaleRule,
    DataBarRule,
    FormulaRule,
    Rule,
)

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

_FILL = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")


def build(kind: str) -> Rule:
    if kind == "cell-is-greater":
        return CellIsRule(operator="greaterThan", formula=["10"], fill=_FILL)
    if kind == "cell-is-less":
        return CellIsRule(operator="lessThan", formula=["10"], fill=_FILL)
    if kind == "cell-is-equal":
        return CellIsRule(operator="equal", formula=["10"], fill=_FILL)
    if kind == "cell-is-between":
        return CellIsRule(operator="between", formula=["5", "15"], fill=_FILL)
    if kind == "contains-text":
        return Rule(
            type="containsText",
            operator="containsText",
            text="foo",
            formula=['NOT(ISERROR(SEARCH("foo",A1)))'],
        )
    if kind == "not-contains-text":
        return Rule(
            type="notContainsText",
            operator="notContains",
            text="bar",
            formula=['ISERROR(SEARCH("bar",A1))'],
        )
    if kind == "begins-with":
        return Rule(
            type="beginsWith",
            operator="beginsWith",
            text="A",
            formula=['LEFT(A1,1)="A"'],
        )
    if kind == "ends-with":
        return Rule(
            type="endsWith",
            operator="endsWith",
            text="X",
            formula=['RIGHT(A1,1)="X"'],
        )
    if kind == "duplicate":
        return Rule(type="duplicateValues")
    if kind == "unique":
        return Rule(type="uniqueValues")
    if kind == "top10":
        return Rule(type="top10", rank=10, bottom=False)
    if kind == "bottom10":
        return Rule(type="top10", rank=10, bottom=True)
    if kind == "expression":
        return FormulaRule(formula=["A1>0"], fill=_FILL)
    if kind == "color-scale":
        return ColorScaleRule(
            start_type="min",
            start_color="FF0000",
            end_type="max",
            end_color="00FF00",
        )
    if kind == "data-bar":
        return DataBarRule(start_type="min", end_type="max", color="638EC6")
    raise ValueError(f"unknown kind: {kind!r}")


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--kind", required=True, help="Rule kind id from the manifest.")
    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
    for i in range(1, 11):
        ws.cell(row=i, column=1, value=i)

    ws.conditional_formatting.add("A1:A10", build(args.kind))
    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/conditional-format-rule--cell-is-equal",
  "kind": "literal",
  "title": "Conditional formatting rule type",
  "format": "xlsx",
  "category": "cell-formatting",
  "summary": "A worksheet declares a single <conditionalFormatting> block over A1:A10 with a <cfRule> of a specific @type (cellIs, containsText, notContainsText, beginsWith, endsWith, duplicateValues, uniqueValues, top10, expression, colorScale, dataBar).",
  "spec": {
    "source": "ecma-376-5-part-1",
    "clause": "18.3.1.10",
    "element": "cfRule",
    "rnc_reference": "spec/ecma-376-5/part-1/rnc/SpreadsheetML.rnc",
    "xsd_reference": "spec/ecma-376-5/part-1/xsd/sml.xsd",
    "notes": "Conditional formatting rules live inside <conditionalFormatting sqref='...'> elements on a worksheet. Each <cfRule> carries a required @type drawn from the ST_CfType enum (ECMA-376 Part 1 18.18.12) plus type-specific attributes (operator, text, rank, bottom, ...) and optional nested <colorScale>, <dataBar>, <iconSet>, or <formula> children. This parameterised family exercises the 15 @type values python-xlsx supports via its public rule factories: CellIsRule covers four cellIs operator variants; Rule(type='containsText'|'notContainsText'|'beginsWith'|'endsWith'|...) covers the text-matching and set-membership kinds; Rule(type='top10', bottom=True|False) covers both top and bottom rank rules under a shared @type='top10'; FormulaRule emits @type='expression'; ColorScaleRule and DataBarRule emit @type='colorScale' and 'dataBar' respectively. SpreadsheetML parts use a default namespace with no prefix; XPath binds it as 'x'."
  },
  "fixtures": {
    "machine": "xlsx/conditional-format-rule--cell-is-equal"
  },
  "generator": {
    "python": "scripts/gen_conditional_format_rule.py",
    "arg_template": "--kind {rule.id} --out fixtures/xlsx/conditional-format-rule--{rule.id}.xlsx"
  },
  "_expansion": {
    "parent_id": "xlsx/conditional-format-rule",
    "bindings": {
      "rule": "cell-is-equal"
    }
  },
  "assertions": [
    {
      "id": "cfrule-type-cell-is-equal",
      "part": "xl/worksheets/sheet1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "string(//x:conditionalFormatting/x:cfRule/@type)",
      "must": "equal",
      "value": "cellIs",
      "description": "The worksheet must contain a <conditionalFormatting>/<cfRule> whose @type equals the expected ST_CfType enum value."
    }
  ]
}