Skip to Content
ClassesFDataValidationBuilder

类: FDataValidationBuilder

Builder for data validation rules. use FUniver univerAPI.newDataValidation() to create a new builder.

示例

// Set the data validation for cell A1 to require a value from B1:B10
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('B1:B2');
fRange.setValues([
  ['Yes'],
  ['No']
]);
 
const rule = univerAPI.newDataValidation()
  .requireValueInRange(fRange)
  .setOptions({
    allowBlank: false,
    showErrorMessage: true,
    error: 'Please enter a value from the list'
  })
  .build();
const cell = fWorksheet.getRange('A1');
cell.setDataValidation(rule);

方法

build()

build(): FDataValidation

Builds an FDataValidation instance based on the _rule property of the current class

返回

FDataValidation

A new instance of the FDataValidation class

示例

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

copy()

copy(): FDataValidationBuilder

Creates a duplicate of the current DataValidationBuilder object

返回

FDataValidationBuilder

A new instance of the DataValidationBuilder class

示例

const builder = univerAPI.newDataValidation().requireNumberBetween(1, 10);
const copyBuilder = builder.copy();
console.log(copyBuilder);

getAllowInvalid()

getAllowInvalid(): boolean

Determines whether invalid data is allowed

返回

boolean

True if invalid data is allowed, False otherwise

示例

const builder = univerAPI.newDataValidation().requireNumberBetween(1, 10);
console.log(builder.getAllowInvalid());

getCriteriaType()

getCriteriaType(): any

Gets the data validation type of the rule

返回

any

The data validation type

示例

const builder = univerAPI.newDataValidation();
console.log(builder.getCriteriaType());
 
builder.requireNumberBetween(1, 10);
console.log(builder.getCriteriaType());
 
builder.requireValueInList(['Yes', 'No']);
console.log(builder.getCriteriaType());

getCriteriaValues()

getCriteriaValues(): [string, string, string]

Gets the values used for criteria evaluation

返回

[string, string, string]

An array containing the operator, formula1, and formula2 values

示例

const builder = univerAPI.newDataValidation().requireNumberBetween(1, 10);
const [operator, formula1, formula2] = builder.getCriteriaValues();
console.log(operator, formula1, formula2);
 
builder.requireValueInList(['Yes', 'No']);
console.log(builder.getCriteriaValues());

getHelpText()

getHelpText(): string

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

返回

string

Returns the help text information. If there is no error message, it returns an undefined value

示例

const builder = univerAPI.newDataValidation().setOptions({
  showErrorMessage: true,
  error: 'Please enter a valid value'
});
console.log(builder.getHelpText()); // 'Please enter a valid value'

requireCheckbox()

requireCheckbox(checkedValue?, uncheckedValue?): FDataValidationBuilder

Sets the data validation type to CHECKBOX and sets the checked and unchecked values

参数

参数类型描述
checkedValue?stringThe value when the checkbox is checked
uncheckedValue?stringThe value when the checkbox is unchecked

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Set the data validation for cell A1:A10 to require a checkbox with default 1 and 0 values
const fRange = fWorksheet.getRange('A1:A10');
const rule = univerAPI.newDataValidation()
  .requireCheckbox()
  .build();
fRange.setDataValidation(rule);
 
// Set the data validation for cell B1:B10 to require a checkbox with 'Yes' and 'No' values
const fRange2 = fWorksheet.getRange('B1:B10');
const rule2 = univerAPI.newDataValidation()
  .requireCheckbox('Yes', 'No')
  .build();
fRange2.setDataValidation(rule2);

requireDateAfter()

requireDateAfter(date): FDataValidationBuilder

Set the data validation type to DATE and configure the validation rules to be after a specific date

参数

参数类型描述
dateDateThe date to compare against

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
fRange.setValues([
  ['2024-01-01', '2024-12-31'],
  ['2025-01-01', '2025-12-31']
]);
const rule = univerAPI.newDataValidation()
  .requireDateAfter(new Date('2025-01-01'))
  .build();
fRange.setDataValidation(rule);
 
const status = await fRange.getValidatorStatus();
console.log(status); // [['invalid', 'invalid', 'invalid', 'valid']]

requireDateBefore()

requireDateBefore(date): FDataValidationBuilder

Set the data validation type to DATE and configure the validation rules to be before a specific date

参数

参数类型描述
dateDateThe date to compare against

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
fRange.setValues([
  ['2024-01-01', '2024-12-31'],
  ['2025-01-01', '2025-12-31']
]);
const rule = univerAPI.newDataValidation()
  .requireDateBefore(new Date('2025-01-01'))
  .build();
fRange.setDataValidation(rule);
 
