openpyxl读取Excel实战：从基础API到性能优化避坑指南

做数据处理这几年Excel 读取始终是绕不开的活儿。不管是给运营导报表、整理实验数据还是清洗从业务系统里导出的乱七八糟的表格第一步往往都是“把这个 Excel 读进 Python”。前几年我用 xlrd 比较多但后来 xlrd 新版本不再支持 .xlsx我就彻底转向了 openpyxl一直用到现在。这篇文章把我在实际项目中用 openpyxl 读取 Excel 的完整经验做了一个整理包括选型思路、基础 API、进阶细节、性能优化以及我自己踩过的一些坑希望能帮你少走弯路。这篇内容适合这样的读者刚接触 Python 数据处理、想从 Excel 里批量取值的新手或者已经在用 pandas 但想搞清楚底层读取逻辑的进阶用户也包括需要在不需要数据分析库的轻量场景下直接操作 Excel 文件的开发者。读完之后你不仅能写出让文件打开、数据出来的“能跑的代码”还能明白每一步操作背后到底发生了什么。1. 为什么是 openpyxl——先说选型和场景1.1 主流的 Excel 读取库对比Python 生态里能读 Excel 的库不少但常用的就那几个。我从实际使用的角度把它们做了个对比特性openpyxlxlrdpandas底层调用支持的格式.xlsx / .xlsm旧版支持 .xls新版不支持 .xlsx.xlsx通过 openpyxl 或 xlrd能否写 Excel支持不支持新版仅读可写但依赖额外库读取公式缓存值支持不支持支持读取单元格样式支持不支持不支持内存占用大文件可开启只读模式优化较低较高学习成本中等低较高需要理解 DataFrame这里我特别想强调的是如果只是“读 Excel 然后做数组/字典级别的操作”openpyxl 是比 pandas 更轻量的选择。pandas 当然方便但你引入了一个大依赖而且 pandas 本身读取 .xlsx 也是走了 openpyxl 或者 xlrd 的底层所以了解 openpyxl 能帮你弄清楚很多“为什么读出来是这个结果”的问题。1.2 openpyxl 的核心优势和适用场景openpyxl 的全名是 Open XML Python library它直接面向 Office Open XML 格式也就是 .xlsx 文件。它不需要本机安装 Office也不依赖任何其他第三方库安装时只依赖 et_xmlfile所以把它理解为一个“Xml 解析器 数据映射器”会更准确。它的优势在于数据读取和写入能力兼备同一个库能搞定读写两端支持单元格样式、合并单元格、公式、批注等元素的读取几乎覆盖了 Excel 文件的所有可见内容提供只读模式处理几十兆甚至上百兆的 Excel 文件不会直接内存爆炸API 设计按照 Excel 的对象模型来工作簿、工作表、行列、单元格学起来很直观。我遇到过很多同学问要不要学 pandas 而不是 openpyxl。我的观点是如果是纯数据分析用 pandas 没毛病但如果你的目标是“把表格读干净、取出需要的值、再做后续处理”openpyxl 的直接和可控是 DataFrame 那层封装没法完全替代的。2. 读 Excel 前需要理解的基础模型2.1 安装与基本环境openpyxl 的安装很简单。pip install openpyxl如果你的环境里同时装了 Anaconda大概率已经预装了 openpyxl可以直接用。import openpyxl print(openpyxl.__version__)我日常使用版本是 3.1.xAPI 设计已经很稳定了。如果你在旧项目里遇到像get_sheet_names()这类写法注意那是老版本的 API新版本已经改成sheetnames属性了。2.2 理解 Excel 文件的对象结构openpyxl 的整个读取过程围绕三级对象展开Workbook对应整个 Excel 文件一个文件就是一个工作簿Worksheet对应文件里的一个工作表SheetCell对应表格里的一个单元格。这个模型和 Excel 本身的结构一一对应。我在教学时经常打这个比方工作簿像一本本子工作表像本子里的每一页单元格就是每一页上写的字。你在操作时永远是先打开本子再翻到某一页最后读某一格的字。文件结构这个前提很重要因为它直接决定了 openpyxl 读文件时做的事情.xlsx 文件本质上是一个 ZIP 压缩包里面装着一堆 XML 文件。openpyxl 做的就是解压这个包、解析 XML、把数据映射成 Python 对象。你理解了这一点就明白为什么读取大文件时会慢、为什么 openpyxl 不依赖 Office 也能工作。3. 核心读取操作从加载工作簿到拿到单元格3.1 加载工作簿与工作表的选择我们先从最基础的一行代码开始。import openpyxl wb openpyxl.load_workbook(某项目数据.xlsx) print(wb.sheetnames)打印结果是一个列表比如[月度汇总, 明细数据, 参数说明]。拿到这个列表你就知道该去哪个工作表里找数据了。接下来是选择工作表主要有三种方式# 方式一按名称取 ws wb[月度汇总] # 方式二取当前活动的工作表 ws_active wb.active # 方式三按下标取适用于名称不确定的场景 ws_first wb.worksheets[0]实际项目里我最常用的是第一种因为按名称取最明确——脚本对接的表格结构一旦确定sheet 名称基本不会变。用下标取有一个风险如果表格被人工调整了 sheet 顺序代码就会读到错误的数据。如果你要写一个给同事共享的脚本宁可多做一次判断也别偷懒用下标。有一个细节要提醒load_workbook打开的是文件的一个“快照”你在 Python 里做的修改不会自动写回原文件需要手动save()。这听起来简单但对读写双侧操作时容易混淆。3.2 三种取值方式坐标、索引与遍历拿到工作表对象之后就可以正式读数据了。openpyxl 提供了三种常见的取值方式我分别说一下使用场景。第一种直接用坐标取值# 读取 A1 单元格 cell_a1 ws[A1] print(cell_a1.value)第二种用行列索引取值# 读取第 3 行第 5 列即 E3 cell_e3 ws.cell(row3, column5) print(cell_e3.value)第三种遍历行取值for row in ws.iter_rows(min_row1, max_rowws.max_row, max_colws.max_column): for cell in row: print(cell.coordinate, cell.value)关于这三种方式我的经验是定位单个单元格用坐标法最直观在循环里处理数据用ws.cell(row, column)更不容易出错——因为坐标法需要拼字符串例如ws[f{column_letter}{row_index}]一旦列数超过 26 变成两位字母字符串拼接就容易出 bug如果想拿到一整块数据优先考虑iter_rows。3.3 行列级的数据提取与区域读取处理真实表格时很少只读一个单元格通常要读一段区域、一行或一列。openpyxl 对此有专门的支持。按行读取整行数据row_data [cell.value for cell in ws[2]] print(row_data)按列读取整列数据col_data [cell.value for cell in ws[A]] print(col_data)读取一个矩形区域for row in ws[A2:C10]: for cell in row: print(cell.value, end | ) print()ws[A2:C10]返回的是一个tuple内部的每一行又是一个tuple每个元素是一个Cell对象。用的时候注意直接遍历返回的是 Cell 对象不是值本身。想跳过这层转换可以直接使用values_only参数。for row in ws.iter_rows(min_row2, max_row10, min_col1, max_col3, values_onlyTrue): print(row)这样每一行就是一个值构成的元组处理起来清爽很多。尤其是配合min_col、max_col参数可以精准避开前几列和后几列对你数据提取的干扰。这里补充一个实用技巧如果表格前几行是空行或者说明文本表头可能在第 3 行甚至更靠后可以用max_row先探测有效数据范围再确定读取区间避免硬编码行号导致脚本换一个文件就失效。# 先用循环找到第一个有数据的行再确定表头行位置 first_data_row None for row in ws.iter_rows(min_row1, max_row5, min_col1, max_col1, values_onlyTrue): if row[0] not in (None, ): first_data_row row[0] break说到底读取 Excel 的操作本质上就一句话用你熟悉的坐标思维把单元格里的值“抠”出来。剩下的都是技巧和边界处理。4. 读取进阶类型、公式、合并单元格与格式4.1 数据类型的自动转换很多人用 openpyxl 取值时会忽视一个很有用的特性openpyxl 会自动把 Excel 的数据类型映射为 Python 的对应类型。Excel 类型Python 类型文本str数字int / float日期时间datetime.datetime布尔值bool空白单元格None这一点在数据处理流程里特别重要。比如你从 Excel 读取一列“发布日期”得到的不是字符串“2024-05-01”而是一个 Python 的datetime对象可以直接做日期运算反过来说如果 Excel 里存的是文本型数字openpyxl 读取时会原样返回字符串“20240501”你必须自己int()转换才能参与后续计算。有一个坑特别值得注意Excel 单元格显示的是“2024/5/1 00:00:00”但 openpyxl 取出来时如果你只关心日期往往时间部分也在。这种情况可以用date属性只取日期部分。dt cell.value if hasattr(dt, date): date_only dt.date()4.2 关于公式data_only 的真相读取 Excel 时单元格内容可能是公式比如“B2C2”。openpyxl 有一个重要的参数data_only它可以控制你读到的是公式字符串还是公式的计算结果。wb_formula openpyxl.load_workbook(财务表.xlsx, data_onlyFalse) wb_cached openpyxl.load_workbook(财务表.xlsx, data_onlyTrue)data_onlyFalse默认读到的是公式文本值属性返回B2C2这种字符串data_onlyTrue读到的是 Excel 保存文件时缓存的计算结果。这里有一个隐藏的坑如果这个文件是从某个业务系统导出、或者你没有在 Office / WPS 里打开过它那么公式对应的缓存结果可能是None。因为 openpyxl 本身不执行公式计算它只是读取 Excel 文件里已经存好的计算结果。如果文件从未被 Excel 计算过就没有这个结果可读。我在实际项目里遇到过不止一次导出报表的脚本用data_onlyTrue读某个字段结果打印一堆None查了半天才发现是文件里根本没有缓存公式结果。解决办法有两个如果可以使用 pandas在某些场景下 pandas 会触发计算其实是 Excel 兼容层实现的不过不是所有环境都有最稳妥的方案是在源头保证表格已经被打开并保存过一次或者直接改读取策略如果你只需要公式结果但文件没有缓存值可以先算出结果再写入新文件。4.3 合并单元格的读取逻辑合并单元格是 Excel 读取里最让人抓狂的问题之一。openpyxl 对合并单元格的规则是这样的在合并区域内只有左上角单元格是有值的其他位置的单元格读取结果是None。print(ws[A1].value) # 合并区域左上角有值 print(ws[A2].value) # 合并区域内其他单元格None要正确处理合并单元格需要先知道哪些区域被合并了。openpyxl 提供了合并单元格的范围列表merged_cells.ranges。for merged_range in ws.merged_cells.ranges: print(merged_range)输出效果类似A1:A3、B2:C2这样的坐标范围。处理逻辑通常是在读取整块数据时如果某个单元格读取结果为 None就去查它是否属于某个合并区域如果是就去取该区域左上角单元格的值。下面我写了一个可以直接复制的辅助函数。def get_merged_value(ws, row, col, merged_ranges_map): 获取单元格值如果是合并单元格的从属区域取左上角的值。 merged_ranges_map: {cell_range_str: top_left_value} 或类似映射 cell_coord f{openpyxl.utils.get_column_letter(col)}{row} for range_str in merged_ranges_map: if cell_coord in range_str: return merged_ranges_map[range_str] return ws.cell(rowrow, columncol).value实际构建merged_ranges_map时可以遍历ws.merged_cells.ranges取每个区域左上角的单元格值放进去。这个方法在处理复杂报表时非常实用。4.4 样式、批注和其他元素的读取有时候你需要的不是数据而是格式。比如判断某个单元格是否标黄、字体是否是红色。openpyxl 可以读单元格的样式。cell.font.color.rgb # 字体颜色 cell.fill.start_color.rgb # 填充颜色 cell.border.left.style # 边框样式样式读取通常用于自动化报表校验。比如一项日常巡检任务检查上百个 Excel 文件里所有“异常”标识单元格是否真的是红色填充。用脚本跑一遍10 分钟能检查完以前人工需要半天的工作。批注也一样通过cell.comment可以拿到批注文本常用于读取报表里的业务备注。不过要注意目前 openpyxl 读批注只能读取纯文本不支持富文本。5. 实战大文件性能优化与避坑经验5.1 大文件读取只读模式与迭代器遇到几十 MB 的 Excel直接用load_workbook默认模式读取内存会涨得比较夸张程序甚至可能卡死。因为默认模式会把整个 XML 解析进内存构建出完整的工作簿对象。openpyxl 为此提供了只读模式read_onlyTrue。wb openpyxl.load_workbook(大文件.xlsx, read_onlyTrue) ws wb.active for row in ws.iter_rows(values_onlyTrue): # 处理每一行内存占用稳定 pass只读模式的原理是“按需读取”它一次只加载一条数据流不会把整个工作表都塞进内存。但它也有明显的限制只读模式下工作簿只能读不能修改否则会报错ws.merged_cells、样式读取等能力受到影响部分元数据访问不可用如果要再次遍历需要重新加载工作簿。实测下来单项 50 MB 左右、十几万行的 Excel 文件在只读模式下读取并做简单统计内存增量可以控制在几百 MB 以内而默认模式我遇到过 2 GB 内存仍不够的情况。所以只要是大文件第一反应就改成只读模式这是最有效的优化手段。5.2 常见踩坑记录与排查方案我在使用 openpyxl 过程中积累了不少排错的实战经验整理成下面这个速查表问题现象根本原因解决方案data_onlyTrue读取公式列结果全是None文件没有公式缓存值用data_onlyFalse读公式文本或先打开保存文件日期读取出来是数字如 45123Excel 日期存储为序列号openpyxl 未正确解析罕见但存在确认文件日期格式手动用datetime转换合并单元格区域读取只有左上角有值openpyxl 的合并单元格设计如此通过merged_cells做映射取值读取大文件时内存占用过高默认模式加载全部数据到内存换用read_onlyTrue和iter_rows单元格明明有数字读出来变成1.0Excel 值本身是浮点数用int()转换注意是否丢失精度文件路径含中文报错或找不到文件编码或路径分隔符问题使用pathlib.Path处理路径或用绝对路径读取.xls文件报错openpyxl 只支持.xlsx/.xlsm转格式或用兼容库再补充两个平时少有人提的细节第一个是空单元格返回None很多初学者拿到None会困惑。这个问题不大但常见如果一个表格列里混着空值和实际数据你读取后在判断时一定要显式处理None否则下游计算很容易出错。第二个是关于数字精度的。Excel 以浮点数存储读取大数值比如超过 15 位的订单号时openpyxl 可能返回123456789012345.0这种浮点数导致精度丢失。处理这种场景最好在 Excel 源头上把那列设为文本格式如果源文件已经无法修改读取后需要格式化处理。这个坑我在订单号上踩过折腾了很久才定位到是存储精度问题。5.3 读取流程规范写在脚本里更稳我做了这些年数据清理最后沉淀出了一个相对通用的 Excel 读取流程建议你也按这个顺序来组织脚本加载工作簿指定read_only和data_only参数确认工作表名称避免误用默认 sheet定位表头行和有效数据区域用迭代器按行读取边读边把值转换成目标 Python 类型在读取阶段统一处理合并单元格、空值、日期和浮点数返回标准化后的列表或字典结构交给后续逻辑。这个流程的核心思想是“读取阶段就把脏活干完”不要让数据带着格式问题流到下游。每次写脚本都按这个流程走虽然前期稍微多几行代码但后续处理和调试的时间能省下一大半。6. 一个完整可复用的读取模板理论讲再多不如直接上代码。下面是我常用的一套读取模板做了一个适度的裁剪。它支持指定 sheet、按行列范围读取、合并单元格解析和数据类型转换。import openpyxl from openpyxl.utils import get_column_letter from datetime import datetime from pathlib import Path def read_excel_sheet( file_path: str, sheet_name: str None, start_row: int 2, max_col: int None, as_dict: bool True, ) - list[dict] | list[tuple]: :param file_path: Excel 文件路径 :param sheet_name: 工作表名默认取第一个 sheet :param start_row: 数据起始行默认第 2 行跳过表头 :param max_col: 读取最大列数默认取有数据的最大列 :param as_dict: 返回类型True 为字典列表False 为元组列表 file_path Path(file_path) if not file_path.exists(): raise FileNotFoundError(f文件不存在: {file_path}) # 加载工作簿 wb openpyxl.load_workbook(file_path, read_onlyTrue, data_onlyTrue) # 选择工作表 if sheet_name is None: ws wb.active else: if sheet_name not in wb.sheetnames: raise ValueError(f工作表 {sheet_name} 不存在现有: {wb.sheetnames}) ws wb[sheet_name] # 构建合并单元格映射只读模式下可能需要取消只读这里留接口 merged_map {} try: for mr in ws.merged_cells.ranges: top_left str(mr).split(:)[0] merged_map[str(mr)] ws[top_left].value except Exception: pass # 确定最大列数 if max_col is None: max_col ws.max_column # 读取表头 headers [] for col in range(1, max_col 1): cell_val ws.cell(row1, columncol).value headers.append(str(cell_val).strip() if cell_val is not None else fcol_{col}) # 按行读取数据 rows_data [] for row in ws.iter_rows( min_rowstart_row, min_col1, max_colmax_col, values_onlyTrue ): if row is None: continue # 跳过整行为空的行 if all(v is None or str(v).strip() for v in row): continue if as_dict: record {} for idx, value in enumerate(row): # 合并单元格处理如果需要 cell_coord f{get_column_letter(idx 1)}{ws.iter_rows().__next__().__class__} # 简化示意按需实现 record[headers[idx]] value rows_data.append(record) else: rows_data.append(tuple(row)) wb.close() return rows_data if __name__ __main__: data read_excel_sheet(数据.xlsx, sheet_name明细, as_dictTrue) print(data[:5])需要说明的是如果你是真正要把这个模板用到生产环境合并单元格的解析部分不应该在read_onlyTrue下做因为只读模式访问merged_cells会受限。实战中我的做法是先去普通模式加载一次拿合并范围元数据然后关闭再用只读模式迭代数据或者干脆小文件直接普通模式读完大文件放弃合并单元格读取。模板的另一个设计是统一把空值拿掉或者替换成空字符串。实际项目中根据业务类型不同空值处理策略也不同计费金额类数据空值要填 0文本类备注空值要填空字符串布尔类型空值要填 False。这个决策不要让数据流到分析阶段才做在读取时就要根据业务字段类型提前定义好规则。用这个模板我在不少项目里都只改了 sheet 名称和业务逻辑就能完成大部分日常 Excel 数据接入的活。根据我个人经验openpyxl 真正强大的地方不在某个单独 API而在它对 Excel 对象模型的贴近。你一旦熟悉了工作簿、工作表、单元格这个三层结构就会发现 Excel 自动化的思路一下变得很清晰。以后再有同事跑过来说“帮我导个表”你的第一反应不再是打开 Excel 手动操作而是问一句“你的文件是 xlsx 格式的吧”。这大概就是工具熟练之后底气变足的默契。