如何使用 openpyxl 自动筛选 Excel 数据？AutoFilter 对象介绍

openpyxl Excel 自动筛选对象 AutoFilter

openpyxl包的AutoFilter类，属于模块openpyxl.worksheet.filters，用于表示 Excel 工作表的自动筛选功能。由于自动筛选功能相对复杂，因此，openpyxl.worksheet.filters模块还包含了FilterColumn，Filters等重要的类，他们对应了 Excel 工作表中某个列的自动筛选。

获取和设置 Excel 工作表中的自动筛选

Worksheet对象的auto_filter变量是一个AutoFilter对象，他对应了 Excel 工作表中的自动筛选功能。

worksheet.auto_filter

获取和设置自动筛选的 Excel 区域

AutoFilter对象的ref变量可以是一个字符串，用于表示 Excel 工作表中进行自动筛选的区域，比如A1:C1表示对工作表的前三列进行自动筛选。需要指出的是，一些 Office 软件仅会考虑ref包含的列，不会考虑包含了多少行，因此，区域A1:C3与区域A1:C1的效果是相同的。

worksheet.auto_filter.ref
worksheet.auto_filter.ref = range

range 值

range是表示需要进行自动筛选的区域的字符串，比如A1:C1。

# 打开 Data.xlsx 中的工作表 Trees
from openpyxl import load_workbook
workbook = load_workbook('Data.xlsx')
worksheet = workbook['Trees']


# 设置对 A，B 两列进行自动筛选
worksheet.auto_filter.ref = 'A1:B1'


workbook.save('Ref.xlsx')

为 Excel 列创建自动筛选

openpyxl包的FilterColumn类，属于模块openpyxl.worksheet.filters，用于表示 Excel 工作表中某个列的自动筛选功能，你可以创建一个FilterColumn对象，并将其添加至工作表对象中，以设置相关列的自动筛选，具体可以参考为列添加自动筛选一段。

以下是FilterColumn类的构造器。

FilterColumn(colId=None, hiddenButton=False, showButton=True, filters=None, top10=None, customFilters=None, dynamicFilter=None, colorFilter=None, iconFilter=None, extLst=None, blank=None, vals=None)

colId 参数

colId参数为列的索引，0表示第一列。

hiddenButton 参数

hiddenButton参数的作用尚不明确。

showButton 参数

showButton参数表示是否在相关的列显示筛选按钮，如果设置为True，则显示筛选按钮。

filters 参数

filters参数是一个表示普通筛选条件的Filters对象。

top10 参数

top10参数是一个表示前十筛选条件的Top10对象。

customFilters 参数

customFilters参数是一个表示自定义筛选条件的CustomFilters对象。

dynamicFilter 参数

dynamicFilter参数是一个表示动态筛选条件的DynamicFilter对象。

colorFilter 参数

colorFilter参数是一个表示颜色筛选条件的ColorFilter对象。

iconFilter 参数

iconFilter参数是一个表示图标筛选条件的IconFilter对象。

extLst 参数

extLst参数的作用尚不明确。

blank 参数

blank参数表示是否将空单元格视为符合筛选条件，如果设置为True，则会将空单元格视为符合条件。该参数将覆盖filters参数所表示的Filters对象的blank变量。

vals 参数

vals参数是一个 Python 序列对象，如果某个单元格的值与序列中的某个项相同，那么该单元格是符合筛选条件的。该参数将覆盖filters参数所表示的Filters对象的filter变量。

FilterColumn对象的构造器涉及到了名称为Filters的类，该类属于模块openpyxl.worksheet.filters，用于表示 Excel 自动筛选的具体筛选条件，其构造器如下。

Filters(blank=None, calendarType=None, filter=(), dateGroupItem=())

blank 参数

blank参数表示是否将空单元格视为符合筛选条件，如果设置为True，则会将空单元格视为符合条件。

calendarType 参数

calendarType参数的作用尚不明确。

filter 参数

filter参数是一个 Python 序列对象，如果某个单元格的值与序列中的某个项相同，那么该单元格是符合筛选条件的。

dateGroupItem 参数

dateGroupItem参数的作用尚不明确。

为 Excel 列添加自动筛选

通过AutoFilter对象的add_filter_column方法，可以简单的为 Excel 工作表中的某个列添加自动筛选。

worksheet.auto_filter.add_filter_column(col_id, vals, blank=False)

col_id 参数

colId参数为列的索引，0表示第一列。

vals 参数

vals参数是一个 Python 序列对象，如果某个单元格的值与序列中的某个项相同，那么该单元格是符合筛选条件的。

