Python Excel AutomationPractical guides for developers
Start learning
Guide
Formatting And Charting Excel Reports With PythonDeep dive

Open a Password-Protected Excel File with Python

Decrypt an encrypted workbook in memory with msoffcrypto-tool, read it with pandas or openpyxl, handle wrong passwords, and tell encryption from sheet protection.

A workbook saved with Excel's "Password to open" is encrypted, and every Python reader fails on it in a way that hides the cause: openpyxl reports that the file is not a zip, and pandas cannot determine the format. Neither message mentions encryption. The fix is a small library, msoffcrypto-tool, which decrypts the container into a buffer that the normal readers then handle unchanged. This guide decrypts one file and a folder of them, handles wrong passwords cleanly, and draws the line between encryption and the sheet protection it is often confused with. It belongs to Protecting and Sharing Excel Workbooks.

Two different things called a password Encryption wraps the whole workbook in an OLE2 container that must be decrypted before any reader sees a zip, while sheet protection is a flag inside a perfectly readable file. encrypted (password to open) protected (password to edit) OLE2 container, encrypted payload openpyxl: "not a zip file" decrypt first, then read ordinary zip, readable bytes openpyxl opens it with no password a flag, not a lock, for code

Prerequisites

Bash
pip install msoffcrypto-tool pandas openpyxl

msoffcrypto-tool handles the encryption formats Office uses, including the modern AES-based scheme and the older ones.

Decrypt into memory and read

The whole operation is one function, and the plaintext workbook never reaches disk:

Python
"""Read an encrypted workbook without writing a decrypted copy."""
import io

import msoffcrypto
import pandas as pd

def read_encrypted(path: str, password: str, **kwargs) -> pd.DataFrame:
    decrypted = io.BytesIO()
    with open(path, "rb") as fh:
        office_file = msoffcrypto.OfficeFile(fh)
        office_file.load_key(password=password)
        office_file.decrypt(decrypted)

    decrypted.seek(0)
    kwargs.setdefault("engine", "openpyxl")
    return pd.read_excel(decrypted, **kwargs)

df = read_encrypted("confidential.xlsx", password="hunter2")
print(df.head())

After decrypt() the buffer holds an ordinary .xlsx, so everything downstream is unchanged — openpyxl works the same way:

Python
from openpyxl import load_workbook

decrypted.seek(0)
wb = load_workbook(decrypted)
print(wb.sheetnames)

Detect encryption before trying to read

Rather than catching a confusing error, ask the question directly:

Python
import msoffcrypto

def is_encrypted(path: str) -> bool:
    with open(path, "rb") as fh:
        try:
            return msoffcrypto.OfficeFile(fh).is_encrypted()
        except Exception:          # not an Office container at all
            return False

print(is_encrypted("confidential.xlsx"))   # True

That lets an ingest step branch cleanly: decrypt what is encrypted, read the rest directly, and report anything that is neither. The related identification problem is covered in Fix BadZipFile when reading an Excel file in Python.

Handle a wrong password properly

A bad password raises, and the message deserves improving before it reaches a log:

Python
import io

import msoffcrypto

class DecryptionError(RuntimeError):
    pass

def decrypt(path: str, password: str) -> io.BytesIO:
    out = io.BytesIO()
    with open(path, "rb") as fh:
        office_file = msoffcrypto.OfficeFile(fh)
        if not office_file.is_encrypted():
            return io.BytesIO(open(path, "rb").read())
        try:
            office_file.load_key(password=password)
            office_file.decrypt(out)
        except msoffcrypto.exceptions.InvalidKeyError as exc:
            raise DecryptionError(f"{path}: wrong password") from exc
    out.seek(0)
    return out

Returning the file unchanged when it is not encrypted makes the function safe to call on everything, which is what an ingest loop wants.

One ingest path that handles both kinds of file Each incoming file is tested for encryption; encrypted ones are decrypted into a buffer and plain ones pass through, so a single reader handles the merged stream. Branch once, read once incoming file is_encrypted()? decrypt to BytesIO read bytes as they are one reader

Keep the password out of the code

A password in a source file outlives every job that used it. Read it from the environment, or from whatever secrets manager the deployment already has:

Python
import os

password = os.environ.get("REPORT_PASSWORD")
if not password:
    raise SystemExit("REPORT_PASSWORD is not set")

For a folder of files from different senders, keep a mapping outside the code — an environment variable per sender, or a secrets-manager lookup keyed on the sender's name. Never log the password, and never put it in a filename.

Decrypt a whole folder

Python
"""Decrypt every encrypted workbook into a working directory."""
import os
from pathlib import Path

import msoffcrypto

SRC = Path("incoming")
DST = Path("decrypted")
DST.mkdir(exist_ok=True)
password = os.environ["REPORT_PASSWORD"]

for src in sorted(SRC.glob("*.xlsx")):
    with open(src, "rb") as fh:
        office_file = msoffcrypto.OfficeFile(fh)
        if not office_file.is_encrypted():
            print("plain:", src.name)
            continue
        office_file.load_key(password=password)
        with open(DST / src.name, "wb") as out:
            office_file.decrypt(out)
    print("decrypted:", src.name)

If you write decrypted copies to disk, treat that directory as sensitive: restrict its permissions, keep it off shared volumes, and delete the contents when the run finishes.

Write an encrypted file back out

