How to use Worksheet.Unprotect in the xlwings API way

The Unprotect member of the Worksheet object in xlwings is used to remove protection from a worksheet that has been previously protected. This is essential when you need to programmatically modify cells, ranges, or other elements that are locked under protection. Without unprotecting the sheet, attempts to write data or change formatting may fail. In xlwings, this functionality directly mirrors the Unprotect method in the Excel Object Model, providing a straightforward way to automate security settings in Excel files.

Syntax and Parameters

In xlwings, the Unprotect method is called on a Sheet object (which corresponds to a worksheet). The basic syntax is:

sheet.api.Unprotect(Password)

Here, sheet refers to the xlwings Sheet object. The .api attribute provides access to the underlying Excel object model, allowing you to use the native Unprotect method. The Password parameter is optional and specifies the password that was used to protect the worksheet. If the worksheet was protected without a password, you can omit this argument or pass None. If an incorrect password is provided when one is required, the method will raise an error.

Parameters:

• Password (optional, str): A string representing the password. It is case-sensitive. If the sheet is not password-protected, this can be omitted.

Example Usage

Below are practical examples demonstrating how to use the Unprotect member in xlwings:

1. Unprotecting a Worksheet Without a Password: If the worksheet was protected without a password, simply call Unprotect without any arguments.

import xlwings as xw

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

# Unprotect the sheet (no password)
sheet.api.Unprotect()

# Now you can modify the sheet, e.g., write a value
sheet.range('A1').value = 'New Data'

2. Unprotecting a Worksheet With a Password: When the worksheet is password-protected, provide the password as a string.

import xlwings as xw

wb = xw.Book('protected_file.xlsx')
sheet = wb.sheets['Sheet1']

# Unprotect using the password 'mysecret'
sheet.api.Unprotect('mysecret')

# Perform edits after unprotecting
sheet.range('B2').value = 100

3. Handling Protection in a Workflow: You might check if a sheet is protected before unprotecting it to avoid errors. While xlwings does not have a direct property for protection status, you can use the .api.ProtectContents property (returns True if protected).

import xlwings as xw

wb = xw.Book('workbook.xlsx')
sheet = wb.sheets['Sheet1']

# Check if the sheet is protected
if sheet.api.ProtectContents:
    sheet.api.Unprotect(Password='password123') # Use password if set
    print("Sheet unprotected successfully.")
else:
    print("Sheet is not protected.")

# Continue with data manipulation
sheet.range('A1:C10').value = [[1, 2, 3], [4, 5, 6]]

4. Unprotecting All Worksheets in a Workbook: To unprotect multiple sheets, iterate through them. This example assumes a common password for all sheets.

import xlwings as xw

wb = xw.Book('multi_sheet.xlsx')
password = 'commonpass'

for sheet in wb.sheets:
    if sheet.api.ProtectContents:
        sheet.api.Unprotect(password)
        print(f"Unprotected: {sheet.name}")

# Now all sheets are editable
wb.sheets[0].range('A1').value = 'Updated in all sheets'