Python Doc & Data Automation

Read Password-Protected Excel Files in Python

The bank sends a password-protected workbook every morning, and the loader fails before reading a single row:

zipfile.BadZipFile: File is not a zip file

or, through openpyxl:

openpyxl.utils.exceptions.InvalidFileException: openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format.

Neither message mentions a password. Opening the same file in Excel prompts for one, and after typing it the data is plainly there.

Root Cause

A password-protected .xlsx is not a ZIP archive with a locked flag — it is an OLE compound file (the old .doc-era container) holding an encrypted stream of the real workbook. pandas and openpyxl expect a ZIP, see the OLE header, and report a format problem rather than an encryption problem, which is why the error text is misleading. Decryption has to happen before any reader sees the file. Two other kinds of "protection" are commonly confused with this one: workbook structure protection, which prevents adding or deleting sheets but leaves the file readable, and sheet protection, which prevents editing cells; neither encrypts anything, and both are invisible to a reader that only extracts values. A fourth case, "read-only recommended", sets a flag Excel honours and Python ignores entirely.

Minimal Diagnostic

Identify which kind of protection a file has from its first bytes and, for a readable file, from its workbook and sheet settings.

# pip install msoffcrypto-tool openpyxl
from pathlib import Path
import msoffcrypto
from openpyxl import load_workbook

SOURCE = Path("in/bank-statement.xlsx")
OLE_MAGIC = b"\xd0\xcf\x11\xe0\xa1\xb1\x1a\xe1"
ZIP_MAGIC = b"PK\x03\x04"

def diagnose(path: Path) -> None:
    head = path.read_bytes()[:8]
    if head.startswith(OLE_MAGIC):
        with path.open("rb") as fh:
            office_file = msoffcrypto.OfficeFile(fh)
            print(f"{path.name}: OLE container, encrypted={office_file.is_encrypted()}")
        return
    if not head.startswith(ZIP_MAGIC):
        print(f"{path.name}: neither ZIP nor OLE — not an Office file?")
        return
    wb = load_workbook(path, read_only=False)
    try:
        print(f"{path.name}: ZIP (not encrypted)")
        print(f"  workbook structure protected: {bool(wb.security and wb.security.lockStructure)}")
        for ws in wb.worksheets:
            if ws.protection.sheet:
                print(f"  sheet {ws.title!r} protected (editing blocked, values still readable)")
    finally:
        wb.close()

if __name__ == "__main__":
    diagnose(SOURCE)

bank-statement.xlsx: OLE container, encrypted=True

Encrypted. A file that reports ZIP (not encrypted) with protected sheets needs no password to read — the loader should simply proceed.

Four things called "protected" The root asks what the first bytes of the file show. An OLE container means the workbook is encrypted and must be decrypted with a password before any reader can open it. A ZIP container means the file is readable, and any protection is structural or per sheet. Workbook structure protection prevents adding or deleting sheets but not reading. Sheet protection prevents editing cells but values are still readable. A read-only recommendation is only a flag that Excel honours and Python ignores. What do the first bytes show? OLE magic or PK zip magic OLE container Encrypted file password needed to read ZIP, lockStructure Structure protected sheets cannot be added ZIP, sheet.protection Sheet protected editing blocked only ZIP, read-only flag Recommendation Excel-only hint Decrypt first msoffcrypto-tool Read normally no password needed Read normally values are readable

Fix: Decrypt to a Temporary Stream, Then Read Normally

msoffcrypto-tool decrypts the workbook into a buffer, which pandas reads like any file. Keep the decrypted bytes in memory so an unprotected copy never lands on disk. Changed lines carry comments.

# pip install msoffcrypto-tool "pandas>=2.2" openpyxl
import io
import os
from pathlib import Path
import msoffcrypto
import pandas as pd

def decrypt_to_buffer(path: Path, password: str) -> io.BytesIO:
    buffer = io.BytesIO()
    with path.open("rb") as fh:
        office_file = msoffcrypto.OfficeFile(fh)
        if not office_file.is_encrypted():
            buffer.write(path.read_bytes())                       # changed: pass through unencrypted files
            buffer.seek(0)
            return buffer
        office_file.load_key(password=password)                    # changed: password, not a key file
        office_file.decrypt(buffer)                                # changed: plaintext stays in memory
    buffer.seek(0)
    return buffer

