Python循环内如何同时导出DataFrame到新Excel表并写入现有工作表

核心问题本质 #

你遇到的冲突根源根本不是to_excel和原生sheet.write的方法不兼容，而是代码在同一个文件路径上开了两个完全独立的xlsxwriter工作簿句柄：

• 第一个是你手动调用xlsxwriter.Workbook()创建的原生工作簿实例
• 第二个是pd.ExcelWriter(..., engine='xlsxwriter')内部自动初始化的、独立指向同一个文件路径的工作簿实例

两个句柄各自独立往目标文件写内容，最后执行关闭操作时，后关闭的句柄会把整个文件的原有内容完全覆盖，所以不管循环里的写入顺序怎么调整，最终只会留下最后关闭的那个句柄写入的内容，自然没法同时实现两个功能。

正确实现思路 #

整个流程只保留一个统一的xlsxwriter工作簿实例，所有写入操作（包括pandas导出DataFrame、原生API写指定单元格值）都基于这同一个实例执行，不让pandas的ExcelWriter重新生成独立的文件句柄。
具体操作要点：

• 先手动创建唯一的目标文件工作簿实例，提前把需要的Totals汇总表加进去
• 初始化pandas的ExcelWriter时，通过参数把已经创建好的工作簿实例直接绑定给它，跳过它内部重新打开/创建文件的逻辑
• 循环里同时执行两类写入操作，全程不新建额外的工作簿句柄
• 流程最后只统一关闭一次工作簿即可

修正后可直接运行的代码 #

import pandas as pd
import xlsxwriter

loopList = ["A", "B", "C", "D", "E"]
data = [['tom', 10], ['nick', 15], ['juli', 14]] 
counter = 1
df = pd.DataFrame(data, columns = ['Name', 'Age'])

# 提前定义文件路径，加r前缀避免Windows路径转义问题
target_file = r'C:\Test\Test.xlsx'
# 只创建一次全局工作簿实例
workbook = xlsxwriter.Workbook(target_file)
totals_sheet = workbook.add_worksheet('Totals')

# 初始化ExcelWriter时，直接绑定已有的工作簿，不让它重新开文件
writer = pd.ExcelWriter(
    target_file,
    engine='xlsxwriter',
    engine_kwargs={'workbook': workbook}
)

for sheet_name in loopList:
    # 循环导出DataFrame到新建工作表
    df.to_excel(writer, sheet_name=sheet_name, index=False)
    # 同步向Totals表写入递增位置的指定值
    totals_sheet.write(counter, counter, f"{sheet_name}{counter}")
    counter += 1

# 统一关闭关联的句柄，不需要重复调用save
writer.close()
workbook.close()

运行效果说明 #

• 生成的Test.xlsx会同时包含Totals汇总表和A/B/C/D/E五个数据表
• 每个A/B/C/D/E的数据表中，会完整导出你定义的姓名、年龄DataFrame内容
• Totals表中会在行列同步递增的位置，依次写入A1、B2、C3、D4、E5的自定义值
• 两类操作全部在同一个循环内完成，没有覆盖冲突

额外注意事项 #

• xlsxwriter引擎本身只支持从零创建全新的Excel文件，不支持修改已存在的旧文件；如果后续需要往已经存在的旧Excel里追加内容，需要切换为openpyxl引擎，核心绑定工作簿的逻辑是通用的
• 不要重复调用冗余的save操作，pandas的ExcelWriter在调用close时，会同步处理绑定的xlsxwriter实例的写入逻辑

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