''' Author: deng.yinping deng.yinping@muchinfo.cn Date: 2024-12-06 10:22:52 LastEditors: deng.yinping deng.yinping@muchinfo.cn LastEditTime: 2025-03-07 16:16:35 FilePath: \MTP20_WEB_GLOBAL_i18n_Tool\common.py Description: 这是默认设置,请设置`customMade`, 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE ''' import json import pandas as pd from openpyxl import load_workbook from openpyxl.styles import PatternFill import os class CommonUti: @staticmethod def read_from_json(file_path): # 从JSON文件中读取内容 with open(file_path, 'r', encoding='utf-8') as file: data = json.load(file) return data @staticmethod def get_leaf_paths(data, path="", result=None): # 递归函数，遍历所有终节点并将全路径和值存入字典 # 不需要翻译的Key ignore_data = ['app.name', 'mine.setting.chinese', 'mine.setting.english', 'mine.setting.enth', 'app.left1', 'app.left2'] if result is None: result = {} if isinstance(data, dict): # 如果是字典，继续遍历字典中的每个键 for key, value in data.items(): current_path = f"{path}.{key}" if path else key CommonUti.get_leaf_paths(value, current_path, result) elif isinstance(data, list): # 如果是列表，遍历每个元素 for index, item in enumerate(data): current_path = f"{path}[{index}]" CommonUti.get_leaf_paths(item, current_path, result) else: # 如果是终节点，将当前路径和值保存到字典中 if path not in ignore_data: result[path] = data return result @staticmethod def generate_dict_from_json(file_path): data = CommonUti.read_from_json(file_path) # 生成全路径的字典 leaf_paths_dict = CommonUti.get_leaf_paths(data) return leaf_paths_dict @staticmethod def generate_excle_by_oem(oem, output_folder, zh_data, en_data, th_data, tw_data, vi_data): 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['vi-VN'] = df.index.map(vi_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 = 40 # 设置第一列宽度 KEY ws.column_dimensions['B'].width = 40 # 设置第二列宽度 中文 ws.column_dimensions['C'].width = 40 # 设置第三列宽度 英文 ws.column_dimensions['D'].width = 40 # 设置第四列宽度 泰文 ws.column_dimensions['E'].width = 40 # 设置第五列宽度 繁体 ws.column_dimensions['F'].width = 40 # 设置第六列宽度 越南语 # 保存更改 wb.save(output_file) print("Excel已成功输出到 " + output_file) @staticmethod def update_excel_by_inc(oem, output_folder, inc_name): ''' 增量更新，在生成excel里标注新增的key为黄色 ''' # 读取输出的Excel文件, 格式：key(A) zh-CN(B) en-US(C) th-TH(D) zh-TW(E) vi-VN(F) 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("增量Excel已成功输出到 " + update_file) @staticmethod def generate_excle(oem, output_folder, json_folder, oem_json_folder): # 据JSON生成excel文件 zh_data = CommonUti.read_data_from_json( oem, json_folder, oem_json_folder, 'zh-CN') en_data = CommonUti.read_data_from_json( oem, json_folder, oem_json_folder, 'en-US') th_data = CommonUti.read_data_from_json( oem, json_folder, oem_json_folder, 'th-TH') tw_data = CommonUti.read_data_from_json( oem, json_folder, oem_json_folder, 'zh-TW') vi_data = CommonUti.read_data_from_json( oem, json_folder, oem_json_folder, 'vi-VN') CommonUti.generate_excle_by_oem( oem, output_folder, zh_data, en_data, th_data, tw_data, vi_data) @staticmethod def read_data_from_json(oem, json_folder, oem_json_folder, lang): common_file = json_folder + lang + '.json' common_data = CommonUti.generate_dict_from_json(common_file) # 处理OEM数据 oem_file = oem_json_folder + oem + '/locales/extras/' + lang + '.json' if os.path.exists(oem_file): oem_data = CommonUti.generate_dict_from_json(oem_file) if oem_data: for key, value in oem_data.items(): # 更新通用字典的oem个性化值(有则更新，无则添加) common_data[key] = value return common_data @staticmethod def read_from_excel(oem, output_folder): # 读取输出的Excel文件, 格式：key(A) zh-CN(B) en-US(C) th-TH(C) zh-TW(D) excel_filename = output_folder + "/excels/" + oem + ".xlsx" df = pd.read_excel(excel_filename) # 将 A 列和 C 列转换为zh-CN字典 dict_cn = dict(zip(df.iloc[:, 0], df.iloc[:, 1])) # 将 A 列和 C 列转换为en-US字典 dict_en = dict(zip(df.iloc[:, 0], df.iloc[:, 2])) # 将 A 列和 D 列转换为th-TH字典 dict_th = dict(zip(df.iloc[:, 0], df.iloc[:, 3])) # 将 A 列和 E 列转换为zh-TW字典 dict_tw = dict(zip(df.iloc[:, 0], df.iloc[:, 4])) # 将 A 列和 F 列转换为vi-VN字典 dict_vi = dict(zip(df.iloc[:, 0], df.iloc[:, 5])) return dict_cn, dict_en, dict_th, dict_tw, dict_vi @staticmethod def modify_json_values(data, modify_func, path="", extra_data=None, exclude_data=None): if isinstance(data, dict): # 如果是字典类型，遍历键值对 for key, value in data.items(): new_path = f"{path}.{key}" if path else key # 更新路径 if isinstance(value, (dict, list)): # 如果值是字典或列表，递归调用 CommonUti.modify_json_values(value, modify_func, new_path, extra_data, exclude_data) else: # 对终节点进行修改，传递完整路径和辅助字典 data[key] = modify_func( value, new_path, extra_data, exclude_data) elif isinstance(data, list): # 如果是列表类型，遍历列表中的每个元素 for index, item in enumerate(data): new_path = f"{path}[{index}]" # 更新路径为列表的索引 if isinstance(item, (dict, list)): CommonUti.modify_json_values(item, modify_func, new_path, extra_data, exclude_data) else: # 修改列表中的值，并传递完整路径 data[index] = modify_func(item, new_path, extra_data) @staticmethod def modify_func(value, path, extra_data, exclude_data): # 修改函数，基于额外字典的内容修改值 if extra_data and path in extra_data: # exclude_data 为空 或是path不在exclude_data if (exclude_data is None) or (exclude_data and path not in exclude_data): new_value = extra_data[path].strip().replace('\r\n', '\n') return new_value.replace('\n', '\r\n') # 从 extra_data 中取出新值 return value # 如果路径不在 extra_data 中，保持原值 @staticmethod def save_to_json(path, data): # 创建目录（如果不存在） directory = os.path.dirname(path) # 获取文件路径的目录部分 if not os.path.exists(directory): os.makedirs(directory) # 创建目录 # 将修改后的字典保存为 .json 文件 with open(path, 'w', encoding='utf-8') as json_file: json.dump(data, json_file, ensure_ascii=False, indent=4) @staticmethod def generate_json(oem, excel_dic, lang, output_folder, json_folder, oem_json_folder): # OEM的Lang文件 oem_input = oem_json_folder + oem + '/locales/extras/' + lang + '.json' # OEM输出文件地址 oem_output = output_folder + '/jsons/' + oem + '/' + lang + '.json' oem_dic = None if os.path.exists(oem_input): # 读取OEM的JSON文件 oem_data = CommonUti.read_from_json(oem_input) # 更新 common_data CommonUti.modify_json_values( oem_data, CommonUti.modify_func, extra_data=excel_dic) # 另存为OEM的json文件 CommonUti.save_to_json(oem_output, oem_data) # 通用JSON：oem中存在的Key，则不更新common的值 oem_dic = CommonUti.generate_dict_from_json(oem_input) print("OEM %s JSON文件已成功输出到 %s" % (lang, oem_output)) # 通用的Lang文件 common_input = json_folder + lang + '.json' # 输出文件地址 common_output = output_folder + '/jsons/' + lang + '.json' # 读取JSON文件 common_data = CommonUti.read_from_json(common_input) # 更新 common_data CommonUti.modify_json_values(common_data, CommonUti.modify_func, extra_data=excel_dic, exclude_data=oem_dic) # 另存为json文件 CommonUti.save_to_json(common_output, common_data) print("%s JSON文件已成功输出到 %s" % (lang, common_output))