Archive

How to use Worksheet.Columns in the xlwings API way

The Columns property of the Worksheet object in xlwings provides a way to access and manipulate entire columns within a specific worksheet. It is a powerful feature for performing operations on columnar data, such as formatting, resizing, or extracting values, without needing to iterate through individual cells. This property returns a Range object that represents one or more columns, enabling batch operations that enhance code efficiency and readability.

In xlwings, the syntax for accessing columns is straightforward. You can reference columns using the Columns property on a Sheet object (which is accessed via the workbook’s sheets collection). The basic syntax is: sheet.columns[item]. Here, item can be specified in several ways:

• A single integer (e.g., 1 for column A, 2 for column B).
• A string representing the column letter (e.g., 'A' for column A).
• A slice to select multiple columns (e.g., 'A:C' or 1:3 for columns A through C).
• A list or tuple for non-contiguous columns (e.g., [1, 3, 5] or ['A', 'C', 'E']).
  When using this property, it’s important to note that xlwings uses 1-based indexing for columns, aligning with Excel’s convention. The returned Range object can then be used to apply various methods and properties, such as setting values, adjusting width, or changing formatting.

For example, to set the width of column B to 20 points in a worksheet named “DataSheet”, you can use: sheet.columns['B'].column_width = 20. This directly accesses column B and modifies its width. Similarly, to clear the contents of columns D through F, you can write: sheet.columns['D:F'].clear_contents(). This demonstrates how the Columns property simplifies tasks that would otherwise require loops or more complex range specifications.

Here are a few practical code examples using the Columns property in xlwings:

1. Setting values for an entire column: To populate column A with a list of values, you can assign a list directly. For instance, sheet.columns['A'].value = [['Item1'], ['Item2'], ['Item3']] will fill cells A1, A2, and A3 with the specified items. Note that values should be provided as a list of lists, where each sublist corresponds to a row in the column.
2. Formatting multiple columns: To apply a bold font to columns C and E, you can use: sheet.columns[['C', 'E']].api.Font.Bold = True. This leverages the underlying Excel API via the .api attribute for advanced formatting, showcasing xlwings’ flexibility in combining high-level and low-level operations.
3. Autofitting column widths based on content: To automatically adjust the width of all columns in a worksheet to fit their content, you can use: sheet.columns.autofit(). This method resizes each column so that the longest entry is fully visible, improving readability without manual adjustments.
4. Hiding specific columns: If you need to hide columns B and D, you can execute: sheet.columns[['B', 'D']].hidden = True. This property controls the visibility of columns, useful for focusing on relevant data in reports or dashboards.

How to use Worksheet.CodeName in the xlwings API way

The CodeName property of a Worksheet object in Excel is a powerful feature that allows developers to assign a unique, programmatic identifier to a sheet. Unlike the Name property, which is the visible tab name a user can change, the CodeName is intended to remain static through the life of the workbook. This makes it an ideal reference in VBA macros and, by extension, in xlwings scripts, as it provides a stable way to target a specific sheet even if a user renames its tab. In xlwings, you access this property directly through the api object, which grants raw access to the underlying Excel object model.

The syntax for accessing the CodeName property in xlwings is straightforward. Since it is a read-only property in the context of xlwings (typically set in the Excel VBA IDE), you primarily retrieve its value. The call format is:

sheet.api.CodeName

Here, sheet is an xlwings Sheet object. The property returns a string representing the sheet’s programmatic name. It’s important to note that while you can read the CodeName via xlwings, changing it programmatically is not directly supported through the standard xlwings API; it is generally set in the Visual Basic for Applications editor (by changing the (Name) property in the Properties window for the sheet) before or during development.

Consider a workbook where you have a data input sheet. In the VBA IDE, you set its CodeName to shDataInput. Even if a user later changes the tab name from “Data” to “Monthly Data”, your xlwings code can still reliably find it. Here is a practical example:

import xlwings as xw

# Connect to the active workbook
wb = xw.books.active

# Method 1: Get a sheet by its CodeName by iterating
target_code_name = "shDataInput"
target_sheet = None
for sheet in wb.sheets:
    if sheet.api.CodeName == target_code_name:
        target_sheet = sheet
        break

if target_sheet:
    print(f"Found sheet with CodeName: {target_sheet.api.CodeName}")
    # Now you can work with the sheet reliably
    target_sheet.range("A1").value = "Updated via CodeName"
