如何在for循环中将不同DataFrame追加到Excel对应工作表

可行实现方案 #

首先你需要用到openpyxl作为操作引擎，结合pandas的ExcelWriter的对应参数控制写入逻辑，代码如下：

import pandas as pd
import os
from openpyxl import load_workbook

# 定义df和对应sheet的映射关系，新增df只需要修改这个字典即可
sheet_df_map = {
    "sheet_a": df_a,
    "sheet_b": df_b
}
excel_path = "test.xlsx"

# 外层替换为你自己的业务循环逻辑
for i in range(3):
    # 每次迭代生成新的df_a、df_b后执行写入逻辑
    for sheet_name, df in sheet_df_map.items():
        if not os.path.exists(excel_path):
            # 文件不存在时首次创建写入，保留表头
            with pd.ExcelWriter(excel_path, engine="openpyxl") as writer:
                df.to_excel(writer, sheet_name=sheet_name, index=False)
        else:
            # 文件已存在时先读取对应sheet的现有行数，确定写入起始位置
            book = load_workbook(excel_path)
            start_row = 0
            header = True
            if sheet_name in book.sheetnames:
                # 已有sheet从最后一行之后开始写，跳过表头
                start_row = book[sheet_name].max_row
                header = False
            # 追加写入，overlay参数避免自动生成带后缀的重复sheet
            with pd.ExcelWriter(excel_path, engine="openpyxl", mode="a", if_sheet_exists="overlay") as writer:
                df.to_excel(writer, sheet_name=sheet_name, startrow=start_row, header=header, index=False)

核心说明 #

• if_sheet_exists="overlay"参数可以避免openpyxl追加模式下自动给重复sheet名加后缀的问题，该参数要求pandas版本 >= 1.4.0
• 写入前自动判断目标sheet是否存在，存在则跳过表头追加到已有数据末尾，不存在则新建sheet并保留表头
• 上下文管理器with会自动处理文件的打开关闭，不会出现资源泄漏问题，你有多少个待保存的df只需要维护sheet_df_map映射即可，不需要重复写写入逻辑

低版本pandas兼容方案 #

如果你使用的pandas版本低于1.4.0没有if_sheet_exists参数，可以使用下面的兼容写法：

import pandas as pd
import os
from openpyxl import load_workbook

excel_path = "test.xlsx"
sheet_df_map = {"sheet_a": df_a, "sheet_b": df_b}

for i in range(3):
    if os.path.exists(excel_path):
        book = load_workbook(excel_path)
        writer = pd.ExcelWriter(excel_path, engine="openpyxl")
        writer.book = book
        writer.sheets = {ws.title: ws for ws in book.worksheets}
    else:
        writer = pd.ExcelWriter(excel_path, engine="openpyxl")
    
    for sheet_name, df in sheet_df_map.items():
        start_row = 0
        header = True
        if sheet_name in writer.sheets:
            start_row = writer.sheets[sheet_name].max_row
            header = False
        df.to_excel(writer, sheet_name=sheet_name, startrow=start_row, header=header, index=False)
    
    writer.save()
    writer.close()

注意事项 #

• 写入时建议加上index=False参数，避免把pandas默认索引列写入Excel
• 不要直接用openpyxl的append方法写入DataFrame，该方法仅支持一维可迭代结构，需要手动把df转成列表才能写入，不如直接使用pandas的to_excel方法便捷

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