Archive

How to use Worksheet.XmlDataQuery in the xlwings API way

The XmlDataQuery member of the Worksheet object in Excel’s object model is a method that returns a Range object representing the cell or cells mapped to a specific XML map data node. This is particularly useful when working with XML data mapped into an Excel worksheet, allowing you to programmatically locate and manipulate data based on its XML structure. In xlwings, you can access this functionality through the api property, which provides direct access to the underlying Excel object model.

Functionality:
The primary purpose of XmlDataQuery is to query a worksheet for a range that is associated with a given XML map element or attribute. It helps in dynamically finding cells that are bound to XML data, enabling automated data processing, validation, or extraction within workbooks that use XML maps.

Syntax in xlwings:
The method is called via the worksheet’s API object. The general syntax is:

range_object = worksheet.api.XmlDataQuery(XPath, SelectionNamespaces, Map)

• XPath (required, string): The XPath expression that specifies the XML map node. This can be an absolute path to an element or attribute.
• SelectionNamespaces (optional, string): A space-delimited string of namespace declarations used in the XPath. It is required if the XPath contains namespaces. For example: "xmlns:ns='http://example.com/namespace'".
• Map (optional, Variant): An XmlMap object representing the specific XML map to query. If omitted, Excel uses all maps in the workbook. You can pass an XmlMap object obtained via workbook.api.XmlMaps(index_or_name).

The method returns a Range object (or None if no matching range is found), which you can then use with xlwings for further operations.

Example:
Suppose you have an XML map in your workbook with data mapped to a worksheet, and you want to find the cell containing the “Price” element. Here’s how you might use XmlDataQuery with xlwings:

import xlwings as xw

# Connect to the active workbook and sheet
wb = xw.books.active
ws = wb.sheets['Sheet1']

# Define the XPath for the XML node (e.g., an element named 'Price')
xpath = "/Invoice/Items/Item/Price"

# Optionally, define namespaces if needed (e.g., for a namespace 'ns')
namespaces = "xmlns:ns='http://schemas.example.com/invoice'"

# Query for the range
try:
    # Use the api to call XmlDataQuery
    target_range = ws.api.XmlDataQuery(XPath=xpath, SelectionNamespaces=namespaces)
    if target_range is not None:
        # Convert to xlwings range for easier manipulation
        xl_range = xw.Range(target_range)
        print(f"Found data at cell: {xl_range.address}")
        print(f"Cell value: {xl_range.value}")
    else:
        print("No matching range found.")
except Exception as e:
    print(f"Error querying XML data: {e}")

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'

How to use Worksheet.ShowDataForm in the xlwings API way

The ShowDataForm method in the Worksheet object is a powerful feature that allows developers to programmatically display Excel’s built-in data form for a specific range or list. This data form provides a user-friendly dialog box for entering, editing, and deleting records in a structured table, which is particularly useful for databases or lists where manual cell-by-cell editing can be error-prone. In xlwings, this method enables automation of form display, integrating seamlessly with Python scripts to enhance data management workflows within Excel.

Functionality
The primary function of ShowDataForm is to open Excel’s data form for a worksheet. This form automatically detects the range of contiguous data (typically a table with headers) and presents it in a modal dialog. Users can navigate through records, add new ones, modify existing entries, or delete data without directly interacting with the worksheet grid. It simplifies data entry tasks, especially for non-technical users, by providing a clean, form-based interface. In automation contexts, calling this method via xlwings can trigger the form as part of a larger process, such as after data validation or before saving a workbook.

Syntax
In xlwings, the ShowDataForm method is accessed through a Worksheet object. The API call follows the pattern below, with no required parameters, as it defaults to using the current region of the active cell or a specified range if set. The method is a member of the worksheet instance, and its invocation is straightforward.

worksheet.api.ShowDataForm()

• Parameters: This method does not accept any parameters in xlwings. It relies on Excel’s internal logic to determine the data range, typically based on the active cell’s current region (a contiguous block of cells surrounded by empty rows and columns). If a specific range needs to be targeted, ensure the active cell is within that range before calling the method, or use Excel’s object model to set the range programmatically via other properties (e.g., Range selection).
• Returns: The method does not return a value; it simply displays the data form as a modal dialog. User interactions in the form (like edits or additions) are directly reflected in the worksheet upon closure.

