🎉 v0.5.0 is released. Read more →
GuidesUniver SheetsFeaturesData Validation

Data Validation

Facade APIPaid VersionUniver 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:

Installation with Presets

import { createUniver, defaultTheme, LocaleType, Tools } 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/locale/en-US';
 
const { univerAPI } = createUniver({
  locale: LocaleType.EN_US,
  locales: {
    enUS: Tools.deepMerge(
      {},
      UniverPresetSheetsCoreEnUS,
      UniverPresetSheetsDataValidationEnUS 
    ),
  },
  theme: defaultTheme,
  presets: [
    UniverSheetsCorePreset(),
    UniverSheetsDataValidationPreset()
  ]
});

Advanced Installation

npm install @univerjs/data-validation @univerjs/sheets-data-validation @univerjs/sheets-data-validation-ui
import { LocaleType, Tools } 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({
  theme: defaultTheme,
  locale: LocaleType.EN_US,
  locales: {
    [LocaleType.EN_US]: Tools.deepMerge(
      SheetsDataValidationEnUS
    ),
  },
});
 
univer.registerPlugin(UniverDataValidationPlugin);
univer.registerPlugin(UniverSheetsDataValidationPlugin);
univer.registerPlugin(UniverSheetsDataValidationUIPlugin, {
  // Whether to display the edit button in the drop-down menu
  showEditOnDropdown: true
});

Installation for Univer on Node.js

npm install @univerjs/data-validation @univerjs/sheets-data-validation
import { LocaleType, Tools } from '@univerjs/core';
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

const workbook = univerAPI.getActiveWorkbook();
 
// don't forget to dispose the listener
const dispose = workbook.onBeforeAddDataValidation((event) => {
  console.log('===onBeforeAddDataValidation', event);
});
 
workbook.onBeforeDeleteAllDataValidation((event) => {
  console.log('===onBeforeDeleteAllDataValidation', event);
});
 
workbook.onBeforeUpdateDataValidationCriteria((event) => {
  console.log('===onBeforeUpdateDataValidationCriteria', event);
});
 
workbook.onBeforeUpdateDataValidationRange((event) => {
  console.log('===onBeforeUpdateDataValidationRange', event);
});
 
workbook.onBeforeUpdateDataValidationOptions((event) => {
  console.log('===onBeforeUpdateDataValidationOptions', event);
});
 
workbook.onDataValidationChange((event) => {
  console.log('===onDataValidationChange', event);
});
 
workbook.onDataValidationStatusChange((event) => {
  console.log('===onDataValidationStatusChange', event);
});

Change List render mode

import { DataValidationRenderMode } from '@univerjs/core';
 
dataValidation.setOptions({
  // support TEXT, ARROW, CUSTOM
  // default is `CUSTOM`
  renderMode: DataValidationRenderMode.TEXT,
});
SortConditional Formatting