Public feed
#97rXDSuZw5
import osimport shutilimport reimport datetimeimport csvimport threadingimport timeimport tkinter as tkfrom tkinter import filedialog, messagebox, ttkfrom tkinter.scrolledtext import ScrolledTextfrom openpyxl import Workbookfrom openpyxl.styles import PatternFillimport openpyxlimport tempfiletry: import win32com.client import pythoncom HAS_WIN32COM = Trueexcept ImportError: HAS_WIN32COM = False# ==========================================# 公用工具函数:读取、切块、格式化# ==========================================def _read_file_with_com(path, sheet_name=None): pythoncom.CoInitialize() xl = win32com.client.DispatchEx("Excel.Application") xl.Visible = False xl.DisplayAlerts = False wb = None try: abs_path = os.path.abspath(path) wb = xl.Workbooks.Open(abs_path, ReadOnly=True, UpdateLinks=0) if sheet_name: ws = wb.Sheets(sheet_name) else: ws = wb.Sheets(1) values = ws.UsedRange.Value data = [] if values: if isinstance(values, tuple): for row in values: if isinstance(row, tuple): data.append(list(row)) else: data.append([values]) else: data.append([values]) return data finally: if wb: try: wb.Close(False) except: pass try: xl.Quit() except: pass pythoncom.CoUninitialize()def _parse_blocks(data): """按空列切块,所有页面共享的通用解析函数""" blocks = [] if not data: return blocks max_cols = max(len(row) for row in data) if data else 0 empty_cols = [] for c in range(max_cols): is_empty = True for row in data: if c < len(row) and row[c] is not None and str(row[c]).strip() != "": is_empty = False; break if is_empty: empty_cols.append(c) start_col = 0 for end_col in empty_cols + [max_cols]: if end_col > start_col: block_data = [] for row in data: row_slice = list(row[start_col:end_col]) if len(row_slice) < (end_col - start_col): row_slice.extend([""] * ((end_col - start_col) - len(row_slice))) if any(str(x).strip() for x in row_slice if x is not None): block_data.append(row_slice) if block_data: first_row = block_data[0] group_name = str(first_row[0]).strip() if first_row[0] is not None else "未知组" headers_map = {} for i in range(1, len(first_row)): h_val = first_row[i] h_name = str(h_val).strip() if h_val is not None else "" if h_name and h_name not in headers_map: headers_map[h_name] = i blocks.append({'name': group_name, 'headers': headers_map, 'data': block_data[1:]}) start_col = end_col + 1 return blocksdef _to_number(val): if val is None: return "" v = str(val).strip() if not v: return "" if re.match(r'^[-+]?\d*\.?\d+(?:[eE][-+]?\d+)?$', v): try: f = float(v) return int(f) if f.is_integer() else f except Exception: pass return valdef _to_abs_number(val): if val is None: return "" v = str(val).strip() if not v: return "" try: f = abs(float(v)) return int(f) if f.is_integer() else f except Exception: return valdef _extract_float(val): if val is None: return None if isinstance(val, (int, float)): return float(val) s = str(val).strip() if not s: return None clean_s = re.sub(r'[^0-9eE\+\-\.]', '', s) try: return float(clean_s) except: return Nonedef _sheet_name(base, *suffixes, limit=31): """生成不超过 31 字符的 sheet 名:后缀(如 _ABS)完整保留,超长时只压缩主名。""" suffix = "".join(suffixes) base = re.sub(r'[\[\]:*?/\\]', '_', str(base)).strip() or "Sheet" keep = limit - len(suffix) if keep >= 1: return base[:keep] + suffix return (base + suffix)[:limit]def _unique_sheet_name(wb, base, *suffixes): """在 workbook 内生成不重复的 sheet 名,重名时在末尾追加 _2/_3。""" name = _sheet_name(base, *suffixes) if name not in wb.sheetnames: return name n = 2 while True: cand = _sheet_name(base, "".join(suffixes) + f"_{n}") if cand not in wb.sheetnames: return cand n += 1def _values_to_rows(values): """把 UsedRange.Value 的元组结构转成二维 list(与原 _read_file_with_com 一致)。""" data = [] if not values: return data if isinstance(values, tuple): for row in values: if isinstance(row, tuple): data.append(list(row)) else: data.append([values]) else: data.append([values]) return datadef _read_sheets_with_com(path, sheet_names): """用一次 Excel 会话读取多个 sheet(保留原 COM 读取语义),失败返回空字典。""" if not HAS_WIN32COM: return {} pythoncom.CoInitialize() xl = win32com.client.DispatchEx("Excel.Application") xl.Visible = False xl.DisplayAlerts = False wb = None out = {} try: wb = xl.Workbooks.Open(os.path.abspath(path), ReadOnly=True, UpdateLinks=0) for name in sheet_names: try: out[name] = _values_to_rows(wb.Sheets(name).UsedRange.Value) except Exception: pass finally: if wb: try: wb.Close(False) except Exception: pass try: xl.Quit() except Exception: pass pythoncom.CoUninitialize() return outdef _read_workbook_sheets(path, sheet_names): """ 读取 xlsx 中指定的多个 sheet,返回 {sheet_name: [row, ...]}。 读取顺序与原版一致:优先 Excel COM,读不到的 sheet 再用 openpyxl 兜底。 """ result = {} if HAS_WIN32COM: try: com_data = _read_sheets_with_com(path, sheet_names) result = {k: v for k, v in com_data.items() if v} except Exception: result = {} missing = [n for n in sheet_names if n not in result] if missing: temp_path = None try: fd, temp_path = tempfile.mkstemp(suffix=".xlsx") os.close(fd) shutil.copy2(path, temp_path) wb = openpyxl.load_workbook(temp_path, data_only=True) try: for name in missing: if name not in wb.sheetnames: continue rows = [list(r) for r in wb[name].iter_rows(values_only=True)] if any(any(str(c).strip() for c in r if c is not None) for r in rows): result[name] = rows finally: wb.close() except Exception: pass finally: if temp_path and os.path.exists(temp_path): try: os.remove(temp_path) except Exception: pass return resultclass SheetSelector: """多 Sheet 勾选控件:解析时读全部 sheet,勾选只决定输出哪些,改完直接执行。""" def __init__(self, parent, title="处理范围:勾选要输出结果的 Sheet(改完直接执行,无需重新加载)"): self.on_change = None self.frame = tk.LabelFrame(parent, text=title, padx=6, pady=4) bar = tk.Frame(self.frame) bar.pack(fill=tk.X) tk.Button(bar, text="全选", width=5, command=self.select_all).pack(side=tk.LEFT) tk.Button(bar, text="全不选", width=6, command=self.select_none).pack(side=tk.LEFT, padx=4) self.lbl_info = tk.Label(bar, text="尚未加载文件", fg="gray", font=("Arial", 9)) self.lbl_info.pack(side=tk.LEFT, padx=8) body = tk.Frame(self.frame) body.pack(fill=tk.X, pady=(3, 0)) self.canvas = tk.Canvas(body, height=92, highlightthickness=0) self.scroll = tk.Scrollbar(body, orient="vertical", command=self.canvas.yview) self.inner = tk.Frame(self.canvas) self._win = self.canvas.create_window((0, 0), window=self.inner, anchor="nw") self.inner.bind("<Configure>", lambda e: self.canvas.configure(scrollregion=self.canvas.bbox("all"))) self.canvas.bind("<Configure>", lambda e: self.canvas.itemconfigure(self._win, width=e.width)) self.canvas.configure(yscrollcommand=self.scroll.set) self.canvas.pack(side=tk.LEFT, fill=tk.X, expand=True) self.scroll.pack(side=tk.RIGHT, fill=tk.Y) self.canvas.bind("<Enter>", lambda e: self.canvas.bind_all("<MouseWheel>", self._on_wheel)) self.canvas.bind("<Leave>", lambda e: self.canvas.unbind_all("<MouseWheel>")) self.vars = {} self.chks = {} def _on_wheel(self, event): try: delta = int(-event.delta / 120) self.canvas.yview_scroll(delta if delta else -1, "units") except Exception: pass def set_sheets(self, names): for w in self.inner.winfo_children(): w.destroy() self.vars = {} self.chks = {} for i, name in enumerate(names): var = tk.BooleanVar(value=True) chk = tk.Checkbutton(self.inner, text=name, variable=var, anchor="w", command=self._sync_label) chk.grid(row=i, column=0, sticky="w", padx=2) self.vars[name] = var self.chks[name] = chk self._sync_label() def mark_no_data(self, names): """把读不出数据组的 sheet 标灰提示,避免误以为漏处理。""" for name in names: chk = self.chks.get(name) if chk is not None: try: chk.config(text=f"{name}(无数据组)", fg="gray") except Exception: pass def select_all(self): for v in self.vars.values(): v.set(True) self._sync_label() def select_none(self): for v in self.vars.values(): v.set(False) self._sync_label() def _sync_label(self): self.lbl_info.config(text=f"勾选 {len(self.selected())}/{len(self.vars)} 个 sheet(仅决定输出范围)") if self.on_change: try: self.on_change(self) except Exception: pass def all_names(self): return list(self.vars.keys()) def selected(self): return [n for n, v in self.vars.items() if v.get()]# ==========================================# 模块一:数据整理与生成 Summary# ==========================================class DataProcessorApp: def __init__(self, parent, main_root): self.root = parent self.main_root = main_root self.folder_path = tk.StringVar() self.custom_keys = tk.StringVar() self.use_com = tk.BooleanVar(value=True if HAS_WIN32COM else False) self.gen_abs = tk.BooleanVar(value=True) # 【新】是否生成ABS self.is_running = False self.global_xl = None self.stats = {"success": 0, "failed": 0, "unmatched": 0, "total_files": 0, "failed_list": []} self.idvd_vars = {} self.setup_ui() def setup_ui(self): frame_top = tk.Frame(self.root, pady=8, padx=10) frame_top.pack(fill=tk.X) tk.Label(frame_top, text="选择文件夹:").pack(side=tk.LEFT) tk.Entry(frame_top, textvariable=self.folder_path, width=60).pack(side=tk.LEFT, padx=5) tk.Button(frame_top, text="浏览", command=self.select_folder).pack(side=tk.LEFT) frame_mid = tk.Frame(self.root, pady=5, padx=10) frame_mid.pack(fill=tk.X) tk.Label(frame_mid, text='分类关键字 (英文双引号包裹,逗号隔开,例:"AA","BB(CC)"):').pack(side=tk.LEFT) tk.Entry(frame_mid, textvariable=self.custom_keys, width=40).pack(side=tk.LEFT, padx=5) frame_vg = tk.Frame(self.root, pady=5, padx=10) frame_vg.pack(fill=tk.X) tk.Label(frame_vg, text="高级 ID-VD 提取目标 (可多选):", font=("Arial", 9, "bold")).pack(side=tk.LEFT, anchor="n") self.idvd_checkbox_frame = tk.Frame(frame_vg) self.idvd_checkbox_frame.pack(side=tk.LEFT, padx=10, fill=tk.X, expand=True) frame_com = tk.Frame(self.root, pady=5, padx=10) frame_com.pack(fill=tk.X) if HAS_WIN32COM: tk.Checkbutton(frame_com, text="启用 Excel 软件兜底解析", variable=self.use_com, fg="red").pack(side=tk.LEFT) tk.Checkbutton(frame_com, text="额外生成 ABS 绝对值数据 Sheet", variable=self.gen_abs, fg="#FF9800").pack(side=tk.LEFT, padx=20) frame_btn = tk.Frame(self.root, pady=10) frame_btn.pack() self.btn_start = tk.Button(frame_btn, text="开始处理", command=self.start_processing, bg="#4CAF50", fg="white", font=("Arial", 11, "bold"), width=20) self.btn_start.pack() frame_progress = tk.Frame(self.root, pady=8, padx=20) frame_progress.pack(fill=tk.X) self.progress = ttk.Progressbar(frame_progress, orient="horizontal", length=100, mode="determinate") self.progress.pack(fill=tk.X) self.lbl_status = tk.Label(frame_progress, text="准备就绪", fg="blue") self.lbl_status.pack() self.log_area = ScrolledText(self.root, height=15, state='disabled', bg="#f4f4f4", font=("Consolas", 10)) self.log_area.pack(fill=tk.BOTH, expand=True, padx=10, pady=5) self.custom_keys.trace_add('write', lambda *a: self.refresh_idvd_options()) self.refresh_idvd_options() def refresh_idvd_options(self): try: cats = self.parse_categories(self.custom_keys.get()) except Exception: cats = [] current_selections = {} if hasattr(self, 'idvd_vars'): for cat_name, var in self.idvd_vars.items(): if var.get(): current_selections[cat_name] = True for widget in self.idvd_checkbox_frame.winfo_children(): widget.destroy() self.idvd_vars = {} for i, c in enumerate(cats): cat_name = c[0] var = tk.BooleanVar(value=current_selections.get(cat_name, False)) chk = tk.Checkbutton(self.idvd_checkbox_frame, text=cat_name, variable=var) row = i // 6 col = i % 6 chk.grid(row=row, column=col, sticky="w", padx=5, pady=2) self.idvd_vars[cat_name] = var def log(self, message): try: self.log_area.config(state='normal') self.log_area.insert(tk.END, message + "\n") self.log_area.see(tk.END) self.log_area.config(state='disabled') self.root.update_idletasks() except Exception: pass def select_folder(self): path = filedialog.askdirectory() if path: self.folder_path.set(path) @staticmethod def parse_categories(raw_text): if not raw_text or not raw_text.strip(): return [] items = re.findall(r'"([^"]*)"', raw_text) if not items: items = re.split(r'[,,]', raw_text) items = [x.strip().strip('"').strip("'") for x in items if x.strip()] result = [] for item in items: item = item.strip() if not item: continue m = re.match(r'^([^(]+)\(([^)]+)\)$', item) if m: primary = m.group(1).strip() others = [x.strip() for x in re.split(r'[,,\s]+', m.group(2)) if x.strip()] keys = [primary] + others display = "".join(keys) result.append((display, keys)) else: result.append((item, [item])) return result @staticmethod def parse_single_group(filename): name = os.path.splitext(os.path.basename(filename))[0] name_nc = re.sub(r'\([^)]*\)', '', name) m = re.search(r'(\d+(?:[Dd.]\d+)?)V', name_nc) if not m: return None vol_str = m.group(1).replace('D', '.').replace('d', '.') try: vol = float(vol_str) except Exception: return None group_name = name_nc[m.end():].strip('_ ').strip() if not group_name: return None return vol, group_name @staticmethod def safe_sheet_name(name): return re.sub(r'[\[\]:*?/\\]', '_', name)[:31] def init_global_excel(self): if not self.use_com.get() or not HAS_WIN32COM: return try: pythoncom.CoInitialize() self.global_xl = win32com.client.DispatchEx("Excel.Application") self.global_xl.Visible = False self.global_xl.DisplayAlerts = False self.global_xl.ScreenUpdating = False self.global_xl.EnableEvents = False except Exception as e: self.log(f"⚠️ 启动后台 Excel 失败: {e}") self.global_xl = None def close_global_excel(self): if self.global_xl: try: self.global_xl.Quit() except Exception: pass self.global_xl = None if HAS_WIN32COM: try: pythoncom.CoUninitialize() except Exception: pass import gc gc.collect() def read_with_excel_com(self, file_path): if not self.global_xl: raise ValueError("Excel COM 实例未初始化") return _read_file_with_com(file_path) def read_file_safe(self, file_path): try: with open(file_path, 'rb') as f: raw = f.read() text_16 = raw.decode('utf-16le', errors='ignore').replace('\x00', '') if 'dataname' in text_16.lower(): data = self._split_text_to_data(text_16) if self.find_dataname(data): return data text_8 = raw.decode('utf-8', errors='ignore').replace('\x00', '') if 'dataname' in text_8.lower(): data = self._split_text_to_data(text_8) if self.find_dataname(data): return data except Exception: pass if self.use_com.get() and HAS_WIN32COM and self.global_xl: return self.read_with_excel_com(file_path) raise ValueError("数据格式不匹配,未找到DataName") def _split_text_to_data(self, text): lines = text.splitlines() data = [] for line in lines: if not line.strip(): continue if '\t' in line: row = line.split('\t') elif ',' in line: row = line.split(',') else: row = re.split(r'\s{2,}', line) data.append([str(c).strip().strip('"').strip("'") for c in row]) return data def find_dataname(self, data): for r_idx, row in enumerate(data): for c_idx, cell in enumerate(row): if 'dataname' in str(cell).replace(' ', '').lower(): return r_idx, c_idx return None def extract_datablock(self, data, r, c): last_nonempty = r max_col = c for i in range(r, len(data)): row = data[i] row_str = [str(x).strip() if x is not None else "" for x in row] if len(row_str) > c + 1 and any(row_str[c+1:]): last_nonempty = i for j in range(len(row_str) - 1, c, -1): if row_str[j]: if j > max_col: max_col = j break target_len = max_col - c if target_len <= 0: return [] result = [] for i in range(r, last_nonempty + 1): row = data[i] row_str = [str(x).strip() if x is not None else "" for x in row] sliced = row_str[c+1:max_col+1] if len(sliced) < target_len: sliced = sliced + [""] * (target_len - len(sliced)) result.append(sliced) return result def move_file_safe(self, src, dest_dir): if not os.path.exists(dest_dir): os.makedirs(dest_dir) filename = os.path.basename(src) dest = os.path.join(dest_dir, filename) if os.path.abspath(src) == os.path.abspath(dest): return src base, ext = os.path.splitext(filename) counter = 1 while os.path.exists(dest): dest = os.path.join(dest_dir, f"{base}_{counter}{ext}") counter += 1 for _ in range(3): try: shutil.move(src, dest) return dest except PermissionError: time.sleep(0.5) raise PermissionError("文件被占用,无法移动") def save_excel(self, matrix_data, sheet_name, output_path): wb = Workbook() ws = wb.active ws.title = self.safe_sheet_name(sheet_name) for row in matrix_data: clean_row = [_to_number(x) for x in row] ws.append(clean_row) wb.save(output_path) @staticmethod def unique_path(folder, name): path = os.path.join(folder, name) base, ext = os.path.splitext(name) counter = 1 while os.path.exists(path): path = os.path.join(folder, f"{base}_{counter}{ext}") counter += 1 return path @staticmethod def _append_abs_sheet(wb, base_name, matrix): """把整表数值取绝对值,写入以「原 sheet 名 + _ABS」命名的新 sheet。""" ws = wb.create_sheet(_unique_sheet_name(wb, base_name, "_ABS")) for row in matrix: ws.append([_to_abs_number(x) for x in row]) return ws @staticmethod def format_vg(v): try: f = float(v) if f.is_integer(): return f"VG={int(f)}V" return f"VG={f}V" except Exception: return f"VG={v}V" def start_processing(self): if self.is_running: return folder = self.folder_path.get() if not folder or not os.path.exists(folder): messagebox.showerror("错误", "请选择有效的文件夹") return self.is_running = True self.btn_start.config(state=tk.DISABLED) self.lbl_status.config(text="准备就绪", fg="blue") self.log_area.config(state='normal') self.log_area.delete(1.0, tk.END) self.log_area.config(state='disabled') self.stats = {"success": 0, "failed": 0, "unmatched": 0, "total_files": 0, "failed_list": []} threading.Thread(target=self._process_files_wrapper, args=(folder,), daemon=True).start() def _process_files_wrapper(self, root_folder): try: self._process_files_impl(root_folder) except Exception as e: import traceback self.log(f"❌ 严重错误: {e}") self.log(traceback.format_exc()) finally: self.is_running = False try: self.main_root.after(0, lambda: self._finish_ui()) except Exception: pass def _finish_ui(self): try: self.btn_start.config(state=tk.NORMAL) self.progress["value"] = self.progress["maximum"] except Exception: pass def _process_files_impl(self, root_folder): date_str = datetime.datetime.now().strftime("%Y%m%d%H%M%S") cats = self.parse_categories(self.custom_keys.get()) if not cats: self.log('⚠️ 未填写任何分类关键字(请用英文双引号包裹,如 "AA","BB")') self.lbl_status.config(text="未填写关键字") return idvd_targets = [cat_name for cat_name, var in self.idvd_vars.items() if var.get()] results_dir = os.path.join(root_folder, "Results") os.makedirs(results_dir, exist_ok=True) exclude_paths = {os.path.abspath(results_dir)} for d, _ in cats: exclude_paths.add(os.path.abspath(os.path.join(root_folder, d))) all_files = [] for r_dir, dirs, files in os.walk(root_folder): dirs[:] = [d for d in dirs if os.path.abspath(os.path.join(r_dir, d)) not in exclude_paths] for f in files: if f.lower().endswith(('.xlsx', '.xls', '.csv')): all_files.append(os.path.join(r_dir, f)) self.stats["total_files"] = len(all_files) self.progress["maximum"] = len(all_files) if all_files else 1 self.progress["value"] = 0 self.log(f"共扫描到 {len(all_files)} 个待处理文件。\n") match_list = [] for display, keys in cats: for k in keys: match_list.append((display, k)) match_list.sort(key=lambda x: -len(x[1])) cat_files = {display: [] for display, _ in cats} self.log("--- 1. 开始分类移动文件 ---") self.lbl_status.config(text="分类移动中...") for idx, file_path in enumerate(all_files): filename = os.path.basename(file_path) matched = None for display, key in match_list: if key in filename: matched = display break if matched: dest_dir = os.path.join(root_folder, matched) try: new_path = self.move_file_safe(file_path, dest_dir) cat_files[matched].append(new_path) except Exception as e: self.stats["failed"] += 1 self.stats["failed_list"].append([filename, f"移动失败: {e}"]) else: self.stats["unmatched"] += 1 self.progress["value"] = idx + 1 try: self.root.update_idletasks() except Exception: pass total_to_read = sum(len(v) for v in cat_files.values()) self.progress["value"] = 0 self.progress["maximum"] = total_to_read if total_to_read else 1 self.init_global_excel() file_cache = {} read_count = 0 self.lbl_status.config(text="读取文件中...") for cat, files in cat_files.items(): for fp in files: read_count += 1 self.progress["value"] = read_count self.lbl_status.config(text=f"读取 ({read_count}/{total_to_read}): {os.path.basename(fp)}") try: self.root.update_idletasks() except Exception: pass try: data = self.read_file_safe(fp) pos = self.find_dataname(data) if not pos: raise ValueError("未找到 DataName") file_cache[fp] = (data, pos) except Exception as e: self.stats["failed"] += 1 self.stats["failed_list"].append([os.path.basename(fp), str(e)]) self.log("\n--- 2. 生成普通分类汇总 ---") self.lbl_status.config(text="生成普通汇总...") for display, files in cat_files.items(): if not files: continue valid_files = [fp for fp in files if fp in file_cache] if not valid_files: continue self.log(f"正在处理分类 '{display}' ({len(valid_files)} 文件)...") try: self._build_normal_summary(display, valid_files, file_cache, results_dir, date_str) except Exception as e: self.log(f"[失败] {display} 普通汇总生成失败: {e}") if idvd_targets: for target in idvd_targets: if cat_files.get(target): self.log(f"\n--- 3. 高级 ID-VD 两列提取: '{target}' ---") self.lbl_status.config(text=f"IDVD 处理中 ({target})...") try: self._build_idvd_summary(target, cat_files[target], file_cache, results_dir, date_str) except Exception as e: self.log(f"[失败] {target} IDVD 生成失败: {e}") self.close_global_excel() if self.stats["failed_list"]: fail_csv = os.path.join(results_dir, "处理失败清单.csv") try: with open(fail_csv, 'w', newline='', encoding='utf-8-sig') as f: writer = csv.writer(f) writer.writerow(["文件名", "失败原因"]) writer.writerows(self.stats["failed_list"]) self.log(f"\n[提示] 失败清单: {os.path.basename(fail_csv)}") except Exception as e: self.log(f"\n[错误] 无法写入失败清单: {e}") self.lbl_status.config(text="处理完成", fg="green") summary_msg = (f"处理完成!\n\n成功: {self.stats['success']}\n失败: {self.stats['failed']}\n" f"未分类: {self.stats['unmatched']}\n\n输出目录: {results_dir}") try: self.main_root.after(0, lambda m=summary_msg: messagebox.showinfo("运行结果", m)) except Exception: pass def _build_normal_summary(self, display, files, file_cache, results_dir, date_str): groups = [] for fp in files: fname = os.path.splitext(os.path.basename(fp))[0] data, (r, c) = file_cache[fp] rows = self.extract_datablock(data, r, c) if not rows: self.stats["failed"] += 1 self.stats["failed_list"].append([os.path.basename(fp), "DataName 右侧无有效数据"]) continue groups.append((fname, rows)) if not groups: return max_rows = max(len(rows) for _, rows in groups) max_cols = max((len(r) for _, rows in groups for r in rows), default=0) matrix = [] for i in range(max_rows): row_line = [] for g_idx, (fname, rows) in enumerate(groups): if g_idx > 0: row_line.append("") row_line.append(fname if i == 0 else "") if i < len(rows): r_data = list(rows[i]) if len(r_data) < max_cols: r_data.extend([""] * (max_cols - len(r_data))) row_line.extend(r_data) else: row_line.extend([""] * max_cols) matrix.append(row_line) out_name = f"{display} Summary_{date_str}.xlsx" out_path = self.unique_path(results_dir, out_name) wb = Workbook() ws1 = wb.active ws1.title = _unique_sheet_name(wb, display) for row in matrix: ws1.append([_to_number(x) for x in row]) if self.gen_abs.get(): self._append_abs_sheet(wb, ws1.title, matrix) wb.save(out_path) self.log(f"[成功] 生成: {os.path.basename(out_path)}") self.stats["success"] += len(groups) def _build_idvd_summary(self, display, files, file_cache, results_dir, date_str): groups = [] single_groups = {} for fp in files: if fp not in file_cache: continue fname_full = os.path.basename(fp) fname = os.path.splitext(fname_full)[0] data, (r, c) = file_cache[fp] vd_col = vg_col = id_col = None for cidx in range(c + 1, len(data[r])): val = str(data[r][cidx]).strip().upper() if val == "VD" and vd_col is None: vd_col = cidx elif val == "VG" and vg_col is None: vg_col = cidx elif val == "ID" and id_col is None: id_col = cidx if vd_col is None or vg_col is None or id_col is None: self.stats["failed"] += 1 self.stats["failed_list"].append([fname_full, "未找到精确的 VD/VG/ID 三列"]) continue raw_rows = [] for i in range(r + 1, len(data)): row = data[i] vd_v = str(row[vd_col]).strip() if vd_col < len(row) else "" vg_v = str(row[vg_col]).strip() if vg_col < len(row) else "" id_v = str(row[id_col]).strip() if id_col < len(row) else "" if not vg_v: continue raw_rows.append((vd_v, vg_v, id_v)) if not raw_rows: self.stats["failed"] += 1 self.stats["failed_list"].append([fname_full, "VD/VG/ID 无有效数据"]) continue vg_map = {} for vd_v, vg_v, id_v in raw_rows: try: key = float(vg_v) except Exception: key = vg_v vg_map.setdefault(key, []).append((vd_v, vg_v, id_v)) def sort_key(k): if isinstance(k, (int, float)): return (0, abs(float(k))) return (1, str(k)) unique_vgs = sorted(vg_map.keys(), key=sort_key) if len(unique_vgs) >= 2: groups.append({'type': 'multi', 'filename': fname, 'vg_keys': unique_vgs, 'blocks': {k: vg_map[k] for k in unique_vgs}}) else: parsed = self.parse_single_group(fname_full) if not parsed: continue vol, group_name = parsed vg_key = unique_vgs[0] item = {'voltage': vol, 'filename': fname, 'vg_key': vg_key, 'rows': vg_map.get(vg_key, [])} if group_name in single_groups: groups[single_groups[group_name]]['items'].append(item) else: single_groups[group_name] = len(groups) groups.append({'type': 'single', 'group_name': group_name, 'items': [item]}) if not groups: return for g in groups: if g['type'] == 'single': g['items'].sort(key=lambda x: (abs(x['voltage']), x['filename'])) for g in groups: if g['type'] == 'multi': g['max_rows'] = max((len(v) for v in g['blocks'].values()), default=0) else: g['max_rows'] = max((len(it['rows']) for it in g['items']), default=0) max_data_rows = max((g['max_rows'] for g in groups), default=0) matrix = [] header = [] for g_idx, g in enumerate(groups): if g_idx > 0: header.append("") if g['type'] == 'multi': header.append(g['filename']) for vg_key in g['vg_keys']: header.extend(["VD", self.format_vg(vg_key)]) else: header.append(g['group_name']) vg_count = {} for item in g['items']: vg_h = self.format_vg(item['vg_key']) if vg_h in vg_count: vg_count[vg_h] += 1 vg_h = f"{vg_h}_{vg_count[vg_h]}" else: vg_count[vg_h] = 1 header.extend(["VD", vg_h]) matrix.append(header) for r_idx in range(max_data_rows): row = [] for g_idx, g in enumerate(groups): if g_idx > 0: row.append("") if g['type'] == 'multi': row.append("") for vg_key in g['vg_keys']: block = g['blocks'][vg_key] if r_idx < len(block): row.extend([block[r_idx][0], block[r_idx][2]]) else: row.extend(["", ""]) else: row.append("") for item in g['items']: block = item['rows'] if r_idx < len(block): row.extend([block[r_idx][0], block[r_idx][2]]) else: row.extend(["", ""]) matrix.append(row) out_name = f"{display}_IDVD Data Summary_{date_str}.xlsx" out_path = self.unique_path(results_dir, out_name) wb = Workbook() ws1 = wb.active base_sheet_name = f"{display}_IDVD Summary" ws1.title = _unique_sheet_name(wb, base_sheet_name) for row in matrix: ws1.append([_to_number(x) for x in row]) if self.gen_abs.get(): self._append_abs_sheet(wb, ws1.title, matrix) wb.save(out_path) self.log(f"[成功] 生成: {os.path.basename(out_path)}") self.stats["success"] += len(groups)# ==========================================# 模块二:条件数据提取# ==========================================class DataExtractorApp: def __init__(self, parent, main_root): self.root = parent self.main_root = main_root self.file_path = tk.StringVar() self.gen_abs_out = tk.BooleanVar(value=False) # 【新】是否生成绝对值 ABS Sheet self.headers = [] self.blocks = [] self.sheet_blocks = {} self.left_cond_frames = [] self.left_target_frames = [] self.right_target_frames = [] self.bv_target_frames = [] self.setup_ui() def setup_ui(self): frame_top = tk.Frame(self.root, pady=10, padx=10) frame_top.pack(fill=tk.X) tk.Label(frame_top, text="选择 Summary 文件:").pack(side=tk.LEFT) tk.Entry(frame_top, textvariable=self.file_path, width=50).pack(side=tk.LEFT, padx=5) tk.Button(frame_top, text="浏览", command=self.select_file).pack(side=tk.LEFT) tk.Button(frame_top, text="加载并解析", command=self.load_file, bg="#2196F3", fg="white").pack(side=tk.LEFT, padx=10) self.sheet_sel = SheetSelector(self.root) self.sheet_sel.frame.pack(fill=tk.X, padx=10, pady=(0, 4)) self.lbl_headers = tk.Label(self.root, text="可用表头: 请先加载文件", fg="blue", wraplength=1150, justify=tk.LEFT) self.lbl_headers.pack(fill=tk.X, padx=10, pady=5) paned_window = tk.PanedWindow(self.root, orient=tk.HORIZONTAL, sashrelief=tk.RAISED) paned_window.pack(fill=tk.BOTH, expand=True, padx=10, pady=5) # 左侧 frame_left = tk.Frame(paned_window) paned_window.add(frame_left, width=450) tk.Label(frame_left, text="【左侧:按条件匹配提取】", font=("Arial", 11, "bold"), fg="#2196F3").pack(anchor="w", pady=5) f_left_cond = tk.LabelFrame(frame_left, text="1. 检索条件设置", padx=5, pady=5) f_left_cond.pack(fill=tk.X, pady=5) self.frame_left_cond_inner = tk.Frame(f_left_cond) self.frame_left_cond_inner.pack(fill=tk.X) tk.Button(f_left_cond, text="+ 增加条件", command=self.add_left_condition).pack(anchor="w", pady=2) f_left_target = tk.LabelFrame(frame_left, text="2. 输出目标列", padx=5, pady=5) f_left_target.pack(fill=tk.X, pady=5) self.frame_left_target_inner = tk.Frame(f_left_target) self.frame_left_target_inner.pack(fill=tk.X) tk.Button(f_left_target, text="+ 增加输出列", command=self.add_left_target).pack(anchor="w", pady=2) # 中间 frame_mid = tk.Frame(paned_window) paned_window.add(frame_mid, width=400) tk.Label(frame_mid, text="【中间:找空值取上一行】", font=("Arial", 11, "bold"), fg="#E91E63").pack(anchor="w", pady=5) f_mid_cond = tk.LabelFrame(frame_mid, text="1. 触发条件 (找该列空值)", padx=5, pady=5) f_mid_cond.pack(fill=tk.X, pady=5) self.right_trigger_cb = ttk.Combobox(f_mid_cond, state="readonly", width=25) self.right_trigger_cb.pack(anchor="w", pady=5, padx=5) f_mid_target = tk.LabelFrame(frame_mid, text="2. 提取上一行目标列", padx=5, pady=5) f_mid_target.pack(fill=tk.X, pady=5) self.frame_right_target_inner = tk.Frame(f_mid_target) self.frame_right_target_inner.pack(fill=tk.X) tk.Button(f_mid_target, text="+ 增加输出列", command=self.add_right_target).pack(anchor="w", pady=2) # 右侧 BV frame_right = tk.Frame(paned_window) paned_window.add(frame_right, width=450) tk.Label(frame_right, text="【右侧:自动抓取 BV_on 击穿点】", font=("Arial", 11, "bold"), fg="#FF9800").pack(anchor="w", pady=5) f_bv_cond = tk.LabelFrame(frame_right, text="1. 突变监测设置", padx=5, pady=5) f_bv_cond.pack(fill=tk.X, pady=5) f_bv_row1 = tk.Frame(f_bv_cond) f_bv_row1.pack(fill=tk.X, pady=2) tk.Label(f_bv_row1, text="触发列(电流):").pack(side=tk.LEFT) self.bv_trigger_cb = ttk.Combobox(f_bv_row1, state="readonly", width=15) self.bv_trigger_cb.pack(side=tk.LEFT, padx=5) f_bv_row2 = tk.Frame(f_bv_cond) f_bv_row2.pack(fill=tk.X, pady=2) tk.Label(f_bv_row2, text="判定方式:").pack(side=tk.LEFT) self.bv_mode_cb = ttk.Combobox(f_bv_row2, state="readonly", width=25) self.bv_mode_cb['values'] = ["自动找最大斜率(推荐)", "超过指定电流值", "找断崖/变空"] self.bv_mode_cb.current(0) self.bv_mode_cb.pack(side=tk.LEFT, padx=5) self.bv_mode_cb.bind("<<ComboboxSelected>>", lambda e: self._update_bv_mode_ui()) f_bv_row3 = tk.Frame(f_bv_cond) f_bv_row3.pack(fill=tk.X, pady=2) self.lbl_bv_threshold = tk.Label(f_bv_row3, text="指定电流阈值:") self.lbl_bv_threshold.pack(side=tk.LEFT) self.entry_bv_threshold = tk.Entry(f_bv_row3, width=10) self.entry_bv_threshold.insert(0, "0.02") self.entry_bv_threshold.pack(side=tk.LEFT, padx=5) self.lbl_bv_threshold.pack_forget() self.entry_bv_threshold.pack_forget() tk.Label(f_bv_cond, text="(将自动提取突变前一行 VD 作为 BV_on)", fg="gray", font=("Arial", 9)).pack(anchor="w", pady=2) f_bv_target = tk.LabelFrame(frame_right, text="2. 额外提取列 (可选)", padx=5, pady=5) f_bv_target.pack(fill=tk.X, pady=5) self.frame_bv_target_inner = tk.Frame(f_bv_target) self.frame_bv_target_inner.pack(fill=tk.X) tk.Button(f_bv_target, text="+ 增加输出列", command=self.add_bv_target).pack(anchor="w", pady=2) frame_btn = tk.Frame(self.root, pady=10) frame_btn.pack() tk.Checkbutton(frame_btn, text="提取结果同时附带绝对值 (ABS) Sheet", variable=self.gen_abs_out, fg="#FF9800").pack(side=tk.LEFT, padx=10) tk.Button(frame_btn, text="执行提取并保存在同目录", command=self.run_extraction, bg="#4CAF50", fg="white", font=("Arial", 12, "bold"), width=30).pack(side=tk.LEFT) self.log_area = ScrolledText(self.root, height=8, state='disabled', font=("Consolas", 10)) self.log_area.pack(fill=tk.BOTH, expand=True, padx=10, pady=5) def _update_bv_mode_ui(self): if self.bv_mode_cb.get() == "超过指定电流值": self.lbl_bv_threshold.pack(side=tk.LEFT) self.entry_bv_threshold.pack(side=tk.LEFT, padx=5) else: self.lbl_bv_threshold.pack_forget() self.entry_bv_threshold.pack_forget() def log(self, msg): self.log_area.config(state='normal') self.log_area.insert(tk.END, msg + "\n") self.log_area.see(tk.END) self.log_area.config(state='disabled') self.root.update_idletasks() def select_file(self): path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")]) if path: self.file_path.set(path) try: wb = openpyxl.load_workbook(path, read_only=True) sheets = list(wb.sheetnames) wb.close() self.sheet_sel.set_sheets(sheets) self.log(f"已载入文件,共 {len(sheets)} 个 Sheet(默认全选)") except Exception as e: self.sheet_sel.set_sheets([]) self.log(f"读取工作表列表失败: {e}") def load_file(self): path = self.file_path.get() if not path or not os.path.exists(path): messagebox.showerror("错误", "请选择有效的 Excel 文件") return targets = self.sheet_sel.all_names() if not targets: messagebox.showwarning("提示", "文件内没有可解析的 Sheet") return self.sheet_blocks = {} self.blocks = [] self.log(f"正在解析文件: {os.path.basename(path)} -> 全部 {len(targets)} 个 Sheet...") sheet_data = _read_workbook_sheets(path, targets) if not sheet_data: self.log("读取失败:所有 Sheet 均无法读取") self.sheet_sel.mark_no_data(targets) return all_h = set() no_data = [] for name in targets: data = sheet_data.get(name) if not data: no_data.append(name) self.log(f"[跳过] Sheet '{name}' 无有效数据") continue try: blocks = _parse_blocks(data) except Exception as e: no_data.append(name) self.log(f"[失败] Sheet '{name}' 解析失败: {e}") continue if not blocks: no_data.append(name) self.log(f"[跳过] Sheet '{name}' 未解析出数据组") continue self.sheet_blocks[name] = blocks for blk in blocks: for h in blk['headers'].keys(): if h: all_h.add(h) self.log(f" '{name}' 解析成功:{len(blocks)} 个数据组") self.sheet_sel.mark_no_data(no_data) if not self.sheet_blocks: messagebox.showwarning("提示", "所有 Sheet 均未解析出数据组,请检查文件") return self.blocks = [b for blocks in self.sheet_blocks.values() for b in blocks] self.headers = sorted(list(all_h)) self.lbl_headers.config(text="可用表头(%d 个 Sheet): %s" % (len(self.sheet_blocks), " | ".join(self.headers))) self.log(f"解析完成:{len(self.sheet_blocks)} 个 Sheet、{len(self.blocks)} 个数据组。" f"上方勾选只决定输出哪些 Sheet,改完直接点执行即可。") for f in self.left_cond_frames: f.frame.destroy() for f in self.left_target_frames: f.frame.destroy() self.left_cond_frames.clear() self.left_target_frames.clear() self.right_trigger_cb['values'] = self.headers if self.headers: self.right_trigger_cb.current(0) for f in self.right_target_frames: f.frame.destroy() self.right_target_frames.clear() self.bv_trigger_cb['values'] = self.headers if self.headers: idx = 0 for i, h in enumerate(self.headers): if h.upper() == "ID": idx = i; break self.bv_trigger_cb.current(idx) for f in self.bv_target_frames: f.frame.destroy() self.bv_target_frames.clear() if self.headers: self.add_left_condition() self.add_left_target() self.add_right_target() self.add_bv_target() def add_left_condition(self): if not self.headers: return frame = tk.Frame(self.frame_left_cond_inner) frame.pack(fill=tk.X, pady=2) cb = ttk.Combobox(frame, values=self.headers, state="readonly", width=15) cb.pack(side=tk.LEFT, padx=2) if self.headers: cb.current(0) tk.Label(frame, text="目标值:").pack(side=tk.LEFT) entry_val = tk.Entry(frame, width=8) entry_val.pack(side=tk.LEFT, padx=2) var_approx = tk.BooleanVar(value=False) chk = tk.Checkbutton(frame, text="近似", variable=var_approx) chk.pack(side=tk.LEFT, padx=2) tk.Label(frame, text="误差:").pack(side=tk.LEFT) entry_err = tk.Entry(frame, width=6) entry_err.pack(side=tk.LEFT, padx=2) def remove(): self.left_cond_frames.remove({'frame': frame, 'cb': cb, 'val': entry_val, 'approx': var_approx, 'err': entry_err}) frame.destroy() tk.Button(frame, text="删", command=remove, fg="red").pack(side=tk.LEFT, padx=2) self.left_cond_frames.append({'frame': frame, 'cb': cb, 'val': entry_val, 'approx': var_approx, 'err': entry_err}) def add_left_target(self): if not self.headers: return frame = tk.Frame(self.frame_left_target_inner) frame.pack(fill=tk.X, pady=2) cb = ttk.Combobox(frame, values=self.headers, state="readonly", width=15) cb.pack(side=tk.LEFT, padx=2) if self.headers: cb.current(0) def remove(): self.left_target_frames.remove({'frame': frame, 'cb': cb}) frame.destroy() tk.Button(frame, text="删", command=remove, fg="red").pack(side=tk.LEFT, padx=2) self.left_target_frames.append({'frame': frame, 'cb': cb}) def add_right_target(self): if not self.headers: return frame = tk.Frame(self.frame_right_target_inner) frame.pack(fill=tk.X, pady=2) cb = ttk.Combobox(frame, values=self.headers, state="readonly", width=15) cb.pack(side=tk.LEFT, padx=2) if self.headers: cb.current(0) def remove(): self.right_target_frames.remove({'frame': frame, 'cb': cb}) frame.destroy() tk.Button(frame, text="删", command=remove, fg="red").pack(side=tk.LEFT, padx=2) self.right_target_frames.append({'frame': frame, 'cb': cb}) def add_bv_target(self): if not self.headers: return frame = tk.Frame(self.frame_bv_target_inner) frame.pack(fill=tk.X, pady=2) cb = ttk.Combobox(frame, values=self.headers, state="readonly", width=15) cb.pack(side=tk.LEFT, padx=2) if self.headers: cb.current(0) def remove(): self.bv_target_frames.remove({'frame': frame, 'cb': cb}) frame.destroy() tk.Button(frame, text="删", command=remove, fg="red").pack(side=tk.LEFT, padx=2) self.bv_target_frames.append({'frame': frame, 'cb': cb}) def _try_parse_num(self, val): try: s = str(val).strip() if not s: return "" return float(s) except: return str(val).strip() def _collect_config(self): """收集三栏配置,校验失败返回 None。""" left_conditions = [] for cf in self.left_cond_frames: header = cf['cb'].get().strip() if not header: continue val_str = cf['val'].get().strip() if not val_str: continue try: val = float(val_str) except Exception: val = val_str err_str = cf['err'].get().strip() err_val = None if err_str: try: err_val = float(err_str) except Exception: pass left_conditions.append({'header': header, 'target_val': val, 'approx': cf['approx'].get(), 'allowed_err': err_val}) left_targets = [tf['cb'].get().strip() for tf in self.left_target_frames if tf['cb'].get().strip()] right_trigger = self.right_trigger_cb.get().strip() right_targets = [tf['cb'].get().strip() for tf in self.right_target_frames if tf['cb'].get().strip()] bv_trigger = self.bv_trigger_cb.get().strip() bv_targets = [tf['cb'].get().strip() for tf in self.bv_target_frames if tf['cb'].get().strip()] bv_mode = self.bv_mode_cb.get() bv_threshold = None do_bv = bool(bv_trigger) if do_bv and bv_mode == "超过指定电流值": th_str = self.entry_bv_threshold.get().strip() try: bv_threshold = float(th_str) if th_str else 0.02 except Exception: messagebox.showerror("错误", "BV阈值必须是数字") return None do_left = bool(left_conditions and left_targets) do_right = bool(right_trigger and right_targets) if not do_left and not do_right and not do_bv: messagebox.showwarning("提示", "请至少完整配置一侧的功能") return None return { 'left_conditions': left_conditions, 'left_targets': left_targets, 'do_left': do_left, 'right_trigger': right_trigger, 'right_targets': right_targets, 'do_right': do_right, 'bv_trigger': bv_trigger, 'bv_targets': bv_targets, 'bv_mode': bv_mode, 'bv_threshold': bv_threshold, 'do_bv': do_bv, } def run_extraction(self): if not getattr(self, 'sheet_blocks', None): messagebox.showwarning("提示", "无可用数据,请先加载文件") return targets = [n for n in self.sheet_blocks if n in self.sheet_sel.selected()] if not targets: messagebox.showwarning("提示", "请至少勾选一个要输出结果的 Sheet") return cfg = self._collect_config() if cfg is None: return src_path = self.file_path.get() folder = os.path.dirname(src_path) base_name = os.path.splitext(os.path.basename(src_path))[0] date_str = datetime.datetime.now().strftime("%Y%m%d%H%M%S") out_dir = os.path.join(folder, "Extract_Results") os.makedirs(out_dir, exist_ok=True) out_path = os.path.join(out_dir, f"{base_name}_提取结果_{date_str}.xlsx") counter = 1 while os.path.exists(out_path): out_path = os.path.join(out_dir, f"{base_name}_提取结果_{date_str}_{counter}.xlsx") counter += 1 wb = Workbook() default_ws = wb.active fill_red = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid") fill_yellow = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid") self.log(f"\n--- 开始处理 {len(targets)}/{len(self.sheet_blocks)} 个 Sheet ---") for sheet_name in targets: blocks = self.sheet_blocks[sheet_name] self.log(f"▶ Sheet '{sheet_name}'({len(blocks)} 个数据组)") if cfg['do_left']: matrix, colors = self._calc_left(blocks, cfg) self._write_result_sheet(wb, sheet_name, "_条件提取", matrix, colors, fill_red, fill_yellow) if cfg['do_right']: matrix, colors = self._calc_right(blocks, cfg) self._write_result_sheet(wb, sheet_name, "_空值提取", matrix, colors, fill_red, fill_yellow) if cfg['do_bv']: matrix, colors = self._calc_bv(blocks, cfg) self._write_result_sheet(wb, sheet_name, "_BV", matrix, colors, fill_red, fill_yellow) if len(wb.sheetnames) > 1 and default_ws.title == "Sheet": wb.remove(default_ws) try: wb.save(out_path) self.log(f"\n🎉 全部完成!文件已保存至: {os.path.basename(out_path)}") messagebox.showinfo("完成", f"数据提取完成!共 {len(targets)} 个 Sheet。\n已保存在 Extract_Results 文件夹:{os.path.basename(out_path)}") except Exception as e: self.log(f"保存失败: {e}") messagebox.showerror("错误", f"保存失败: {e}") def _write_result_sheet(self, wb, base, suffix, matrix, colors, fill_red, fill_yellow): """按「源 sheet 名_功能名」写一张结果表;勾选 ABS 时紧跟一张 _ABS 表。""" ws = wb.create_sheet(_unique_sheet_name(wb, base, suffix)) for r_idx, row_data in enumerate(matrix): ws.append([_to_number(x) for x in row_data]) color = colors[r_idx] if r_idx > 0 and color != "NONE": fill = fill_red if color == "RED" else fill_yellow for c_idx in range(1, len(row_data) + 1): ws.cell(row=ws.max_row, column=c_idx).fill = fill if self.gen_abs_out.get(): ws_abs = wb.create_sheet(_unique_sheet_name(wb, base, suffix + "_ABS")) for r_idx, row_data in enumerate(matrix): ws_abs.append([_to_abs_number(x) for x in row_data]) color = colors[r_idx] if r_idx > 0 and color != "NONE": fill = fill_red if color == "RED" else fill_yellow for c_idx in range(1, len(row_data) + 1): ws_abs.cell(row=ws_abs.max_row, column=c_idx).fill = fill return ws def _calc_left(self, blocks, cfg): """左侧:按条件匹配提取,返回 (矩阵, 颜色标记)。""" left_conditions = cfg['left_conditions'] left_targets = cfg['left_targets'] out_headers = ["组名"] for c in left_conditions: if c['approx']: out_headers.extend([f"{c['header']}_需求值", f"{c['header']}_实际值"]) else: out_headers.append(c['header']) out_headers.extend(left_targets) out_headers.append("额外匹配值") matrix = [out_headers] colors = ["NONE"] for blk in blocks: group_name = blk['name'] headers_map = blk['headers'] data_rows = blk['data'] missing = [c['header'] for c in left_conditions if c['header'] not in headers_map] + [t for t in left_targets if t not in headers_map] if missing: matrix.append([group_name] + ["NA"] * (len(out_headers) - 1)) colors.append("RED") continue cond_indices = [headers_map[c['header']] for c in left_conditions] target_indices = [headers_map[t] for t in left_targets] matched_rows = [] for row_data in data_rows: is_match = True total_err = 0 for i, c in enumerate(left_conditions): cidx = cond_indices[i] if cidx >= len(row_data): is_match = False; break cell_val = _extract_float(row_data[cidx]) if cell_val is None: is_match = False; break if not isinstance(c['target_val'], float): if str(row_data[cidx]).strip() != str(c['target_val']).strip(): is_match = False; break else: diff = abs(cell_val - c['target_val']) if not c['approx']: if diff > 1e-5: is_match = False; break else: if c['allowed_err'] is not None and diff > c['allowed_err']: is_match = False; break total_err += diff if is_match: matched_rows.append({'row': row_data, 'err': total_err}) if not matched_rows: matrix.append([group_name] + ["NA"] * (len(out_headers) - 1)) colors.append("RED") continue if any(c['approx'] for c in left_conditions): min_err = min(m['err'] for m in matched_rows) matched_rows = [m for m in matched_rows if abs(m['err'] - min_err) < 1e-7] primary = matched_rows[0]['row'] out_row = [group_name] for i, c in enumerate(left_conditions): if c['approx']: out_row.append(self._try_parse_num(c['target_val'])) out_row.append(self._try_parse_num(primary[cond_indices[i]])) else: out_row.append(self._try_parse_num(primary[cond_indices[i]])) for t_idx in target_indices: out_row.append(self._try_parse_num(primary[t_idx])) if len(matched_rows) > 1: extra_strs = [f"[{str(m['row'][target_indices[0]])}]" for m in matched_rows[1:]] out_row.append("; ".join(extra_strs)) colors.append("YELLOW") else: out_row.append("") colors.append("NONE") matrix.append(out_row) return matrix, colors def _calc_right(self, blocks, cfg): """中间:找触发列空值,取上一行目标列。""" right_trigger = cfg['right_trigger'] right_targets = cfg['right_targets'] out_headers = ["组名", f"触发列({right_trigger})", f"上一行({right_trigger})值"] + right_targets matrix = [out_headers] colors = ["NONE"] for blk in blocks: group_name = blk['name'] headers_map = blk['headers'] data_rows = blk['data'] missing = [right_trigger] if right_trigger not in headers_map else [] missing += [t for t in right_targets if t not in headers_map] if missing: matrix.append([group_name, "缺少列", "NA"] + ["NA"] * len(right_targets)) colors.append("RED") continue trigger_idx = headers_map[right_trigger] target_indices = [headers_map[t] for t in right_targets] found_empty_row_idx = -1 for r_idx, row_data in enumerate(data_rows): if trigger_idx >= len(row_data): continue cell_str = str(row_data[trigger_idx]).strip() if cell_str == "" or cell_str.lower() == "none": found_empty_row_idx = r_idx break if found_empty_row_idx == -1: matrix.append([group_name, "整列无空值", "NA"] + ["NA"] * len(right_targets)) colors.append("RED") continue if found_empty_row_idx == 0: matrix.append([group_name, "第一行为空", "NA"] + ["NA"] * len(right_targets)) colors.append("RED") continue prev_row_data = data_rows[found_empty_row_idx - 1] has_empty_target = False out_row = [group_name, "已找到空值", self._try_parse_num(prev_row_data[trigger_idx])] for t_idx in target_indices: val = prev_row_data[t_idx] if t_idx < len(prev_row_data) else "" if str(val).strip() == "" or str(val).lower() == "none": has_empty_target = True out_row.append(self._try_parse_num(val)) if has_empty_target: out_row[1] = "上一行对应值为空" colors.append("RED") else: colors.append("NONE") matrix.append(out_row) return matrix, colors def _calc_bv(self, blocks, cfg): """右侧:自动抓取 BV_on 击穿点(取突变前一行 VD)。""" bv_trigger = cfg['bv_trigger'] bv_targets = cfg['bv_targets'] bv_mode = cfg['bv_mode'] bv_threshold = cfg['bv_threshold'] out_headers = ["组名", "BV_on(上一行VD)", "上一行ID"] + bv_targets matrix = [out_headers] colors = ["NONE"] for blk in blocks: group_name = blk['name'] headers_map = blk['headers'] data_rows = blk['data'] if "VD" not in headers_map or bv_trigger not in headers_map: matrix.append([group_name, "缺少VD或ID列", "NA"] + ["NA"] * len(bv_targets)) colors.append("RED") continue missing = [t for t in bv_targets if t not in headers_map] if missing: matrix.append([group_name, "缺少目标列", "NA"] + ["NA"] * len(bv_targets)) colors.append("RED") continue vd_idx = headers_map["VD"] id_idx = headers_map[bv_trigger] target_indices = [headers_map[t] for t in bv_targets] trigger_idx = -1 if bv_mode == "自动找最大斜率(推荐)": max_slope = -1 for r_idx in range(1, len(data_rows)): prev_row = data_rows[r_idx - 1] curr_row = data_rows[r_idx] if vd_idx >= len(prev_row) or vd_idx >= len(curr_row): continue if id_idx >= len(prev_row) or id_idx >= len(curr_row): continue prev_vd = _extract_float(prev_row[vd_idx]) curr_vd = _extract_float(curr_row[vd_idx]) prev_id = _extract_float(prev_row[id_idx]) curr_id = _extract_float(curr_row[id_idx]) if prev_vd is not None and curr_vd is not None and prev_id is not None and curr_id is not None: delta_v = abs(curr_vd - prev_vd) delta_i = abs(curr_id - prev_id) if delta_v > 1e-9: slope = delta_i / delta_v if delta_i > 1e-9 and slope > max_slope: max_slope = slope trigger_idx = r_idx - 1 elif bv_mode == "超过指定电流值": for r_idx in range(1, len(data_rows)): row = data_rows[r_idx] if id_idx >= len(row): continue curr_id = _extract_float(row[id_idx]) if curr_id is not None and abs(curr_id) >= bv_threshold: trigger_idx = r_idx - 1 break elif bv_mode == "找断崖/变空": for r_idx in range(1, len(data_rows)): prev_row = data_rows[r_idx - 1] curr_row = data_rows[r_idx] if id_idx >= len(prev_row) or id_idx >= len(curr_row): continue prev_id = _extract_float(prev_row[id_idx]) curr_id = _extract_float(curr_row[id_idx]) if prev_id is not None and curr_id is None and abs(prev_id) > 0: trigger_idx = r_idx - 1 break if trigger_idx == -1: matrix.append([group_name, "未发现明显突变", "NA"] + ["NA"] * len(bv_targets)) colors.append("RED") continue safe_row = data_rows[trigger_idx] out_row = [group_name] bv_vd = safe_row[vd_idx] if vd_idx < len(safe_row) else "NA" bv_id = safe_row[id_idx] if id_idx < len(safe_row) else "NA" out_row.append(self._try_parse_num(bv_vd)) out_row.append(self._try_parse_num(bv_id)) for t_idx in target_indices: val = safe_row[t_idx] if t_idx < len(safe_row) else "" out_row.append(self._try_parse_num(val)) matrix.append(out_row) colors.append("NONE") return matrix, colors# ==========================================# 模块三:Merge Data 自定义拼表# ==========================================class MergeDataApp: def __init__(self, parent, main_root): self.root = parent self.main_root = main_root self.file_path = tk.StringVar() self.gen_abs_out = tk.BooleanVar(value=True) # 【新】是否生成绝对值 ABS Sheet self.blocks = [] self.group_vars = {} self.col_frames = [] self.sheet_blocks = {} self.setup_ui() def setup_ui(self): frame_top = tk.Frame(self.root, pady=10, padx=10) frame_top.pack(fill=tk.X) tk.Label(frame_top, text="选择 Summary 文件:").pack(side=tk.LEFT) tk.Entry(frame_top, textvariable=self.file_path, width=50).pack(side=tk.LEFT, padx=5) tk.Button(frame_top, text="浏览", command=self.select_file).pack(side=tk.LEFT) tk.Button(frame_top, text="加载并解析", command=self.load_file, bg="#2196F3", fg="white").pack(side=tk.LEFT, padx=10) self.sheet_sel = SheetSelector(self.root) self.sheet_sel.frame.pack(fill=tk.X, padx=10, pady=(0, 4)) self.sheet_sel.on_change = self._on_sheet_toggle paned_window = tk.PanedWindow(self.root, orient=tk.HORIZONTAL, sashrelief=tk.RAISED) paned_window.pack(fill=tk.BOTH, expand=True, padx=10, pady=5) # 左侧:数据组选择 frame_left = tk.Frame(paned_window) paned_window.add(frame_left, width=450) tk.Label(frame_left, text="【左侧:勾选要提取的数据组】", font=("Arial", 11, "bold"), fg="#2196F3").pack(anchor="w", pady=5) self.group_canvas = tk.Canvas(frame_left) self.group_scroll = tk.Scrollbar(frame_left, orient="vertical", command=self.group_canvas.yview) self.group_inner = tk.Frame(self.group_canvas) self.group_inner.bind("<Configure>", lambda e: self.group_canvas.configure(scrollregion=self.group_canvas.bbox("all"))) self.group_canvas.create_window((0, 0), window=self.group_inner, anchor="nw") self.group_canvas.configure(yscrollcommand=self.group_scroll.set) self.group_canvas.pack(side=tk.LEFT, fill=tk.BOTH, expand=True) self.group_scroll.pack(side=tk.RIGHT, fill=tk.Y) # 右侧:需要提取的列名 frame_right = tk.Frame(paned_window) paned_window.add(frame_right, width=450) tk.Label(frame_right, text="【右侧:添加要提取的列】", font=("Arial", 11, "bold"), fg="#FF9800").pack(anchor="w", pady=5) f_add_col = tk.Frame(frame_right) f_add_col.pack(fill=tk.X, pady=5) self.entry_new_col = tk.Entry(f_add_col, width=15) self.entry_new_col.pack(side=tk.LEFT, padx=5) tk.Button(f_add_col, text="+ 增加提取列", command=self.add_col_input).pack(side=tk.LEFT) self.col_inner = tk.Frame(frame_right) self.col_inner.pack(fill=tk.BOTH, expand=True, pady=5) frame_btn = tk.Frame(self.root, pady=10) frame_btn.pack() tk.Checkbutton(frame_btn, text="合并结果同时附带绝对值 (ABS) Sheet", variable=self.gen_abs_out, fg="#FF9800").pack(side=tk.LEFT, padx=10) tk.Button(frame_btn, text="执行合并并保存", command=self.run_merge, bg="#4CAF50", fg="white", font=("Arial", 12, "bold"), width=30).pack(side=tk.LEFT) self.log_area = ScrolledText(self.root, height=8, state='disabled', font=("Consolas", 10)) self.log_area.pack(fill=tk.BOTH, expand=True, padx=10, pady=5) def log(self, msg): self.log_area.config(state='normal') self.log_area.insert(tk.END, msg + "\n") self.log_area.see(tk.END) self.log_area.config(state='disabled') self.root.update_idletasks() def select_file(self): path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")]) if path: self.file_path.set(path) try: wb = openpyxl.load_workbook(path, read_only=True) sheets = list(wb.sheetnames) wb.close() self.sheet_sel.set_sheets(sheets) self.log(f"已载入文件,共 {len(sheets)} 个 Sheet(默认全选)") except Exception as e: self.sheet_sel.set_sheets([]) self.log(f"读取工作表列表失败: {e}") def load_file(self): path = self.file_path.get() if not path or not os.path.exists(path): messagebox.showerror("错误", "请选择有效的 Excel 文件") return targets = self.sheet_sel.all_names() if not targets: messagebox.showwarning("提示", "文件内没有可解析的 Sheet") return self.sheet_blocks = {} self.blocks = [] self.log(f"正在解析文件: {os.path.basename(path)} -> 全部 {len(targets)} 个 Sheet...") sheet_data = _read_workbook_sheets(path, targets) if not sheet_data: self.log("读取失败:所有 Sheet 均无法读取") self.sheet_sel.mark_no_data(targets) return no_data = [] for name in targets: data = sheet_data.get(name) if not data: no_data.append(name) self.log(f"[跳过] Sheet '{name}' 无有效数据") continue try: blocks = _parse_blocks(data) except Exception as e: no_data.append(name) self.log(f"[失败] Sheet '{name}' 解析失败: {e}") continue if not blocks: no_data.append(name) self.log(f"[跳过] Sheet '{name}' 未解析出数据组") continue self.sheet_blocks[name] = blocks self.sheet_sel.mark_no_data(no_data) if not self.sheet_blocks: messagebox.showwarning("提示", "所有 Sheet 均未解析出数据组,请检查文件") return self.blocks = [b for blocks in self.sheet_blocks.values() for b in blocks] for widget in self.group_inner.winfo_children(): widget.destroy() self.group_vars = {} for sheet_name, blocks in self.sheet_blocks.items(): tk.Label(self.group_inner, text=f"■ {sheet_name}({len(blocks)} 组)", fg="#2196F3", font=("Arial", 9, "bold")).pack(anchor="w", padx=4, pady=(6, 2)) for i, blk in enumerate(blocks): var = tk.BooleanVar(value=False) tk.Checkbutton(self.group_inner, text=blk['name'], variable=var).pack(anchor="w", padx=16, pady=1) self.group_vars[(sheet_name, i)] = var self.log(f"解析成功!共 {len(self.sheet_blocks)} 个 Sheet、{len(self.blocks)} 个数据组。" f"勾选 Sheet 与数据组后直接点执行即可,不需要重新加载。") for f in self.col_frames: f[0].destroy() self.col_frames.clear() self.add_col_input() def _on_sheet_toggle(self, sel): """取消某张 sheet 时,把它下面的数据组一并取消,避免留下无效勾选。""" checked = set(sel.selected()) for (sheet, _i), var in self.group_vars.items(): if sheet not in checked and var.get(): var.set(False) def add_col_input(self): default_col = self.entry_new_col.get().strip() frame = tk.Frame(self.col_inner) frame.pack(fill=tk.X, pady=2) entry = tk.Entry(frame, width=20) if default_col: entry.insert(0, default_col) self.entry_new_col.delete(0, tk.END) entry.pack(side=tk.LEFT, padx=5) def remove(): self.col_frames.remove((frame, entry)) frame.destroy() tk.Button(frame, text="删除", command=remove, fg="red").pack(side=tk.LEFT, padx=5) self.col_frames.append((frame, entry)) def _build_merge_matrix(self, blocks, cols): """把一个 sheet 内勾选的数据组按列拼成矩阵。""" matrix = [] header_row = [] for blk in blocks: for col in cols: header_row.append(f"{blk['name']}_{col}") matrix.append(header_row) max_rows = max(len(blk['data']) for blk in blocks) if blocks else 0 for r_idx in range(max_rows): row_line = [] for blk in blocks: for col in cols: cidx = blk['headers'][col] if r_idx < len(blk['data']) and cidx < len(blk['data'][r_idx]): row_line.append(blk['data'][r_idx][cidx]) else: row_line.append("") matrix.append(row_line) return matrix def run_merge(self): if not getattr(self, 'sheet_blocks', None): messagebox.showwarning("提示", "请先加载 Summary 文件") return checked_sheets = set(self.sheet_sel.selected()) selected = [(sheet, i) for (sheet, i), var in self.group_vars.items() if var.get() and sheet in checked_sheets] if not selected: messagebox.showwarning("提示", "请至少勾选一个数据组") return selected_cols = [] for frame, entry in self.col_frames: col_name = entry.get().strip() if col_name: selected_cols.append(col_name) if not selected_cols: messagebox.showwarning("提示", "请至少输入一列要提取的列名(例如 VD 或 ID)") return plan = {} missing = [] for sheet, i in selected: blk = self.sheet_blocks[sheet][i] lacks = [c for c in selected_cols if c not in blk['headers']] if lacks: missing.append(f"[{sheet}] {blk['name']} 缺少列: {', '.join(lacks)}") else: plan.setdefault(sheet, []).append(blk) if missing: for line in missing: self.log("缺列: " + line) msg = "以下数据组缺少所需列,将被跳过:\n\n" + "\n".join(missing[:12]) if len(missing) > 12: msg += f"\n... 共 {len(missing)} 条" if not messagebox.askyesno("缺列提示", msg + "\n\n是否继续合并其余数据组?"): return if not plan: messagebox.showerror("错误", "所选数据组均缺少所需列,无法合并") return src_path = self.file_path.get() folder = os.path.dirname(src_path) date_str = datetime.datetime.now().strftime("%Y%m%d%H%M%S") out_name = f"merge data_{date_str}.xlsx" out_path = os.path.join(folder, out_name) counter = 1 while os.path.exists(out_path): out_path = os.path.join(folder, f"merge data_{date_str}_{counter}.xlsx") counter += 1 try: wb = Workbook() default_ws = wb.active for sheet, blocks in plan.items(): matrix = self._build_merge_matrix(blocks, selected_cols) ws1 = wb.create_sheet(_unique_sheet_name(wb, sheet, "_Merge")) for row in matrix: ws1.append([_to_number(x) for x in row]) if self.gen_abs_out.get(): ws2 = wb.create_sheet(_unique_sheet_name(wb, sheet, "_Merge_ABS")) for row in matrix: ws2.append([_to_abs_number(x) for x in row]) self.log(f" ✅ {sheet}:{len(blocks)} 组,{len(matrix) - 1} 行") if len(wb.sheetnames) > 1 and default_ws.title == "Sheet": wb.remove(default_ws) wb.save(out_path) self.log(f"\n🎉 合并完成!文件已保存至: {os.path.basename(out_path)}") messagebox.showinfo("完成", f"合并完成!共 {len(plan)} 个 Sheet。\n已保存在同目录:{os.path.basename(out_path)}") except Exception as e: self.log(f"保存失败: {e}") messagebox.showerror("错误", f"保存失败: {e}")# ==========================================# 主控台:用多标签页组合三个功能# ==========================================if __name__ == "__main__": root = tk.Tk() root.title("B1500 综合数据处理与提取工具") root.geometry("1300x850") style = ttk.Style() style.theme_use('clam') style.configure("TNotebook.Tab", padding=[20, 5], font=('Arial', 11, 'bold')) notebook = ttk.Notebook(root) notebook.pack(fill=tk.BOTH, expand=True) tab_summary = tk.Frame(notebook) tab_extract = tk.Frame(notebook) tab_merge = tk.Frame(notebook) notebook.add(tab_summary, text=" 一、 原始数据整理 (生成 Summary) ") notebook.add(tab_merge, text=" 二、 数据拼表 (Data Merger) ") notebook.add(tab_extract, text=" 三、 数据提取 (Data Extractor) ") app1 = DataProcessorApp(tab_summary, root) app3 = MergeDataApp(tab_merge, root) app2 = DataExtractorApp(tab_extract, root) root.mainloop()