import tkinter as tk from tkinter import filedialog, messagebox, ttk import pandas as pd from openpyxl import load_workbook import os import sys import shutil from collections import Counter from datetime import datetime class ExcelMapperApp: def __init__(self, root): self.root = root self.root.title("Excel 自动填表工具") self.root.geometry("800x600") self.root.resizable(True, True) if getattr(sys, 'frozen', False): self.base_dir = os.path.dirname(sys.executable) else: self.base_dir = os.path.dirname(os.path.abspath(__file__)) self.source_file = tk.StringVar() self.header_row = tk.StringVar(value="4") self.source_header_row = tk.StringVar(value="2") self.df = None self.valid_values = {} # 存储 Valid Values 工作表的数据 self.status_text = tk.StringVar(value="请选择来源Excel文件") self.setup_ui() def setup_ui(self): title_label = tk.Label(self.root, text="Excel 自动填表工具", font=("Arial", 16, "bold")) title_label.pack(pady=10) file_frame = tk.LabelFrame(self.root, text="文件选择", font=("Arial", 10, "bold")) file_frame.pack(pady=10, padx=20, fill="x") tk.Label(file_frame, text="来源数据Excel:").grid(row=0, column=0, sticky="w", pady=5) tk.Entry(file_frame, textvariable=self.source_file, width=50).grid(row=0, column=1, padx=5, pady=5) tk.Button(file_frame, text="浏览", command=self.select_source_file).grid(row=0, column=2, pady=5) tk.Label(file_frame, text="来源表头行号:").grid(row=1, column=0, sticky="w", pady=5) tk.Entry(file_frame, textvariable=self.source_header_row, width=10).grid(row=1, column=1, sticky="w", padx=5, pady=5) tk.Label(file_frame, text="（第2行是字段名）", fg="gray").grid(row=1, column=1, sticky="e", padx=5, pady=5) tk.Label(file_frame, text="模板表头行号:").grid(row=2, column=0, sticky="w", pady=5) tk.Entry(file_frame, textvariable=self.header_row, width=10).grid(row=2, column=1, sticky="w", padx=5, pady=5) tk.Label(file_frame, text="（第4行是字段名）", fg="gray").grid(row=2, column=1, sticky="e", padx=5, pady=5) template_path = os.path.join(self.base_dir, "模板.xlsx") tk.Label(file_frame, text=f"目标模板: {template_path}", fg="blue").grid(row=3, column=0, columnspan=3, sticky="w", pady=3) tk.Label(file_frame, text="（数据将直接追加到模板文件末尾）", fg="green").grid(row=4, column=0, columnspan=3, sticky="w", pady=3) btn_frame = tk.Frame(self.root) btn_frame.pack(pady=10) tk.Button(btn_frame, text="🔍 查看来源结构", command=self.inspect_source, bg="#9C27B0", fg="white", font=("Arial", 10), width=14).pack(side="left", padx=5) tk.Button(btn_frame, text="🔍 检查模板表头", command=self.check_template_header, bg="#FF9800", fg="white", font=("Arial", 10), width=14).pack(side="left", padx=5) tk.Button(btn_frame, text="🚀 追加到模板", command=self.generate_output, bg="#4CAF50", fg="white", font=("Arial", 10), width=14).pack(side="left", padx=5) tk.Button(btn_frame, text="清空日志", command=self.clear_log, bg="#f44336", fg="white", font=("Arial", 10), width=14).pack(side="left", padx=5) log_frame = tk.LabelFrame(self.root, text="运行日志", font=("Arial", 10, "bold")) log_frame.pack(pady=10, padx=20, fill="both", expand=True) self.log_text = tk.Text(log_frame, height=16, font=("Courier", 9)) self.log_text.pack(side="left", fill="both", expand=True) scrollbar = tk.Scrollbar(log_frame, command=self.log_text.yview) scrollbar.pack(side="right", fill="y") self.log_text.config(yscrollcommand=scrollbar.set) status_bar = tk.Label(self.root, textvariable=self.status_text, relief="sunken", anchor="w", font=("Arial", 9), bg="#f0f0f0") status_bar.pack(side="bottom", fill="x") def log(self, msg): self.log_text.insert(tk.END, msg + "\n") self.log_text.see(tk.END) self.root.update() print(msg) def clear_log(self): self.log_text.delete(1.0, tk.END) def select_source_file(self): file_path = filedialog.askopenfilename( title="选择来源Excel文件", filetypes=[("Excel files", "*.xlsx *.xls"), ("All files", "*.*")] ) if file_path: self.source_file.set(file_path) self.status_text.set(f"已选择: {os.path.basename(file_path)}") self.log(f"📁 已选择来源文件: {file_path}") def inspect_source(self): source_path = self.source_file.get() if not source_path: messagebox.showwarning("警告", "请先选择来源Excel文件！") return self.log("\n" + "="*60) self.log("🔍 分析来源Excel结构...") try: wb = load_workbook(source_path) ws = wb.active self.log(f"📊 共 {ws.max_row} 行, {ws.max_column} 列") self.log("\n📌 前3行 × 前20列:") for row in range(1, min(4, ws.max_row + 1)): values = [] for col in range(1, min(21, ws.max_column + 1)): val = ws.cell(row=row, column=col).value if val: values.append(f"列{col}:{str(val)[:30]}") if values: self.log(f" 第{row}行: {' | '.join(values)}") self.log(f"\n📌 第2行（列名）:") row2_vals = [] for col in range(1, min(ws.max_column + 1, 30)): val = ws.cell(row=2, column=col).value if val: row2_vals.append(f"列{col}:{str(val)[:25]}") if row2_vals: self.log(f" {' | '.join(row2_vals)}") except Exception as e: self.log(f"❌ 分析失败: {str(e)}") def check_template_header(self): template_path = os.path.join(self.base_dir, "模板.xlsx") if not os.path.exists(template_path): self.log(f"❌ 找不到模板文件: {template_path}") return self.log("\n" + "="*60) self.log("🔍 检查模板表头...") try: wb = load_workbook(template_path) ws = wb.active self.log(f"📊 共 {ws.max_row} 行, {ws.max_column} 列") # 显示前5行 for row in range(1, min(6, ws.max_row + 1)): values = [] for col in range(1, min(31, ws.max_column + 1)): val = ws.cell(row=row, column=col).value if val: values.append(f"列{col}:{str(val)[:30]}") if values: self.log(f"\n📌 第{row}行: {' | '.join(values[:10])}") if len(values) > 10: self.log(f" ... 还有 {len(values)-10} 个有值单元格") else: self.log(f"\n📌 第{row}行: (全空)") # 检查 Valid Values 工作表 if "Valid Values" in wb.sheetnames: self.log(f"\n✅ 找到 'Valid Values' 工作表") ws_valid = wb["Valid Values"] self.log(f" 共 {ws_valid.max_row} 行, {ws_valid.max_column} 列") # 读取前几行 for row in range(1, min(6, ws_valid.max_row + 1)): values = [] for col in range(1, min(10, ws_valid.max_column + 1)): val = ws_valid.cell(row=row, column=col).value if val: values.append(str(val)[:20]) if values: self.log(f" Valid Values 第{row}行: {' | '.join(values)}") else: self.log(f"\n⚠️ 未找到 'Valid Values' 工作表") except Exception as e: self.log(f"❌ 检查失败: {str(e)}") def load_valid_values(self, wb): """从 Valid Values 工作表读取有效值""" valid_data = {} if "Valid Values" not in wb.sheetnames: self.log("⚠️ 未找到 'Valid Values' 工作表，将使用硬编码默认值") return valid_data ws_valid = wb["Valid Values"] # 读取第1行作为列名 headers = [] for col in range(1, ws_valid.max_column + 1): val = ws_valid.cell(row=1, column=col).value if val: headers.append(str(val).strip()) else: headers.append(f"Column_{col}") # 读取数据行 for row in range(2, ws_valid.max_row + 1): for col in range(1, len(headers) + 1): val = ws_valid.cell(row=row, column=col).value if val: header = headers[col - 1] if header not in valid_data: valid_data[header] = [] if str(val).strip() not in valid_data[header]: valid_data[header].append(str(val).strip()) return valid_data def generate_output(self): self.log("\n" + "="*60) self.log("🚀 开始处理...") source_path = self.source_file.get() template_path = os.path.join(self.base_dir, "模板.xlsx") if not source_path: messagebox.showwarning("警告", "请选择来源Excel文件！") return if not os.path.exists(template_path): self.log(f"❌ 找不到模板文件: {template_path}") messagebox.showerror("错误", f"找不到模板文件:\n{template_path}") return try: header_row = int(self.header_row.get()) source_header = int(self.source_header_row.get()) # ========== 1. 备份模板 ========== backup_path = template_path.replace(".xlsx", f"_backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx") shutil.copy2(template_path, backup_path) self.log(f"📦 已备份模板到: {backup_path}") # ========== 2. 读取来源数据 ========== self.log(f"📖 读取来源数据: {source_path}") self.log(f"📌 使用第 {source_header} 行作为列名") self.df = pd.read_excel(source_path, header=source_header - 1) self.log(f"✅ 成功读取 {len(self.df)} 行数据") # ========== 3. 加载模板并读取 Valid Values ========== self.log(f"\n📖 加载模板: {template_path}") wb = load_workbook(template_path) ws = wb.active # 加载 Valid Values self.valid_values = self.load_valid_values(wb) self.log(f"📋 从 Valid Values 读取到 {len(self.valid_values)} 个字段的有效值") # ========== 4. 验证模板表头 ========== col_indices = {} for col in range(1, ws.max_column + 1): cell_value = ws.cell(row=header_row, column=col).value if cell_value: col_name = str(cell_value).strip() if col_name and not col_name.startswith("Unnamed"): col_indices[col_name] = col self.log(f"📊 模板中共有 {len(col_indices)} 个有名称的列") # ========== 5. 找到已有数据的最后一行 ========== last_row = 5 for row in range(5, ws.max_row + 2): is_empty = True for col in range(1, min(20, ws.max_column + 1)): if ws.cell(row=row, column=col).value is not None: is_empty = False break if is_empty: last_row = row break else: last_row = ws.max_row + 1 self.log(f"📌 已有数据到第 {last_row - 1} 行，从第 {last_row} 行开始追加") # ========== 6. 定义默认值映射 ========== # 格式: 目标列名 -> (值来源, 处理方式) # 值来源: 'fixed' 固定值, 'valid' 从Valid Values取第一个, 'source' 从来源取, 'computed' 计算 default_mapping = { # 固定值 "Variant Type": ("fixed", "Not Variant"), "Minimum Order Quantity": ("fixed", 1), "Force Quantity Multiplier": ("fixed", 1), "Display Set Quantity": ("fixed", 1), "Ship Type": ("valid", "Ship Type"), # 从Valid Values取第一个 "Lead Time": ("fixed", 48), "Replacement Lead Time": ("fixed", 120), "Flat Pack": ("fixed", "Yes"), "Assembly Required": ("fixed", "Yes"), "Canada Product Restriction": ("fixed", "Yes"), "CARB Phase II Compliant (formaldehyde emissions)": ("fixed", "Yes"), "Commercial Warranty": ("fixed", "Yes"), "CANFER Compliant": ("fixed", "Yes"), "Commercial Warranty Length": ("fixed", "1 Years"), "Composite Wood Product (CWP)": ("fixed", "Yes"), "Country Of Manufacturer": ("fixed", "China"), "General Certificate of Conformity (GCC)": ("fixed", "Yes"), "Hazard Class(es)": ("fixed", "Does Not Apply"), "Hazardous Material / Dangerous Goods": ("fixed", "No"), "Hazardous Material Weight": ("fixed", "Does Not Apply"), "ISTA Certified": ("popup", ""), # 弹出输入框 "Level of Assembly": ("fixed", "Full Assembly Needed"), "Packing Group": ("fixed", "Does Not Apply"), "Reason for Restriction": ("fixed", "Does Not Apply"), "Supplier Intended and Approved Use": ("fixed", "Residential Use"), "TSCA Title VI Compliant (formaldehyde emissions)": ("fixed", "Yes"), "UN or ID number": ("fixed", "Does Not Apply"), "Uniform Packaging and Labeling Regulations (UPLR) Compliant": ("fixed", "Yes"), "Warning Required": ("fixed", "No"), "Warranty Length": ("fixed", "1 Years"), "Battery or Batteries Included": ("fixed", "No"), # 动态值（从来源取） "Product Type": ("source", "产品类目"), "Brand": ("valid", "Brand"), # 从Valid Values取第一个 } # ========== 7. 匹配来源列 ========== source_mapping = { "Item Code": "Item Code", "产品名称": "产品名称", "优惠单价": "优惠单价", "产品重量": "产品重量(磅)", "包装尺寸-重量": "包装尺寸-重量(磅)", "包装尺寸-高度": "包装尺寸-高度(英寸)", "包装尺寸-宽度": "包装尺寸-宽度(英寸)", "包装尺寸-长度": "包装尺寸-长度(英寸)", "颜色": "颜色", "材质": "材质", "组装长度": "组装长度(英寸)", "组装高度": "组装高度(英寸)", "组装宽度": "组装宽度(英寸)", "产品类目": "产品类目", } self.log(f"\n🔍 匹配来源列:") source_map = {} for key, expected in source_mapping.items(): found = False for col in self.df.columns: if col == expected: source_map[key] = col found = True break if found: self.log(f" ✅ '{key}' -> '{source_map[key]}'") else: self.log(f" ❌ '{key}' -> 未找到") source_map[key] = None # ========== 8. 填充数据 ========== def get_key(row): item = row.get("Item Code", "") name = row.get("产品名称", "") return f"{item}_{name}" keys = [get_key(row) for _, row in self.df.iterrows()] key_count = Counter(keys) key_index = Counter() row_num = last_row filled_count = 0 self.log(f"\n✍️ 开始追加数据...") for idx, (_, src_row) in enumerate(self.df.iterrows()): item_code = src_row.get("Item Code", "") product_name = src_row.get("产品名称", "") if pd.isna(item_code): item_code = "" if pd.isna(product_name): product_name = "" key = get_key(src_row) if key_count[key] > 1: key_index[key] += 1 suffix = f".{key_index[key] - 1}" mfr_suffix = f".{key_index[key]}" else: suffix = "" mfr_suffix = "" self.log(f"\n--- 第 {idx+1}/{len(self.df)} 行 ---") self.log(f" Item Code: '{item_code}'") self.log(f" 产品名称: '{str(product_name)[:50]}...'") fill_count = 0 # ===== Supplier Part Number ===== if "Supplier Part Number" in col_indices: val = f"{item_code}{product_name}{suffix}" ws.cell(row=row_num, column=col_indices["Supplier Part Number"], value=val) fill_count += 1 # ===== Manufacturer Part Number ===== if "Manufacturer Part Number" in col_indices: val = f"{item_code}{product_name}{mfr_suffix}" ws.cell(row=row_num, column=col_indices["Manufacturer Part Number"], value=val) fill_count += 1 # ===== Base Cost ===== if "Base Cost" in col_indices and source_map.get("优惠单价"): col = source_map["优惠单价"] val = src_row.get(col, "") if pd.notna(val): ws.cell(row=row_num, column=col_indices["Base Cost"], value=val) fill_count += 1 # ===== MSRP ===== if "Manufacturer Suggested Retail Price" in col_indices and source_map.get("优惠单价"): col = source_map["优惠单价"] val = src_row.get(col, 0) try: num = float(val) if pd.notna(val) else 0 ws.cell(row=row_num, column=col_indices["Manufacturer Suggested Retail Price"], value=num + 70) fill_count += 1 except: pass # ===== Product Weight ===== if "Product Weight" in col_indices and source_map.get("产品重量"): col = source_map["产品重量"] val = src_row.get(col, "") if pd.notna(val): ws.cell(row=row_num, column=col_indices["Product Weight"], value=val) fill_count += 1 # ===== Carton 尺寸 ===== carton_map = [ ("Carton Weight 1", "包装尺寸-重量"), ("Carton Height 1", "包装尺寸-高度"), ("Carton Width 1", "包装尺寸-宽度"), ("Carton Depth 1", "包装尺寸-长度"), ] for target, src_name in carton_map: if target in col_indices and source_map.get(src_name): col = source_map[src_name] val = src_row.get(col, "") if pd.notna(val): ws.cell(row=row_num, column=col_indices[target], value=val) fill_count += 1 # ===== Color/Material ===== cm_map = [ ("Base Color", "颜色"), ("Base Material", "材质"), ("Color", "颜色"), ("Material", "材质"), ("Top Color", "颜色"), ("Top Material", "材质"), ] for target, src_name in cm_map: if target in col_indices and source_map.get(src_name): col = source_map[src_name] val = src_row.get(col, "") if pd.notna(val): ws.cell(row=row_num, column=col_indices[target], value=val) fill_count += 1 # ===== Overall 尺寸 ===== overall_map = [ ("Overall Depth - Front to Back", "组装长度"), ("Overall Height - Top to Bottom", "组装高度"), ("Overall Product Weight", "产品重量"), ("Overall Width - Side to Side", "组装宽度"), ] for target, src_name in overall_map: if target in col_indices and source_map.get(src_name): col = source_map[src_name] val = src_row.get(col, "") if pd.notna(val): ws.cell(row=row_num, column=col_indices[target], value=val) fill_count += 1 # ===== 默认值字段 ===== variant_type = "Not Variant" # 默认值 for target_col, (source_type, source_val) in default_mapping.items(): if target_col not in col_indices: continue col_idx = col_indices[target_col] if source_type == "fixed": # 固定值 ws.cell(row=row_num, column=col_idx, value=source_val) fill_count += 1 self.log(f" ✅ {target_col} = '{source_val}' (固定值)") elif source_type == "valid": # 从 Valid Values 取第一个值 if source_val in self.valid_values and self.valid_values[source_val]: val = self.valid_values[source_val][0] ws.cell(row=row_num, column=col_idx, value=val) fill_count += 1 self.log(f" ✅ {target_col} = '{val}' (从Valid Values)") else: self.log(f" ⚠️ {target_col}: Valid Values中无数据，使用默认值'Not Variant'") ws.cell(row=row_num, column=col_idx, value="Not Variant") fill_count += 1 elif source_type == "source": # 从来源取 if source_map.get(source_val): col = source_map[source_val] val = src_row.get(col, "") if pd.notna(val): ws.cell(row=row_num, column=col_idx, value=val) fill_count += 1 self.log(f" ✅ {target_col} = '{val}' (从来源)") else: self.log(f" ⚠️ {target_col}: 来源为空") else: self.log(f" ⚠️ {target_col}: 找不到来源列'{source_val}'") elif source_type == "popup": # 弹出输入框（ISTA Certified） self.log(f" ⏳ 请为 {target_col} 输入值...") # 这里用简单方式：如果10秒没输入就跳过 result = self.show_input_dialog(f"请输入 {target_col}", "包装认证 (ISTA Certified):") if result: ws.cell(row=row_num, column=col_idx, value=result) fill_count += 1 self.log(f" ✅ {target_col} = '{result}' (用户输入)") else: self.log(f" ⏭️ {target_col}: 用户跳过") # ===== 特殊逻辑：Variant Grouping 1/2 ===== # Variant Grouping 1: 如果 Variant Type 是 Not Variant 则不填，否则填 "Color" if "Variant Grouping 1" in col_indices: if variant_type != "Not Variant": ws.cell(row=row_num, column=col_indices["Variant Grouping 1"], value="Color") fill_count += 1 if "Variant Attribute Name On Site 1" in col_indices: if variant_type != "Not Variant": ws.cell(row=row_num, column=col_indices["Variant Attribute Name On Site 1"], value="Finish") fill_count += 1 if "Variant Grouping 2" in col_indices: if variant_type != "Not Variant": ws.cell(row=row_num, column=col_indices["Variant Grouping 2"], value="Size") fill_count += 1 if "Variant Attribute Name On Site 2" in col_indices: if variant_type != "Not Variant": ws.cell(row=row_num, column=col_indices["Variant Attribute Name On Site 2"], value="Dimensions") fill_count += 1 self.log(f" 📊 本行填充 {fill_count} 个字段") row_num += 1 filled_count += 1 # ========== 9. 保存 ========== wb.save(template_path) self.log(f"\n✅ 成功追加到模板: {template_path}") self.log(f"📊 共追加 {filled_count} 行数据") self.log(f"📦 备份文件: {backup_path}") self.status_text.set(f"✅ 已追加 {filled_count} 行到模板") messagebox.showinfo("成功", f"已追加 {filled_count} 行数据到模板文件:\n{template_path}\n\n" f"备份文件保存在: {backup_path}" ) except Exception as e: self.log(f"\n❌ 错误: {str(e)}") import traceback self.log(traceback.format_exc()) messagebox.showerror("错误", f"生成失败:\n{str(e)}") def show_input_dialog(self, title, prompt): """显示输入对话框，10秒超时自动跳过""" result = tk.StringVar() dialog = tk.Toplevel(self.root) dialog.title(title) dialog.geometry("400x120") dialog.transient(self.root) dialog.grab_set() tk.Label(dialog, text=prompt, font=("Arial", 11)).pack(pady=10) entry = tk.Entry(dialog, width=40) entry.pack(pady=5) entry.focus() # 超时计数器 timeout_seconds = 10 time_label = tk.Label(dialog, text=f"剩余 {timeout_seconds} 秒...", fg="gray") time_label.pack(pady=5) def on_ok(): result.set(entry.get()) dialog.destroy() def on_cancel(): dialog.destroy() def countdown(count): if count <= 0: dialog.destroy() return time_label.config(text=f"剩余 {count} 秒...") dialog.after(1000, countdown, count - 1) tk.Button(dialog, text="确定", command=on_ok, width=10).pack(side="left", padx=20, pady=10) tk.Button(dialog, text="跳过", command=on_cancel, width=10).pack(side="right", padx=20, pady=10) dialog.after(1000, countdown, timeout_seconds - 1) self.root.wait_window(dialog) return result.get() if __name__ == "__main__": root = tk.Tk() app = ExcelMapperApp(root) root.mainloop()