Выпадающие списки в Excel через openpyxl: DataValidation

Журнал продаж кофейни «Дукат» заполняют четыре баристы, и каждый пишет по-своему: «капучино», «Капучино», «капучино с двумя о». В конце недели ты делаешь сводку — и вместо трёх напитков получаешь девять. Лечится это проверкой данных: ячейка становится выпадающим списком, и ввести в неё что-то постороннее Excel просто не даст. Сейчас добавим такое правило кодом.

Отдельно честно: openpyxl правило записывает, но не исполняет. Файл — это архив с XML, и внутри него появляется описание валидации. Следит за ним Excel в тот момент, когда человек вводит значение. Это не баг, а разделение труда: библиотека готовит файл, программа — guard-ит ввод. Ты уже видел такой же раздел в уроке про формулы: записываем строку, считает Excel.

Если ты когда-нибудь настраивал это руками, то знаешь путь: «Данные → Проверка данных», тип «Список», источник. Ровно то же самое мы сейчас сделаем кодом, только файл с правилом соберётся сам — и таких файлов можно собрать сколько угодно за один запуск. Ручной путь остаётся полезным как карта: каждое поле диалога Excel имеет зеркало в параметре DataValidation.

DataValidation: выпадающий список из строк

Класс DataValidation живёт в модуле openpyxl.worksheet.datavalidation. Три действия: создать правило, привязать его к листу и назначить диапазон ячеек. В formula1 для списка варианты перечисляются через запятую — и вся эта строка оборачивается в кавычки, которые Excel считает частью значения.

import os
from openpyxl import Workbook, load_workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
ws.title = "Журнал"
ws["A1"] = "Бариста"
ws["B1"] = "Напиток"

dv = DataValidation(type="list", formula1='"Американо,Капучино,Латте"', allow_blank=True)
dv.errorTitle = "Нет такого напитка"
dv.error = "Выбери напиток из списка меню"
ws.add_data_validation(dv)   # правило прикреплено к листу
dv.add("B2:B20")             # диапазон, где работает правило
wb.save("dukat_dv.xlsx")

wb2 = load_workbook("dukat_dv.xlsx")
d = wb2["Журнал"].data_validations.dataValidation[0]
print("type:", d.type)
print("formula1:", d.formula1)
print("диапазон:", str(d.sqref))
print("allow_blank:", d.allow_blank)
print("errorTitle:", d.errorTitle)
wb2.close()
os.remove("dukat_dv.xlsx")

type: list
formula1: "Американо,Капучино,Латте"
диапазон: B2:B20
allow_blank: True
errorTitle: Нет такого напитка

Разбираем поля. type="list" — это выпадающий список; позже возьмём type="whole" для целых чисел. formula1 — сам список вариантов. allow_blank=True разрешает оставить ячейку пустой — иначе незаполненный журнал превратится в сплошные жалобы на ошибку. sqref — диапазон, к которому привязано правило; правило можно добавить и нескольким диапазонам: dv.add("B2:B20"), затем dv.add("D2:D20"). Читается всё это обратно через ws.data_validations.dataValidation — список всех правил листа.

Единственный параметр, который мы сознательно не трогаем, — showDropDown. В спецификации xlsx его смысл вывернут наизнанку: True прячет выпадающий список из ячейки, оставляя только проверку ввода. Поэтому в девяноста девяти случаях из ста этот параметр просто не указывают — список выпадает как задумано.

Почему сообщение об ошибке не показывается?

Ты написал dv.error и dv.errorTitle — а пользователь вводит ерунду, и ничего не происходит. Причина в переключателе showErrorMessage: по умолчанию он выключен, и Excel хранит текст ошибки, но молчит. Это же касается подсказки при выборе ячейки — она живёт в prompt и promptTitle и включается флагом showInputMessage.

import os
from openpyxl import Workbook, load_workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
ws.title = "Журнал"
dv = DataValidation(
    type="list",
    formula1='"Американо,Капучино,Латте"',
    allow_blank=True,
    showErrorMessage=True,   # показать ошибку при плохом вводе
    showInputMessage=True,   # показать подсказку при выборе ячейки
)
dv.errorTitle = "Нет такого напитка"
dv.error = "Выбери напиток из списка меню"
dv.promptTitle = "Напиток"
dv.prompt = "Выбери из выпадающего списка"
ws.add_data_validation(dv)
dv.add("B2:B20")
wb.save("dukat_dv2.xlsx")

wb2 = load_workbook("dukat_dv2.xlsx")
d = wb2["Журнал"].data_validations.dataValidation[0]
print("showErrorMessage:", d.showErrorMessage)
print("showInputMessage:", d.showInputMessage)
print("prompt:", d.prompt)
wb2.close()
os.remove("dukat_dv2.xlsx")

showErrorMessage: True
showInputMessage: True
prompt: Выбери из выпадающего списка

Список из диапазона другого листа

