Class: FDataValidation

Properties

Property	Type
`rule`	`IDataValidationRule`

Methods

copy()


copy(): FDataValidationBuilder

Creates a new instance of FDataValidationBuilder using the current rule object

Returns

FDataValidationBuilder

A new FDataValidationBuilder instance with the same rule configuration

Example


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(): boolean

Delete the data validation rule from the worksheet

Returns

boolean

true if the rule is deleted successfully, false otherwise

Example


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


getAllowInvalid(): boolean

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

Returns

boolean

true if invalid data is allowed, false otherwise

Example


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

getApplied()


getApplied(): boolean

Gets whether the data validation rule is applied to the worksheet

Returns

boolean

true if the rule is applied, false otherwise

Example


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


getCriteriaType(): any

Gets the data validation type of the rule

Returns

any

The data validation type

Example


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

getCriteriaValues()


getCriteriaValues(): [string, string, string]

Gets the values used for criteria evaluation

Returns

[string, string, string]

An array containing the operator, formula1, and formula2 values

Example


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


getHelpText(): string

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

Returns

string

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

Example


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


getRanges(): FRange[]

Gets the ranges to which the data validation rule is applied

Returns

FRange[]

An array of FRange objects representing the ranges to which the data validation rule is applied

Example


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


getSheetId(): string

Gets the sheet ID of the worksheet

Returns

string

The sheet ID of the worksheet

Example


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

getUnitId()


getUnitId(): string

Gets the unit ID of the worksheet

Returns

string

The unit ID of the worksheet

Example


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

setCriteria()


setCriteria(
   type, 
   values, 
   allowBlank?): FDataValidation

Set Criteria for the data validation rule

Parameters

Parameter	Type	Description
`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

Example


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


setOptions(options): FDataValidation

Set the options for the data validation rule

Parameters

Parameter	Type	Description
`options`	`IDataValidationRuleOptions`	The options to set for the data validation rule

Returns

FDataValidation

The current instance for method chaining

Example


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


setRanges(ranges): FDataValidation

Set the ranges to the data validation rule

Parameters

Parameter	Type	Description
`ranges`	`FRange`[]	New ranges array

Returns

FDataValidation

The current instance for method chaining

Example


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