Is there a way to add autofilter to all columns using xlsxwriter without specifying a column range?

Question

I have a dataframe which I am writing to excel using xlsxwriter and I want there to be autofilter applied to all columns where the header is not blank in my spreadsheet without having to specify a range (e.g. A1:D1). Is there any way to do this?

Answer 1

You will need to specify the range in some way but you can do it programatically based on the shape() of the data frame.

For example:

import xlsxwriter
import pandas as pd

df = pd.DataFrame({'A' : [1, 2, 3, 4, 5, 6, 7, 8],
                   'B' : [1, 2, 3, 4, 5, 6, 7, 8],
                   'C' : [1, 2, 3, 4, 5, 6, 7, 8],
                   'D' : [1, 2, 3, 4, 5, 6, 7, 8]})

writer = pd.ExcelWriter('test.xlsx', engine = 'xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Apply the autofilter based on the dimensions of the dataframe.
worksheet.autofilter(0, 0, df.shape[0], df.shape[1])

workbook.close()
writer.save()

Output: