game008_findfail/tools/excel2json/excel2json/excel2json.py

import pandas as pd
import json
import os
import glob
import shutil

# 定义一个函数来将Excel文件中的部分数据转换为JSON
def excel2json(df, json_file_path):
    # 将DataFrame的选定部分转换为JSON格式
    print(f"json_file_path: {json_file_path}")
    # print(f"json_file_path.columns: {df.columns}")
    # print(f"json_file_path.columns.values: {df.columns.values}")
    # print(f"json_file_path.index: {df.index}")
    # print(f"json_file_path.index.values: {df.index.values}")
    # print(f"json_file_path.values: {df.values}")
    df.fillna(0, inplace=True)

    keys = df.columns.values
    key_len = len(keys)
    types = df.iloc[0].values
    values = df.iloc[2:].values

    json_data = ""
    if types[0] == "string" :
        json_data = "{\n"
        for line in values :
            for i in range(1, key_len):
                if not keys[i].startswith("_"):
                    if types[i] == "string":
                        if line[i] != 0:
                            json_data += f'    "{line[0]}":"{line[i]}"\n'
                            break
                    elif types[i] == "num":
                        json_data += f'    "{line[0]}":{line[i]}\n'
                        break
        json_data += "}"
    else :
        json_data = "["
        for line in values :
            line_data = "\n    {"
            for i in range(key_len):
                if not keys[i].startswith("_"):
                    if types[i] == "string":
                        if line[i] == 0 :
                            line_data += f'"{keys[i]}":"", '
                        else:
                            line_data += f'"{keys[i]}":"{line[i]}", '
                    elif types[i] == "num":
                        line_data += f'"{keys[i]}":{(line[i])}, '
            line_data = line_data[0:-2]
            line_data += "},"
            json_data += line_data
        json_data = json_data[0:-1]
        json_data += "\n]"

    # print(f"json_data: {json_data}")

    # 写入JSON文件
    with open(json_file_path, 'w', encoding='utf-8') as json_file:
        json_file.write(json_data)


# 获取当前脚本的文件路径
script_path = os.path.abspath(__file__)

# 获取当前脚本所在的目录
script_dir = os.path.dirname(script_path)

print(f"当前脚本文件路径: {script_path}")
print(f"当前脚本所在目录: {script_dir}")

# 设置Excel和JSON文件的路径
excel_file_path = os.path.join(script_dir, "excel")  # 替换为你的Excel文件路径
json_file_path = os.path.join(script_dir + "../../../../assets/hall", "data")  # 替换为你想要保存的JSON文件路径
#print(f" 终点：{script_dir + "../../../../assets/AssetPackage"}")

#创建excel目录
if not os.path.exists(excel_file_path) :
    os.makedirs(excel_file_path)

#删除旧json目录，并创建新的
if os.path.exists(json_file_path) :
    shutil.rmtree(json_file_path)
os.makedirs(json_file_path)

#读出所有excel文件
excel_files = glob.glob(os.path.join(excel_file_path, '*.xlsx'))

for file in excel_files:
    print(f"excel file: {file}")
    file_name = os.path.basename(file)
    if not file_name.startswith("_") :
        # 读取Excel文件中的所有sheets
        xls = pd.ExcelFile(file)
        # 获取所有sheet名
        sheet_names = xls.sheet_names
        for sheet_name in sheet_names:
            if not sheet_name.startswith("_"):
                excel_data = pd.read_excel(xls, sheet_name=sheet_name)
                excel2json(excel_data, os.path.join(json_file_path, sheet_name + ".json"))
        # 关闭Excel文件
        xls.close()

print(f"Selected data from Excel file '{excel_file_path}' has been converted to JSON and saved as '{json_file_path}'.")