Write dataframe to excel with filter on

Question

I am trying to write my dataframe to excel and would like to do it so that the filter option based on the first row in the dataframe is automatically selected when I open excel. I can obviously manually select filter in excel but was wondering if there is a way to code this in python?

Result would be that I open excel file containing dataframe and the filter option is already selected.

I've seen that there is an option via openpyxl but this seems to require knowing the references for the cells in excel over which you want to filter. This is not handy when the dataframe can differ in size so you will never be sure over which columns and rows you want to filter. — jyh5
– jyh5, Commented Sep 13, 2019 at 9:45

Tox · Accepted Answer · 2019-09-13 11:41:49Z

5

You can use openpyxl as you already tried to.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

data = [
    ["Fruit", "Quantity"],
    ["Kiwi", 3],
    ["Grape", 15],
    ["Apple", 3],
    ["Peach", 3],
]

for r in data:
    ws.append(r)

ws.auto_filter.ref = "A:B"

wb.save("filtered.xlsx")

The ws.auto_filter.ref = "A:B" sets the filter on the column name. More information can be found here.

edited Sep 13, 2019 at 11:41

answered Sep 13, 2019 at 11:17

Tox

8543 gold badges14 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jyh5 Over a year ago

Thanks, this is the solution I was looking for.

Pythn Over a year ago

Can xlsxwriter also do this? Already made all my exports with xlsxwriter. If not, is there a way to combine the 2? Would love to get an example. Thank you

Collectives™ on Stack Overflow

Write dataframe to excel with filter on

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related