|
|
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("恭喜，所有操作成功完成！")
|
