Json2Excel.py3

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import json
import os
import sys
import xlwt
import xlrd
from xlutils.copy import copy

# json 格式 {"a" : {"a1": "a111", "a2": "a2222"}, "b" : {"b1": "b111", "b2": "b2222"}}

def write_excel(dicts, excelname):
    print("-------------", excelname)
    excelpath = os.path.join(os.getcwd(), excelname)

    #excel 不存在则创建
    if not os.path.isfile(excelpath):
        print("create excel file")
        wb = xlwt.Workbook(encoding = 'ascii')
        sheet1 = wb.add_sheet(u'sheet1', cell_overwrite_ok=True)
        wb.save(excelpath)
    # 读取excel 表
    rb = xlrd.open_workbook(excelpath)  #创建新的Excel（新的workbook），建议还是用ascii编码
    wb = copy(rb)
    # 获取第一个sheet开始写入
    sheet1 = wb.get_sheet(0)
    idxX = 0
    for key in dicts:
        idxX = idxX + 1
        # 写入竖向表头内容
        sheet1.write(idxX, 0, key)
        idxJ = 0
        for _k in dicts[key]:
            idxJ = idxJ + 1
            # 写入横向表头内容
            if idxX == 0:
                sheet1.write(0, idxJ, _k)
            # 写入加入转义
            dicts[key][_k] = dicts[key][_k].replace("\n", "\\n")
            # 写入表格内容
            sheet1.write(idxX, idxJ, dicts[key][_k])
    # 保存 excel 文件
    wb.save(excelpath)
    print('----save path ', excelpath)

    
def readDictsInfile(filepath):
    with open(filepath, "r", encoding="utf-8") as fo:
        dicts = json.load(fo)
    print(len(dicts))
    return dicts

if __name__ == '__main__':
    if len(sys.argv) < 2:
        print("not have argv[1] , please input your open JSON file")
        exit(0)
    filename = sys.argv[1]
    print("------filename", filename)
    filepath = os.path.join("./", filename)
    if not os.path.isfile(filepath):
        print("file is not exits")
        exit(0)
    else:
        dicts = readDictsInfile(filepath)
    if dicts:
        arr = filename.split("/")
        excelname = arr[len(arr) - 1].split(".")[0] + ".xls"
        write_excel(dicts, excelname)