ExcelScript.DataValidationRule interface

包:

ExcelScript

数据验证规则包含不同类型的数据验证。 根据 ExcelScript.DataValidationType，一次只能使用其中一个。

属性

custom	自定义数据有效性条件。
date	日期数据有效性条件。
decimal	小数数据有效性条件。
list	列表数据有效性条件。
textLength	文本长度数据验证条件。
time	时间数据有效性条件。
wholeNumber	整数数据验证条件。

属性详细信息

custom

自定义数据有效性条件。

custom?: CustomDataValidation;

属性值

ExcelScript.CustomDataValidation

示例

/**
 * This script adds data validation to a range.
 * The validation prevents duplicate entries within that range.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the range "B2:B20".
  const sheet = workbook.getActiveWorksheet();
  const range = sheet.getRange("B2:B20");

  // Set data validation on the range to prevent duplicate, non-blank entries.
  const dataValidation = range.getDataValidation();
  dataValidation.setIgnoreBlanks(true);
  const duplicateRule : ExcelScript.CustomDataValidation = { 
    formula: "=COUNTIF($B$2:$B$20, B2)=1"
  };
  dataValidation.setRule({
    custom: duplicateRule
  });
}

date

日期数据有效性条件。

date?: DateTimeDataValidation;

属性值

ExcelScript.DateTimeDataValidation

decimal

小数数据有效性条件。

decimal?: BasicDataValidation;

属性值

ExcelScript.BasicDataValidation

list

列表数据有效性条件。

list?: ListDataValidation;

属性值

ExcelScript.ListDataValidation

示例

/**
 * This script creates a dropdown selection list for a cell.
 * It uses the existing values of the selected range as the choices for the list.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the values for data validation.
    const selectedRange = workbook.getSelectedRange();
    const rangeValues = selectedRange.getValues();

    // Convert the values into a comma-delimited string.
    let dataValidationListString = "";
    rangeValues.forEach((rangeValueRow) => {
        rangeValueRow.forEach((value) => {
            dataValidationListString += value + ",";
        });
    });

    // Clear the old range.
    selectedRange.clear(ExcelScript.ClearApplyTo.contents);

    // Apply the data validation to the first cell in the selected range.
    const targetCell = selectedRange.getCell(0, 0);
    const dataValidation = targetCell.getDataValidation();

    // Set the content of the dropdown list.
    let validationCriteria : ExcelScript.ListDataValidation = {
        inCellDropDown: true,
        source: dataValidationListString
    };
    let validationRule: ExcelScript.DataValidationRule = {
        list: validationCriteria
    };
    dataValidation.setRule(validationRule);
}

textLength

文本长度数据验证条件。

textLength?: BasicDataValidation;

属性值

ExcelScript.BasicDataValidation

time

时间数据有效性条件。

time?: DateTimeDataValidation;

属性值

ExcelScript.DateTimeDataValidation

wholeNumber

整数数据验证条件。

wholeNumber?: BasicDataValidation;

属性值

ExcelScript.BasicDataValidation

示例

/**
 * This script creates a data validation rule for the range B1:B5.
 * All values in that range must be a positive number.
 * Attempts to enter other values are blocked and an error message appears.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the range B1:B5 in the active worksheet.
  const currentSheet = workbook.getActiveWorksheet();
  const positiveNumberOnlyCells = currentSheet.getRange("B1:B5");

  // Create a data validation rule to only allow positive numbers.
  const positiveNumberValidation: ExcelScript.BasicDataValidation = {
    formula1: "0",
    operator: ExcelScript.DataValidationOperator.greaterThan
  };
  const positiveNumberOnlyRule: ExcelScript.DataValidationRule = {
    wholeNumber: positiveNumberValidation
  };

  // Set the rule on the range.
  const rangeDataValidation = positiveNumberOnlyCells.getDataValidation();
  rangeDataValidation.setRule(positiveNumberOnlyRule);

  // Create an alert to appear when data other than positive numbers are entered.
  const positiveNumberOnlyAlert: ExcelScript.DataValidationErrorAlert = {
    message: "Positive numbers only",
    showAlert: true,
    style: ExcelScript.DataValidationAlertStyle.stop,
    title: "Invalid data"
  };
  rangeDataValidation.setErrorAlert(positiveNumberOnlyAlert);
}