CODEWITHSUNDEEP
pythonexcelopenpyxl
Omar Najeed
Omar Najeed

Reputation: 81

Password Protecting an Excel file using Python

I am trying to password protect an entire Excel file (same functionality as File > Protect Workbook > Encrypt with Password) using Python.

I have come across openpyxl and the protection features it offers (https://openpyxl.readthedocs.io/en/stable/protection.html) seems to fulfill this need. I have the following code:

from openpyxl import Workbook
from openpyxl import load_workbook

test_spreadsheet = "test.xlsx"
wb = load_workbook(test_spreadsheet)
wb.security.workbookPassword = "password"

However, I am getting the following error:

AttributeError: 'NoneType' object has no attribute 'workbookPassword'

Does anyone have an idea of what is causing this AttributeError? I have printed the sheetnames from wb and that is correctly printing the tabs in my Excel document.

Upvotes: 8

Views: 5387

Answers (2)

tehhowch
tehhowch

Reputation: 9872

For a default-constructed workbook, the security property is initialized by default:

self.security = DocumentSecurity()

Reference: 1

However, workbooks constructed by reading a workbook are not just default-constructed; they are also manipulated by a Parser object:

wb_part = _find_workbook_part(self.package)
self.parser = WorkbookParser(self.archive, wb_part.PartName[1:], keep_links=self.keep_links)
self.parser.parse()
wb = self.parser.wb
...
self.wb = wb

Reference: 2

Parser.init does default-construct a Workbook, but then overrides specific properties with those of the source document:

self.wb.security = package.workbookProtection

Reference: 3

This means that for files that had no security settings, the imported workbook object has a value of None for its security property (and thus your error, as None clearly has no attribute workbookPassword).

Your solution is then to create a default WorkbookProtection(), assign it to the workbook, and then set the workbook password.

Upvotes: 0

moken
moken

Reputation: 6639

As the Openpyxl document states "Workbook Protection To prevent other users from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets, you can protect the structure of your workbook with a password." It's not the same as File > Protect Workbook > Encrypt with Password. Also does not work with an existing workbook.

If you run the following code, and open the newly created book 'test.xlsx' you should see it will open without a password however you cannot do any of those actions in italics above unless you go to the 'changes' toolbar and click 'Protect Workbook' then enter the password.

from openpyxl import Workbook
from openpyxl import load_workbook

test_spreadsheet = "test.xlsx"

wb = Workbook()
wb.security.workbookPassword = 'password'
wb.security.lockStructure = True
wb.save(test_spreadsheet)

I don't believe openpyxl supports the option you want.

Upvotes: 0

Related Questions