Skip to content
GitLab
Projects Groups Topics Snippets
  • /
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Register
  • Sign in
  • O openpyxl
  • Project information
    • Project information
    • Activity
    • Labels
    • Members
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributor statistics
    • Graph
    • Compare revisions
  • Issues 282
    • Issues 282
    • List
    • Boards
    • Service Desk
    • Milestones
  • Merge requests 7
    • Merge requests 7
  • CI/CD
    • CI/CD
    • Pipelines
    • Jobs
    • Schedules
  • Deployments
    • Deployments
    • Environments
    • Releases
  • Packages and registries
    • Packages and registries
    • Container Registry
  • Monitor
    • Monitor
    • Incidents
  • Analytics
    • Analytics
    • Value stream
    • CI/CD
    • Repository
  • Snippets
    • Snippets
  • Activity
  • Graph
  • Create a new issue
  • Jobs
  • Commits
  • Issue Boards
Collapse sidebar

Due to a massive storm of spam, registration on this instance now requires explicit administrator approval. Sorry for the added friction, we're also looking into automatic filtering options.

  • openpyxl
  • openpyxl
  • Issues
  • #1156
Closed
Open
Issue created Dec 24, 2018 by Bitbucket Importer@bitbucket_importerOwner

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)
Edited Jul 13, 2022 by CharlieC
To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information
Assignee
Assign to
Time tracking