import tkinter as tk from tkinter import ttk, filedialog, messagebox, scrolledtext import pandas as pd import os import threading import requests from openpyxl import load_workbook from openpyxl.utils import get_column_letter import re class ImageMatcherApp: def __init__(self, root): self.root = root self.root.title("图片匹配与上传工具") self.root.geometry("850x650") # 存储文件路径 self.template_path = tk.StringVar() self.image_dir = tk.StringVar() # 固定上传配置 self.upload_url = "https://cos.port.run/uploadFile" self.upload_group = "soundSynthesis" self.upload_user = "1" # 创建UI self.create_widgets() def create_widgets(self): # 主框架 main_frame = ttk.Frame(self.root, padding="10") main_frame.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S)) # 配置网格权重 self.root.columnconfigure(0, weight=1) self.root.rowconfigure(0, weight=1) main_frame.columnconfigure(1, weight=1) main_frame.rowconfigure(4, weight=1) # 标题 title_label = ttk.Label(main_frame, text="图片匹配与上传工具", font=("Arial", 16, "bold")) title_label.grid(row=0, column=0, columnspan=3, pady=10) # 模板文件选择 ttk.Label(main_frame, text="模板文件:").grid(row=1, column=0, sticky=tk.W, pady=5) ttk.Entry(main_frame, textvariable=self.template_path, width=50).grid(row=1, column=1, sticky=(tk.W, tk.E), pady=5, padx=5) ttk.Button(main_frame, text="选择模板", command=self.select_template).grid(row=1, column=2, pady=5, padx=5) # 图片目录选择 ttk.Label(main_frame, text="图片目录:").grid(row=2, column=0, sticky=tk.W, pady=5) ttk.Entry(main_frame, textvariable=self.image_dir, width=50).grid(row=2, column=1, sticky=(tk.W, tk.E), pady=5, padx=5) ttk.Button(main_frame, text="选择目录", command=self.select_image_dir).grid(row=2, column=2, pady=5, padx=5) # 按钮框架 button_frame = ttk.Frame(main_frame) button_frame.grid(row=3, column=0, columnspan=3, pady=10) ttk.Button(button_frame, text="开始处理", command=self.start_matching, width=15).pack(side=tk.LEFT, padx=5) ttk.Button(button_frame, text="清空日志", command=self.clear_log, width=15).pack(side=tk.LEFT, padx=5) # 日志框 log_frame = ttk.LabelFrame(main_frame, text="日志", padding="5") log_frame.grid(row=4, column=0, columnspan=3, sticky=(tk.W, tk.E, tk.N, tk.S), pady=10) log_frame.columnconfigure(0, weight=1) log_frame.rowconfigure(0, weight=1) self.log_text = scrolledtext.ScrolledText(log_frame, height=15, width=80, wrap=tk.WORD) self.log_text.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S)) # 状态栏 self.status_var = tk.StringVar() self.status_var.set("就绪") status_bar = ttk.Label(main_frame, textvariable=self.status_var, relief=tk.SUNKEN, anchor=tk.W) status_bar.grid(row=5, column=0, columnspan=3, sticky=(tk.W, tk.E), pady=5) # 存储生成的文件路径 self.generated_file_path = None def select_template(self): filename = filedialog.askopenfilename( title="选择模板文件", filetypes=[("Excel文件", "*.xlsx *.xls"), ("所有文件", "*.*")] ) if filename: self.template_path.set(filename) self.log(f"已选择模板文件: {filename}") def select_image_dir(self): directory = filedialog.askdirectory(title="选择图片目录") if directory: self.image_dir.set(directory) self.log(f"已选择图片目录: {directory}") def log(self, message): """在日志框中添加消息""" self.log_text.insert(tk.END, f"{message}\n") self.log_text.see(tk.END) self.root.update_idletasks() def clear_log(self): """清空日志""" self.log_text.delete(1.0, tk.END) def start_matching(self): """开始匹配处理""" if not self.template_path.get(): messagebox.showerror("错误", "请先选择模板文件！") return if not self.image_dir.get(): messagebox.showerror("错误", "请先选择图片目录！") return thread = threading.Thread(target=self.process_matching, daemon=True) thread.start() def process_matching(self): """执行匹配处理""" try: self.status_var.set("处理中...") self.log("=" * 50) self.log("开始处理...") template_path = self.template_path.get() image_dir = self.image_dir.get() self.log(f"读取模板文件: {template_path}") # 使用openpyxl加载工作簿，保留所有样式 wb = load_workbook(template_path) ws = wb.active # 获取表头（第二行） headers = [] for col in range(1, ws.max_column + 1): cell_value = ws.cell(row=2, column=col).value headers.append(str(cell_value) if cell_value else '') self.log(f"找到 {len(headers)} 列") # 查找各列的列号 col_a_index = 1 # A列是第1列 product_main_col = None image_cols = {} for idx, header in enumerate(headers, start=1): if header == '产品主图': product_main_col = idx elif '上架图片' in header: # 提取数字 match = re.search(r'(\d+)', header) if match: num = int(match.group(1)) image_cols[num] = idx if product_main_col is None: self.log("警告: 未找到'产品主图'列") else: self.log(f"找到产品主图列: 第{product_main_col}列") self.log(f"找到上架图片列: {len(image_cols)} 列") # 处理每一行（从第三行开始） processed_count = 0 for row_idx in range(3, ws.max_row + 1): # 获取A列的值 a_cell = ws.cell(row=row_idx, column=col_a_index) a_value = str(a_cell.value).strip() if a_cell.value else '' if not a_value or a_value == 'nan' or a_value == 'None': continue self.log(f"\n处理第 {row_idx} 行, A列值: {a_value}") # 处理产品主图 (图片编号1) if product_main_col is not None: img_url = self.find_and_upload_image(a_value, 1, image_dir) if img_url: ws.cell(row=row_idx, column=product_main_col, value=img_url) self.log(f" 产品主图上传成功: {img_url[:50]}...") else: self.log(f" 产品主图: 未找到或上传失败") # 处理上架图片1-6 (图片编号2-7) for i in range(1, 7): if i in image_cols: img_url = self.find_and_upload_image(a_value, i+1, image_dir) if img_url: ws.cell(row=row_idx, column=image_cols[i], value=img_url) self.log(f" 上架图片{i}上传成功: {img_url[:50]}...") else: self.log(f" 上架图片{i}: 未找到或上传失败") processed_count += 1 # 保存结果（保留所有样式） output_path = self.generate_output_path(template_path) wb.save(output_path) self.generated_file_path = output_path self.log(f"\n处理完成！共处理 {processed_count} 行数据") self.log(f"结果已保存到: {output_path}") self.status_var.set(f"处理完成！共处理 {processed_count} 行") messagebox.showinfo("完成", f"处理完成！\n共处理 {processed_count} 行数据\n结果保存在:\n{output_path}") except Exception as e: error_msg = f"处理出错: {str(e)}" self.log(f"错误: {error_msg}") self.status_var.set("处理出错") messagebox.showerror("错误", error_msg) import traceback self.log(traceback.format_exc()) def generate_output_path(self, template_path): """生成输出文件路径""" dir_path = os.path.dirname(template_path) base_name = os.path.basename(template_path) name_without_ext = os.path.splitext(base_name)[0] new_filename = f"{name_without_ext}_结果.xlsx" return os.path.join(dir_path, new_filename) def find_and_upload_image(self, a_value, num, image_dir): """查找图片并上传，返回URL""" # 查找本地图片 extensions = ['.jpg', '.jpeg', '.png'] local_file = None for ext in extensions: filename = f"{a_value}_{num}{ext}" filepath = os.path.join(image_dir, filename) if os.path.exists(filepath): local_file = filepath break if not local_file: return None # 上传图片到服务器 try: full_url = f"{self.upload_url}?group={self.upload_group}&user={self.upload_user}" # 根据文件扩展名确定content-type content_type = 'image/jpeg' if local_file.lower().endswith('.png'): content_type = 'image/png' with open(local_file, 'rb') as f: files = {'file': (os.path.basename(local_file), f, content_type)} response = requests.post(full_url, files=files, timeout=30) if response.status_code == 200: result = response.json() if result.get('err') == 0: return result.get('url') else: self.log(f" 上传失败: {result.get('msg', '未知错误')}") return None else: self.log(f" 上传请求失败，状态码: {response.status_code}") return None except Exception as e: self.log(f" 上传出错: {str(e)}") return None def main(): root = tk.Tk() app = ImageMatcherApp(root) root.mainloop() if __name__ == "__main__": main()