Модуль 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")
dv = DataValidation(type="whole", operator="greaterThan", formula1=100)
dv = DataValidation(type="decimal")
dv = DataValidation(type="decimal", operator="between", formula1=0, formula2=1)
# проверяем дату dv = DataValidation(type="date") # проверяем время dv = DataValidation(type="time")
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
является относительной ссылкой - она должна меняться в каждой ячейке.
В этой формуле:
COUNTIF
предотвращает дублирование чисел.ISNUMBER
предотвращает ввод текста.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.