如何用Python动态过滤Excel工作簿（不使用VBA）并关联下拉筛选器？

嘿，我明白你的需求啦——已经用xlsxwriter搞定了Cabin的下拉菜单，现在想把原Cabin列藏起来，让用户选完下拉选项后，整个数据集自动跟着过滤，而且还不想用VBA，对吧？

刚好有两种实用方案，分别适配不同版本的Excel，咱们一步步来：

方案一：适配Excel 365/2021+的动态自动过滤（推荐） #

这个方案用Excel的FILTER动态数组函数，用户选完下拉选项后，结果会自动实时更新，不用手动操作，体验最好。

代码实现 #

import pandas as pd
import xlsxwriter

# 1. 准备你的数据集（这里用示例数据代替）
data = {
    'Name': ['Alice', 'Bob', 'Charlie', 'David'],
    'Cabin': ['eco', 'bus', 'eco', 'bus'],
    'Price': [100, 200, 150, 250],
    'Date': ['2024-01-01', '2024-01-02', '2024-01-03', '2024-01-04']
}
df = pd.DataFrame(data)

# 2. 创建Excel写入器
writer = pd.ExcelWriter('cabin_filter.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Filter', index=False)
workbook = writer.book
worksheet = writer.sheets['Filter']

# 3. 设置下拉筛选器（放在F1单元格）
worksheet.write('E1', '选择舱型：')
worksheet.data_validation('F1', {
    'validate': 'list',
    'source': ['eco', 'bus'],
    'input_title': '选择舱型',
    'input_message': '从下拉列表中挑选舱型'
})

# 4. 用FILTER函数实现动态过滤（结果从A7开始展示）
worksheet.write('A6', '过滤后结果')
# 公式解释：筛选A1:D5区域中，B列（Cabin）等于F1下拉值的行，无匹配时显示"无匹配数据"
filter_formula = '=FILTER(A1:D5, B1:B5=F1, "无匹配数据")'
worksheet.write_formula('A7', filter_formula)

# 5. 隐藏原Cabin列和原始数据区域（可选，让界面更简洁）
# 隐藏原Cabin列（B列）
worksheet.set_column('B:B', None, None, {'hidden': True})
# 隐藏原始数据行（1-5行），只留筛选控件和结果
for row in range(5):
    worksheet.set_row(row, None, None, {'hidden': True})

# 6. 保存文件
writer.close()

怎么用？ #

用户打开生成的Excel文件后，直接点击F1的下拉菜单选择舱型，下面的结果区域会自动刷新显示对应的数据，完全不用手动操作~

方案二：兼容旧版Excel的高级筛选（无动态更新） #

如果用户用的是Excel 2019及更早版本，不支持FILTER函数，那就用Excel的高级筛选功能，虽然需要手动触发筛选，但完全不用VBA。

代码实现 #

import pandas as pd
import xlsxwriter

# 1. 准备数据集
data = {
    'Name': ['Alice', 'Bob', 'Charlie', 'David'],
    'Cabin': ['eco', 'bus', 'eco', 'bus'],
    'Price': [100, 200, 150, 250],
    'Date': ['2024-01-01', '2024-01-02', '2024-01-03', '2024-01-04']
}
df = pd.DataFrame(data)

# 2. 创建Excel写入器
writer = pd.ExcelWriter('cabin_filter_legacy.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Filter', index=False)
workbook = writer.book
worksheet = writer.sheets['Filter']

# 3. 设置下拉筛选器（F1单元格）
worksheet.write('E1', '选择舱型：')
worksheet.data_validation('F1', {
    'validate': 'list',
    'source': ['eco', 'bus'],
    'input_title': '选择舱型',
    'input_message': '从下拉列表中挑选舱型'
})

# 4. 设置高级筛选的条件区域（G1:G2）
# G1写列名"Cabin"，G2引用下拉单元格F1的值
worksheet.write('G1', 'Cabin')
worksheet.write_formula('G2', '=F1')

# 5. 把原始数据转换成Excel表格（方便筛选操作）
row_count, col_count = df.shape
worksheet.add_table(0, 0, row_count, col_count - 1, {
    'name': 'CabinData',
    'style': 'Table Style Medium 2'
})

# 6. 隐藏原Cabin列（B列）
worksheet.set_column('B:B', None, None, {'hidden': True})

# 7. 添加操作提示
worksheet.write('H1', '操作提示：选完舱型后，点击「数据」选项卡→「高级」，')
worksheet.write('H2', '选择条件区域为G1:G2，即可查看过滤结果')

# 8. 保存文件
writer.close()

怎么用？ #

用户打开文件后，选完下拉舱型，按照提示点击「数据」→「高级」，在弹出的窗口里确认条件区域是G1:G2，点击确定就能看到过滤后的数据集了。

备注：内容来源于stack exchange，提问作者user22083723