else:
    print("Sheet not found.")

# Method 2: A more direct approach using the `api` collection (requires knowing the index/name in the VBA project)
# This is less common but demonstrates the direct COM access.
try:
    # The VBA workbook object has a `Worksheets` collection accessible via `api`
    vba_sheet = wb.api.Worksheets(target_code_name) # This uses the *CodeName* in    the VBA collection
    xw_sheet = xw.Sheet(vba_sheet)
    print(f"Direct access successful. Sheet name (tab): {xw_sheet.name}")
except Exception as e:
    print(f"Direct access failed: {e}")

How to use Worksheet.CircularReference in the xlwings API way

In Excel, a circular reference occurs when a formula refers back to its own cell, either directly or through a chain of references, which can lead to calculation errors or iterative calculations. The CircularReference member of a Worksheet object in the xlwings API is a property that allows developers to identify and manage such references programmatically. This is particularly useful for debugging complex spreadsheets, ensuring data integrity, and automating error-checking processes. By accessing this property, users can pinpoint cells that contain circular formulas, enabling them to correct or analyze these references efficiently within Python scripts.

The CircularReference property is part of the Worksheet object in xlwings and provides read-only access to the range representing the first circular reference found on the worksheet. If no circular reference exists, it returns None. The syntax for accessing this property in xlwings is straightforward, as it does not require any parameters. It leverages the underlying Excel object model through the xlwings wrapper, making it seamless to integrate into Python code for Excel automation.

Syntax:
worksheet.api.CircularReference
Here, worksheet is an instance of the xlwings Sheet object (which corresponds to the Excel Worksheet). The .api attribute exposes the native Excel object model, allowing access to the CircularReference property. This property returns a Range object representing the cell with the circular reference, or None if there are none. Note that this property is specific to the Excel API and is accessed via xlwings’ COM or Apple Script bridge, depending on the operating system.

Example Use Cases:
To demonstrate the usage, consider an Excel workbook where a worksheet contains formulas that might create circular references. In xlwings, you can open the workbook, check for circular references, and take action based on the findings. Below is a code example that illustrates this:

import xlwings as xw

# Open an existing workbook and specify a worksheet
wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']

# Access the CircularReference property
circ_ref = ws.api.CircularReference

# Check if a circular reference exists
if circ_ref is not None:
    print(f"Circular reference found at: {circ_ref.address}")
    # You can get details like the cell value or formula
    print(f"Cell formula: {circ_ref.formula}")
    print(f"Cell value: {circ_ref.value}")

    # Optionally, clear or modify the circular reference
    # For example, set the cell to a static value or adjust the formula
circ_ref.value = 0 # Resetting to zero as a simple fix
    print("Circular reference has been addressed.")
else:
    print("No circular references detected in this worksheet.")

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

How to use Worksheet.Cells in the xlwings API way

The Cells property of a Worksheet object in the Excel object model is a fundamental member for accessing and manipulating individual cells or ranges of cells within a worksheet. In xlwings, this is accessed through the api property, which provides direct access to the underlying Excel object model, allowing for precise control similar to VBA. The Cells property is versatile, enabling both reading and writing of cell values, as well as formatting and other cell-specific operations.

Functionality:
The primary function of the Cells property is to return a Range object that represents a single cell or a collection of cells. It is commonly used to refer to cells by their row and column numbers, which is particularly useful in loops or when programmatically determining cell positions. This property is essential for tasks that require iterating over cells, dynamically referencing ranges, or accessing cells based on calculated indices.

Syntax:
In xlwings, the syntax to access the Cells property is:

worksheet.api.Cells(row_index, column_index)

• row_index: Required. An integer that specifies the row number of the cell (1-indexed).
• column_index: Required. An integer that specifies the column number of the cell (1-indexed). Alternatively, a string representing the column letter (e.g., “A”) can be used, but when using the Cells property directly via api, it typically expects numeric indices for consistency with the Excel object model.

The Cells property can also be called with a single argument to return a range encompassing all cells in the worksheet, though this is less common. For example, worksheet.api.Cells without arguments refers to all cells, but in practice, worksheet.used_range or similar methods are often preferred for performance.

Examples:
Here are several xlwings API code examples demonstrating the use of the Cells property:

1. Accessing a Single Cell Value:

