# Data Validation

- Human documentation: [https://docs.univer.ai/guides/sheets/features/data-validation](https://docs.univer.ai/guides/sheets/features/data-validation)

- Agent Markdown: [https://docs.univer.ai/guides/sheets/features/data-validation.md](https://docs.univer.ai/guides/sheets/features/data-validation.md)

- Requested language: `en-US`

- Content language: `en-US`

- Documentation version: `1.0.0-rc.0`

- Source: [sheets/features/data-validation.mdx](https://github.com/dream-num/documentation/blob/dev/content/guides/sheets/features/data-validation.mdx)

---

#### Package metadata

```json
{
  "preset": [
    {
      "client": "@univerjs/preset-sheets-data-validation",
      "locale": "@univerjs/preset-sheets-data-validation/locales/en-US",
      "style": "@univerjs/preset-sheets-data-validation/lib/index.css"
    }
  ],
  "plugins": [
    {
      "client": "@univerjs/data-validation",
      "locale": "@univerjs/data-validation/locale/en-US"
    },
    {
      "client": "@univerjs/sheets-data-validation",
      "facade": "@univerjs/sheets-data-validation/facade",
      "locale": "@univerjs/sheets-data-validation/locale/en-US"
    },
    {
      "client": "@univerjs/sheets-data-validation-ui",
      "locale": "@univerjs/sheets-data-validation-ui/locale/en-US",
      "style": "@univerjs/sheets-data-validation-ui/lib/index.css"
    }
  ],
  "mobile": "Supported",
  "server": false
}
```

Data validation is a feature that allows you to set rules in cells to ensure that the data entered meets specific requirements. It helps users avoid input errors and improves the accuracy and consistency of data.

Currently, the following data validation types are supported:

* Number
* Integer
* Text length
* Date
* Checkbox
* Dropdown list (single/multiple selection)
* Custom formula

## Preset Mode

### Installation

#### npm

```bash
npm install @univerjs/preset-sheets-data-validation
```

#### pnpm

```bash
pnpm add @univerjs/preset-sheets-data-validation
```

#### yarn

```bash
yarn add @univerjs/preset-sheets-data-validation
```

#### bun

```bash
bun add @univerjs/preset-sheets-data-validation
```

### Usage

```typescript
import { UniverSheetsCorePreset } from '@univerjs/preset-sheets-core'
import UniverPresetSheetsCoreEnUS from '@univerjs/preset-sheets-core/locales/en-US'
import { UniverSheetsDataValidationPreset } from '@univerjs/preset-sheets-data-validation' // [!code ++]
import UniverPresetSheetsDataValidationEnUS from '@univerjs/preset-sheets-data-validation/locales/en-US' // [!code ++]
import { createUniver, LocaleType, mergeLocales } from '@univerjs/presets'

import '@univerjs/preset-sheets-core/lib/index.css'
import '@univerjs/preset-sheets-data-validation/lib/index.css' // [!code ++]

const { univerAPI } = createUniver({
  locale: LocaleType.EN_US,
  locales: {
    [LocaleType.EN_US]: mergeLocales(
      UniverPresetSheetsCoreEnUS,
      UniverPresetSheetsDataValidationEnUS, // [!code ++]
    ),
  },
  presets: [
    UniverSheetsCorePreset(),
    UniverSheetsDataValidationPreset(), // [!code ++]
  ],
})
```

### Presets and Configuration

```typescript
UniverSheetsDataValidationPreset({
  /**
   * Whether to display the edit button in the drop-down menu
   * @default true
   */
  showEditOnDropdown: true,
  /**
   * Whether to display the search box in the drop-down menu
   * @default true
   */
  showSearchOnDropdown: true,
})
```

Complete configuration options can be found in the [`IUniverSheetsDataValidationPresetConfig`](https://github.com/dream-num/univer/tree/dev/presets/packages/preset-sheets-data-validation).

## Plugin Mode

### Installation

#### npm

```bash
npm install @univerjs/data-validation @univerjs/sheets-data-validation @univerjs/sheets-data-validation-ui
```

#### pnpm

```bash
pnpm add @univerjs/data-validation @univerjs/sheets-data-validation @univerjs/sheets-data-validation-ui
```

#### yarn

```bash
yarn add @univerjs/data-validation @univerjs/sheets-data-validation @univerjs/sheets-data-validation-ui
```

#### bun

```bash
bun add @univerjs/data-validation @univerjs/sheets-data-validation @univerjs/sheets-data-validation-ui
```

### Usage

```typescript
import { LocaleType, mergeLocales, Univer } from '@univerjs/core'
import { UniverDataValidationPlugin } from '@univerjs/data-validation' // [!code ++]
import { UniverSheetsDataValidationPlugin } from '@univerjs/sheets-data-validation' // [!code ++]
import { UniverSheetsDataValidationUIPlugin } from '@univerjs/sheets-data-validation-ui' // [!code ++]
import SheetsDataValidationEnUS from '@univerjs/sheets-data-validation-ui/locale/en-US' // [!code ++]

import '@univerjs/sheets-data-validation-ui/lib/index.css' // [!code ++]

import '@univerjs/sheets-data-validation/facade' // [!code ++]

const univer = new Univer({
  locale: LocaleType.EN_US,
  locales: {
    [LocaleType.EN_US]: mergeLocales(
      SheetsDataValidationEnUS, // [!code ++]
    ),
  },
})

univer.registerPlugin(UniverDataValidationPlugin) // [!code ++]
univer.registerPlugin(UniverSheetsDataValidationPlugin) // [!code ++]
univer.registerPlugin(UniverSheetsDataValidationUIPlugin) // [!code ++]
```

### Plugins and Configuration

```typescript
univer.registerPlugin(UniverSheetsDataValidationUIPlugin, {
  /**
   * Whether to display the edit button in the drop-down menu
   * @default true
   */
  showEditOnDropdown: true,
  /**
   * Whether to display the search box in the drop-down menu
   * @default true
   */
  showSearchOnDropdown: true,
})
```

Complete configuration options can be found in the [`IUniverSheetsDataValidationUIConfig`](https://github.com/dream-num/univer/blob/dev/packages/sheets-data-validation-ui/src/config/config.ts).

## Facade API

Complete Facade API type definitions can be found in the [FacadeAPI](https://reference.univer.ai/en-US).

### Importing

> [!INFO: Plugin mode note]
> Only plugin mode requires manually importing the Facade package. Preset mode already includes the corresponding Facade package, so no extra import is needed.

```typescript
import '@univerjs/sheets-data-validation/facade'
```

### Add a data validation rule

[`univerAPI.newDataValidation()`](https://docs.univer.ai/reference/facade/univer.md#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`](https://docs.univer.ai/reference/facade/data-validation.md):

| 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. Supports single/multiple selection and show/hide dropdown |
| requireValueInRange               | Sets a data validation rule that requires the user to enter a value within a specific range. Supports single/multiple selection and show/hide dropdown        |
| setOptions                        | Sets the options for the data validation rule                                                                                                                 |

```typescript
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Create a new data validation rule that requires a number between 1 and 10 for 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

```typescript
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`](https://docs.univer.ai/reference/facade/data-validation.md):

| Method            | Description                                                                              |
| ----------------- | ---------------------------------------------------------------------------------------- |
| copy              | Creates a new instance of FDataValidationBuilder using the current rule object           |
| 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                                       |

```typescript
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Get all data validation rules in current sheet.
const rulesOfSheet = fWorksheet.getDataValidations()

// Get a data validation rule by rule ID.
const ruleById = fWorksheet.getDataValidation(rulesOfSheet[0]?.rule?.uid)

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

### Update/Delete data validation

```typescript
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 validation errors

```typescript
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Get all data validation errors for current workbook.
const workbookErrors = await fWorkbook.getAllDataValidationErrorAsync()
console.log('workbookErrors:', workbookErrors)

// Get all data validation errors for current sheet.
const worksheetErrors = await fWorksheet.getAllDataValidationErrorAsync()
console.log('worksheetErrors:', worksheetErrors)

// Get data validation errors for current range.
const fRange = fWorksheet.getRange('A1:B10')
const rangeErrors = await fRange.getDataValidationErrorAsync()
console.log('rangeErrors:', rangeErrors)
```

Each error object contains the following fields:

* `sheetName`: The name of the worksheet where the error occurred
* `row`: The row index of the cell that triggered the error
* `column`: The column index of the cell that triggered the error
* `ruleId`: The ID of the rule that triggered the error
* `inputValue`: The input value that triggered the error
* `rule`: The rule content snapshot

### Get validator status

The validator status is an enum value of `DataValidationStatus`, which includes:

* `valid`: The cell value passes validation
* `invalid`: The cell value fails validation
* `validating`: The cell is being validated

```typescript
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) => {
})

// Get data validation validator status for current range.
const fRange = fWorksheet.getRange('A1:B10')
fRange.getValidatorStatus().then((status) => {
  console.log('===tatusOfRange', status)
})
```

### Event Listeners

Full event type definitions, please refer to [Events](https://docs.univer.ai/reference/facade/events.md).

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

```typescript
const disposable = univerAPI.addEvent(univerAPI.Event.SheetDataValidationChanged, (params) => {
  const { origin, worksheet, workbook, changeType, oldRule, rule } = params
})

// Remove the event listener, use `disposable.dispose()`
```

`univerAPI.Event.BeforeSheetDataValidationAdd`: Triggered before adding a validation rule

```typescript
const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationAdd, (params) => {
  const { worksheet, workbook, rule } = 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

```typescript
const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationDelete, (params) => {
  const { worksheet, workbook, ruleId, rule } = 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

```typescript
const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationDeleteAll, (params) => {
  const { worksheet, workbook, rules } = 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

```typescript
const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationCriteriaUpdate, (params) => {
  const { worksheet, workbook, ruleId, rule, newCriteria } = 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

```typescript
const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationRangeUpdate, (params) => {
  const { worksheet, workbook, ruleId, rule, newRanges } = 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

```typescript
const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetDataValidationOptionsUpdate, (params) => {
  const { worksheet, workbook, ruleId, rule, newOptions } = 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

```typescript
const disposable = univerAPI.addEvent(univerAPI.Event.SheetDataValidatorStatusChanged, (params) => {
  const { worksheet, workbook, row, column, status, rule } = params
})

// Remove the event listener, use `disposable.dispose()`
```

Each event includes the following common parameters:

* `workbook`: Current workbook instance
* `worksheet`: Current worksheet instance

Special parameters:

* `rule`: The related validation rule object
* `ruleId`: Unique identifier of the validation rule
* `status`: Validation status (only in `SheetDataValidatorStatusChanged` event)
* `newCriteria`: New validation criteria (only in `BeforeSheetDataValidationCriteriaUpdate` event)
* `newRanges`: New validation ranges (only in `BeforeSheetDataValidationRangeUpdate` event)
* `newOptions`: New validation options (only in `BeforeSheetDataValidationOptionsUpdate` event)

All event callbacks with the `Before` prefix can use `params.cancel = true` to prevent the corresponding operation.

### Error style

The `errorStyle` option controls the behavior when invalid data is entered. It is an enum value of `DataValidationErrorStyle`, which includes:

* `STOP`: Prevents the user from entering invalid data
* `WARNING`: Allows the user to enter invalid data, but displays a warning
* `INFO`: Allows the user to enter invalid data, but displays an information message

```typescript
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
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',
    errorStyle: univerAPI.Enum.DataValidationErrorStyle.STOP,
  })
  .build()
fRange.setDataValidation(rule)
```

### Change List render mode

```typescript
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B10')

fRange.getDataValidation()?.setOptions({
  // support TEXT, ARROW, CUSTOM
  // default is `CUSTOM`
  renderMode: univerAPI.Enum.DataValidationRenderMode.TEXT,
})
```