blank 参数

blank参数表示是否将空单元格视为符合筛选条件，如果设置为True，则会将空单元格视为符合条件。

通过AutoFilter对象的filterColumn.append方法，可以为 Excel 工作表中的某个列添加更为复杂的自动筛选。

worksheet.auto_filter.filterColumn.append(filterColumn)

filterColumn 参数

filterColumn参数是一个FilterColumn对象，表示了某个列的自动筛选功能。

# 打开 Data.xlsx 中的工作表 Trees
from openpyxl import load_workbook
wb = load_workbook('Data.xlsx')
ws = wb['Trees']


from openpyxl.worksheet.filters import FilterColumn, Filters
af = ws.auto_filter


# 为第一列添加自动筛选
f = Filters(filter=('苹果树', '梨树'))
# 将覆盖之前在 Filters 中设置的 filter
fc = FilterColumn(0, filters=f, blank=True, vals=('大树', '柳树'))
af.filterColumn.append(fc)


# 选出第四列中值为 3，3.5 的单元格或空单元格
af.add_filter_column(3, [3, 3.5], True)


# 将第一和第四列包含在进行自动筛选的区域中
af.ref = 'A1:D1'
# 在保存的 Excel 文件中，可能需要重新应用自动筛选才能看到效果
wb.save('Add.xlsx')

# 打开 Data.xlsx 中的工作表 Trees
from openpyxl import load_workbook
workbook = load_workbook('Data.xlsx')
worksheet = workbook['Trees']


af = worksheet.auto_filter


# 选出第二列中值为 20 的单元格或空单元格
af.add_filter_column(1, [20], True)
# 选出第二列中值为 15 的单元格
af.add_filter_column(1, [15], False)


workbook.save('AddMulti.xlsx')

为 Excel 列修改或移除（删除）自动筛选

openpyxl包并没有直接提供用于修改或移除（删除）Excel 列中的自动筛选的方法或函数，要完成此目标，你可以遍历AutoFilter对象的filterColumn变量所表示的 Python 列表，对其中符合条件的FilterColumn对象进行修改或移除。

# 打开 Data.xlsx 中的工作表 Teachers
from openpyxl import load_workbook
workbook = load_workbook('Data.xlsx')
worksheet = workbook['Teachers']


# 选出与第二列有关的所有 FilterColumn 对象
fcs = worksheet.auto_filter.filterColumn
del_fcs = [x for x in fcs if x.colId == 1]


# 移除与第二列有关的 FilterColumn 对象
for fc in del_fcs:
	fcs.remove(fc)


# 修改第一列的 FilterColumn 对象
for fc in fcs:
	if fc.colId == 0:
		fc.filters.filter = ('大刚', '小刚')


# 在保存的 Excel 文件中，可能需要重新应用自动筛选才能看到效果
workbook.save('Change.xlsx')

为 Excel 列创建自定义筛选

使用FilterColumn类的构造器中的customFilters参数，你可以为 Excel 列创建自定义筛选，以设置较为复杂的筛选条件，比如，值大于1并小于10的单元格。

参数customFilters是类CustomFilters的实例，该类属于模块openpyxl.worksheet.filters，用于表示一组自定义筛选条件，而组中的每一个自定义筛选条件均对应了CustomFilter类或其派生类的实例。

CustomFilters(_and=None, customFilter=())

_and 参数

_and参数表示自定义筛选条件（CustomFilter对象）之间的逻辑关系，如果设置为True，则自定义筛选条件之间的逻辑关系为and（与），否则为or（或）。需要说明的是，当你设置了多个筛选条件，并且这些筛选条件的逻辑关系既有and也有or时，那么某些筛选条件可能无法生效或被保存。

customFilter 参数

customFilter参数是一个 Python 序列对象，该对象包含一组表示自定义筛选条件的CustomFilter对象。

CustomFilter(operator='equal', val=None)

operator 参数

operator参数是一个字符串，表示了与自定义筛选条件相关的运算方式，可以是以下取值之一。'equal'（等于），'lessThan'（小于），'lessThanOrEqual'（小于等于），'notEqual'（不等于），'greaterThanOrEqual'（大于等于），'greaterThan'（大于）。

val 参数

val参数是表示与自定义筛选条件相关的值的字符串，该字符串可以针对单元格中的数字或文字内容，比如，如果需要选出数字大于3的单元格，则val参数可以被设置为'3'，如果需要选出文字等于小明的单元格，则val参数可以被设置为'小明'。

