使用Openpyxl编辑Excel生成异常文件但内容正常的解决方法

问题背景 #

需要基于现有Excel模板生成报表：复制模板并重命名，将DataFrame写入模板内已存在的空白工作表，模板中另有工作表包含基于该数据的数据透视表（无法通过Pandas直接生成，必须依赖模板）。生成的文件打开时Excel会弹出内容丢失提示，但恢复后文件显示正常。当前使用Openpyxl 3.1.4版本，无法降级。

经测试，问题触发于pd.ExcelWriter()初始化环节，即使仅打开并关闭文件也会出现提示，尝试设置样式修复无效。

原始代码 #

from openpyxl import load_workbook
import pandas as pd
import shutil

data = {
    'Column1': ['Dato1', 'Dato2', 'Dato3'],
    'Column2': ['Dato4', 'Dato5', 'Dato6'],
    'Column3': ['Dato7', 'Dato8', 'Dato9']
}

df_filtrado = pd.DataFrame(data)

template_path = 'template.xlsx'
file_name = 'new_test.xlsx'  
shutil.copyfile(template_path, file_name)
writer = pd.ExcelWriter(file_name, engine='openpyxl', mode = 'a', if_sheet_exists='replace')
df_filtrado.to_excel(writer, sheet_name='Data', index=False)
writer.save() 

尝试过的样式修复代码（无效） #

from openpyxl import load_workbook
from openpyxl.styles import colors
from openpyxl.styles import Font
import pandas as pd
import shutil

data = {
    'Column1': ['Dato1', 'Dato2', 'Dato3'],
    'Column2': ['Dato4', 'Dato5', 'Dato6'],
    'Column3': ['Dato7', 'Dato8', 'Dato9']
}

df_filtrado = pd.DataFrame(data)

template_path = 'template.xlsx'
file_name = 'new_test.xlsx'  
wb = load_workbook(template_path)
fontStyle = Font(name="Calibri", size=12, color=colors.BLACK)
sheet = wb.active
sheet['A1'].font = fontStyle
wb.save(file_name)
writer = pd.ExcelWriter(file_name, engine='openpyxl', mode = 'a', if_sheet_exists='replace')
sheet = wb.active
sheet['A1'].font = fontStyle
df_filtrado.to_excel(writer, sheet_name='Datos', index=False)
writer.save() 

问题原因 #

pd.ExcelWriter的mode='a'（追加）模式在处理包含数据透视表的Excel模板时，会破坏文件内部的缓存结构和公式关联，导致Excel检测到文件异常并弹出提示。

解决方案 #

放弃mode='a'模式，改为直接加载模板工作簿并关联到ExcelWriter，保留模板的完整内部结构。修改后的代码如下：

from openpyxl import load_workbook
import pandas as pd
import shutil

data = {
    'Column1': ['Dato1', 'Dato2', 'Dato3'],
    'Column2': ['Dato4', 'Dato5', 'Dato6'],
    'Column3': ['Dato7', 'Dato8', 'Dato9']
}

df_filtrado = pd.DataFrame(data)

template_path = 'template.xlsx'
file_name = 'new_test.xlsx'  
# 复制模板文件
shutil.copyfile(template_path, file_name)

# 加载工作簿，data_only=False保留公式和数据透视表关联
wb = load_workbook(file_name, data_only=False)
# 初始化ExcelWriter，关联已加载的工作簿
writer = pd.ExcelWriter(file_name, engine='openpyxl')
writer.book = wb
writer.sheets = {ws.title: ws for ws in wb.worksheets}

# 将DataFrame写入指定工作表，覆盖原有内容
df_filtrado.to_excel(writer, sheet_name='Data', index=False)

# 保存并关闭文件
writer.close()

关键注意事项 #

• load_workbook时设置data_only=False：确保保留模板中的公式和数据透视表的数据源关联，若设为True会仅保留单元格值，丢失公式结构。
• 目标工作表必须已存在于模板中：to_excel会直接覆盖该工作表内容，符合需求。

内容的提问来源于stack exchange，提问作者Xabier San Sebastian