使用Openpyxl通过筛选按列值隐藏行时遇错误求助

想要实现的功能：当特定列的值大于指定数字时隐藏对应行，使用Openpyxl完成。已查阅大量资料但文档描述模糊，编写的代码运行后保存的文件打开时出错。

错误提示 #

在文件'D:\filtered.xlsx'中检测到错误
已移除功能：/xl/worksheets/sheet1.xml部分中的自动筛选

当前编写的代码 #

from openpyxl import Workbook
from openpyxl.worksheet.filters import (
    FilterColumn,
    CustomFilter,
    CustomFilters,
    DateGroupItem,
    Filters,
    )

wb = 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)

filters = ws.auto_filter
filters.ref = "B1:B15"

flt2 = CustomFilter(operator='greaterThan', val='3')

cfs = CustomFilters(customFilter=[flt2])
col = FilterColumn(colId=1, customFilters=cfs) 
filters.filterColumn.append(col)

wb.save("filtered.xlsx")

解决方案 #

文件报错的核心原因是自定义筛选器参数格式错误，且Openpyxl的auto_filter仅负责设置Excel的筛选规则，不会自动执行筛选并隐藏行，需要手动遍历行来设置隐藏状态。

修改后的代码：

from openpyxl import Workbook
from openpyxl.worksheet.filters import FilterColumn, CustomFilter, CustomFilters

wb = 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)

# 可选：设置自动筛选规则，让Excel界面显示筛选条件
filters = ws.auto_filter
filters.ref = "A1:B15"  # 筛选范围需包含表头
flt = CustomFilter(operator='greaterThan', val=3)  # val使用数字类型而非字符串
cfs = CustomFilters(customFilter=[flt])
col = FilterColumn(colId=1, customFilters=cfs)
filters.filterColumn.append(col)

# 手动遍历行，隐藏符合条件的行（跳过表头行）
threshold = 3
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
    quantity_cell = row[1]  # B列对应索引1
    if quantity_cell.value > threshold:
        ws.row_dimensions[quantity_cell.row].hidden = True

wb.save("filtered.xlsx")

关键修改点：

• 将val='3'改为val=3，匹配数值类型避免Excel解析错误
• 筛选范围调整为包含表头的A1:B15，符合Excel自动筛选规范
• 通过ws.row_dimensions[row_num].hidden = True直接设置行隐藏状态，这是Openpyxl实现行隐藏的有效方式
• 自动筛选规则为可选内容，若不需要在Excel界面显示筛选条件，可直接省略这部分代码，仅保留遍历隐藏行的逻辑

内容的提问来源于stack exchange，提问作者Umair Ayub