MTP20_i18n_Tool/manage_generate_excel.py

import json
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
import os

output_folder="output_manage"

def read_from_json(file_path):
    # 从JSON文件中读取内容
    with open(file_path, 'r', encoding='utf-8') as file:
        data = json.load(file)

    return data


# 递归函数，遍历所有终节点并将全路径和值存入字典
def get_leaf_paths(data, path="", result=None):
    # 不需要翻译的Key
    ignore_data = ['app.name', 'mine.setting.chinese',
                   'mine.setting.english', 'mine.setting.enth']
    if result is None:
        result = {}

    if isinstance(data, dict):
        # 如果是字典，继续遍历字典中的每个键
        for key, value in data.items():
            current_path = f"{path}.{key}" if path else key
            get_leaf_paths(value, current_path, result)
    elif isinstance(data, list):
        # 如果是列表，遍历每个元素
        for index, item in enumerate(data):
            current_path = f"{path}[{index}]"
            get_leaf_paths(item, current_path, result)
    else:
        # 如果是终节点，将当前路径和值保存到字典中
        if path not in ignore_data:
            result[path] = data

    return result


def generate_dict_from_json(file_path):
    data = read_from_json(file_path)
    # 生成全路径的字典
    leaf_paths_dict = get_leaf_paths(data)
    return leaf_paths_dict


def read_data_from_json(oem, lang):
    # C:\Workspaces\Code_Git\MTP2.0_NEWMANAGE_WEB\public\locales\zh-CN.json
    common_file = 'C:/Workspaces/Code_Git/MTP2.0_NEWMANAGE_WEB\public/locales/' + lang + '.json'
    common_data = generate_dict_from_json(common_file)

    '''
    管理后台暂无OEM 
    # C:\Workspaces\Code_Git\MTP2.0_NEWMANAGE_WEB\oem\tss\locales\extras\zh-CN.json
    oem_file = 'C:/Workspaces/Code_Git/MTP20_WEB_GLOBAL/oem/' + \
        oem + '/locales/extras/' + lang + '.json'
    oem_data = generate_dict_from_json(oem_file)

    if oem_data:
        for key, value in oem_data.items():
            # 更新通用字典的oem个性化值(有则更新，无则添加)
            common_data[key] = value
    '''
    
    return common_data


def generate_excle_by_oem(oem):
    zh_data = read_data_from_json(oem, 'zh-CN')
    en_data = read_data_from_json(oem, 'en-US')
    th_data = read_data_from_json(oem, 'th-TH')
    tw_data = read_data_from_json(oem, 'zh-TW')

    df = pd.DataFrame.from_dict(zh_data, orient='index', columns=['zh-CN'])

    # 将 其它 的值合并到 DataFrame 中
    df['en-US'] = df.index.map(en_data).fillna('')
    df['th-TH'] = df.index.map(th_data).fillna('')
    df['zh-TW'] = df.index.map(tw_data).fillna('')

    # 重置索引，以便将索引变为一列
    df.reset_index(inplace=True)
    df.rename(columns={'index': 'Key'}, inplace=True)  # 将索引列重命名为 'Key'

    # 导出到 Excel 文件
    output_file = output_folder + '/excels/' + oem + '.xlsx'
    df.to_excel(output_file, index=False)

    # 使用 openpyxl 设置列宽
    wb = load_workbook(output_file)
    ws = wb.active

    # 指定列宽
    ws.column_dimensions['A'].width = 60  # 设置第一列宽度
    ws.column_dimensions['B'].width = 50  # 设置第二列宽度
    ws.column_dimensions['C'].width = 50  # 设置第三列宽度
    ws.column_dimensions['D'].width = 50  # 设置第四列宽度
    ws.column_dimensions['E'].width = 50  # 设置第五列宽度

    # 保存更改
    wb.save(output_file)

    print("字典已成功输出到 " + output_file)

def update_excel_by_inc(oem, inc_name):
    ''' 增量更新，在生成excel里标注新增的key为黄色 '''
    # 读取输出的Excel文件, 格式：key(A)	zh-CN(B)	en-US(C)	th-TH(C)    zh-TW(D)
    inc_file = output_folder + "/excels/" + inc_name + ".xlsx"
    if not os.path.exists(inc_file):
        return
        
    df = pd.read_excel(inc_file)
    # 将 A 列和 C 列转换为zh-CN字典
    old_dic = dict(zip(df.iloc[:, 0], df.iloc[:, 1]))
    
    new_file = output_folder + "/excels/" + oem + ".xlsx"
    wb = load_workbook(new_file)
    sheet = wb.active  #  

    # 定义黄色填充样式
    yellow_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

    # 遍历第一列
    for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row, min_col=1, max_col=1):
        cell = row[0]
        if cell.value not in old_dic:
            cell.fill = yellow_fill

    # 保存文件
    update_file = output_folder + "/excels/" + oem + "_inc.xlsx"
    wb.save(update_file)  # 保存为新文件，避免覆盖原文件
    
    print("增量字典已成功输出到 " + update_file)

if __name__ == '__main__':
    # 根据代码json地址生成excel
    generate_excle_by_oem('newmanage')
    
    # 增量更新，在生成excel里标注新增的key为黄色
    update_excel_by_inc('newmanage', '20241201_newmanage_inc')