Перечислять напитки прямо в формуле удобно, пока меню не поменяется: сменил цену и состав — и ползуй по всем правилам файла. Правильнее держать меню на отдельном листе и указывать правило ссылку на диапазон: =Меню!$A$1:$A$5. Поменял меню — список обновился сам, без единой строчки кода.

import os
from openpyxl import Workbook, load_workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
menu = wb.active
menu.title = "Меню"
for i, drink in enumerate(["Американо", "Капучино", "Латте", "Раф", "Какао"], start=1):
    menu.cell(row=i, column=1, value=drink)

ws = wb.create_sheet("Журнал")
dv = DataValidation(type="list", formula1="=Меню!$A$1:$A$5", allow_blank=True)
ws.add_data_validation(dv)
dv.add("B2:B20")
wb.save("dukat_dv3.xlsx")

wb2 = load_workbook("dukat_dv3.xlsx")
d = wb2["Журнал"].data_validations.dataValidation[0]
print("formula1:", d.formula1)
print("диапазон:", str(d.sqref))
wb2.close()
os.remove("dukat_dv3.xlsx")

formula1: =Меню!$A$1:$A$5
диапазон: B2:B20

Два штриха к ссылке. Знак равенства в начале обязателен — так правило отличает ссылку от перечня строк. Доллары в $A$1:$A$5 фиксируют адрес: правило не «съедет», когда на листе что-то добавят или отсортируют. У строкового списка есть ещё жёсткий лимит — вся строка formula1 не длиннее 255 символов; для длинных справочников (список клиентов, SKU) диапазон — единственный выход.

openpyxl проверяет введённые значения?

Проверим границу ответственности напрямую: запишем в проверяемую ячейку напиток, которого нет в списке, и посмотрим, что будет.

import os
from openpyxl import Workbook, load_workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
dv = DataValidation(type="list", formula1='"Американо,Капучино,Латте"', allow_blank=True)
ws.add_data_validation(dv)
dv.add("B2:B20")

ws["B2"] = "Латте макиято"   # в списке такого нет
wb.save("dukat_check.xlsx")

wb2 = load_workbook("dukat_check.xlsx")
print("B2:", wb2.active["B2"].value)
wb2.close()
os.remove("dukat_check.xlsx")

B2: Латте макиято

Ни ошибки, ни предупреждения — значение лежит в файле как ни в чём не бывало. Проверка данных — это не про запись, а про ввод: openpyxl кладёт правило в файл, а следит за ним Excel в тот момент, когда человек печатает. Это же значит, что «испорченные» старые файлы правило само не вычистит: Excel помечает невалидные значения только по команде «Обвести неверные данные» на ленте.

С уже испорченными журналами поступают так: читают колонку через iter_rows, приводят варианты к канону словарём нормализации («капучино» → «Капучино», «каппучино» → «Капучино») и перезаписывают файл — а уже затем закрывают колонку списком. Сначала чистка, потом замок: правило, поставленное поверх мусора, останется декорацией.

Как ограничить не список, а число?

Выпадающим списком дело не ограничивается: в журнал «Дуката» бариста вписывают количество проданных стаканов, и туда умудрялись записать «-5» и «два». Правило type="whole" с оператором between разрешает только целые числа в границах.

import os
from openpyxl import Workbook, load_workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
ws.title = "Журнал"
dv = DataValidation(
    type="whole",
    operator="between",
    formula1="0",
    formula2="500",
    allow_blank=True,
    showErrorMessage=True,
    errorTitle="Проверь количество",
    error="Количество — целое число от 0 до 500",
)
ws.add_data_validation(dv)
dv.add("C2:C50")
wb.save("dukat_whole.xlsx")

wb2 = load_workbook("dukat_whole.xlsx")
d = wb2["Журнал"].data_validations.dataValidation[0]
print("type:", d.type, "| operator:", d.operator)
print("границы:", d.formula1, "...", d.formula2)
print("диапазон:", str(d.sqref))
wb2.close()
os.remove("dukat_whole.xlsx")

type: whole | operator: between
границы: 0 ... 500
диапазон: C2:C50

Тот же каркас работает для дат (type="date" — не дай вписать смену 32-м числом) и длины текста (type="textLength" — артикул ровно из восьми символов). Меняются только type, operator и границы — механика привязки к диапазону одна и та же.

Какие колонки вообще стоит закрывать списком?

Проверка данных — не украшение, а способ зафиксировать договор о том, что именно заходит в таблицу. Практика показывает: чем раньше колонка закрыта, тем меньше боли в сводках. Типовые кандидаты в любом бизнесе:

• статусы: «принят», «в работе», «готов», «отменён» — без списка рано или поздно появится «готово (не отдано)»;
• справочники: товары, точки, исполнители — держи их на отдельном листе и ссылайся диапазоном;
• колонки для чужих людей: журнал баристы, опрос клиентов, заявки от коллег — люди пишут как удобнее им;
• числа с границами: количество, оценка, процент — whole и decimal с operator вместо списка;
• даты: type="date" отсечёт «31.02» ещё на вводе.

