Table Auto Filter Columns are not saved when saving a worksheet for the first time

Issue #1156 new
Alex Spurling
created an issue

When you add a filter column to a table's Auto Filter, it will be overwritten the first time that your worksheet is saved. If you add it after the first save, then the Auto Filter will be saved correctly. Here is an example where I am creating a new Workbook, a new AutoFilter with a FilterColumn which I then add to a Table. When I save this workbook the first time you can see that the filter columns gets removed. When I save it for a second time, it is retained. I have tracked down the piece of code responsible which you can also see below:

import openpyxl
from openpyxl.cell import Cell
from openpyxl.worksheet.table import Table
from openpyxl.worksheet.filters import AutoFilter, Filters, FilterColumn

# Is use to create a reference of the Excel to wb
wb = openpyxl.Workbook()
ws =

ws['A1'] = "Numbers"  # Header row

for x in range(2, 21):
    cell = Cell(ws, column="A", row=x, value=x)
    ws.append([cell]), x, hidden=(x!=16))  # Hide the row if it is not equal to 16

table = Table(ref="A1:A20", displayName="TableName")
filters = Filters(blank=False, filter=["16"])  # Filter out values that are not equal to 16
col = FilterColumn(colId=0, filters=filters)
auto_filter = AutoFilter("A1:A20", [col])
table.autoFilter = auto_filter


xlsx_file = "table_filter_test.xlsx"

wb2 = openpyxl.load_workbook(xlsx_file)

print("Table autofilter after first save: {}".format([0].autoFilter))

table.autoFilter = auto_filter

wb3 = openpyxl.load_workbook(xlsx_file)

print("Table autofilter after second save: {}".format([0].autoFilter))

Here is the output of the above:

Table autofilter after first save: <openpyxl.worksheet.filters.AutoFilter object>
ref='A1:A20', filterColumn=[], sortState=None
Table autofilter after second save: <openpyxl.worksheet.filters.AutoFilter object>
ref='A1:A20', filterColumn=[<openpyxl.worksheet.filters.FilterColumn object>
colId=0, hiddenButton=None, showButton=None, filters=<openpyxl.worksheet.filters.Filters object>
blank=False, calendarType=None, filter=['16'], dateGroupItem=[], top10=None, customFilters=None, dynamicFilter=None, colorFilter=None, iconFilter=None], sortState=None

Here is the piece of code in which overwrites any existing autoFilter when _initialise_columns is called upon save:

    def _initialise_columns(self):
        Create a list of table columns from a cell range
        Always set a ref if we have headers (the default)
        Column headings must be strings and must match cells in the worksheet.

        min_col, min_row, max_col, max_row = range_boundaries(self.ref)
        for idx in range(min_col, max_col+1):
            col = TableColumn(id=idx, name="Column{0}".format(idx))
        if self.headerRowCount:
            self.autoFilter = AutoFilter(ref=self.ref)

Comments (4)

  1. CharlieC

    Thanks for the report. So you just want autoFilter not to be overriden if it exists in a table? The private method was written to do just enough for Excel to be happy with the result if people create tables.

  2. Alex Spurling reporter

    Yes, I guess if there is already an autoFilter on a table, it should not be overwritten. However, it might also be useful to have a function such as add_filter_column on a table which does the necessary initialisation for filters on tables.

  3. Log in to comment