FDataValidation

packages	`@univerjs/sheets-data-validation`

APIs

`copy`

Creates a new instance of FDataValidationBuilder using the current rule object

Signature

copy(): FDataValidationBuilder

Returns

(FDataValidationBuilder) — A new FDataValidationBuilder instance with the same rule configuration

Examples

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',
  })
  .build()
fRange.setDataValidation(rule)

const builder = fRange.getDataValidation().copy()
const newRule = builder
  .requireNumberBetween(1, 5)
  .setOptions({
    error: 'Please enter a number between 1 and 5',
  })
  .build()
fRange.setDataValidation(newRule)

`delete`

Delete the data validation rule from the worksheet

Signature

delete(): boolean

Returns

(boolean) — true if the rule is deleted successfully, false otherwise

Examples

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)

// Delete the data validation rule
fRange.getDataValidation().delete()

`getAllowInvalid`

Gets whether invalid data is allowed based on the error style value

Signature

getAllowInvalid(): boolean

Returns

(boolean) — true if invalid data is allowed, false otherwise

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const rules = fWorksheet.getDataValidations()
rules.forEach((rule) => {
  console.log(rule, rule.getAllowInvalid())
})

`getApplied`

Gets whether the data validation rule is applied to the worksheet

Signature

getApplied(): boolean

Returns

(boolean) — true if the rule is applied, false otherwise

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const rules = fWorksheet.getDataValidations()
rules.forEach((rule) => {
  console.log(rule, rule.getApplied())
})

const fRange = fWorksheet.getRange('A1:B10')
console.log(fRange.getDataValidation()?.getApplied())

`getCriteriaType`

Gets the data validation type of the rule

Signature

getCriteriaType(): DataValidationType | string

Returns

(DataValidationType | string) — The data validation type

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const rules = fWorksheet.getDataValidations()
rules.forEach((rule) => {
  console.log(rule, rule.getCriteriaType())
})

`getCriteriaValues`

Gets the values used for criteria evaluation

Signature

getCriteriaValues(): [string | undefined, string | undefined, string | undefined]

Returns

([string | undefined, string | undefined, string | undefined]) — An array containing the operator, formula1, and formula2 values

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const rules = fWorksheet.getDataValidations()
rules.forEach((rule) => {
  console.log(rule)
  const criteriaValues = rule.getCriteriaValues()
  const [operator, formula1, formula2] = criteriaValues
  console.log(operator, formula1, formula2)
})

`getHelpText`

Gets the help text information, which is used to provide users with guidance and support

Signature

getHelpText(): string | undefined

Returns

(string | undefined) — Returns the help text information. If there is no error message, it returns an undefined value

Examples

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',
  })
  .build()
fRange.setDataValidation(rule)
console.log(fRange.getDataValidation().getHelpText()) // 'Please enter a number between 1 and 10'

`getRanges`

Gets the ranges to which the data validation rule is applied

Signature

getRanges(): FRange[]

Returns

(FRange[]) — An array of FRange objects representing the ranges to which the data validation rule is applied

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const rules = fWorksheet.getDataValidations()
rules.forEach((rule) => {
  console.log(rule)
  const ranges = rule.getRanges()
  ranges.forEach((range) => {
    console.log(range.getA1Notation())
  })
})

`getSheetId`

Gets the sheet ID of the worksheet

Signature

getSheetId(): string | undefined

Returns

(string | undefined) — The sheet ID of the worksheet

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B10')
console.log(fRange.getDataValidation().getSheetId())

`getUnitId`

Gets the unit ID of the worksheet

Signature

getUnitId(): string | undefined

Returns

(string | undefined) — The unit ID of the worksheet

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B10')
console.log(fRange.getDataValidation().getUnitId())

`setCriteria`

Set Criteria for the data validation rule

Signature

setCriteria(type: DataValidationType, values: [DataValidationOperator, string, string], allowBlank?: boolean = true): FDataValidation

Parameters

type (DataValidationType) — - The type of data validation criteria
values ([DataValidationOperator, string, string]) — - An array containing the operator, formula1, and formula2 values
allowBlank (boolean) — - Whether to allow blank values

Returns

(FDataValidation) — The current instance for method chaining

Examples

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)

// Change the rule criteria to require a number between 1 and 10
fRange.getDataValidation().setCriteria(
  univerAPI.Enum.DataValidationType.DECIMAL,
  [univerAPI.Enum.DataValidationOperator.BETWEEN, '1', '10'],
)

`setOptions`

Set the options for the data validation rule

Signature

setOptions(options: Partial<IDataValidationRuleOptions>): FDataValidation

Parameters

options (Partial<IDataValidationRuleOptions>) — - The options to set for the data validation rule

Returns

(FDataValidation) — The current instance for method chaining

Examples

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)

// Supplement the rule with additional options
fRange.getDataValidation().setOptions({
  allowBlank: true,
  showErrorMessage: true,
  error: 'Please enter a valid value',
})

`setRanges`

Set the ranges to the data validation rule

Signature

setRanges(ranges: FRange[]): FDataValidation

Parameters

ranges (FRange[]) — - New ranges array

Returns

(FDataValidation) — The current instance for method chaining

Examples

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)

// Change the range to C1:D10
const newRuleRange = fWorksheet.getRange('C1:D10')
fRange.getDataValidation().setRanges([newRuleRange])

Table of Contents