OpenPyXL导出Excel时日期列自动转为自定义格式问题求助

你遇到的问题根源有两点：

1. 代码中将datetime列转换为Python原生date对象，而非保留pandas的datetime64类型，导致ExcelWriter的日期格式参数未正确生效
2. pandas设置的date_format属于Excel自定义格式，而你需要的是Excel内置的「短日期」格式（而非自定义格式）

以下是无需遍历单元格的批量处理优化代码：

from pandas import DataFrame, ExcelWriter, to_datetime
from pandas._libs.tslibs.timestamps import Timestamp
from openpyxl.styles import numbers

def Send_Dataframe_To_Excel(dataframe):
    # 筛选所有datetime类型列
    datetime_cols = dataframe.select_dtypes(include=["datetime", "datetime64", "datetime64[ns]", "datetimetz"]).columns.tolist()
    
    # 保留pandas datetime类型，仅提取日期部分（不转成Python date对象）
    for col in datetime_cols:
        dataframe[col] = to_datetime(dataframe[col], format='%Y-%m-%d %H:%M:%S', errors='coerce').dt.floor('D')
    
    with ExcelWriter("output_file.xlsx", engine="openpyxl") as writer:
        dataframe.to_excel(writer, sheet_name="Sheet1", index=False)
        
        # 获取目标工作表对象
        worksheet = writer.sheets["Sheet1"]
        
        # 批量设置日期列格式
        for col in datetime_cols:
            # 计算列对应的Excel字母标识（如第4列对应D）
            col_index = dataframe.columns.get_loc(col) + 1
            col_letter = chr(ord('A') + col_index - 1)
            
            # 应用Excel内置短日期格式（ID=14，显示格式随系统区域，若要固定DD/MM/YYYY则用下方注释代码）
            worksheet.column_dimensions[col_letter].number_format = numbers.FORMAT_DATE_SHORT
            # worksheet.column_dimensions[col_letter].number_format = 'DD/MM/YYYY'

# 测试数据
dataframe = DataFrame({
    'ID': [1, 2, 3, 4, 5, 6, 7],
    'text_data': ['text1', 'text2', 'text3', 'text4', 'text5', 'text6', 'text7'],
    'number_data': [11, 12, 13, 14, 15, 16, 17],
    'date_data': [
        Timestamp('2011-01-01 00:20:00'), Timestamp('2012-02-02 00:00:00'),
        Timestamp('2013-03-03 00:00:00'), Timestamp('2014-04-04 00:00:00'),
        Timestamp('2015-05-05 00:00:00'), Timestamp('2016-06-06 00:00:00'),
        Timestamp('2017-07-07 00:00:00')
    ]
})
            
Send_Dataframe_To_Excel(dataframe)

关键说明 #

• 保留pandas的datetime64类型：用dt.floor('D')提取日期部分，避免转为Python原生date对象，确保Excel能正确识别为日期类型
• 批量整列设置：通过openpyxl的column_dimensions直接对整列应用格式，无需遍历单元格，处理大数量级数据时效率极高
• 格式可选：如果需要不受系统区域影响的固定DD/MM/YYYY格式，替换代码中注释的格式字符串即可

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