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

Используйте API JavaScript для Excel для обеспечения качества данных. Применяйте правила и используйте пользовательский интерфейс проверки Excel для запросов и оповещений об ошибках. В этой статье показано, как определять типы правил, настраивать запросы и оповещения об ошибках, а также удалять или настраивать проверку. Если вам нужен фон для встроенного пользовательского интерфейса проверки Excel, ознакомьтесь с этими статьями.

• Применение проверки данных к ячейкам
• Подробнее о проверке данных
• Описание и примеры проверки данных в Excel

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

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

• 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)").

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 определяет список допустимых значений. Строковый аргумент ссылается на диапазон с именами. Можно также назначить список с разделителями-запятыми, например "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) возвращает начальную позицию в B2 строки в A2. Если 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();

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

Примечание.

Удаление проверки данных из диапазона также распространяется на любую проверку данных, которую пользователь добавил вручную в диапазон.

Дальнейшие действия

• Объединение проверки с событиями: События.
• Добавьте условное форматирование для более строгих визуальных подсказок.

См. также

• Объектная модель JavaScript для Excel в надстройках Office
• Объект DataValidation (API JavaScript для Excel)
• Объект Range (API JavaScript для Excel)