接过一个统计报表的活儿要在一堆 Excel 里提取数据并汇总。刚开始我图省事直接用 pandas 一把梭后来发现需求越来越细要判断单元格格式、要区分公式和结果、要处理合并单元格和文本型数字还得在大文件场景下控内存。试了一圈下来真正让我用得顺手、能掌控到单元格级别的还是Python 读取 Excel时的老牌库openpyxl。这篇文章不是官方文档的翻译而是我在实际项目里反复折腾后整理出的使用心得包括 openpyxl 和其他方案怎么选、读取时最容易被忽略的坑、从单格取值到批量遍历的写法差异、数据类型的隐性陷阱以及大文件的性能处理。适合刚开始接触 openpyxl 的读者也适合已经用了一段时间但想排查隐患的人。1. 为什么最终选了 openpyxl几种读取方案的边界1.1 纯 Python 环境下的选型对比先说结论如果在服务器上跑脚本且目标文件是.xlsxopenpyxl 基本是最稳的选择。它不依赖本机安装 Office跨平台表现一致而且能直接操作单元格对象本身不只是拿数据。我用过的其他方案各有各的边界方案擅长短板pandas.read_excel快速把表格变成 DataFrame做统计分析底层依赖 openpyxl/xlrd但封装之后丢掉了很多单元格级信息也不方便改样式xlrd老牌库新版只支持 .xls.xlsx 的支持停留在旧版本项目基本处于维护状态xlwings操控本机 Excel可以跑 VBA、做交互必须装 Excel不适合无图形界面的服务器openpyxl读写 .xlsx/.xlsm能处理单元格样式、公式、合并单元格不支持 .xls需要转换你可能发现我列了 pandas因为很多人会问“有 pandas 为什么还要专门学 openpyxl”。我的体会是如果你只要一个统计结果pandas 确实快但如果要精确判断某个单元格是不是公式、读取合并单元格的边界、拿到日期单元格的真实类型、或者往模板里回填数据并保留原格式最终还是要回到 openpyxl。pandas 内部的 Excel 读取引擎本质上也是它。1.2 openpyxl 做不到的事也得说清楚它的边界避免一上来踩坑不支持老版 .xls 文件。遇到 .xls 只能先用其他工具转换我是用 LibreOffice 批量转的因为目标环境装不了 Office这个方案实测还算稳。不负责公式计算。openpyxl 读公式单元格时拿到的是公式字符串或 Excel 保存时留下的缓存值它不会自己重新算一遍。对图片、图表、透视表的支持有限。它主要操作的是单元格层能读取图片对象的存在与否但不做图片内容解析。理解了这些边界后面的使用姿势就清晰了openpyxl 适合做“单元格级的数据搬运工”。2. 动手读之前先搞清 Excel 的底层结构2.1 xlsx 文件其实是个压缩包这一点很多人没意识到。xlsx 本质上是一个 zip 包里面装着多个 XML 文件分别描述工作簿、工作表、样式、共享字符串等。openpyxl 做的就是把这一堆 XML 解析成 Python 对象。理解这个底层事实很多玄学问题就有了解释。比如为什么 read_only 模式能省内存因为它惰性加载不把所有 XML 一次性构建成对象。为什么有时候文件明明看起来很小读出来却慢因为里面可能有大量样式定义或共享字符串表体积不大但解析成本高。2.2 对象模型Workbook、Worksheet、Cellopenpyxl 的核心模型很简单就三层Workbook对应整个工作簿相当于一个 Excel 文件。Worksheet对应一个工作表就是左下角那些 Sheet。Cell对应一个单元格你可以通过坐标或行列索引去拿它。代码里最常见的启动方式是这样from openpyxl import load_workbook wb load_workbook(sales.xlsx, data_onlyTrue) ws wb[Sheet1] print(ws.max_row) # 最大行数 print(ws.max_column) # 最大列数 print(ws[A1].value) # 单元格的值这里有个我一开始没注意的点wb[Sheet1]和wb.active的区别。active只是默认显示的那个工作表不一定就是第一个。如果你写脚本处理别人给的表格对方打开文件时停在哪一页active就是哪一页。所以只要是明确目标建议用工作表名去拿而不是active。2.3 max_row 和 max_column 为什么会“虚高”这是读取阶段最容易出现的性能陷阱。max_row和max_column是 openpyxl 根据工作表 XML 中记录的维度算出来的。问题是这个维度并不代表“最后一行有数据”而是代表“这个区域被定义过”。什么意思呢如果一个表格曾经在第 999 行填过内容后来删了但某些单元格保存过格式那么 openpyxl 读出来的max_row可能还是 999。此时你写循环遍历到 999就会发现前面几行是数据后面全是None白跑一大圈。我处理这个问题的思路是定位到最后几行然后自己做截断。比如读取时只取前几百行或者在循环里统计连续空行数量连续空行超过一定阈值就主动 break。后面实战部分我会给具体写法。3. load_workbook 最关键的 data_only 和 read_only3.1 data_only你要公式还是结果如果你在读别人发的报表单元格里可能写的是SUM(B2:B10)这类公式。默认情况下openpyxl 不做任何计算也不读取公式算出来的结果它把公式字符串原样给你。# 不传 data_only读出来的是公式字符串 wb_formula load_workbook(formula.xlsx, data_onlyFalse) ws wb_formula[Sheet1] print(ws[B2].value) # SUM(B2:B10)要想直接拿结果需要在加载时设置data_onlyTrue。它读的是 Excel 保存文件时缓存下来的计算结果wb_value load_workbook(formula.xlsx, data_onlyTrue) ws wb_value[Sheet1] print(ws[B2].value) # 180 之类的数值有个隐藏问题必须提醒你如果这个文件是用 openpyxl 新建出来、写下公式后又从来没有被 Excel 打开并保存过那文件里不存在缓存结果data_onlyTrue会读到None。所以当你发现所有公式单元格都是None时先确认一下文件是不是经过了 Excel 的“保存”这个动作。3.2 read_only大文件的内存救星默认加载一个 xlsxopenpyxl 会把整个工作表的结构都构建成对象文件越大内存占用越高。我遇到过一个几十 MB 的报表普通模式加载后脚本直接吃掉了快两个 G 内存。处理这类文件建议用read_onlyTrue。wb load_workbook(large_sales.xlsx, data_onlyTrue, read_onlyTrue) ws wb[Sheet1] for row in ws.iter_rows(values_onlyTrue): # 按行处理 pass wb.close()read_only 的核心机制是惰性加载它不会一开始就把所有单元格对象建好而是按行流式读取。代价是部分能力受限比如无法随意修改单元格、访问随机单元格的性能也会变差。另外read_only 模式下用完了一定要调wb.close()否则文件句柄不会释放。Windows 上你甚至可能因此无法删除或覆盖这个 Excel 文件。3.3 还有哪些值得关注的参数keep_vbaTrue读取带宏的 .xlsm 文件时保留 VBA 工程。rich_textTrue如果单元格内容包含富文本格式需要开启它才能拿到带格式的文本对象。普通场景用不到但遇到特殊报表可以留个印象。read_only和data_only不是互斥的可以同时使用。4. 五种读取写法从 A1 到 iter_rows4.1 最简单的两种单点取值直接按坐标读取最直观# 坐标方式 value ws[A1].value # 行列索引方式 value ws.cell(row1, column1).value两者没有本质区别ws[A1]底层也会解析成行列索引。我推荐在代码里统一用ws.cell(row..., column...)因为写循环时更清晰不容易出现“把 B 列和 2 行搞混”的低级错误。4.2 用 iter_rows 按范围读取只读一小片区域时iter_rows是最实用的。它返回的是一个生成器每个元素是一行元组元组里是单元格对象或值取决于你有没有开values_only。# 读取第2行到第10行、第1列到第5列 for row in ws.iter_rows( min_row2, max_row10, min_col1, max_col5, values_onlyTrue ): print(row)很多教程默认values_onlyFalse也就是说你拿到的是一排单元格对象还要再写一次.value才能取到内容。如果只关心数据本身建议直接设置成True少一层对象解包代码也简洁。差别主要体现在性能上对象封装是有开销的几万行数据时差异就明显了。补充一个容易漏掉的方法iter_cols按列遍历。有些需求是提取某一整列去做去重、统计用它比逐行取第 n 个元素直观for col in ws.iter_cols( min_col2, max_col2, values_onlyTrue ): for value in col: # 处理 B 列的每个值 pass4.3 ws.values最天然的按行读取如果只是按顺序读整张表ws.values是最简洁的写法。它是按行生成值的生成器等价于不带参数地调用iter_rows(values_onlyTrue)。它的问题在于无法指定读取范围所以适合“整表清洗”类任务。for row in ws.values: # 每行是一个元组 name, city, amount row[0], row[2], row[3]4.4 为什么我推荐“指定范围 values_only”如果只能记住一条读取建议我的选择是循环遍历时永远指定必要的范围并用 values_onlyTrue。指定范围能避免前面说的max_row虚高问题values_onlyTrue能减少对象解包的额外开销。两件事本质上都是少做无用功。数据量小的时候无所谓一旦到了几十万行这两种写法之间的耗时差距是倍数级的。5. 读取结果里的数据类型陷阱日期、百分比、文本数字、合并单元格5.1 日期可能是对象也可能是字符串Excel 内部存储日期的方式是序列号比如 2023 年 1 月 1 日在 Excel 里其实是 44927 这样的数字。openpyxl 在读取时会帮你转换成 Python 的datetime对象所以你通常会看到datetime.datetime(2023, 1, 1, 0, 0)。但有一种情况如果源文件里的日期本身就是用文本写的比如2023-01-01那读出来就是字符串。所以写清洗逻辑时最好都做类型判断而不是假设一定是datetimefrom datetime import datetime def parse_date(value): if isinstance(value, datetime): return value if isinstance(value, str): return datetime.strptime(value, %Y-%m-%d) return None5.2 百分比、文本数字、空值的三座大山百分比单元格在 Excel 里看到的是85%但 openpyxl 读到的是0.85。如果你直接拼字符串去做展示会得到0.85而不是85%。需要自己处理格式化。文本型数字单元格左上角的绿色小三角就是标志。openpyxl 读出来是字符串12345而不是数字12345。参与求和前必须先转类型。判断一个值是文本还是数字可以用isinstance(value, (int, float))但更稳妥的方式是写个安全转换函数。空值openpyxl 对空单元格返回None不是空字符串也不是 0。这个坑会直接影响条件判断if value会把 0 和空字符串也当成假值但None同样假需要区分处理。安全的数值转换函数我经常这么写def safe_number(value): if isinstance(value, (int, float)): return value if isinstance(value, str): try: return float(value.replace(,, ).replace(%, )) except ValueError: return None return None5.3 合并单元格只有左上角有值合并单元格读取时有个经典陷阱从B2:C4这样的合并区域取值实际上只有B2有值C2、B3这些位置返回的都是None。我一度以为是数据源缺失后来才意识到是合并单元格导致的。处理方式有两个思路读取前先检查ws.merged_cells.ranges拿到所有合并区域自己做映射。或者放弃读取中间行只在表头合并区域做定向读取数据行尽量避免合并。for merged_range in ws.merged_cells.ranges: print(merged_range) # B2:C4 这样的字符串如果是展示类需求要先“填充”合并区域让区域内每个位置都有左上角的值。openpyxl 没有内置这个功能需要你自己循环填充。我后来写了个小工具函数需求再次出现时直接复用。6. 实战从销售明细表里按条件筛选并汇总6.1 原始表格场景假设现在有一张sales_detail.xlsx结构如下第 1 行表头A 列订单日期B 列城市C 列产品D 列销量E 列单价F 列销售额公式计算列需求有两个一是按城市汇总销售额二是只保留销售额大于 5000 的行输出到一个新文本文件。同时文件名可能是别人手工维护的表格可能混入了文本类型的数字也可能有空行。6.2 完整实现用read_onlyTrue加data_onlyTrue加载然后用iter_rows流式遍历。这里我特意加了类型转换和空值判断避免脏数据让脚本直接报错from collections import defaultdict from openpyxl import load_workbook def safe_float(value): if isinstance(value, (int, float)): return value if isinstance(value, str): try: return float(value.replace(,, )) except ValueError: return None return None wb load_workbook(sales_detail.xlsx, data_onlyTrue, read_onlyTrue) ws wb[Sheet1] city_sales defaultdict(float) filtered_lines [] for row in ws.iter_rows(min_row2, values_onlyTrue): # 防止拖尾空行 if row[0] is None and row[1] is None: break city row[1] amount safe_float(row[5]) if amount is None: continue city_sales[city] amount if amount 5000: filtered_lines.append(row) wb.close() print(按城市汇总, dict(city_sales)) with open(filtered_output.txt, w, encodingutf-8) as f: for line in filtered_lines: f.write(\t.join(str(cell) for cell in line) \n)注意我在min_row2后没有指定max_row而是用“连续空行 break”来避免 max_row 虚高带来的无意义遍历。这个技巧在处理手工维护的表格时非常实用。6.3 还能往哪个方向扩展这样的读取逻辑非常容易接到自动化流程里比如把filtered_lines继续清洗、转成 DataFrame、批量写回新的 Excel 模板或者对接数据库。openpyxl 负责的是“把 Excel 变成可编程的数据流”这步后面的处理完全看业务需求。7. 大文件读取的性能实测与收尾细节7.1 实测下来的性能感受关于大文件我做过一个对比测试一个约 20 万行、15 列的 xlsx普通模式加载后用ws.values遍历脚本内存占用能到 1GB 以上整个过程肉眼可见地变慢同样一台机器上改用read_onlyTrue内存大幅下降整体耗时能缩减到前者的几分之一。虽然具体数值取决于文件里的共享字符串比例、列宽设置等但趋势是一致的文件越大read_only 的收益越大。还有一个容易被忽视的性能杀手在 read_only 模式下随机访问单元格。比如你在循环里不断调用ws.cell(rowi, columnj)它没法像普通模式那样直接在内存里索引而是按位置重新定位到对应的 XML 片段一旦跳来跳去速度会急剧下降。如果确实需要多个位置的值建议一次性把需要的行区域读出来再在内存里访问。7.2 收尾工作close、xls 兼容、异常处理读取任务完成后记得wb.close()。别小看这一步Windows 下不关闭句柄可能无法删除或覆盖该文件。read_only 模式下不关闭部分平台的资源迟迟不释放连续处理多个文件时会累积成内存压力。如果脚本中途出现异常最好用try/finally确保关闭wb load_workbook(sales.xlsx, data_onlyTrue, read_onlyTrue) try: ws wb[Sheet1] for row in ws.iter_rows(values_onlyTrue): pass finally: wb.close()另外openpyxl 只认 xlsx/xlsm。要是手上只有一个 .xls 老文件别硬闯直接报错。我习惯的做法是先做个格式探测读文件头判断是不是 zip 包xlsx 本质是 zip是就进 openpyxl否则提示先转换。7.3 最后分享一个工作习惯处理 Excel 读取类任务我现在的固定工作流是先写出独立的数据读取函数入参是文件路径、工作表名、要读的列范围出参是干净的二维列表类型清洗单独放一个模块业务逻辑再调用这两层。这样换数据源、换清洗规则时不会把代码改成一团乱麻。这套 openpyxl 的组合用法官方文档其实也都有但“什么时候用哪个参数”并不是文档能帮你决定的。我在反复处理真实报表后最深刻的体会是读取 Excel 的难点从来不在 API 本身而在数据源的不确定性和性能边界。所以读之前先看清楚文件长什么样读的时候多留一手类型判断和空行截断读完之后记得关掉工作簿——做到这几点基本就能避开绝大多数坑了。
