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