时间戳

首页 > 解决方案 > Python循环在循环中保存两次到Excel电子表格选项卡

python - Python循环在循环中保存两次到Excel电子表格选项卡

问题描述

作为长循环结构的一部分，我的代码如下所示，该结构将 2 个 dfs 写入同一个电子表格和 excel 选项卡。这是我尝试附加每个写入序列的代码：

# WRITE OUTPUT TO EXCEL FILE - 
with pd.ExcelWriter('\\\porfiler03\\gtdshare\\ERA5-MAPPING\\PCA\\PCA_ERA5_output.xlsx', engine="openpyxl", mode="a") as writer:
    w.to_excel(writer, sheet_name=sheet) 
with pd.ExcelWriter('\\\porfiler03\\gtdshare\\ERA5-MAPPING\\PCA\\PCA_ERA5_output.xlsx', engine="openpyxl", mode="a") as writer:
    finalpcs.to_excel(writer, sheet_name=sheet,startrow=5)

但是，我的结果是将 df1（“w”）写入一个工作表选项卡（14），然后将下一个 df（“finalpcs”）写入新的工作表选项卡（141）。我需要将它们都写入同一个工作表选项卡 - “14”。我尝试对第二个df使用“startrow”来不覆盖df“w”的第一个写入。

这是第一个保存序列的图片：

在此处输入图像描述

并且，在第二个保存序列之后：

在此处输入图像描述

谢谢你，

标签： pythonexcelloopsxlsxwriterpandas.excelwriter

解决方案

It's not possible with how you're currently doing it. The docs for the ExcelWriter class has this flag:

if_sheet_exists{‘error’, ‘new’, ‘replace’}, default ‘error’: How to behave when trying to write to a sheet that already exists (append mode only).

  • error: raise a ValueError.

  • new: Create a new sheet, with a name determined by the engine.

  • replace: Delete the contents of the sheet before writing to it.

You can either wipe the sheet or have a suffix appended to it in the event of a clash. My suggestion would be to take your excel code out of the 'with' since every time you're writing, it will be starting the process from the beginning. Meaning the worksheet and workbook will be closed then reopened. I'm assuming this is the issue.

Since this is tagged as xlsxwriter, but you're using openpyxl. This is the answer for multiple dataframes in xlsxwriter:

Under Handling multiple Pandas Dataframes in the Working with Python Pandas in the docs shows the following example:

writer = pd.ExcelWriter("foo.xlsx",
                        engine='xlsxwriter',
                        datetime_format='mmm d yyyy hh:mm:ss',
                        date_format='mmmm dd yyyy')

# Position the dataframes in the worksheet.
df1.to_excel(writer, sheet_name='Sheet1')  # Default position, cell A1.
df2.to_excel(writer, sheet_name='Sheet1', startcol=3)
df3.to_excel(writer, sheet_name='Sheet1', startrow=6)

# Write the dataframe without the header and index.
df4.to_excel(writer, sheet_name='Sheet1',
             startrow=7, startcol=4, header=False, index=False)

推荐阅读