import json import os import sys from natsort import natsorted from openpyxl import Workbook from openpyxl.styles import Alignment from openpyxl.styles import Border, Side, PatternFill from openpyxl.styles import Font from openpyxl.utils import get_column_letter from Util.PgUtil import * # pip install natsort # pip install openpyxl # comfyUi的提示词所在位置 comfyUiPrompts = [ { "file": "16.json", # 文件名称 "prompt": ['333', 'inputs', 'text'] # 正向提示词位置 } , { "file": "17.json", # 文件名称 "prompt": ['447', 'inputs', 'text'] # 正向提示词位置 } , { "file": "19.json", # 文件名称 "prompt": ['113', 'inputs', 'text'] # 正向提示词位置 } , { "file": "20.json", # 文件名称 "prompt": ['113', 'inputs', 'text'] # 正向提示词位置 } , { "file": "22.json", # 文件名称 "prompt": ['131', 'inputs', 'String'] # 正向提示词位置 } , { "file": "23.json", # 文件名称 "prompt": ['63', 'inputs', 'tags'] # 正向提示词位置 }, { "file": "24.json", # 文件名称 "prompt": ['21', 'inputs', 'tags'] # 正向提示词位置 } , { "file": "25.json", # 文件名称 "prompt": ['186', 'inputs', 'text'] # 正向提示词位置 } , { "file": "26.json", # 文件名称 "prompt": ['79', 'inputs', 'text_positive'] # 正向提示词位置 } , { "file": "27.json", # 文件名称 "prompt": ['89', 'inputs', 'text'] # 正向提示词位置 } ] def merge_cell(col_num): # 获取指定列数据 col_list = [cell[0].value for cell in ws.iter_rows(min_row=2, min_col=col_num, max_col=col_num)] # 合并单元格的逻辑 merge_ranges = [] start_index = 0 current_value = col_list[0] for i in range(1, len(col_list)): if col_list[i] != current_value: end_index = i - 1 if end_index >= start_index: merge_ranges.append((start_index, end_index)) current_value = col_list[i] start_index = i # 合并单元格和设置对齐 col_letter = get_column_letter(col_num) for start, end in merge_ranges: merge_range = f"{col_letter}{start + 2}:{col_letter}{end + 2}" ws.merge_cells(merge_range) for row in ws[merge_range]: for cell in row: cell.alignment = Alignment(horizontal="center", vertical="center") def setCell(cellIdx, text, font): ws[cellIdx] = text ws[cellIdx].font = font ws[cellIdx].alignment = Alignment(horizontal='center', vertical='center') if __name__ == '__main__': # 指定目录路径 directory = '../JSON/' # 获取目录下的所有文件和目录的完整路径 entries = [entry for entry in os.listdir(directory)] # 使用natsort进行自然排序 sorted_entries = natsorted(entries) # 设置字体加粗和颜色 headFont = Font(bold=True) headFont.size = 15 font = Font() font.size = 13 # 创建一个新的工作簿 wb = Workbook() # 选择默认工作表 ws = wb.active # 给工作表命名 ws.title = "绘鸭AI模板情况报表" # 向工作表中添加数据 setCell('A1', '模板编号', headFont) setCell('B1', '模板名称', headFont) setCell('C1', '模板类型', headFont) setCell('D1', '提示词模板', headFont) setCell('E1', '提示词样例', headFont) # 获取列的维度对象 col_dimension = ws.column_dimensions # 设置列宽，参数为列标识（如'A'）和宽度 col_dimension['A'].width = 12 # 设置A列的宽度为12 col_dimension['B'].width = 30 # 设置B列的宽度为30 col_dimension['C'].width = 20 # 设置C列的宽度为30 col_dimension['D'].width = 60 # 设置C列的宽度为30 col_dimension['E'].width = 60 # 设置D列的宽度为80 # 创建边框样式 border = Border(left=Side(border_style='thin', color="000000"), right=Side(border_style='thin', color="000000"), top=Side(border_style='thin', color="000000"), bottom=Side(border_style='thin', color="000000")) # 填充颜色 fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid") # 遍历entries，打印出所有文件和目录 rowIdx = 0 for entry in sorted_entries: if '.json' in entry: # 读取文件内容到JSON对象中 json_file_path = directory + entry # 用于存储JSON文件内容的变量 modelData = None # 打开并读取JSON文件 with open(json_file_path, 'r', encoding='utf-8') as file: # 加载JSON文件内容到data变量中，json.load()自动将JSON字符串转换为Python字典 modelData = json.load(file) # 模板编号 model_id = entry.lower().replace('.json', '').replace("../json/", '') # 模板名称 sql = "select model_name from t_hy_model where model_id=%s" % model_id res = execute_query(sql) model_name = res[0][0] # 区别对待 if 'prompts' in modelData: for x in modelData['prompts']: rowIdx = rowIdx + 1 # 1、WEBUI模板 # 写入模板编号 cellIdx = 'A' + str(rowIdx + 1) ws[cellIdx] = model_id ws[cellIdx].font = font ws[cellIdx].alignment = Alignment(horizontal='center', vertical='center') # 模板名称 cellIdx = 'B' + str(rowIdx + 1) ws[cellIdx] = model_name ws[cellIdx].font = font ws[cellIdx].alignment = Alignment(horizontal='center', vertical='center') # 模板类型 cellIdx = 'C' + str(rowIdx + 1) ws[cellIdx] = 'WebUI' ws[cellIdx].font = font ws[cellIdx].alignment = Alignment(horizontal='center', vertical='center') # 写入提示词 cellIdx = 'D' + str(rowIdx + 1) ws[cellIdx] = x ws[cellIdx].font = font ws[cellIdx].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) # print(json_file_path + " WEBUI模板！") else: # 2、COMFYUI模板 for x in comfyUiPrompts: if x['file'] == model_id + '.json': l1 = x['prompt'][0] l2 = x['prompt'][1] l3 = x['prompt'][2] break # 写入模板编号 rowIdx = rowIdx + 1 cellIdx = 'A' + str(rowIdx + 1) ws[cellIdx] = model_id ws[cellIdx].font = font ws[cellIdx].alignment = Alignment(horizontal='center', vertical='center') # 模板名称 cellIdx = 'B' + str(rowIdx + 1) ws[cellIdx] = model_name ws[cellIdx].font = font ws[cellIdx].alignment = Alignment(horizontal='center', vertical='center') # print(json_file_path + " COMFYUI模板！") # 模板类型 cellIdx = 'C' + str(rowIdx + 1) ws[cellIdx] = 'ComfyUI' ws[cellIdx].font = font ws[cellIdx].alignment = Alignment(horizontal='center', vertical='center') # 写入提示词 try: cellIdx = 'D' + str(rowIdx + 1) ws[cellIdx] = modelData[l1][l2][l3] ws[cellIdx].font = font ws[cellIdx].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) except Exception as err: print("Error:" + str(rowIdx)) print("model_id=" + model_id) sys.exit(0) # 设置第一行到第10行的边框和填充颜色 idx = 0 for row in ws.iter_rows(min_row=1, max_row=rowIdx + 1, max_col=ws.max_column): idx = idx + 1 for cell in row: cell.border = border # 应用边框 if idx == 1: cell.fill = fill # 应用填充颜色 # 设置所有行的行高为55 for row in range(1, ws.max_row + 1): # 假设ws.max_row是工作表的最大行数 ws.row_dimensions[row].height = 55 # 将第一列由上到下按内容一致性合并 merge_cell(1) # 将第二列由上到下按内容一致性合并 merge_cell(2) # 保存 wb.save('绘鸭AI模板.xlsx') wb.close() print("恭喜，所有操作成功完成！")