Openpyxl password protect excel file python

Question

I already referred these pposts here - here, here and here. Please don't mark it as duplicate

am trying to do some operations in my excel and finally save and protect them using a password. My openpyxl version is 3.0.9

So, I tried the below

for search, v in merge_df.groupby(['Country']):
    writer = pd.ExcelWriter(f"BC_{Country}.xlsx", engine='xlsxwriter')
    v.to_excel(writer,columns=col_list,sheet_name=f'BC_{Country}',index=False, startrow = 1)
    wb1 = load_workbook(filename = f"BC_{Country}.xlsx")
    sheet_to = wb1.worksheets[0]
    wb1.security.workbookPassword = "test"
    wb1.save(f"BC_{Country}.xlsx")

But I get the below error

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

How can I protect my excel sheet with a password?

So, user can only open with a password

I don't believe there is a way to do it in penpyxl. Assuming you want to LOCK the file (requires password to open) and not prevent users from updating the data/format. The closest I could find was a way of calling VB Script from python [here](https://stackoverflow.com/questions/36122496/password-protecting-excel-file-using-python) — Redox, Jul 20 '22 at 12:42
@Redox - Is it not possible to protect the worksheet as well in excel (which prevents users from making any changes to the columns, cells and rows etc) in that specific sheet? — The Great, Jul 20 '22 at 13:03
So by asking this question, I'm assuming you've tried all the solutions in the 3 links specified at the top of your post? Did something occur while trying them? Also is there a potential explination for why `wb.security` is `None`? — Anony Mous, Jul 20 '22 at 13:21
Maybe refer [here](https://stackoverflow.com/questions/36122496/password-protecting-excel-file-using-python) and use VBA — Anony Mous, Jul 20 '22 at 13:29
Are you trying to protect the workbook or individual worksheets? — Charlie Clark, Jul 20 '22 at 15:10
i initially wanted to protect workbook but finally settled for worksheet based on the answer provided — The Great, Jul 20 '22 at 15:11

score 1 · Accepted Answer · answered Jul 20 '22 at 13:40

1

Please refer to the documentation here. Workbooks can be protected, but this is from specific things like " ..... viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets...".

But, I think you want to allow your users to open and view the file, but not update/overwrite the information. So, you can use worksheet protection to lock it. The code below will do this.... open a new file, write something, lock the sheet with a password and save. To unlock and edit, user will need to go to the excel file Review tab > Unprotect Sheet and provide the password. Hope this works...

wb=openpyxl.Workbook()
sheet1=wb.active
sheet1.cell(3,3).value = "Though shalt not overwrite"
sheet1.protection.sheet = True
sheet1.protection.password = 'test'
wb.save('Book1.xlsx')

Open excel error when you try to double click/type something

answered Jul 20 '22 at 13:40

Redox

9,321
5
9
26

nice. it works and serves my purpose. but how do protect for certain operations like in excel. when I click "protect workbook" in excel it shows lot of checkboxes where I select/tick what I want. whatever you did ticks all of those checkboxes? – The Great Jul 20 '22 at 14:20
oh i see, is it just `sheet1.protection.selectlockedcells = True` and `sheet1.protection.autofilter = True` etc? – The Great Jul 20 '22 at 14:25
Yes, those seem to work. Although I am not sure of all the options. Don't see any documentation. – Redox Jul 20 '22 at 14:34
But I don't see some options not working correctly. Meaning, autofilter by default is set to True but when I open the excel (and check review unprotect, it is not checked/ticked). – The Great Jul 20 '22 at 14:47
Yes, I know what you are saying. In case we lock it directly in Excel and chose a particular set of options, then unlock and try lock it again, same options show up. But here, although locked, when we unlock and click on protect, the same options are not ticked. But, I did see that the autofilter enable/disable could be controlled from openpyxl code, which I think is good. – Redox Jul 20 '22 at 15:17

Openpyxl password protect excel file python

1 Answers1