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

Created originally on Bitbucket by A (Alex Spurling)

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:

#!python

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 = wb.active

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

for x in range(2, 21):
    cell = Cell(ws, column="A", row=x, value=x)
    ws.append([cell])
    ws.row_dimensions.group(x, 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

ws.add_table(table)

xlsx_file = "table_filter_test.xlsx"
wb.save(xlsx_file)

wb2 = openpyxl.load_workbook(xlsx_file)

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

table.autoFilter = auto_filter

wb.save(xlsx_file)

wb3 = openpyxl.load_workbook(xlsx_file)

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

Here is the output of the above:

Table autofilter after first save: <openpyxl.worksheet.filters.AutoFilter object>
Parameters:
ref='A1:A20', filterColumn=[], sortState=None
Table autofilter after second save: <openpyxl.worksheet.filters.AutoFilter object>
Parameters:
ref='A1:A20', filterColumn=[<openpyxl.worksheet.filters.FilterColumn object>
Parameters:
colId=0, hiddenButton=None, showButton=None, filters=<openpyxl.worksheet.filters.Filters object>
Parameters:
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 table.py which overwrites any existing autoFilter when _initialise_columns is called upon save:

#!python



    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))
            self.tableColumns.append(col)
        if self.headerRowCount:
            self.autoFilter = AutoFilter(ref=self.ref)