如何用Pandas将带时区Datetime写入Excel并保留指定格式？

错误原因分析 #

1. 格式不匹配报错：你指定的format="DD-MMM-YYYY HH:MM:SS"不符合原数据的ISO格式（2023-04-07 13:06:18.931000+00:00），且pandas的日期格式符需用%开头（如%d表示日、%b表示月份缩写、%Y表示年份），手动指定错误格式导致解析失败。
2. 时区报错：Excel不支持带时区信息（tz-aware）的datetime类型，必须转换为无时区（tz-naive）的datetime才能导出。

两种可行方案 #

方案1：转换为指定格式的字符串（适合无需Excel日期功能的场景）

先正确解析带时区的datetime，移除时区后转换为目标格式的字符串：

import pandas as pd

df2 = pd.DataFrame.from_dict(dicts)

# 自动解析ISO格式的带时区datetime
df2['Creation date'] = pd.to_datetime(df2['Creation date'], utc=True)
df2['Updated date'] = pd.to_datetime(df2['Updated date'], utc=True)

# 移除时区信息，转换为无时区datetime
df2['Creation date'] = df2['Creation date'].dt.tz_convert(None)
df2['Updated date'] = df2['Updated date'].dt.tz_convert(None)

# 转换为DD-MMM-YYYY HH:MM:SS格式的字符串（如07-Apr-2023 13:06:18）
df2['Creation date'] = df2['Creation date'].dt.strftime("%d-%b-%Y %H:%M:%S")
df2['Updated date'] = df2['Updated date'].dt.strftime("%d-%b-%Y %H:%M:%S")

# 导出至Excel
df2.to_excel('output.xlsx', index=False)

方案2：保留datetime类型并设置Excel单元格格式（推荐，支持Excel日期操作）

保留无时区的datetime类型，导出时通过xlsxwriter引擎设置单元格显示格式，既满足格式要求，又保留Excel的日期功能（排序、筛选等）：

import pandas as pd

df2 = pd.DataFrame.from_dict(dicts)

# 解析并移除时区
df2['Creation date'] = pd.to_datetime(df2['Creation date'], utc=True).dt.tz_convert(None)
df2['Updated date'] = pd.to_datetime(df2['Updated date'], utc=True).dt.tz_convert(None)

# 创建Excel写入器，使用xlsxwriter引擎
writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
df2.to_excel(writer, index=False, sheet_name='Sheet1')

# 获取工作簿和工作表对象
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# 创建目标日期格式（dd-mmm-yyyy hh:mm:ss）
date_format = workbook.add_format({'num_format': 'dd-mmm-yyyy hh:mm:ss'})

# 获取日期列的索引位置
creation_col_idx = df2.columns.get_loc('Creation date')
updated_col_idx = df2.columns.get_loc('Updated date')

# 设置列宽和格式
worksheet.set_column(creation_col_idx, creation_col_idx, 20, date_format)
worksheet.set_column(updated_col_idx, updated_col_idx, 20, date_format)

# 保存文件
writer.close()

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