Excel.DataValidationRule interface

数据有效性规则包含不同类型的数据验证。Data validation rule contains different types of data validation. 一次只能根据 DataValidationType 使用其中一种方法。You can only use one of them at a time according the Excel.DataValidationType.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

属性

custom

自定义数据有效性条件。Custom data validation criteria.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

date

日期数据有效性条件。Date data validation criteria.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

decimal

小数数据有效性条件。Decimal data validation criteria.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

list

列表数据有效性条件。List data validation criteria.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

textLength

TextLength 数据有效性条件。TextLength data validation criteria.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

time

时间数据有效性条件。Time data validation criteria.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

wholeNumber

WholeNumber 数据有效性条件。WholeNumber data validation criteria.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

属性详细信息

custom

自定义数据有效性条件。Custom data validation criteria.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

custom?: Excel.CustomDataValidation;

属性值

date

日期数据有效性条件。Date data validation criteria.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

date?: Excel.DateTimeDataValidation;

属性值

decimal

小数数据有效性条件。Decimal data validation criteria.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

decimal?: Excel.BasicDataValidation;

属性值

list

列表数据有效性条件。List data validation criteria.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

list?: Excel.ListDataValidation;

属性值

示例Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Decision");
    const nameRange = 
        sheet.tables.getItem("NameOptionsTable").columns.getItem("Baby Name").getDataBodyRange();

    // When you are developing, it is a good practice to
    // clear the dataValidation object with each run of your code.
    nameRange.dataValidation.clear();

    const nameSourceRange = context.workbook.worksheets.getItem("Names").getRange("A1:A3");

    let approvedListRule = {
        list: {
            inCellDropDown: true,
            source: nameSourceRange
        }
    };
    nameRange.dataValidation.rule = approvedListRule;

    await context.sync();
});

textLength

TextLength 数据有效性条件。TextLength data validation criteria.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

textLength?: Excel.BasicDataValidation;

属性值

time

时间数据有效性条件。Time data validation criteria.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

time?: Excel.DateTimeDataValidation;

属性值

wholeNumber

WholeNumber 数据有效性条件。WholeNumber data validation criteria.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

wholeNumber?: Excel.BasicDataValidation;

属性值

示例Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Decision");
    const rankingRange = sheet.tables.getItem("NameOptionsTable").columns.getItem("Ranking").getDataBodyRange();

    // When you are developing, it is a good practice to
    // clear the dataValidation object with each run of your code.
    rankingRange.dataValidation.clear();

    let greaterThanZeroRule = {
        wholeNumber: {
            formula1: 0,
            operator: Excel.DataValidationOperator.greaterThan
        }
    };
    rankingRange.dataValidation.rule = greaterThanZeroRule;

    rankingRange.dataValidation.prompt = {
        message: "Please enter a positive number.",
        showPrompt: true,
        title: "Positive numbers only."
    };

    rankingRange.dataValidation.errorAlert = {
        message: "Sorry, only positive numbers are allowed",
        showAlert: true,
        style: "Stop",
        title: "Negative Number Entered"
    };

    await context.sync();
});