Сообщить об ошибке.

Средства проверки данных при заполнении в ячеек

Модуль openpyxl поддерживает установку средства проверки данных при заполнении пользователем ячеек электронной таблицы. Эти средства проверки данных могут применяться к диапазонам ячеек. Диапазоны обязательно должны быть непрерывными: например. 'B2:B5' содержит ячейки от 'B2' до 'B5'.

Примечание. Проверка принудительно не применяются, а синтаксис формулы проверки не оценивается.

Сразу смотрим пример, что бы лучше понимать о чем идет речь:

>>> from openpyxl import Workbook
>>> from openpyxl.worksheet.datavalidation import DataValidation
# Создаем книгу и рабочий лист
>>> wb = Workbook()
>>> ws = wb.active
# создаем объект, который будет проверять вводимые данные из списка
>>> lst_valid = "Кот,Собака,Лошадь"
>>> dv = DataValidation(type="list", formula1=lst_valid, allow_blank=True)
# установим сообщение об ошибке
>>> dv.error ='Такой записи нет в списке'
>>> dv.errorTitle = 'Недопустимое значение.'
# установим подсказку пользователю
>>> dv.prompt = 'Пожалуйста, выберите  из списка'
>>> dv.promptTitle = 'Выбор из списка.'
# добавим объект проверки данных на лист
>>> ws.add_data_validation(dv)
# добавим одну ячейку в объект проверки данных
>>> dv.add('D2')
# теперь применим проверку к диапазону ячеек
# например, для всей колонки `B`
>>> dv.add('B1:B1048576')
# проверка нахождения ячейки в валидаторе
>>> 'B4' in dv
# True

# сохраняем и смотрим что получилось
>>> wb.save('validation.xlsx')

Теперь откроем сохраненный файл validation.xlsx в Excel. При переходе в любую ячейку столбца B или в ячейку D2, электронные таблицы предложат выбрать значение из выпадающего списка. Если в эти ячейки ввести данные, отличающиеся от предложенных в списке, то программа выдаст соответствующее предупреждение, а ячейка останется не заполненной.

Как составлять правила проверки ячеек?

Общий принцип составления правила проверки:

dv = DataValidation(type, operator, formula1, formula2)
  • type: обязательный аргумент, значение должно быть одним из: ‘date’, ‘list’, ‘decimal’, ‘custom’, ‘time’, ‘textLength’, ‘whole’.
  • operator: необязательный аргумент, значение должно быть одним из: ‘lessThanOrEqual’, ‘notBetween’, ‘lessThan’, ‘equal’, ‘greaterThan’, ‘between’, ‘notEqual’, ‘greaterThanOrEqual’.
  • formula1 и formula2 - необязательный аргументы, представляют собой значения или Excel формулы, используемые для проведения проверок.

Примеры популярных правил проверок ячеек.

Проверка на целое число:

dv = DataValidation(type="whole")

Проверка на любое целое число больше 100:

dv = DataValidation(type="whole",
                    operator="greaterThan",
                    formula1=100)

Проверка на десятичное число:

dv = DataValidation(type="decimal")

Проверка на любое десятичное число от 0 до 1:

dv = DataValidation(type="decimal",
                    operator="between",
                    formula1=0,
                    formula2=1)

Проверка на корректную дату или время:

# проверяем дату
dv = DataValidation(type="date")
# проверяем время
dv = DataValidation(type="time")

Проверка на строку, длиной не более 15 символов:

dv = DataValidation(type="textLength",
                    operator="lessThanOrEqual"),
                    formula1=15)

Проверка диапазона ячеек:

from openpyxl.utils import quote_sheetname
dv = DataValidation(type="list",
                    formula1=f"{quote_sheetname(sheetname)}!$B$1:$B$10")

Общий вид пользовательского правила проверки:

dv = DataValidation(type="custom",
                    formula1"=SOMEFORMULA")

Предотвращение дублирования в диапазоне ячеек B3:B10:

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

dv = DataValidation(type="custom",
                    formula1"=COUNTIF($B$3:$B$10,B3) <= 1")

В этой формуле функция Excel COUNTIF предотвращает дублирование.

Примечание. Диапазон вводится как абсолютная ссылка: $B$3:$B$10, поэтому правило остается одинаковым во всех ячейках. Ячейка со значением B3 является относительной ссылкой - она должна меняться в каждой ячейке.

Проверка на уникальные 5-значные числа:

В этой формуле:

  • Функция Excel COUNTIF предотвращает дублирование чисел.
  • Функция Excel ISNUMBER предотвращает ввод текста.
  • Функция Excel LEN проверяет количество цифр.
dv = DataValidation(type="custom",
                    formula1"=AND(ISNUMBER(A2),LEN(A2)=5,COUNTIF(EmpIDs,A2)<=1)")

Предотвращение дубликатов с текстовыми идентификаторами.

В этом примере в столбец 'B' вводятся уникальные названия продуктов. Столбец имеет текстовый формат, а названия продуктов представляют собой текстовыми идентификаторы.

dv = DataValidation(type="custom",
                    formula1"=SUMPRODUCT(- -($B$4:$B$22=B4)) <=1")

В этой формуле функция Excel SUMPRODUCT предотвращает дублирование текста.

Более подробно о составлении формул для проведения проверок ячеек читайте в документации к электронным таблицам EXCEL.