Protecting Excel Files with Python
I havent found much of the topic of creating a password protected Excel file using Python.
In Openpyxl, I found a module SheetProtection
using:
from openpyxl.worksheet import SheetProtection
However, the problem is that I'm not sure how to use it. It's not an attribute Workbook
or Worksheet
, so I can't just do this:
wb = Workbook() ws = wb.worksheets[0] ws_encrypted = ws.SheetProtection() ws_encrypted.password = 'test' ...
Does anyone know if such a request is possible with Python? Thank!
source to share
Looking at the docs for openpyxl
, I noticed that there is indeed a class openpyxl.worksheet.SheetProtection
. However, it looks like it is already part of the worksheet object:
>>> wb = Workbook()
>>> ws = wb.worksheets[0]
>>> ws.protection
<openpyxl.worksheet.protection.SheetProtection object at 0xM3M0RY>
The test dir(ws.protection)
shows that there is a method set_password
that, when called with a string argument, actually points to the set flag.
>>> ws.protection.set_password('test')
>>> wb.save('random.xlsx')
I opened random.xlsx
in LibreOffice and the sheet was really secure. However, I only needed to toggle the option to disable protection and not enter the password, so I could do that for more ...
source to share
Here is the workaround that I am using. It generates a VBS script and calls it from a Python script.
def set_password(excel_file_path, pw):
from pathlib import Path
excel_file_path = Path(excel_file_path)
vbs_script = \
f"""' Save with password required upon opening
Set excel_object = CreateObject("Excel.Application")
Set workbook = excel_object.Workbooks.Open("{excel_file_path}")
excel_object.DisplayAlerts = False
excel_object.Visible = False
workbook.SaveAs "{excel_file_path}",, "{pw}"
excel_object.Application.Quit
"""
vbs_script_path = excel_file_path.parent.joinpath("set_pw.vbs")
with open(vbs_script_path, "w") as file:
file.write(vbs_script)
subprocess.call(['cscript.exe', str(vbs_script_path)])
# remove the VBS script
vbs_script_path.unlink()
return None
source to share