本文介绍使用 openpyxl 直接操作 xlsm 文件的方法，解决 pandas.to_excel 无法保留 vba 宏的问题，实现对数千个含宏 excel 文件中特定列字符串的精准查找与替换。
本文介绍使用 openpyxl 直接操作 xlsm 文件的方法，解决 pandas.to_excel 无法保留 vba 宏的问题，实现对数千个含宏 excel 文件中特定列字符串的精准查找与替换。
在处理大量含宏的 .xlsm 文件（如财务模型、自动化报表）时，一个常见痛点是：
用 pandas 读写 Excel 会丢失 VBA 宏
——即使指定 .xlsm 扩展名，to_excel() 实际生成的是无宏的 .xlsx 格式（仅文件名欺骗），导致宏功能失效。根本原因在于 pandas 的 ExcelWriter 默认不传递或持久化 vba_archive，且 mode='a' 模式下对已存在 .xlsm 的追加写入并不真正“保留原始宏结构”。
✅ 正确解法是绕过 pandas DataFrame 的整表写入逻辑，
直接使用 openpyxl 加载、定位、修改单元格
。openpyxl 支持 keep_vba=True 参数，可完整加载 VBA 项目（包括模块、窗体、ThisWorkbook 等），并在保存时原样嵌入。
以下为生产级可用的批量处理脚本：
?
关键注意事项
：
不要使用 read_only=True
：它会禁用单元格写入能力，且 keep_vba=True 在只读模式下无效；
避免 ws.max_row 误判
：Excel 中空行可能导致 max_row 过大；若数据量极大或格式不规范，建议先用 ws.iter_rows() + values_only=True 配合 next(ws.iter_rows()) 获取首行确认结构，或限定合理行数范围；
字符串替换策略
：示例中采用 in 检查 + str.replace()，适用于子串替换；如需精确匹配整个单元格内容，请改用 cell.value == target；
性能优化
：对 3000+ 文件，建议添加 concurrent.futures.ThreadPoolExecutor 并行处理（注意 openpyxl 非线程安全，需每个线程独立加载/保存）；
备份先行
：正式运行前务必对源文件夹执行完整备份，或在脚本中自动复制 .bak 副本（shutil.copy2(file_path, file_path.with_suffix('.xlsm.bak'))）。
?
总结
：当核心需求是「保留宏 + 精准修改少量单元格」时，openpyxl 原生操作远比绕道 pandas 更可靠、更轻量、更可控。放弃“先转 DataFrame 再写回”的思维惯性，直击 Excel 对象模型，才是处理 XLSM 自动化的专业路径。
import openpyxl
import os
from pathlib import Path
# 配置项（按需修改）
FIND_VAL = ["mystring", "OLD_TEXT"] # 支持多个待替换字符串（大小写敏感）
REPLACE_VAL = "NEW_TEXT"
SHEET_NAME = "Planning" # 目标工作表名
COLUMN_INDEX = 5 # 目标列索引（1-based，即第5列，对应 Excel 中的 E 列）
FILES_DIR = Path("path/to/your/xlsm/files") # XLSM 文件所在目录
xlsm_files = list(FILES_DIR.glob("*.xlsm"))
for file_path in sorted(xlsm_files):
try:
# ✅ 关键：启用 keep_vba=True，确保宏被加载
wb = openpyxl.load_workbook(filename=file_path, read_only=False, keep_vba=True)
ws = wb[SHEET_NAME]
modified_count = 0
# 遍历目标列所有非空行（推荐用 iter_rows 提升性能）
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=COLUMN_INDEX, max_col=COLUMN_INDEX, values_only=False):
cell = row[0] # 因为只取一列，所以 row 是单元素元组
if cell.value is not None:
# 字符串匹配（支持列表中的任意值，忽略大小写）
if isinstance(cell.value, str):
for target in FIND_VAL:
if target.lower() in cell.value.lower():
cell.value = cell.value.replace(target, REPLACE_VAL)
modified_count += 1
break # 替换一次即跳出，避免重复替换
if modified_count > 0:
wb.save(file_path) # ✅ 直接保存回原文件，宏完整保留
print(f"✓ 已更新 {file_path.name}，共替换 {modified_count} 处")
else:
print(f"→ {file_path.name} 未找到匹配项")
wb.close()
except KeyError as e:
print(f"⚠ 跳过 {file_path.name}：工作表 '{SHEET_NAME}' 不存在")
except Exception as e:
print(f"❌ 处理失败 {file_path.name}：{e}")