How to use Worksheet.Protection in the xlwings API way

The Protection member of a Worksheet object in xlwings provides a way to control and query the protection settings of a worksheet. This is essential for securing data by preventing unauthorized users from modifying cells, formatting, or other elements. Through xlwings, you can access the protection properties to check the current protection status, apply protection with specific options, or unprotect the sheet if needed. It’s a powerful feature for automating the security aspects of Excel workbooks in Python.

Functionality
The Protection object allows you to:

• Protect a worksheet to restrict editing.
• Unprotect a worksheet to allow edits.
• Check if a worksheet is currently protected.
• Customize protection settings, such as allowing users to select locked cells, format cells, insert rows, etc.

Syntax
In xlwings, you access the Protection member via the api property to interact with the underlying Excel object model. The typical syntax is:

sheet.protection # This returns the Protection object

To protect a worksheet:

sheet.api.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

To unprotect a worksheet:

sheet.api.Unprotect(Password)

To check protection status:

sheet.api.ProtectContents # Returns True if the worksheet is protected

Parameters and Values
The Protect method has multiple optional parameters that control what users can do. Here are key parameters and their meanings:

Parameter	Type	Description	Typical Values
Password	String	A password to protect the sheet (optional).	Any string, e.g., “mypass123”
DrawingObjects	Boolean	Protects drawing objects (shapes).	True or False
Contents	Boolean	Protects cell contents (locked cells).	True or False
UserInterfaceOnly	Boolean	If True, protection applies only via UI, not via code.	True or False
AllowFormattingCells	Boolean	Allows formatting of cells.	True or False
AllowInsertingRows	Boolean	Allows inserting rows.	True or False
AllowSorting	Boolean	Allows sorting.	True or False
AllowFiltering	Boolean	Allows filtering.	True or False

For a full list, refer to the Excel VBA documentation, as xlwings passes these directly to Excel.

Code Examples
Here are practical examples using xlwings to work with worksheet protection:

1. Protecting a worksheet with a password and specific allowances:

import xlwings as xw

# Connect to an existing workbook and sheet
wb = xw.Book('example.xlsx')
sheet = wb.sheets['Sheet1']

# Protect the sheet with a password, allowing formatting and sorting
sheet.api.Protect(Password="secret123", AllowFormattingCells=True, AllowSorting=True)
print("Worksheet protected.")

2. Unprotecting a worksheet:

# Unprotect the sheet (if no password, omit the argument)
sheet.api.Unprotect("secret123")
print("Worksheet unprotected.")

3. Checking if a worksheet is protected:

# Check protection status
if sheet.api.ProtectContents:
    print("The worksheet is protected.")
else:
    print("The worksheet is not protected.")

4. Applying protection with multiple options:

# Protect without a password but allow various actions
sheet.api.Protect(
Password=None,
DrawingObjects=True,
Contents=True,
AllowInsertingRows=True,
AllowFiltering=True,
UserInterfaceOnly=False
)
print("Protection applied with custom settings.")