Public feed
#6jMjup8QZv
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# ==========================================# 模块一:数据整理与生成 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.idvd_target = tk.StringVar() self.use_com = tk.BooleanVar(value=True if HAS_WIN32COM else False) self.is_running = False self.global_xl = None self.stats = {"success": 0, "failed": 0, "unmatched": 0, "total_files": 0, "failed_list": []} 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 提取目标 (可选):").pack(side=tk.LEFT) self.cb_idvd = ttk.Combobox(frame_vg, textvariable=self.idvd_target, state='readonly', width=30) self.cb_idvd.pack(side=tk.LEFT, padx=5) tk.Label(frame_vg, text="(额外生成 IDVD Data Summary)", fg="gray").pack(side=tk.LEFT, padx=5) 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) 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=18, 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 = [] names = [c[0] for c in cats] self.cb_idvd['values'] = [""] + names cur = self.idvd_target.get() if cur and cur not in names: self.idvd_target.set("") 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 实例未初始化") wb = None try: abs_path = os.path.abspath(file_path) if file_path.lower().endswith(('.csv', '.txt')): try: wb = self.global_xl.Workbooks.OpenText(Filename=abs_path, Origin=1200, StartRow=1, DataType=1, TextQualifier=1, Tab=True, Comma=True) except Exception: wb = self.global_xl.Workbooks.Open(abs_path, ReadOnly=True, UpdateLinks=0, Local=True) else: wb = self.global_xl.Workbooks.Open(abs_path, ReadOnly=True, UpdateLinks=0) ws = wb.Sheets(1) values = ws.UsedRange.Value data = [] if values: if isinstance(values, tuple): for row in values: if isinstance(row, tuple): clean_row = [str(cell).replace('\x00', '').replace('\r', '').replace('\n', '').strip() if cell is not None else "" for cell in row] data.append(clean_row) else: data.append([str(values).replace('\x00', '').strip() if values is not None else ""]) else: data.append([str(values).replace('\x00', '').strip()]) return data except Exception as e: raise ValueError(f"Excel COM 读取失败: {e}") finally: if wb: try: wb.Close(False) except Exception: pass 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 to_number(self, 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 val 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 = [self.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 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_target = self.idvd_target.get().strip() valid_names = [c[0] for c in cats] if idvd_target and idvd_target not in valid_names: self.log(f"⚠️ 高级提取目标 '{idvd_target}' 不在分类列表中,已忽略") idvd_target = "" 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_target and cat_files.get(idvd_target): self.log(f"\n--- 3. 高级 ID-VD 两列提取: '{idvd_target}' ---") self.lbl_status.config(text="IDVD 处理中...") try: self._build_idvd_summary(idvd_target, cat_files[idvd_target], file_cache, results_dir, date_str) except Exception as e: self.log(f"[失败] {idvd_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) self.save_excel(matrix, display, 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) self.save_excel(matrix, f"{display}_IDVD Summary", 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.headers = [] self.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=60).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.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.Button(frame_btn, text="执行提取并保存在同目录", command=self.run_extraction, bg="#4CAF50", fg="white", font=("Arial", 12, "bold"), width=30).pack() self.log_area = ScrolledText(self.root, height=10, 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) def _read_with_excel_com(self, path): 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) 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 load_file(self): path = self.file_path.get() if not path or not os.path.exists(path): messagebox.showerror("错误", "请选择有效的 Excel 文件") return self.log(f"正在解析文件: {os.path.basename(path)}...") data = None if HAS_WIN32COM: try: data = self._read_with_excel_com(path) self.log("✅ 后台 Excel 读取成功!") except Exception: pass if data is None: try: temp_dir = tempfile.gettempdir() temp_path = os.path.join(temp_dir, "temp_summary_reader.xlsx") shutil.copy2(path, temp_path) wb = openpyxl.load_workbook(temp_path, data_only=True) ws = wb.active data = list(ws.iter_rows(values_only=True)) wb.close() self.log("✅ 常规读取成功!") except Exception as e: self.log(f"❌ 读取彻底失败: {e}") return if not data: return try: self.blocks = self._parse_blocks(data) all_h = set() for blk in self.blocks: for h in blk['headers'].keys(): if h: all_h.add(h) self.headers = sorted(list(all_h)) self.lbl_headers.config(text="可用表头: " + " | ".join(self.headers)) self.log(f"解析成功!共找到 {len(self.blocks)} 个数据组。") 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() except Exception as e: self.log(f"❌ 数据解析失败: {e}") def _parse_blocks(self, 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 blocks 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 _extract_float(self, 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 None def _try_parse_num(self, val): try: s = str(val).strip() if not s: return "" return float(s) except: return str(val).strip() def run_extraction(self): if not self.blocks: messagebox.showwarning("提示", "无可用数据,请先加载文件") return 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: val = val_str err_str = cf['err'].get().strip() err_val = None if err_str: try: err_val = float(err_str) except: 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()] do_bv = False bv_mode = self.bv_mode_cb.get() bv_threshold = None if bv_trigger: do_bv = True if bv_mode == "超过指定电流值": try: th_str = self.entry_bv_threshold.get().strip() if th_str: bv_threshold = float(th_str) else: bv_threshold = 0.02 except: messagebox.showerror("错误", "BV阈值必须是数字") return 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 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") 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") if do_left: ws_left = wb.create_sheet("条件提取结果") 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("额外匹配值") ws_left.append(out_headers) self.log("\n--- 开始执行左侧:条件提取 ---") for blk in self.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: row_data = [group_name] + ["NA"] * (len(out_headers) - 1) ws_left.append(row_data) for col_idx in range(1, len(row_data) + 1): ws_left.cell(row=ws_left.max_row, column=col_idx).fill = fill_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 = self._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: row_data = [group_name] + ["NA"] * (len(out_headers) - 1) ws_left.append(row_data) for col_idx in range(1, len(row_data) + 1): ws_left.cell(row=ws_left.max_row, column=col_idx).fill = fill_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)) ws_left.append(out_row) for col_idx in range(1, len(out_row) + 1): ws_left.cell(row=ws_left.max_row, column=col_idx).fill = fill_yellow else: out_row.append("") ws_left.append(out_row) self.log(f"✅ 左侧条件提取完成,已写入 Sheet。") if do_right: ws_right = wb.create_sheet("空值上一行提取") out_headers_r = ["组名", f"触发列({right_trigger})", f"上一行({right_trigger})值"] + right_targets ws_right.append(out_headers_r) self.log("\n--- 开始执行中间:找空值取上一行 ---") for blk in self.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: row_data = [group_name, "缺少列", "NA"] + ["NA"] * len(right_targets) ws_right.append(row_data) for col_idx in range(1, len(row_data) + 1): ws_right.cell(row=ws_right.max_row, column=col_idx).fill = fill_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: row_data = [group_name, "整列无空值", "NA"] + ["NA"] * len(right_targets) ws_right.append(row_data) for col_idx in range(1, len(row_data) + 1): ws_right.cell(row=ws_right.max_row, column=col_idx).fill = fill_red continue if found_empty_row_idx == 0: row_data = [group_name, "第一行为空", "NA"] + ["NA"] * len(right_targets) ws_right.append(row_data) for col_idx in range(1, len(row_data) + 1): ws_right.cell(row=ws_right.max_row, column=col_idx).fill = fill_red continue prev_row_data = data_rows[found_empty_row_idx - 1] has_empty_target = False out_row_r = [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_r.append(self._try_parse_num(val)) if has_empty_target: out_row_r[1] = "上一行对应值为空" ws_right.append(out_row_r) for col_idx in range(1, len(out_row_r) + 1): ws_right.cell(row=ws_right.max_row, column=col_idx).fill = fill_red else: ws_right.append(out_row_r) self.log(f"✅ 中间空值提取完成,已写入 Sheet。") if do_bv: ws_bv = wb.create_sheet("BV_on 提取结果") out_headers_bv = ["组名", "BV_on(上一行VD)", "上一行ID"] + bv_targets ws_bv.append(out_headers_bv) self.log("\n--- 开始执行右侧:自动抓取 BV_on 击穿点 ---") for blk in self.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: row_data = [group_name, "缺少VD或ID列", "NA"] + ["NA"] * len(bv_targets) ws_bv.append(row_data) for col_idx in range(1, len(row_data) + 1): ws_bv.cell(row=ws_bv.max_row, column=col_idx).fill = fill_red continue missing = [t for t in bv_targets if t not in headers_map] if missing: row_data = [group_name, "缺少目标列", "NA"] + ["NA"] * len(bv_targets) ws_bv.append(row_data) for col_idx in range(1, len(row_data) + 1): ws_bv.cell(row=ws_bv.max_row, column=col_idx).fill = fill_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 = self._extract_float(prev_row[vd_idx]) curr_vd = self._extract_float(curr_row[vd_idx]) prev_id = self._extract_float(prev_row[id_idx]) curr_id = self._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)): prev_row = data_rows[r_idx - 1] curr_row = data_rows[r_idx] if id_idx >= len(curr_row): continue curr_id = self._extract_float(curr_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 = self._extract_float(prev_row[id_idx]) curr_id = self._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: row_data = [group_name, "未发现明显突变", "NA"] + ["NA"] * len(bv_targets) ws_bv.append(row_data) for col_idx in range(1, len(row_data) + 1): ws_bv.cell(row=ws_bv.max_row, column=col_idx).fill = fill_red else: safe_row = data_rows[trigger_idx] out_row_bv = [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_bv.append(self._try_parse_num(bv_vd)) out_row_bv.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_bv.append(self._try_parse_num(val)) ws_bv.append(out_row_bv) self.log(f"✅ 右侧 BV_on 提取完成,已写入 Sheet。") 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"数据提取完成!\n已保存在 Extract_Results 文件夹:{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) notebook.add(tab_summary, text=" 一、 原始数据整理 (生成 Summary) ") notebook.add(tab_extract, text=" 二、 数据综合提取 (条件 / 空值 / BV) ") app1 = DataProcessorApp(tab_summary, root) app2 = DataExtractorApp(tab_extract, root) root.mainloop()