import xlwings as xw
wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']
# Get value from cell at row 5, column 3 (C5)
cell_value = ws.api.Cells(5, 3).Value
print(cell_value)
# Set value in cell at row 2, column 1 (A2)
ws.api.Cells(2, 1).Value = "Hello, World!"

2. Iterating Over a Range of Cells:

# Write values to the first 5 rows in column A
for i in range(1, 6):
    ws.api.Cells(i, 1).Value = f"Data {i}"
# Read values from the first 3 rows in column B
for i in range(1, 4):
    print(ws.api.Cells(i, 2).Value)

3. Formatting Cells:

# Change the font color of cell D10 to red
ws.api.Cells(10, 4).Font.Color = 0xFF0000 # RGB color for red
# Set the interior color of cell E5 to yellow
ws.api.Cells(5, 5).Interior.Color = 0xFFFF00

4. Using Cells with Variables for Dynamic References:

row_num = 7
col_num = 4
# Dynamically access cell at row 7, column 4 (D7)
dynamic_cell = ws.api.Cells(row_num, col_num)
dynamic_cell.Value = "Dynamic Entry"

5. Accessing All Cells (Entire Worksheet):

# Refer to all cells in the worksheet (use with caution for large sheets)
all_cells = ws.api.Cells
print(f"Total rows: {all_cells.Rows.Count}, Total columns: {all_cells.Columns.Count}")

How to use Worksheet.AutoFilterMode in the xlwings API way

The AutoFilterMode property of a Worksheet object in Excel is a read-write Boolean attribute that indicates whether the AutoFilter drop-down arrows are currently displayed on the worksheet. This property is particularly useful for programmatically controlling the visibility of AutoFilter UI elements without directly interacting with the filter criteria. In xlwings, you can access this property through the api property of a Worksheet object, which provides direct access to the underlying Excel object model.

Functionality:

• When AutoFilterMode is set to True, the AutoFilter drop-down arrows appear in the header row of a filtered range (if one exists), allowing users to interactively filter data.
• When set to False, the arrows are hidden, but any existing filter settings remain intact. This means data may still be filtered, but the UI for adjusting filters is not visible.
• It is important to note that AutoFilterMode does not actually apply or remove filters; it only toggles the display of the AutoFilter interface. To manage filter criteria, use methods like AutoFilter.

Syntax in xlwings:
The property is accessed via the api interface of a Worksheet object. The general syntax is:

worksheet.api.AutoFilterMode

This returns a Boolean value (True or False). To set the property, assign a Boolean value directly:

worksheet.api.AutoFilterMode = True # Shows AutoFilter arrows
worksheet.api.AutoFilterMode = False # Hides AutoFilter arrows

No parameters are required for this property, as it is a simple attribute.

Example Usage:
Below are practical xlwings code examples that demonstrate how to use AutoFilterMode in different scenarios.

1. Checking AutoFilter Visibility:
   This example checks if AutoFilter arrows are displayed on a worksheet and prints the status.

import xlwings as xw

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

# Check the current AutoFilterMode status
if ws.api.AutoFilterMode:
    print("AutoFilter arrows are visible.")
else:
    print("AutoFilter arrows are hidden.")

2. Toggling AutoFilter Display:
   This example toggles the visibility of AutoFilter arrows based on their current state.

import xlwings as xw

wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']

# Toggle the AutoFilterMode
ws.api.AutoFilterMode = not ws.api.AutoFilterMode
print(f"AutoFilterMode is now set to: {ws.api.AutoFilterMode}")

3. Ensuring AutoFilter Arrows Are Hidden:
   This example hides the AutoFilter arrows without affecting any active filters, useful for cleaning up the UI before sharing the workbook.

import xlwings as xw

wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']

# Hide AutoFilter arrows if they are visible
if ws.api.AutoFilterMode:
    ws.api.AutoFilterMode = False
    print("AutoFilter arrows have been hidden.")
else:
    print("AutoFilter arrows were already hidden.")

4. Combining with AutoFilter Application:
   This example applies an AutoFilter to a range and then ensures the arrows are visible. It demonstrates how AutoFilterMode interacts with actual filtering.

import xlwings as xw

wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']

# Apply AutoFilter to range A1:C10 (assuming headers are in row 1)
ws.range('A1:C10').api.AutoFilter(Field=1, Criteria1=">100") # Filter column A for values > 100

