Table Auto Filter Columns are not saved when saving a worksheet for the first time
*Created originally on Bitbucket by [A (Alex Spurling)](https://bitbucket.org/%7B6c71bb62-6f6e-4243-b08b-07e7086e6789%7D/)* 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) ```
issue