const status = await fRange.getValidatorStatus();
console.log(status); // [['valid', 'valid', 'invalid', 'invalid']]

requireDateBetween()

requireDateBetween(start, end): FDataValidationBuilder

Set the data validation type to DATE and configure the validation rules to be within a specific date range

参数

参数类型描述
startDateThe starting date of the range
endDateThe ending date of the range

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
fRange.setValues([
  ['2024-01-01', '2024-12-31'],
  ['2025-01-01', '2025-12-31']
]);
const rule = univerAPI.newDataValidation()
  .requireDateBetween(new Date('2024-06-01'), new Date('2025-06-01'))
  .build();
fRange.setDataValidation(rule);
 
const status = await fRange.getValidatorStatus();
console.log(status); // [['invalid', 'valid', 'valid', 'invalid']]

requireDateEqualTo()

requireDateEqualTo(date): FDataValidationBuilder

Set the data validation type to DATE and configure the validation rules to be equal to a specific date

参数

参数类型描述
dateDateThe date to compare against

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
fRange.setValues([
  ['2024-01-01', '2024-12-31'],
  ['2025-01-01', '2025-12-31']
]);
const rule = univerAPI.newDataValidation()
  .requireDateEqualTo(new Date('2025-01-01'))
  .build();
fRange.setDataValidation(rule);
 
const status = await fWorksheet.getRange('A2').getValidatorStatus();
console.log(status?.[0]?.[0]); // 'valid'
 
const status2 = await fWorksheet.getRange('B2').getValidatorStatus();
console.log(status2?.[0]?.[0]); // 'invalid'

requireDateNotBetween()

requireDateNotBetween(start, end): FDataValidationBuilder

Set the data validation type to DATE and configure the validation rules to be not within a specific date range

参数

参数类型描述
startDateThe starting date of the date range
endDateThe ending date of the date range

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
fRange.setValues([
  ['2024-01-01', '2024-12-31'],
  ['2025-01-01', '2025-12-31']
]);
const rule = univerAPI.newDataValidation()
  .requireDateNotBetween(new Date('2024-06-01'), new Date('2025-06-01'))
  .build();
fRange.setDataValidation(rule);
 
const status = await fRange.getValidatorStatus();
console.log(status); // [['valid', 'invalid', 'invalid', 'valid']]

requireDateOnOrAfter()

requireDateOnOrAfter(date): FDataValidationBuilder

Set the data validation type to DATE and configure the validation rules to be on or after a specific date

参数

参数类型描述
dateDateThe date to compare against

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
fRange.setValues([
  ['2024-01-01', '2024-12-31'],
  ['2025-01-01', '2025-12-31']
]);
const rule = univerAPI.newDataValidation()
  .requireDateOnOrAfter(new Date('2025-01-01'))
  .build();
fRange.setDataValidation(rule);
 
const status = await fRange.getValidatorStatus();
console.log(status); // [['invalid', 'invalid', 'valid', 'valid']]

requireDateOnOrBefore()

requireDateOnOrBefore(date): FDataValidationBuilder

Set the data validation type to DATE and configure the validation rules to be on or before a specific date

参数

参数类型描述
dateDateThe date to compare against

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
fRange.setValues([
  ['2024-01-01', '2024-12-31'],
  ['2025-01-01', '2025-12-31']
]);
const rule = univerAPI.newDataValidation()
  .requireDateOnOrBefore(new Date('2025-01-01'))
  .build();
fRange.setDataValidation(rule);
 
const status = await fRange.getValidatorStatus();
console.log(status); // [['valid', 'valid', 'valid', 'invalid']]

requireFormulaSatisfied()

requireFormulaSatisfied(formula): FDataValidationBuilder

Requires that a custom formula be satisfied

参数

参数类型描述
formulastringThe formula string that needs to be satisfied, formula result should be TRUE or FALSE, and references range will relative offset

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const cell = fWorksheet.getRange('A1:B2');
cell.setValues([
  [4, 3],
  [2, 1]
]);
const fRange = fWorksheet.getRange('C1:D2');
fRange.setValues([
  [1, 2],
  [3, 4]
]);
const rule = univerAPI.newDataValidation()
  .requireFormulaSatisfied('=A1>2')
  .setOptions({
    showErrorMessage: true,
    error: 'Please enter a value equal to A1'
  })
  .build();
fRange.setDataValidation(rule);
 
const status = await fRange.getValidatorStatus();
console.log(status); // [['valid', 'valid', 'invalid', 'invalid']]

requireNumberBetween()

requireNumberBetween(
   start, 
   end, 
   isInteger?): FDataValidationBuilder

