Password Protected Excel Files and Scripts That Read Them

Read password protected Excel files from a script with msoffcrypto-tool, tell file encryption from sheet protection, and keep passwords out of code and logs.

7 min read

Summarize in AI

Quick Summary

A password to open encrypts the whole workbook and needs the password to read, which msoffcrypto-tool can supply in a script. Sheet and workbook protection only block editing in the interface and can be bypassed. Keep passwords in a secret store, decrypt in memory and never log them.

Key takeaways

Excel has three different things called a password, and only the one that opens the file encrypts anything.
Sheet and workbook protection store a hash used by the interface and can be removed by editing the XML, so they prevent accidents and nothing else.
A script can decrypt an encrypted workbook with the msoffcrypto-tool package, given the password, and pass the decrypted bytes straight to the reader.
Passwords belong in a secret store or an environment variable, never in code, never on a command line and never in a log.
Decrypt in memory when you can, and delete any decrypted copy on disk as soon as you have finished with it.
Encryption protects a file at rest and in transit, so a weak password or a shared password reduces it to a deterrent.

The email says the file is password protected, and the password is in the next email. That is a ritual rather than a control, and the script that has to read the file every night still needs a way in. There are three separate things called passwords in Excel, and they behave very differently: one locks the file, and two only lock the interface. A pipeline author needs to know which is which, how to read the first from code and why not to describe the others as security. This article covers both, and fits with the broader risks in Excel security problems developers overlook.

Three passwords, one real

Password to open (File, Info, Protect Workbook, Encrypt with Password) encrypts the whole file. Without the password you cannot read the content. Excel 2007 and later use AES, and Excel 2013 and later use the "agile" encryption scheme with AES-256 and a key derived by many rounds of hashing, as described in Microsoft's open specifications for Office document cryptography. A good password makes this a real barrier.

Protect sheet stops editing of locked cells through the interface. The password is stored as a short hash in the sheet XML, <sheetProtection ... password="..."/>, and the data in the sheet is not encrypted. Removing the element, or replacing the hash, removes the protection, and tools exist that do it in seconds. It is a guard against accidental edits and nothing more.

Protect workbook structure stops people adding, deleting or unhiding sheets, and works the same way: a flag and a hash in workbook.xml.

A small test makes the difference visible. Rename the xlsx to .zip and open it. If you can see xl/workbook.xml and read the sheets, the file is not encrypted, whatever the sheet protection says. An encrypted workbook is not a zip at all; it is a Compound File Binary container holding an EncryptedPackage stream, and a zip tool cannot open it.

Reading an encrypted workbook

The msoffcrypto-tool package decrypts Office files given the password. Decrypt into memory and hand the bytes to the reader.

Python

import io
import os
import msoffcrypto
import pandas as pd

password = os.environ["REPORT_PASSWORD"]

decrypted = io.BytesIO()
with open("report_encrypted.xlsx", "rb") as f:
    office = msoffcrypto.OfficeFile(f)
    office.load_key(password=password)
    office.decrypt(decrypted)

decrypted.seek(0)
df = pd.read_excel(decrypted, sheet_name="Data", engine="openpyxl")

The decrypted bytes never touch the disk, so there is no plaintext copy to clean up. A wrong password raises an exception, and you should catch it and report "wrong password or corrupt file", with no further detail. If the file is not encrypted, OfficeFile.is_encrypted() returns false, which lets a script handle both cases:

Python

with open(path, "rb") as f:
    office = msoffcrypto.OfficeFile(f)
    if office.is_encrypted():
        office.load_key(password=password)
        buf = io.BytesIO()
        office.decrypt(buf)
        buf.seek(0)
        source = buf
    else:
        source = path

The package works on .xlsx and .xls, and on other Office formats. Check which encryption schemes the installed version supports before promising a partner that every file will open.

