Data Validation

Witan supports Excel data validation rules as workbook constraints on cells and ranges. Use them to document expected inputs, create dropdowns, preserve Excel-compatible prompts and error alerts, and lint current workbook values against the rules.

Data validation is part of the workbook model: scripts can inspect existing Excel rules, add or replace rules, remove rules, reject writes that violate the rules, and use lint rule D043 to report existing invalid values.

Supported Rules

The comparison operators are Between, NotBetween, EqualTo, NotEqualTo, GreaterThan, LessThan, GreaterThanOrEqualTo, and LessThanOrEqualTo.

Add Rules

Use setDataValidations to add rules to a sheet. The address is relative to the sheet argument, and can be a range or a union of ranges on that same sheet.

witan xlsx exec budget.xlsx --save --stdin <<'WITAN'
await xlsx.setDataValidations(wb, "Inputs", [
  {
    address: "B2:B20",
    rule: {
      decimal: {
        operator: "GreaterThanOrEqualTo",
        formula1: 0
      }
    },
    ignoreBlanks: false,
    prompt: {
      title: "Forecast input",
      message: "Enter a non-negative amount."
    },
    errorAlert: {
      style: "Stop",
      title: "Invalid amount",
      message: "Amounts must be zero or greater."
    }
  }
])
WITAN

Use a list rule for dropdown-style inputs:

await xlsx.setDataValidations(wb, "Inputs", [
  {
    address: "C2:C20",
    rule: {
      list: {
        source: "Draft,Reviewed,Approved",
        inCellDropDown: true
      }
    }
  }
])

For a range-backed list, pass a formula-style source:

await xlsx.setDataValidations(wb, "Inputs", [
  {
    address: "D2:D20",
    rule: {
      list: {
        source: "=Lists!$A$2:$A$8"
      }
    }
  }
])

Inspect Rules

Use getDataValidations to list validation rules across the workbook, on one sheet, or intersecting one address:

witan xlsx exec model.xlsx --expr 'await xlsx.getDataValidations(wb, { sheet: "Inputs" })'

Each returned rule includes:

• index: the sheet-local rule index, used when removing rules by index.
• sheet and address: the target cells. A rule can cover multiple comma-separated areas.
• type and rule: the rule family and criteria.
• ignoreBlanks, prompt, and errorAlert: Excel-compatible validation behavior and UI metadata.

Check Values With Lint

Use lint with onlyRuleIds: ["D043"] to check current cell values against their validation rules:

witan xlsx exec model.xlsx --expr 'await xlsx.lint(wb, { rangeAddresses: ["Inputs!B2:D20"], onlyRuleIds: ["D043"] })'

The result contains lint diagnostics for cells whose current value or formula result violates an applicable data validation rule.

{
  "diagnostics": [
    {
      "severity": "Error",
      "ruleId": "D043",
      "message": "Cell value violates Decimal data validation rule.",
      "location": "Inputs!B5",
      "visibility": "visible"
    }
  ],
  "total": 1
}

The standalone lint command uses the same rule:

witan xlsx lint model.xlsx --range "Inputs!B2:D20" --only-rule D043

Reject Invalid Writes

By default, setCells can write values even when they violate data validation. This matches many automation workflows where a script may intentionally stage invalid inputs before fixing them.

When the rule should be enforced during the write, pass validationMode: "reject":

witan xlsx exec model.xlsx --save --stdin <<'WITAN'
await xlsx.setCells(
  wb,
  [{ address: "Inputs!B5", value: -10 }],
  { validationMode: "reject" }
)
WITAN

If the value violates an applicable rule, the operation fails with a DATA_VALIDATION error and the workbook is not saved.

Replace And Remove Rules

setDataValidations replaces any existing validation that intersects the target cells. If an existing rule covers A1:A3 and you add a new rule to A2, Witan splits the original rule so A1 and A3 keep the old validation while A2 gets the new one.

Pass { clear: true } to clear existing validations on the sheet before adding the provided rules:

await xlsx.setDataValidations(wb, "Inputs", rules, { clear: true })

Remove a whole rule by index:

await xlsx.removeDataValidations(wb, "Inputs", { indices: [0, 2] })

Or remove validation from a specific range while preserving the same rule outside that range:

await xlsx.removeDataValidations(wb, "Inputs", { address: "B5:B10" })

Notes And Limits

• Data validation rules are stored as normal Excel workbook rules and round-trip through .xlsx files.
• Excel input prompts and invalid-value error alerts are read and written with each rule.
• Formula values can be literals or formula-style strings beginning with =.
• Lint rule D043 evaluates the current value or formula result for cells in the lint scope. Blank handling follows each rule's ignoreBlanks setting.
• If an existing rule cannot be evaluated because the rule definition is invalid, lint reports one D043 diagnostic for that validation rule.

See xlsx API for exact TypeScript and Python signatures.