Excel筛选公式无法识别Pandas写入的表格行问题求助

在Excel的一个工作表中创建了结构化表格，第二个工作表使用筛选公式引用该表格。手动向第一个工作表的表格添加数据时，筛选公式能得到正确结果；但通过Pandas代码写入新行后，筛选公式无法识别这些新增行。

import pandas as pd
def data1():

    list_auto = ['nissan','mazda','bmw','lexus','bmw']
    list_price = [100,388,455,555,265]
    col1 = {
        'auto':list_auto,
        'price':list_price
    }

    df1 = pd.DataFrame(col1)
    df1.to_excel('/Users/18m0011/parse/auto1.xlsx', sheet_name = 'home', index=False)


data1()

def data2():

    list_auto2 = ['nissan','mazda','mersedes','bmw','bmw']
    list_price2 = [100,388,455,555,265]
    col2 = {
        'auto':list_auto2,
        'price':list_price2
    }

    df2 = pd.DataFrame(col2)
    with pd.ExcelWriter('/Users/18m0011/parse/auto1.xlsx', engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer:
                df2.to_excel(writer, sheet_name='home',index = False, header= False, startrow = writer.sheets['home'].max_row)
    print(df2)

# data2()

核心原因 #

Pandas写入数据仅填充单元格内容，但不会自动更新Excel结构化表格（List Object）的区域范围。Excel的结构化表格有固定的关联区域，手动添加数据时Excel会自动扩展该区域，而Pandas没有这个机制，导致筛选公式仍基于原区域计算。

代码修复方案 #

使用openpyxl直接操作Excel的表格对象，扩展其区域范围，确保新增行被纳入表格：

import pandas as pd
from openpyxl import load_workbook

def data2():
    list_auto2 = ['nissan','mazda','mersedes','bmw','bmw']
    list_price2 = [100,388,455,555,265]
    col2 = {'auto':list_auto2, 'price':list_price2}
    df2 = pd.DataFrame(col2)
    
    file_path = '/Users/18m0011/parse/auto1.xlsx'
    # 第一步：写入新增数据
    with pd.ExcelWriter(file_path, engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer:
        start_row = writer.sheets['home'].max_row
        df2.to_excel(writer, sheet_name='home', index=False, header=False, startrow=start_row)
    
    # 第二步：加载工作簿并更新表格区域
    wb = load_workbook(file_path)
    ws = wb['home']
    # 替换成你实际的表格名称（可在Excel「表格设计」选项卡查看）
    table_name = 'Table1'
    table = ws.tables[table_name]
    
    # 计算新的表格区域范围
    new_end_row = ws.max_row
    start_ref, end_ref = table.ref.split(':')
    # 保持列不变，更新结束行号
    new_end_ref = end_ref[0] + str(new_end_row)
    table.ref = f"{start_ref}:{new_end_ref}"
    
    # 保存修改
    wb.save(file_path)
    wb.close()
    print(df2)

临时手动方案 #

如果不需要自动化处理，写入数据后打开Excel：

• 选中结构化表格的任意单元格
• 点击「表格设计」选项卡 → 「调整表格大小」
• 拖动选区包含新增的行，确认后筛选公式即可识别新增数据

内容的提问来源于stack exchange，提问作者Александр Цветков