# 打开 Data.xlsx 中的工作表 Games
from openpyxl import load_workbook
wb = load_workbook('Data.xlsx')
ws = wb['Games']


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


# 选出数字内容大于 9 并且 小于等于 11 的 Excel 单元格
cfs = CustomFilters(True, [
	CustomFilter('greaterThan', '9'),
	CustomFilter('lessThanOrEqual', '11')
	])


# 为工作表的第二列设置自定义筛选
fc = FilterColumn(1, customFilters=cfs)
ws.auto_filter.filterColumn.append(fc)


# 选出第一列中文字内容不等于 疯狂自行车 的 Excel 单元格
cfs = CustomFilters(customFilter=[CustomFilter('notEqual', '疯狂自行车')])
ws.auto_filter.filterColumn.append(FilterColumn(0, customFilters=cfs))


# 在保存的 Excel 文件中，可能需要重新应用自动筛选才能看到效果
wb.save('Custom.xlsx')

openpyxl包的NumberFilter是CustomFilter的派生类，虽然CustomFilter也可以完成数字运算，但NumberFilter更为明确。

NumberFilter(operator='equal', val=None)

operator 参数

operator参数与CustomFilter构造器中的operator参数的作用相同。

val 参数

val参数与CustomFilter构造器中的val参数的作用类似，但他是浮点数类型。

openpyxl包的StringFilter是CustomFilter的派生类，用于完成较为复杂的字符串运算。

StringFilter(operator='contains', val=None, exclude=False)

operator 参数

operator参数与CustomFilter构造器中的operator参数的作用类似，可以是以下取值之一。'contains'（包含某些字符串），'startswith'（以某些字符串开始），'endswith'（以某些字符串结束），'wildcard'（采用通配符）。

val 参数

val参数与CustomFilter构造器中的val参数的作用相同。

exclude 参数

exclude参数是一个布尔值，如果为True，则将运算结果为假的单元格视为是符合条件的。

openpyxl包的BlankFilter是CustomFilter的派生类，用于排除 Excel 列中的空单元格。

BlankFilter(**kw)

# 打开 Data.xlsx 中的工作表 Games
from openpyxl import load_workbook
wb = load_workbook('Data.xlsx')
ws = wb['Games']


from openpyxl.worksheet.filters import FilterColumn, CustomFilters, NumberFilter, StringFilter


# 选出数字内容大于等于 10 或 小于等于 11 的 Excel 单元格
cfs = CustomFilters(True, [
	NumberFilter('greaterThanOrEqual', 10),
	NumberFilter('lessThanOrEqual', 11)
	])


# 为工作表的第二列设置自定义筛选
fc = FilterColumn(1, customFilters=cfs)
ws.auto_filter.filterColumn.append(fc)


# 选出第一列中文字内容不包含 不 的 Excel 单元格
cfs = CustomFilters(customFilter=[StringFilter('contains', '不', True)])
ws.auto_filter.filterColumn.append(FilterColumn(0, customFilters=cfs))


# 在保存的 Excel 文件中，可能需要重新应用自动筛选才能看到效果
wb.save('NumStr.xlsx')

为 Excel 列创建包含通配符的自定义筛选

在使用CustomFilter或StringFilter类为 Excel 列创建自定义筛选的过程中，你可以为其构造器的val参数指定通配符，比如，*表示多个任意字符，?表示单个任意字符。而要使通配符产生预期效果，CustomFilter和StringFilter对象需要被指定正确的operator参数，比如，为CustomFilter指定'equal'或'notEqual'，为StringFilter指定'wildcard'。

此外，CustomFilter和StringFilter对象中的通配符不仅可以匹配 Excel 单元格中的文字内容，也可以匹配单元格中的数字内容。

# 打开 Data.xlsx 中的工作表 Games
from openpyxl import load_workbook
wb = load_workbook('Data.xlsx')
ws = wb['Games']


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


# 选出第一列中文字内容包含 自?车 的单元格
cfs = CustomFilters(customFilter=[CustomFilter('equal', '*自?车*')])
ws.auto_filter.filterColumn.append(FilterColumn(0, customFilters=cfs))


# 选出第二列中包含个位数的单元格
cfs = CustomFilters(customFilter=[StringFilter('wildcard', '?')])
ws.auto_filter.filterColumn.append(FilterColumn(1, customFilters=cfs))


# 在保存的 Excel 文件中，可能需要重新应用自动筛选才能看到效果
wb.save('Wildcards.xlsx')

源码

src/zh/openpyxl/data/filters·codebeatme/office-programming·GitHub