When you see BadZipFile from openpyxl on a file that opens fine in Excel, an encryption wrapper is a common cause (fixing BadZipFile when reading xlsx). The file is a CFB container with a password, not a zip.

Keep the password out of everything

The password is a credential, and the usual rules apply.

Never in source code or in a repository, including the one for private projects.
Never on the command line, where it appears in the process list and the shell history.
Never in a log. A line such as opening report with password hunter2 ends up in a log aggregator that many people can read.
Load it from a secret store or, at a minimum, from an environment variable set by the scheduler, with access limited to the service account that runs the job.

Treat the password like an API key: rotate it when people leave and when it is shared more widely than intended. A password that three vendors and a dozen staff all know has the strength of the weakest of them. If the sender controls it, ask for a separate password per recipient, or use a better channel.

When a person sets a password in Excel and emails it, the weak link is not the cipher. It is the password's strength, where it is stored and how it is passed on. Ask for passphrases of reasonable length, and send them over a different channel from the file.

Writing an encrypted workbook

If your script produces a workbook for someone outside, you can encrypt it. msoffcrypto-tool can encrypt as well as decrypt in recent versions:

Python

import msoffcrypto

with open("report.xlsx", "rb") as plain, open("report_encrypted.xlsx", "wb") as enc:
    f = msoffcrypto.OfficeFile(plain)
    f.encrypt(os.environ["SHARE_PASSWORD"], enc)

Encryption protects the file in transit and at rest, and it does not remove hidden data inside, so clean the workbook first (hidden sheets and metadata leaking out of shared workbooks). Test that the recipient's version of Excel opens the result. Some tools, including preview panes and mail scanners, cannot read an encrypted file at all, which can hide malware from a scanner as well; many organizations treat encrypted attachments with suspicion for exactly that reason.

For a generated report, consider whether a link to an authenticated download beats a password protected file. Access control on the server gives you logs, revocation and expiry, and a password on a file gives you none of those.

Cracking and recovery

People forget passwords. The honest advice is that a modern, strong file password cannot be recovered, and a weak one can be guessed offline, because the file contains everything an attacker needs to test guesses. Recovery tools run guesses against the key derivation, and speed depends on the Excel version and the password length. Legacy .xls files protected by older schemes are much weaker.

Do not rely on being able to recover a lost password, and do not treat recovery tools as a way into files you have no right to open. If you own the file and lost the password, check backups for an unencrypted copy first.

Sheet protection in scripts

You will meet files with sheet protection that block nothing for a reader. openpyxl reads protected sheets without a password, because the protection only affects editing. If you need to write into a protected sheet in a template, you will have to unprotect it, either by knowing the password or by clearing the flag in your own copy:

Python

from openpyxl import load_workbook

wb = load_workbook("template.xlsx")
ws = wb["Input"]
ws.protection.sheet = False

That is acceptable for a template you own. Do not do it to someone else's file in order to alter content they meant to lock. See protecting a generated workbook from accidental edits for how to use protection well on files you produce, and locking down shared input sheets for the input case.

What to put in the handling notes

Write down, for each recurring encrypted file: who sends it, how the password is delivered, where it is stored, who can read it, when it is rotated and what the script does on failure. When the sender changes the password without telling you, the job fails with a decryption error, and the notes tell the person on call whom to ask. The Microsoft documentation on encrypting workbooks describes the feature from the user's side, and the msoffcrypto-tool project page lists the supported formats.

When a password is the wrong control

A password is the wrong control when many people need access and you need to know who opened what: use access control on the system holding the file. It is wrong when the content is regulated and the password travels by email beside the file: use a managed transfer service. It is wrong as protection for a sheet inside a workbook that everyone can open, since sheet protection is not encryption. And it is wrong when your pipeline needs to scan uploads for threats, because an encrypted file defeats the scanner. In that case ask senders for unencrypted files over an authenticated, encrypted channel, and let transport security do the protecting.

Also inSecurity

Taggedhow-to security