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()
]
});
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
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 instanceworksheet
: Current worksheet instance
Special parameters:
rule
: The related validation rule objectruleId
: Unique identifier of the validation rulestatus
: Validation status (only inSheetDataValidatorStatusChanged
event)newCriteria
: New validation criteria (only inBeforeSheetDataValidationCriteriaUpdate
event)newRanges
: New validation ranges (only inBeforeSheetDataValidationRangeUpdate
event)newOptions
: New validation options (only inBeforeSheetDataValidationOptionsUpdate
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,
});