openpyxl完全指南：Python Excel读写与样式处理实战

这标题看着简单实际上手写一阵子就能发现openpyxl这库的坑全藏在细节里。我从最早用xlrd读Excel、再用xlwt写、中间还得靠xlutils来回倒腾到后来换成openpyxl一把梭中间经历了不少折腾。这篇就把我日常用得最多的读写场景完整梳理一遍代码都是可以直接抄走改改就用的。1. 为什么偏偏是openpyxlExcel处理库的选型逻辑1.1 主流Excel处理库的横向对比Python处理Excel的库不少但各有各的脾气。如果你没经历过选错库然后返工的过程我帮你把常见的几个按实际体验排个序。xlrd老牌读库但注意新版本已经不支持xlsx了只读旧格式的xls。很多人网上搜教程还在用xlrd读xlsx直接报错“格式不支持”这是新手最容易踩的第一个坑。读取性能不错但功能边界很死只能读不能写。xlwt专门写xls格式生成的还是老式二进制格式不支持xlsx。最难受的是它默认65536行上限超过直接抛异常现在随便一个数据表就破万行了根本不够用。xlutils本来是给xlrd和xlwt搭桥用的把读到的文件复制一份再修改流程绕来绕去而且对格式的保留效果很差处理完经常出现样式错乱。pandas如果你要处理的是“表格数据”而不是“Excel文件本身”用pandas的read_excel/to_excel确实快但它内部还是调用了openpyxl或xlrd做底层解析。pandas擅长的是数据计算和聚合但要做单元格合并、设置样式、调整行高列宽、冻结窗格这些精细操作pandas根本使不上劲。它用openpyxl做引擎后又包了一层等于中间隔了层纱。openpyxl直接读写xlsx格式读写一体支持样式、公式、图表、图片操作模型直观文档也全。xlsx本身就是zip压缩包内部是xml文件openpyxl本质就是在操作这些xml结构但封装得足够舒服你不用关心底层的混乱格式。我现在的工作流基本就一个原则单纯算数据用pandas但最终落盘成Excel一定用openpyxl来做样式和布局。这两者不是替代关系是协作关系。pandas算完导出的裸表格太丑拿openpyxl重新包装一遍才能交出去。1.2 个人项目中的选型依据之前做过一个数据报表自动化的需求每天要从接口拉一批数据生成Excel发给业务方看。最初用pandas一把梭to_excel写完就交了。结果业务方提了一堆要求标题要合并单元格、表头要带底色、数字要千分位格式、列宽要自适应、还要冻结首行。pandas这些全都做不了或者做得很别扭。后来全部改成openpyxlpandas算数据openpyxl排版。比如你可以一行代码把DataFrame数据导入工作表from openpyxl import load_workbook from openpyxl.utils.dataframe import dataframe_to_rows wb load_workbook(template.xlsx) ws wb.active # 把DataFrame逐行写入已有工作表 for row in dataframe_to_rows(df, indexFalse, headerTrue): ws.append(row) wb.save(report.xlsx)这一下就把数据处理和表格美化两个问题彻底分开了。选库的时候先想清楚你的核心需求是什么如果只是把结构化数据存成Excel给机器读pandas够了但只要是给人看的表格最终都躲不开openpyxl。人看表格就讲究格式格式就是openpyxl的强项。2. 环境准备与工作簿加载先让文件“打开”在内存里2.1 安装与版本选择安装很简单pip一把梭pip install openpyxl但要注意几点。Python版本最好在3.7以上openpyxl现在新版对老版本Python的兼容性已经在逐步收紧了。版本号我建议别用最新尝鲜稳定版就行比如3.1.x系列就非常稳生产环境跑很久没出过问题。如果是在公司内网环境装不了可以选择下载wheel文件离线安装。还有个常见场景你机器上同时有pandas和openpyxlpandas的ExcelWriter引擎版本如果和openpyxl不匹配写入时可能报错这时候把两个库一起升级到兼容版本就好。2.2 工作簿、工作表、单元格三层模型很多人一开始会懵openpyxl的对象关系其实特别清晰就三层Workbook工作簿对应一个Excel文件你可以理解成一本书的封面和目录。代码里的变量名习惯用wb。Worksheet工作表对应文件中底部的sheet标签类似书里的每一章。变量名常用ws。Cell单元格对应表格里的每个格子通过行列坐标定位。比如A1就是第一列第一行的交叉点。这三层是一路向下的wb打开文件wb.sheetnames拿到所有工作表名字wb[Sheet1]选工作表ws[A1]取单元格。我一般会把这句记在脑子里一切操作都是“工作簿→工作表→单元格”的逐级下钻从不下钻就是最常犯的错误。2.3 打开文件的三种姿势先导入库然后根据场景选打开方式from openpyxl import Workbook, load_workbook # 场景一新建空白工作簿 wb Workbook() # 场景二读取已有xlsx文件最常用 wb load_workbook(data.xlsx) # 场景三读取已有xlsx但忽略样式和格式加了data_only参数 wb load_workbook(data.xlsx, data_onlyTrue)场景三里这个data_onlyTrue是个大坑后面会单独说。现在先记住如果你只关心单元格里的计算值不关心公式本身就加data_onlyTrue如果需要修改公式就别加。打开之后操作工作表的常用方式# 获取所有工作表名字 print(wb.sheetnames) # [Sheet1, Sheet2] # 获取当前活动工作表 ws wb.active # 按名字取工作表 ws wb[Sheet1] # 新建工作表 ws_new wb.create_sheet(新表, position0) # position0插到最前面 # 删除工作表 del wb[要删除的表] # 重命名 ws.title 改名了我之前接手过一个乱糟糟的报表程序里面每次运行都新建一个“Sheet1、Sheet1_1、Sheet1_2”这样的工作表就是因为对Workbook对象不熟悉反复调用wb Workbook()而不是在同一个wb上create_sheet。搞清楚这三层关系之后再复杂的文件结构都能理清楚。3. 写入操作从空白工作簿到一张能看的报表3.1 单元格写入的四种方式写入单元格是我用得最多的操作但大多数教程只教一种写法实际工作里四种都得掌握。from openpyxl import Workbook wb Workbook() ws wb.active # 方式一按坐标直接赋值最简单 ws[A1] 姓名 # 方式二用cell方法指定行列适合循环 ws.cell(row1, column2, value年龄) # 方式三append追加写入适合从列表批量写入 row_data [张三, 25, 北京] ws.append(row_data) # 自动追加到第一行空行 # 方式四一次性写入多行数据 multi_rows [ [李四, 30, 上海], [王五, 28, 广州], ] ws.append(multi_rows) # 注意append不支持直接传二维列表等等方式四直接传二维列表会报错的正确做法是循环appendfor row_data in multi_rows: ws.append(row_data)方式三和方式四里的append是我最推荐的批量写入方式。它的特点是永远从第一行无数据的行开始写你不用自己维护行号递增省掉手写row_index变量到处1的烦恼。以前我用cell方式循环写入一旦中间插入一行后面全乱套换append再也没出过这种问题。3.2 三种写入场景及其适用场景对比我把写入场景归纳成三类下面这个表可以帮你快速判断用哪种场景推荐方式原因固定位置的表头、标题直接坐标赋值位置确定语义清晰A1就是标题循环写入大量结构化数据append批量追加自动定位空行代码简洁不容易错行从DataFrame转到Exceldataframe_to_rows再append处理数据用pandas排版用openpyxl各取所长实践里最忌讳的就是拿着append写所有数据结果想往中间某个位置插一个单元格都费劲。写入之前先画一遍表格布局草图标清楚哪些是固定位置、哪些是流式数据再决定写法和循环结构。3.3 样式设置让报表“能看”的基本功数据写进去了但光秃秃的报表交出去会被业务方吐槽。openpyxl的样式系统我承认刚接触时有点繁琐但符合“每个属性都要从模块导入”的设计。下面是我封装的常用样式函数from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter def style_header(ws, row_idx, columns): 设置表头样式加粗、背景色、居中 header_font Font(name微软雅黑, size11, boldTrue, colorFFFFFF) header_fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) center Alignment(horizontalcenter, verticalcenter) for col_idx in range(1, columns 1): cell ws.cell(rowrow_idx, columncol_idx) cell.font header_font cell.fill header_fill cell.alignment center def auto_width(ws, min_width10, max_width40): 根据单元格内容自动调整列宽 for col_cells in ws.columns: col_letter get_column_letter(col_cells[0].column) max_len min_width for cell in col_cells: if cell.value: # 中文按2个字符宽度估算 text_length sum(2 if ord(ch) 127 else 1 for ch in str(cell.value)) max_len max(max_len, text_length 2) ws.column_dimensions[col_letter].width min(max_len, max_width) def freeze_and_filter(ws, freeze_cellA2, ref_rangeNone): 冻结首行并添加筛选器 ws.freeze_panes freeze_cell if ref_range: ws.auto_filter.ref ref_range用的时候就这样# 写入表头 headers [姓名, 年龄, 城市] ws.append(headers) # 写入数据 for item in data_list: ws.append(item) # 美化 style_header(ws, 1, len(headers)) auto_width(ws) freeze_and_filter(ws, A2, fA1:C{ws.max_row}) # 设置行高标题行高一点 ws.row_dimensions[1].height 25有几个容易被忽略但又特别实用的设置合并单元格用ws.merge_cells(A1:C1)取消用unmerge_cells合并后只保留左上角的值边框对象Side配合Border四边设置注意我国表格的审美偏好比较偏向左边框、细边框你全设成粗框就会显得很“学习资料”数字格式用cell.number_format #,##0.00这会直接显示千分位不用动原始数据。样式调整是个细活每次改完记得save重新打开看效果别靠脑补。4. 读取操作把Excel里的数据准确“捞”出来4.1 读取单元格与区域的几种姿势读取相比写入稍微简单点我不直接给代码先讲清楚你面对一个未知表格时怎么问问题。你用openpyxl打开一个别人发的Excel第一件事永远是搞清楚这张表长什么样from openpyxl import load_workbook wb load_workbook(别人发的表.xlsx) ws wb.active # 1. 看表头在不在第一行最大行列是多少 print(ws.max_row, ws.max_column) # 2. 读单个单元格 value ws[A1].value # 或者用行列坐标 value ws.cell(row1, column1).value # 3. 读整个区域的值返回二维数组 all_data ws.values # 这是个生成器循环打印 for row in all_data: print(row) # 4. 读指定区域的切片 for row in ws.iter_rows(min_row2, max_row5, min_col1, max_col3, values_onlyTrue): print(row)values_onlyTrue这个参数相当实用如果不加iter_rows返回的是Cell对象你还得再取.value加了你拿到的直接就是纯值。有人觉得直接用ws.values就完了但那个是全表扫描如果你只要中间几列性能浪费很大。4.2 遍历数据的惯用手法掌握了上面这些基础访问之后遍历就顺理成章了。我给你一个比较标准的遍历模式# 从第2行开始逐行读取跳过表头 for row in ws.iter_rows(min_row2, values_onlyTrue): name, age, city row # 假设表头是姓名、年龄、城市 if name is None: continue # 跳过空行 print(f{name} - {age} - {city})这里有个容易踩的坑ws.max_row获取的是“曾经被编辑过的最大行”如果表中间有一行被删掉过行号会出现空洞。你先测一下它的值和实际行数对不对得上如果对不上要么手动用iter_rows限定范围要么读取时做None判断跳过空行。按列读取的场景我也常遇到# 单独读某一列 col_letter B col_values [cell.value for cell in ws[col_letter] if cell.value is not None] # 一次性读所有列返回的是每列的元素列表 for col_cells in ws.columns: col_name col_cells[0].value # 第一个单元格当作列名 col_data [cell.value for cell in col_cells[1:] if cell.value is not None]读取最需要注意的是数据类型保留。Excel在openpyxl眼里是有类型的字符串、数字、日期、布尔值。你读出来是什么就是什么比如Excel里的日期是datetime.datetime对象不是字符串。很多新手拿到日期直接str()转字符串结果格式变成“2024-01-15 00:00:00”。我的习惯是读的时候判断一下类型再做格式化from datetime import datetime cell_value ws[B2].value if isinstance(cell_value, datetime): cell_value cell_value.strftime(%Y-%m-%d)4.3 带格式数据的读取边界这里必须提醒一个openpyxl的根本限制它读不到你Excel里肉眼看到的“显示值”。你看到的表格里可能是“1,234.56”但单元格里的原始值其实是1234.56你看到的是“2024/1/15”但底层值可能是日期对象。数据本身没丢只是“给人看”和“给程序读”是两套东西。如果你的下游程序需要的是显示格式的字符串那得自己写代码模拟Excel的显示逻辑比如根据number_format判断再加千分位。千万不要指望openpyxl能替你完成这一步。遇到这种显示值需求的场景我一般分两条路走如果数据量不大直接用openpyxl读原始值然后用Python的字符串格式化自己处理如果数据量大且显示格式非常复杂比如条件格式、自定义格式我会考虑用Excel本身打开另存为CSV或者用其他专业库绕开format的盲区。5. 实战中绕不开的坑与性能调优5.1 公式存读与data_only参数公式这块我估计是初学者遇到最多的困惑因为光是data_onlyTrue和data_onlyFalse很多人就搞不清楚自己该用哪个。openpyxl不干“计算”这件事它只负责把公式字符串存进单元格。比如你让ws[C1] SUM(A1:B1)它真就把这个公式写进文件了。Excel打开文件时会自动计算并显示结果。但是——如果你用openpyxl读这个文件不加data_only的时候你拿到的值是公式字符串“SUM(A1:B1)”不是算好的结果。加了data_onlyTrue你拿到的才是这个单元格的“缓存值”也就是Excel上次保存时算出来的结果。问题来了如果一个文件生成后从没被Excel打开保存过那么它的缓存值就不存在data_onlyTrue读出来是个None。这个坑特别隐蔽我见过很多同事调了半天代码发现读出来全是None然后怀疑人生。解决办法很简单要么生成文件后用Excel或其他能计算并重新保存的工具过一遍要么自己用Python公式库计算一遍再写进去。我的建议很简单凡是你要读公式计算结果且文件是别人维护的Exceldata_onlyTrue没问题凡是你的脚本自己生成的文件又立刻读别用data_onlyTrue直接用False读公式字符串或者干脆绕开公式写入前先把算术结果算好存成值。5.2 大文件性能问题与读写模式我遇到过一份10MB左右的xlsx用普通模式load_workbook等了近30秒内存直接飙了几百MB操作起来还卡。问题就出在openpyxl为了支持随机访问所有单元格默认把所有工作表的全部内容都塞进了内存。解决方法是openpyxl提供的两种内存优化模式# 只读模式加载时只保留数据不加载样式对象 wb_r load_workbook(big.xlsx, read_onlyTrue) # 只写模式只能写入不能读但写入时内存占用极低 wb_w Workbook(write_onlyTrue)只读模式的用法是迭代不能用随机访问ws_r wb_r[Sheet1] for row in ws_r.iter_rows(values_onlyTrue): # 这里处理每一行数据 pass只写模式则是只有append不能改已有单元格ws_w wb_w.create_sheet() for row_data in big_data_list: ws_w.append(row_data) wb_w.save(output_big.xlsx)之前的报表需求数据20万行用默认的普通模式生成直接卡死换write_only模式后十几秒就完事了。这个模式跑完后打开文件看样式、合并、列宽一个不少非常能打。但注意write_only模式下你不能用ws[A1]这种方式去改单元格也不能合并单元格或设置样式。所以如果写入的内容需要复杂格式排版只能在数据量没那么大的时候用普通模式做精细操作超大文件且排版要求又不高时优先write_only。5.3 合并单元格与读取的相爱相杀合并单元格是表格美化常用的但读取时一定出幺蛾子。比如A1:A3合并了A1有值A2和A3用openpyxl读出来是None不是和A1相同的值。这是符合Excel底层逻辑的——合并后只有左上角那个格真实存在值。处理办法是在读取时反查合并区域def get_merged_value(ws, row, col): 读取单元格如果它在合并区域里返回合并区域左上角的值 for merged_range in ws.merged_cells.ranges: if row in range(merged_range.min_row, merged_range.max_row 1) and \ col in range(merged_range.min_col, merged_range.max_col 1): return ws.cell(rowmerged_range.min_row, columnmerged_range.min_col).value return ws.cell(rowrow, columncol).value写入时的合并也要注意顺序**先写入数据到左上角再合并。**如果先合并再写经常会把值写到被合并掉的隐藏格子上导致你这个值丢了。我一开始就犯过这个错写完合并且同一个文件再打开标题不见了查半天发现是写到被合并的犄角旮旯里了。5.4 日常用下来最顺手的一批小技巧对已有文件做模板化复用把表头、样式、列宽全部手工设置好存成一个模板文件程序里每次load进来然后追加数据这样样式代码几乎为零运行也快。这是我最推荐的方案比程序里反复设置样式省太多事。单元格写入时避免自动转类型Excel里输入“00123”会被自动转成123但openpyxl不会你写入字符串00123存出来就是00123。这是好事还是坏事取决于场景但你要知道这一点就不会因为订单号前导0丢失而抓狂。如果是从其他来源读数据再写入注意源数据先转成字符串。保存前检查覆盖风险wb.save(同名文件.xlsx)会直接覆盖旧文件。如果有重要历史文件先做备份或者用时间戳命名freport_{datetime.now().strftime(%Y%m%d_%H%M%S)}.xlsx。附一个完整的“洗数据”小工具我做数据处理前经常先用它探路def inspect_sheet(ws, max_preview5): 快速查看工作表的基本结构和前几行内容 print(f工作表名{ws.title}) print(f最大行列{ws.max_row} x {ws.max_column}) print(f合并区域{list(ws.merged_cells.ranges)}) print(前几行数据) for i, row in enumerate(ws.iter_rows(min_row1, max_rowmax_preview, values_onlyTrue), start1): print(f Row{i}: {row})拿到任何一张陌生表先跑一遍这个函数文件结构一目了然再决定后面怎么处理。6. 常用的一个完整读写范例把上面的内容串起来给你一个最小可用的报表示例。这个示例涵盖新建文件→写入数据→设置样式→保存重新打开→读取数据→打印结果。你把它当成模板改改就能用。from openpyxl import Workbook, load_workbook from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter # ---------- 写入部分 ---------- wb Workbook() ws wb.active ws.title 销售报表 headers [月份, 销售额, 利润] ws.append(headers) data [ [1月, 12000, 3000], [2月, 15000, 4000], [3月, 13000, 3500], ] for row in data: ws.append(row) # 总和行 ws.append([合计, sum(r[1] for r in data), sum(r[2] for r in data)]) # 表头样式 for cell in ws[1]: cell.font Font(boldTrue, colorFFFFFF) cell.fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) cell.alignment Alignment(horizontalcenter) # 列宽 for col in range(1, 4): ws.column_dimensions[get_column_letter(col)].width 18 # 冻结首行 ws.freeze_panes A2 wb.save(sales_report.xlsx) # ---------- 读取部分 ---------- wb2 load_workbook(sales_report.xlsx) ws2 wb2[销售报表] print(f数据范围{ws2.max_row}行 x {ws2.max_column}列) for row in ws2.iter_rows(values_onlyTrue): print(row)这个流程跑通之后你已经能应付日常90%的Excel读写需求。剩下那些更高级的场景比如图表、图片、数据透视表、条件格式都是在这个基础上加料而已主体框架不变。我在实际项目中最大的体会是**openpyxl最突出的优点是把“能用代码操作Excel”这件事的门槛压到了极低但代价是它并不能替代你理解Excel本身的数据模型——合并单元格会带来空值公式和缓存值是两套系统显示格式与原始值并不一致。**先把这些绕开你的读写代码就会稳得多。