Example Usage
Below is a practical xlwings code example that demonstrates how to use ShowDataForm to open the data form for a worksheet. The example assumes an Excel workbook is already open or created via xlwings, and it targets a specific worksheet with existing data. The script automates the process of activating the worksheet and displaying the form, which can be integrated into larger automation tasks, such as data review steps.

import xlwings as xw

# Connect to an existing Excel workbook (adjust the path as needed)
wb = xw.Book('example.xlsx')

# Access a specific worksheet by name
ws = wb.sheets['DataSheet']

# Ensure the worksheet is active (optional, but good practice for form display)
ws.activate()

# Display the data form for the worksheet
ws.api.ShowDataForm()

How to use Worksheet.ShowAllData in the xlwings API way

The ShowAllData member of the Worksheet object in Excel is a method used to clear any filters that have been applied to an Excel table, list, or range on the specified worksheet. When filters are active, some rows may be hidden based on the filter criteria. Calling ShowAllData removes these filters, making all rows in the data range visible again. This is particularly useful in data analysis workflows when you need to reset the view to the full dataset after performing filtered operations or before applying new filters. It ensures that subsequent operations, such as sorting or calculations, consider the entire dataset unless otherwise specified.

In xlwings, the ShowAllData method is accessed through the api property of a Worksheet object, which provides direct access to the underlying Excel object model. The method does not take any parameters.

Syntax:

worksheet.api.ShowAllData()

Here, worksheet is an xlwings Worksheet object representing the Excel worksheet where you want to clear filters. The api property exposes the native Excel VBA object model, allowing you to call the ShowAllData method directly. Since no parameters are required, you simply invoke it without arguments.

Example:
Consider a scenario where you have an Excel workbook with a worksheet named “SalesData” containing a table with filters applied to certain columns. You want to clear all filters to display the full dataset. Below is an example using xlwings to achieve this:

import xlwings as xw

# Connect to the active Excel instance or open a specific workbook
app = xw.apps.active # Use the currently active Excel application
wb = app.books['SalesReport.xlsx'] # Specify your workbook name

# Access the worksheet by name
ws = wb.sheets['SalesData']

# Check if filters are applied (optional step, for demonstration)
# Note: There's no direct xlwings property to check filter status, so we rely on the Excel method.
try:
    # Attempt to show all data; if no filter is applied, this may raise an error.
    ws.api.ShowAllData()
    print("All filters cleared successfully.")
except Exception as e:
    # Handle cases where no filters are present or other errors occur
    print(f"No filters to clear or an error occurred: {e}")

# Perform further operations, such as sorting the entire dataset
ws.range('A1').current_region.api.Sort(Key1=ws.range('A2'), Order1=1) # Sort by column A ascending

# Save the workbook if needed
wb.save()

How to use Worksheet.SetBackgroundPicture in the xlwings API way

The SetBackgroundPicture method in the Worksheet object is a feature in Excel’s object model that allows developers to set a background image for a worksheet. This can enhance the visual appeal of spreadsheets, such as adding logos, watermarks, or decorative backgrounds for reports or dashboards. In xlwings, a Python library for interacting with Excel, this functionality is exposed through the api property, which provides direct access to Excel’s COM objects. Using SetBackgroundPicture, users can programmatically apply images to worksheet backgrounds, automating tasks that might otherwise require manual steps in Excel’s user interface.

Functionality:
The primary function of SetBackgroundPicture is to assign an image file as the background of a specific worksheet. This image is tiled across the entire sheet, meaning it repeats to cover the worksheet area. It’s important to note that setting a background picture does not affect cell formatting or data entry; the image appears behind the cells. However, background pictures are not printed by default in Excel, and they may impact performance if the image file is large. This method is useful for branding purposes or creating visually consistent templates in automated Excel reports.

Syntax in xlwings:
In xlwings, you can call SetBackgroundPicture through the api property of a Sheet object (which corresponds to a Worksheet in Excel). The syntax is as follows:

