Pandas追加DataFrame到Excel已有工作表报错 求可行解决方案

方案1：升级Pandas版本（推荐，性能最优） #

Pandas 1.4.0及以上版本新增了if_sheet_exists='overlay'参数，原生支持在已有工作表的内容尾部追加数据，无需额外读写全表，修改后的append_df_to_excel函数实现如下：

import pandas as pd
import os
from openpyxl import load_workbook

def append_df_to_excel(filename, df, sheet_name='Sheet1', startrow=None,
                       truncate_sheet=False, **to_excel_kwargs):
    # ExcelWriter仅支持openpyxl引擎的追加模式
    file_exist = os.path.exists(filename)
    writer = pd.ExcelWriter(
        filename, 
        engine='openpyxl', 
        mode='a' if file_exist else 'w',
        if_sheet_exists='overlay' if file_exist else None
    )
    # 自动获取已有工作表的最后一行作为写入起始位置
    if startrow is None and sheet_name in writer.book.sheetnames:
        startrow = writer.book[sheet_name].max_row

    # 写入数据
    df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs)
    writer.close()

注：使用前需升级Pandas版本，执行命令pip install pandas>=1.4.0即可

测试验证 #

两种场景均可正常运行：

1. 同表追加内容

if __name__=="__main__":
    filename_out = 'excel_output.xlsx'
    list_1 = [1,2,3,4,5]
    list_2 = [6,7,8,9,10]
    df = pd.DataFrame(list_1)
    append_df_to_excel(filename_out, df, header=None, index=False)
    df = pd.DataFrame(list_2)
    append_df_to_excel(filename_out, df, header=None, index=False)

运行后Sheet1会依次写入两行数据，无覆盖、无报错。

2. 不同工作表分别写入

if __name__=="__main__":
    filename_out = 'excel_output.xlsx'
    list_1 = [1,2,3,4,5]
    list_2 = [6,7,8,9,10]
    df = pd.DataFrame(list_1)
    append_df_to_excel(filename_out, df, sheet_name='list1', header=None, index=False)
    df = pd.DataFrame(list_2)
    append_df_to_excel(filename_out, df, sheet_name='list2', header=None, index=False)

运行后会生成list1、list2两个工作表，分别写入对应数据。

方案2：兼容Pandas 1.3.x版本（无需升级） #

如果无法升级Pandas版本，可以采用「读全表-拼接数据-覆写」的逻辑实现追加，适合数据量较小的场景：

import pandas as pd
import os

def append_df_to_excel(filename, df, sheet_name='Sheet1', **to_excel_kwargs):
    if not os.path.exists(filename):
        # 文件不存在直接写入
        df.to_excel(filename, sheet_name=sheet_name, **to_excel_kwargs)
        return
    # 文件存在时读取原表数据
    original_df = pd.read_excel(filename, sheet_name=sheet_name)
    # 拼接新旧数据
    combined_df = pd.concat([original_df, df], ignore_index=True)
    # 覆写原工作表
    with pd.ExcelWriter(filename, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
        combined_df.to_excel(writer, sheet_name=sheet_name, **to_excel_kwargs)

注：如果写入时不需要保留原表头，可以在concat后调整输出的header参数即可

内容的提问来源于stack exchange，提问作者BurgerKing Lee