Password Protect an Excel File with Python

Sheet protection stops a reader typing in the wrong cell. It does not stop them reading anything — every value in a "protected" workbook is plainly visible, and any library will hand them over. When the contents genuinely should not be seen, you need file-level encryption: the workbook wrapped in an encrypted container that cannot be opened at all without the password. Neither openpyxl nor xlsxwriter can write one, so this guide uses msoffcrypto-tool, covering both directions and the operational details that decide whether the protection is real. It is the security layer of Protecting and Sharing Excel Workbooks.

Prerequisites

pip install pandas xlsxwriter msoffcrypto-tool

And a password that does not live in your code. Read it from the environment:

export REPORT_PASSWORD='...'

import os

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

Failing loudly at start-up beats discovering the variable was missing after the job wrote an unencrypted payroll file.

Step 1 — Encrypt a workbook you generate

Build the workbook in memory, then hand the bytes to msoffcrypto. The unencrypted version never reaches the filesystem, so there is no window in which a plain copy exists on disk:

import io
import os
import msoffcrypto
import pandas as pd

def write_encrypted(df, path, password, sheet_name="Summary"):
    """Write a DataFrame to a password-encrypted .xlsx."""
    if not password:
        raise ValueError("refusing to write with an empty password")

    plain = io.BytesIO()
    with pd.ExcelWriter(plain, engine="xlsxwriter") as writer:
        df.to_excel(writer, sheet_name=sheet_name, index=False)
    plain.seek(0)

    office = msoffcrypto.OfficeFile(plain)
    with open(path, "wb") as out:
        office.encrypt(password, out)
    return path

payroll = pd.DataFrame({
    "employee": ["A. Chen", "B. Ortiz", "C. Novak"],
    "salary": [72000, 68500, 81250],
})
write_encrypted(payroll, "payroll.xlsx", os.environ["REPORT_PASSWORD"])
print("wrote encrypted payroll.xlsx")

Confirm it worked rather than assuming. An encrypted workbook is an OLE2 container, not a zip, so the first four bytes tell you immediately:

with open("payroll.xlsx", "rb") as fh:
    head = fh.read(4)

assert head == b"\xd0\xcf\x11\xe0", "file is NOT encrypted — it is still a plain zip"
print("confirmed encrypted")

That assertion is worth keeping in the job. An encryption step that silently no-ops is the worst possible failure, because everything downstream looks normal.

Step 2 — Encrypt an existing file

When the workbook already exists — produced by an earlier stage, or by a template fill — encrypt it in place from disk:

import io
import msoffcrypto
from pathlib import Path

def encrypt_file(src, dest, password):
    """Encrypt an existing .xlsx, leaving the source untouched."""
    src, dest = Path(src), Path(dest)
    data = io.BytesIO(src.read_bytes())

    office = msoffcrypto.OfficeFile(data)
    if office.is_encrypted():
        raise ValueError(f"{src.name} is already encrypted")

    with dest.open("wb") as out:
        office.encrypt(password, out)
    return dest

encrypt_file("report.xlsx", "report_secure.xlsx", os.environ["REPORT_PASSWORD"])

Write to a new path rather than over the source. If encryption fails halfway, an in-place write leaves you with a truncated file and no original — and for a monthly report that is often the only copy.

Step 3 — Read an encrypted file back

The reverse direction matters just as much: banks, payroll bureaux and insurers routinely send encrypted workbooks, and an ingest job has to open them.

import io
import msoffcrypto
import pandas as pd

def read_encrypted(path, password, **read_kwargs):
    """Decrypt an .xlsx in memory and read it with pandas."""
    decrypted = io.BytesIO()
    with open(path, "rb") as fh:
        office = msoffcrypto.OfficeFile(fh)
        office.load_key(password=password)
        office.decrypt(decrypted)

    decrypted.seek(0)
    return pd.read_excel(decrypted, **read_kwargs)

df = read_encrypted("payroll.xlsx", os.environ["REPORT_PASSWORD"])
print(df.head())

The decrypted bytes stay in a BytesIO, so no plaintext copy is written to disk for another process to find. That in-memory handoff is the same technique described in the formats topic for reading workbooks from HTTP responses.

A wrong password raises, and the message is not always obvious. Catch it and say something useful:

import msoffcrypto

def read_encrypted_safely(path, password):
    try:
        return read_encrypted(path, password)
    except msoffcrypto.exceptions.InvalidKeyError:
        raise RuntimeError(
            f"Wrong password for {path}. Check REPORT_PASSWORD, and note that "
            "some providers rotate it monthly."
        ) from None
    except msoffcrypto.exceptions.FileFormatError:
        raise RuntimeError(
            f"{path} is not an encrypted Office file — it may already be plain "
            "or may be a different format entirely."
        ) from None

Never retry on InvalidKeyError. A wrong password will be wrong on the second attempt too, and a retry loop only wastes time — the same reasoning applied to authentication failures in retrying a failed Excel report job.

Step 4 — Handle a mixed inbox

In practice a folder of incoming files contains both encrypted and plain workbooks, and you cannot tell from the name. Detect and branch:

import io
import os
import shutil
from pathlib import Path
import msoffcrypto
import pandas as pd

