7

I try to protect an Excel workbook with openpyxl.

So far I had a look into the different, potentially relevant classes but I can't find a set_password method like the one for worksheets.

There happens to be the workbook.protection module that I tried my luck with. My code, boiled down to the absolute relevant minimum is as follows:

from openpyxl import Workbook
from openpyxl.workbook.protection import WorkbookProtection

wb = Workbook()
wb.security = WorkbookProtection(workbookPassword='0000', revisionsPassword = '0000', lockWindows = True, lockStructure = True, lockRevision = True)
wb.create_sheet("testSheet")
wb.save("test.xlsx")

I don't get any errors but no protection either. Any help would be dearly appreciated.

Thomas
  • 299
  • 4
  • 12
  • the only protection i could found is on worksheet, with `wb["testSheet"].protection.set_password('test')`, check this [post](https://stackoverflow.com/questions/36122496/password-protecting-excel-file-using-python). – PRMoureu Jul 20 '17 at 17:36
  • would using `load_workbook(file, read_only=True).get_sheet_names()` to create a list, and then loop through protecting each sheet work? – MattR Jul 20 '17 at 20:08
  • @PRMoureu yes, as I said, I am aware of this but can't find a similar method for workbooks. – Thomas Jul 20 '17 at 22:45
  • @MattR protecting each sheet is something different than protecting the workbook. In a protected sheet the cells are protected from editing but the worksheet itself can still be edited. In a protected workbook the sheet could not be edited (eg renamed). – Thomas Jul 20 '17 at 22:45
  • from the source, it seems the worksheet is not protected by default, have you try to override this parameter by creating a protection like you did with Workbook and give `sheet=False` or even other elements? – PRMoureu Jul 21 '17 at 04:41
  • 1
    This doesn't currently work in Openpyxl (v2.4.8) - the password is not written to the output file. I'm working on a PR for this (https://bitbucket.org/openpyxl/openpyxl/pull-requests/180/read-and-write-workbook-protection/diff), so hopefully it will be included in future releases. – gar Jul 27 '17 at 14:24
  • @gar thank you for your response. If you submit it as an answer I will accept it. – Thomas Aug 01 '17 at 08:19
  • It appears you were satisfied with the answer from @gar but in case other visitors to this question are actually looking for a way to prevent the workbook from being opened at all without the password, [this other question](https://stackoverflow.com/questions/50245208/add-a-password-unattended-to-existing-xlsx-without-windows-exclusive-tools) is more relevant. – John Y May 10 '18 at 23:45
  • Any idea when it comes to locking all columns except one , from being copied, as opposed to just locking at workbook level? I'm able to lock the worksheet but still one can Select all cells, copy and paste values in a new sheet. That exposes hidden columns etc. – Hummer Apr 30 '21 at 00:51

1 Answers1

3

This doesn't currently work in Openpyxl (v2.4.8) - the password is not written to the output file. There is an open PR for this (https://bitbucket.org/openpyxl/openpyxl/pull-requests/180/read-and-write-workbook-protection/diff), so hopefully it will be included in future releases.

gar
  • 14,152
  • 5
  • 30
  • 31