Skip to Content
๐ŸŽ‰ Univer 0.6.0 is released.Read more โ†’
GuidesUniver SheetsFeaturesData Validation

Data Validation

Facade APIHas Paid PlanUniver ServerUniver on Node.jsPreset
โœ…--โœ…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:

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()
  ]
});

Piecemeal Installation

npm install @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, {
  // Whether to display the edit button in the drop-down menu
  showEditOnDropdown: true
});

Univer on Node.js Piecemeal Installation

npm install @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 defination of facade api, please refer to FacadeAPIโ€‰

Add a data validation rule

const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
// get range
const range = sheet.getRange(0, 0, 1, 1);
 
// build data validation
const dataValidationBuilder = FUniver.newDataValidation();
const dataValidation = dataValidationBuilder.requireCheckbox().build();
 
// set data validation
range.setDataValidation(dataValidation);

Clear data validations of range

const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
// get range
const range = sheet.getRange(0, 0, 10, 10); // A1:J10
 
// clear data validation of range
range.setDataValidation(null);

Get data validations of Range / Worksheet

const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
// get range
const range = sheet.getRange(0, 0, 10, 10); // A1:J10
 
// get data validation of range
const dataValidation = range.getDataValidation();
const dataValidations = range.getDataValidations();
 
// get data validation of worksheet
const dataValidationsOfSheet = sheet.getDataValidations();

Update/Delete data validation

import { DataValidationErrorStyle, DataValidationOperator, DataValidationType } from '@univerjs/core';
 
const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
// get range
const range = sheet.getRange(0, 0, 10, 10); // A1:J10
 
// get data validation
const dataValidation = range.getDataValidation();
 
// update data validation range
dataValidation.setRanges([{
  startRow: 0,
  startColumn: 0,
  endColumn: 20,
  endRow: 20,
}]);
 
// update data validation criteria
dataValidation.setCriteria(DataValidationType.WHOLE, [DataValidationOperator.BETWEEN, '1', '100']);
 
// update data validation options
dataValidation.setOptions({
  errorStyle: DataValidationErrorStyle.STOP,
});
 
// delete data validation
dataValidation.delete();

Get validator status

const workbook = univerAPI.getActiveWorkbook();
const worksheet = workbook.getActiveSheet();
 
// get range
const range = worksheet.getRange(0, 0, 10, 10); // A1:J10
 
range.getValidatorStatus().then((status) => {
  console.log('===status', status);
});
 
worksheet.getValidatorStatus().then((statusOfSheet) => {
  console.log('===statusOfSheet', statusOfSheet);
});
 
workbook?.getValidatorStatus().then((statusOfWorkbook) => {
  console.log('===statusOfWorkbook', statusOfWorkbook);
});

Event Listening

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
  • univerAPI.Event.BeforeSheetDataValidationAdd: Triggered before adding a validation rule
  • univerAPI.Event.BeforeSheetDataValidationDelete: Triggered before deleting a validation rule
  • univerAPI.Event.BeforeSheetDataValidationDeleteAll: Triggered before deleting all validation rules
// Listen for validation rule change event
univerAPI.addEvent(univerAPI.Event.SheetDataValidationChanged, (event) => {
    const { worksheet, workbook, changeType, oldRule, rule } = event;
    console.log('Validation rule changed:', { changeType, rule });
});
 
// Listen for before validation rule add event
univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationAdd, (event) => {
    const { worksheet, workbook, rule } = event;
    console.log('About to add validation rule:', rule);
    // Return false to prevent rule addition
    return true;
});
 
// Listen for before validation rule delete event
univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationDelete, (event) => {
    const { worksheet, workbook, ruleId, rule } = event;
    console.log('About to delete validation rule:', { ruleId, rule });
    // Return false to prevent rule deletion
    return true;
});
 
// Listen for before all validation rules delete event
univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationDeleteAll, (event) => {
    const { worksheet, workbook, rules } = event;
    console.log('About to delete all validation rules:', rules);
    // Return false to prevent rules deletion
    return true;
});

Validation Rule Update Events

  • univerAPI.Event.BeforeSheetDataValidationCriteriaUpdate: Triggered before updating validation rule criteria
  • univerAPI.Event.BeforeSheetDataValidationRangeUpdate: Triggered before updating validation rule ranges
  • univerAPI.Event.BeforeSheetDataValidationOptionsUpdate: Triggered before updating validation rule options
// Listen for before validation criteria update event
univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationCriteriaUpdate, (event) => {
    const { worksheet, workbook, ruleId, rule, newCriteria } = event;
    console.log('About to update validation criteria:', { ruleId, newCriteria });
    // Return false to prevent criteria update
    return true;
});
 
// Listen for before validation range update event
univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationRangeUpdate, (event) => {
    const { worksheet, workbook, ruleId, rule, newRanges } = event;
    console.log('About to update validation ranges:', { ruleId, newRanges });
    // Return false to prevent range update
    return true;
});
 
// Listen for before validation options update event
univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationOptionsUpdate, (event) => {
    const { worksheet, workbook, ruleId, rule, newOptions } = event;
    console.log('About to update validation options:', { ruleId, newOptions });
    // Return false to prevent options update
    return true;
});

Validation Status Events

  • univerAPI.Event.SheetDataValidatorStatusChanged: Triggered when a cellโ€™s validation status changes
// Listen for validation status change event
univerAPI.addEvent(univerAPI.Event.SheetDataValidatorStatusChanged, (event) => {
    const { worksheet, workbook, row, column, status, rule } = event;
    console.log('Cell validation status changed:', { row, column, status });
});

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 return false to prevent the corresponding operation.

Change List render mode

import { DataValidationRenderMode } from '@univerjs/core';
 
dataValidation.setOptions({
  // support TEXT, ARROW, CUSTOM
  // default is `CUSTOM`
  renderMode: DataValidationRenderMode.TEXT,
});
Was this page helpful?
Last updated on
SortConditional Formatting