用中文脚本解释器操控Excel：从业务DSL设计到openpyxl踩坑实录

这个系列写到第三篇了。前两篇我们把自定义中文脚本解释器的底层骨架搭了出来分词器、语法树、表达式求值、if/loop流程控制跑通定义 变量 1这类基础指令已经不成问题。但解释器这种东西不挂上真实业务场景就是个玩具。所以这一篇我把它拉到最日常的办公场景——用自己写的中文脚本解释器去操作Excel目标是让业务同事直接写中文描述脚本自己去打开工作簿、逐行读数据、按条件筛选、汇总结果到新文件。这篇不打算从头讲openpyxl怎么用而是讲我的解释器怎么把打开工作簿 xxx这类中文命令翻译成底层操作以及在这个过程中实打实踩过的坑。适合两类人看一类是想给团队做个零代码工具、正在纠结方案的技术同学另一类是已经接触过用脚本处理Excel、想进一步理解自定义语言到底怎么落地的朋友。如果你正打算自己动手写个内部DSL这篇里的设计取舍和踩坑记录应该能帮你省下不少时间。1. 这一版的设计思路把解释器做成业务DSL1.1 为什么我不做中文版Python最省事的方案其实是把解释器做成一个语法简化版的Python然后内置openpyxl模块让大家这样写book openpyxl.load_workbook(...)。我一开始确实也这么想过后来做原型的时候果断放弃了。原因特别简单这个工具的目标用户是完全没有编程概念的业务人员他们脑子里没有对象方法调用模块导入这一整套东西。让他们理解什么叫book.active比让他们学会用中文写选择工作表要难十倍。所以我把解释器定位成业务DSL而不是通用语言。DSL的意思是只解决一个领域的问题语法足够窄但在这个领域里面表达力要足够强。对Excel自动化的场景来说最高频的操作其实就那么几类打开文件、切换工作表、读写单元格、遍历行、按条件筛选、求个总数。把这些做成固定指令中文脚本读起来就非常自然。这个设计哲学很关键别想着把解释器做成万能的那会掉进再造一套Python的无底洞。我见过有人做类似的项目最后语法膨胀到几百个关键字连自己都维护不过来。真正能落地的内部工具指令集一定要克制能覆盖80%的日常需求就够了剩下的边缘场景让人手动处理或者留一套插件机制兜底。1.2 指令体系怎么定从业务场景倒推我在动手写执行器之前先列了一堆真实的办公需求然后从里面抽象指令。最常见的四类需求是这样的第一是合并报表比如分公司发过来十几个Excel要把里面的数据汇总成一个总表。第二是条件筛选从一张几千行的大表里把满足某些条件的记录捞出来。第三是简单统计比如按区域汇总销售额、算个平均值。第四是把处理结果写到一个全新的文件里还要加个标题行。由这四类需求倒推出了一套指令集分成四个层级层级中文指令对应功能文件级打开工作簿、创建输出工作簿、保存工作簿、关闭工作簿管理Excel文件生命周期工作表级选择工作表、遍历工作簿中的工作表、新建工作表切换和枚举页面单元格级读取单元格、写入单元格、读取区域、写入行数据读写流程控制逐行读取、如果/否则、定义变量、设置变量循环和条件逻辑再加一组内置函数求和、求平均、统计非空数。不要小看这几条指令把它们组合起来前面说的四类需求基本都能覆盖。我做了一张对照表能让团队同学直观感受到中文指令比传统API好用在哪需求openpyxl常规写法中文脚本打开文件load_workbook(a.xlsx)打开工作簿 a.xlsx读单元格ws[B2].value单元格(B2)遍历行for row in ws.iter_rows(min_row2)逐行读取 当前表 从第2行 到 第100行保存文件wb.save(out.xlsx)保存工作簿1.3 架构上这一版改了什么前两篇写的分词器和语法解析器基本没有大改中文关键字本来就容易识别新增关键字只需要加到分词表的配置里就行。这一版的改动集中在Executor执行层我新增了一个ExcelContext会话上下文对象它持有当前打开的工作簿、输出工作簿、当前工作表、当前行号还有用户自定义变量表。为什么一定要加这个上下文因为中文脚本的语法高度依赖语境。用户写写入单元格(B3, 100)的时候他不会说往哪个工作簿的哪个工作表写解释器必须记住前面语境里打开的是哪个文件、当前选中的是哪张表。这种省略主语的表达习惯在自然语言里是优势但在编程语言里就得靠上下文对象来兜底。整个执行流程是这样的用户输入一段中文脚本分词器切成token流语法解析器生成ASTExecutor拿到AST之后逐节点执行。Executor识别出当前节点是哪类指令就调用ExcelContext上的对应方法最终由这些方法去驱动openpyxl完成真正的Excel操作。核心思想就是把用户说话的习惯和底层库的方法签名彻底解耦。2. 核心执行器从中文指令到Excel调用的翻译2.1 会话状态类是怎么设计的ExcelContext是整个解释器的心脏。我先给出核心代码再解释每个字段的用途。class ExcelContext: def __init__(self): self.input_wb None # 当前打开的工作簿对象 self.output_wb None # 输出工作簿对象 self.current_ws None # 当前选中的工作表 self.row_var 0 # 行号变量供 {行号} 模板替换 self.sheet_var # 工作表名变量供 {工作表名} 替换 self.var_table {} # 用户自定义变量表 self.mem_cache None # 预加载的内存二维数组这个类的必要性在于解释器执行指令时需要知道现在说的是哪张表。选择工作表 华东执行完后续所有读操作都应该作用在华东这张表上直到再次切换。输出工作簿同理创建输出工作簿之后所有写入操作都进这个新文件。这些状态散落在各个处理方法里是搞不干净的集中到一个上下文对象里最清爽。还有一个隐藏设计mem_cache。后面实战环节我会专门讲性能问题这里先埋个伏笔。对于大型工作表我让解释器支持预加载模式一次性把整张表的数据读进内存二维数组后面所有单元格(A1)读取都从内存取不再走openpyxl的逐格访问速度差别非常大。2.2 关键指令的处理函数每条中文指令在Executor里对应一个处理函数。这里挑三个最有代表性的拆开讲。第一个是打开工作簿def cmd_open(self, args): path resolve_path(args.workbook_path) self.input_wb load_workbook(path, data_onlyTrue) self.current_ws self.input_wb.active if self.debug: log(f[执行] 打开工作簿 {path}) log(f[执行] 发现工作表: {self.input_wb.sheetnames})注意data_onlyTrue这个参数我在这里栽过大跟头。它表示加载工作簿时读取单元格的缓存值而不是公式字符串后面第4章节会展开讲。默认打开后自动选择第一个工作表符合用户打开就干活的直觉。第二个是写入单元格def cmd_write_cell(self, sheet_name, cell_name, value): ws self._get_sheet(sheet_name) ws[cell_name] value_get_sheet会先判断传入的表名在不在当前工作簿里不在就尝试用之前遍历时记住的sheet名再不行就抛一个中文报错工作表 xxx 不存在请先选择正确的工作表。这个报错信息设计也很重要我后面会细说。第三个是逐行读取循环这是最复杂的单独用一个小节讲。2.3 行号变量和模板替换的实现逐行读取 当前表 从第2行 到 第200行这段指令难点在于循环体里的单元格(A{行号})要跟着当前行号变化。我的方案非常朴素模板字符串替换。def resolve_template(self, text): if {行号} in text: text text.replace({行号}, str(self.row_var)) if {工作表名} in text: text text.replace({工作表名}, str(self.sheet_var)) for key, val in self.var_table.items(): text text.replace({ key }, str(val)) return textresolve_template会在每次读取单元格参数之前被调用。{行号}被替换成当前行号数字{工作表名}被替换成当前遍历到的工作表名字用户自定义变量也可以通过{输出行}这种形式参与。比如写入单元格(汇总, A{输出行}, 区域)执行前先替换成写入单元格(汇总, A2, 华东)一条指令就落到了实际位置。这个模板替换虽然听着不够高级但在业务DSL里非常够用而且好调试。出问题的时候一眼就能看出替换结果对不对不用去追什么复杂的作用域链。2.4 循环体和条件判断的执行逐行读取的核心逻辑其实是一个解释器内部的循环def cmd_iter_rows(self, start_row, end_row, body_ast): for r in range(start_row, end_row 1): self.row_var r execute_block(body_ast)execute_block按顺序执行循环体的AST节点。每次迭代先把row_var更新为当前行再执行整段循环体。循环体里可能嵌套如果条件和多次单元格读写解释器会递归处理。条件判断如果 销售额 ! 空里的空是我定义的一个特殊值对应openpyxl读出的None。数值比较时要注意类型Excel里单元格读出的数值可能是int也可能是float所以在内置比较函数里我做了一层类型规整两头都是数字就统一转成float再比避免1和1判断不相等这种隐蔽问题。内置聚合函数同样走解析和映射。比如求和def builtin_sum(self, range_str): total 0 for row_cells in self.current_ws[range_str]: for cell in row_cells: if isinstance(cell.value, (int, float)): total cell.value return total这里故意跳过非数字类型的值。因为Excel区域里常有空单元格、文本、甚至错误值只有数字才参与求和这个逻辑符合业务直觉。3. 完整实操跑一个真实的分公司报表汇总3.1 场景和报表结构我拿一个非常典型的场景来做完整演示月底分公司发来一个区域报表.xlsx里面按大区分了多个sheet华东、华北、华南各一张另有一张说明表。每张数据表的列结构是三列A列是店铺名B列是销售额C列是店长。现在的需求是把所有大区的店铺和销售额汇总到一个新工作簿里形成一张总表并且算出整体销售额合计。拿到这种需求如果让业务同事用Excel手工操作无非是挨个sheet复制粘贴几百行数据搞一下午。用我写的中文脚本解释器只需一段文字描述逻辑。3.2 中文脚本全文下面是脚本完整内容打开工作簿 区域报表.xlsx 创建输出工作簿 区域汇总.xlsx 选择输出工作表 汇总 写入单元格(汇总, A1, 区域) 写入单元格(汇总, B1, 店铺) 写入单元格(汇总, C1, 销售额) 定义 输出行 2 遍历工作簿中的工作表: 选择工作表 {工作表名} 如果 {工作表名} ! 说明: 定义 区域 {工作表名} 逐行读取 当前表 从第2行 到 第200行: 定义 店铺 单元格(A{行号}) 定义 销售额 单元格(B{行号}) 如果 销售额 ! 空 且 店铺 ! 空: 写入单元格(汇总, A{输出行}, 区域) 写入单元格(汇总, B{输出行}, 店铺) 写入单元格(汇总, C{输出行}, 销售额) 设置 输出行 输出行 1 写入单元格(汇总, E1, 总销售额) 写入单元格(汇总, F1, 求和(C2:C{输出行})) 保存工作簿读起来基本就是一段带缩进的自然语言。这个脚本在语法设计上有个小心思遍历工作簿中的工作表和选择工作表 {工作表名}配合使用循环内部自动把{工作表名}替换成当前遍历到的名字所以区域列能正确地填上华东华北这些来源。3.3 解释器一步步执行时发生了什么我开着DEBUG模式跑了一遍日志输出是这样的[执行] 打开工作簿 区域报表.xlsx - 发现工作表: [华东, 华北, 华南, 说明] [执行] 创建输出工作簿 区域汇总.xlsx [执行] 写入单元格(汇总, A1, 区域) [执行] 写入单元格(汇总, B1, 店铺) [执行] 写入单元格(汇总, C1, 销售额) [执行] 遍历工作簿中的工作表 - 当前: 华东 [执行] 选择工作表 华东 [执行] 逐行读取 第2行 到 第200行 [执行] 第2行: 店铺南京东路店, 销售额32000 [执行] 写入单元格(汇总, A2, 华东) [执行] 写入单元格(汇总, B2, 南京东路店) [执行] 写入单元格(汇总, C2, 32000) ...注意几个执行细节。第一选择工作表 华东这一句没有产生任何可见的Excel操作它只改了上下文对象里的current_ws指针但后续所有读操作都指向正确的表。第二逐行读取时条件销售额 ! 空过滤掉了空行原来报表里有些行只有店名没有销售额这些行被自动跳过。第三求和(C2:C{输出行})这行模板替换是在运行时做的执行到它的时候输出行已经变成了汇总表的末尾行号加1所以求和区域是动态的正好覆盖所有写入的数据行。最终生成的区域汇总.xlsx效果如下区域店铺销售额华东南京东路店32000华东徐家汇店28500华北三里屯店41000.........总销售额208500整个处理过程大约耗时3秒数据量大概是三个工作表各150行。3.4 调试脚本的三个小技巧第一个技巧是给解释器加一个-debug启动参数。我之前说过的DEBUG模式不只是打印指令还会把每次模板替换的结果打出来。比如单元格(A{行号})替换成单元格(A42)这一条日志能让你立刻定位是脚本写错了还是解释器替换错了。第二个技巧是把中间状态的Excel文件保留下来。我在创建输出工作簿之后先执行一次保存工作簿再把完整流程跑完这样即使后续崩了也能打开半成品文件看到哪一步出了岔子。虽然会多生成一个临时文件但排查问题时作用巨大。第三个技巧是断言指令。我在DSL里加了一条断言 条件, 错误提示指令专门用来在脚本里校验前置条件。比如汇总前先断言输出行 - 2 读取总数能提前发现问题而不是等最后出来的Excel数据对不上再回头找。4. 写Excel自动化最容易踩的五个坑4.1 公式单元格读不出值的问题第一次跑真实报表时我发现脚本读到的销售额全是None但用户用Excel打开明明看得到数字。排查到最后发现是data_only参数的坑。Excel文件在磁盘上会保存两样东西公式本身比如SUM(B2:B10)以及这个公式最近一次计算的结果缓存。用data_onlyFalse读取拿到的是公式字符串用data_onlyTrue读取拿到的是缓存值。这里的坑在于如果这个Excel文件从来没被Excel或WPS软件打开计算过缓存可能压根不存在data_onlyTrue读出来就是None。比如程序批量生成的报表就容易出现这种情况。我的解决方案是在解释器里加了一个主动检查打开工作簿后读前十个单元格如果发现全部是None但实际有公式就自动调用一次底层重算。Windows环境下我用的是Excel COM接口触发重算其他环境则提示用户请先打开文件按一次保存。这不是最优解但胜在简单可靠。4.2 日期在Excel里根本不是字符串Excel的日期本质上是一个序列号比如2024年5月1日实际存储的是45478这个数字单元格格式让它显示成日期而已。用openpyxl读取格式正确的单元格拿到的是Python的datetime对象如果你往单元格里写入一个字符串2024-05-01Excel只会把它当文本存下来没法参与日期排序和计算。我在中文脚本里内置了一个格式化日期函数统一把日期值转成YYYY-MM-DD字符串再写入汇总表。识别逻辑很简单先从单元格读值如果是datetime对象就格式化如果是数字就按Excel的日期序列号换算如果是字符串就原样保留。这样处理之后写出去的值在Excel里既能看又能算。4.3 合并单元格制造幽灵空值有一个报表纵向合并了很多单元格看起来一整列都有值但逐行读取时只有合并区域左上角那个格子有值其他格子的值是None。这导致我统计店铺数的时候总数偏小了一大截当时差点以为是数据源本身缺数据。openpyxl有一个merged_cells.ranges属性能从工作表里拿到所有合并区域的范围列表。我的处理办法是在逐行读取开始时扫描一次合并区域建立一个从被合并的格子坐标到左上角值的映射表。后续读取到某个单元格返回None时先查这个映射表如果命中就补上左上角的值。这个功能默认是关闭的通过开启合并单元格补全指令手动控制避免在不需要的场景里引入误读。4.4 几万行数据时逐格读写慢到崩溃用openpyxl逐单元格读写数据量小的时候没感觉一旦到几千行、几十列性能衰减非常明显。我曾经处理过一个3000行20列的报表逐格读逐格写到新表跑了将近30秒。这个速度业务方完全不能接受。优化思路有两个。读的方向我让解释器支持预加载工作表指令先把整个工作表通过iter_rows(values_onlyTrue)一次性读进mem_cache二维数组之后所有单元格()操作都改成内存数组访问3000行数据毫秒级完成读取。写的方向尽量减少逐格写入新增了一个写入行指令用worksheet.append()整行追加数据配合mem_cache同样的任务从30秒优化到了5秒以内。4.5 中文文件名和CSV编码的隐形刺客Windows系统下中文文件名本身问题不大但如果脚本文件保存成了UTF-8编码而目标文件路径里有中文在部分Python环境下弹路径报错。我统一用pathlib.Path处理路径然后把脚本文件本身也强制以UTF-8读取基本消灭了这类问题。CSV文件的编码则更隐蔽。业务同事用Excel导出的CSV通常是GBK或GB2312编码直接用UTF-8打开全是乱码。我写了一个编码探测器先读文件头几个字节有BOM就走utf-8-sig否则尝试用gbk解码解码失败再回退到utf-8。导出的CSV统一写成utf-8-sig因为Excel双击打开utf-8无BOM的CSV会把中文解释成乱码加上BOM就一切正常。5. 一些性能参数和经验参数如果你也打算做类似工具有些踩过的性能边界可以直接拿去参考。我实测过一个基准openpyxl写一个单元格大约0.1到0.3毫秒但这是纯写操作如果每个单元格还带着样式写入单格可能超过1毫秒。一万个常规单元格的纯写入量级在1秒左右但如果循环里还调用了select切表、查询合并区域整体可能翻好几倍。所以我在文档里给团队定了一条规则超过2000行的数据写入禁止使用写入单元格逐格循环必须走写入行指令。超过5000行的数据读取必须先执行预加载工作表。这个规则听起来很死板但能避免绝大多数性能崩盘。还有一个容易被忽略的参数是工作表的最大行数限制。Excel单表最多1048576行听起来很多但如果你在逐行读取时把结束行写成了9999999openpyxl不会报错只会慢悠悠地遍历到实际范围浪费时间。所以在解释器层我加了行号上限钳制超过1048576的结束行自动截断加一条警告日志。个人经验里还有一个很重要的点解释器的报错信息一定要用中文业务视角。用户写错指令系统提示第3行第5个token附近存在语法错误完全没用他们只想知道表格里没有华东这个工作表。所以我把所有异常统一包装成FriendlyError提示语格式是做xxx时失败原因是xxx建议xxx。比如打开工作簿 123.xlsx 时失败原因是文件没找到请检查路径是否写对。这个自定义错误处理让我节省了无数答疑时间强烈建议做工具的时候把用户体验落到报错上。回头再看这个项目中文脚本解释器值钱的地方从来不是语法解析有多精巧而是把Excel那一堆反人性的行为全部封装起来了。我翻看自己写的第一版代码大量时间花在解决日期序列号、合并单元格、公式缓存值这些脏活上。如果你的目标也是给团队做一个内部自动化工具我最大的建议是先去摸清底层库的边界和脾气再来谈语言设计顺序反了后续维护会相当痛苦。