要使用 Python 实现 **BOM（Bill of Materials，物料清单）自动比对编码**，核心目标通常是： ✅ 读取两个或多个 BOM 文件（如 Excel/CSV）， ✅ 按关键字段（如“物料编码”“层级”“父件编码”“数量”等）进行结构化比对， ✅ 自动识别差异项（新增、缺失、数量/规格/版本变更等）， ✅ 生成可读性强的比对报告（如高亮差异的 Excel 表、HTML 报告或结构化 JSON）。 下面提供一个**生产级可用、模块化、可扩展**的完整解决方案（基于 `pandas` + `openpyxl`，支持 Excel BOM，兼顾可维护性与鲁棒性）： --- ### ✅ 回答问题：如何用 Python 实现 BOM 自动比对编码？ #### ✅ 核心步骤： 1. **标准化读取 BOM**：统一列名、数据类型、空值处理（尤其编码需转为字符串并去首尾空格）； 2. **定义主键（Primary Key）**：通常为 `(父件编码, 物料编码, 层级)` 或仅 `物料编码`（视业务而定）； 3. **合并比对（pandas.merge + indicator）**：使用 `indicator=True` 标识 left_only / right_only / both； 4. **逐字段差异检测**：对数值型（数量）、文本型（规格、版本、单位）、嵌套结构（如“是否替代料”）做精细化比对； 5. **生成差异报告**：含差异类型、原始值、新值、行号、建议操作（如“需更新ECN”）； 6. **导出结果**：Excel 多 Sheet（Summary / Added / Removed / Modified），带条件格式高亮。 --- ### ✅ 完整可运行代码（Python 3.8+，需安装 `pandas openpyxl`） ```python import pandas as pd import numpy as np from typing import List, Dict, Optional, Tuple, Any import re # ================== 配置区（按实际BOM结构调整） ================== BOM_COLUMNS = { "code": "物料编码", # 主键字段（必填） "parent": "父件编码", # 可选：用于层级比对 "level": "层级", # 可选 "qty": "用量", # 数值型比对字段 "spec": "规格型号", # 文本型比对字段 "version": "版本号", # 文本型比对字段 "unit": "单位", # 文本型比对字段 "remark": "备注" # 可选比对字段 } # 默认主键组合（可根据需要调整） PRIMARY_KEY = ["code"] # 或 ["parent", "code"] 或 ["parent", "code", "level"] NUMERIC_FIELDS = ["qty"] TEXT_FIELDS = ["spec", "version", "unit", "remark"] # ================== 工具函数 ================== def clean_code(s: str) -> str: """标准化编码：去空格、转大写、处理常见乱码""" if pd.isna(s): return "" s = str(s).strip() s = re.sub(r"[^\w\u4e00-\u9fa5\-_]", "", s) # 保留字母、数字、中文、-_（可按需调整） return s.upper() def load_bom(filepath: str, sheet_name: str = 0) -> pd.DataFrame: """安全读取BOM文件（支持xlsx/csv），自动清洗编码列""" if filepath.endswith(".csv"): df = pd.read_csv(filepath, dtype=str) else: df = pd.read_excel(filepath, sheet_name=sheet_name, dtype=str) # 重命名列 → 统一内部字段名 reverse_map = {v: k for k, v in BOM_COLUMNS.items()} df = df.rename(columns={col: reverse_map[col] for col in df.columns if col in reverse_map}) # 清洗主键字段 for pk in PRIMARY_KEY: if pk in df.columns: df[pk] = df[pk].apply(clean_code) # 清洗数值字段（转float，异常置NaN） for col in NUMERIC_FIELDS: if col in df.columns: df[col] = pd.to_numeric(df[col], errors="coerce") return df def compare_boms( df_old: pd.DataFrame, df_new: pd.DataFrame, primary_key: List[str] = PRIMARY_KEY, numeric_fields: List[str] = NUMERIC_FIELDS, text_fields: List[str] = TEXT_FIELDS, ) -> Dict[str, pd.DataFrame]: """ 执行BOM比对，返回差异字典 返回: { "summary": 汇总统计, "added": 新增行, "removed": 缺失行, "modified": 字段级变更明细 } """ # 确保主键存在 missing_pk = [pk for pk in primary_key if pk not in df_old.columns or pk not in df_new.columns] if missing_pk: raise ValueError(f"主键字段缺失: {missing_pk}") # 合并比对（outer join + indicator） merged = df_old.merge( df_new, on=primary_key, how="outer", suffixes=("_old", "_new"), indicator=True ) # 分离三类差异 added = merged[merged["_merge"] == "right_only"].drop(columns=["_merge"]) removed = merged[merged["_merge"] == "left_only"].drop(columns=["_merge"]) both = merged[merged["_merge"] == "both"].drop(columns=["_merge"]) # 字段级差异检测（仅 both 部分） mod_rows = [] for _, row in both.iterrows(): diff_fields = [] for field in numeric_fields + text_fields: old_val = row.get(f"{field}_old") new_val = row.get(f"{field}_new") # 数值型：考虑浮点精度（如 qty 允许 ±0.001 误差） if field in numeric_fields: if pd.isna(old_val) and pd.isna(new_val): continue if pd.isna(old_val) or pd.isna(new_val) or abs((old_val or 0) - (new_val or 0)) > 1e-3: diff_fields.append((field, old_val, new_val)) # 文本型：忽略空格和大小写差异（可配置） else: old_str = str(old_val).strip().upper() if not pd.isna(old_val) else "" new_str = str(new_val).strip().upper() if not pd.isna(new_val) else "" if old_str != new_str: diff_fields.append((field, old_val, new_val)) if diff_fields: base_row = {pk: row[pk] for pk in primary_key} for field, old_v, new_v in diff_fields: mod_rows.append({ **base_row, "field": field, "old_value": old_v, "new_value": new_v, "change_type": "numeric_change" if field in numeric_fields else "text_change" }) df_modified = pd.DataFrame(mod_rows) df_summary = pd.DataFrame({ "type": ["added", "removed", "modified"], "count": [len(added), len(removed), len(df_modified)] }) return { "summary": df_summary, "added": added, "removed": removed, "modified": df_modified } def export_comparison_report( result: Dict[str, pd.DataFrame], output_path: str = "bom_comparison_report.xlsx" ): """导出比对报告到Excel（含样式）""" with pd.ExcelWriter(output_path, engine="openpyxl") as writer: # 汇总页 result["summary"].to_excel(writer, sheet_name="Summary", index=False) # 新增/缺失页（保留原始列顺序） if not result["added"].empty: result["added"].to_excel(writer, sheet_name="Added", index=False) if not result["removed"].empty: result["removed"].to_excel(writer, sheet_name="Removed", index=False) # 修改明细页 if not result["modified"].empty: result["modified"].to_excel(writer, sheet_name="Modified_Details", index=False) # ✨ 添加自动列宽 & 高亮（openpyxl 后处理） workbook = writer.book for sheet_name in ["Summary", "Added", "Removed", "Modified_Details"]: if sheet_name in workbook.sheetnames: ws = workbook[sheet_name] for column in ws.columns: max_length = 0 column_letter = column[0].column_letter for cell in column: try: if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) except: pass adjusted_width = min(max_length + 2, 50) ws.column_dimensions[column_letter].width = adjusted_width # 高亮 Modified_Details 中的变更值（可选增强） if "Modified_Details" in workbook.sheetnames: ws = workbook["Modified_Details"] from openpyxl.styles import PatternFill yellow_fill = PatternFill(start_color="FFFFE6", end_color="FFFFE6", fill_type="solid") for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=4, max_col=5): # old_value, new_value 列 for cell in row: if cell.value is not None and str(cell.value).strip() != "": cell.fill = yellow_fill print(f"✅ BOM比对报告已生成：{output_path}") # ================== 使用示例（主流程） ================== if __name__ == "__main__": # 👇 替换为你的真实路径 OLD_BOM_PATH = "bom_v1.xlsx" NEW_BOM_PATH = "bom_v2.xlsx" OUTPUT_REPORT = "bom_diff_20241201.xlsx" try: print("🔍 正在加载旧版BOM...") df_old = load_bom(OLD_BOM_PATH) print(f"✅ 旧版共 {len(df_old)} 行") print("🔍 正在加载新版BOM...") df_new = load_bom(NEW_BOM_PATH) print(f"✅ 新版共 {len(df_new)} 行") print("🔄 开始比对...") result = compare_boms(df_old, df_new) print("📊 生成报告...") export_comparison_report(result, OUTPUT_REPORT) print("\n📈 比对摘要：") print(result["summary"].to_string(index=False)) except Exception as e: print(f"❌ 执行失败：{e}") import traceback traceback.print_exc() ``` --- ### ✅ 解释（为什么这样设计？） | 设计点 | 原因说明 | |--------|----------| | **`clean_code()`** | BOM编码常含空格、全半角符号、大小写混用；统一清洗避免“ABC” vs “abc”误判为差异。 | | **主键灵活配置** | 不同企业BOM结构不同（单编码 vs 父子编码组合），通过 `PRIMARY_KEY` 列表解耦逻辑。 | | **数值容差比对** | `qty` 字段可能因四舍五入产生微小差异（如 `1.000` vs `1.001`），用 `1e-3` 防止误报。 | | **文本忽略大小写+空格** | 规格型号如 `"φ10x20"` 和 `"Φ10X20"` 应视为相同，提升比对准确率。 | | **`indicator=True` merge** | 是 pandas 最高效、最语义清晰的集合比对方式，天然支持 left/right/both 分类。 | | **字段级修改明细** | 不只告诉你“某行变了”，而是精确到“`qty` 从 2→3，`version` 从 A→B”，支撑ECN（工程变更通知）闭环。 | | **openpyxl 样式增强** | Excel 报告中高亮变更值、自适应列宽，大幅提升工程师可读性与复核效率。 | > 💡 进阶提示： > - 若需支持 **多级BOM树形结构比对**（如检查子件展开一致性），可引入 `networkx` 构建父子图，用图同构/子图匹配算法； > - 若需 **对接PLM系统（如Windchill/Teamcenter）**，可封装 REST API 调用替代本地文件读取； > - 若需 **自动化邮件推送+审批流**，可集成 `smtplib` + `PyQt5` 或接入钉钉/企微 Webhook。 ---