How to use Worksheet.Protect in the xlwings API way

The protect method of the Worksheet object in xlwings provides a way to secure a worksheet by preventing unauthorized changes. This is particularly useful when you want to share a workbook but restrict editing of specific cells, formulas, or structural elements. By protecting a worksheet, you can allow certain actions, such as selecting cells, while blocking others, like modifying locked cells. In xlwings, this method wraps the corresponding functionality in the Excel object model, offering a programmatic approach to worksheet protection directly from Python.

The syntax for the protect method in xlwings is as follows:

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

Here, worksheet is an xlwings Sheet object representing the target worksheet. The parameters correspond to those in the Excel VBA Protect method, with most being optional boolean values that default to True or False depending on the action. Key parameters include:

• Password: A string to set a password for unprotecting the sheet (optional; if omitted, no password is set).
• Contents: If True (default), protects the contents (locked cells) of the worksheet.
• UserInterfaceOnly: If True, protection applies only to the UI, allowing macros to make changes via code; defaults to False.
• AllowFormattingCells, AllowFormattingColumns, etc.: These boolean parameters control specific user permissions, such as allowing cell formatting or inserting rows; most default to False when the sheet is protected.

For example, to protect a worksheet with a password while allowing users to format cells and sort data, you can set AllowFormattingCells and AllowSorting to True. Note that in xlwings, you access this via the .api property to call the underlying Excel object model method, as xlwings does not have a native wrapper for all protection options in its high-level API.

Below are xlwings API code examples demonstrating the use of the protect method:

1. Basic protection without a password: This protects the worksheet with default settings, preventing edits to locked cells.

import xlwings as xw
wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']
ws.api.Protect()

2. Protection with a password and specific allowances: Here, a password “mypass123” is set, and users are permitted to format cells and insert hyperlinks, while other actions are restricted.

ws.api.Protect(Password='mypass123', AllowFormattingCells=True, AllowInsertingHyperlinks=True)

3. UI-only protection for macro flexibility: This protects the worksheet in the user interface but allows VBA or xlwings macros to modify it programmatically, without a password.

ws.api.Protect(UserInterfaceOnly=True)

4. Disabling protection: To unprotect a worksheet, use the Unprotect method. If a password was set, provide it as an argument.

ws.api.Unprotect('mypass123') # If password was used
ws.api.Unprotect() # If no password was set