Edit read-only password protected excel with Python

Question

I am trying to find a way to edit a password protected excel with Python. Password is not required to open the excel, it is only required if you want to edit it:

Is it possible to access this using password and edit it? Would be perfect if it would be possible to edit it using Openpyxl. I have already tried to use msoffcrypto:

decrypted = io.BytesIO()

with open("encrypted.xlsx", "rb") as f:
    file = msoffcrypto.OfficeFile(f)
    file.load_key(password="Passw0rd")  # Use password
    file.decrypt(decrypted)

df = pd.read_excel(decrypted)

But I think this works only when you can access the file only with password (the whole file is password encrypted, not the edit part.

With Xlwings yes, see here for the options https://stackoverflow.com/questions/71267540/xlwings-open-password-protected-worksheet-in-xlsx — moken, Jan 10 '23 at 23:27
@hc_dev - yes, I receive following error: "Unencrypted document or unsupported file format". — Kirilas, Jan 18 '23 at 06:27

score 0 · Answer 1 · answered Jan 11 '23 at 07:31

If the Excel workbook has been saved with a "Password to modify" like in Office 2011 then you you usually need to perform following steps in Excel to save an unprotected version:

enter the password
open the workbook (still it is write-protected)
save as (unprotected)

In Python

You can decrypt it using a password and then pass the decrypted byte stream - as file-like object - to the opening function, be it pandas.read_excel() or openpyxl.load_workbook():

import io

import msoffcrypto
import openpyxl


decrypted_workbook = io.BytesIO()

with open('encrypted.xlsx', 'rb') as file:
    office_file = msoffcrypto.OfficeFile(file)
    office_file.load_key(password='passw0rd')
    office_file.decrypt(decrypted_workbook)

# `filename` can also be a file-like object.
workbook = openpyxl.load_workbook(filename=decrypted_workbook)

Edit read-only password protected excel with Python

1 Answers1

In Python