Python向Excel工作表追加模拟数据失败问题求助

我通过Python运行外部程序的10次模拟任务，希望将两组数据分别存储到同一个Excel文件的不同工作表中，且每次迭代时新数据需追加至现有数据下方，不覆盖原有数据。但仅第一次迭代的数据能成功保存，后续迭代均失效，报错信息如下：

Traceback (most recent call last):
  File "G:\Drive\main.py", line 308, in <module>
    df_mvar.to_excel(writer, sheet_name='mvar', index=False)
  File "G:\Drive\main.py\venv\Lib\site-packages\pandas\core\generic.py", line 2345, in to_excel
    formatter.write(
  File "G:\Drive\main.py\venv\Lib\site-packages\pandas\io\formats\excel.py", line 955, in write
    writer._write_cells(
  File "G:\Drive\main.py\venv\Lib\site-packages\pandas\io\excel\_openpyxl.py", line 463, in _write_cells
    raise ValueError(
ValueError: Sheet 'mvar' already exists and if_sheet_exists is set to 'error'.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "G:\Drive\main.py", line 314, in <module>
    writer.book = book
    ^^^^^^^^^^^
AttributeError: property 'book' of 'OpenpyxlWriter' object has no setter

Process finished with exit code 1

原实现代码：

# Creates DataFrames for each dataset
df_mvar = pd.DataFrame([dados_coletados_mvar], columns=nomes_colunas)
df_tensao = pd.DataFrame([dados_coletados_tensao], columns=nomes_colunas)

# Excel file name
nome_excel = 'dados_coletados_juntos.xlsx'

# Checks if the Excel file already exists
if os.path.exists(nome_excel):
    # Load existing Excel file into a DataFrame
    try:
        with pd.ExcelWriter(nome_excel, engine='openpyxl', mode='a') as writer:
            # Adiciona os DataFrames como planilhas diferentes no mesmo arquivo
            df_mvar.to_excel(writer, sheet_name='mvar', index=False)
            df_tensao.to_excel(writer, sheet_name='tensao', index=False)
    except ValueError:
        # If the worksheet already exists, load the file and add the DataFrames to the existing worksheets
        book = load_workbook(nome_excel)
        writer = pd.ExcelWriter(nome_excel, engine='openpyxl')
        writer.book = book
        writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

        df_mvar.to_excel(writer, sheet_name='mvar', index=False)
        df_tensao.to_excel(writer, sheet_name='tensao', index=False)

else:
    # Creates an empty DataFrame if the file does not exist
    df = pd.DataFrame()
    with pd.ExcelWriter(nome_excel, engine='openpyxl', mode='w') as writer:
        # Add DataFrames as different sheets in the same file
        df_mvar.to_excel(writer, sheet_name='mvar', index=False)
        df_tensao.to_excel(writer, sheet_name='tensao', index=False)

print(f'Data exported to {nome_excel}')

问题分析 #

1. 第一个报错：使用mode='a'时，pandas默认if_sheet_exists='error'，当目标工作表已存在时直接抛出错误
2. 第二个报错：新版pandas中OpenpyxlWriter的book属性为只读，无法直接赋值修改

解决方案 #

方案一：读取现有数据合并后写入（兼容所有pandas版本） #

先读取Excel中已有的工作表数据，与本次迭代的新数据合并，再重新写入整个工作表，确保数据正确追加：

import pandas as pd
import os
from openpyxl import load_workbook

# 创建本次迭代的DataFrame
df_mvar = pd.DataFrame([dados_coletados_mvar], columns=nomes_colunas)
df_tensao = pd.DataFrame([dados_coletados_tensao], columns=nomes_colunas)

nome_excel = 'dados_coletados_juntos.xlsx'

if os.path.exists(nome_excel):
    # 读取现有工作表的数据
    with pd.ExcelFile(nome_excel) as xls:
        # 合并mvar工作表数据
        existing_mvar = pd.read_excel(xls, 'mvar')
        updated_mvar = pd.concat([existing_mvar, df_mvar], ignore_index=True)
        
        # 合并tensao工作表数据
        existing_tensao = pd.read_excel(xls, 'tensao')
        updated_tensao = pd.concat([existing_tensao, df_tensao], ignore_index=True)
    
    # 写入更新后的数据
    with pd.ExcelWriter(nome_excel, engine='openpyxl', mode='w') as writer:
        updated_mvar.to_excel(writer, sheet_name='mvar', index=False)
        updated_tensao.to_excel(writer, sheet_name='tensao', index=False)
else:
    # 文件不存在时直接写入初始数据
    with pd.ExcelWriter(nome_excel, engine='openpyxl', mode='w') as writer:
        df_mvar.to_excel(writer, sheet_name='mvar', index=False)
        df_tensao.to_excel(writer, sheet_name='tensao', index=False)

print(f'Data exported to {nome_excel}')

方案二：使用if_sheet_exists参数追加（pandas 1.4.0+支持） #

利用pandas新增的if_sheet_exists参数设置为overlay，配合startrow指定从现有数据的下一行开始写入，无需读取整个工作表数据，适合大数据场景：

import pandas as pd
import os
from openpyxl import load_workbook

# 创建本次迭代的DataFrame
df_mvar = pd.DataFrame([dados_coletados_mvar], columns=nomes_colunas)
df_tensao = pd.DataFrame([dados_coletados_tensao], columns=nomes_colunas)

nome_excel = 'dados_coletados_juntos.xlsx'

if os.path.exists(nome_excel):
    with pd.ExcelWriter(nome_excel, engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer:
        # 获取mvar工作表的现有行数，从下一行开始写入
        book = writer.book
        mvar_sheet = book['mvar']
        startrow_mvar = mvar_sheet.max_row
        
        df_mvar.to_excel(writer, sheet_name='mvar', index=False, startrow=startrow_mvar)
        
        # 处理tensao工作表
        tensao_sheet = book['tensao']
        startrow_tensao = tensao_sheet.max_row
        df_tensao.to_excel(writer, sheet_name='tensao', index=False, startrow=startrow_tensao)
else:
    with pd.ExcelWriter(nome_excel, engine='openpyxl', mode='w') as writer:
        df_mvar.to_excel(writer, sheet_name='mvar', index=False)
        df_tensao.to_excel(writer, sheet_name='tensao', index=False)

print(f'Data exported to {nome_excel}')

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