import os
import json
from openpyxl import load_workbook
from tqdm import tqdm
def update_excel_with_data(excel_path, data_root):
    # 加载工作簿并获取活动工作表
    workbook = load_workbook(excel_path)
    sheet = workbook.active
    
    # 确定开始行
    last_row = sheet.max_row + 1

    # 遍历目录和文件
    for root, dirs, files in tqdm(os.walk(data_root)):
        for file in files:
            if file.endswith('.json'):
                json_path = os.path.join(root, file)
                image_path = os.path.splitext(json_path)[0] + '.jpg'  # 假设图像文件是jpg格式

                # 读取JSON文件
                with open(json_path, 'r', encoding='utf-8') as json_file:
                    data = json.load(json_file)
                    content = data.get('content', '')

                # 提取文件夹名称作为关键字
                keyword = os.path.basename(root)

                # 填充Excel表格
                sheet.cell(row=last_row, column=1, value=last_row - 1)  # 序号
                sheet.cell(row=last_row, column=2, value=keyword)       # 关键字
                sheet.cell(row=last_row, column=3, value=content)       # 文本内容
                sheet.cell(row=last_row, column=4, value=1)             # 数据功能类型
                sheet.cell(row=last_row, column=5, value=image_path)    # 对应图片路径

                # 为下一条记录增加行索引
                last_row += 1

    # 保存更新后的工作簿
    workbook.save(excel_path)

# 路径需要根据您的实际情况调整
excel_path = '/data1/Hatcher/model/project/MPKFNet/大规模数据库-文本.xlsx'
data_root = '/data5/Hatcher/data/llma/result'

# 调用此函数以运行
update_excel_with_data(excel_path, data_root)