python3将json数据转换到excel中
- #!/usr/bin/env python
- # coding=utf-8
- # json转换为excel
-
- import xlrd
- import json
- import os
- from openpyxl import Workbook
- wb = Workbook()
- ws = wb.active
-
- cols = []
- def json2excel(jsfile, excfile):
- # 读取json数据
- a = 1
- if os.path.exists(jsfile):
- # 先用key值写表头
- with open(jsfile, 'r',encoding='utf8') as fp:
- # 先用key值写表头
- line = fp.readline()
- if not line:
- print("没有内容")
- else:
- # 每一行转换成字典类型
- jsdata = json.loads(line)
- # 用key值做标题
- for k in jsdata.keys():
- if k not in cols:
- cols.append(k)
- ws.append(cols) # 标题
- # 写值
- with open(jsfile, 'r', encoding='utf8') as fp:
- # 循环写值
- while True:
- print('正在写入的行数%s:' % a)
- line = fp.readline()
- if not line:
- break
- # 转换为python对象
- jsdata = json.loads(line)
- rowdata = []
- for col in cols:
- # 获取每一行key值对应的value值
- rowdata.append(jsdata.get(col))
- a += 1
- ws.append(rowdata) # 写行
- # ws.append(cols) # 标题
- print('保存中')
- wb.save(excfile) # 保存
-
- if __name__ == '__main__':
- jsfile = "./dong.json"
- excfile = "./111.xlsx"
- json2excel(jsfile, excfile)
-
-