如何将pandas多DataFrame写入同一Excel不同工作表及导出问题排查
代码调整方案
核心问题原因
- 你同时创建了
xlsxwriter.Workbook和pd.ExcelWriter两个写入对象操作同一个Excel文件,文件句柄冲突导致写入内容被覆盖 - 循环中
marker变量没有自增,所有内容会重复写入同一个工作表,后续内容直接覆盖之前的写入结果 - 两个DataFrame写入同一个工作表时未设置行偏移,第二个DataFrame会覆盖第一个的内容
- 写入完成后没有关闭ExcelWriter对象,缓冲区的内容不会写入磁盘,导致打开文件看不到输出
修正后代码(不需要保留原xlsxwriter单独写入逻辑)
如果你不需要用原生xlsxwriter写入其他字符串内容,直接删除冗余的xlsxwriter实例,用pandas自带的写入逻辑即可,推荐用上下文管理器自动处理资源关闭:
import pandas as pd fullList = ['a','b','c',...] labels = ['a','b','c',...] dfA = pd.read_csv('01.csv') dfB = pd.read_csv('02.csv') # 用上下文管理器自动处理写入完成后的保存关闭 with pd.ExcelWriter('NewExcelFile.xlsx', engine='xlsxwriter') as writer: for marker, element in enumerate(fullList): dfA_prod = dfA[dfA["Column in A"] == element] dfB_prod = dfB[dfB["Column in B"] == element] # 先写dfA_prod dfA_prod.to_excel(writer, sheet_name=labels[marker], index=False) # 计算dfB_prod的起始行,和dfA间隔2行避免覆盖 start_row = len(dfA_prod) + 2 # 写dfB_prod,设置起始行,不需要重复写表头可以加header=False dfB_prod.to_excel(writer, sheet_name=labels[marker], startrow=start_row, index=False)
修正后代码(需要保留原xlsxwriter写入字符串逻辑)
如果你确实需要用原生xlsxwriter的workbook对象写入其他字符串内容,将pandas的writer和已有workbook绑定即可:
import pandas as pd import xlsxwriter fullList = ['a','b','c',...] labels = ['a','b','c',...] dfA = pd.read_csv('01.csv') dfB = pd.read_csv('02.csv') # 保留你原有的workbook实例,用来写入其他字符串 workbook = xlsxwriter.Workbook('NewExcelFile.xlsx') # 你原有写入字符串的逻辑放在这里 # worksheet = workbook.add_worksheet() # worksheet.write(0,0, "测试字符串") # 绑定已有workbook到pandas的writer writer = pd.ExcelWriter('NewExcelFile.xlsx', engine='xlsxwriter') writer.book = workbook writer.sheets = {ws.title: ws for ws in workbook.worksheets()} for marker, element in enumerate(fullList): dfA_prod = dfA[dfA["Column in A"] == element] dfB_prod = dfB[dfB["Column in B"] == element] dfA_prod.to_excel(writer, sheet_name=labels[marker], index=False) start_row = len(dfA_prod) + 2 dfB_prod.to_excel(writer, sheet_name=labels[marker], startrow=start_row, index=False) # 保存关闭 writer.close() workbook.close()
内容的提问来源于stack exchange,提问作者Spiinner
相关产品推荐
相关产品推荐

