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

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

在Excel中，数据验证功能可以帮助用户确保工作表中数据的一致性和准确性。本文将通过三个具体场景，介绍如何使用数据验证功能：创建下拉列表、添加提示注释以及设置输入错误时的警告。

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

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

function main(workbook: ExcelScript.Workbook) {
  // 获取数据验证所需的值
  const selectedRange = workbook.getSelectedRange();
  const rangeValues = selectedRange.getValues();
  // 将值转换为逗号分隔的字符串
  let dataValidationListString = "";
  rangeValues.forEach((rangeValueRow) => {
    rangeValueRow.forEach((value) => {
      dataValidationListString += value + ",";
    });
  });
  // 清除旧的范围
  selectedRange.clear(ExcelScript.ClearApplyTo.contents);
  // 将数据验证应用于所选范围的第一个单元格
  const targetCell = selectedRange.getCell(0,0);
  const dataValidation = targetCell.getDataValidation();
  // 设置下拉列表的内容
  dataValidation.setRule({
      list: {
        inCellDropDown: true,
        source: dataValidationListString
      }
    });
}

向区域添加提示

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

/**
 * 该脚本在C2:C8单元格区域创建一个文本提示，当用户进入单元格时显示该提示。
 */
function main(workbook: ExcelScript.Workbook) {
    // 获取当前工作表中的C2:C8范围的数据验证对象
    const selectedSheet = workbook.getActiveWorksheet();
    const dataValidation = selectedSheet.getRange("C2:C8").getDataValidation();
    // 清除任何之前的验证以避免冲突
    dataValidation.clear();
    // 创建一个提示，提醒用户只能在此列中输入名字
    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);
}

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

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

/**
 * 该脚本为B2:B5范围创建数据验证规则。
 * 该范围内的所有值必须是正数。
 * 尝试输入其他值将被阻止，并显示错误消息。
 */
function main(workbook: ExcelScript.Workbook) {
    // 获取活动工作表中的B2:B5范围
    const currentSheet = workbook.getActiveWorksheet();
    const positiveNumberOnlyCells = currentSheet.getRange("B2:B5");
    // 创建一个仅允许正数的数据验证规则
    const positiveNumberValidation: ExcelScript.BasicDataValidation = {
        formula1: "0",
        operator: ExcelScript.DataValidationOperator.greaterThan
    };
    const positiveNumberOnlyRule: ExcelScript.DataValidationRule = {
      wholeNumber: positiveNumberValidation
    };
    // 在范围内设置规则
    const rangeDataValidation = positiveNumberOnlyCells.getDataValidation();
    rangeDataValidation.setRule(positiveNumberOnlyRule);
    // 创建一个警报，当输入非正数数据时显示
    const positiveNumberOnlyAlert: ExcelScript.DataValidationErrorAlert = {
        message: "Positive numbers only.",
        showAlert: true,
        style: ExcelScript.DataValidationAlertStyle.stop,
        title: "Invalid data"
    };
    rangeDataValidation.setErrorAlert(positiveNumberOnlyAlert);
}

本文原文来自Microsoft官方文档