Python操作Excel全攻略：openpyxl读写、封装与性能优化实战

做后端、爬虫或者数据分析的同学应该都经历过被 Excel 支配的时刻几十个 sheet、上千行的报表、每天手动复制粘贴改格式改完还要发邮件汇报。我第一次认真思考能不能用 Python 把这套流程自动化的时候第一个搜索到的就是 openpyxl。试了一圈同类库之后它成了我处理 Excel 的首选而且一用就是好几年。这篇文章把我这几年的实战经验整理出来从选型逻辑、读写细节、样式公式处理到如何把 Excel 操作封装成可复用的工具类再到大数据量场景的性能优化和日常高频坑点。不管你是刚学 Python 的新手还是写了几年脚本的老手照着这篇文章的思路走基本能覆盖日常工作中八成以上的 Excel 自动化需求。1. 为什么是 openpyxlExcel 处理库的选型逻辑网上搜Python Excel能蹦出一堆库名xlrd、xlwt、xlsxwriter、pandas、openpyxl。新手很容易被绕晕甚至装上好几个库结果读写各用一套代码写到一半就精神分裂。我先说结论如果你处理的是 .xlsx 文件并且既要读又要写还要尽量保留原文件的样式openpyxl 就是最省心的默认选项。1.1 常见库的功能边界库名能读能写支持格式特点与限制xlrd是否主要 .xls新版本已放弃 .xlsx 读取支持xlwt否是主要 .xls只能写旧格式样式能力弱xlsxwriter否是.xlsx写样式和图表很强但不能读取pandas借用引擎借用引擎多种数据分析方便格式和样式基本丢失openpyxl是是.xlsx / .xlsm读写兼修样式保留较好这张表里最扎心的一点是分类能读的不能写能写的不能读。早期我做报表自动化时先用 xlwt 生成文件后面要改模板再引入 xlrd两套 API 完全不兼容数据在 Excel、DataFrame、list 之间来回倒腾出了类型问题你都不知道怪谁。1.2 openpyxl 的优势与限制openpyxl 的最大优势就是一条龙它直接操作 .xlsx 的 XML 结构能够读取单元格的值、样式、公式、合并单元格、条件格式也能创建带样式的文件。除此之外它是纯 Python 实现不依赖系统里的 Office 组件服务器上装好解释器就能跑这在 Linux 环境里是决定性优势——你总不能为生成一个表格专门去装一套 WPS。当然它也有明显边界不支持 .xls 老格式。遇到老文件要么先用 Excel/WPS 转成 .xlsx要么临时配合 xlrd 读取然后再交给 openpyxl 处理。另外openpyxl 不会计算公式结果它保存的是公式字符串这个后面我会专门讲。2. 环境准备与第一个读写 Demo选型定了下一步就是把它用起来。这一节从安装开始重点讲讲很多人忽略的离线安装思路然后给你一个最小可运行的读写例子。2.1 安装与离线安装的完整思路联网环境下没什么好说的pip install openpyxl但实际工作中我更常遇到的是内网服务器。之前有一次数据平台迁移目标机器连外网都没有我当时整理了一套离线安装的固定流程分享给你在上海通的外网机器上执行pip download openpyxl -d ./offline_packages把包本身和它的依赖一起拉下来。openpyxl 有一个基础依赖et_xmlfile所以下载目录里至少会有两个文件。把整个目录拷贝到目标机器执行pip install --no-index --find-links./offline_packages openpyxl。如果你连pip download这一步都不方便也可以直接去 PyPI 官网下载对应版本的 wheel 文件再手动pip install 文件名.whl。老规矩wheel 文件是有平台和 Python 版本区分的注意看文件名后缀装的时候提示不支持就换一个。2.2 工作簿、工作表、单元格的三层结构上手之前先理解 openpyxl 的对象模型。它和 Excel 的文件结构是一一对应的Workbook对应整个 Excel 文件也就是你看到的.xlsx。Worksheet对应一个 sheet 页签。Cell对应具体的单元格用 列字母 行号 定位比如A1。日常编码里最常接触的就是这个三层结构。加载文件就是load_workbook取某个 sheet 就是wb[Sheet1]读写单元格就是ws[A1]或者ws.cell(row1, column1)。理解了这个层级openpyxl 的常见操作基本都能顺下来。2.3 最小可运行的读写例子给你一个非常朴素的 Demofrom openpyxl import Workbook, load_workbook # 写入 wb Workbook() ws wb.active ws[A1] 姓名 ws[B1] 得分 ws.append([张三, 88]) ws.append([李四, 95]) wb.save(demo.xlsx) # 读取 wb2 load_workbook(demo.xlsx) ws2 wb2.active for row in ws2.iter_rows(min_row1, max_row3, values_onlyTrue): print(row)注意一个细节ws[A1] value是给指定单元格赋值而ws.append([...])是往当前已有内容的下一行追加一行。刚创建的空 sheet 默认只有一行所以第一次append落到第 2 行上面的代码即使用起来没问题实际写正式工具时很多人就在这里埋下了错位隐患。建议初始化后先自己读一遍循环结果确认行号符合预期再继续。3. 核心读写能力拆解数据、样式、公式与合并单元格跑通 Demo 只是开始真正处理业务表格时你会发现需求永远不只是把值塞进去。这节把读取、样式、公式、合并单元格这些高频场景逐个过一遍。3.1 数据读取的完整姿势读取最常用的是一行行取配合values_onlyTrue直接拿到值类型是元组wb load_workbook(report.xlsx) ws wb[明细] for row in ws.iter_rows(min_row2, values_onlyTrue): name, score row[0], row[1] # 处理业务逻辑iter_rows是按行扫描如果想按列扫描对应的是iter_cols。还有一种场景是在 Excel 里快速定位某个字符串比如在几百行数据里找出所有包含待处理的单元格hits [] for row in ws.iter_rows(values_onlyFalse): for cell in row: if cell.value and 待处理 in str(cell.value): hits.append(cell.coordinate)这里我没用values_onlyTrue因为需要拿到cell.coordinate来获取单元格地址。两种模式一个拿纯值、一个拿 Cell 对象应用场景完全不同别混着用。3.2 写入进阶样式与格式控制写入最核心的两个诉求控制外观和控制数据类型。外观主要通过Font、PatternFill、Alignment、Border这四个类组合实现from openpyxl.styles import Font, PatternFill, Alignment, Border, Side header_font Font(name微软雅黑, size11, boldTrue, colorFFFFFF) header_fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) center_align Alignment(horizontalcenter, verticalcenter) thin_border Border( topSide(stylethin), bottomSide(stylethin), leftSide(stylethin), rightSide(stylethin) ) for cell in ws[1]: # 第一行所有单元格 cell.font header_font cell.fill header_fill cell.alignment center_align cell.border thin_border纯手工写这些代码容易又臭又长所以我的习惯是把表头样式和内容样式定义成常量写进一个配置模块后面封装章节会展开讲。另外别忘了两个低调但高频的设置列宽和行高ws.column_dimensions[A].width 20 ws.row_dimensions[1].height 30中文内容如果不调列宽导出的表格一打开就是###或者被截断这个坑我在早期踩得很惨现在只要生成文件必先循环设置列宽。3.3 公式与条件格式的处理方式写入公式很简单直接把字符串赋值给单元格ws[D2] IF(C290,优秀,及格) ws[E2] SUM($B$2:$B$100)但这里有个很多人不知道的坑openpyxl 不负责计算保存后你用 openpyxl 再读回来拿到的还是公式字符串不是结果值。如果你希望读取时能拿到 Excel 缓存的最终结果需要在load_workbook时传data_onlyTruewb load_workbook(report.xlsx, data_onlyTrue)这个参数的行为是读取文件里由 Excel/WPS 计算并缓存的结果。如果这个文件从未被 Excel 打开过、没有计算缓存那你拿到的会是None。所以业务流程上要注意要么让文件先被 Excel 打开保存一次要么你在脚本里完成计算别依赖 openpyxl 给你算出来。条件格式也有原生支持比如把低于 60 分的单元格标红from openpyxl.formatting.rule import CellIsRule ws.conditional_formatting.add( B2:B100, CellIsRule(operatorlessThan, formula[60], fillPatternFill(start_colorFFC7CE, end_colorFFC7CE, fill_typesolid)) )合并单元格类似直接指定范围即可merge_cells之后值只保存在左上角那个单元格里读取时要注意右下角区间的值是None。4. 封装实战把 Excel 操作变成配置式工具很多教程讲到样式、公式就结束了但真正到业务里你会发现最痛苦的不是不会写而是每个脚本里都塞了 200 行重复的样式代码。这节是本文的核心聊聊怎么把 Excel 操作封装成配置式工具顺便讲讲 Python 对象怎么和表格行做映射。4.1 为什么要封装三个真实的重复劳动场景我复盘过去几年的项目发现重复最多的是这三件事同样的导出逻辑散落在十几个脚本里。导出订单要写一遍表头样式导出用户信息又写一遍改一个主题色要改十几个文件。Python 对象和 Excel 行没有直接关联。数据存在dataclass或者字典里导出时得手动按字段顺序塞进列表少一个字段就错位。异常处理完全缺失。文件被占用、sheet 不存在、日期格式不对这些错误每个脚本抛出各种不同的报错排查成本高。封装的核心目标就一句话调用方只需关心数据长什么样不用关心Excel 怎么画。4.2 一个实用的 ExcelWriter 工具类我设计了一个轻量级的ExcelWriter核心思路是列配置驱动。先定义每一列的表头、字段名、宽度和数据格式然后批量写入行数据from dataclasses import dataclass, fields from datetime import datetime from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter dataclass class ColumnConfig: header: str # 表头显示文字 key: str # 数据源中的字段名 width: int 18 # 列宽 number_format: str None # 单元格格式如 yyyy-mm-dd class ExcelWriter: def __init__(self, sheet_nameSheet1): self.wb Workbook() self.ws self.wb.active self.ws.title sheet_name self._columns [] self._row_idx 1 def set_columns(self, columns: list[ColumnConfig]): self._columns columns # 写表头并套用样式 for i, col in enumerate(columns, start1): cell self.ws.cell(row1, columni, valuecol.header) cell.font Font(boldTrue, colorFFFFFF) cell.fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) cell.alignment Alignment(horizontalcenter, verticalcenter) self.ws.column_dimensions[get_column_letter(i)].width col.width self._row_idx 2 def add_row(self, data: dict): if not self._columns: raise ValueError(请先调用 set_columns 设置列。) for i, col in enumerate(self._columns, start1): value data.get(col.key) cell self.ws.cell(rowself._row_idx, columni, valuevalue) if col.number_format: cell.number_format col.number_format if isinstance(value, datetime): cell.number_format col.number_format or yyyy-mm-dd self._row_idx 1 def save(self, path): self.wb.save(path)使用起来非常直观writer ExcelWriter(订单导出) writer.set_columns([ ColumnConfig(订单号, order_id, width22), ColumnConfig(下单时间, created_at, width22, number_formatyyyy-mm-dd hh:mm), ColumnConfig(金额, amount, width12, number_format#,##0.00), ]) writer.add_row({order_id: A1001, created_at: datetime.now(), amount: 199.9}) writer.save(orders.xlsx)这个类把列定义和数据填充解耦了以后调整表头宽度、增加新列只改set_columns那一处就行。如果你要更复杂的表头合并、多级样式在这个基础上扩展即可。4.3 Python 对象与行数据的自动映射上面的add_row接收的是字典但很多业务代码里数据是dataclass对象。封装时要顺手解决对象→字典的转换这里推荐一个省事做法用dataclasses.asdict。from dataclasses import asdict dataclass class Student: name: str score: int students [Student(张三, 88), Student(李四, 95)] for stu in students: writer.add_row(asdict(stu))如果你的对象是普通类就手动建一个to_dict方法返回字典。这样做的收益在于表结构调整时字段名的修改集中在配置和数据模型里不会在整个脚本中散落一堆row[0]、row[1]这种魔法下标。这也是封装在实际项目里最能提效的地方——把 Python 世界的东西翻译成 Excel 世界的表格翻译规则只写一遍。5. 大数据量场景下的性能优化read_only 与 write_only日常小表格随便造但一遇到几十万行的数据直接load_workbook就能把你的服务器内存吃干净。openpyxl 提供了两种流式模式很多人知道名字却没用对这节把原理和取舍讲清楚。5.1 全量加载模式的内存困局默认情况下load_workbook会把整个工作表的 XML 解析成一个对象树放进内存。几十个 sheet 的大文件加载时内存轻松突破 500MB要是机器内存本来就不大直接 OOM。生成同理Workbook()默认也把全部内容缓存着最后才序列化。我印象最深的一次是处理一个 80MB 左右的日志明细表脚本跑在 2G 内存的云服务器上load_workbook(large.xlsx)还没执行完就被系统 kill 掉了。当时换用read_onlyTrue之后同样的文件几秒读完内存占用从接近 1GB 掉到几十 MB。5.2 流式读取与流式写入的正确姿势读取大文件用read_onlyTrue配合iter_rows逐行处理wb load_workbook(large.xlsx, read_onlyTrue) ws wb.active for row in ws.iter_rows(values_onlyTrue): # 处理完一行、释放一行内存不会随数据量增长 process(row) wb.close()生成大文件用write_onlyTrue。注意write_only 模式下不能用ws[A1] value这种方式赋值只能用append按行追加而且样式能力非常有限更适合纯数据导出wb Workbook(write_onlyTrue) ws wb.create_sheet(大数据) ws.append([姓名, 得分]) # 第一行 for record in generator_from_db(): ws.append([record.name, record.score]) wb.save(huge.xlsx) wb.close()5.3 性能对比与取舍建议模式读取占用写入方式样式支持适用场景默认模式高任意单元格完整中小文件需要精细控制布局read_only极低不支持写入只读能力有限大文件逐行扫描write_only不涉及仅 append弱大文件纯数据落盘我个人的取舍标准是文件行数在 1 万以上读取一定用read_only只需要导出数据、样式不是重点写入用write_only需要精美报表格式、行数又不多才回到默认模式。还有一种常见组合大文件 最终需要正式格式。我的做法是先流式读取处理再把小结果写入默认模式的新文件里套上样式两边都占了便宜。6. 高频踩坑实录复现、定位与修复最后这部分把我这些年踩过的、读者群里被问得最多的几个坑一次性列清楚。每个都是真实场景复现看完能帮你省下不少排查时间。6.1 日期变成了数字或字符串写入日期时如果不设置number_formatExcel 会当成无格式的数字串处理显示成 45123 这种序列值读取时又是一番景象Excel 里的日期会被 openpyxl 读成datetime.datetime而不是字符串。解决方案在写入端给单元格显式指定格式cell.value datetime.now() cell.number_format yyyy-mm-dd hh:mm:ss读取端如果你希望拿到字符串来拼接、渲染就自己格式化if isinstance(cell_value, datetime): cell_value cell_value.strftime(%Y-%m-%d)6.2 公式读不到值、写了公式却不计算前面已经讲了一次这是所有 openpyxl 使用者必踩的坑。关键是区分两个load_workbook参数默认拿到公式data_onlyTrue拿到缓存值。如果你读出来一堆None先确认这个文件到底有没有被 Excel 计算并保存过。写公式的场景我的建议是能不依赖公式就尽量不用脚本里算好结果直接写值如果业务一定要公式再考虑生成后用程序打开重算或者明确告诉用户打开文件后公式才会计算。6.3 前导零丢失、长数字变成科学计数法写入00123、620102199001011234这类数据Excel 默认会去掉前导零、把长数字转成科学计数法。解决办法是写入时把单元格格式设为文本cell.value 00123 cell.number_format 另外要提醒的是读取时这类文本单元格返回的是字符串。如果你之前已经被 Excel 改成了科学计数法显示那原始值可能已经在转换过程中丢了这种只能从源头上防。6.4 合并单元格、样式丢失与文件占用合并单元格读取时有个经典问题真正有值的是左上角单元格其他区域是None循环时容易漏数据。处理方法是先构建一个合并区域 → 左上角单元格的映射for merged_range in ws.merged_cells.ranges: print(merged_range, ws.cell(merged_range.min_row, merged_range.min_col).value)样式丢失分两种情况一种是用 openpyxl 读取后再保存部分复杂特性比如某些图表、数据验证可能被破坏这是底层 XML 能力限制没有彻底解法只能提醒你保留一份原始文件做备份。另一种是文件在 Excel 里被打开着脚本去保存会直接抛PermissionError这也是很多后台任务挂在半路的原因写脚本时记得做异常捕获和重试。最后分享一个我自己养成的习惯每次用 openpyxl 生成的正式报表我都会先跑一段冒烟测试——重新加载刚保存的文件抽查表头、行数、几个关键单元格的值确认没有类型错乱和错位再交付。这个习惯帮我挡掉了不少线上事故也希望所有把 Python 和 Excel 结合起来的同学都能带着这套方法论少踩坑、多产出。