如何使用 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-hant/openpyxl/data/filters·codebeatme/office-programming·GitHub