Filter column in Excel based on cell color

[CoyoteRay]

Trying to use Python to filter a column in Excel based on the cell foreground color using openpyxl. Read the openpyxl documents many times to no avale. According to the documentation openpyxl.worksheet.filters.ColorFilter(dxfId=None, cellColor=None) is what I should use however I cannot seem to get it to work, any help or pointer would be greatly appreciated. I can get text filtering to work, just not color.

Example code

import openpyxl

wb = openpyxl.Workbook()
ws = wb.active

data = [
    ["Fruit", "Quantity"],
    ["Kiwi", 3],
    ["Grape", 15],
    ["Apple", 3],
    ["Peach", 3],
    ["Pomegranate", 3],
    ["Pear", 3],
    ["Tangerine", 3],
    ["Blueberry", 3],
    ["Mango", 3],
    ["Watermelon", 3],
    ["Blackberry", 3],
    ["Orange", 3],
    ["Raspberry", 3],
    ["Banana", 3]
]

for r in data:
    ws.append(r)

ws.auto_filter.ref = "A1:B15"
ws['A2'].fill = openpyxl.styles.PatternFill(fgColor="F0F0F0", fill_type="solid")

# Tried:
# ws = openpyxl.worksheet.filters.ColorFilter(0, cellColor="F0F0F0")
# ws.worksheet.filters.ColorFilter(0, cellColor="F0F0F0")
# ws.color_filter(0, cellColor="F0F0F0")
# ... Many others

# Text filter works:
# ws.auto_filter.add_filter_column(0, ["Kiwi", "Apple", "Mango"])  # This works

wb.save("filtered.xlsx")

[Charlie Clark]

On 20 Apr 2020, at 18:25, CoyoteRay wrote:

> Trying to use Python to filter a column in Excel based on the cell
> foreground color using openpyxl. Read the openpyxl documents many
> times to
> no avale. According to the documentation
> openpyxl.worksheet.filters.ColorFilter(dxfId=None, cellColor=None) is
> what
> I should use however I cannot seem to get it to work, any help or
> pointer
> would be greatly appreciated. I can get text filtering to work, just
> not
> color.

As with all these things, the specification only tells you so much and
sometimes you just have no choice but to compare the XML that Excel
generates to see what you need to.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226