Pandas升级至1.5.3后，to_excel与Excel编辑操作无法同时执行求助

• 环境：Python 3.8，Pandas 1.5.3，openpyxl 3.0.7
• 需求：基于带格式模板，从CSV/JSON生成XLSX，需实现列宽自适应、条件着色等功能
• 异常表现：升级Pandas到1.5.3前（使用1.2.3版本），可同时完成to_excel导出数据+后续编辑操作；升级后只能单独完成其中一项，无法同时实现

基础DataFrame导出代码 #

data = 'some .csv'
writer = pandas.ExcelWriter(outfilename, mode='a', if_sheet_exists='overlay', engine='openpyxl')
book = openpyxl.load_workbook(outfilename)
writer.workbook = book
data.to_excel(self.writer, sheet_name, startrow=start_row, startcol=start_column, header=header, index=index)

列宽自适应函数代码 #

def autoResize(self, sheet_name, outfilename):
    book = openpyxl.load_workbook(outfilename)
    sheet = book[sheet_name]

    for col in sheet.columns:
        column = col[0].column_letter # 获取列名
        max_length = 0

        ## 获取标题和内容的最大长度 ##
        for cell in col:
            if max_length < len(str(cell.value)):
                max_length = len(str(cell.value))

        adjusted_width = (max_length+2)*1.2
        sheet.column_dimensions[column].width = adjusted_width

    book.save(outfilename)

    return 0

解决方案 #

问题根源是Pandas 1.5.x版本对ExcelWriter的资源管理逻辑做了调整，导出后未正确释放文件句柄，导致后续openpyxl无法正常读写。

修正步骤 #

1. 必须显式关闭ExcelWriter：在to_excel调用后立即调用writer.close()，释放文件资源
2. 避免重复加载工作簿：导出阶段和编辑阶段共用同一个工作簿实例，减少文件IO冲突
3. 调整代码顺序：先加载模板工作簿，再初始化ExcelWriter，导出后直接在该工作簿上执行编辑操作，最后统一保存

修正后完整代码示例 #

import pandas as pd
from openpyxl import load_workbook

def export_and_format(outfilename, sheet_name, data_df, start_row=0, start_column=0, header=True, index=False):
    # 加载模板工作簿
    book = load_workbook(outfilename)
    # 初始化ExcelWriter并绑定已加载的工作簿
    writer = pd.ExcelWriter(outfilename, mode='a', if_sheet_exists='overlay', engine='openpyxl')
    writer.workbook = book
    
    # 导出DataFrame数据
    data_df.to_excel(writer, sheet_name=sheet_name, startrow=start_row, startcol=start_column, header=header, index=index)
    
    # 显式关闭writer释放资源
    writer.close()
    
    # 执行列宽自适应（直接使用已加载的工作簿，无需重复打开）
    sheet = book[sheet_name]
    for col in sheet.columns:
        column = col[0].column_letter
        max_length = 0
        for cell in col:
            if max_length < len(str(cell.value)):
                max_length = len(str(cell.value))
        adjusted_width = (max_length + 2) * 1.2
        sheet.column_dimensions[column].width = adjusted_width
    
    # 统一保存工作簿
    book.save(outfilename)

额外注意事项 #

• 确保模板文件在操作前未被其他程序占用（如Excel客户端）
• 若需添加条件着色，可在列宽调整步骤后直接在sheet对象上操作，无需重新加载文件
• 避免在导出和编辑之间多次打开/保存文件，减少文件锁冲突

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