sheet.api.SetBackgroundPicture(Filename)

• Parameters:
• Filename (required): A string that specifies the path to the image file. This should be a full or relative path to a supported image format, such as JPEG, PNG, BMP, or GIF. The path must be accessible from the system where the Excel application is running.
• Returns:
  This method does not return a value; it applies the background picture directly to the worksheet.
• Notes:
• If the file path is invalid or the image cannot be loaded, Excel may raise an error.
• To remove an existing background picture, you can use sheet.api.SetBackgroundPicture("") with an empty string as the filename.
• Background pictures are specific to each worksheet, so you need to call this method for each sheet individually.
• In xlwings, ensure that the Excel application is visible or running in the background when executing this method, as it relies on COM interop.

Code Examples:
Below are practical examples using xlwings to set a background picture for a worksheet.

1. Setting a Background Picture:
   This example assumes you have an Excel file open and want to set a background image for the active sheet.

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active
workbook = app.books.active
sheet = workbook.sheets['Sheet1'] # Specify the worksheet name

# Set the background picture using an image file path
image_path = r'C:\Images\background.jpg' # Use raw string for Windows paths
sheet.api.SetBackgroundPicture(image_path)

# Save the workbook to persist changes
workbook.save()

2. Removing a Background Picture:
   To clear the background picture from a worksheet, pass an empty string as the filename.

import xlwings as xw

# Connect to the active workbook and sheet
sheet = xw.books.active.sheets[0] # Access the first sheet

# Remove any existing background picture
sheet.api.SetBackgroundPicture("")

# Optionally, save the changes
xw.books.active.save()

3. Setting Background Pictures for Multiple Sheets:
   You can loop through multiple worksheets in a workbook to apply the same background image.

import xlwings as xw

# Open a specific workbook
workbook = xw.Book('report.xlsx')
image_path = '/path/to/logo.png' # Adjust path for your system

# Apply background picture to all sheets
for sheet in workbook.sheets:
sheet.api.SetBackgroundPicture(image_path)

# Save and close if needed
workbook.save()
workbook.close()

How to use Worksheet.Select in the xlwings API way

The Select method of the Worksheet object in Excel is used to activate and highlight a specific worksheet, making it the active sheet in the workbook. This is particularly useful when you need to programmatically switch between sheets to perform operations like data entry, formatting, or analysis on a particular sheet without manual intervention. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model, allowing for precise control over worksheet selection.

Syntax in xlwings:
The Select method is called on a worksheet object via its api attribute. The basic syntax is:

worksheet.api.Select(Replace)

• Replace (Optional): A Boolean parameter that specifies whether the current selection should be replaced.
• If True (or omitted, as the default is True), the selected worksheet replaces any previous selection, making it the only active sheet.
• If False, the worksheet is added to the current selection, allowing multiple sheets to be selected simultaneously (e.g., for grouping or multi-sheet operations). This is applicable only if the workbook is not protected and the sheets are adjacent.

Example Usage:
Below are practical examples demonstrating how to use the Select method with xlwings.

1. Basic Selection: Activate and select a single worksheet named “DataSheet”.

import xlwings as xw
# Connect to an existing workbook
wb = xw.Book("example.xlsx")
# Access the worksheet by name
ws = wb.sheets["DataSheet"]
# Select the worksheet, replacing any previous selection
ws.api.Select()
# Alternatively, explicitly set Replace to True
ws.api.Select(Replace=True)

2. Selecting Multiple Sheets: Select multiple adjacent worksheets by adding to the selection without replacing it. This example selects “Sheet1” and “Sheet2” together.

import xlwings as xw
wb = xw.Book("example.xlsx")
# First, select Sheet1 with replacement
wb.sheets["Sheet1"].api.Select(Replace=True)
# Then, select Sheet2 without replacing, so both are selected
wb.sheets["Sheet2"].api.Select(Replace=False)
# Note: This requires the sheets to be next to each other in the workbook.

3. Integration with Other Operations: Combine Select with other actions, such as formatting or data input, after making a worksheet active. Here, we select a sheet and then clear its contents.