# Make sure AutoFilter arrows are displayed
ws.api.AutoFilterMode = True
print("AutoFilter applied and arrows are visible.")

How to use Worksheet.AutoFilter in the xlwings API way

The AutoFilter member of the Worksheet object in xlwings provides a powerful way to programmatically manage Excel’s AutoFilter feature, which is essential for sorting, filtering, and analyzing data in ranges. By using the xlwings API, you can automate the process of applying, modifying, and clearing filters, enabling efficient data manipulation in Python scripts. This functionality is exposed through the api.AutoFilter property of a Worksheet object, which corresponds directly to the Excel VBA AutoFilter object model, allowing for detailed control over filter criteria and ranges.

The primary method to access the AutoFilter is via the api property of a worksheet. In xlwings, the api property grants direct access to the underlying Excel object model, making it possible to use Excel’s native methods and properties. The syntax for working with AutoFilter typically involves setting the filter range and applying criteria. For example, to apply an AutoFilter to a specific range, you can use ws.api.AutoFilter. The key parameters include the range to filter, field indices for columns, criteria for filtering, and optional operators. Here is a breakdown of common parameters in methods like AutoFilter:

• Range: Specifies the range to apply the filter, usually a string like “A1:D10” or an xlwings Range object.
• Field: An integer representing the column number in the filter range (1-based index).
• Criteria1: The primary filter criterion, such as a string for text filters or a number for value filters.
• Operator: An optional parameter that defines the filter type, using Excel constants like xlAnd, xlOr, xlTop10Items, etc. In xlwings, these are accessed via app.constants (e.g., app.constants.xlAnd).
• Criteria2: A secondary criterion used with operators like xlAnd or xlOr.

For instance, to filter a range to show rows where the first column equals “Product A”, you would set Field=1, Criteria1="Product A", and optionally use Operator=app.constants.xlAnd if combining criteria. It’s important to note that the AutoFilter must be applied to a range that includes headers; otherwise, Excel may not behave as expected. The xlwings API also allows checking if a filter is active via ws.api.AutoFilterMode and clearing it with ws.api.AutoFilter.ShowAllData().

Here are some practical xlwings API code examples for using the Worksheet AutoFilter member:

1. Applying an AutoFilter to a Range: This example applies an AutoFilter to the range A1:D20 on the active worksheet, enabling the filter dropdowns in the header row.

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('example.xlsx')
ws = wb.sheets['Sheet1']
ws.api.AutoFilter(ws.range('A1:D20').api)

2. Filtering Based on Text Criteria: This filters the first column to display only rows where the value is “Completed”.

ws.api.AutoFilter(ws.range('A1:D100').api, Field=1, Criteria1="Completed")

3. Using Multiple Criteria with an Operator: This filters the second column for values greater than 50 and less than 100, using the xlAnd operator.

ws.api.AutoFilter(ws.range('A1:D100').api, Field=2, Criteria1="50", Operator=app.constants.xlAnd, Criteria2="100")

4. Clearing All Filters: To remove filters and show all data in the worksheet, use the ShowAllData method.

if ws.api.AutoFilterMode:
    ws.api.AutoFilter.ShowAllData()

5. Checking Filter Status: This checks if an AutoFilter is currently applied to the worksheet.

filter_active = ws.api.AutoFilterMode
print(f"Filter active: {filter_active}")

How to use Worksheet.Application in the xlwings API way

In the xlwings library, the Worksheet object’s Application member is a property that returns a reference to the parent Application object of the workbook. This provides access to the broader Excel application instance, enabling control over application-level settings, properties, and methods. Through the Application property, you can interact with Excel’s global environment from a specific worksheet context, such as adjusting screen updating, calculation mode, or accessing other workbooks. This is particularly useful for automating tasks that require changes to the Excel application’s behavior while working within a specific sheet.

Functionality:
The primary function is to retrieve the Excel Application object associated with the worksheet. This allows you to:

• Control application settings like ScreenUpdating, Calculation, and DisplayAlerts.
• Access global collections such as Workbooks and Windows.
• Execute application-level methods like Quit to close Excel.
• Read application properties like Version or UserName.

Syntax:
In xlwings, the Application property is accessed from a Worksheet object. The general syntax is:

worksheet.application

