Добавление проверки данных в диапазоны ExcelAdd data validation to Excel ranges

Библиотека JavaScript Excel предоставляет API, позволяющие вашей надстройке добавлять функцию автоматической проверки данных для таблиц, столбцов, строк и других диапазонов в книге. The Excel JavaScript Library provides APIs to enable your add-in to add automatic data validation to tables, columns, rows, and other ranges in a workbook. Чтобы понять принципы и терминологию проверки данных, ознакомьтесь со следующими статьями о том, как пользователи добавляют проверку данных в пользовательском интерфейсе Excel:To understand the concepts and the terminology of data validation, please see the following articles about how users add data validation through the Excel UI:

Программное управление проверкой данныхProgrammatic control of data validation

Свойство Range.dataValidation, которое получает объект DataValidation, является точкой входа для программного управления проверкой данных в Excel.The Range.dataValidation property, which takes a DataValidation object, is the entry point for programmatic control of data validation in Excel. Существует пять свойств объекта DataValidation:There are five properties to the DataValidation object:

  • rule — определяет, какие данные для диапазона являются допустимыми.rule — Defines what constitutes valid data for the range. См. статью DataValidationRule.See DataValidationRule.
  • errorAlert — указывает, появляется ли ошибка, если пользователь вводит недопустимые данные, и определяет текст, название и стиль оповещения, например Informational (информирование), Warning (предупреждение) и Stop (остановка). errorAlert — Specifies whether an error pops up if the user enters invalid data, and defines the alert text, title, and style; for example, Informational, Warning, and Stop. См. статью DataValidationErrorAlert.See DataValidationErrorAlert.
  • prompt — указывает, появляется ли подсказка, когда пользователь наводит указатель мыши на диапазон, и определяет текст подсказки.prompt — Specifies whether a prompt appears when the user hovers over the range and defines the prompt message. См. статью DataValidationPrompt.See DataValidationPrompt.
  • ignoreBlanks — указывает, применяется ли правило проверки данных к пустым ячейкам в диапазоне.ignoreBlanks — Specifies whether the data validation rule applies to blank cells in the range. Значение по умолчанию: true.Defaults to true.
  • type — идентификация типа проверки "только для чтения", например WholeNumber, Date, TextLength и т. д. Это свойство устанавливается неявно при задании свойства rule.type — A read-only identification of the validation type, such as WholeNumber, Date, TextLength, etc. It is set indirectly when you set the rule property.

Примечание

Проверка данных, добавляемая программно, ведет себя так же, как проверка данных, добавляемая вручную. Data validation added programmatically behaves just like manually added data validation. В частности, обратите внимание на то, что проверка данных запускается только в том случае, если пользователь вводит значение в ячейку или копирует и вставляет ячейки из другого источника в книге и выбирает параметр вставки Значения.In particular, note that data validation is triggered only if the user directly enters a value into a cell or copies and pastes a cell from elsewhere in the workbook and chooses the Values paste option. Если пользователь копирует ячейку и выполняет простую вставку в диапазон проверки данных, проверка не выполняется.If the user copies a cell and does a plain paste into a range with data validation, validation is not triggered.

Создание правил проверкиCreating validation rules

Чтобы добавить проверку данных в диапазон, ваш код должен установить свойство rule объекта DataValidation в Range.dataValidation.To add data validation to a range, your code must set the rule property of the DataValidation object in Range.dataValidation. Это приводит к получению объекта DataValidationRule, который имеет семь дополнительных свойств.This takes a DataValidationRule object which has seven optional properties. Максимум одно свойство может присутствовать в любом объекте DataValidationRule.No more than one of these properties may be present in any DataValidationRule object. Указываемое свойство определяет тип выполняемой проверки.The property that you include determines the type of validation.

Типы правил проверки Basic и DateTimeBasic and DateTime validation rule types

Первые три свойства DataValidationRule (т. е. типы правил проверки) в качестве своего значения принимают объект BasicDataValidation.The first three DataValidationRule properties (i.e., validation rule types) take a BasicDataValidation object as their value.

  • wholeNumber — требует целое число в дополнение к другим проверкам, указанным объектом BasicDataValidation.wholeNumber — Requires a whole number in addition to any other validation specified by the BasicDataValidation object.
  • decimal — требует десятичное число в дополнение к другим проверкам, указанным объектом BasicDataValidation.decimal — Requires a decimal number in addition to any other validation specified by the BasicDataValidation object.
  • textLength — применяет сведения проверки объекта BasicDataValidation к длине значения ячейки.textLength — Applies the validation details in the BasicDataValidation object to the length of the cell's value.

