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}")