import tkinter as tk from tkinter import filedialog, messagebox import pandas as pd from openpyxl import load_workbook import os import sys import re from collections import Counter class ExcelMapperApp: def __init__(self, root): self.root = root self.root.title("Excel 自动填表工具") self.root.geometry("750x550") 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="3") self.source_header_row = tk.StringVar(value="2") self.df = None 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="（第3行是字段名）", fg="gray").grid(row=2, column=1, sticky="e", padx=5, pady=5) template_path = os.path.join(self.base_dir, "模板.xlsx") output_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=f"输出文件: {output_path}", 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.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=14, 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} 列") # 判断是横向还是纵向 first_row_vals = [] for col in range(1, min(20, ws.max_column + 1)): val = ws.cell(row=1, column=col).value if val: first_row_vals.append(str(val)) is_horizontal = len(first_row_vals) >= 3 and not any(':' in v for v in first_row_vals[:3]) if is_horizontal: self.log(f"\n📌 检测到：横向格式（多列表格）") self.log(f" 第1行有 {len(first_row_vals)} 个有值单元格") self.log(f" 前几个: {first_row_vals[:5]}") # 显示第2行列名 row2_vals = [] for col in range(1, min(30, ws.max_column + 1)): val = ws.cell(row=2, column=col).value if val: row2_vals.append(str(val)) if row2_vals: self.log(f"\n📌 第2行（列名）: {row2_vals[:10]}...") else: self.log(f"\n📌 检测到：纵向格式（从上往下排列）") # 显示前20行 self.log(f"\n📌 前20行内容:") for row in range(1, min(21, ws.max_row + 1)): val = ws.cell(row=row, column=1).value if val: self.log(f" 第{row}行: {str(val)[:80]}") except Exception as e: self.log(f"❌ 分析失败: {str(e)}") def parse_vertical_format(self, ws): """解析纵向格式（Key: Value 格式），相同Item Code合并""" products_dict = {} # 用字典存储，key是Item Code current_item_code = None current_product = {} field_map = { "Item Code": "Item Code", "产品名称": "产品名称", "颜色": "颜色", "材质": "材质", "组装长度 (英寸)": "组装长度(英寸)", "组装宽度 (英寸)": "组装宽度(英寸)", "组装高度 (英寸)": "组装高度(英寸)", "产品重量 (磅)": "产品重量(磅)", "长度 (英寸)": "包装尺寸-长度(英寸)", "宽度 (英寸)": "包装尺寸-宽度(英寸)", "高度 (英寸)": "包装尺寸-高度(英寸)", "重量 (磅)": "包装尺寸-重量(磅)", } price_pattern = re.compile(r'^\$?([\d.]+)$') for row in range(1, ws.max_row + 1): val = ws.cell(row=row, column=1).value if val is None: continue val_str = str(val).strip() if not val_str: continue # 检测是否是 "Key: Value" 格式 if ':' in val_str: parts = val_str.split(':', 1) key = parts[0].strip() value = parts[1].strip() if len(parts) > 1 else '' if key == "Item Code": # 保存当前产品到字典 if current_item_code and current_product: if current_item_code in products_dict: # 合并（不覆盖已有字段） for k, v in current_product.items(): if k not in products_dict[current_item_code] or not products_dict[current_item_code][k]: products_dict[current_item_code][k] = v else: products_dict[current_item_code] = current_product.copy() # 开始新产品 current_item_code = value current_product = {"Item Code": value} else: mapped_key = field_map.get(key, key) # 只有当前产品中没有该字段时才设置（保留第一次出现的值） if mapped_key not in current_product or not current_product[mapped_key]: current_product[mapped_key] = value else: # 没有冒号，可能是价格 match = price_pattern.match(val_str.replace('$', '').strip()) if match and current_item_code: price_val = match.group(1) # 判断是 Base Cost 还是 MSRP if current_item_code in products_dict: existing = products_dict[current_item_code] if "优惠单价" not in existing or not existing["优惠单价"]: existing["优惠单价"] = price_val elif "Manufacturer Suggested Retail Price" not in existing or not existing["Manufacturer Suggested Retail Price"]: existing["Manufacturer Suggested Retail Price"] = str(float(price_val) + 70) else: if "优惠单价" not in current_product or not current_product["优惠单价"]: current_product["优惠单价"] = price_val elif "Manufacturer Suggested Retail Price" not in current_product or not current_product["Manufacturer Suggested Retail Price"]: current_product["Manufacturer Suggested Retail Price"] = str(float(price_val) + 70) # 保存最后一个产品 if current_item_code and current_product: if current_item_code in products_dict: for k, v in current_product.items(): if k not in products_dict[current_item_code] or not products_dict[current_item_code][k]: products_dict[current_item_code][k] = v else: products_dict[current_item_code] = current_product # 转换为列表 return list(products_dict.values()) 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. 检测并读取来源数据 ========== wb_source = load_workbook(source_path) ws_source = wb_source.active # 检测格式 first_row_vals = [] for col in range(1, min(20, ws_source.max_column + 1)): val = ws_source.cell(row=1, column=col).value if val: first_row_vals.append(str(val)) is_horizontal = len(first_row_vals) >= 3 and not any(':' in v for v in first_row_vals[:3]) if is_horizontal: self.log(f"📌 检测到横向格式，使用 pandas 读取...") self.df = pd.read_excel(source_path, header=source_header - 1) self.log(f"✅ 成功读取 {len(self.df)} 行数据") else: self.log(f"📌 检测到纵向格式，使用解析器读取...") products = self.parse_vertical_format(ws_source) self.log(f"✅ 成功解析 {len(products)} 个产品") self.df = pd.DataFrame(products) self.log(f"📋 解析到的字段: {list(self.df.columns)}") # ========== 2. 加载模板（直接修改原文件） ========== self.log(f"\n📖 加载模板: {template_path}") wb = load_workbook(template_path) ws = wb.active # ========== 3. 找到已有数据的最后一行 ========== # 从第4行开始找，找到第一个完全空的行 last_row = 3 # 从第4行开始检查 for row in range(4, ws.max_row + 2): is_empty = True for col in range(1, min(20, ws.max_column + 1)): # 检查前20列 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} 行开始追加") # ========== 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 # ========== 5. 目标列映射 ========== target_cols = [ "Supplier Part Number", "Manufacturer Part Number", "Base Cost", "Manufacturer Suggested Retail Price", "Product Weight", "Carton Weight 1", "Carton Height 1", "Carton Width 1", "Carton Depth 1", "Base Color", "Base Material", "Color", "Material", "Overall Depth - Front to Back", "Overall Height - Top to Bottom", "Overall Product Weight", "Overall Width - Side to Side", "Top Color", "Top Material" ] found_cols = {} for target in target_cols: if target in col_indices: found_cols[target] = col_indices[target] if not found_cols: self.log(f"\n❌ 没有找到任何目标列！") return # ========== 6. 匹配来源列 ========== source_mapping = { "Item Code": "Item Code", "产品名称": "产品名称", "优惠单价": "优惠单价", "产品重量": "产品重量(磅)", "包装尺寸-重量": "包装尺寸-重量(磅)", "包装尺寸-高度": "包装尺寸-高度(英寸)", "包装尺寸-宽度": "包装尺寸-宽度(英寸)", "包装尺寸-长度": "包装尺寸-长度(英寸)", "颜色": "颜色", "材质": "材质", "组装长度": "组装长度(英寸)", "组装高度": "组装高度(英寸)", "组装宽度": "组装宽度(英寸)", } 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 not found: # 部分匹配 for col in self.df.columns: if expected in col or col in expected: source_map[key] = col found = True break if not found: source_map[key] = None # ========== 7. 填充数据 ========== 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 found_cols: val = f"{item_code}{product_name}{suffix}" ws.cell(row=row_num, column=found_cols["Supplier Part Number"], value=val) fill_count += 1 # Manufacturer Part Number if "Manufacturer Part Number" in found_cols: val = f"{item_code}{product_name}{mfr_suffix}" ws.cell(row=row_num, column=found_cols["Manufacturer Part Number"], value=val) fill_count += 1 # Base Cost if "Base Cost" in found_cols: col = source_map.get("优惠单价") if col and col in src_row: val = src_row[col] if pd.notna(val): ws.cell(row=row_num, column=found_cols["Base Cost"], value=val) fill_count += 1 # MSRP if "Manufacturer Suggested Retail Price" in found_cols: col = source_map.get("优惠单价") if col and col in src_row: val = src_row[col] try: num = float(val) if pd.notna(val) else 0 result = num + 70 ws.cell(row=row_num, column=found_cols["Manufacturer Suggested Retail Price"], value=result) fill_count += 1 except: pass else: msrp_col = source_map.get("Manufacturer Suggested Retail Price") if msrp_col and msrp_col in src_row: val = src_row[msrp_col] if pd.notna(val): ws.cell(row=row_num, column=found_cols["Manufacturer Suggested Retail Price"], value=val) fill_count += 1 # Product Weight if "Product Weight" in found_cols: col = source_map.get("产品重量") if col and col in src_row: val = src_row[col] if pd.notna(val): ws.cell(row=row_num, column=found_cols["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 found_cols: col = source_map.get(src_name) if col and col in src_row: val = src_row[col] if pd.notna(val): ws.cell(row=row_num, column=found_cols[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 found_cols: col = source_map.get(src_name) if col and col in src_row: val = src_row[col] if pd.notna(val): ws.cell(row=row_num, column=found_cols[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 found_cols: col = source_map.get(src_name) if col and col in src_row: val = src_row[col] if pd.notna(val): ws.cell(row=row_num, column=found_cols[target], value=val) fill_count += 1 self.log(f" 📊 本行填充 {fill_count} 个字段") row_num += 1 filled_count += 1 # ========== 8. 直接保存到模板文件 ========== wb.save(template_path) self.log(f"\n✅ 成功追加到模板文件: {template_path}") self.log(f"📊 共追加 {filled_count} 行数据") self.status_text.set(f"✅ 已追加 {filled_count} 行到模板") messagebox.showinfo("成功", f"已追加 {filled_count} 行数据到模板文件:\n{template_path}") except Exception as e: self.log(f"\n❌ 错误: {str(e)}") import traceback self.log(traceback.format_exc()) messagebox.showerror("错误", f"生成失败:\n{str(e)}") if __name__ == "__main__": root = tk.Tk() app = ExcelMapperApp(root) root.mainloop()