def read_protected_excel(path: Path, password: str, **read_kwargs) -> dict[str, pd.DataFrame]:
    try:
        buffer = decrypt_to_buffer(path, password)
    except msoffcrypto.exceptions.InvalidKeyError:
        raise SystemExit(f"wrong password for {path.name}")        # changed: clear, specific failure
    except msoffcrypto.exceptions.FileFormatError as exc:
        raise SystemExit(f"{path.name} is not a readable Office file: {exc}")
    return pd.read_excel(buffer, sheet_name=None, engine="openpyxl", **read_kwargs)

if __name__ == "__main__":
    password = os.environ["STATEMENT_PASSWORD"]                    # changed: never hard-code it
    sheets = read_protected_excel(Path("in/bank-statement.xlsx"), password,
                                  dtype={"account": "string"})
    for name, frame in sheets.items():
        print(f"{name}: {frame.shape}")

Transactions: (412, 7)
Summary: (14, 3)

Decrypting into io.BytesIO matters for more than tidiness: a decrypted copy on a shared drive removes the protection the sender applied, and often sits there long after the job finishes. If a file is too large for memory, decrypt into a file inside a tempfile.TemporaryDirectory() so it is deleted even when the job crashes.

Decrypt in memory, read, discard The encrypted workbook is opened in binary mode and msoffcrypto detects encryption. The password is read from an environment variable or secret store, never from code. The workbook is decrypted into a BytesIO buffer in memory. pandas reads the buffer with the openpyxl engine. The buffer is discarded when the function returns, so no unprotected copy exists on disk. Encrypted file OLE container Password from secret store Decrypt into BytesIO Read pandas + openpyxl Discard buffer nothing on disk

Variant Fix 1: Sheet and Workbook Protection

Protected sheets need no password to read. To modify one, remove the protection on the in-memory workbook — which works without knowing the password, because sheet protection is not encryption:

# pip install openpyxl
from pathlib import Path
from openpyxl import load_workbook

def unprotect_sheets(src: Path, dest: Path) -> list[str]:
    wb = load_workbook(src)
    changed = []
    try:
        if wb.security is not None:
            wb.security.lockStructure = False                 # allow adding/removing sheets
        for ws in wb.worksheets:
            if ws.protection.sheet:
                ws.protection.sheet = False                    # allow cell edits in the output copy
                changed.append(ws.title)
        wb.save(dest)
    finally:
        wb.close()
    return changed

That this works without the password is worth understanding rather than exploiting: sheet protection is a convenience feature to stop accidental edits, not a security control, and any tool can remove it. Data that genuinely must not be read needs file encryption — and data that must not be changed needs a controlled source system, not a spreadsheet. When a job needs to write into a protected template, removing protection on the copy it writes is legitimate; leave the original untouched.

Variant Fix 2: Legacy .xls and Modern Encryption Variants

Old .xls files use a different, weaker encryption scheme, and some workbooks are encrypted with a modify password while being readable without one. msoffcrypto-tool handles the common cases; the legacy path needs a conversion step:

# pip install msoffcrypto-tool
import io
import subprocess
from pathlib import Path
import msoffcrypto

def open_any_protected(path: Path, password: str | None) -> io.BytesIO:
    with path.open("rb") as fh:
        office_file = msoffcrypto.OfficeFile(fh)
        if office_file.is_encrypted():
            if password is None:
                raise ValueError(f"{path.name} is encrypted and no password was supplied")
            office_file.load_key(password=password)
            buffer = io.BytesIO()
            office_file.decrypt(buffer)
            buffer.seek(0)
            return buffer
    if path.suffix.lower() == ".xls":                          # readable but legacy format
        out_dir = path.parent / ".converted"
        out_dir.mkdir(exist_ok=True)
        subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", str(out_dir), str(path)],
                       check=True, capture_output=True, timeout=180)
        return io.BytesIO((out_dir / (path.stem + ".xlsx")).read_bytes())
    return io.BytesIO(path.read_bytes())

LibreOffice converts legacy formats including some protected ones; it can also be given a password with --infilter="MS Excel 97:PASSWORD" when msoffcrypto cannot read the variant. The .xls reading path itself, and the xlrd errors that come with it, are covered in fix xlrd error reading .xlsx files.

Protection types and what Python can do File encryption requires the password to read and to modify, and is handled by decrypting with msoffcrypto-tool. Workbook structure protection needs no password to read; the structure flag can be cleared in a copy. Sheet protection needs no password to read and the flag can be cleared in a copy to allow edits. A read-only recommendation needs no password at all and is ignored by Python readers. Protection Password to read Password to modify Handled by File encryption yes yes msoffcrypto decrypt Workbook structure no no clear lockStructure Sheet protection no no clear ws.protection.sheet Read-only recommended no no ignored by Python

Keeping the Password Out of the Code