Ниже приведен пример создания правила проверки. Here is an example of creating a validation rule. Обратите внимание на следующие особенности этого кода:Note the following about this code:

  • operator — это бинарный оператор "GreaterThan".The operator is the binary operator "GreaterThan". При использовании бинарного оператора значение, которое пользователь пытается ввести в ячейку, — это левый операнд, а значение, указанное в formula1, — это правый операнд.Whenever you use a binary operator, the value that the user tries to enter in the cell is the left-hand operand and the value specified in formula1 is the right-hand operand. Поэтому согласно этому правилу только целые числа больше 0 являются допустимыми.So this rule says that only whole numbers that are greater than 0 are valid.
  • formula1 — это жестко заданное число.The formula1 is a hard-coded number. Если во время кодирования вы не знаете, какое значение должно быть задано, можно также использовать формулу Excel (в виде строки) для значения.If you don't know at coding time what the value should be, you can also use an Excel formula (as a string) for the value. Например, "= A3" и "= SUM(A4,B5)" могут также быть значениями formula1.For example, "=A3" and "=SUM(A4,B5)" could also be values of formula1.
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            wholeNumber: {
                formula1: 0,
                operator: "GreaterThan"
            }
        };

    return context.sync();
})

Перечень других бинарных операторов см. в статье BasicDataValidation.See BasicDataValidation for a list of the other binary operators.

Существует также два тернарных оператора: "Between" и "NotBetween".There are also two ternary operators: "Between" and "NotBetween". Для их использования необходимо указать необязательное свойство formula2. To use these, you must specify the optional formula2 property. Значения formula1 и formula2 — это ограничивающие операнды.The formula1 and formula2 values are the bounding operands. Значение, которое пользователь пытается ввести в ячейку, — это третий (вычисленный) операнд.The value that the user tries to enter in the cell is the third (evaluated) operand. Ниже приведены примеры использования оператора "Between":The following is an example of using the "Between" operator:

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            decimal: {
                formula1: 0,
                formula2: 100,
                operator: "Between"
            }
        };

    return context.sync();
})

Следующие два свойства правила в качестве своего значения принимают объект DateTimeDataValidation.The next two rule properties take a DateTimeDataValidation object as their value.

  • date
  • time

Объект DateTimeDataValidation структурирован так же, как и BasicDataValidation: он имеет свойства formula1, formula2 и operator и используется аналогичным образом.The DateTimeDataValidation object is structured similarly to the BasicDataValidation: it has the properties formula1, formula2, and operator, and is used in the same way. Различие состоит в том, что в свойствах формулы нельзя использовать число, но можно ввести строку даты и времени ISO 8606 (или формулу Excel).The difference is that you cannot use a number in the formula properties, but you can enter a ISO 8606 datetime string (or an Excel formula). Ниже приведен пример, в котором определяются допустимые значения для дат в первую неделю апреля 2018 года.The following is an example that defines valid values as dates in the first week of April, 2018.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            date: {
                formula1: "2018-04-01",
                formula2: "2018-04-08",
                operator: "Between"
            }
        };

    return context.sync();
})

Тип правила проверки для спискаList validation rule type

Используйте свойство list в объекте DataValidationRule, чтобы указать, что единственными допустимыми значениями являются значения из конечного списка.Use the list property in the DataValidationRule object to specify that the only valid values are those from a finite list. Ниже приведен пример.The following is an example. Обратите внимание на следующие особенности этого кода:Note the following about this code:

  • Предполагается, что существует лист с именем "Имена", а значения в диапазоне "A1:A3" являются именами.It assumes that there is a worksheet named "Names" and that the values in the range "A1:A3" are names.
  • Свойство source определяет список допустимых значений.The source property specifies the list of valid values. Строковый аргумент ссылается на диапазон с именами.The string argument refers to a range containing the names. Можно также назначить разделенный запятыми список, например "Регина, Сергей, Анна".You can also assign a comma-delimited list; for example: "Sue, Ricky, Liz".
  • Свойство inCellDropDown указывает, будет ли раскрывающийся элемент управления отображаться в ячейке, когда пользователь выбирает ее.The inCellDropDown property specifies whether a drop-down control will appear in the cell when the user selects it. Если свойству присвоено значение true, то раскрывающийся список отображается со списком значений из source.If set to true, then the drop-down appears with the list of values from the source.
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");   
    var nameSourceRange = context.workbook.worksheets.getItem("Names").getRange("A1:A3");

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

    return context.sync();
})

Настраиваемый тип правила проверкиCustom validation rule type

