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