A password in a script, a notebook or a committed .env file is the real risk in this workflow. Read it from the environment, populated by the scheduler's secret store, and fail clearly when it is absent:

# pip install msoffcrypto-tool python-dotenv
import os
from pathlib import Path

def password_for(sender: str) -> str:
    variable = f"XLSX_PASSWORD_{sender.upper().replace('-', '_')}"
    password = os.environ.get(variable)
    if not password:
        raise SystemExit(
            f"no password configured for {sender}: set {variable} in the job's secret store"
        )
    return password

def load_statement(path: Path, sender: str):
    return read_protected_excel(path, password_for(sender), dtype={"account": "string"})

One variable per sender keeps a rotated password for one bank from breaking the others, and the error message names the exact variable to set. Log which sender a file was decrypted for, never the password itself, and rotate credentials when the person who set them up leaves — the same discipline as fix IMAP authentication failed error applies to these shared passwords.

Handling a Mixed Intake Folder

Senders are inconsistent: the same bank protects the statement on some days and not on others, and a second sender never protects anything. Make the reader handle both without configuration changes, and record which files were encrypted:

# pip install msoffcrypto-tool "pandas>=2.2"
from pathlib import Path
import msoffcrypto
import pandas as pd

def read_any(path: Path, password: str | None) -> tuple[dict[str, pd.DataFrame], bool]:
    with path.open("rb") as fh:
        encrypted = msoffcrypto.OfficeFile(fh).is_encrypted()
    if not encrypted:
        return pd.read_excel(path, sheet_name=None, dtype={"account": "string"}), False
    if password is None:
        raise ValueError(f"{path.name} is encrypted but no password is configured for its sender")
    return read_protected_excel(path, password, dtype={"account": "string"}), True

def load_folder(folder: Path, sender: str) -> pd.DataFrame:
    frames, encrypted_count = [], 0
    for path in sorted(folder.glob("*.xlsx")):
        if path.name.startswith("~$"):
            continue
        sheets, was_encrypted = read_any(path, password_for(sender))
        encrypted_count += was_encrypted
        frames.append(sheets["Transactions"].assign(source_file=path.name, encrypted=was_encrypted))
    print(f"{len(frames)} file(s) read, {encrypted_count} encrypted")
    return pd.concat(frames, ignore_index=True) if frames else pd.DataFrame()

Recording the encrypted flag per file is more useful than it looks: when a sender stops protecting files, that is worth noticing and querying, because it usually means their export process changed — and export changes tend to bring column changes with them.

Verification

Confirm the decryption produced a genuine workbook with the expected sheets and no plaintext copy left behind.

# pip install msoffcrypto-tool "pandas>=2.2" openpyxl
import io
import tempfile
from pathlib import Path
import pandas as pd

def verify_protected_read(path: Path, password: str, expected_sheets: set[str],
                          key_col: str = "account") -> None:
    buffer = decrypt_to_buffer(path, password)
    head = buffer.getvalue()[:4]
    assert head == b"PK\x03\x04", "decryption did not produce a ZIP-based workbook"
    buffer.seek(0)
    sheets = pd.read_excel(buffer, sheet_name=None, dtype={key_col: "string"})
    missing = expected_sheets - set(sheets)
    assert not missing, f"missing sheets after decryption: {sorted(missing)}"
    for name, frame in sheets.items():
        assert not frame.empty, f"sheet {name!r} decrypted but empty"
    leftovers = list(path.parent.glob(f"{path.stem}*decrypted*")) + list(Path(tempfile.gettempdir()).glob("*decrypted*.xlsx"))
    assert not leftovers, f"unprotected copies left on disk: {leftovers}"
    print(f"{path.name}: decrypted, {len(sheets)} sheet(s), no plaintext copies")

The ZIP-magic assertion is the useful one: a wrong password sometimes produces a buffer of garbage rather than an exception, depending on the encryption variant, and the check catches that before pandas reports a confusing parsing error.

FAQ

Can I brute-force a forgotten password? Modern Office encryption (AES with many key-derivation rounds) makes that impractical, and doing it to someone else's file is a different question entirely. Ask the sender to resend.

Does pd.read_excel have a password argument? No. Decrypt first and pass the buffer.

Is msoffcrypto-tool also able to encrypt? Yes — office_file.encrypt(password, outbuf) — useful when a job must send a protected workbook back, with the password shared through a separate channel.

What about protected Word and PowerPoint files? The same library handles .docx and .pptx; decrypt to a buffer and pass it to python-docx or python-pptx.

Part of Reading Excel Files with Python.

/html>