Data Validation

GitHubEdit on GitHub
Preset Info
@univerjs/preset-sheets-data-validation
Server Required
No

Data validation is a feature that allows you to set rules in cells to ensure that the data entered meets specific requirements. It helps users avoid input errors and improves the accuracy and consistency of data.

Currently, the following data validation types are supported:

  • Number
  • Integer
  • Text length
  • Date
  • Checkbox
  • Dropdown list (single/multiple selection)
  • Custom formula

Preset Mode

Installation

npm install @univerjs/preset-sheets-data-validation

Usage

import { UniverSheetsCorePreset } from '@univerjs/preset-sheets-core'
import UniverPresetSheetsCoreEnUS from '@univerjs/preset-sheets-core/locales/en-US'
import { UniverSheetsDataValidationPreset } from '@univerjs/preset-sheets-data-validation'
import UniverPresetSheetsDataValidationEnUS from '@univerjs/preset-sheets-data-validation/locales/en-US'
import { createUniver, LocaleType, merge } from '@univerjs/presets'

import '@univerjs/preset-sheets-core/lib/index.css'
import '@univerjs/preset-sheets-data-validation/lib/index.css'

const { univerAPI } = createUniver({
  locale: LocaleType.En_US,
  locales: {
    [LocaleType.En_US]: merge(
      {},
      UniverPresetSheetsCoreEnUS,
      UniverPresetSheetsDataValidationEnUS, 
    ),
  },
  presets: [
    UniverSheetsCorePreset(),
    UniverSheetsDataValidationPreset(), 
  ],
})

Presets and Configuration

UniverSheetsDataValidationPreset({
  // Whether to display the edit button in the drop-down menu
  showEditOnDropdown: true,
})

Complete configuration options can be found in the IUniverSheetsDataValidationPresetConfig.

Plugin Mode

Installation

npm install @univerjs/data-validation @univerjs/sheets-data-validation @univerjs/sheets-data-validation-ui

Usage

import { LocaleType, merge, Univer } from '@univerjs/core'
import { UniverDataValidationPlugin } from '@univerjs/data-validation'
import { UniverSheetsDataValidationPlugin } from '@univerjs/sheets-data-validation'
import { UniverSheetsDataValidationUIPlugin } from '@univerjs/sheets-data-validation-ui'
import SheetsDataValidationEnUS from '@univerjs/sheets-data-validation-ui/locale/en-US'

import '@univerjs/sheets-data-validation-ui/lib/index.css'

import '@univerjs/sheets-data-validation/facade'

const univer = new Univer({
  locale: LocaleType.En_US,
  locales: {
    [LocaleType.En_US]: merge(
      {},
      SheetsDataValidationEnUS, 
    ),
  },
})

univer.registerPlugin(UniverDataValidationPlugin) 
univer.registerPlugin(UniverSheetsDataValidationPlugin) 
univer.registerPlugin(UniverSheetsDataValidationUIPlugin) 

Plugins and Configuration

univer.registerPlugin(UniverSheetsDataValidationUIPlugin, {
  // Whether to display the edit button in the drop-down menu
  showEditOnDropdown: true,
})

Complete configuration options can be found in the IUniverSheetsDataValidationUIConfig.

Facade API

Complete Facade API type definitions can be found in the FacadeAPI.

Add a data validation rule

univerAPI.newDataValidation() can create a new data validation builder, which returns an FDataValidationBuilder instance that can generate data validation rules through chained calls.

Here are some member methods on FDataValidationBuilder:

