3

I'm trying to automate Excel reports, and I'd prefer users didn't try to rename or reorder the worksheets. While I've had no problems protecting individual cells using xlsxwriter, I've failed to see an option to protect the workbook itself. I'm looking to openpyxl, but the tutorial does not seem to have any effect.

Edit: I'm now using this block of code, but does neither produce an error or protect my workbooks.

from openpyxl import load_workbook
from openpyxl.workbook.protection import WorkbookProtection

workbook = load_workbook(filepath, read_only=False, keep_vba=True)
workbook.security = WorkbookProtection(workbookPassword = 'secret-password', lockStructure = True)
workbook.save(filepath)

By the way, I am dealing with .xlsm files. If there are any solutions or points that I've missed, please let me know.

upset undergrad
  • 41
  • 1
  • 8

3 Answers3

7

From this code:

from openpyxl.workbook.protection import WorkbookProtection

myWorkbook.security = WorkbookProtection(workBookPassword = 'super-secret-password', lockStructure = True)
myWorkbook.save(filepath)

Change:

WorkbookProtection(workBookPassword = 'super-secret-password', lockStructure = True)

to:

WorkbookProtection(workbookPassword = 'super-secret-password', lockStructure = True)

workBookPassword should be workbookPassword

Tested on Python32 3.8 and OpenPyXL version 3.0.2

ConSod
  • 743
  • 8
  • 18
  • dont work for me.. Have you tried a file that already existed before? – GiovaniSalazar Dec 12 '19 at 20:44
  • I was using openpyxl 2.6.3 and upgraded to 3.0.2 and made the correction from workBookPassword to workbookPassword but, I'm still not producing any errors or success. – upset undergrad Dec 12 '19 at 21:24
  • @ConSod actually, did you test with .xlsx files or .xlsm files? I'm using .xlsm files and I have a feeling that might be the reason. – upset undergrad Dec 12 '19 at 21:41
  • Strange, I tested both xlsx and xlsm files. Have you tried running the code twice? – ConSod Dec 12 '19 at 21:42
  • Yes, but I just noticed the worksheets I protected using xlsxwriter aren't protected anymore. – upset undergrad Dec 12 '19 at 21:49
  • Could you try applying an empty WorkbookProtection() first, and then set the password and lock structure. – ConSod Dec 12 '19 at 21:51
  • So the worksheets were protected by xlsxwriter, but not the workbook? I tried with a workbook that was not protected beforehand. – ConSod Dec 12 '19 at 21:57
  • I got it to work by protecting the sheets along with the workbook using openpyxl instead of xlsxwriter. I'm thinking the two of them might've caused some disagreement...I wanted to use xlsxwriter to protect the workbook initially, but I couldn't find any documentation on it, previously. – upset undergrad Dec 12 '19 at 22:08
  • 1
    "I wanted to use xlsxwriter to protect the workbook initially, but I couldn't find any documentation on it". @upset_undergrad XlsxWriter doesn't support Workbook level protection. – jmcnamara Dec 13 '19 at 14:37
0

Xlsxwriter has the option to protect the workbook with the command worksheet.protect() (have a look at the documentation: https://xlsxwriter.readthedocs.io/worksheet.html )

However take into consideration this:

Worksheet level passwords in Excel offer very weak protection. They do not encrypt your data and are very easy to deactivate. Full workbook encryption is not supported by XlsxWriter since it requires a completely different file format and would take several man months to implement.

Dimitris Thomas
  • 1,363
  • 9
  • 14
  • 1
    The question is about protecting **existing workbooks**. xlsxwriter cannot read existing workbooks and only does worksheet protection. – Charlie Clark Dec 14 '19 at 16:50
0

Try using xlwings

import xlwings as xw  

wb = xw.Book(r'<path_to_.xlsx file>')

wb.save(password='<your_password>', path=r'<path_to_save_.xlsx file>')
Irzelindo S
  • 51
  • 1
  • 3