Blog

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'

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()