MethodDescription
buildBuild the data validation rule.
requireCheckboxSets the data validation rule to require that the input is a boolean value; this value is rendered as a checkbox
requireDateAfterSet the data validation type to DATE and configure the validation rules to be after a specific date
requireDateBeforeSet the data validation type to DATE and configure the validation rules to be before a specific date
requireDateBetweenSet the data validation type to DATE and configure the validation rules to be within a specific date range
requireDateEqualToSet the data validation type to DATE and configure the validation rules to be equal to a specific date
requireDateNotBetweenSet the data validation type to DATE and configure the validation rules to be not within a specific date range
requireDateOnOrAfterSet the data validation type to DATE and configure the validation rules to be on or after a specific date
requireDateOnOrBeforeSet the data validation type to DATE and configure the validation rules to be on or before a specific date
requireFormulaSatisfiedSets the data validation rule to require that the given formula evaluates to true
requireNumberBetweenSets the data validation rule to require a number that falls between, or is either of, two specified numbers
requireNumberEqualToSets the data validation rule to require a number equal to the given value
requireNumberGreaterThanSets the data validation rule to require a number greater than the given value
requireNumberGreaterThanOrEqualToSets the data validation rule to require a number greater than or equal to the given value
requireNumberLessThanSets the data validation rule to require a number less than the given value
requireNumberLessThanOrEqualToSets the data validation rule to require a number less than or equal to the given value
requireNumberNotBetweenSets the data validation rule to require a number that does not fall between, and is neither of, two specified numbers
requireNumberNotEqualToSets the data validation rule to require a number not equal to the given value
requireValueInListSets a data validation rule that requires the user to enter a value from a list of specific values
requireValueInRangeSets a data validation rule that requires the user to enter a value within a specific range
setOptionsSets the options for the data validation rule
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Create a new data validation rule that requires a number between 1 and 10 fot the range A1:B10
const fRange = fWorksheet.getRange('A1:B10')
const rule = univerAPI.newDataValidation()
  .requireNumberBetween(1, 10)
  .setOptions({
    allowBlank: true,
    showErrorMessage: true,
    error: 'Please enter a number between 1 and 10',
  })
  .build()
fRange.setDataValidation(rule)

Clear data validations of range

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Clear data validation of range A1:B10
const fRange = fWorksheet.getRange('A1:B10')
fRange.setDataValidation(null)

Get data validations of Range / Worksheet

The returned data validation rule object is an FDataValidation instance, which can be used to obtain the conditions, options, etc. of the validation rule.

Here are some member methods on FDataValidation:

MethodDescription
getCriteriaTypeGets the data validation type of the rule
getCriteriaValuesGets the values used for criteria evaluation
getHelpTextGets the help text information, which is used to provide users with guidance and support
getRangesGets the ranges to which the data validation rule is applied
setCriteriaSet Criteria for the data validation rule
setOptionsSet the options for the data validation rule
setRangesSet the ranges to the data validation rule
deleteDelete the data validation rule from the worksheet
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Get all data validation rules in current sheet.
const rulesOfSheet = fWorksheet.getDataValidations()

const fRange = fWorksheet.getRange('A1:B10')

// Get first data validation rule in current range.
const ruleOfRange = fRange.getDataValidation()
console.log('ruleOfRange:', ruleOfRange, ruleOfRange?.getCriteriaValues())

// Get all data validation rules in current range.
const rulesOfRange = fRange.getDataValidations()

Update/Delete data validation

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Create a new data validation rule that requires a number equal to 20 for the range A1:B10
const fRange = fWorksheet.getRange('A1:B10')
const rule = univerAPI.newDataValidation()
  .requireNumberEqualTo(20)
  .build()
fRange.setDataValidation(rule)

const newRuleRange = fWorksheet.getRange('C1:D10')

// Update the rule after 3 seconds
setTimeout(() => {
  fRange.getDataValidation()
    // Change the range to C1:D10
    .setRanges([newRuleRange])
    // Change the criteria to require a number between 1 and 10
    .setCriteria(
      univerAPI.Enum.DataValidationType.DECIMAL,
      [univerAPI.Enum.DataValidationOperator.BETWEEN, '1', '10'],
    )
    // Change option to stop input on validation errors and show error message
    .setOptions({
      allowBlank: true,
      showErrorMessage: true,
      error: 'Please enter a valid value',
      errorStyle: univerAPI.Enum.DataValidationErrorStyle.STOP,
    })
}, 3000)

// Delete the rule after 6 seconds
setTimeout(() => {
  newRuleRange.getDataValidation().delete()
}, 6000)

Get validator status

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Get data validation validator status for current workbook.
fWorkbook.getValidatorStatus().then((status) => {
  console.log('===statusOfWorkbook', status)
})

