CODEWITHSUNDEEP
pythonexcelopenpyxl
Slavi
Slavi

Reputation: 130

Create a visual filter in excel using Python - openpyxl

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?

Upvotes: 0

Views: 4417

Answers (2)

Justin Malinchak
Justin Malinchak

Reputation: 557

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

Upvotes: 2

Charlie Clark
Charlie Clark

Reputation: 19507

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'

Upvotes: 2

Related Questions