Protect and Unprotect Excel Worksheets and Workbooks...

Protect and Unprotect Excel Worksheets and Workbooks Using Python

Q: How can I protect an Excel sheet with Python without losing existing cell formatting?

Use openpyxl to load the workbook, modify only the protection attributes, and then call ws.protection.set_password('pwd'). The library preserves all formatting, charts, and conditional rules because it works on the file’s XML structure directly.

Q: Can I protect a workbook that contains macros (xlsm) using Python?

Yes—openpyxl can read/write .xlsm files, but it will not alter the VBA code. After protecting the workbook (setting wb.security.workbookPassword), the macros remain functional; just ensure the macro security settings in Excel allow them to run.

Q: Is there a way to programmatically check if a sheet is already protected before applying a new password?

Both openpyxl and xlwings expose a ws.protection.sheet (boolean) flag. You can read this flag, and if it’s True, retrieve the existing password hash (if needed) or simply skip re‑applying protection.

Over 70 % of data‑driven businesses claim accidental spreadsheet edits cause costly errors. With just a few lines of Python, you can lock down those vulnerable cells the same way you would with the Excel lengthy click‑through. Imagine you’ve spent hours building a VLOOKUP‑driven model, only to have a teammate unintentionally overwrite a critical formula. A quick Python script can prevent that mishap before it ever happens.

Why Worksheet Protection Matters in Real‑World Workflows

Data integrity is king. When you lock formulas like VLOOKUP or XLOOKUP, you stop the accidental erasure that can ripple through a whole analysis. Compliance folks love a locked spreadsheet because it proves sensitive cells are safeguarded, making audit trails cleaner. Collaboration also gets a boost: multiple team members can edit a shared file, but the core logic stays untouched. This is especially handy when you’re handing off models to finance punches or QA reviewers. Think about a monthly revenue model. If someone changes a lookup range, the entire forecast shifts. By protecting the sheet, you keep the logic intact while still letting users input new data. It’s a simple safeguard that saves headaches.

Core Concepts: Excel’s Protection Model Explained

Excel offers two layers of protection: sheet‑level and workbook‑level. Sheet protection locks individual cells, rows, columns, and objects on a specific worksheet. Workbook protection, on the other hand, locks the file’s structure—no adding, deleting, or renaming sheets without a password. By default, all cells are locked, but Excel only enforces the lock once you turn on protection. That means you can set certain ranges to be editable by toggling the `lockedैयाँ` property. Remember, protected sheets still let you copy, print, or view them. Only editing gets blocked. If you need to prevent a user from even seeing the formula bar, you’d have to use advanced VBA or a third‑party add‑in; Python can’t cover that.

Setting Up Your Python Environment (Prerequisites)

Install openpyxl for pure‑Python manipulation of .xlsx files.
For full Excel COM support on Windows, add pywin32 or xlwings if you need to launch Excel itself.
On macOS or Linux, xlwings works too, but it requires the Excel app to be installed.

Here’s a quick test workbook you can create:

Open Excel, type A1 with Product, B1 with Price, C1 with Qty, D1 with Total.
In D2, insert =B2*C2 using a VLOOKUP to pull the price.
Fill down a few rows.
Save the file as template.xlsx in your working folder.

That template will be the playground for our code.

Practical Walkthrough: Protecting & Unprotecting with Python

Below is a step‑by‑step code example that uses openpyxl to lock formulas and keep an input range editable. We’ll also show how to unprotect later.

import openpyxl
from openpyxl.styles import Protection

def protect_sheet(file_path, sheet_name, pwd, editable_range):
    # Load the workbook; keep the original formatting intact
    wb = openpyxl.load_workbook(file_path)
    ws = wb[sheet_name]

    # Define editable cells: e.g., B2:C10
    start_col, start_row = editable_range[0]
    end_col, end_row = editable_range[1]

    for row in ws.iter_rows(min_row=start_row, max_row=end_row,
                            min_col=openpyxl.utils.column_index_from_string(start_col),
                            max_col=openpyxl.utils.column_index_from_string(end_col)):
        for cell in row:
            cell.protection = Protection(locked=False)

    # Apply sheet protection
    ws.protection.set_password(pwd, strong=True)
    ws.protection.sheet = True
    ws.protection.enable()

    wb.save(file_path)
    print(f"Sheet '{sheet_name}' protected. Editable range: {editable_range[0]}:{editable_range[1]}")

