数据验证：下拉列表、提示和警告弹出窗口

数据验证可帮助用户确保工作表中的一致性。 使用这些功能可以限制可在单元格中输入的内容，并在不符合这些条件时向用户提供警告或错误。 若要详细了解 Excel 中的数据验证，请参阅 向单元格应用数据验证。

使用数据验证创建下拉列表

以下示例为单元格创建下拉列表。 它使用所选区域的现有值作为列表的选项。

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.
  dataValidation.setRule({
      list: {
        inCellDropDown: true,
        source: dataValidationListString
      }
    });
}

向区域添加提示

此示例创建一个提示注释，当用户输入给定单元格时显示该注释。 这用于提醒用户有关输入要求的信息，而无需严格执行。

/**
 * This script creates a text prompt that's shown in C2:C8 when a user enters the cell.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the data validation object for C2:C8 in the current worksheet.
    const selectedSheet = workbook.getActiveWorksheet();
    const dataValidation = selectedSheet.getRange("C2:C8").getDataValidation();

    // Clear any previous validation to avoid conflicts.
    dataValidation.clear();

    // Create a prompt to remind users to only enter first names in this column.
    const prompt: ExcelScript.DataValidationPrompt = {
      showPrompt: true,
      title: "First names only",
      message: "Only enter the first name of the employee, not the full name."
    }
    dataValidation.setPrompt(prompt);
}

输入无效数据时向用户发出警报

以下示例脚本阻止用户将除正数以外的任何内容输入到某个范围中。 如果他们尝试放置任何其他内容，将弹出错误消息并指示问题。

/**
 * This script creates a data validation rule for the range B2: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 B2:B5 in the active worksheet.
    const currentSheet = workbook.getActiveWorksheet();
    const positiveNumberOnlyCells = currentSheet.getRange("B2: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);
}