OLE2 = b"\xd0\xcf\x11\xe0"

def read_maybe_encrypted(path, password=None):
    """Read an .xlsx whether or not it is encrypted."""
    path = Path(path)
    head = path.open("rb").read(4)

    if head != OLE2:
        return pd.read_excel(path)          # ordinary zip-based workbook

    if not password:
        raise RuntimeError(f"{path.name} is encrypted but no password was given")

    buffer = io.BytesIO()
    with path.open("rb") as fh:
        office = msoffcrypto.OfficeFile(fh)
        office.load_key(password=password)
        office.decrypt(buffer)
    buffer.seek(0)
    return pd.read_excel(buffer)

quarantine = Path("quarantine")
quarantine.mkdir(exist_ok=True)

for src in sorted(Path("inbox").glob("*.xlsx")):
    try:
        frame = read_maybe_encrypted(src, os.environ.get("REPORT_PASSWORD"))
        print(f"{src.name:<32} {frame.shape[0]:>6} rows")
    except Exception as exc:
        shutil.move(str(src), quarantine / src.name)
        print(f"{src.name:<32} quarantined: {exc}")

Quarantining rather than raising keeps one bad file from costing you the other forty. The sniffing technique generalises to every format, as described in handling Excel file formats and conversions.

Common pitfalls and fixes

Performance and scale notes

Encryption cost is dominated by key derivation, not by the data. The agile scheme runs many hashing rounds by design — making a guessed password expensive to test — and that fixed cost lands on every call, so encrypting fifty small reports costs roughly fifty times as much as encrypting one, regardless of how small they are.

For a fan-out job producing one file per recipient, that is worth measuring:

import io, os, time
import msoffcrypto
import pandas as pd

df = pd.DataFrame({"region": ["North"], "revenue": [159.92]})
password = os.environ["REPORT_PASSWORD"]

start = time.perf_counter()
for i in range(10):
    buf = io.BytesIO()
    with pd.ExcelWriter(buf, engine="xlsxwriter") as writer:
        df.to_excel(writer, index=False)
    buf.seek(0)
    with open(f"out_{i}.xlsx", "wb") as fh:
        msoffcrypto.OfficeFile(buf).encrypt(password, fh)
print(f"{(time.perf_counter() - start) / 10:.2f}s per file")

Because each file is independent, the work parallelises cleanly across processes — the same shape as the per-region loop in generating one Excel report per region:

from concurrent.futures import ProcessPoolExecutor

def build_one(region):
    frame = load_region(region)                     # your data step
    return write_encrypted(frame, f"{region}.xlsx", os.environ["REPORT_PASSWORD"])

if __name__ == "__main__":
    with ProcessPoolExecutor(max_workers=4) as pool:
        for path in pool.map(build_one, ["north", "south", "west", "east"]):
            print("wrote", path)

Two memory notes. Encryption holds the whole workbook in memory twice — once plain, once encrypted — so a 200 MB report needs headroom for both. And decryption cannot stream: office.decrypt writes the entire plaintext to the target buffer before pandas reads a byte, so the streaming techniques in reading large Excel files in chunks apply only after the decryption completes. For very large encrypted inputs, decrypt to a temporary file on a disk you control rather than into RAM.

Conclusion

Real password protection for an .xlsx means encrypting the container, and that is a job for msoffcrypto-tool rather than openpyxl. Build the workbook in a BytesIO, encrypt straight from memory so no plaintext copy ever lands on disk, and assert that the output really starts with the OLE2 signature. On the way in, sniff the leading bytes to tell encrypted files from plain ones, quarantine what will not open, and never retry a wrong password. Then keep the secret in the environment, and send it to recipients by a channel other than the one carrying the file.

Frequently asked questions

Can openpyxl set a file open password? No. openpyxl writes sheet and workbook protection, which only restricts editing once the file is open. Encrypting the container needs msoffcrypto-tool, or a headless LibreOffice save with a password.

What encryption does an .xlsx actually use? Modern Office files use the agile encryption scheme built on AES with a key derived from the password by repeated SHA-512 hashing. It is genuine encryption, so the strength rests almost entirely on how good the password is.

How do I tell whether a file is encrypted before opening it?msoffcrypto's is_encrypted method answers it, and the cheap structural check is that an encrypted workbook is an OLE2 container rather than a zip — its first bytes are D0 CF 11 E0 rather than PK.

Can I remove the password from a file I have the password for? Yes. Decrypt it to a buffer with load_key and decrypt, then write those bytes out as a normal .xlsx. That is the standard first step when ingesting encrypted files from a provider.

Where should the password live in a scheduled job? In an environment variable injected by the scheduler, or in a secrets manager the job reads at start-up. Never in the script, and never in a config file committed to the repository.

Related

• Up to the parent: Protecting and Sharing Excel Workbooks — how encryption compares with the other protection layers.
• Lock Cells and Protect a Sheet with openpyxl — the interface guard that encryption complements.
• Handling Excel File Formats and Conversions — the byte-sniffing technique used to detect encrypted files.
• Keep Excel Report Settings in a Config File — configuration without secrets in it.
• Emailing Excel Reports with smtplib — delivering the encrypted file to its recipients.