Data Validation

Since v1.4.0 you can attach data-validation rules to cells and ranges — dropdown lists, numeric/date/time/length comparisons, and custom formula checks — with optional input prompts and error alerts, exactly like Excel’s Data Validation.

Note: Authoring validation rules is a Pro feature. Reading and enforcement (getValidations, getValidationAt, validate) run in Lite, so rules authored in Pro — or imported from xlsx — are still enforced in the free tier.

Attaching a rule

Attach a ValidationRule to a range, a single cell, or an explicit rectangle:

import { createReogrid } from '@reogrid/pro'

const grid = createReogrid({ workspace: '#app' })
const ws = grid.worksheet

// A whole column
ws.range('E2:E100').setValidation({ type: 'list', options: ['Low', 'Medium', 'High'] })

// A single cell
ws.cell('B2').setValidation({ type: 'whole', operator: 'between', value1: 1, value2: 100 })

// An explicit rectangle (r1, c1, r2, c2)
ws.setValidation(1, 3, 99, 3, { type: 'decimal', operator: 'greaterThan', value1: 0 })

Remove rules with ws.range(...).removeValidation(), ws.removeValidation(r1, c1, r2, c2), or ws.clearValidations().

Base options (every rule)

Every rule extends a shared base:

Rule types

List (dropdown)

Provide inline options or a same-sheet source range/formula — the two are mutually exclusive:

// Inline options
ws.range('B2:B100').setValidation({
  type: 'list',
  options: ['Draft', 'In review', 'Done'],
  showInputMessage: true,
  inputTitle: 'Status',
  inputMessage: 'Pick a status from the dropdown.',
})

// Source range (resolved at use)
ws.range('A2:A100').setValidation({
  type: 'list',
  source: '=$H$1:$H$5',
  showDropdown: true, // render the dropdown arrow (default true)
})

Comparison (whole / decimal / date / time / textLength)

type picks the value domain; operator and value1 (plus value2 for between / notBetween) define the test. Literals or formula strings are both accepted:

// Whole number between 1 and 100
ws.range('C2:C100').setValidation({
  type: 'whole', operator: 'between', value1: 1, value2: 100,
  errorMessage: 'Quantity must be a whole number from 1 to 100.',
})

// Positive decimal
ws.range('D2:D100').setValidation({ type: 'decimal', operator: 'greaterThan', value1: 0 })

// Date on or after today (advisory warning)
ws.range('E2:E100').setValidation({
  type: 'date', operator: 'greaterThanOrEqual', value1: '=TODAY()',
  alertStyle: 'warning', errorMessage: 'The due date is in the past.',
})

// Text length ≤ 5
ws.range('F2:F100').setValidation({ type: 'textLength', operator: 'lessThanOrEqual', value1: 5 })

Operators: between · notBetween · equal · notEqual · greaterThan · lessThan · greaterThanOrEqual · lessThanOrEqual.

Custom (formula)

Any formula that returns a truthy value is valid. Relative references are offset by (row - r1, col - c1) from the range’s top-left — the same relative-reference semantics as conditional-format expression rules:

// G2:G100 must be greater than the value to its left
ws.range('G2:G100').setValidation({
  type: 'custom',
  formula: '=G2>F2',
  errorMessage: 'Value must exceed the previous column.',
})

Any

{ type: 'any' } clears the effective rule while keeping any input prompt — useful for showing a hint without restricting input.

Alert styles

Only alertStyle: 'stop' (the default) actually blocks an invalid entry. 'warning' and 'information' surface the message but still allow the value through — pick them when you want to guide rather than enforce.

Reading and validating (Lite-readable)

ws.getValidations()          // ValidationEntry[]
ws.getValidationAt(row, col) // the rule covering a cell, or null
ws.validate()                // returns validity info for current values

These run in Lite, and Pro-authored rules enforce in Lite too.

xlsx round-trip

Rules serialize to <dataValidations> and round-trip through both xlsx and ReoGrid JSON, so a validated workbook keeps its rules when opened in Excel and back.

Related

• Cell Tooltip — richer contextual hints alongside validation prompts.
• Formula Engine — the functions available in custom-rule and comparison formulas.
• Cell Types — dropdown cell types for fixed choice lists.