import xlwings as xw
wb = xw.Book("example.xlsx")
ws = wb.sheets["Report"]
# Select the worksheet
ws.api.Select()
# Now perform an operation on the active sheet, like clearing cell A1 to D10
ws.range("A1:D10").clear()

How to use Worksheet.Scenarios in the xlwings API way

In Excel, a Scenario is a set of input values (called changing cells) that you can save and later substitute into a worksheet to see different outcomes. The Scenarios collection of a Worksheet object in Excel’s object model allows you to manage these saved scenarios. Through xlwings, you can programmatically access, create, modify, and apply these scenarios, enabling powerful what-if analysis automation within your Python scripts.

Functionality
The Scenarios member provides a way to interact with all scenarios defined on a specific worksheet. You can add new scenarios, retrieve existing ones, change their values, show (apply) a particular scenario, and delete scenarios. This is particularly useful for building financial models, project plans, or any analysis where you need to quickly switch between different sets of assumptions.

Syntax and Key Members
In xlwings, you access the Scenarios collection via the api property of a Sheet object (which corresponds to a Worksheet). The primary properties and methods include:

• Accessing the Collection: sheet.api.Scenarios
• Count Property: sheet.api.Scenarios.Count returns the number of scenarios on the sheet.
• Item Method: sheet.api.Scenarios(Index) or sheet.api.Scenarios(Name) retrieves a specific Scenario object. Index can be the scenario’s number (1-based) or name.
• Add Method: Used to create a new scenario.

sheet.api.Scenarios.Add(Name, ChangingCells, Values, Comment, Locked, Hidden)

• Name (String, Required): The name for the new scenario.
• ChangingCells (Object, Required): An xlwings Range object (e.g., sheet.range("B2:B3")), specifying the cells that will change.
• Values (Variant, Optional): An array of values to be entered into the changing cells. If omitted, the current values in the cells are used.
• Comment (String, Optional): A comment describing the scenario (up to 255 characters).
• Locked (Boolean, Optional): True to prevent modifications when the sheet is protected.
• Hidden (Boolean, Optional): True to hide the scenario when the sheet is protected.
• A Scenario object itself has key methods like:
• Show(): Applies the scenario’s values to the worksheet.
• ChangeScenario(ChangingCells, Values): Modifies the scenario’s changing cells or values.
• Delete(): Removes the scenario.

Code Examples

1. Adding a New Scenario:

import xlwings as xw
wb = xw.Book("Analysis.xlsx")
sheet = wb.sheets["Sheet1"]

# Define changing cells and values
changing_cells = sheet.range("B2, B4") # Assumptions for Price and Units
scenario_values = [29.99, 1200]

# Add a "Best Case" scenario
sheet.api.Scenarios.Add(Name="Best Case",
ChangingCells=changing_cells,
Values=scenario_values,
Comment="Optimistic sales forecast")

2. Applying (Showing) an Existing Scenario:

# Apply the "Worst Case" scenario to see its impact
try:
    sheet.api.Scenarios("Worst Case").Show()
    print("Applied 'Worst Case' scenario.")
except Exception as e:
    print(f"Scenario not found: {e}")

3. Iterating Through and Managing Scenarios:

# List all scenarios and delete a specific one
scenarios = sheet.api.Scenarios
print(f"Number of scenarios: {scenarios.Count}")

for i in range(1, scenarios.Count + 1):
    scen = scenarios(i)
    print(f"{i}: {scen.Name} - {scen.Comment}")

# Delete the "Obsolete" scenario if it exists
if scenarios.Count > 0:
    for scen in scenarios:
        if scen.Name == "Obsolete":
            scen.Delete()
            print("Deleted 'Obsolete' scenario.")
            break

4. Modifying a Scenario’s Values:

# Update the values for the "Base Case" scenario
target_scenario = sheet.api.Scenarios("Base Case")
new_changing_cells = sheet.range("B2:B3")
new_values = [25.50, 950]
target_scenario.ChangeScenario(ChangingCells=new_changing_cells, Values=new_values)

How to use Worksheet.SaveAs in the xlwings API way

