Excel.DataValidationRule interface
数据验证规则包含不同类型的数据验证。 根据 Excel.DataValidationType，一次只能使用其中一个。
属性
| custom | 自定义数据有效性条件。 |
| date | 日期数据有效性条件。 |
| decimal | 小数数据有效性条件。 |
| list | 列表数据有效性条件。 |
| text |
文本长度数据验证条件。 |
| time | 时间数据有效性条件。 |
| whole |
整数数据验证条件。 |
属性详细信息
custom
自定义数据有效性条件。
custom?: Excel.CustomDataValidation;属性值
注解
示例
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation-types.yaml
function applyCustom(sheet: Excel.Worksheet) {
// Custom formula: Value in B8 must not duplicate any value already in B2:B7.
const customRule: Excel.CustomDataValidation = {
formula: "=COUNTIF($B$2:$B$7,B8)=0"
};
const rule: Excel.DataValidationRule = { custom: customRule };
sheet.getRange("B8").dataValidation.rule = rule;
}
date
日期数据有效性条件。
date?: Excel.DateTimeDataValidation;属性值
注解
示例
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation-types.yaml
function applyDate(sheet: Excel.Worksheet) {
// Date must be on or after January 1, 2000.
const basicRule: Excel.DateTimeDataValidation = {
formula1: "1/1/2000",
operator: Excel.DataValidationOperator.greaterThanOrEqualTo
};
const rule: Excel.DataValidationRule = { date: basicRule };
sheet.getRange("B5").dataValidation.rule = rule;
}
decimal
小数数据有效性条件。
decimal?: Excel.BasicDataValidation;属性值
注解
示例
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation-types.yaml
function applyDecimal(sheet: Excel.Worksheet) {
// Decimal values between 0 and 10 are allowed.
const basicRule: Excel.BasicDataValidation = {
formula1: 0,
formula2: 10,
operator: Excel.DataValidationOperator.between
};
const rule: Excel.DataValidationRule = { decimal: basicRule };
sheet.getRange("B3").dataValidation.rule = rule;
}
list
列表数据有效性条件。
list?: Excel.ListDataValidation;属性值
注解
示例
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation-types.yaml
function applyList(sheet: Excel.Worksheet) {
// Value must be chosen from the predefined list.
const listRule: Excel.ListDataValidation = {
inCellDropDown: true,
source: "Apple,Banana,Cherry"
};
const rule: Excel.DataValidationRule = { list: listRule };
sheet.getRange("B4").dataValidation.rule = rule;
}
textLength
文本长度数据验证条件。
textLength?: Excel.BasicDataValidation;属性值
注解
示例
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation-types.yaml
function applyTextLength(sheet: Excel.Worksheet) {
// Text must be 10 characters or fewer.
const basicRule: Excel.BasicDataValidation = {
formula1: 10,
operator: Excel.DataValidationOperator.lessThanOrEqualTo
};
const rule: Excel.DataValidationRule = { textLength: basicRule };
sheet.getRange("B7").dataValidation.rule = rule;
}
time
时间数据有效性条件。
time?: Excel.DateTimeDataValidation;属性值
注解
示例
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation-types.yaml
function applyTime(sheet: Excel.Worksheet) {
// Time must be between 9:00 AM and 5:00 PM.
const basicRule: Excel.DateTimeDataValidation = {
formula1: "9:00 AM",
formula2: "5:00 PM",
operator: Excel.DataValidationOperator.between
};
const rule: Excel.DataValidationRule = { time: basicRule };
sheet.getRange("B6").dataValidation.rule = rule;
}
wholeNumber
整数数据验证条件。
wholeNumber?: Excel.BasicDataValidation;属性值
注解
示例
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation-types.yaml
function applyWholeNumber(sheet: Excel.Worksheet) {
// Only integers greater than 0 are allowed.
const basicRule: Excel.BasicDataValidation = {
formula1: 0,
operator: Excel.DataValidationOperator.greaterThan
};
const rule: Excel.DataValidationRule = { wholeNumber: basicRule };
sheet.getRange("B2").dataValidation.rule = rule;
}