How to add column filters to Excel worksheet using XLWINGS

Question

My Python method for generating an Excel sheet is almost complete. One of the last things to do is to add a filtering option to the sheet's top row (using the Excel shortcut CTRL+SHIFT+L). No filtering should be done; just add the filter. Although I can open the file and complete the task manually, it would be wonderful if it could be automated.

Is it possible?

Answer 1

Call the API property to activate the autofilter:

import xlwings as xw

path = r"test.xlsx"

wb = xw.Book(path)
ws = wb.sheets[0]

ws.used_range.api.AutoFilter(Field:=1)

 You can also use native xlwings functions (create a table object and then set its show_autofilter to True):

import xlwings as xw

path = r"test.xlsx"

wb = xw.Book(path)
ws = wb.sheets[0]

ws.tables.add(ws.used_range, name="a_table")
ws.tables["a_table"].show_autofilter = True
# This way adds formatting in addition to the filter.