前阵子同事甩给我一堆周报让我用openpyxl读取工作簿里的销售数据再按城市汇个总。他以为这活儿至少得折腾一下午结果我花十分钟写了个脚本吃顿饭的功夫就跑完了。如果你也经常被Excel表格折磨或者想把手动复制粘贴的活儿自动化openpyxl这个库值得认真了解。它不是什么冷门工具而是Python生态里处理xlsx文件的主力库读取工作簿、读取单元格、批量遍历、处理合并单元格一套流程下来基本不会卡壳。这篇文章我就围绕读取工作簿展开把加载文件、定位工作表、读单元格、踩过的坑、大文件优化这些内容一次讲透连着代码和注释一并给到你可以直接拿去改。1. 为什么处理Excel我选openpyxl这个库1.1 对比pandas、xlrd、xlwingsopenpyxl赢在哪遇到Excel处理很多人的第一反应是pandas但我不建议一上来就开pandas。pandas的read_excel确实方便一行代码能把工作表读成DataFrame可它是个重型库底层还要依赖其他解析器而且一旦涉及合并单元格、公式缓存、样式保留、列格式识别这些细节pandas的可控性反而不好。尤其是当你只是想“打开工作簿、按行读取、按条件过滤”的时候pandas属于杀鸡用牛刀还会让脚本体积变大、启动变慢。xlrd这个库曾经是读取xlsx的主流选择但新版xlrd已经转向只支持xls老格式xlsx的读取被移除所以新项目再用它处理xlsx很容易踩版本坑。xlwings则是另一种思路它本质上是调用本机安装的Excel程序来操作文件功能强大但依赖GUI环境在服务器或者没装Office的机器上根本跑不起来自动化场景里非常受限。openpyxl的优势在于纯Python实现不依赖Excel组件能读能写能处理样式、公式、图表、合并单元格、批注等细节而且对xlsx这种基于XML的压缩格式解析得很稳定。对“读取工作簿”这种需求来说它是最稳妥的选择。我做文件数据处理的时候十次里有八次会选openpyxl收尾剩下两次是因为对方给的是xls老文件才考虑先用工具转个格式。1.2 安装与版本别小看环境准备这步安装很简单直接pip搞定pip install openpyxl如果你在公司内网环境可能需要换到内部镜像源这个按你们自己的网络设置来。装完之后可以在Python里确认一下版本python -c import openpyxl; print(openpyxl.__version__)建议用3.1以上版本早期的2.x版本虽然也能用但某些API表现不一样比如合并单元格范围的处理方式就改过。顺便提一句openpyxl只认xlsx、xlsm这类基于Office Open XML格式的文件老版xls它是打不开的报错也不是很友好。你要是收到的是.xls要么让上游重新导出为.xlsx要么先用其他工具转一下再跑脚本。2. 读取工作簿的基本盘load_workbook与工作表导航2.1 load_workbook的完整参数认知读取工作簿的入口是load_workbook我第一次用的时候只传了个文件名后来才发现这函数的参数里藏着不少坑from openpyxl import load_workbook wb load_workbook(某销售汇总.xlsx)默认情况下这个调用会把整个xlsx解析进内存所有工作表、所有单元格对象都会被创建。如果文件不大感觉不到差异等你哪天拿到一个几十MB的大表格就会明白普通模式到底有多吃内存。load_workbook还有几个关键参数read_only设置为True会进入只读流式模式一行一行从文件里读数据内存占用大幅下降代价是部分高级操作不可用。后面我会专门展开讲。data_only这个参数特别容易误解它不是“只读取数据”的意思而是“是否只读取公式的缓存结果”。默认False拿到的是公式字符串比如“SUM(A1:A3)”设为True拿到的才是Excel计算后保存下来的数值。如果公式从没被Excel真正计算过data_onlyTrue反而会返回None。keep_vba处理xlsm宏文件时使用如果你只想读数据保持默认False就行。keep_links控制是否保留外部链接信息默认True。如果你的工作表里引用了别的工作簿加载时会尝试解析这些链接某些文件会导致加载变慢纯粹读内容的话可以设成False。除了传文件路径load_workbook也可以接收一个二进制流对象比如从内存里读from io import BytesIO from openpyxl import load_workbook data bytes_io.getvalue() wb load_workbook(BytesIO(data))这个技巧在做接口对接、文件上传处理的时候很实用不用把临时文件写到磁盘上再读。2.2 从工作簿到工作表再到单元格三步定位数据拿到工作簿对象之后第一件事不是急着读单元格而是先摸清楚里面有几张表、每张表叫什么。print(wb.sheetnames) # 全部工作表名称列表 ws wb.active # 默认激活的工作表很多人习惯直接用wb.active但我要提醒一句active不一定永远是第一个工作表它表示的是“上次打开时激活的那张表”有时候文件处理逻辑变了active也会跟着变。想稳定定位某张表最好用表名索引ws wb[月度汇总]如果表名是动态的可以遍历sheetnames挨个处理for name in wb.sheetnames: ws wb[name] print(name, ws.max_row, ws.max_column)这里有个常用技巧先打印每个sheet的行列范围快速判断表是不是空表、数据区域在哪再决定怎么处理。我做啥脚本都习惯先来这段“侦察代码”省得后面读取时对不上数据范围。单元格的定位方式也很直接。你可以按Excel坐标写cell ws[A1] print(cell.value)也可以按行列索引写cell ws.cell(row1, column1)注意openpyxl的行列编号是从1开始的不是从0开始跟Python里list的索引习惯不一样用错的话读到的值会整体偏移一行或一列。工作表本身还带一些元信息偶尔也会用到ws.title # 表名 ws.sheet_state # visible / hidden / veryHidden ws.freeze_panes # 冻结窗格位置读取工作簿不只是读值表的隐藏状态、冻结窗格这些信息也是可以在自动化流程里利用的比如跳过隐藏表不处理。3. 读取单元格的几种实用姿势索引、切片与批量遍历3.1 用字母坐标还是行列索引读取单个单元格最直观的方式就是字母坐标。value ws[B2].value这种方式适合快速读取固定位置的字段比如表头在某一行、汇总值在某个固定单元格。但如果要循环处理整张表我更推荐用cell(rowrow, columncol)这种索引方式col_index 2 total 0 for i in range(1, ws.max_row 1): value ws.cell(rowi, columncol_index).value if isinstance(value, (int, float)): total value行列索引的好处是可以用变量控制列号比如动态匹配某个字段在第几列这在多张表结构不一致的场景里非常管用。有时候你还需要在字母坐标和数字索引之间转换openpyxl已经给了工具函数from openpyxl.utils import get_column_letter, column_index_from_string print(get_column_letter(2)) # B print(column_index_from_string(B)) # 2比如别人给的Excel里表头是“销售额”但不知道它排在AB列还是CD列这时候可以先遍历表头用column_index_from_string把列字母转成数字再统一按数字取数据。如果你想一口气拿一整列或一整行openpyxl也支持类似切片的方式col_cells ws[A] # A列从第一行到已使用区域末尾的所有Cell对象 row_cells ws[1] # 第一行的Cell对象拿到的是一组Cell对象用的时候记得取它的.value属性。这种写法比较省事但可读性不如iter_rows清晰我建议新手还是优先掌握行列索引和遍历方法。3.2 批量遍历iter_rows、rows与values_only读取工作簿的高频场景其实是“整张表读出来”这时候一个单元格一个单元格取太慢了用iter_rows最顺手for row in ws.iter_rows(min_row1, max_rowws.max_row, min_col1, max_colws.max_col, values_onlyTrue): print(row)iter_rows返回的是一个生成器每轮迭代给出一行数据。如果不写values_onlyTrue它返回的是这一行所有Cell对象你拿到后还得再取.value设置了values_onlyTrue直接就是单元格的值元组处理起来干净得多。如果你嫌参数太长还有个等价捷径for row in ws.iter_rows(): pass省略参数时它会按工作表的实际使用范围遍历所有行。但在某些情况下Excel里的“使用范围”可能比你真实数据范围更大比如有人删除过数据但没保存干净导致max_row比实际行数多很多。这种时候最好自己设定min_row和max_row别全依赖默认值。等价的还有ws.rows和ws.valuesrows ws.rows # 生成器每个item是一行的Cell对象元组 values ws.values # 生成器每个item是一行的值元组注意ws.values在普通模式下会一次性把整个工作表的值迭代完适合小表。对大表流式配合iter_rows更安全下面会专门讲。还有一个实用组合先把整个表变成值列表再处理。data list(ws.values)这个写法很直观但只推荐用在数据量不大、内存随便吃的情况下。一个几万行的表转成list通常没多大问题但如果你确定文件有几十万行就别这么写很容易把内存撑爆。3.3 区域切片读取与合并单元格的连带处理除了正向遍历openpyxl支持像Excel公式一样按区域切片region ws[A1:C3] print(region) # ((A1, B1, C1), (A2, B2, C2), (A3, B3, C3))返回的是元组套元组每一行是一个元组里面是Cell对象。这种写法适合读取表头区域或者某个固定报表区块。真正麻烦的是合并单元格。openpyxl读取合并单元格时只有左上角那个单元格有值其他区域返回的是None。比如A1:B2合并后B1、A2、B2的值都是None只保留A1有值。这在读报表时是非常容易踩的坑。要识别合并区域可以这样for merge_range in list(ws.merged_cells.ranges): print(merge_range.min_row, merge_range.min_col, merge_range.max_row, merge_range.max_col)老版本的openpyxl直接遍历ws.merged_cells也能得到类似信息新版统一推荐使用merged_cells.ranges。如果你的表格里有合并单元格而且你需要每个单元格都有值可以自己做一个“填充合并区域”的小函数def fill_merged(ws): for mr in list(ws.merged_cells.ranges): top_left ws.cell(rowmr.min_row, columnmr.min_col).value for r in range(mr.min_row, mr.max_row 1): for c in range(mr.min_col, mr.max_col 1): ws.cell(rowr, columnc).value top_left这样处理后原来为None的单元格会被填上左上角的值。注意如果你后面还要保存这个工作簿这个函数其实改变了单元格格式它会破坏合并单元格结构。所以我一般不是真的去改原表而是把值读到一个新的数据结构里比如所有行都变成统一的dict列表再继续做汇总。4. 真实数据读取中的三个经典坑与应对姿势4.1 data_onlyTrue却取不到公式结果这是我接到求助最多的问题之一。有位同学用openpyxl读一个带公式的报表load_workbook时特意加了data_onlyTrue结果某些单元格读出来还是None他一度怀疑是openpyxl的bug。其实openpyxl本身不会计算公式。当Excel在保存xlsx文件时会把每个公式最近一次计算的结果作为缓存一起存进去。data_onlyTrue只是告诉openpyxl“你去读那个缓存值”而不是“你帮我重新算一遍”。如果这个xlsx是由openpyxl直接创建的里面公式压根没被Excel打开计算过那文件里就没有缓存值data_onlyTrue自然读到None。解决办法有几种让上游用Excel打开文件并按一次保存把公式结果缓存刷新到文件里。如果只是临时用用LibreOffice这类可命令行启动的办公软件在服务器上重新计算并保存文件。自己写逻辑兜底先以data_onlyTrue读取遇到None再用data_onlyFalse打开一次拿到公式字符串再做后续处理。我常用的兜底思路是这样def get_cell_value(file_path, cell_ref): wb_value load_workbook(file_path, data_onlyTrue) ws_value wb_value.active val ws_value[cell_ref].value if val is None: wb_formula load_workbook(file_path, data_onlyFalse) ws_formula wb_formula.active val ws_formula[cell_ref].value return val注意这种写法对单个固定单元格是够用的如果要遍历整张表最好一次打开两个workbook同时维护两套值按单元格判断别每次都重复加载文件。4.2 合并单元格带来的空值前面已经提过合并单元格会导致其他区域变成None但在真实数据分析里这个问题经常结合遍历出现你的数据明明看起来完整打印出来却全是None。比如有些报表把“部门”列按多行合并只保留第一行的值后面的行都是空。如果你直接用openpyxl读取不做处理后面这些空值就会被当成缺失数据。遇到这种情况我的处理思路是先用区间识别合并区域再按上一行的值向下填充。也就是把合并单元格的左上角值“往下传”。下面是一个处理竖排列合并的示例for mr in list(ws.merged_cells.ranges): if mr.min_col mr.max_col: # 同列上下合并 fill_value ws.cell(rowmr.min_row, columnmr.min_col).value for r in range(mr.min_row 1, mr.max_row 1): ws.cell(rowr, columnmr.min_col).value fill_value这种处理很适合“部门列合并了但明细行需要显示部门名”的场景。做完之后再按行遍历数据就是完整的。还有一个隐藏问题是合并单元格如果出现在表头区域可能会导致表头字段名读取不上来。比如第一行和第二行合并过但你用iter_rows默认遍历第二行的表头字段就是None。遇到这个建议只读取合并区域左上角那一行作为表头或者先把表头区域按前面fill_merged方法铺平。4.3 数字、日期与百分比的类型识别openpyxl读取单元格时会自动映射数据类型但有些映射结果跟Excel里“看到的内容”不一样这也是很多人读出来之后发现对不上的原因。日期时间类型在工作表里会被读成datetime对象from datetime import datetime cell_val ws[B2].value if isinstance(cell_val, datetime): print(cell_val.strftime(%Y-%m-%d))如果单元格存的是数字openpyxl默认会保留数字类型不会给你转成字符串。但有一种情况要注意Excel里如果单元格是“文本”格式里面的数字会被存成字符串“123”而不是数字123。读出来之后做加法时就会报错。我一般在拿数字之前都做一层防御def to_float(value): if value is None: return None if isinstance(value, (int, float)): return float(value) if isinstance(value, str): text value.replace(,, ).replace(, ).strip() try: return float(text) except ValueError: return None return None百分比的坑更深。Excel里显示“12.5%”的单元格openpyxl读出来的value其实是0.125不是字符串“12.5%”。如果你直接格式化输出会变成“0.125”跟原表对不上。要用单元格的number_format来判断value ws[C3].value fmt ws[C3].number_format # 类似 0.0% if % in str(fmt) and isinstance(value, (int, float)): display_value value * 100同样的道理还适用于千分位、货币符号等格式openpyxl读取的是原始存储值不是Excel的显示值。如果你要复现“用户看到的数字”必须结合number_format做二次加工。5. 大文件与异常环境性能优化和安全防护5.1 read_only模式只读模式到底省了多少内存普通模式加载工作簿时openpyxl会把整个文件内容解析成内存里的对象逐行、逐单元格都创建Cell对象。文件小的时候无所谓一旦遇到几万行几十列甚至更大规模的表普通模式能让你的脚本内存占用直线上涨。解决办法是load_workbook时打开read_only模式wb load_workbook(大文件.xlsx, read_onlyTrue) ws wb.active for row in ws.iter_rows(values_onlyTrue): # 处理这一行 passread_only模式采用的是流式读取思路不会一次性把整张表都加载到内存里而是一行一行从底层XML文件里解析出来。配合values_onlyTrue一起使用性能表现会更好因为values_onlyTrue直接返回值不会去创建完整的Cell对象。但只读模式也有明显的代价。首先你不能再像普通模式那样方便地按坐标随机读取某个单元格最稳妥的方式就是从头到尾遍历行其次一些高级操作比如修改后保存、处理合并单元格、调整样式都是不可用的第三读取过程中对工作表的约束逻辑比普通模式松不适合做需要精确解析格式的场景。我自己的选择标准很简单文件行数在几千行以内普通模式随便用超过几万行或者明确知道文件很大毫不犹豫开read_only模式。还有一点read_only模式下千万不要因为图方便就写data list(ws.values)这会把流式读取的好处全丢掉等于又把整表加载进了内存。5.2 防御式代码文件不存在、权限异常与空表我是越来越认同一个观点脚本的报错信息要能说人话。openpyxl如果遇到非法文件抛出来的异常一般比较底层比如BadZipFile新手看到会懵。所以正式脚本里我会包一层异常处理from pathlib import Path from zipfile import BadZipFile from openpyxl import load_workbook from openpyxl.exceptions import InvalidFileException def load_workbook_safe(file_path): path Path(file_path) if not path.exists(): raise FileNotFoundError(f文件不存在: {path.resolve()}) try: wb load_workbook(path, read_onlyTrue, data_onlyTrue) except BadZipFile: raise ValueError(文件不是有效的xlsx格式可能被损坏或不是xlsx文件) except InvalidFileException: raise ValueError(openpyxl无法识别该文件请确认扩展名为.xlsx/.xlsm) except PermissionError: raise PermissionError(文件权限异常请检查是否被占用或没有读取权限) if len(wb.sheetnames) 0: raise ValueError(工作簿里没有任何工作表) return wb这段代码里有一个容易被忽略的点报错信息要尽量明确。我在实际工作中就遇到过一个文件被另一个进程占用导致读取权限异常的情况如果不去捕获PermissionError默认报错看着就像文件坏掉一样排查方向完全跑偏。除了文件层面的异常空表也要做防御。有些自动化流程会生成内容为空的xlsx但工作表还在read_only模式遍历时可能连表头都取不到。这种场景我一般会在循环外先加判断if ws.max_row 2: print(这个工作表只有一行或没有数据跳过) continue还有一个安全细节openpyxl不支持加密保护的xlsx。如果你拿到一个带打开密码的文件load_workbook直接会报错。这个不是openpyxl的bug是它版本里就没有实现加密文件的解析。遇到加密文件需要让上游先解密或者用支持密码的工具另存一份再处理。6. 实战把12个月度报表合并成一个结构化结果6.1 需求与数据样张结合上面的知识点我来还原一个真实的读取工作簿小任务。某数据小组每个月会收到一份销售周报工作簿名是“年度销售周报.xlsx”里面有12张工作表分别叫“第1周”“第2周”一直到“第12周”。每张表的行数可能不一样列也不完全对齐但至少都有“城市”“销售额”“日期”这三个字段。任务是把所有周报的数据读出来按城市汇总销售额输出一个摘要字典。先模拟一下数据结构。工作表“第1周”大致长这样城市销售额日期A市12800.52025-03-01B市96322025-03-01C市104202025-03-01A市87002025-03-02另一张“第2周”可能城市列不在A列日期列也可能有“2025/03/08”这种格式。这种“表结构不固定”才是真实世界最常见的Excel问题。6.2 核心实现代码下面这个脚本就是把前面的知识串起来用表名导航、用iter_rows遍历、按表头映射列索引、对空行跳过、对字符串数字做转换。from openpyxl import load_workbook from collections import defaultdict from datetime import datetime def parse_weekly_file(file_path): wb load_workbook(file_path, data_onlyTrue, read_onlyFalse) summary defaultdict(lambda: {amount: 0.0, count: 0}) for sheet_name in wb.sheetnames: ws wb[sheet_name] rows ws.iter_rows(values_onlyTrue) try: header next(rows) except StopIteration: print(f跳过空表: {sheet_name}) continue header_map {} for idx, name in enumerate(header): if name is not None: header_map[str(name).strip().lower()] idx if 城市 not in header_map or 销售额 not in header_map: print(f跳过 {sheet_name}: 缺少必要列实际列名 {list(header_map.keys())}) continue city_idx header_map[城市] amount_idx header_map[销售额] date_idx header_map.get(日期) for row in rows: if all(item is None for item in row): continue city str(row[city_idx]).strip() if row[city_idx] is not None else 未填写 amount_raw row[amount_idx] amount to_float(amount_raw) if amount is None: continue summary[city][amount] amount summary[city][count] 1 if date_idx is not None: date_value row[date_idx] if isinstance(date_value, datetime): pass return summary def to_float(value): if value is None: return None if isinstance(value, (int, float)): return float(value) if isinstance(value, str): text value.replace(,, ).replace(, ).strip() try: return float(text) except ValueError: return None return None if __name__ __main__: result parse_weekly_file(年度销售周报.xlsx) for city, info in result.items(): print(city, info)这里我特意没有引入pandas因为如果你只需要读完数据并汇总用openpyxl加基础数据结构就够了依赖少、部署简单。代码里header_map的作用很关键它会把每一列的表头转成小写再映射索引这样不同周报表头顺序不一致时也能自动定位到正确的列。再补充一个细节data_onlyTrue这里是为了读取销售额的缓存值。如果销售额列本身是手工填的数字不带公式用data_onlyFalse其实也能得到正确结果。但为了保险我依然写了data_onlyTrue因为一旦某些行的销售额是公式算出来的不加的话你会读到一个公式字符串后面转float就会报错。6.3 运行结果与小坑回顾跑完脚本后输出大概是这样A市 {amount: 158320.0, count: 12} B市 {amount: 147500.0, count: 11} C市 {amount: 129800.0, count: 10}看上去很顺利但这个脚本在调试过程中踩过两个小坑。第一个坑是某周报表的城市列里出现了合并单元格A市下面的几行城市字段为None直接被脚本归类成了“未填写”。这个问题我用前面说的合并区域识别思路解决先判断merged_cells.ranges把合并后的空值按左上角值填充再做后续处理。如果你遇到类似情况记得在进入iter_rows之前先做一次合并值铺平操作。第二个坑是某张表里销售额列有些单元格被存成了文本里面带了千分位逗号比如“12,800.5”。直接用float会报错我在to_float函数里做了replace(,, )这才把数据救回来。别觉得这种数据规范问题少见真实业务里各种手填表格什么格式都有。最后分享一个我的习惯写读取脚本时先在主逻辑后面加一段“表结构概览”输出打印每张工作表的表名、max_row和字段顺序。这样即使脚本后面跑出奇怪结果第一反应是去看数据本身而不是怀疑读取逻辑。很多bug其实就是源数据的结构跟你预想的不一样先摸清底细再动手解析能省下一大半排查时间。