pythonTest/自动匹配上架图片.py

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()