"""VBA 导出工具依赖：pip install oletools"""import osimport reimport threadingimport tkinter as tkfrom collections import Counterfrom tkinter import ttk, filedialog, messageboxfrom datetime import datetimetry: from oletools.olevba import VBA_Parserexcept ImportError: VBA_Parser = None# ────────────────────────────────# 模块类型识别（基于名称启发式判断）# ────────────────────────────────# 用户窗体名称前缀/全名的白名单，避免 "frm" 误匹配 "framework" 等_FORM_PREFIXES = re.compile(r'^(frm|userform)', re.IGNORECASE)def _guess_module_type(vba_filename: str, stream_path: str) -> tuple[int, str]: """ 返回 (type_id, type_label) type_id: 1=标准模块, 2=类模块, 3=用户窗体, 100=文档模块 """ name_lower = vba_filename.lower() stream_lower = stream_path.lower() if name_lower == "thisworkbook": return 100, "文档模块 · ThisWorkbook" if name_lower.startswith("sheet"): return 100, "文档模块 · 工作表" # FIX: use prefix/regex check instead of "frm" in name to avoid false positives if _FORM_PREFIXES.match(vba_filename) or "/3/" in stream_lower: return 3, "用户窗体 · UserForm" if "class" in name_lower or name_lower.endswith("cls") or "/2/" in stream_lower: return 2, "类模块 · Class Module" return 1, "标准模块 · Module"# ─────────────────────────────────────────────# VBA 提取# ─────────────────────────────────────────────def extract_vba_modules(excel_path: str) -> tuple[list[dict], str | None]: """提取所有 VBA 模块，返回 (modules, error_msg)""" if VBA_Parser is None: return [], "未安装 oletools，请运行：pip install oletools" try: parser = VBA_Parser(excel_path) except Exception as e: return [], f"无法解析文件：{e}" # FIX: use try/finally to guarantee parser.close() even on exceptions try: if not parser.detect_vba_macros(): return [], "未检测到 VBA 代码" modules = [] seen_names: dict[str, int] = {} # 处理同名模块 for filename, stream_path, vba_filename, vba_code in parser.extract_macros(): type_id, type_label = _guess_module_type(vba_filename, stream_path) # 同名模块加后缀 base = vba_filename if base in seen_names: seen_names[base] += 1 vba_filename = f"{base}_{seen_names[base]}" else: seen_names[base] = 0 modules.append({ "name": vba_filename, "type_id": type_id, "type_label": type_label, "code": vba_code.strip(), "stream_path": stream_path, }) finally: parser.close() return modules, None# ─────────────────────────────────────────────# 内容生成（单文件）# ─────────────────────────────────────────────def _header_info(excel_name: str, modules: list[dict]) -> dict: # FIX: single pass with Counter instead of 4 separate sum() iterations counts = Counter(m["type_id"] for m in modules) return { "excel_name": excel_name, "timestamp": datetime.now().strftime("%Y-%m-%d %H:%M:%S"), "total": len(modules), "userforms": counts[3], "doc_mods": counts[100], "std_mods": counts[1], "cls_mods": counts[2], }def build_single_md(excel_name: str, modules: list[dict]) -> str: h = _header_info(excel_name, modules) lines = [ f"# VBA 导出：{h['excel_name']}", "", f"| 项目 | 值 |", f"|------|---|", f"| 导出时间 | {h['timestamp']} |", f"| 模块总数 | {h['total']} |", f"| 标准模块 | {h['std_mods']} |", f"| 类模块 | {h['cls_mods']} |", f"| 文档模块 | {h['doc_mods']} |", f"| 用户窗体 | {h['userforms']} |", "", "---", "", ] for i, mod in enumerate(modules, 1): lines += [ f"## {i}. {mod['name']}", "", f"> **类型**：{mod['type_label']} ", f"> **流路径**：`{mod['stream_path']}`", "", "```vba", mod["code"], "```", "", "---", "", ] return "\n".join(lines)def build_single_txt(excel_name: str, modules: list[dict]) -> str: h = _header_info(excel_name, modules) SEP = "=" * 65 THIN = "-" * 65 lines = [ SEP, f" VBA 导出：{h['excel_name']}", f" 导出时间：{h['timestamp']}", f" 模块总数：{h['total']}（标准:{h['std_mods']} 类:{h['cls_mods']}" f" 文档:{h['doc_mods']} 窗体:{h['userforms']}）", SEP, "", ] for i, mod in enumerate(modules, 1): lines += [ THIN, f" [{i}/{h['total']}] 模块名：{mod['name']}", f" 类型：{mod['type_label']}", THIN, mod["code"], "", ] return "\n".join(lines)# ─────────────────────────────────────────────# 内容生成（多文件，每个模块单独）# ─────────────────────────────────────────────def build_module_md(excel_name: str, mod: dict, index: int, total: int, ts: str) -> str: # FIX: accept shared timestamp instead of calling datetime.now() per module return "\n".join([ f"# {mod['name']}", "", f"| 项目 | 值 |", f"|------|---|", f"| 来源文件 | {excel_name} |", f"| 模块类型 | {mod['type_label']} |", f"| 序号 | {index} / {total} |", f"| 导出时间 | {ts} |", "", "```vba", mod["code"], "```", "", ])def build_module_txt(excel_name: str, mod: dict, index: int, total: int, ts: str) -> str: # FIX: accept shared timestamp instead of calling datetime.now() per module SEP = "=" * 65 return "\n".join([ SEP, f" 模块名：{mod['name']}", f" 类型： {mod['type_label']}", f" 来源： {excel_name} [{index}/{total}]", f" 时间： {ts}", SEP, "", mod["code"], "", ])def build_module_bas(mod: dict) -> str: return mod["code"]# ─────────────────────────────────────────────# 文件名安全化# ─────────────────────────────────────────────# FIX: use str.translate for a single-pass replacement — faster and cleaner_UNSAFE_CHARS = str.maketrans(r'\/:*?"<>|', "_________")def safe_name(name: str) -> str: return name.translate(_UNSAFE_CHARS)# ─────────────────────────────────────────────# 导出逻辑# ─────────────────────────────────────────────def export_excel(excel_path: str, out_root: str, formats: list[str], multi_file: bool, log_fn) -> bool: """ 处理单个 Excel 文件。 formats: 包含 "md" / "txt" / "bas" 的列表 multi_file: True=多文件模式，False=单文件模式 log_fn: 回调函数，接收一行字符串 返回 True 表示成功 """ excel_name = os.path.splitext(os.path.basename(excel_path))[0] modules, error = extract_vba_modules(excel_path) if error: log_fn(f" ❌ {error}") return False if not modules: log_fn(" ⚠ 未发现任何 VBA 模块，跳过") return False uf_count = sum(1 for m in modules if m["type_id"] == 3) log_fn(f" ✓ 发现 {len(modules)} 个模块（含 {uf_count} 个 UserForm）") # FIX: compute shared timestamp once for all modules in this batch ts = datetime.now().strftime("%Y-%m-%d %H:%M:%S") try: if multi_file: sub_dir = os.path.join(out_root, safe_name(excel_name)) os.makedirs(sub_dir, exist_ok=True) total = len(modules) for idx, mod in enumerate(modules, 1): fname = safe_name(mod["name"]) for fmt in formats: filepath = os.path.join(sub_dir, f"{fname}.{fmt}") if fmt == "md": content = build_module_md(excel_name, mod, idx, total, ts) elif fmt == "txt": content = build_module_txt(excel_name, mod, idx, total, ts) else: # bas content = build_module_bas(mod) with open(filepath, "w", encoding="utf-8") as f: f.write(content) log_fn(f" 📁 多文件已保存至：{sub_dir} （{len(modules)} 个模块 × {len(formats)} 种格式）") else: for fmt in formats: filepath = os.path.join(out_root, f"{safe_name(excel_name)}.{fmt}") if fmt == "md": content = build_single_md(excel_name, modules) elif fmt == "txt": content = build_single_txt(excel_name, modules) # bas 在单文件模式下被禁用，不会出现 with open(filepath, "w", encoding="utf-8") as f: f.write(content) log_fn(f" 📄 已保存：{os.path.basename(filepath)}") except Exception as e: log_fn(f" ❌ 保存失败：{e}") return False return True# ─────────────────────────────────────────────# UI# ─────────────────────────────────────────────class App: PAD = {"padx": 10, "pady": 5} def __init__(self, root: tk.Tk): self.root = root self.root.title("VBA 导出工具") self.root.geometry("700x600") self.root.minsize(600, 500) # 状态变量 self.file_list: list[str] = [] self.output_dir = tk.StringVar() self.fmt_md = tk.BooleanVar(value=True) self.fmt_txt = tk.BooleanVar(value=False) self.fmt_bas = tk.BooleanVar(value=False) self.org_mode = tk.StringVar(value="single") self._build_ui() self._sync_bas_lock() # ── 界面构建 ────────────────────────────── def _build_ui(self): p = self.PAD # ── 文件列表区域 ── file_frame = ttk.LabelFrame(self.root, text="Excel 文件（支持多选）", padding=8) file_frame.pack(fill="both", expand=False, **p) list_area = ttk.Frame(file_frame) list_area.pack(fill="both", expand=True) self.listbox = tk.Listbox(list_area, height=6, selectmode=tk.EXTENDED, font=("Consolas", 9)) sb = ttk.Scrollbar(list_area, orient="vertical", command=self.listbox.yview) self.listbox.configure(yscrollcommand=sb.set) self.listbox.pack(side="left", fill="both", expand=True) sb.pack(side="left", fill="y") btn_col = ttk.Frame(file_frame) btn_col.pack(side="right", fill="y", padx=(8, 0)) ttk.Button(btn_col, text="添加文件", width=12, command=self._add_files).pack(pady=2) ttk.Button(btn_col, text="移除选中", width=12, command=self._remove_selected).pack(pady=2) ttk.Button(btn_col, text="清空列表", width=12, command=self._clear_files).pack(pady=2) # ── 输出目录 ── dir_frame = ttk.LabelFrame(self.root, text="输出目录", padding=8) dir_frame.pack(fill="x", **p) ttk.Entry(dir_frame, textvariable=self.output_dir).pack( side="left", fill="x", expand=True) ttk.Button(dir_frame, text="浏览", command=self._browse_dir).pack( side="left", padx=(6, 0)) # ── 导出选项 ── opt_frame = ttk.LabelFrame(self.root, text="导出选项", padding=8) opt_frame.pack(fill="x", **p) # 格式行 fmt_row = ttk.Frame(opt_frame) fmt_row.pack(fill="x", pady=3) ttk.Label(fmt_row, text="输出格式：", width=11, anchor="w").pack(side="left") self.cb_md = ttk.Checkbutton(fmt_row, text=".md（推荐，AI 友好）", variable=self.fmt_md, command=self._sync_bas_lock) self.cb_md.pack(side="left", padx=6) self.cb_txt = ttk.Checkbutton(fmt_row, text=".txt", variable=self.fmt_txt, command=self._sync_bas_lock) self.cb_txt.pack(side="left", padx=6) self.cb_bas = ttk.Checkbutton(fmt_row, text=".bas", variable=self.fmt_bas, command=self._sync_bas_lock) self.cb_bas.pack(side="left", padx=6) # 文件组织行 org_row = ttk.Frame(opt_frame) org_row.pack(fill="x", pady=3) ttk.Label(org_row, text="文件组织：", width=11, anchor="w").pack(side="left") self.rb_single = ttk.Radiobutton( org_row, text="单文件（所有模块合并）", variable=self.org_mode, value="single", command=self._sync_bas_lock) self.rb_single.pack(side="left", padx=6) self.rb_multi = ttk.Radiobutton( org_row, text="多文件（每模块独立保存）", variable=self.org_mode, value="multi", command=self._sync_bas_lock) self.rb_multi.pack(side="left", padx=6) # 提示标签 self.hint_label = ttk.Label(opt_frame, text="", foreground="#888888") self.hint_label.pack(anchor="w", padx=2) # ── 开始按钮 ── self.btn_export = ttk.Button(self.root, text="▶ 开始导出", command=self._start_export) self.btn_export.pack(pady=8, ipadx=20, ipady=4) # ── 日志区域 ── log_frame = ttk.LabelFrame(self.root, text="日志", padding=8) log_frame.pack(fill="both", expand=True, **p) self.log_box = tk.Text(log_frame, state="disabled", wrap="word", font=("Consolas", 9), background="#1e1e1e", foreground="#d4d4d4", insertbackground="white") log_sb = ttk.Scrollbar(log_frame, orient="vertical", command=self.log_box.yview) self.log_box.configure(yscrollcommand=log_sb.set) self.log_box.pack(side="left", fill="both", expand=True) log_sb.pack(side="left", fill="y") # 彩色标签 self.log_box.tag_configure("ok", foreground="#6abe6a") self.log_box.tag_configure("err", foreground="#f44747") self.log_box.tag_configure("warn", foreground="#ddbf5e") self.log_box.tag_configure("title", foreground="#9cdcfe") # ── 控件逻辑 ───────────────────────────── def _sync_bas_lock(self): """若勾选 .bas 则强制多文件并禁用单文件选项""" if self.fmt_bas.get(): self.org_mode.set("multi") self.rb_single.configure(state="disabled") self.hint_label.configure( text=" ℹ .bas 格式仅支持多文件模式（每模块独立 .bas 文件）") else: self.rb_single.configure(state="normal") self.hint_label.configure(text="") def _add_files(self): paths = filedialog.askopenfilenames( title="选择 Excel 文件", filetypes=[ ("Excel 含宏文件", "*.xlsm *.xlam *.xls"), ("所有 Excel", "*.xlsx *.xlsm *.xls *.xlam"), ("所有文件", "*.*"), ], ) if not paths: return # ✅ 同步第一个文件目录 first_path = os.path.abspath(paths[0]) d = os.path.dirname(first_path) self.output_dir.set(d) added = 0 for p in paths: if p not in self.file_list: self.file_list.append(p) self.listbox.insert("end", os.path.basename(p)) added += 1 if added: self._log(f"已添加 {added} 个文件，共 {len(self.file_list)} 个", "title") def _remove_selected(self): for i in reversed(self.listbox.curselection()): self.listbox.delete(i) self.file_list.pop(i) def _clear_files(self): self.listbox.delete(0, "end") self.file_list.clear() def _browse_dir(self): d = filedialog.askdirectory(title="选择输出目录") if d: self.output_dir.set(d) # ── 日志 ────────────────────────────────── def _log(self, msg: str, tag: str = ""): def _write(): self.log_box.configure(state="normal") self.log_box.insert("end", msg + "\n", tag) self.log_box.see("end") self.log_box.configure(state="disabled") self.root.after(0, _write) # ── 导出入口 ────────────────────────────── def _start_export(self): if not self.file_list: messagebox.showwarning("提示", "请先添加 Excel 文件") return if not self.output_dir.get(): messagebox.showwarning("提示", "请选择输出目录") return if not (self.fmt_md.get() or self.fmt_txt.get() or self.fmt_bas.get()): messagebox.showwarning("提示", "请至少选择一种输出格式") return self.btn_export.configure(state="disabled", text="导出中…") threading.Thread(target=self._run_export, daemon=True).start() def _run_export(self): # FIX: cleaner list comprehension without extra parentheses formats = [fmt for fmt, var in [("md", self.fmt_md), ("txt", self.fmt_txt), ("bas", self.fmt_bas)] if var.get()] multi = self.org_mode.get() == "multi" out_root = self.output_dir.get() files = list(self.file_list) self._log(f"\n{'─'*55}", "title") self._log(f" 开始导出 {len(files)} 个文件 格式:{formats} " f"模式:{'多文件'if multi else'单文件'}", "title") self._log(f"{'─'*55}", "title") success = 0 for i, excel_path in enumerate(files, 1): fname = os.path.basename(excel_path) self._log(f"\n[{i}/{len(files)}] {fname}", "title") # FIX: pass self._log directly instead of wrapping in a lambda ok = export_excel(excel_path, out_root, formats, multi, log_fn=self._log) if ok: success += 1 self._log(" ✅ 完成", "ok") else: self._log(" ❌ 跳过", "err") self._log(f"\n{'─'*55}", "title") tag = "ok" if success == len(files) else "warn" self._log(f" 全部完成：成功 {success} / {len(files)} 个文件", tag) self._log(f"{'─'*55}\n", "title") self.root.after(0, lambda: self.btn_export.configure( state="normal", text="▶ 开始导出"))# ─────────────────────────────────────────────# 主程序# ─────────────────────────────────────────────if __name__ == "__main__": if VBA_Parser is None: import sys print("请先安装依赖：pip install oletools") sys.exit(1) root = tk.Tk() App(root) root.mainloop()
10个月宝宝每天需要喝多少奶粉？