sample_row_dict1 = {
    "name": "登录",
    "output": [],
    "request": {
        "headers": {},
        "json": {
            "holderAccount": 2063701,
            "mac": "",
            "operCode": "cgbzlzy3",
            "operPwd": "1qaz@WSX"
        },
        "method": "POST",
        "url": "/test/login"
    },
    "validate": [
        {
            "eq": []
        }
    ],
    "variables": {}
}

sample_row_dict2 = {
    "name": "登录",
    "output": [],
    "request": {
        "headers": {},
        "json": {
            "certificateNo": "041@769728763-7@CHFM@00000002",
            "mac": "",
            "operCode": "cgbzlzy3",
        },
        "method": "POST",
        "url": "/test/login"
    },
    "validate": [
        {
            "eq": []
        }
    ],
    "variables": {}
}

import pandas as pd
import os
import json


def get_processed_row_dict(row_dict):
    processed_row_dict = dict()
    # TODO name 键对应的值需另做处理
    processed_row_dict['name'] = row_dict.get("name")
    # 获取request键
    request = row_dict.get("request")
    if request is None:
        raise Exception("【request】键不存在，数据格式错误")

    processed_row_dict['method'] = request.get("method")
    processed_row_dict['url'] = request.get("url")

    if request.get("json") is None:
        request["json"] = dict()
    for key, value in request["json"].items():
        processed_row_dict[key] = value
    return processed_row_dict


def write_to_excel(row_dict_list, file_name):
    processed_row_dict_list = []
    for row_dict in row_dict_list:
        processed_row_dict = get_processed_row_dict(row_dict)
        processed_row_dict_list.append(processed_row_dict)
    df = pd.DataFrame(processed_row_dict_list)
    df.to_excel(file_name, index=False)
    return processed_row_dict_list


def get_dir_json_files(dir_path):
    child_dir_list = os.listdir(dir_path)
    dirs = [i for i in child_dir_list if os.path.isdir(os.path.join(dir_path, i))]
    files = [os.path.join(dir_path, i) for i in child_dir_list if
             os.path.isfile(os.path.join(dir_path, i)) and i[i.rfind(".") + 1:] == 'json']
    if dirs:
        for i in dirs:
            files += get_dir_json_files(os.path.join(dir_path, i))
    return files


def write_to_excel_from_dir(excel_file_name, json_file_dir, json_file_encoding='utf-8'):
    json_file_list = get_dir_json_files(json_file_dir)
    row_dict_list = []
    for json_file in json_file_list:
        fp = open(json_file, 'r', encoding=json_file_encoding)
        row_dict = json.load(fp)
        fp.close()
        row_dict_list.append(row_dict)
    processed_row_dict_list = []
    for row_dict in row_dict_list:
        processed_row_dict = get_processed_row_dict(row_dict)
        processed_row_dict_list.append(processed_row_dict)
    df = pd.DataFrame(processed_row_dict_list)
    df.to_excel(excel_file_name, index=False)
    return processed_row_dict_list


def write_to_excel_from_dir_new(excel_file_dir, json_file_dir, json_file_encoding='utf-8'):
    json_file_list = get_dir_json_files(json_file_dir)
    for json_file in json_file_list:
        fp = open(json_file, 'r', encoding=json_file_encoding)
        row_dict = json.load(fp)
        fp.close()
        processed_row_dict = get_processed_row_dict(row_dict)
        df = pd.DataFrame([processed_row_dict])
        json_file_name = json_file[json_file.rfind("\\") + 1:]
        excel_file_name = json_file_name[:json_file_name.rfind(r".")] + r".xlsx"
        df.to_excel(excel_file_dir + "\\" + excel_file_name, index=False)


# 先确保以下依赖存在
# pip install -i https://pypi.douban.com/simple --trusted-host pypi.douban.com xlrd
# pip install -i https://pypi.douban.com/simple xlsxwriter
# pip install -i https://pypi.douban.com/simple openpyxl
# pip install -i https://pypi.douban.com/simple pandas
if __name__ == "__main__":
    # row_dict_list = [sample_row_dict1, sample_row_dict2]
    # file_name = "test_to_excel.xlsx"
    # write_to_excel(row_dict_list, file_name)
    # file_list = get_dir_json_files(r"D:\learnproject\nlp-learn\json_file")
    file_name = r"D:\learnproject\nlp-learn\json_file"
    write_to_excel_from_dir_new(file_name, r"D:\learnproject\nlp-learn\json_file")