🎉 v0.5.0 版本已发布。查看详情 →
指南Univer Sheets功能数据验证

数据验证

Facade API付费版本需要 Univer 服务端Univer on Node.jsPreset
✅--✅UniverSheetsDataValidationPreset

数据验证是一种在单元格中设置规则，以确保用户输入的数据符合特定条件的功能。 目前已经支持的数据验证类型:

  • 数字
  • 整数
  • 文本长度
  • 日期
  • 复选框
  • 下拉列表(单选/多选)
  • 自定义公式

该功能包含以下插件包：

Presets 安装

import { createUniver, defaultTheme, LocaleType, Tools } from '@univerjs/presets';
import { UniverSheetsCorePreset } from '@univerjs/presets/preset-sheets-core';
import UniverPresetSheetsCoreZhCN from '@univerjs/presets/preset-sheets-core/locales/zh-CN';
import { UniverSheetsDataValidationPreset } from '@univerjs/presets/preset-sheets-data-validation';
import UniverPresetSheetsDataValidationZhCN from '@univerjs/presets/preset-sheets-data-validation/locale/zh-CN';
 
const { univerAPI } = createUniver({
  locale: LocaleType.ZH_CN,
  locales: {
    zhCN: Tools.deepMerge(
      {},
      UniverPresetSheetsCoreZhCN,
      UniverPresetSheetsDataValidationZhCN 
    ),
  },
  theme: defaultTheme,
  presets: [
    UniverSheetsCorePreset(),
    UniverSheetsDataValidationPreset()
  ]
});

高级安装

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 SheetsDataValidationZhCN from '@univerjs/sheets-data-validation-ui/locale/zh-CN';
 
import '@univerjs/sheets-data-validation-ui/lib/index.css';
 
import '@univerjs/sheets-data-validation/facade';
 
const univer = new Univer({
  theme: defaultTheme,
  locale: LocaleType.ZH_CN,
  locales: {
    [LocaleType.ZH_CN]: Tools.deepMerge(
      SheetsDataValidationZhCN
    ),
  },
});
 
univer.registerPlugin(UniverDataValidationPlugin);
univer.registerPlugin(UniverSheetsDataValidationPlugin);
univer.registerPlugin(UniverSheetsDataValidationUIPlugin, {
  // 是否在下拉菜单中显示编辑按钮
  showEditOnDropdown: true
});

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+

完整 facade api 类型定义，请查看 FacadeAPI

添加一个数据验证规则

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);

清除 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);

获取 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();

更新/删除数据验证

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

查询校验状态

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

事件监听

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

变更列表型验证的渲染模式

import { DataValidationRenderMode } from '@univerjs/core';
 
dataValidation.setOptions({
  // support TEXT, ARROW, CUSTOM
  // default is `CUSTOM`
  renderMode: DataValidationRenderMode.TEXT,
});
排序条件格式