Data Validation

Facade API	Has Paid Plan	Univer Server	Univer on Node.js	Preset
✅	-	-	✅	UniverSheetsDataValidationPreset

Data validation is a feature that sets rules in cells to ensure that the data entered by users meets specific conditions. Currently supported data validation types:

number
integer
Text length
date
checkbox
Drop-down list (single/multiple)
Custom formula

This feature includes the following plugin packages:

@univerjs/data-validation - Fundamental Data Validation Plugin
@univerjs/sheets-data-validation - Sheet Data Validation Plugin
@univerjs/sheets-data-validation-ui - Sheet Data Validation UI Plugin

Presets Installation


import { createUniver, defaultTheme, LocaleType, merge } from '@univerjs/presets';
import { UniverSheetsCorePreset } from '@univerjs/presets/preset-sheets-core';
import UniverPresetSheetsCoreEnUS from '@univerjs/presets/preset-sheets-core/locales/en-US';
import { UniverSheetsDataValidationPreset } from '@univerjs/presets/preset-sheets-data-validation';
import UniverPresetSheetsDataValidationEnUS from '@univerjs/presets/preset-sheets-data-validation/locales/en-US';
 
import '@univerjs/presets/lib/styles/preset-sheets-core.css'
import '@univerjs/presets/lib/styles/preset-sheets-data-validation.css'
 
const { univerAPI } = createUniver({
  locale: LocaleType.EN_US,
  locales: {
    [LocaleType.EN_US]: merge(
      {},
      UniverPresetSheetsCoreEnUS,
      UniverPresetSheetsDataValidationEnUS 
    ),
  },
  theme: defaultTheme,
  presets: [
    UniverSheetsCorePreset(),
    UniverSheetsDataValidationPreset()
  ]
});

Configuration


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

For a complete list of configuration options, see IUniverSheetsDataValidationPresetConfig 。

Piecemeal Installation


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


pnpm add @univerjs/data-validation @univerjs/sheets-data-validation @univerjs/sheets-data-validation-ui


yarn add @univerjs/data-validation @univerjs/sheets-data-validation @univerjs/sheets-data-validation-ui


bun add @univerjs/data-validation @univerjs/sheets-data-validation @univerjs/sheets-data-validation-ui


import { LocaleType, merge, Univer } from '@univerjs/core';
import { defaultTheme } from "@univerjs/design";
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({
  theme: defaultTheme,
  locale: LocaleType.EN_US,
  locales: {
    [LocaleType.EN_US]: merge(
      SheetsDataValidationEnUS
    ),
  },
});
 
univer.registerPlugin(UniverDataValidationPlugin);
univer.registerPlugin(UniverSheetsDataValidationPlugin);
univer.registerPlugin(UniverSheetsDataValidationUIPlugin);

Configuration


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

For a complete list of configuration options, see IUniverSheetsDataValidationUIConfig 。

Univer on Node.js Piecemeal Installation


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


pnpm add @univerjs/data-validation @univerjs/sheets-data-validation


yarn add @univerjs/data-validation @univerjs/sheets-data-validation


bun add @univerjs/data-validation @univerjs/sheets-data-validation


import { LocaleType, Univer } from '@univerjs/core';
import { defaultTheme } from "@univerjs/design";
import { UniverDataValidationPlugin } from '@univerjs/data-validation';
import { UniverSheetsDataValidationPlugin } from '@univerjs/sheets-data-validation';
 
import '@univerjs/sheets-data-validation/facade';
 
const univer = new Univer({
  theme: defaultTheme,
  locale: LocaleType.ZH_CN,
});
 
univer.registerPlugin(UniverDataValidationPlugin);
univer.registerPlugin(UniverSheetsDataValidationPlugin);

Facade API 0.2.10+

To get full definition of facade api, please refer to 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:

Method	Description
build	Build the data validation rule.
requireCheckbox	Sets the data validation rule to require that the input is a boolean value; this value is rendered as a checkbox
requireDateAfter	Set the data validation type to DATE and configure the validation rules to be after a specific date
requireDateBefore	Set the data validation type to DATE and configure the validation rules to be before a specific date
requireDateBetween	Set the data validation type to DATE and configure the validation rules to be within a specific date range
requireDateEqualTo	Set the data validation type to DATE and configure the validation rules to be equal to a specific date
requireDateNotBetween	Set the data validation type to DATE and configure the validation rules to be not within a specific date range
requireDateOnOrAfter	Set the data validation type to DATE and configure the validation rules to be on or after a specific date
requireDateOnOrBefore	Set the data validation type to DATE and configure the validation rules to be on or before a specific date
requireFormulaSatisfied	Sets the data validation rule to require that the given formula evaluates to `true`
requireNumberBetween	Sets the data validation rule to require a number that falls between, or is either of, two specified numbers
requireNumberEqualTo	Sets the data validation rule to require a number equal to the given value
requireNumberGreaterThan	Sets the data validation rule to require a number greater than the given value
requireNumberGreaterThanOrEqualTo	Sets the data validation rule to require a number greater than or equal to the given value
requireNumberLessThan	Sets the data validation rule to require a number less than the given value
requireNumberLessThanOrEqualTo	Sets the data validation rule to require a number less than or equal to the given value
requireNumberNotBetween	Sets the data validation rule to require a number that does not fall between, and is neither of, two specified numbers
requireNumberNotEqualTo	Sets the data validation rule to require a number not equal to the given value
requireValueInList	Sets a data validation rule that requires the user to enter a value from a list of specific values
requireValueInRange	Sets a data validation rule that requires the user to enter a value within a specific range
setOptions	Sets 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:

Method	Description
getCriteriaType	Gets the data validation type of the rule
getCriteriaValues	Gets the values used for criteria evaluation
getHelpText	Gets the help text information, which is used to provide users with guidance and support
getRanges	Gets the ranges to which the data validation rule is applied
setCriteria	Set Criteria for the data validation rule
setOptions	Set the options for the data validation rule
setRanges	Set the ranges to the data validation rule
delete	Delete 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();
console.log('rulesOfSheet:', rulesOfSheet);
 
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();
console.log('rulesOfRange:', rulesOfRange);

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) => {
  console.log('===statusOfSheet', status);
});
 
// Get data validation validator status for current range.
const fRange = fWorksheet.getRange('A1:B10');
fRange.getValidatorStatus().then((status) => {
  console.log('===statusOfRange', status);
});

Event Listening

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;
  console.log(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;
  console.log(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;
  console.log(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;
  console.log(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;
  console.log(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;
  console.log(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;
  console.log(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;
  console.log(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,
});