Добавление проверки данных в диапазоны Excel

Статья
04/07/2023

Библиотека JavaScript Excel предоставляет API, позволяющие вашей надстройке добавлять функцию автоматической проверки данных для таблиц, столбцов, строк и других диапазонов в книге. Чтобы понять основные понятия и терминологию проверки данных, ознакомьтесь со следующими статьями о том, как пользователи добавляют проверку данных с помощью пользовательского интерфейса Excel.

Программное управление проверкой данных

Свойство Range.dataValidation, которое получает объект DataValidation, является точкой входа для программного управления проверкой данных в Excel. Существует пять свойств объекта DataValidation:

rule — определяет, что представляет собой допустимые данные для диапазона. См. статью DataValidationRule.
errorAlert — указывает, возникает ли ошибка, если пользователь вводит недопустимые данные, и определяет текст оповещения, заголовок и стиль; например, information, warningи stop. См. статью DataValidationErrorAlert.
prompt — указывает, отображается ли запрос при наведении указателя мыши на диапазон и определении сообщения запроса. См. статью DataValidationPrompt.
ignoreBlanks — указывает, применяется ли правило проверки данных к пустым ячейкам в диапазоне. Значение по умолчанию — true.
type — идентификация типа проверки только для чтения, например WholeNumber, Date, TextLength и т. д. Он задается косвенно при установке rule свойства .

Примечание.

Проверка данных, добавляемая программно, ведет себя так же, как проверка данных, добавляемая вручную. В частности, обратите внимание на то, что проверка данных запускается только в том случае, если пользователь вводит значение в ячейку или копирует и вставляет ячейки из другого источника в книге и выбирает параметр вставки Значения. Если пользователь копирует ячейку и выполняет простую вставку в диапазон проверки данных, проверка не выполняется.

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

Чтобы добавить проверку данных в диапазон, ваш код должен установить свойство rule объекта DataValidation в Range.dataValidation. Это приводит к получению объекта DataValidationRule, который имеет семь дополнительных свойств. Максимум одно свойство может присутствовать в любом объекте DataValidationRule. Указываемое свойство определяет тип выполняемой проверки.

Типы правил проверки Basic и DateTime

Первые три свойства DataValidationRule (т. е. типы правил проверки) в качестве своего значения принимают объект BasicDataValidation.

wholeNumber — требуется целое число в дополнение к любой другой проверке, указанной BasicDataValidation объектом .
decimal — требуется десятичное число в дополнение к любой другой проверке, заданной BasicDataValidation объектом .
textLength — применяет сведения о проверке в объекте BasicDataValidation к длине значения ячейки.

Ниже приведен пример создания правила проверки. Обратите внимание на указанные ниже аспекты этого кода.

— operator это двоичный оператор greaterThan. При использовании бинарного оператора значение, которое пользователь пытается ввести в ячейку, — это левый операнд, а значение, указанное в formula1, — это правый операнд. Поэтому согласно этому правилу только целые числа больше 0 являются допустимыми.
formula1 — это жестко заданное число. Если во время кодирования вы не знаете, какое значение должно быть задано, можно также использовать формулу Excel (в виде строки) для значения. Например, "= A3" и "= SUM(A4,B5)" могут также быть значениями formula1.

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 г.

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, чтобы указать, что единственными допустимыми значениями являются значения из конечного списка. Ниже приведен пример. Обратите внимание на указанные ниже аспекты этого кода.

Предполагается, что существует лист с именем "Имена", а значения в диапазоне "A1:A3" являются именами.
Свойство source определяет список допустимых значений. Строковый аргумент ссылается на диапазон с именами. Можно также назначить разделенный запятыми список, например "Регина, Сергей, Анна".
Свойство 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 в объекте DataValidationRule, чтобы задать настраиваемую формулу проверки. Ниже приведен пример. Обратите внимание на указанные ниже аспекты этого кода.

Предполагается, что на листе расположена таблица с двумя столбцами 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". Только stop действительно не позволяет пользователю добавлять недопустимые данные. Всплывающие окна для warning и information имеют параметры, которые позволяют пользователю вводить недопустимые данные в любом случае.
Свойству showAlert по умолчанию присвоено значение true. Это означает, что Excel будет отображать универсальное оповещение (типа stop), если вы не создадите пользовательское оповещение, которое задает или задает showAlertfalse пользовательское сообщение, заголовок и стиль. Этот код задает настраиваемое сообщение и заголовок.

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()

Необязательно, чтобы очищаемый диапазон был тем же диапазоном, к которому вы применили проверку данных. Если это не один и тот же диапазон, удаляются только перекрывающиеся ячейки двух диапазонов (при их наличии).