Protect and Unprotect Excel Worksheets and Workbooks Using Python

In enterprise data management and report distribution scenarios, Excel files often contain sensitive information or critical formulas that need protection from unauthorized modifications. Manually setting protection passwords on each worksheet is feasible for a small number of files, but it becomes inefficient when dealing with batch processing or automated report generation.

By implementing worksheet and workbook protection programmatically with Python, security policies can be seamlessly integrated into document generation and distribution workflows. This article covers how to password-protect worksheets, lock specific cells and rows/columns, define editable ranges, hide formulas, and remove protection using Python.

Environment Setup

This article uses the Spire.XLS for Python library to work with Excel files. Install it via pip:

pip install Spire.XLS

Import the required modules in your script:

from spire.xls import *
from spire.xls.common import *

Understanding Excel Protection Basics

Excel's protection mechanism operates at two levels:

• Worksheet protection: Restricts editing of cell contents within a worksheet. You can set a password and selectively allow certain operations (such as sorting or filtering).
• Workbook protection: Restricts modifications to the workbook structure, preventing users from adding, deleting, or renaming worksheets.

In Spire.XLS, worksheet protection is implemented through the Protect() method, while workbook protection uses the Workbook.Protect() method. Every cell has a Locked property — by default, all cells are locked, but the lock only takes effect once worksheet protection is enabled.

Protect a Worksheet

The simplest form of protection is setting a password on the entire worksheet. Once protection is enabled, all cells are locked by default and users cannot edit any content:

from spire.xls import *
from spire.xls.common import *

# Create a workbook and load the file
workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Protect the worksheet with a password
sheet.Protect("MyPassword", SheetProtectionType.All)

# Save the document
workbook.SaveToFile("ProtectSheet.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

SheetProtectionType.All denies all operations. You can also choose more granular protection types, such as preventing only row insertion, column deletion, or other specific actions.

Lock Specific Cells

In most scenarios, you don't need to lock everything — only critical formula cells or summary data need protection. The key to achieving this is to first unlock all cells, then lock only the ones that need protection, and finally enable worksheet protection.

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")
sheet = workbook.Worksheets[0]

# Lock cell B3
sheet.Range["B3"].Style.Locked = True

# Unlock cell C3 (editable even when the sheet is protected)
sheet.Range["C3"].Style.Locked = False

# Enable worksheet protection
sheet.Protect("TestPassword", SheetProtectionType.All)

workbook.SaveToFile("LockSpecificCells.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

This approach is ideal for form-style documents where most fields should be user-editable, with only formulas, headers, and other key areas requiring protection.

Lock Specific Rows or Columns

When you need to protect entire rows or columns of data, you can iterate through and set the lock status accordingly. The following example unlocks all rows first, then locks only row 3:

workbook = Workbook()
workbook.CreateEmptySheet()
sheet = workbook.Worksheets[0]

# Unlock all rows
for i in range(0, 255):
    sheet.Rows[i].Style.Locked = False

# Lock row 3
sheet.Rows[2].Text = "Locked"
sheet.Rows[2].Style.Locked = True

# Enable protection
sheet.Protect("123", SheetProtectionType.All)

workbook.SaveToFile("LockSpecificRow.xlsx", ExcelVersion.Version2013)

Locking a specific column follows a similar pattern. The example below locks column D (the 4th column):

workbook = Workbook()
workbook.CreateEmptySheet()
sheet = workbook.Worksheets[0]

# Unlock all rows
for i in range(0, 255):
    sheet.Rows[i].Style.Locked = False

# Lock column D
sheet.Columns[3].Text = "Locked"
sheet.Columns[3].Style.Locked = True

# Enable protection
sheet.Protect("123", SheetProtectionType.All)

workbook.SaveToFile("LockSpecificColumn.xlsx", ExcelVersion.Version2013)

Define Editable Ranges

A more flexible approach is to specify certain areas as "allowed edit ranges" while the worksheet remains protected. Users can freely edit within these ranges while the rest of the worksheet stays locked:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")
sheet = workbook.Worksheets[0]

# Define the range that users are allowed to edit
sheet.AddAllowEditRange("EditableRanges", sheet.Range["B4:E12"])

# Protect the worksheet
sheet.Protect("TestPassword", SheetProtectionType.All)

workbook.SaveToFile("ProtectedWithEditableRange.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The AddAllowEditRange() method takes two parameters: a range name and a range reference. This approach is more intuitive than setting the Locked property on individual cells, making it especially useful when you need to open up data entry areas within a protected spreadsheet.

Protect Workbook Structure

Beyond worksheet-level protection, you can also protect the overall workbook structure to prevent users from adding, deleting, or renaming worksheets:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

# Protect the workbook structure
workbook.Protect("e-iceblue")

workbook.SaveToFile("ProtectWorkbook.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Workbook protection and worksheet protection can be applied simultaneously, creating multi-layered defense from the overall structure down to individual cell contents.

Hide Formulas

In reports containing complex formulas, you may want users to see the calculated results without being able to view or modify the formulas themselves. By setting the IsFormulaHidden property and enabling protection, all formulas in the worksheet can be hidden:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")
sheet = workbook.Worksheets[0]

# Hide all formulas in the used range
sheet.AllocatedRange.IsFormulaHidden = True

# Enable worksheet protection for the hidden setting to take effect
sheet.Protect("e-iceblue")

workbook.SaveToFile("HideFormulas.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

It's important to note that the IsFormulaHidden property only takes effect after worksheet protection is enabled. Setting this property alone will not hide formulas.

Unprotect a Worksheet

Removing protection requires the correct password. Use the Unprotect() method and pass in the password that was set during protection:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("ProtectedSheet.xlsx")
sheet = workbook.Worksheets[0]

# Remove protection using the password
sheet.Unprotect("e-iceblue")

workbook.SaveToFile("UnprotectedSheet.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

If the worksheet was protected without a password, you can call Unprotect() without passing any arguments.

Practical Tips

Combining protection with editable ranges: When building data entry templates, use AddAllowEditRange() to open up input areas, then protect the worksheet. This prevents users from accidentally modifying headers and formulas while still allowing normal business data entry.

Batch protecting multiple worksheets: Iterate through all worksheets in a workbook and apply a uniform protection password:

for i in range(workbook.Worksheets.Count):
    sheet = workbook.Worksheets[i]
    sheet.Protect("CommonPassword", SheetProtectionType.All)

Password security best practices: In production environments, passwords should not be hardcoded in source code. Use environment variables or configuration files to manage passwords, and choose sufficiently strong passwords to guard against brute-force attacks.

Conclusion

This article covered the complete approach to protecting and unprotecting Excel worksheets and workbooks using Python, including basic protection, locking specific cells and rows/columns, defining editable ranges, hiding formulas, and removing protection. These capabilities have broad practical value in automated report generation, batch file processing, and document security management scenarios.

Combined with features like conditional formatting and data validation, you can build more secure and feature-rich Excel automation solutions.