Skip to Content
ClassesFDataValidation

类: FDataValidation

属性

属性类型

rule

IDataValidationRule

方法

copy()

copy(): FDataValidationBuilder;

Creates a new instance of FDataValidationBuilder using the current rule object

返回

FDataValidationBuilder

A new FDataValidationBuilder instance with the same rule configuration

示例

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

返回

boolean

true if the rule is deleted successfully, false otherwise

示例

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

返回

boolean

true if invalid data is allowed, false otherwise

示例

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

返回

boolean

true if the rule is applied, false otherwise

示例

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

返回

any

The data validation type

示例

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

返回

[string, string, string]

An array containing the operator, formula1, and formula2 values

示例

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

返回

string

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

示例

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

返回

FRange[]

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

示例

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

返回

string

The sheet ID of the worksheet

示例

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

返回

string

The unit ID of the worksheet

示例

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

参数

参数类型描述
typeDataValidationTypeThe type of data validation criteria
values[DataValidationOperator, string, string]An array containing the operator, formula1, and formula2 values
allowBlank?booleanWhether to allow blank values

返回

FDataValidation

The current instance for method chaining

示例

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

参数

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

返回

FDataValidation

The current instance for method chaining

示例

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

参数

参数类型描述
rangesFRange[]New ranges array

返回

FDataValidation

The current instance for method chaining

示例

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