pandas用for循环写入同一Excel多sheet仅保留最后sheet的问题排查

• 读取包含多个工作表的Excel文件
• 遍历输入文件的每个工作表，读取指定列并处理原有数据
• 生成输出用DataFrame
• 将处理得到的DataFrame作为新工作表存入同一个输出Excel文件

for sheet in all_sheets: # all_sheets是输入文件包含的所有工作表名称列表
    print(sheet) # 此处省略数据转换处理逻辑
    writer = pd.ExcelWriter('Output.xlsx', engine='xlsxwriter')
    df_final_4.to_excel(writer, sheet_name=sheet, index=True)
    writer.save()

• 输出文件仅包含最后一次迭代写入的单个工作表数据
• 前序迭代写入的内容被覆盖，最终输出文件仅留存1个工作表
• 尝试多种实现方案均得到相同结果

方案1 #

for sheet in all_sheets:
    writer = pd.ExcelWriter('Output.xlsx', engine='xlsxwriter')
    df_final_4.to_excel(writer, sheet_name=sheet, encoding='utf-8', index=True)
    writer.save()

方案2 #

for sheet in all_sheets[8:10]:
    print(sheet)
    writer = pd.ExcelWriter('newfile.xlsx', engine='xlsxwriter')
    df_final_4.to_excel(writer, sheet_name=sheet, index=True)
    writer.save()

方案3 #

wb = openpyxl.Workbook()
wb.save('Output.xlsx')
book = load_workbook('Output.xlsx')
writer = pd.ExcelWriter('Output.xlsx', engine='openpyxl') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df_final_4.to_excel(writer, sheet, index=True)
writer.save()
writer.close()

所有实现出错的核心问题完全一致：pd.ExcelWriter对象被放在了for循环内部初始化。

• 用xlsxwriter引擎时，每次新建ExcelWriter实例都会默认生成全新的空文件，直接覆盖路径下的同名已有文件，前序循环写入的内容会被全部清空，最终只会保留最后一次循环写入的工作表。
• 最后一个openpyxl方案的问题同理：加载工作簿、初始化writer的逻辑放在循环内，每次循环都会重新读取最初的空白工作簿，不会保留之前循环写入的内容，最终输出还是只有最后一个工作表。

方式1：一次性写入所有工作表（推荐，xlsxwriter引擎） #

把writer初始化、保存的逻辑都移到循环外，循环只负责往同一个writer对象里写入不同sheet：

# 循环外初始化writer，仅创建一次输出文件
writer = pd.ExcelWriter('Output.xlsx', engine='xlsxwriter')
for sheet in all_sheets:
    print(sheet)
    # 此处执行数据处理逻辑生成df_final_4
    df_final_4.to_excel(writer, sheet_name=sheet, index=True)
# 所有sheet写完后统一保存关闭
writer.close()

方式2：追加工作表到已有Excel文件（openpyxl引擎） #

如果需要在已存在的Excel文件基础上追加新工作表，初始化一次writer即可，不要在循环内重复加载原始文件：

from openpyxl import load_workbook

# 先创建空输出文件
wb = openpyxl.Workbook()
wb.save('Output.xlsx')

# 循环外初始化一次writer，绑定已有工作簿
book = load_workbook('Output.xlsx')
writer = pd.ExcelWriter('Output.xlsx', engine='openpyxl') 
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}

# 循环写入所有sheet
for sheet in all_sheets:
    print(sheet)
    # 此处执行数据处理逻辑生成df_final_4
    df_final_4.to_excel(writer, sheet_name=sheet, index=True)

# 写完统一保存关闭
writer.close()

提示：pandas 1.4以上版本可以直接在ExcelWriter中加mode='a'参数实现追加，不需要手动加载工作簿，写法更简洁：

# 先创建空输出文件
pd.DataFrame().to_excel('Output.xlsx')
# 追加模式初始化writer
writer = pd.ExcelWriter('Output.xlsx', engine='openpyxl', mode='a')
for sheet in all_sheets:
    df_final_4.to_excel(writer, sheet_name=sheet, index=True)
writer.close()

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