向特定 Excel 范围添加数据验证

2025-10-02

使用 Excel JavaScript API 强制实施数据质量。应用规则并依赖 Excel 的验证 UI 获取提示和错误警报。本文介绍如何定义规则类型、配置提示和错误警报，以及删除或调整验证。如果需要有关 Excel 内置验证 UI 的背景信息，请查看以下文章。

数据验证的编程控制

Range.dataValidation 属性（使用 DataValidation 对象）是在 Excel 中对数据验证进行编程控制的切入点。对象具有五个属性：

rule — 定义构成范围的有效数据的内容。请参阅 DataValidationRule。
errorAlert — 指定当用户输入无效数据时是否弹出错误，并定义警报文本、标题和样式（如 information、 warning和 stop）。请参阅 DataValidationErrorAlert。
prompt — 指定当用户将鼠标悬停在范围上并定义提示消息时是否显示提示。请参阅 DataValidationPrompt。
ignoreBlanks — 指定数据验证规则是否应用于区域中的空白单元格。默认为 true。
type — 验证类型的只读标识，例如 WholeNumber、Date、TextLength 等。它是在设置属性时间接设置的 rule 。

注意

以编程方式添加的数据验证与手动添加的数据验证的行为方式类似。特别要注意的是，只有当用户直接将值输入单元格或从工作簿的其他地方复制单元格并选择“数值”粘贴选项来进行粘贴时，才会触发数据验证。如果用户复制单元格并将纯文本粘贴到具有数据验证的范围内，则不会触发验证。

创建验证规则

若要为某个范围添加数据验证，你的代码必须在 Range.dataValidation 中设置 DataValidation 对象的 rule 属性。这会用到 DataValidationRule 对象，该对象具有七个可选属性。 任何 DataValidationRule 对象中都最多只能有一个上述属性。 其中包括的属性将决定验证的类型。

基本和日期/时间验证规则类型

前三个 DataValidationRule 属性（即验证规则类型）会取 BasicDataValidation 对象作为其值。

wholeNumber — 除了对象指定的 BasicDataValidation 任何其他验证之外，还需要一个整数。
decimal — 除了对象指定 BasicDataValidation 的任何其他验证之外，还需要一个十进制数。
textLength — 将对象中的 BasicDataValidation 验证详细信息应用于单元格值的长度。

以下示例创建验证规则。要点：

是 operator 二进制运算符 greaterThan。每当使用二进制运算符时，用户试图在单元格中输入的值是左操作数，formula1 中指定的值是右操作数。此规则指示只有大于 0 的整数有效。
formula1 是一个硬编码数字。如果在编码时不知道值应是什么，还可以使用 Excel 公式作为字符串 (，例如“=A3”或“=SUM (A4，B5) ”) 。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

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

    await context.sync();
});

有关其他二进制运算符，请参阅 BasicDataValidation 。

还有两个三元运算符： between 和 notBetween。若要使用这些属性，请指定可选 formula2 属性。 formula1 和 formula2 值为边界操作数。用户试图在单元格中输入的值是第三个（被评估）操作数。下面是使用“Between”运算符的示例。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            decimal: {
                formula1: 0,
                formula2: 100,
              operator: Excel.DataValidationOperator.between
            }
        };

    await context.sync();
});

接下来的两个规则属性均取 DateTimeDataValidation 对象作为其值。

date
time

DateTimeDataValidation 对象的结构与 BasicDataValidation 类似：具有属性 formula1、formula2 和 operator，而且使用方式相同。不同之处在于，你不能在公式属性中使用数字，但是可以输入 ISO 8606 日期/时间字符串（或 Excel 公式）。以下示例将有效值定义为 2022 年 4 月第一周的日期。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            date: {
                formula1: "2022-04-01",
                formula2: "2022-04-08",
                operator: Excel.DataValidationOperator.between
            }
        };

    await context.sync();
});

列表验证规则类型

list使用对象中的 DataValidationRule 属性将值约束为有限集。以下代码示例演示。要点：

它假定有一个名为“"Names”的工作表，且范围“A1:A3”内的值均为姓名。
source 属性指定一个有效值列表。该字符串参数会指向一个包含姓名的范围。还可以分配逗号分隔的列表，例如“Sue， Ricky， Liz”。
属性 inCellDropDown 指定当用户选择下拉控件时是否显示在单元格中。如果 true为，则下拉列表将显示，其中包含中的 source值列表。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");   
    let nameSourceRange = context.workbook.worksheets.getItem("Names").getRange("A1:A3");

    range.dataValidation.rule = {
        list: {
            inCellDropDown: true,
            source: "=Names!$A$1:$A$3"
        }
    };

    await context.sync();
})

自定义验证规则类型

custom使用属性指定自定义验证公式。示例如下。要点：

它假定工作表的 A 和 B 列中有一个包含 “运动员名称” 和“ 注释” 列的两列表。
为了减少 “注释 ”列中的详细程度，该规则会使包含运动员姓名的数据无效。
SEARCH(A2,B2) 返回 A2 中字符串 B2 的起始位置。如果 A2 未包含在 B2 中，则它不会返回数字。
ISNUMBER() 会返回布尔值。因此， formula 属性表示 “注释 ”的有效数据是不包含 “运动员姓名” 字符串的数据。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let commentsRange = sheet.tables.getItem("AthletesTable").columns.getItem("Comments").getDataBodyRange();

    commentsRange.dataValidation.rule = {
            custom: {
                formula: "=NOT(ISNUMBER(SEARCH(A2,B2)))"
            }
        };

    await context.sync();
});

创建验证错误警报

创建错误警报，以在输入无效数据时指导用户。以下示例创建一个基本警报。要点：

style 属性决定用户是会收到信息警报、警告还是“停止”警报。实际上，只有 stop 会阻止用户添加无效数据。和 information 的warning弹出窗口具有允许用户仍然输入无效数据的选项。
showAlert 属性默认为 true。这意味着，除非你创建了将 showAlertfalse 设置为或设置自定义 (消息、标题和样式的自定义警报，否则 Excel 将弹出) 类型的stop通用警报。以下代码设置了自定义消息和标题。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

    range.dataValidation.errorAlert = {
            message: "Sorry, only positive whole numbers are allowed",
            showAlert: true, // The default is 'true'.
              style: Excel.DataValidationAlertStyle.stop,
            title: "Negative or Decimal Number Entered"
        };

    // Set range.dataValidation.rule and optionally .prompt here.

    await context.sync();
});

有关详细信息，请参阅 DataValidationErrorAlert。

创建验证提示语

创建在用户选择单元格时显示的说明性提示。本示例在用户输入数据之前告知用户有关正数验证的信息。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

    range.dataValidation.prompt = {
            message: "Please enter a positive whole number.",
            showPrompt: true, // The default is 'false'.
            title: "Positive Whole Numbers Only."
        };

    // Set range.dataValidation.rule and optionally .errorAlert here.

    await context.sync();
});

有关详细信息，请参阅 DataValidationPrompt。

从某个范围删除数据验证

若要从区域中删除数据验证，请调用 Range.dataValidation.clear () 方法。

myrange.dataValidation.clear();

清除的范围不需要精确匹配添加数据验证的范围。如果两个区域不完全匹配，则仅清除重叠单元格。

注意

从某个范围清除数据验证还会清除用户手动添加至该范围的任何数据验证。