如何使用pd.read_excel读取Excel并让日期按指定格式输出为JSON

我有一份Excel文件中的数据，使用以下Python代码读取并转换为JSON：

df = pd.read_excel(xls_file_path)
# Convert DataFrame to JSON
json_data = df.to_json(orient='records')
print(json_data)

得到的JSON结果如下：

[{"Name":"Deepak Kalindi","Employee ID":101,"Joining Date":1704153600000,"Leaving Date":null,"Monthly Salary":12000,"Number of full days":27,"Number of half days":2.5,"Number of leaves":1.5,"OT (full Day)":3,"OT (half Day)":2}]

需要让Joining Date字段在JSON字符串中显示为"02-01-2024"格式，该怎么做？

解决方法 #

核心思路是先将DataFrame中的日期列转换为目标格式的字符串，再导出JSON，具体步骤如下：

1. 读取Excel时解析日期列：确保Pandas将Joining Date识别为datetime类型（避免后续处理时间戳数值）：

df = pd.read_excel(xls_file_path, parse_dates=['Joining Date'])

2. 将日期格式化为指定字符串：利用dt.strftime方法将datetime对象转为DD-MM-YYYY格式的字符串：

df['Joining Date'] = df['Joining Date'].dt.strftime('%d-%m-%Y')

3. 导出为JSON：此时执行导出操作，日期就会以目标字符串格式输出：

json_data = df.to_json(orient='records')
print(json_data)

执行后得到的JSON结果：

[{"Name":"Deepak Kalindi","Employee ID":101,"Joining Date":"02-01-2024","Leaving Date":null,"Monthly Salary":12000,"Number of full days":27,"Number of half days":2.5,"Number of leaves":1.5,"OT (full Day)":3,"OT (half Day)":2}]

如果Leaving Date也需要相同格式处理，重复步骤2即可，空值（null）会被自动保留，不会出现报错。

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