Requires the user to enter a number within a specific range, which can be integer or decimal

参数

参数类型描述
startnumberThe starting value of the number range
endnumberThe ending value of the number range
isInteger?booleanIndicates whether the required number is an integer

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B10');
const rule = univerAPI.newDataValidation()
  .requireNumberBetween(1, 10)
  .setOptions({
    allowBlank: false,
    showErrorMessage: true,
    error: 'Please enter a number between 1 and 10'
  })
  .build();
fRange.setDataValidation(rule);

requireNumberEqualTo()

requireNumberEqualTo(num, isInteger?): FDataValidationBuilder

Requires the user to enter a number that is equal to a specific value, which can be an integer or a decimal

参数

参数类型描述
numnumberThe number to which the entered number should be equal
isInteger?booleanIndicates whether the required number is an integer

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B10');
const rule = univerAPI.newDataValidation()
  .requireNumberEqualTo(10)
  .setOptions({
    allowBlank: false,
    showErrorMessage: true,
    error: 'Please enter a number equal to 10'
  })
  .build();
fRange.setDataValidation(rule);

requireNumberGreaterThan()

requireNumberGreaterThan(num, isInteger?): FDataValidationBuilder

Requires the user to enter a number that is greater than a specific value, which can be an integer or a decimal

参数

参数类型描述
numnumberThe number to which the entered number should be greater
isInteger?booleanIndicates whether the required number is an integer

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B10');
const rule = univerAPI.newDataValidation()
  .requireNumberGreaterThan(10)
  .setOptions({
    allowBlank: false,
    showErrorMessage: true,
    error: 'Please enter a number greater than 10'
  })
  .build();
fRange.setDataValidation(rule);

requireNumberGreaterThanOrEqualTo()

requireNumberGreaterThanOrEqualTo(num, isInteger?): FDataValidationBuilder

Requires the user to enter a number that is greater than or equal to a specific value, which can be an integer or a decimal

参数

参数类型描述
numnumberThe number to which the entered number should be greater than or equal
isInteger?booleanIndicates whether the required number is an integer

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B10');
const rule = univerAPI.newDataValidation()
  .requireNumberGreaterThanOrEqualTo(10)
  .setOptions({
    allowBlank: false,
    showErrorMessage: true,
    error: 'Please enter a number greater than 10 or equal to 10'
  })
  .build();
fRange.setDataValidation(rule);

requireNumberLessThan()

requireNumberLessThan(num, isInteger?): FDataValidationBuilder

Requires the user to enter a number that is less than a specific value, which can be an integer or a decimal

参数

参数类型描述
numnumberThe number to which the entered number should be less
isInteger?booleanIndicates whether the required number is an integer

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B10');
const rule = univerAPI.newDataValidation()
  .requireNumberLessThan(10)
  .setOptions({
    allowBlank: false,
    showErrorMessage: true,
    error: 'Please enter a number less than 10'
  })
  .build();
fRange.setDataValidation(rule);

requireNumberLessThanOrEqualTo()

requireNumberLessThanOrEqualTo(num, isInteger?): FDataValidationBuilder

Sets the data validation rule to require a number less than or equal to a specified value The specified value can be an integer or a decimal

参数

参数类型描述
numnumberThe number to which the entered number should be less than or equal
isInteger?booleanIndicates whether the required number is an integer

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B10');
const rule = univerAPI.newDataValidation()
  .requireNumberLessThanOrEqualTo(10)
  .setOptions({
    allowBlank: false,
    showErrorMessage: true,
    error: 'Please enter a number less than 10 or equal to 10'
  })
  .build();
fRange.setDataValidation(rule);

requireNumberNotBetween()

requireNumberNotBetween(
   start, 
   end, 
   isInteger?): FDataValidationBuilder

Sets a data validation rule that requires the user to enter a number outside a specified range The specified range includes all integers and decimals

参数

参数类型描述
startnumberThe starting point of the specified range
endnumberThe end point of the specified range
isInteger?booleanOptional parameter, indicating whether the number to be verified is an integer

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B10');
const rule = univerAPI.newDataValidation()
  .requireNumberNotBetween(1, 10)
  .setOptions({
    allowBlank: false,
    showErrorMessage: true,
    error: 'Please enter a number not between 1 and 10'
  })
  .build();
fRange.setDataValidation(rule);

requireNumberNotEqualTo()

requireNumberNotEqualTo(num, isInteger?): FDataValidationBuilder

Creates a data validation rule that requires the user to enter a number that is not equal to a specific value The specific value can be an integer or a decimal

参数

