如何避免Pandas导出Excel时日期附加时间并保留原格式

问题场景 #

我有一个Excel文件，表头包含文本和19.07.2023这类日-月-年格式的日期：

Item/Date   19.07.2023  20.07.2023  21.07.2023
Some Item      58               51             57

将其导入Pandas执行合并、填充空值操作后导出到Excel，原日期会变成2023-07-19 0:00:00，既附加了不必要的时间部分，又改变了原有的日-月-年格式。希望在Pandas处理流程中直接解决该问题，无需后续手动调整Excel。

现有代码示例：

import pandas as pd
test_df = pd.read_excel('file.xlsx')
test2_df = pd.read_excel('file2.xlsb')
test_total = test_df.merge(test2_df, how='outer').fillna(0)
test_total.to_excel('file_total.xlsx', index = False)

解决方案 #

1. 读取文件时保留表头日期的字符串格式 #

Pandas会自动将表头中的日期字符串解析为Timestamp类型，需要手动将其转回指定格式的字符串：

import pandas as pd

# 读取第一个文件并处理表头
test_df = pd.read_excel('file.xlsx', header=0)
test_df.columns = [
    col.strftime('%d.%m.%Y') if isinstance(col, pd.Timestamp) else col 
    for col in test_df.columns
]

# 读取第二个文件并做相同处理
test2_df = pd.read_excel('file2.xlsb', header=0)
test2_df.columns = [
    col.strftime('%d.%m.%Y') if isinstance(col, pd.Timestamp) else col 
    for col in test2_df.columns
]

2. 合并与填充空值（原有逻辑不变） #

test_total = test_df.merge(test2_df, how='outer').fillna(0)

3. 导出时设置Excel数据列的日期格式 #

使用openpyxl引擎自定义日期格式，确保Excel中数据列显示为日-月-年且无时间：

from openpyxl.styles import NamedStyle

# 创建自定义日期样式（日-月-年格式）
date_style = NamedStyle(name='custom_date', number_format='DD.MM.YYYY')

# 导出Excel并应用格式
with pd.ExcelWriter('file_total.xlsx', engine='openpyxl') as writer:
    test_total.to_excel(writer, index=False)
    worksheet = writer.sheets['Sheet1']
    
    # 遍历表头，识别日期列并为整列应用格式
    for col_idx, col_name in enumerate(test_total.columns):
        try:
            # 验证列名是否为日-月-年格式的日期
            pd.to_datetime(col_name, format='%d.%m.%Y')
            # 从第二行开始（第一行是表头）为整列设置格式
            for row in range(2, len(test_total) + 2):
                worksheet.cell(row=row, column=col_idx + 1).style = date_style
        except ValueError:
            # 非日期列跳过
            continue

内容的提问来源于stack exchange，提问作者Антон