Защита листа и ячеек в openpyxl: пароль и разрешённые действия

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

Механика двухслойная. Первый слой — замок на каждой ячейке: у них есть свойство protection.locked, и по умолчанию оно включено. Второй слой — замок на всём листе: ws.protection.sheet = True. Работают замки только в паре: пока лист не защищён, флаг locked ничего не значит, а когда лист закрыт — редактировать можно лишь те ячейки, у которых locked выключен.

В Excel всё это делается через меню «Рецензирование»: «Защитить лист» и «Разблокировать ячейки» (формат ячеек, вкладка «Защита»). Мы делаем то же самое кодом — и потому можем защитить сотню листов одним циклом, а не сотней кликов.

Как поставить пароль на лист Excel?

Прежде чем закрывать лист, посмотрим на заводские настройки объекта ws.protection — они объяснят половину сюрпризов, которые ждут тебя после включения защиты.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
p = ws.protection
print("лист защищён:", p.sheet)
print("sort:", p.sort)
print("autoFilter:", p.autoFilter)
print("formatCells:", p.formatCells)
print("insertRows:", p.insertRows)
print("selectLockedCells:", p.selectLockedCells)

лист защищён: False
sort: True
autoFilter: True
formatCells: True
insertRows: True
selectLockedCells: False

Стоп, что? Сортировка «True» на незащищённом листе? Здесь и кроется главная ловушка темы: в объекте protection значение True означает «действие запрещено», а False — «разрешено». Флаги sort и formatCells стоят в True заранее: как только ты включишь защиту листа, сортировка и форматирование окажутся под замком, даже если ты их не трогал. К инверсии вернёмся через пару разделов, а пока — сам пароль.

import os
from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active
ws.title = "Журнал"
ws["A1"] = "Товар"
ws["B1"] = "Цена, руб."

ws.protection.sheet = True
ws.protection.password = "dukat"
wb.save("dukat_locked.xlsx")

wb2 = load_workbook("dukat_locked.xlsx")
p = wb2["Журнал"].protection
print("лист защищён:", p.sheet)
print("хеш пароля:", p.password)
wb2.close()
os.remove("dukat_locked.xlsx")

лист защищён: True
хеш пароля: C49A

Обрати внимание, что вернулось вместо пароля: C49A — четыре шестнадцатеричных цифры. Excel никогда не хранит пароль; сохраняется его короткий отпечаток-хеш, по которому проверяется совпадение при попытке снять защиту. Сам пароль «dukat» в файле не лежит нигде — это безопаснее, чем звучит пугающе, и ровно настолько же слабее, чем кажется.

Почему снять защиту — это три строки кода?

Лучший способ понять силу замка — открыть его. Снимается защита тем же свойством, что и ставится, причём даже без пароля: openpyxl доверяет тому, кто уже прочитал файл.

import os
from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active
ws.protection.sheet = True
ws.protection.password = "dukat"
wb.save("dukat_p.xlsx")

wb2 = load_workbook("dukat_p.xlsx")
ws2 = wb2.active
print("до снятия: sheet =", ws2.protection.sheet, "hash =", ws2.protection.password)
ws2.protection.sheet = False   # флаг снят - пароль не потребовался
wb2.save("dukat_p2.xlsx")

wb3 = load_workbook("dukat_p2.xlsx")
print("лист защищён:", wb3.active.protection.sheet)
print("хеш в файле:", wb3.active.protection.password)
wb3.close()
os.remove("dukat_p.xlsx")
os.remove("dukat_p2.xlsx")

до снятия: sheet = True hash = C49A
лист защищён: False
хеш в файле: None

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

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

Почему заблокировался весь лист, если нужен был один столбец?

Ты поставил пароль, чтобы защитить цены, — и бариста больше не может вписать количество проданных стаканов. Причина в замке по умолчанию: locked включён у всех ячеек, поэтому лист закрылся целиком. Решение — заранее выключить замок там, где нужна свобода: Protection(locked=False). Protection — из того же модуля openpyxl.styles, что Font и PatternFill из урока про стили.

import os
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Protection

wb = Workbook()
ws = wb.active
ws.title = "Журнал"
ws.append(["Товар", "Цена", "Продано"])
for name, price in [("Американо", 190), ("Капучино", 250), ("Латте", 270)]:
    ws.append([name, price, None])

for r in range(2, 5):
    # колонка «Продано» остаётся редактируемой
    ws.cell(row=r, column=3).protection = Protection(locked=False)

ws.protection.sheet = True
wb.save("dukat_journal.xlsx")

wb2 = load_workbook("dukat_journal.xlsx")
ws2 = wb2["Журнал"]
print("B2 (цена) заперта:", ws2["B2"].protection.locked)
print("C2 (продано) заперта:", ws2["C2"].protection.locked)
wb2.close()
os.remove("dukat_journal.xlsx")

B2 (цена) заперта: True
C2 (продано) заперта: False

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

Разлочивать можно и пачкой: цикл по диапазону строк, целая колонка или группа ячеек — где нужен ввод от человека, там выключенный locked. В больших шаблонах удобно держать «полосу ввода» — несколько колонок, окрашенных светло-жёлтым и разлоченных, — а весь остальной лист держать под замком.

True — это разрешить или запретить?

