使用xlwt将json文件，写入xls文件

json文件

{"stock_hold_cycle":205,"end_date":"20170601","fare":0.0,"cost":0.0,"sell_balance":0.0,"user_id":"100000877","begin_date":"20160819","fund_account":"100000877","stock_code":"603031","buy_balance":0.0}
{"stock_hold_cycle":261,"end_date":"20170601","fare":0.0,"cost":0.0,"sell_balance":0.0,"user_id":"100000877","begin_date":"20160602","fund_account":"100000877","stock_code":"732031","buy_balance":0.0}
{"stock_hold_cycle":261,"end_date":"20170601","fare":46.1,"cost":64183.94,"sell_balance":60160.0,"user_id":"100000877","begin_date":"20160602","fund_account":"100000877","stock_code":"300287","buy_balance":124234.0}

上述是json文件的样式，一行一组数据。每个json文件内的每一行的key字段是一致的，不同json文件内的每一行key字段会不同。

需求

1、需要将每个json文件的数据导出，以xls的格式保存。
2、xls文件的首行是json文件的key字段，每行数据的对应key字段的值，依次写入到xls文件内，字段值要对应。

样式转换举例：

原数据为

{"stock_hold_cycle":205,"end_date":"20170601"}
{"stock_hold_cycle":20,"end_date":"20170611"}

需转成下述样式：

stock_hold_cycle	end_date
205	20170601
20	20170611

3、每一个json文件的数据一个sheet页，若有10个json文件，则需在一个xls文件内创建10个sheet，存入对应数据。

实现

1、json文件 –> 字典

with open(file, "r", encoding="utf-8") as f:
    while True:
        read_line = f.readline()
        if not read_line:
            break
        json_line = json.loads(read_line)

上述代码块，按行读取文件，然后转换成字典项，若读取行数据为空，就判断为文件读取完毕，结束读取过程。

2、对字典项进行排序

list_json_line = sorted(json_line.items(), key=lambda d:d[0]) #以字典的key排序
list_json_line = sorted(json_line.items(), key=lambda d:d[1]) #以字典的value排序

上述排序，是按照sort方法默认排序。

3、创建xls文件

wt = xlwt.Workbook() # 创建一个对象
table = wt.add_sheet("sheet1", cell_overwrite_ok=True) # 创建一个表格（sheet）页
table.write(j, i, data) #写入数据，j是行，i是列，data是写入数据
wt.save(name) #保存xls文件

源码

# -*- coding: utf-8 -*-
__author__ = "chenk"
import  os, json
import xlrd, xlwt
from collections import namedtuple

def create_xls():
    """创建xls文档对象"""
    wt = xlwt.Workbook()
    # table = wt.add_sheet("sheet1", cell_overwrite_ok=True)
    return wt

def get_tag_name(path=r"C:\Users\Administrator\Desktop\xxx\scripts", file_name="tag_name.json"):
    """pass"""
    current = os.getcwd() #获取当前运行路径
    os.chdir("/")
    os.chdir(path)
    with open(file_name, "r", encoding="utf-8") as f:
        f_read = f.read()
        read_json = json.loads(f_read)
    os.chdir("/")
    os.chdir(current)#设置运行路径为原运行路径
    return read_json

def file_to_xls(path):
    os.chdir("/")
    os.chdir(path)
    wt = create_xls()
    tag_name = get_tag_name()
    for each in os.listdir("."):
        if "_HuaAn.txt" in each:
            sheet_name = tag_name[each[:-len("_HuaAn.txt")]]["name"]
            table = wt.add_sheet(sheet_name, cell_overwrite_ok=True)    #创建table
            flag = False # 判断xls文件是否写入首行字段标题栏
            j = 1   # 列
            print("*"*20, each, sheet_name)
            with open(each, "r", encoding="utf-8") as f:
                while True:
                    read_line = f.readline()
                    if not read_line:#文件读取数据不为空
                        name = "xxx.xls"   #保存文件名
                        wt.save(name)   #保存xls文件
                        break
                    json_line = json.loads(read_line) #转换成json
                    list_json_line = sorted(json_line.items(), key=lambda d:d[0]) #以字典的key排序
                    if not flag:
                        length = len(list_json_line)
                        temp = 0
                        for i in range(length):
                            key = list_json_line[i][0]
                            table.write(0,temp,key)
                            temp += 1
                        flag = True
                    else:
                        try:
                            # i为列， j为行
                            for i in range(length):
                                try:
                                    table.write(j, i, list_json_line[i][1])
                                except:
                                    print(each, i, j, "*"*20)
                            j += 1
                        except Exception as e:
                            name = "xxx.xls"
                            wt.save(name)
                            print(each, i, j)
                            print(str(e))
                            break

if __name__ == "__main__":
    file_to_xls(r"C:\Users\Administrator\Desktop\xxx\scripts\xxx")

最终生成excel.xls文档如下：