// Get data validation validator status for current sheet.
fWorksheet.getValidatorStatusAsync().then((status) => {
})

// Get data validation validator status for current range.
const fRange = fWorksheet.getRange('A1:B10')
fRange.getValidatorStatus().then((status) => {
  console.log('===tatusOfRange', status)
})

Event Listeners

Full event type definitions, please refer to Events.

The data validation module provides a series of events for monitoring validation rule additions, updates, deletions, and validation status changes. All events can be listened to using univerAPI.addEvent().

Validation Rule Change Events

univerAPI.Event.SheetDataValidationChanged: Triggered after a validation rule changes

const disposable = univerAPI.addEvent(univerAPI.Event.SheetDataValidationChanged, (params) => {
  const { origin, worksheet, workbook, changeType, oldRule, rule } = params
})

// Remove the event listener, use `disposable.dispose()`

univerAPI.Event.BeforeSheetDataValidationAdd: Triggered before adding a validation rule

const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationAdd, (params) => {
  const { worksheet, workbook, rule } = params

  // Cancel the data validation rule addition operation
  params.cancel = true
})

// Remove the event listener, use `disposable.dispose()`

univerAPI.Event.BeforeSheetDataValidationDelete: Triggered before deleting a validation rule

const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationDelete, (params) => {
  const { worksheet, workbook, ruleId, rule } = params

  // Cancel the data validation rule deletion operation
  params.cancel = true
})

// Remove the event listener, use `disposable.dispose()`

univerAPI.Event.BeforeSheetDataValidationDeleteAll: Triggered before deleting all validation rules

const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationDeleteAll, (params) => {
  const { worksheet, workbook, rules } = params

  // Cancel the data validation rule deletion operation
  params.cancel = true
})

// Remove the event listener, use `disposable.dispose()`

Validation Rule Update Events

univerAPI.Event.BeforeSheetDataValidationCriteriaUpdate: Triggered before updating validation rule criteria

const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationCriteriaUpdate, (params) => {
  const { worksheet, workbook, ruleId, rule, newCriteria } = params

  // Cancel the data validation rule criteria update operation
  params.cancel = true
})

// Remove the event listener, use `disposable.dispose()`

univerAPI.Event.BeforeSheetDataValidationRangeUpdate: Triggered before updating validation rule ranges

const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationRangeUpdate, (params) => {
  const { worksheet, workbook, ruleId, rule, newRanges } = params

  // Cancel the data validation rule range update operation
  params.cancel = true
})

// Remove the event listener, use `disposable.dispose()`

univerAPI.Event.BeforeSheetDataValidationOptionsUpdate: Triggered before updating validation rule options

const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationOptionsUpdate, (params) => {
  const { worksheet, workbook, ruleId, rule, newOptions } = params

  // Cancel the data validation rule options update operation
  params.cancel = true
})

// Remove the event listener, use `disposable.dispose()`

Validation Status Events

univerAPI.Event.SheetDataValidatorStatusChanged: Triggered when a cell's validation status changes

const disposable = univerAPI.addEvent(univerAPI.Event.SheetDataValidatorStatusChanged, (params) => {
  const { worksheet, workbook, row, column, status, rule } = params
})

// Remove the event listener, use `disposable.dispose()`

Each event includes the following common parameters:

  • workbook: Current workbook instance
  • worksheet: Current worksheet instance

Special parameters:

  • rule: The related validation rule object
  • ruleId: Unique identifier of the validation rule
  • status: Validation status (only in SheetDataValidatorStatusChanged event)
  • newCriteria: New validation criteria (only in BeforeSheetDataValidationCriteriaUpdate event)
  • newRanges: New validation ranges (only in BeforeSheetDataValidationRangeUpdate event)
  • newOptions: New validation options (only in BeforeSheetDataValidationOptionsUpdate event)

All event callbacks with the Before prefix can use params.cancel = true to prevent the corresponding operation.

Change List render mode

dataValidation.setOptions({
  // support TEXT, ARROW, CUSTOM
  // default is `CUSTOM`
  renderMode: univerAPI.Enum.DataValidationRenderMode.TEXT,
})

How is this guide?