如何导出无时分秒的日期格式至Excel？附Python代码问题排查

问题背景 #

用Pandas、openpyxl对比两份员工Excel数据，核心功能正常，但导出的Excel日期始终显示为2023-06-18 00:00:00格式。需要将日期格式改为m-d-yyyy，且必须保留Excel日期函数属性（不能转为字符串）。尝试过转换datetime列格式、导出时设置格式均无效。

原因分析 #

1. Pandas默认to_excel方法不会自动应用自定义日期格式，默认输出包含时间的datetime格式
2. 尝试的xlwings代码存在API错误：add_format是xlwt/xlrd的方法，xlwings中需用NumberFormat设置格式
3. 读取Excel时重复调用pd.to_datetime属于冗余操作，可能导致datetime列内部存储异常

解决方案 #

推荐两种可靠实现方式，二选一即可：

方式1：用Pandas ExcelWriter（openpyxl引擎）直接指定日期格式 #

在导出阶段通过pd.ExcelWriter的datetime_format参数设置目标格式，一步完成导出和格式设置，无需后续修改Excel文件。

方式2：导出后用openpyxl给日期列添加自定义格式 #

如果需要后续用openpyxl处理其他样式（比如填充色），可以在导出后给日期列设置NamedStyle，指定数字格式为m-d-yyyy。

修改后的完整代码 #

以下代码采用方式1，同时保留原有的openpyxl样式处理逻辑：

import pandas as pd  
import datetime as dt
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter


# 读取新数据
new_WFRL = pd.read_excel(
    "H:/DIR/Human Resources/HR Audits/Raw Files/WFRL 6.24.24.xlsx",
    usecols=["Empl ID", "Name", "Grade", "Step", "Grade Entry Date", "Step Date", "WGI Due Dt"],
    parse_dates=["Grade Entry Date", "Step Date", "WGI Due Dt"],
    index_col=False
).sort_values("Name")   
new_WFRL = new_WFRL.add_prefix("New ")  

# 读取旧数据
old_WFRL = pd.read_excel(
    "H:/DIR/Human Resources/HR Audits/Raw Files/WFRL 6.14.24.xlsx",
    usecols=["Empl ID", "Name", "Grade", "Step", "Grade Entry Date", "Step Date", "WGI Due Dt"],
    parse_dates=["Grade Entry Date", "Step Date", "WGI Due Dt"],
    index_col=False
).sort_values("Name")   
old_WFRL = old_WFRL.add_prefix("Old ")  

# 合并数据
merged_WFRL = pd.merge(old_WFRL, new_WFRL, how="outer", left_on="Old Empl ID", right_on="New Empl ID")  

# 标记Grade/Step变化
def compare_WFRL(df):  
    return 1 if df["Old Grade"] == df["New Grade"] and df["Old Step"] == df["New Step"] else 0  

merged_WFRL["changes"] = merged_WFRL.apply(compare_WFRL, axis=1)  
merged_changes = merged_WFRL[merged_WFRL["changes"] == 0].drop("changes", axis=1)  

# 导出Excel，同时设置日期格式
export_file = "H:/DIR/Human Resources/HR Audits/WFRL Comparison Reports/6.24.24v7.xlsx"
# 使用openpyxl引擎，指定datetime格式为m-d-yyyy
with pd.ExcelWriter(export_file, engine="openpyxl", datetime_format="m-d-yyyy") as writer:
    merged_changes.to_excel(writer, index=False)

# 后续的样式处理（填充色、列宽）
wb = load_workbook(export_file)
ws = wb.active

# 定义填充样式
yellow_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
red_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")

# 标记差异单元格
for row in range(2, len(merged_changes) + 2):
    old_grade_cell = ws.cell(row=row, column=2)  # Old Grade
    new_grade_cell = ws.cell(row=row, column=10)  # New Grade
    old_step_cell = ws.cell(row=row, column=3)  # Old Step
    new_step_cell = ws.cell(row=row, column=11)  # New Step
    
    if new_grade_cell.value != old_grade_cell.value:
        new_grade_cell.fill = yellow_fill
    
    if new_step_cell.value != old_step_cell.value:
        new_step_cell.fill = red_fill

# 调整列宽
dims = {}
for row in ws.rows:
    for cell in row:
        if cell.value:
            dims[cell.column_letter] = max(dims.get(cell.column_letter, 0), len(str(cell.value)))
for col, value in dims.items():
    ws.column_dimensions[col].width = value * 1.25  

wb.save(export_file)

关键修改点说明 #

1. 移除了冗余的pd.to_datetime重复转换，读取时parse_dates已经将列转为datetime类型
2. 用pd.ExcelWriter配合openpyxl引擎，通过datetime_format="m-d-yyyy"直接设置导出的日期格式，确保日期保留Excel日期属性
3. 简化了compare_WFRL函数的返回逻辑，更简洁高效

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