Используйте свойство custom в объекте DataValidationRule, чтобы задать настраиваемую формулу проверки.Use the custom property in the DataValidationRule object to specify a custom validation formula. Ниже приведен пример.The following is an example. Обратите внимание на следующие особенности этого кода:Note the following about this code:

  • Предполагается, что на листе расположена таблица с двумя столбцами A и B: Имя спортсмена и Комментарии.It assumes there is a two-column table with columns Athlete Name and Comments in the A and B columns of the worksheet.
  • Чтобы исключить многословие в столбце Комментарии, данные, содержащие имя спортсмена, определяются недопустимыми.To reduce verbosity in the Comments column, it makes data that includes the athlete's name invalid.
  • SEARCH(A2,B2) возвращает стартовую позицию строки в ячейке A2 в строку в ячейке B2.SEARCH(A2,B2) returns the starting position, in string in B2, of the string in A2. Если A2 не находится в ячейке B2, не возвращается числовое значение.If A2 is not contained in B2, it does not return a number. ISNUMBER() возвращает логическое значение.ISNUMBER() returns a boolean. Поэтому свойство formula указывает, что допустимые данные для столбца Комментарии — это данные, которые не содержат строку в столбце Имя спортсмена.So the formula property says that valid data for the Comment column is data that does not include the string in the Athlete Name column.
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");
    var commentsRange = sheet.tables.getItem("AthletesTable").columns.getItem("Comments").getDataBodyRange();

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

    return context.sync();
})

Создание оповещений об ошибках проверкиCreate validation error alerts

Вы можете создать настраиваемое оповещение об ошибке, которое отображается, если пользователь пытается ввести недопустимые данные в ячейке.You can a create custom error alert that appears when a user tries to enter invalid data in a cell. Ниже приведен простой пример.The following is a simple example. Обратите внимание на следующие особенности этого кода:Note the following about this code:

  • Свойство style определяет, получает ли пользователь информационное уведомление, предупреждение или оповещение "stop".The style property determines whether the user gets an informational alert, a warning, or a "stop" alert. Только Stop действительно не позволяет пользователю добавлять недопустимые данные. Only Stop actually prevents the user from adding invalid data. Всплывающее окно для Warning и Information содержит параметры, позволяющие пользователю в любом случае ввести недопустимые данные.The pop-up for Warning and Information has options that allow the user enter the invalid data anyway.
  • Свойству showAlert по умолчанию присвоено значение true. The showAlert property defaults to true. Это означает, что Excel выводит сообщение об общем поступающем (тип Stop ), если вы не создаете настраиваемое оповещение, которое задает showAlert false или задает настраиваемое сообщение, заголовок и стиль.This means that Excel will pop-up a generic alert (of type Stop) unless you create a custom alert which either sets showAlert to false or sets a custom message, title, and style. Этот код задает настраиваемое сообщение и заголовок.This code sets a custom message and title.
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

    range.dataValidation.errorAlert = {
            message: "Sorry, only positive whole numbers are allowed",
            showAlert: true, // default is 'true'
            style: "Stop", // other possible values: Warning, Information
            title: "Negative or Decimal Number Entered"
        };

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

    return context.sync();
})

Дополнительные сведения см. в статье DataValidationErrorAlert.For more information, see DataValidationErrorAlert.

Создание запросов проверкиCreate validation prompts

Вы можете создать пояснительную подсказку, которая появляется, когда пользователь наводит указатель мыши на ячейку, к которой была применена проверка данных, или выбирает ее.You can create an instructional prompt that appears when a user hovers over, or selects, a cell to which data validation has been applied. Ниже приведен пример.The following is an example:

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

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

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

    return context.sync();
})

Дополнительные сведения см. в статье DataValidationPrompt.For more information, see DataValidationPrompt.

Удаление проверки данных из диапазонаRemove data validation from a range

Чтобы удалить проверку данных из диапазона, вызовите метод Range.dataValidation.clear().To remove data validation from a range, call the Range.dataValidation.clear() method.

myrange.dataValidation.clear()

Необязательно, чтобы очищаемый диапазон был тем же диапазоном, к которому вы применили проверку данных.It isn't necessary that the range you clear is exactly the same range as a range on which you added data validation. Если это не один и тот же диапазон, удаляются только перекрывающиеся ячейки двух диапазонов (при их наличии).If it isn't, only the overlapping cells, if any, of the two ranges are cleared.

Примечание

Удаление проверки данных из диапазона также распространяется на любую проверку данных, которую пользователь добавил вручную в диапазон.Clearing data validation from a range will also clear any data validation that a user has added manually to the range.

См. такжеSee also