Create a visual filter in excel using Python - openpyxl

Question

I am trying to create a filter in excel programatically, so when the sheet is created with openpyxl, the first row of each sheet will already be set to a be a filter. I've looked at the docs but all I can find is how to filter data not to create a filter.

Is it even possible with the current implementation of openpyxl?

Take a look at xlsxwriter, it appears to have the functionality you're looking for — wnnmaw
– wnnmaw, Commented Apr 1, 2016 at 13:21

Charlie Clark · Accepted Answer · 2016-04-01 15:32:49Z

2

openpyxl does support filters. See the worksheet.filters module and the associated tests.

Sample of what you can do:

    ws.auto_filter.ref = 'C1:G9'

answered Apr 1, 2016 at 15:32

Charlie Clark

19.7k4 gold badges56 silver badges64 bronze badges

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

2 Comments

Slavi Over a year ago

Exactly, thanks! P.S if anyone wonders,it can be helpful to use max column to use the filter for the entire row. Use openpyxl.utils._get_column_letter(worksheet.max_column)

Charlie Clark Over a year ago

Why are you using the private function? Use get_column_letter() from the utils module.

Justin Malinchak · Accepted Answer · 2018-07-19 15:18:19Z

2

Copy and paste this into a .py file and run.

import pandas as pd
import numpy as np

# Here is an example dataframe
df_example = pd.DataFrame(np.random.randint(0,100,size=(100, 4)), columns=list('ABCD'))

# Create xlsx file
filepath = 'mytempfile.xlsx'
with pd.ExcelWriter(filepath, engine='xlsxwriter') as writer:
    df_example.to_excel(writer, sheet_name='Sheet1',index=False)

# Add filter feature to first row
import openpyxl
xfile = openpyxl.load_workbook(filepath)
sheet = xfile.get_sheet_by_name('Sheet1')
maxcolumnletter = openpyxl.utils.get_column_letter(sheet.max_column)
sheet.auto_filter.ref = 'A1:'+maxcolumnletter+str(len(sheet['A']))

# Save the file
xfile.save(filepath)
print 'your file:',filepath

answered Jul 19, 2018 at 15:18

Justin Malinchak

5671 gold badge6 silver badges12 bronze badges

Collectives™ on Stack Overflow

Create a visual filter in excel using Python - openpyxl

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related