def unprotect_sheet(file_path, sheet_name, pwd):
    wb = openpyxl.load_workbook(file_path)
    ws = wb[sheet_name]

    if ws.protection.sheet:
        ws.protection.set_password(pwd)
        ws.protection.sheet = False
        ws.protection.enable()
        wb.save(file_path)
        print(f"Sheet '{sheet_name}' unprotected.")
    else:
        print(f"Sheet '{sheet_name}' was already unprotected.")

To use the functions:

protect_sheet('template.xlsx', 'Sheet1', 'mypassword', (('B', 2), ('C', 10)))
# Later on...
unprotect_sheet('template.xlsx', 'Sheet1', 'mypassword')

If you want to lock the whole workbook, just add:

wb.security.workbookPassword = 'mypwd'
wb.security.lockStructure = True

Now the file can’t be rearranged without the password.

Actionable Takeaways & Best Practices

Automate protection in your ETL pipelines: lock sheets right after generating reports.
Do not hard‑code passwords. Store them in environment variables or a secret manager like Azure Key Vault.
Keep a “protected template” file. When data entry is needed, copy it to a new workbook and leave the template untouched.
Before sharing, run a quick checklist:
- Formulas locked?
- Password set?
- Structure locked?
- Test an edit attempt; if it fails, you’re good.
Remember that openpyxl preserves all formatting, charts, and conditional rules. You’re only touching the protection flags.

Frequently Asked Questions

How can I protect an Excel sheet with Python without losing existing cell formatting?

Use openpyxl to load the workbook, modify only the protection attributes, and then call ws.protection.set_password('pwd'). The library preserves all formatting, charts, and conditional rules because it works on the file’s XML structure directly.

Can I protect a workbook that contains macros (xlsm) using Python?

Yes—openpyxl can read/write .xlsm files, but it will not alter the VBA code. After protecting the workbook (setting wb.security.workbookPassword), the macros remain functional; just ensure the macro security settings in Excel allow them to run.

What’s the difference between protecting a worksheet and protecting a workbook?

Worksheet protection stops users from editing cells, rows, columns, and objects on that specific sheet. Workbook protection locks the overall file structure—preventing sheet addition, deletion, renaming, or moving—while still allowing normal cell edits if the sheet itself isn’t locked.

Is there a way to programmatically check if a sheet is already protected before applying a new password?

Both openpyxl and xlwings expose a ws.protection.sheet (boolean) flag. You can read this flag, and if it’s True, retrieve the existing password hash (if needed) or simply skip re‑applying protection.

How do I unprotect a sheet when I’ve forgotten the password?

Python cannot bypass Excel’s encryption; you’ll need the original password or a third‑party password‑recovery tool. The best practice is to store passwords securely (e.g., Azure Key Vault, AWS Secrets Manager) so your script can retrieve them when needed.

Pydantic V2 Discriminated Unions in FastAPI: Modeling...

Pydantic V2 Discriminated Unions in FastAPI: Modeling Polymorphic AI Feature Configs Without Schema Sprawl Over 70 % of FastAPI projects hit a breaking point when their request models start to balloon with duplicated fields. Imagine a single endpoint that can accept any AI‑feature configuration—text‑generation, image‑to‑image, or speech‑synthesis—without exploding your OpenAPI schema or writing endless if‑else validation logic. With Pydantic V2’s discriminated unions, that dream becomes a clean, type‑safe reality. In This Article Why Polymorphic Configs Matter in Modern AI‑Driven APIs Core Concepts: Discriminated Unions in Pydantic V2 Step‑by‑Step Walkthrough: Building a FastAPI Endpoint with AI Feature Configs Handling Edge Cases & Integration with Popular Data‑Science Tools Actionable Takeaways & Best‑Practice Checklist Frequently Asked Questions 1️⃣ Why Polymorphic Configs Matter in Modern AI‑Driven APIs In my experience, the biggest pain point for teams is th...

Code & Crumbs

Search This Blog