#!/usr/local/bin/python3.9


import os
import json
import pandas as pd
import sys


if len(sys.argv) < 2 or len(sys.argv) >= 3:
    print("Usage: json2cvs.py <directory path>")
    sys.exit(1)

directory_path = sys.argv[1]

info_list = []

required_fields = ['type', 'vendor',  'model', 'protocol', 'version']
#url_prefix = 'https://1.2.3.4/'

field_translation = {
        'type': '类型',
        'vendor': '厂商',
        'version': '版本',
        'model': '型号',
        'protocol': '协议',
        'filename': '文件名',
        'url': '链接'
}

for filename in os.listdir(directory_path):
    if filename.endswith('.json'):
        file_path = os.path.join(directory_path, filename)
        try:
            with open(file_path, 'r', encoding='utf-8') as f:
                data = json.load(f)
                if 'info' in data:
                    info = data['info']
                    filtered_info = {field: info.get(field, None) for field in required_fields}
                    parts = filename.split('_')
                    if len(parts) > 2:
                        filtered_info['真实厂商'] = parts[1]
                    else:
                        filtered_info['真实厂商'] = filename
                    if 'protocol' in filtered_info and isinstance(filtered_info['protocol'], list):
                        filtered_info['protocol'] = ', '.join(filtered_info['protocol'])
                    if 'url' in info:
                        filtered_info['链接'] = info['url']
                    else:
                        filtered_info['链接'] = None
                    filtered_info['文件名'] = filename
                    info_list.append(filtered_info)
        except FileNotFoundError:
            print(f"未找到文件:{file_path}")
        except json.JSONDecodeError:
            print(f"解析JSON失败:{file_path}")
        except Exception as e:
            print(f"读取文件时发生错误:{e}")
        df_info = pd.DataFrame(info_list)
columns = required_fields + ['真实厂商', '文件名', '链接']
df_info = df_info[columns]

df_info.rename(columns=field_translation, inplace=True)

excel_file = 'Info_Summary.xlsx'
with pd.ExcelWriter(excel_file, engine='xlsxwriter') as writer:
    df_info.to_excel(writer, sheet_name='Info', index=False)
    workbook  = writer.book
    worksheet = writer.sheets['Info']
    link_format = workbook.add_format({'color': 'blue', 'underline': 1})
    for row_num in range(1, len(df_info) + 1):
        url = df_info.at[8, '链接']
        if pd.notna(url):
            worksheet.write_url(row_num, len(required_fields) + 1, url, link_format, url)

print(f"所有JSON文件中的info字段已汇总到Excel文件：{excel_file}")