The SaveAs method in the Worksheet object of the Excel object model is a powerful feature for saving a specific worksheet as a new workbook file. This is particularly useful in scenarios where you need to extract or archive a single sheet from a larger workbook, or when generating reports from a template. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model, allowing for precise control similar to VBA.

The syntax for calling SaveAs via xlwings is: worksheet.api.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodepage, TextVisualLayout, Local). The key parameters are:

• Filename: A string specifying the full path and name of the file to save. It is required.
• FileFormat: A constant or number specifying the file format. Common values include 51 for .xlsx (Excel Workbook), 52 for .xlsm (Macro-Enabled Workbook), and 6 for .csv (CSV format). If omitted, the current file format is used.
• Password: An optional string to set a password for opening the workbook.
• WriteResPassword: An optional string to set a password for write reservation.
• CreateBackup: If set to True, Excel creates a backup file.
• Other parameters like ReadOnlyRecommended, AddToMru, TextCodepage, TextVisualLayout, and Local are less frequently used and often left as default.

For example, to save the active worksheet as a new Excel workbook in the .xlsx format to a specific directory, you can use the following xlwings code. This example assumes you have an existing workbook with a worksheet object referenced. The code saves the worksheet “DataSheet” as a separate file, ensuring the original workbook remains unchanged. It demonstrates setting a filename, specifying the file format, and optionally adding a password for protection. This method is efficient for automating report generation or data extraction tasks, as it leverages Excel’s native saving capabilities through a clean Python interface.

import xlwings as xw

# Connect to the active Excel instance or open a workbook
app = xw.apps.active
wb = app.books['OriginalWorkbook.xlsx']
ws = wb.sheets['DataSheet']

# Save the worksheet as a new workbook
new_file_path = r'C:\Reports\Extracted_DataSheet.xlsx'
ws.api.SaveAs(Filename=new_file_path, FileFormat=51) # 51 corresponds to .xlsx

# To save as a CSV file without a password
csv_path = r'C:\Reports\DataSheet.csv'
ws.api.SaveAs(Filename=csv_path, FileFormat=6) # 6 corresponds to CSV

# To save with a password for opening
protected_path = r'C:\Reports\Secure_DataSheet.xlsx'
ws.api.SaveAs(Filename=protected_path, FileFormat=51, Password='mysecret')

How to use Worksheet.ResetAllPageBreaks in the xlwings API way

The Worksheet.ResetAllPageBreaks method in Excel’s object model is a useful tool for managing print layout, specifically by removing all manually inserted page breaks on a specified worksheet. When preparing a document for printing, users often insert horizontal or vertical page breaks to control where pages end. Over time, a sheet can accumulate many such breaks, which may no longer be needed or could interfere with updated print settings. The ResetAllPageBreaks method clears all these user-defined page breaks at once, reverting the sheet to automatic page breaking based on current margins, paper size, and scaling. This is particularly helpful when you want to start fresh with page layout adjustments or ensure print output follows default pagination.

In xlwings, the API closely mirrors the Excel object model. To call this method, you access it through a Worksheet object. The syntax is straightforward as it does not take any parameters. The general format is:

worksheet.api.ResetAllPageBreaks()

Here, worksheet refers to an xlwings Worksheet object. The .api property provides direct access to the underlying Excel object model, allowing you to use native Excel methods like ResetAllPageBreaks. Since it is a method, you invoke it with parentheses. It affects only the worksheet it is called on and does not return a value.

Consider a scenario where you have an Excel workbook for monthly reports. After several rounds of manual page break adjustments, you want to clear them all to apply a new uniform print setup. The following xlwings code example demonstrates this:

import xlwings as xw

# Connect to the active Excel application or start a new one
app = xw.apps.active

# Open a specific workbook (adjust the path as needed)
wb = app.books.open('Monthly_Report.xlsx')

# Access the desired worksheet
ws = wb.sheets['DataSheet']

# Reset all manually inserted page breaks on this worksheet
ws.api.ResetAllPageBreaks()

# Save the workbook to persist changes
wb.save()

# Optionally, close the workbook and app
wb.close()
app.quit()

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