#!/usr/bin/python
# coding:utf-8
import json
import pandas as pd
import os
import datetime
# 脚本文件要跟excel文件放在同一路径下,也可以自行修改
files = os.listdir() # 路径
files.sort()
print('Convert Started!')
for file in files:
# 如果是不.xlsx文件则跳过
if '.xlsx' not in file:
continue
print('converting ' + file)
# 读取Excel文件
df_excel = pd.read_excel(file)
# 将DataFrame对象转换成字典
df_dict = df_excel.to_dict(orient='records')
class DateEncoder(json.JSONEncoder):
def default(self, obj):
if isinstance(obj, datetime.datetime):
return obj.strftime("%Y-%m-%dT%H:%M:%S")
else:
return json.JSONEncoder.default(self, obj)
# 转换成JSON格式
json_str = json.dumps(df_dict, ensure_ascii=False, cls=DateEncoder)
# 存为同名的json文件 # 写入文件
with open((file.replace('.xlsx', '') + '.json'), 'w', encoding='utf-8') as f:
f.write(json_str)
print('Convert Done!')
运行: > python multi-excel2json.py