CODEWITHSUNDEEP
pythonopenpyxl
dyao
dyao

Reputation: 1011

Password Protecting Excel file using Python

I havent found much of the topic of creating a password protected Excel file using Python.

In Openpyxl, I did find a SheetProtection module using:

from openpyxl.worksheet import SheetProtection

However, the problem is I'm not sure how to use it. It's not an attribute of 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 even possible with Python? Thanks!

Upvotes: 6

Views: 37607

Answers (5)

James McIntyre
James McIntyre

Reputation: 116

Here is a rework of Michał Zawadzki's solution that doesn't require creating and executing a separate vbs file:

def PassProtect(Path, Pass):

    from win32com.client.gencache import EnsureDispatch
    
    xlApp = EnsureDispatch("Excel.Application")
    
    xlwb = xlApp.Workbooks.Open(Path)
    
    xlApp.DisplayAlerts = False
    xlwb.Visible = False
    
    xlwb.SaveAs(Path, Password = Pass)
    
    xlwb.Close()
    
    xlApp.Quit()
    
PassProtect(FullExcelWorkbookPathGoesHere, DesiredPasswordGoesHere)

If you wanted to choose a file name that's in your project's folder, you could also do:

from os.path import abspath

PassProtect(abspath(FileNameInsideProjectFolderGoesHere), DesiredPasswordGoesHere)

Upvotes: 4

liu quan
liu quan

Reputation: 41

You can use python win32com to save an excel file with a password.

import win32com.client as win32

excel = win32.gencache.EnsureDispatch('Excel.Application')
#Before saving the file set DisplayAlerts to False to suppress the warning dialog:
excel.DisplayAlerts = False
wb = excel.Workbooks.Open(your_file_name)
# refer https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb214129(v=office.12)?redirectedfrom=MSDN
# FileFormat = 51 is for .xlsx extension
wb.SaveAs(your_file_name, 51, 'your password')                                               
wb.Close() 
excel.Application.Quit()

Upvotes: 4

Michał Zawadzki
Michał Zawadzki

Reputation: 752

Here's a workaround I use. It generates a VBS script and calls it from within your 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
    """

    # write
    vbs_script_path = excel_file_path.parent.joinpath("set_pw.vbs")
    with open(vbs_script_path, "w") as file:
        file.write(vbs_script)

    #execute
    subprocess.call(['cscript.exe', str(vbs_script_path)])

    # remove
    vbs_script_path.unlink()

    return None

Upvotes: 10

Charlie Clark
Charlie Clark

Reputation: 19507

openpyxl is unlikely ever to provide workbook encryption. However, you can add this yourself because Excel files (xlsx format version >= 2010) are zip-archives: create a file in openpyxl and add a password to it using standard utilities.

Upvotes: 3

icedwater
icedwater

Reputation: 4887

Looking at the docs for openpyxl, I noticed there is indeed a openpyxl.worksheet.SheetProtection class. However, it seems to be already part of a worksheet object:

>>> wb = Workbook()
>>> ws = wb.worksheets[0]
>>> ws.protection
<openpyxl.worksheet.protection.SheetProtection object at 0xM3M0RY>

Checking dir(ws.protection) shows there is a method set_password that when called with a string argument does indeed seem to set a protected flag.

>>> ws.protection.set_password('test')
>>> wb.save('random.xlsx')

I opened random.xlsx in LibreOffice and the sheet was indeed protected. However, I only needed to toggle an option to turn off protection, and not enter any password, so I might be doing it wrong still...

Upvotes: 7

Related Questions