Protect Workbook Structure with openpyxl

A report workbook often has a layout that matters: a summary sheet first, a data sheet the formulas depend on, a lookup table that should stay out of sight. Readers will rename "Data" to "Data (old)", drag sheets into a different order, or unhide the lookup and edit it — and the next run of the report breaks. Protecting the workbook structure stops those changes in Excel without restricting what people can do inside the sheets. This guide, part of Protecting and Sharing Excel Workbooks, applies structure protection with openpyxl, combines it with very hidden sheets and sheet protection, and is honest about how little the password protects.

Prerequisites

pip install openpyxl

A report with a summary, a data sheet and a lookup table:

import re
import zipfile

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

wb = Workbook()
summary = wb.active
summary.title = "Summary"
summary["A1"] = "Total sales"
summary["B1"] = "=SUM(Data!B2:B4)"
data = wb.create_sheet("Data")
for row in [("Region", "Sales"), ("North", 120), ("South", 95), ("East", 60)]:
    data.append(row)
lookup = wb.create_sheet("Lookup")
lookup.append(["Code", "Region"])
lookup.append(["N", "North"])

Step 1 — Lock the structure

Assign a WorkbookProtection to wb.security with lockStructure=True. The password is what a reader must enter in Excel to unprotect the structure:

wb.security = WorkbookProtection(workbookPassword="change-me", lockStructure=True)
wb.save("report.xlsx")

xml = zipfile.ZipFile("report.xlsx").read("xl/workbook.xml").decode()
print(re.search(r"<workbookProtection[^>]*>", xml).group(0))
# <workbookProtection workbookPassword="…" lockStructure="1"/>

In Excel, the sheet tabs' context menu now has Insert, Delete, Rename, Move or Copy, Hide and Unhide greyed out until someone chooses Review → Protect Workbook and enters the password. Everything inside the sheets remains editable; that is a separate protection, covered in Step 3. The password is optional: WorkbookProtection(lockStructure=True) without one still blocks accidental changes, and anyone who deliberately wants to restructure the workbook can switch the protection off from the Review tab without asking. For many internal reports that is exactly the right level — a reminder, not a gate.

Step 2 — Hide the helper sheets properly

Structure protection is what makes hidden sheets stay hidden: without it, anyone can right-click a tab and unhide. openpyxl offers two levels. "hidden" sheets appear in Excel's Unhide dialog; "veryHidden" sheets do not appear anywhere in the interface and can only be revealed through the VBA editor or code:

wb = load_workbook("report.xlsx")
wb["Lookup"].sheet_state = "veryHidden"
wb.save("report.xlsx")
print([(ws.title, ws.sheet_state) for ws in load_workbook("report.xlsx").worksheets])
# [('Summary', 'visible'), ('Data', 'visible'), ('Lookup', 'veryHidden')]

Formulas can still refer to a very hidden sheet — =VLOOKUP(A2,Lookup!A:B,2,FALSE) works normally — so it suits lookup tables, settings and intermediate calculations. Remember that hidden is not secret: anyone who opens the file with Python sees every sheet. Hiding sheets, rows and columns in general is covered in Hide Sheets, Rows and Columns with openpyxl.

Step 3 — Combine with sheet protection

Structure protection keeps sheets in place; sheet protection keeps formulas intact. A typical report uses both — locked structure, a protected summary, and an editable data sheet:

wb = load_workbook("report.xlsx")
summary = wb["Summary"]
summary.protection.sheet = True
summary.protection.password = "change-me"
wb.security = WorkbookProtection(workbookPassword="change-me", lockStructure=True)
wb.save("report.xlsx")

check = load_workbook("report.xlsx")
print(check.security.lockStructure, check["Summary"].protection.sheet, check["Data"].protection.sheet)
# True True False

Loading a workbook with openpyxl keeps its protection settings, but assigning wb.security again after loading is a good habit: it makes the script's intent explicit and repairs files where someone removed the protection. Locking individual cells while leaving input cells editable is covered in Lock Cells and Protect a Sheet with openpyxl.

Step 4 — Know what the password is worth

openpyxl stores the password the way old versions of Excel did: as a 16-bit hash, four hexadecimal digits in the file. There are only 65,536 possible values, so a matching password can be found almost instantly, and many different passwords unlock the same file:

protection = WorkbookProtection(workbookPassword="change-me")
print(protection.workbookPassword, len(protection.workbookPassword))     # a 4-digit hex hash, 4
print(16 ** 4)                                                           # 65536