参数类型描述
numnumberThe number to which the entered number should not be equal
isInteger?booleanIndicates whether the required number is an integer

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B10');
const rule = univerAPI.newDataValidation()
  .requireNumberNotEqualTo(10)
  .setOptions({
    allowBlank: false,
    showErrorMessage: true,
    error: 'Please enter a number not equal to 10'
  })
  .build();
fRange.setDataValidation(rule);

requireValueInList()

requireValueInList(
   values, 
   multiple?, 
   showDropdown?): FDataValidationBuilder

Sets a data validation rule that requires the user to enter a value from a list of specific values The list can be displayed in a dropdown, and the user can choose multiple values according to the settings

参数

参数类型描述
valuesstring[]An array containing the specific values that the user can enter
multiple?booleanOptional parameter indicating whether the user can select multiple values
showDropdown?booleanOptional parameter indicating whether to display the list in a dropdown

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B10');
const rule = univerAPI.newDataValidation()
  .requireValueInList(['Yes', 'No'])
  .setOptions({
    allowBlank: true,
    showErrorMessage: true,
    error: 'Please enter a value from the list'
  })
  .build();
fRange.setDataValidation(rule);

requireValueInRange()

requireValueInRange(
   range, 
   multiple?, 
   showDropdown?): FDataValidationBuilder

Sets a data validation rule that requires the user to enter a value within a specific range The range is defined by an FRange object, which contains the unit ID, sheet name, and cell range

参数

参数类型描述
rangeFRangeAn FRange object representing the range of values that the user can enter
multiple?booleanOptional parameter indicating whether the user can select multiple values
showDropdown?booleanOptional parameter indicating whether to display the list in a dropdown

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('B1:B2');
fRange.setValues([
  ['Yes'],
  ['No']
]);
 
const rule = univerAPI.newDataValidation()
  .requireValueInRange(fRange)
  .setOptions({
    allowBlank: false,
    showErrorMessage: true,
    error: 'Please enter a value from the list'
  })
  .build();
const cell = fWorksheet.getRange('A1');
cell.setDataValidation(rule);

setAllowBlank()

setAllowBlank(allowBlank): FDataValidationBuilder

Sets whether to allow blank values

参数

参数类型描述
allowBlankbooleanWhether to allow blank values

返回

FDataValidationBuilder

The current instance for method chaining

示例

// Assume current sheet is empty data
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Set the data validation for cell A1:B2 to allow blank values
const fRange = fWorksheet.getRange('A1:B2');
const rule = univerAPI.newDataValidation()
  .requireValueInList(['Yes', 'No'])
  .setAllowBlank(true)
  .build();
fRange.setDataValidation(rule);
 
// Set the data validation for cell C1:D2 to not allow blank values
const fRange2 = fWorksheet.getRange('C1:D2');
const rule2 = univerAPI.newDataValidation()
  .requireValueInList(['Yes', 'No'])
  .setAllowBlank(false)
  .build();
fRange2.setDataValidation(rule2);

setAllowInvalid()

setAllowInvalid(allowInvalidData): FDataValidationBuilder

Sets whether to allow invalid data and configures the error style If invalid data is not allowed, the error style will be set to STOP, indicating that data entry must stop upon encountering an error If invalid data is allowed, the error style will be set to WARNING, indicating that a warning will be displayed when invalid data is entered, but data entry can continue

参数

参数类型描述
allowInvalidDatabooleanWhether to allow invalid data

返回

FDataValidationBuilder

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Set the data validation for cell A1:B2 to allow invalid data, so A1:B2 will display a warning when invalid data is entered
const fRange = fWorksheet.getRange('A1:B2');
const rule = univerAPI.newDataValidation()
  .requireValueInList(['Yes', 'No'])
  .setAllowInvalid(true)
  .build();
fRange.setDataValidation(rule);
 
// Set the data validation for cell C1:D2 to not allow invalid data, so C1:D2 will stop data entry when invalid data is entered
const fRange2 = fWorksheet.getRange('C1:D2');
const rule2 = univerAPI.newDataValidation()
  .requireValueInList(['Yes', 'No'])
  .setAllowInvalid(false)
  .build();
fRange2.setDataValidation(rule2);

setOptions()

setOptions(options): this

Sets the options for the data validation rule

参数

参数类型描述
optionsIDataValidationRuleOptionsThe options to set for the data validation rule

返回

this

The current instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B10');
const rule = univerAPI.newDataValidation()
  .requireValueInList(['Yes', 'No'])
  .setOptions({
    allowBlank: true,
    showErrorMessage: true,
    error: 'Please enter a value from the list'
  })
  .build();
fRange.setDataValidation(rule);
FConditionalFormattingBuilderFDefinedNameBuilder