平时做数据处理Excel 绝对是绕不开的格式。不管是运营给的报表、财务给的流水还是客户发来的模板拿到手第一步往往就是把这些表格读进 Python 里做清洗和分析。说实话网上讲 openpyxl 的教程不少但大多停留在“能跑出结果”的层面很多实际场景里必须注意的细节——比如合并单元格怎么读、公式怎么处理、大文件怎么读不卡——反而没人展开讲。今天我用一篇完整的实操详解把 openpyxl 读取 Excel 的常用姿势、坑点、性能优化都过一遍希望能帮你在真实项目里少走几步弯路。1. 为什么读 Excel 我首选 openpyxl1.1 生态里那些读 Excel 的库各有什么脾气Python 生态里处理 Excel 的库不少很多人一上来就选 pandas这个选择本身没有错但需要搞清楚不同库的分工。pandas 的 read_excel 底层其实就是调用了其他引擎比如 openpyxl 或 xlrd也就是说 pandas 只是一个更上层的封装。如果你只是偶尔读几个表格做分析pandas 确实爽一行代码就能把数据拉成 DataFrame。但如果你要精细控制单元格级别的操作比如读取某个单元格的样式、批注、超链接或者手动遍历一张大表的某几列直接用 openpyxl 反而更顺手。还有一个常被提到的库是 xlrd老牌库但它的主要支持范围是 xls 格式对 xlsx 的支持不完整而且官方已经明确放慢了更新节奏。xlwt 则是用来写 xls 的早就不推荐在新项目里用了。xlwings 能调用 Excel 应用程序本身来做操作功能逆天但依赖 Windows 环境的 Excel 客户端适合做自动化办公的桌面工具不适合纯服务器环境。对比下来openpyxl 定位非常清晰原生支持 xlsx 格式、不依赖 Office 软件、性能可控、文档齐全是我在服务端脚本和自动化工具里的首选。1.2 openpyxl 到底适合解决什么问题openpyxl 适合的场景可以总结为三类第一类是从 xlsx 里读取数据做二次处理比如读取导出报表、收集测试数据、批量读取模板第二类是程序化生成 xlsx比如把小批量数据写成格式化表格交付给非技术同事第三类是修改已有表格的结构和样式比如批量加边框、调列宽、补充公式。今天这篇主要围绕第一类场景展开。要注意的是openpyxl 不支持 xls 老格式遇到 xls 文件需要先用工具转成 xlsx或者改用其他库。另外如果你只需要极致的读取性能、要处理几十万行以上的庞大数据openpyxl 有只读模式可以应付但本质上 xlsx 是 XML 压缩包解析开销摆在那里这时往往更适合把数据导出成 CSV 再处理。每种工具都有自己的边界选型的关键是匹配需求而不是追着功能多去用。2. 环境准备安装和加载工作簿2.1 安装与版本选择安装 openpyxl 非常简单直接用 pippip install openpyxl如果你用的是国内镜像可能会遇到缓存导致版本不是最新的问题这时候可以指定版本安装pip install openpyxl3.1.5目前常用的版本是 3.xAPI 结构稳定网上大多数教程也基于这个版本。安装完成后可以用下面的方式验证import openpyxl print(openpyxl.__version__)我建议在虚拟环境里安装避免污染全局环境。另外强调一点openpyxl 不依赖 Office 软件它的本质是直接解析 xlsx 文件内部的 XML 结构这意味着你在没有安装 Excel 的 Linux 服务器上也能读写 xlsx这一点在实际部署时太重要了。2.2 加载工作簿的两种姿势openpyxl 读取文件的核心入口是 load_workbook。最基本的做法是传文件路径from openpyxl import load_workbook wb load_workbook(销售报表.xlsx) print(wb.sheetnames)如果你已经用其他方式把文件读进了内存比如从网络接口下载的字节流、或者上级系统传过来的 BytesIO 对象可以用如下方式加载from io import BytesIO from openpyxl import load_workbook # 假设 data 是 bytes 类型 file_stream BytesIO(data) wb load_workbook(file_stream)这种写法在开发 API 服务和自动化脚本时特别常见。文件从 HTTP 响应里拿到之后不用先落地到磁盘直接喂给模块解析既省了磁盘 IO也避免了临时文件清理的麻烦。2.3 工作簿对象的基本属性加载成功后wb 是一个 Workbook 对象。有几个属性值得一提# 获取所有工作表名称 sheet_names wb.sheetnames # 获取当前默认激活的工作表 active_sheet wb.active # 获取指定名称的工作表 sheet wb[销售明细]sheetnames 返回列表里面是字符串顺序就是工作表在 Excel 里的标签页顺序。wb.active 返回的不是名称而是工作表对象本身如果你不指定读取哪个工作表默认就是第一个可见的工作表。需要注意的是如果文件里有隐藏的工作表sheetnames 里依然会显示出来默认加载时隐藏表和普通表一样都可以读取。3. 工作表与单元格读取3.1 拿到工作表的常用方式当你面对一个内容复杂的工作簿时第一步通常是搞清楚里面有哪些工作表。这里有个小技巧如果工作表名称不固定最好用循环去匹配for name in wb.sheetnames: if 汇总 in name: ws wb[name] break这样做的原因是 Excel 表格的 sheet 名称经常被业务同事修改比如从“Sheet1”改成“七月汇总”如果硬编码名称脚本就容易崩。当然如果你确定名称固定直接访问更快ws wb[月度数据]另外还有一个细节值得注意工作表的名称区分大小写“sheet1”和“Sheet1”是两个完全不同的名称在脚本里写错一个字母就会 KeyError排查起来需要一点耐心。3.2 读取单元格的几种姿势拿到工作表对象 ws 之后读取单个单元格最直观的方式是用坐标直接访问value ws[A1].value print(value)也可以用行号列号的方式value ws.cell(row1, column1).value print(value)第一种适合手写坐标第二种适合在循环里动态定位。我平时写代码更推荐 cell 方式因为传参更明确而且 cell 方法的性能在循环访问时也足够稳定。读取到的 value 属性就是单元格的内容。如果单元格是空白的value 返回 None。这里要特别留意Excel 本身区分“真空单元格”和“空字符串单元格”有些系统导出的表格里看起来是空的其实存的是空字符串读取出来就是一个空字符串而不是 None处理数据时要用不同的逻辑去兜底。3.3 批量读取区域数据单格读取不够用实际需求往往是读取一个区域。openpyxl 提供了一种类似切片的方式# 读取 A1 到 C10 区域的数据 data ws[A1:C10]这个返回的不是二维列表而是一个由行组成的元组每行里是 Cell 对象。一般我们会嵌套列表推导式把它转成二维数组rows [[cell.value for cell in row] for row in data]这种方式简单直接适合一次拉取小范围数据。如果区域不确定大小想动态读取整张表可以用 ws.iter_rows 配合属性这部分内容在后面单独展开。4. 遍历行列把表格变成结构化数据4.1 按行遍历和按列遍历读取整张表最常见的需求是按行遍历openpyxl 提供了 ws.rows 属性它返回一个生成器每次迭代出这一行的所有单元格对象for row in ws.rows: values [cell.value for cell in row] # 在这里处理每一行数据按列遍历用 ws.columns类似但方向不同。实际工作中按列遍历用得不多因为大多数业务表格的结构是“一行是一条记录”但偶尔遇到列式布局的配置表按列遍历就派上用场了。处理行数据时要特别注意表头。绝大多数表格的第一行是标题直接遍历会把表头当数据读取。通常的做法是单独取出表头然后从第二行开始遍历header [cell.value for cell in next(ws.iter_rows(min_row1, max_row1))] data_rows [] for row in ws.iter_rows(min_row2, values_onlyTrue): data_rows.append(dict(zip(header, row)))这里用到了 iter_rows 的 values_only 参数设为 True 之后迭代出来的直接是值而不是 Cell 对象代码清爽不少。4.2 iter_rows 和 iter_cols 的正确用法iter_rows 是遍历行数据最常用的方法因为它可以精确控制开始行、结束行、开始列、结束列# 读取第 1 行到第 10 行第 1 列到第 5 列 for row in ws.iter_rows(min_row1, max_row10, min_col1, max_col5, values_onlyTrue): print(row)如果不指定这些边界参数默认会遍历整张表的所有已占用区域。需要提醒的是这里的“已占用区域”由 max_row 和 max_column 决定指的是工作表右下角有内容的坐标。有时候表格里某个单元格不小心写入了空格这个区域就会被撑得很大遍历时会出现大量空行。iter_cols 的用法和 iter_rows 对称它按列返回数据。需要配合 values_only 使用时也要注意它迭代出来的每一列是一个元组包含该列所有行上的值处理时想法和按行遍历完全不同。4.3 组装成 List[Dict] 或 DataFrame在读取业务数据时我习惯把每行数据转换成字典键是表头值是行数据这样后续逻辑里可以直接通过字段名访问不容易因为列顺序变动而出错。如果项目里已经用了 pandas也可以直接转 DataFrameimport pandas as pd data ws.iter_rows(values_onlyTrue) df pd.DataFrame(data)但这里有个常见的坑DataFrame 会把第一行自动当作表头吗其实不会默认情况下第一行是数据行。当然你可以传 header0 让它处理表头但如果你自己已经切片好了数据直接构造 df 再手动指定列名更可控rows list(ws.iter_rows(values_onlyTrue)) df pd.DataFrame(rows[1:], columnsrows[0])这一下就把 openpyxl 读出来的数据无缝衔接到了 pandas后面的数据清洗工作就方便很多。5. 复杂场景实战公式、合并单元格、类型5.1 读取公式 vs 读取计算结果openpyxl 在读取单元格时如果单元格里写的是 Excel 公式默认拿到的是什么公开答案它拿到的是公式字符串本身而不是计算结果。举例来说如果 C1 单元格里写的是“A1B1”那么print(ws[C1].value) # 输出 A1B1这对于大多数场景来说并不友好因为我们通常希望拿到数值 100 而不是等号表达式。解决办法是加载文件时使用 data_onlyTruewb load_workbook(报表.xlsx, data_onlyTrue)这个参数的含义是让 openpyxl 尝试读取单元格的缓存值——也就是 Excel 保存文件时存下来的计算结果。前提是这个文件曾被 Excel 打开并保存过计算缓存才会存在。如果你的 xlsx 文件是程序生成的、从来没有被 Excel 打开过那么 data_onlyTrue 读出来的公式单元格可能全是 None因为根本没有计算过的缓存值。所以这里要形成一个概念openpyxl 本身不执行公式计算它只负责读取文件里已有的数据。需要执行公式求值的话要么文件先经过 Excel/WPS 打开保存要么用更专业的计算引擎去处理这已经超出 openpyxl 的能力范围了。5.2 合并单元格的读取策略合并单元格是读取 Excel 时最让人头疼的情况之一。假设 A1:C1 三个单元格合并成了一个openpyxl 读取时A1 有值B1 和 C1 都是 None。这符合我们肉眼看到的逻辑但程序处理时容易出错。我举一个真实业务场景。某公司导出的一张订单表每一行订单有多个商品明细订单号所在行做了纵向合并第二行到第五行的订单号都合并成了一个大单元格。直接按行读取时只有第一行有订单号剩下四行读出来是 None链路根本对不上。处理思路其实很清晰先用 ws.merged_cells.ranges 拿到所有合并区域的信息然后判断某个坐标是否在合并区域内如果是就向上查找这个区域左上角的值作为有效值。from openpyxl.utils import range_boundaries merged_coords [] for merged_range in ws.merged_cells.ranges: merged_coords.append(merged_range.coord) def get_merged_value(row, col): for coord in merged_coords: min_col, min_row, max_col, max_row range_boundaries(coord) if min_row row max_row and min_col col max_col: return ws.cell(rowmin_row, columnmin_col).value return ws.cell(rowrow, columncol).value不要以为这个问题很少见实际上业务表格中合并单元格的密度远超你的想象几乎每个公司都会有人为了“好看”去合并表头、合并同类项。提前写好这个兼容函数能省掉很多排查数据的痛苦。5.3 日期和数字的隐藏类型问题读出来的数据经常遇到类型不一致的情况。比如某列看起来是日期实际读取结果是 datetime.datetime 对象某列看起来是数字实际读取结果是字符串。openpyxl 读取时基本保留了底层存储的类型不会强转所以我们要自己做好类型判断和转换。日期类型的处理相对简单因为 datetime 对象可以直接用于后续处理。真正让人头疼的是 Excel 的日期序列号。有些文件在生成的时候没有设置标准的日期单元格格式读出来的日期值可能是一个整数比如 44562这其实是 Excel 内部的日期序列号。转换方式是把 1899 年 12 月 30 日作为基准日加上这个天数from datetime import timedelta, datetime BASE_DATE datetime(1899, 12, 30) def excel_serial_to_date(serial): return BASE_DATE timedelta(daysserial)用 openpyxl 读取时如果单元格本身是数字而不是日期我们就拿不到 datetime 对象只能拿到这个序列号这时候这个转换函数就派上用场了。建议在数据处理入口统一做一层类型清洗。5.4 单元格样式等元信息openpyxl 又一大特点是能读取单元格的样式信息这部分能力在纯数据读取工具如 pandas 里是没有的。你可以通过 cell.font、cell.fill、cell.alignment 等属性查看单元格的字体、背景色、对齐方式。某些业务场景下这非常有用比如需要识别表格里被标红的数据、筛选底色为黄色的标记行。cell ws[A1] if cell.font.color and cell.font.color.rgb FFFF0000: print(该单元格字体是红色)不过要注意样式信息的读取在性能上有额外开销如果只是读取数值用普通方式即可不要遍历所有单元格的样式否则大表的处理时间会翻十倍以上得不偿失。6. 常见问题与排查技巧实录6.1 常见问题速查表我把实战中经常遇到的一些报错和诡异现象整理成了表格方便大家快速排查现象常见原因解决办法load_workbook 报 FileNotFoundError路径不存在或文件名拼写错误检查路径、使用绝对路径报 BadZipFile 错误文件不是真正的 xlsx比如改后缀的 xls重新另存为 xlsxsheet 名称取不到工作表名大小写、空格不一致用 sheetnames 列出来核对data_onlyTrue 读取公式格为 None文件从未被 Excel 打开计算过先用 Excel/WPS 打开保存一次单元格读出来是 44562 这样的数Excel 日期序列号用序列号转换函数处理遍历出现大量 Nonemax_row 被空白单元格撑大用 min_row/max_row 精确限制范围A1 有值但 B1 读出来为 NoneB1 与 A1 合并单元格用合并单元格取值函数处理6.2 大文件读取性能优化几十万行的大表用 openpyxl 默认模式读取非常痛苦因为默认模式会把整个工作表的数据和样式都加载进内存。处理大文件的关键是用只读模式wb load_workbook(大文件.xlsx, read_onlyTrue) ws wb[Sheet1] for row in ws.iter_rows(values_onlyTrue): # 每一行都是流式读取 pass只读模式下 openpyxl 不会一次性把所有数据加载到内存而是流式解析速度虽然慢一点但内存占用大幅下降。实测读取一个 5 万行、20 列的文件默认模式可能吃掉几个 GB 内存只读模式能控制在几百 MB 以内。另一个优化思路是只读取需要的列用 iter_rows 限定 min_col 和 max_col避免把不需要的列也加载进来。6.3 其他容易踩的坑第一个坑文件路径中包含中文。在 Windows 环境下如果路径里有中文系统编码偶尔会出现兼容问题排查半天发现是工作目录的问题。稳妥的做法是在代码开头用 os.chdir 切换工作目录或者直接使用绝对路径配合 Path 对象。第二个坑不同环境下的 openpyxl 版本差异。3.0 和 3.1 在部分 API 上有些微差别线上部署的时候建议在 requirements.txt 里锁定版本号避免代码在开发环境能跑、生产环境崩掉。第三个坑excel 文件被另一个进程占用导致读取失败。Windows 环境下如果有用户打开了这个 Excel 文件程序读取时可能拿到被锁定的文件句柄这个问题不好通过 openpyxl 本身解决通常要在流程上处理让系统先生成副本再读取。第四个坑很多人都没想到格式伪装。你拿到一个文件叫“数据.xlsx”但它实际上是 CSV 改后缀名或者是一个 HTML 页面另存为的 xlsx。这种情况下 openpyxl 加载会直接报错排查方法是用文件头的魔数判断真实格式或者用更宽容的方式打开文件后看内容再决定解析策略。我自己在实际项目里还遇到过一种情况某个自动化脚本抓取网页并生成了 xlsx 文件但因为编码问题导致内部 XML 含有不规范字符openpyxl 打开时出现神秘报错最后把源文件重新生成一番才解决。这类情况没有太好的统一办法只能说遇到 xlsx 不可解析时先怀疑文件本身而不是怀疑库。最后分享一个我自己一直保持的习惯凡是线上跑的数据脚本入口处统一加日志把读取到的行数、列数、表头信息全都打出来。很多看似诡异的 Bug其实在第一步读数据的时候就已经错了只是后面没察觉。openpyxl 提供了 .max_row 和 .max_column 属性顺手打印出来比对一下人工看到的表格范围能帮你第一时间发现文件是不是被重新处理过。做数据处理稳比快更重要。