Current Excel versions write a salted SHA-512 hash instead when a person sets the password; openpyxl reads and preserves those, but writes the legacy form. Either way, structure and sheet protection are guard rails against accidental changes by colleagues, not security. For confidentiality, encrypt the file with a password to open, as described in Password Protect an Excel File with Python, and control who receives it.

Step 5 — Your scripts are not blocked

openpyxl does not enforce protection. A script can add, rename and delete sheets in a structure-locked workbook without the password:

wb = load_workbook("report.xlsx")
wb.create_sheet("Archive")
wb.save("report_archived.xlsx")
reloaded = load_workbook("report_archived.xlsx")
print(reloaded.sheetnames, reloaded.security.lockStructure)
# ['Summary', 'Data', 'Lookup', 'Archive'] True

That is convenient — the report job can maintain its own workbook — and it is a reminder of what protection is: an instruction to Excel's user interface, not a property of the data. The same applies to other tools: a pandas read, a database import or a conversion to CSV all see every sheet and every cell regardless of the lock. The lock is still in place afterwards, so readers remain unable to make the same change in Excel.

Step 6 — Audit protection across your reports

When a team produces dozens of workbooks, protection settings drift: one job forgets to lock the structure, another leaves a lookup sheet merely hidden. A short audit over the output folder lists what each file actually contains:

from pathlib import Path

def protection_report(folder):
    rows = []
    for path in sorted(Path(folder).glob("*.xlsx")):
        wb = load_workbook(path)
        security = wb.security
        rows.append({
            "file": path.name,
            "structure_locked": bool(security and security.lockStructure),
            "protected_sheets": [ws.title for ws in wb.worksheets if ws.protection.sheet],
            "hidden_sheets": [f"{ws.title} ({ws.sheet_state})" for ws in wb.worksheets
                              if ws.sheet_state != "visible"],
        })
    return rows

for row in protection_report("."):
    if row["file"].startswith("report"):
        print(row)

Run it after the report jobs, or as part of their tests, and compare the result with what each report is supposed to have. A dictionary of expected settings per report, checked with a simple equality test, turns a silent regression into a failing build. The audit reads only settings, so it works on files you did not create too — though for files that someone has encrypted with a password to open, openpyxl cannot read them at all and the audit should record that instead of failing.

Two other workbook settings sit beside lockStructure. lockWindows once locked the size and position of workbook windows; recent Windows versions of Excel no longer offer that option, so leave it unset. lockRevision relates to Excel's legacy shared-workbook change tracking and is rarely wanted in generated reports.

Common pitfalls

• Expecting cell protection. Structure protection leaves every cell editable. Add sheet protection for formulas.
• Forgetting the password. Keep the password with the report's code or configuration; the person who needs to change the structure next year is probably you.
• Very hidden sheets with data people need. A reader cannot unhide a very hidden sheet even with the password to the structure; they need the VBA editor. Use it only for sheets nobody outside the process should see.
• Protection on a template, not the output. If the job copies a protected template and pandas rewrites sheets, check that protection survives the write — the audit in Step 6 does exactly that.
• Hard-coding the password in public code. It is weak anyway, but there is no reason to publish it. Read it from configuration.

Conclusion

Workbook structure protection keeps a report's sheets where they belong: assign WorkbookProtection(workbookPassword=…, lockStructure=True) to wb.security, set helper sheets to "veryHidden", and add sheet protection where formulas must not be overwritten. Treat the password as a speed bump — openpyxl writes a 16-bit legacy hash — and use encryption when the content itself must be kept private. Your own scripts can still restructure the file whenever they need to.

Frequently asked questions

What does protecting workbook structure do? In Excel it prevents adding, deleting, renaming, moving, copying, hiding and unhiding sheets. It does not stop anyone editing cells, and it does not encrypt the file — for that you need sheet protection and a password to open respectively.

How do I protect the workbook structure with openpyxl? Assign wb.security = WorkbookProtection(workbookPassword="…", lockStructure=True) before saving. Excel then asks for the password before allowing any change to the sheets.

Is the openpyxl workbook password secure? No. openpyxl stores Excel's legacy 16-bit password hash, which has only 65,536 possible values and can be matched almost instantly. Treat structure protection as a guard against accidents, not as security.

Can I still change a protected workbook with Python? Yes. openpyxl does not enforce the lock, so scripts can add or remove sheets freely. Only applications that honour the protection, such as Excel, prevent the changes.

Related

• Up one level: Protecting and Sharing Excel Workbooks — protection, comments and sharing.
• Remove Sheet Protection from a Workbook You Own with openpyxl — the reverse operation.
• Open a Password-Protected Excel File with Python — encrypted files, which are different.
• Rename, Reorder and Delete Excel Sheets with openpyxl — structural changes from code.