Pandas处理结果无法合并至Excel第19列，遇格式识别错误求助

问题场景 #

需要将Pandas处理后的4列数据（Date création DI、Anné、Moi、Jour）从第19列开始写入原Excel文件Extraction_DI.xlsx的Sheet1中，保持表头一致。但执行代码时出现格式识别错误，且文件数据量超20万行，优先使用Pandas而非openpyxl操作。

原代码 #

import pandas as pd
from datetime import datetime


kpi_workbook=pd.read_excel(r'D:\Maintenance\Bloc 6\PROJECT KPI\Performance\Source code\Extraction_DI.xlsx')
kpi_workbook['Date création DI'] = pd.to_datetime(kpi_workbook['Date création DI'])
kpi_workbook['Anné'] = kpi_workbook['Date création DI'].dt.year
kpi_workbook['Moi'] = kpi_workbook['Date création DI'].dt.month
kpi_workbook['Jour'] = kpi_workbook['Date création DI'].dt.day
selected_columns = ['Date création DI', 'Anné', 'Moi', 'Jour']

writer = pd.ExcelWriter(r'D:\Maintenance\Bloc 6\PROJECT KPI\Performance\Source code\Extraction_DI.xlsx')
writer.book = pd.load_workbook(r'D:\Maintenance\Bloc 6\PROJECT KPI\Performance\Source code\Extraction_DI.xlsx', engine='openpyxl')

kpi_workbook[selected_columns].to_excel(writer, index=False, sheet_name='Sheet1', startcol=19)

writer.save()

报错信息 #

File "d:\Maintenance\Bloc 6\PROJECT KPI\Performance\Source code\Done_Performance_Date filter - Copy.py", line 5, in <module>
    kpi_workbook=pd.read_excel(r'D:\Maintenance\Bloc 6\PROJECT KPI\Performance\Source code\Extraction_DI.xlsx')
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\seljoukia\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\io\excel\_base.py", line 478, in read_excel
    io = ExcelFile(io, storage_options=storage_options, engine=engine)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\seljoukia\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\io\excel\_base.py", line 1500, in __init__ 
    raise ValueError(
ValueError: Excel file format cannot be determined, you must specify an engine manually.

解决步骤 #

1. 修复文件读取的引擎指定问题 #

报错核心是Pandas无法自动识别Excel文件格式，需在read_excel中明确指定engine='openpyxl'（针对.xlsx格式文件）。

2. 修正ExcelWriter的初始化逻辑 #

原代码中手动替换writer.book的方式易引发冲突，正确做法是创建ExcelWriter时指定engine='openpyxl'并使用mode='a'（追加模式），同时设置if_sheet_exists='overlay'来覆盖已有工作表的指定列区域，避免清空原有数据。

3. 优化后的完整代码 #

import pandas as pd

# 读取文件时指定引擎，避免格式识别错误
kpi_workbook = pd.read_excel(
    r'D:\Maintenance\Bloc 6\PROJECT KPI\Performance\Source code\Extraction_DI.xlsx',
    engine='openpyxl'
)

# 日期处理逻辑不变
kpi_workbook['Date création DI'] = pd.to_datetime(kpi_workbook['Date création DI'])
kpi_workbook['Anné'] = kpi_workbook['Date création DI'].dt.year
kpi_workbook['Moi'] = kpi_workbook['Date création DI'].dt.month
kpi_workbook['Jour'] = kpi_workbook['Date création DI'].dt.day
selected_columns = ['Date création DI', 'Anné', 'Moi', 'Jour']

# 初始化ExcelWriter，使用追加模式并指定覆盖工作表区域
with pd.ExcelWriter(
    r'D:\Maintenance\Bloc 6\PROJECT KPI\Performance\Source code\Extraction_DI.xlsx',
    engine='openpyxl',
    mode='a',
    if_sheet_exists='overlay'
) as writer:
    # 从第19列开始写入，不保留索引
    kpi_workbook[selected_columns].to_excel(
        writer,
        index=False,
        sheet_name='Sheet1',
        startcol=19
    )

4. 大文件额外建议 #

• 读取时通过dtype参数指定列的数据类型（如dtype={'列名': 'str'}），减少内存占用，提升处理速度。
• 若文件过大，可使用chunksize分块读取处理，但此场景下整读整写已能满足需求。

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