Собираем журнал «Дуката» целиком

Теперь всё вместе: лист «Меню» как источник вариантов, журнал с оформленной шапкой и проверкой на колонке напитков. Так выглядит файл, который можно отдавать баристам без страха за сводку.

import os
from datetime import datetime
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
menu = wb.active
menu.title = "Меню"
for i, drink in enumerate(["Американо", "Капучино", "Латте", "Раф", "Какао"], start=1):
    menu.cell(row=i, column=1, value=drink)

ws = wb.create_sheet("Журнал")
for col, title in enumerate(["Дата", "Бариста", "Напиток"], start=1):
    c = ws.cell(row=1, column=col, value=title)
    c.font = Font(bold=True, color="FFFFFF")
    c.fill = PatternFill("solid", fgColor="6F4E37")

ws["A2"] = datetime(2026, 10, 1)
dv = DataValidation(
    type="list",
    formula1="=Меню!$A$1:$A$5",
    allow_blank=True,
    showErrorMessage=True,
    errorTitle="Нет в меню",
    error="Выбери напиток из листа Меню",
)
ws.add_data_validation(dv)
dv.add("C2:C30")
wb.save("dukat_journal.xlsx")

wb2 = load_workbook("dukat_journal.xlsx")
ws2 = wb2["Журнал"]
d = ws2.data_validations.dataValidation[0]
print("листы:", wb2.sheetnames)
print("список из:", d.formula1)
print("проверяем:", str(d.sqref))
wb2.close()
os.remove("dukat_journal.xlsx")

листы: ['Меню', 'Журнал']
список из: =Меню!$A$1:$A$5
проверяем: C2:C30

Файл готов к выдаче: дата в A2 — настоящий datetime из урока про даты, шапка окрашена, напитки выбираются из меню. Когда заведение добавит «Матча», ты допишешь одну строку на лист «Меню» — правило подхватит новый вариант без единой правки кода.

Ошибку можно смягчить: stop, warning, information

Строгий stop — не единственный вариант: errorStyle="warning" разрешит ввести постороннее значение после предупреждения, а "information" — мягко напомнит и примет всё, что ввели. Для журнала, где бариста иногда продаёт лимонад вне меню, warning честнее stop: правило помогает, а не воюет.

import os
from openpyxl import Workbook, load_workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
ws.title = "Журнал"
dv = DataValidation(
    type="list",
    formula1='"Американо,Капучино,Латте"',
    allow_blank=True,
    showErrorMessage=True,
    errorStyle="warning",
    errorTitle="Точно верно?",
    error="Такого напитка нет в меню",
)
ws.add_data_validation(dv)
dv.add("B2:B20")
wb.save("dukat_warn.xlsx")

d = load_workbook("dukat_warn.xlsx")["Журнал"].data_validations.dataValidation[0]
print("errorStyle:", d.errorStyle)
print("error:", d.error)
os.remove("dukat_warn.xlsx")

errorStyle: warning
error: Такого напитка нет в меню

Выбор строгости — это вопрос доверия к тому, кто заполняет файл. Для колонок, где ошибка ломает расчёты (цены, артикулы), оставляй stop: молчаливое «почти правильно» хуже честного отказа. Для журналов и форм, где возможны исключения вне меню, warning спасает и от анархии, и от тупика — человек подтвердит «Лимонад» осознанно, и он останется в файле.

Как правило выглядит внутри файла?

Ты уже видел в уроке про картинки, что xlsx — zip-архив. Правило валидации живёт там же: XML-элемент dataValidation внутри разметки листа. Заглянем глазами zipfile — и заодно увидим те самые флаги, о которых говорили выше.

import os
import re
import zipfile
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
dv = DataValidation(type="list", formula1='"Американо,Капучино,Латте"', allow_blank=True)
ws.add_data_validation(dv)
dv.add("B2:B20")
wb.save("dukat_xml.xlsx")

z = zipfile.ZipFile("dukat_xml.xlsx")
xml = z.read("xl/worksheets/sheet1.xml").decode("utf-8")
m = re.search(r"<dataValidation .*?</dataValidation>", xml)
print(m.group(0))
z.close()
os.remove("dukat_xml.xlsx")

<dataValidation sqref="B2:B20" showDropDown="0" showInputMessage="0" showErrorMessage="0" allowBlank="1" type="list"><formula1>"Американо,Капучино,Латте"</formula1></dataValidation>

Вот они, наши знания в первозданном виде: sqref с диапазоном, allowBlank="1", формула со списком во внутренних кавычках — и showErrorMessage="0", потому что флаг мы не включали. Excel читает ровно эту разметку и решает, как охранять ячейки.

Правило валидации — это договор, записанный в файл: библиотека его составляет, Excel — исполняет.