Data Validation Facade API 0.2.10+

📊 Univer Sheet

To get full defination of facade api, please refer to FacadeAPI

Add a data validation rule

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

Clear data validations of 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);

Get data validations of 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();

Update/Delete data validation

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

Get validator status

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

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);
});
 
## Other
### Change List render mode
```typescript
// update data validation options
dataValidation.setOptions({
    // support TEXT, ARROW, CUSTOM
    // default is `CUSTOM`
    renderMode: DataValidationRenderMode.TEXT,
});

Data Validation Conditional Formatting