Python Remove Excel Protection

Last Updated on February 26, 2022 by Jay

This short tutorial will show you how to protect & unprotect Excel sheets and workbooks using Python. By the end of the tutorial, you’ll also learn that Excel password protection is a joke and not meant for file security, since the file content can be unlocked easily without a password!

Library

We are going to use openpyxl for this tutorial. You can use pip to install this library on your computer. If you already have pandas, chances are that you already have openpyxl because it’s one of the libraries that pandas uses for reading Excel files.

openpyxl can read existing Excel files, or create new ones.

pip install openpyxl

Ways To Protect Excel Files

There are three ways to protect our precious Excel files. Each has a different purpose and we’ll discuss them briefly.

Password encrypted – This is probably the most strict protection level. Without the password, we won’t be able to open the file. Usually people use this to ensure data don’t leak accidentally. This tutorial provides a detailed guide on how to remove password on a password-encrypted file.
Protect Workbook – The structure of the Workbook is protected – we can’t add, remove, hide or unhide sheets. A password is optional for this protection. Usually we use this approach when we want to hide certain sheets from users.
Protect Sheet – The content on individual Sheet is protected – we can’t edit anything on the protect sheet (it depends on the setting, but this is usually the case). A password is optional for this protection. Use this protection when we try to prevent others from modifying contents.

Protect An Excel Workbook Using Python

As you can see in the screenshot above. Once we protect the Workbook, we won’t be able to add, delete, hide or unhide the sheets.

The openpyxl.workbook.protection.WorkbookProtection class can set passwords and enable worksheet protection. Note the workbookPassword is optional, we can protect a worksheet without a password. Be aware in the below code example I overwrote the existing file. If you want to keep the original file, just change the file path inside the wb.save() to save to a new location.

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


wb = load_workbook(r'C:\Users\jay\Desktop\PythonInOffice\excel_protection\eg.xlsx')
wb.security = WorkbookProtection(workbookPassword = '1234', lockStructure = True)
wb.save(r'C:\Users\jay\Desktop\PythonInOffice\excel_protection\eg.xlsx')

Remove Excel Workbook Protection Password Using Python

DO NOT assume that your Excel is 100% safe even if you use a password to protect it! The Excel specification states: “Worksheet or workbook element protection should not be confused with file security. It is meant to make your workbook safe from unintentional modification, and cannot protect it from malicious modification.”

Let’s use openpyxl to load the previous protected Excel file. Now, we can inspect its security property. There are a bunch of things and you might notice the workbookPassword isn’t the ‘1234’ that we set. This is because openpyxl used an algorithm to generate a hashed password so the real password is never revealed.

from openpyxl import load_workbook


>>> wb = load_workbook(r'C:\Users\jay\Desktop\PythonInOffice\excel_protection\eg.xlsx')
>>> wb.security
<openpyxl.workbook.protection.WorkbookProtection object>
Parameters:
workbookPassword='CC3D', workbookPasswordCharacterSet=None, revisionsPassword=None, revisionsPasswordCharacterSet=None, lockStructure=True, lockWindows=None, lockRevision=None, revisionsAlgorithmName=None, revisionsHashValue=None, revisionsSaltValue=None, revisionsSpinCount=None, workbookAlgorithmName=None, workbookHashValue=None, workbookSaltValue=None, workbookSpinCount=None

The funny thing is, we don’t even need to know the password to unprotect the worksheet. Programming is like a superpower!

Note in the above wb.security has a parameter lockStructure=True. We can simply set it to False, then save the workbook. We effectively just removed the password without even knowing what it is.

wb.security.lockStructure = False
wb.save(r'C:\Users\jay\Desktop\PythonInOffice\excel_protection\eg.xlsx')

Protect Excel Sheets using Python

We can also protect individual sheets to remove accidentally modifying the content. Once a sheet is protected, we won’t be able to change anything on that sheet.

To protect an Excel sheet using Python, we simply call the worksheet.protection class. Note we can modify a bunch of parameters in this class:

>>> ws = wb['Sheet1']
>>> ws.protection
<openpyxl.worksheet.protection.SheetProtection object>
Parameters:
selectLockedCells=False, selectUnlockedCells=False, algorithmName=None, sheet=False, objects=False, insertRows=True, insertHyperlinks=True, autoFilter=True, scenarios=False, formatColumns=True, deleteColumns=True, insertColumns=True, pivotTables=True, deleteRows=True, formatCells=True, saltValue=None, formatRows=True, sort=True, spinCount=None, password=None, hashValue=None

To make this more intuitive, let’s head back to Excel. As we press the “Protect Sheet” button inside Excel, a window will show up to ask us what do we want to protect. Take a look at the list in that window, then compare to the parameters of worksheet.protection class. Now you get the idea.

To protect the sheet, we just need to set the sheet parameter to True. Of course, don’t forget to save the file to keep the changes.

ws.protection.sheet = True

#password is optional
ws.protection.password = '1234'
wb.save(r'C:\Users\jay\Desktop\PythonInOffice\excel_protection\eg.xlsx')

Remove Excel Sheet Protection Password Using Python

By now you probably know already how to do this. Simply set the appropriate parameter to False to “unprotect” the sheet, and we don’t even need to know the password!

Below we load an Excel file that I previously manually protected “Sheet2”. By the way the password I used was “1111”, but it’s not showing here as discussed previously – openpyxl generated a hashed password rather than storing the original password itself.

>>> wb = load_workbook(r'C:\Users\jay\Desktop\PythonInOffice\excel_protection\eg.xlsx')
>>> wb.sheetnames
['test1', 'Sheet1', 'Sheet2']
>>> ws = wb['Sheet2']
>>> ws.protection
<openpyxl.worksheet.protection.SheetProtection object>
Parameters:
selectLockedCells=False, selectUnlockedCells=False, algorithmName='SHA-512', sheet=True, objects=True, insertRows=True, insertHyperlinks=True, autoFilter=True, scenarios=True, formatColumns=True, deleteColumns=True, insertColumns=True, pivotTables=True, deleteRows=True, formatCells=True, saltValue='gSNfEBrsrqNqvxxD1tmGgQ==', formatRows=True, sort=True, spinCount=100000, password=None, hashValue='OV8tSnvomxVmbeUgbh1XcVUT4Yu7WMTlutSAGwaEUz6W06uv9dzxNhdZULQLiQ79c0byFISlPMwcYCf1zBQEkQ=='

Note the parameter sheet=True, let’s set it to False, then save the file.

ws.protection.sheet = False
wb.save(r'C:\Users\jay\Desktop\PythonInOffice\excel_protection\eg.xlsx')

Open up the Excel file to check… the sheet is no longer protected!

Remove Protection For All Sheets and Workbook

Here’s a short code snippet to remove protection. You know, sometimes we get an Excel file that’s both Workbook and Sheets protected. It’s annoying to unlock them one by one, especially if we don’t know the password… However, with what you have learned today, you don’t need a password to unlock it!

wb = load_workbook(r'C:\Users\jay\Desktop\PythonInOffice\excel_protection\eg.xlsx')
wb.security.lockStructure = False
for s in wb.sheetnames:
    wb[s].protection.sheet = False
wb.save(r'C:\Users\jay\Desktop\PythonInOffice\excel_protection\eg.xlsx')