msoffcrypto-tool decrypts; it does not encrypt. To produce a protected workbook you need Excel itself, or a library that wraps it — the options and their trade-offs are in Password protect an Excel file with Python. For most report pipelines the better answer is transport security rather than file encryption: deliver through a link that requires authentication instead of emailing an encrypted attachment and its password separately.

Do not confuse this with sheet protection

Sheet and structure protection are flags inside a readable file. openpyxl opens such a workbook without a password and can read every cell; the protection only stops editing in Excel's UI:

Python
from openpyxl import load_workbook

wb = load_workbook("protected.xlsx")     # no password needed
ws = wb["Data"]
print(ws.protection.sheet)               # True — but every value is readable

That distinction matters when someone asks for a "password-protected" report: if the intent is confidentiality, protection is not enough, and if the intent is preventing accidental edits, encryption is too much. Setting protection is covered in Lock cells and protect a sheet with openpyxl.

Choose the protection that matches the intent

"Protect the report" means at least three different things, and picking the wrong one produces either a false sense of security or a file nobody can use:

Three levels of workbook protection and what each stops Sheet protection prevents accidental edits, encryption prevents reading without the password, and an authenticated download prevents the file being shared at all. What are you actually protecting against? sheet protection stops accidental edits contents fully readable no help for confidentiality for: shared templates encryption unreadable without a key password must travel too lost password, lost file for: attachments in transit authenticated link access checked per person revocable, auditable needs somewhere to host it for: recurring reports

The third column is the one most teams should be moving towards. An encrypted attachment travels with its own password, usually in a second email, and cannot be revoked once sent; a link checked against an identity can be withdrawn the day someone changes team. The delivery mechanics are in Publishing Excel Reports to Cloud Storage.

Fit it into a scheduled ingest

The decryption step belongs at the very front of a pipeline, before anything else forms an opinion about the file. A small wrapper keeps the rest of the job unaware that encryption exists at all:

Python
"""ingest.py — encryption handled once, at the boundary."""
import io
import os
from pathlib import Path

import msoffcrypto
import pandas as pd

PASSWORD = os.environ.get("REPORT_PASSWORD", "")

def open_workbook(path: Path) -> io.BytesIO:
    """Return readable workbook bytes, decrypting only if necessary."""
    raw = path.read_bytes()
    if raw[:2] == b"PK":                 # already a plain zip
        return io.BytesIO(raw)

    buffer = io.BytesIO()
    with io.BytesIO(raw) as source:
        office_file = msoffcrypto.OfficeFile(source)
        office_file.load_key(password=PASSWORD)
        office_file.decrypt(buffer)
    buffer.seek(0)
    return buffer

for path in sorted(Path("incoming").glob("*.xlsx")):
    df = pd.read_excel(open_workbook(path), engine="openpyxl")
    print(f"{path.name}: {len(df):,} rows")

Checking the first two bytes rather than calling is_encrypted() avoids opening the file twice, and it also handles the case where a sender switches protection on or off between months without telling anybody. Log which files needed decrypting — that record is useful when a password rotates and half the batch suddenly fails.

Common pitfalls and gotchas

  • Reading before seek(0). The decrypted buffer is positioned at the end after writing.
  • Assuming the error means corruption. "Not a zip file" is exactly what an encrypted workbook looks like to openpyxl.
  • Leaving decrypted copies behind. Delete them, or decrypt into memory and avoid the question.
  • Hard-coded passwords. They end up in version control and in log lines built with f-strings.
  • Legacy .xls encryption. Older schemes are supported but weaker; if you control the sender, ask for a modern format.

Performance and scale notes

Decryption is fast — it is a symmetric cipher over a file that is already compressed — so the cost is dominated by the subsequent Excel parse, exactly as it would be for a plain file. Memory is the consideration: the decrypted workbook exists in the buffer alongside the encrypted source, so peak usage is roughly twice the file size before parsing begins. For a large encrypted workbook, decrypt to a temporary file rather than a buffer, parse from the path, and delete it in a finally block. The same reasoning applies to any in-memory pipeline, as covered in Build an Excel workbook in memory with BytesIO.

Conclusion

An encrypted workbook is not a corrupt one — it is an OLE2 container that no zip-based reader can open. Decrypt it with msoffcrypto-tool into a BytesIO, then read it exactly as you would any other file. Detect encryption up front so an ingest loop can branch cleanly, raise a clear error on a wrong password, keep the password in the environment, and remember that sheet protection is a different thing entirely.

Frequently asked questions

Why does openpyxl say a protected file is not a zip? An encrypted workbook is an OLE2 container wrapping the encrypted payload, not a zip. openpyxl opens the zip first, so it fails before it can ask for a password. Decrypt the file first, then read the result.

Is this the same as a sheet-protection password? No. Sheet and workbook-structure protection stop editing but leave the file readable — openpyxl opens it without any password. Encryption ("Password to open") makes the bytes unreadable until decrypted.

Can Python remove the password permanently? You can write out a decrypted copy, which is what most pipelines want. Keep the encrypted original if the protection is a requirement rather than an obstacle.

What if I do not have the password? Then you cannot read the file. Modern Office encryption is AES-based and there is no supported way around it — ask the sender to re-issue the workbook or share the password through a secrets manager.

Does the decrypted copy have to touch disk? No. msoffcrypto-tool writes to any file-like object, so decrypting into an io.BytesIO and reading from that keeps the plaintext workbook in memory only.