Возвращаемся к инверсии флагов. Логика такая: True в объекте protection означает «действие защищено, то есть запрещено», False — «действие доступно». Поэтому чтобы разрешить баристе сортировать журнал, пишут p.sort = False. Звучит нелогично, но таков формат xlsx — openpyxl передаёт значения один в один.

import os
from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active
p = ws.protection
p.sheet = True
p.sort = False              # сортировку разрешаем
p.autoFilter = False        # фильтр разрешаем
p.selectLockedCells = True  # выделять запертые ячейки - нельзя
wb.save("dukat_flags.xlsx")

wb2 = load_workbook("dukat_flags.xlsx")
p2 = wb2.active.protection
print("лист защищён:", p2.sheet)
print("sort:", p2.sort)
print("autoFilter:", p2.autoFilter)
print("selectLockedCells:", p2.selectLockedCells)
wb2.close()
os.remove("dukat_flags.xlsx")

лист защищён: True
sort: False
autoFilter: False
selectLockedCells: True

Разумная стратегия для рабочих файлов — открыть максимум и запретить только разрушительное: сортировку, фильтр и вставку строк разрешают, а вот удаление строк и изменение формул закрывают. Файл, защищённый от всего подряд, люди обходят: снимают защиту целиком и дальше работают без неё.

Отдельный случай — формы ввода для чужих людей: заявки, опросы, журналы. Там включают selectUnlockedCells и selectLockedCells так, чтобы курсор вообще не застревал в закрытых ячейках: перемещение по Tab идёт только по открытым полям, и заполнение превращается в последовательность «Tab, ввод, Tab, ввод».

Где хранится пароль и почему хеши разные?

Хеш считается по паролю детерминированно: один и тот же пароль даёт один и тот же отпечаток в любом файле. Убедимся на трёх паролях — и заодно посмотрим на защиту уровня книги.

from openpyxl.worksheet.protection import SheetProtection

for pw in ["dukat", "latte", "dukat2026"]:
    print(pw, "->", SheetProtection(password=pw).password)

dukat -> C49A
latte -> C752
dukat2026 -> 8E16

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

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

Как запретить удалять листы целиком?

У защиты есть уровень выше листа: защита структуры книги. Она не даёт добавлять, переименовывать, перемещать и удалять листы — полезно, когда отчёт состоит из трёх связанных листов и чей-то эксперимент удалил «Итоги» вместе с диаграммой. В openpyxl это объект WorkbookProtection, присваиваемый свойству wb.security.

import os
from openpyxl import Workbook, load_workbook
from openpyxl.workbook.protection import WorkbookProtection

wb = Workbook()
ws = wb.active
ws.title = "Журнал"
wb.security = WorkbookProtection(workbookPassword="dukat", lockStructure=True)
wb.save("dukat_wb.xlsx")

wb2 = load_workbook("dukat_wb.xlsx")
print("структура заблокирована:", wb2.security.lockStructure)
print("хеш книги:", wb2.security.workbookPassword)
wb2.close()
os.remove("dukat_wb.xlsx")

структура заблокирована: True
хеш книги: C49A

Ограничения те же самые: замок на структуру не мешает ни читать данные, ни править содержимое листов — он только про состав книги. Для журнала «Дуката» этого уровня достаточно: листы фиксированы, данные внутри — под своим замком.

Два уровня защиты удобно запомнить как разные вопросы. Замок листа отвечает на «что можно менять в этом листе», замок структуры — «можно ли менять состав книги». В отчётах из нескольких листов их ставят вместе: структура зафиксирована, чтобы «Итоги» никто не удалил, а листы открыты для ввода ровно в тех колонках, которые размечены.

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

Всё вместе: цены заперты, «Продано» открыто, сортировка, фильтр и ширины разрешены, пароль на месте. Такой файл можно отправлять баристам.

import os
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Protection

wb = Workbook()
ws = wb.active
ws.title = "Журнал"
ws.append(["Товар", "Цена", "Продано"])
for name, price in [("Американо", 190), ("Капучино", 250), ("Латте", 270)]:
    ws.append([name, price, None])

for r in range(2, 5):
    ws.cell(row=r, column=3).protection = Protection(locked=False)

p = ws.protection
p.sheet = True
p.password = "dukat2026"
p.sort = False           # сортировать можно
p.autoFilter = False     # фильтр доступен
p.formatColumns = False  # ширины двигать можно
wb.save("dukat_guard.xlsx")

wb2 = load_workbook("dukat_guard.xlsx")
ws2 = wb2["Журнал"]
p2 = ws2.protection
print("лист защищён:", p2.sheet)
print("цены заперты:", ws2["B2"].protection.locked)
print("продано открыто:", not ws2["C2"].protection.locked)
print("сортировка разрешена:", not p2.sort)
print("фильтр разрешён:", not p2.autoFilter)
wb2.close()
os.remove("dukat_guard.xlsx")

лист защищён: True
цены заперты: True
продано открыто: True
сортировка разрешена: True
фильтр разрешён: True

Такой файл рассылают по точкам: бариста вносит продажи в открытые ячейки, форматирование не разъезжается, а сводка по итогам недели собирается консолидацией из следующего урока — защита этому нисколько не мешает, openpyxl читает защищённый лист как обычный.

Замок на листе — это способ объяснить файлом, где проходят границы чужой территории.