How to use Range.AutoFilter in the xlwings API way

The AutoFilter member of the Range object in Excel, accessible via the xlwings API, is a powerful feature for programmatically applying and managing autofilters on worksheet data. It allows for dynamic data filtering based on specified criteria, enabling users to focus on relevant subsets of information within a larger dataset. This functionality is essential for data analysis tasks where sifting through rows to find matching records is required.

In xlwings, the AutoFilter is applied through the api property, which provides direct access to the underlying Excel object model. The primary method used is AutoFilter. The syntax for applying a basic autofilter to a range is straightforward. For more complex filtering involving specific criteria, the AutoFilter method can accept parameters.

The core syntax for applying an autofilter in xlwings is:
range.api.AutoFilter
This will apply filters to the headers of the specified range. To set specific filter criteria, you can use the extended syntax:
range.api.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

• Field: The integer offset of the field (column) to filter on, where the first column in the range is 1.
• Criteria1: The primary filter criterion as a string (e.g., “>100”, “Product A”).
• Operator: An optional constant from the XlAutoFilterOperator enumeration (e.g., xlAnd, xlOr) to define the relationship between Criteria1 and Criteria2. Common xlwings constants are win32c.XlAutoFilterOperator.xlAnd or win32c.XlAutoFilterOperator.xlOr.
• Criteria2: The secondary filter criterion, used when Operator is xlAnd or xlOr.
• VisibleDropDown: An optional boolean to show or hide the filter dropdown arrow (default is True).

For example, to filter the first column for values equal to “Yes”:
range.api.AutoFilter(Field:=1, Criteria1:="Yes")
To filter a numeric column for values greater than 50:
range.api.AutoFilter(Field:=2, Criteria1:=">50")
For a filter with two conditions (e.g., values between 10 and 100), you would use:
range.api.AutoFilter(Field:=3, Criteria1:=">=10", Operator:=win32c.XlAutoFilterOperator.xlAnd, Criteria2:="<=100")

Below are practical xlwings code examples. First, ensure you have a reference to the desired worksheet and range.

import xlwings as xw
# Connect to the active workbook and sheet
wb = xw.books.active
ws = wb.sheets['Sheet1']
# Define the range to filter, including headers
data_range = ws.range('A1').current_region

# Example 1: Apply a basic autofilter to the entire data range
data_range.api.AutoFilter()
print("Basic autofilter applied.")

# Example 2: Filter the first column for the value "Completed"
data_range.api.AutoFilter(Field=1, Criteria1="Completed")
print("Filtered column 1 for 'Completed'.")

# Example 3: Filter the second column for values greater than 100
data_range.api.AutoFilter(Field=2, Criteria1=">100")
print("Filtered column 2 for values > 100.")

# Example 4: Clear all filters from the worksheet
ws.api.AutoFilterMode = False
print("All filters cleared.")

# Example 5: Complex filter on the third column for values between 50 and 150
import win32com.client.constants as win32c
data_range.api.AutoFilter(Field=3, Criteria1=">=50", Operator=win32c.XlAutoFilterOperator.xlAnd, Criteria2="<=150")
print("Filtered column 3 for values between 50 and 150.")