Where worksheet is an instance of the xlwings Sheet object (representing a worksheet). This returns an App object in xlwings, which corresponds to the Excel Application. Note that xlwings uses App to represent the application, and it is typically obtained via xlwings.App() or from existing objects. The application property provides a direct link from a sheet to its parent app.

Key parameters or attributes are not directly passed to application, as it is a property. However, once you have the App object, you can use its properties and methods. For example:

• app.screen_updating: Controls screen updates (Boolean).
• app.calculation: Sets calculation mode (e.g., 'automatic', 'manual').
• app.visible: Makes Excel visible or hidden (Boolean).
• app.quit(): Closes the Excel application.

Example:
Below is an xlwings code example demonstrating the use of the Worksheet.Application property. This example assumes you have an Excel workbook open and a specific worksheet. It accesses the application to modify settings and retrieve information.

import xlwings as xw

# Connect to an existing workbook and worksheet
wb = xw.Book('example.xlsx') # Open a workbook
ws = wb.sheets['Sheet1'] # Select a specific worksheet

# Access the Application property from the worksheet
app = ws.application # Returns the parent App object

# Disable screen updating for performance
app.screen_updating = False

# Change calculation mode to manual
app.calculation = 'manual'

# Retrieve and print application version
print(f"Excel Version: {app.version}")

# Access the user name from the application
print(f"Current User: {app.user_name}")

# Re-enable screen updating and set calculation back to automatic
app.screen_updating = True
app.calculation = 'automatic'

# Example of using application to list all open workbooks
for workbook in app.books:
    print(f"Open Workbook: {workbook.name}")

# Close the application (use with caution as it closes Excel entirely)
# app.quit() # Uncomment to close Excel

How to use Worksheet.XmlMapQuery in the xlwings API way

The XmlMapQuery member of the Worksheet object in the Excel object model provides a powerful interface for querying and retrieving data from XML maps that have been added to a workbook. In xlwings, this functionality is accessible through the api property, allowing Python scripts to interact directly with Excel’s underlying COM objects. This is particularly useful for extracting structured data from XML sources mapped into Excel worksheets, enabling automation of data retrieval and integration tasks.

Functionality:
The XmlMapQuery method executes a query against an XML map associated with the worksheet, returning a Range object that represents the cells containing the queried data. It allows you to specify XPath expressions to filter or select specific nodes from the XML data, making it possible to import only relevant subsets into Excel. This is essential for handling large or complex XML datasets where selective data extraction is needed for analysis or reporting.

Syntax in xlwings:
In xlwings, you call this member via the api property of a Worksheet object. The general syntax is:

worksheet.api.XmlMapQuery(XPath, SelectionNamespaces, Map)

• XPath: A string specifying the XPath expression to query the XML map. This determines which data nodes are retrieved. For example, "/root/element" selects all element nodes under the root.
• SelectionNamespaces: A string containing the namespace declarations required for the XPath query, if the XML uses namespaces. It should be formatted as a space-separated list of xmlns:prefix="URI" declarations. For instance, 'xmlns:ns="http://example.com"'.
• Map: An optional parameter that specifies the XmlMap object to query. If omitted, Excel uses the first XML map in the workbook. You can pass an XmlMap object retrieved via the workbook’s XmlMaps collection.

Example Usage:
Suppose you have an XML map in an Excel workbook linked to a worksheet, and you want to query data from it using xlwings. Below is a code example that demonstrates how to use XmlMapQuery to retrieve specific data:

import xlwings as xw

# Connect to the open Excel workbook and worksheet
app = xw.apps.active
wb = app.books.active
ws = wb.sheets['Sheet1']

# Define the XPath query and namespaces (if needed)
xpath_expression = "/Orders/Order[Status='Shipped']"
namespaces = 'xmlns:ord="http://www.example.com/orders"'

# Execute the XML map query
# Assuming the first XML map in the workbook is used
result_range = ws.api.XmlMapQuery(xpath_expression, namespaces)

# Check if data was returned and output it
if result_range:
    # Convert the range to a list of lists for easy processing in Python
    data = result_range.value
    print("Queried Data:", data)
    # You can now analyze or visualize this data using Python libraries
else:
    print("No data found for the query.")

# Alternatively, specify a particular XML map by name
xml_map = wb.api.XmlMaps("MyXmlMap") # Replace with your map name
result_range_specific = ws.api.XmlMapQuery(xpath_expression, namespaces, xml_map)

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'