数据验证 Facade API 0.2.10+

📊 Univer Sheet

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

添加一个数据验证规则

import { FUniver } from '@univerjs/facade';
 
const univerAPI = FUniver.newAPI(univer);
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 的数据验证规则

import { FUniver } from '@univerjs/facade';
 
const univerAPI = FUniver.newAPI(univer);
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 的数据验证规则

import { FUniver } from '@univerjs/facade';
 
const univerAPI = FUniver.newAPI(univer);
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';
import { FUniver } from '@univerjs/facade';
 
const univerAPI = FUniver.newAPI(univer);
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();

查询校验状态

import { FUniver } from '@univerjs/facade';
 
const univerAPI = FUniver.newAPI(univer);
 
// get range
const workbook = univerAPI.getActiveWorkbook();
const worksheet = workbook.getActiveSheet();
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);
});

事件监听

import { FUniver } from '@univerjs/facade';
const univerAPI = FUniver.newAPI(univer);
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);
});

其他

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

dataValidation.setOptions({
    // support TEXT, ARROW, CUSTOM
    // default is `CUSTOM`
    renderMode: DataValidationRenderMode.TEXT,
});

数据验证条件格式