How to cancel auto filter on table with openpyxl

Question

The title said it all :)

But still, I'm using the class Table from openpyxl.worksheet.table to define a table in excel file which I create. My problem is that the table that is created has Filter on the first row that I want to remove (from the script, not by opening the Excel file).

This is the calling for Table class:

tab = Table(displayName='Table_{}'.format(table_name.replace(' ', '_')),
                                          ref="{}:{}".format(table_start, table_end))

This is what I get:

This is what I want to get:

I search for it at OpenPyXL Docs but find only adding that filtering...

There is any way to remove this?

Many thanks!

Fieldsman

This is more a question about the OOXML specification than openpyxl and I don't see anything in it that makes specific reference to this. You're best off comparing the XML for the relevant tables. – Afton

Answer 1

wb = load_workbook(filename="data.xlsx")
ws = wb["Sheet1"]
ws.auto_filter.ref = None
for i in range(2, ws.max_row + 1):
    ws.row_dimensions[i].hidden = False
wb.save("data.xlsx")

Reims

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. – Curlew

Answer 2

I just ran across this and here is how I solved it.

#Opens the file
wb = load_workbook(filename = 'somefile')

#Set worksheet
ws = wb['sheetname'] # Tab name

#set table
tbl = ws.tables["tablename"]

#removes all filters 
tbl.autoFilter.filterColumn.clear()

Piotrowski

for me after creating a table and sheet.add_table(tab1), I got tab1.autoFilter is None – Gifferd

Answer 3

The documentation actually claims that "Filters" are required for Tables with headers. In any case some reverse engineering can get us there. I found FilterColumn controls this setting with a property called hiddenButton. When it is None the filter buttons show up on all columns. However, we can turn them off on any column we choose as shown below:

from openpyxl.worksheet.filters import FilterColumn, AutoFilter

# get table
tbl = ws.tables["tablename"]

# set up filter for each column
indexes = [1,2,5]
fc = [FilterColumn(colId=col, hiddenButton=True) for col in indexes]

# set autoFilter on table
table.autoFilter = AutoFilter(ref=table.ref, filterColumn=fc)

Setting hiddenButton = True will turn the filter button off. I don't see another solution that will work if you are authoring a new Table so this should solve that.

Tailstock

Answer 4

helo,

tab = Table( displayName='Table_{}'.format(table_name.replace(' ', '_'))
       , ref="{}:{}".format(table_start, table_end)
       , headerRowCount = 0 # default is 1
       )

Mckale

Please consider adding a little bit of explanation what exactly your answer is doing, and where your improvements over OPs method are. – Mathewson

This doesn't solve the problem, it just turns the header row into a regular row. – Galwegian