使用ExcelWriter和Openpyxl格式化整列日期的技术问题

问题场景 #

用pandas将DataFrame写入多工作表Excel，要求日期显示为YYYY-MM-DD（不带时间），同时需要设置列宽等高级格式，因此选用openpyxl作为ExcelWriter引擎。遇到以下问题：

• 直接给ExcelWriter加datetime_format='YYYY-MM-DD'参数，在指定openpyxl引擎时完全无效（不指定引擎时正常）
• 尝试通过writer.sheets['test'].column_dimensions['B'].number_format = 'YYYY-MM-DD'设置整列格式，列宽生效但日期格式没变化，只有逐个设置单元格格式才管用
• 有成千上万个日期单元格，不想挨个遍历处理

可行解决方案 #

方案1：用openpyxl自定义样式批量设置整列（保留日期类型）

核心思路是创建自定义日期样式，将样式绑定到目标列，这样整列的日期单元格都会应用该格式，无需逐个遍历。

import pandas as pd
from openpyxl.styles import NamedStyle, numbers

# 创建自定义日期样式，对应YYYY-MM-DD格式
custom_date_style = NamedStyle(name="yyyymmdd_style", number_format=numbers.FORMAT_DATE_YYYYMMDD2)

# 构造测试数据
df = pd.DataFrame({'string_col': ['abc', 'def', 'ghi']})
df['date_col'] = pd.date_range(start='2020-01-01', periods=3)

with pd.ExcelWriter('test.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, 'test', index=False)
    sheet = writer.sheets['test']
    # 设置列宽
    sheet.column_dimensions['B'].width = 50
    # 将自定义样式应用到目标列
    sheet.column_dimensions['B'].style = custom_date_style

方案2：将日期转成字符串后写入（简单直接，适合无需编辑日期的场景）

如果不需要保留Excel中的日期类型，只是要显示YYYY-MM-DD格式，直接把DataFrame中的日期列转成字符串即可，后续设置列宽也不受影响。

import pandas as pd

df = pd.DataFrame({'string_col': ['abc', 'def', 'ghi']})
# 将日期列转为YYYY-MM-DD格式的字符串
df['date_col'] = pd.date_range(start='2020-01-01', periods=3).strftime('%Y-%m-%d')

with pd.ExcelWriter('test.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, 'test', index=False)
    sheet = writer.sheets['test']
    sheet.column_dimensions['B'].width = 50

为什么直接设置column_dimensions.number_format无效？ #

因为pandas用openpyxl写入日期时，已经给每个日期单元格单独设置了默认的日期格式，列维度的number_format设置不会覆盖单元格已有的格式，必须通过样式来统一覆盖。

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