先说结论如果你每天的工作里有一部分是和 Excel 报表打交道——填数据、调格式、汇总各个来源的表格、发周报——那 openpyxl 这个库确实值得花一个下午好好了解一下。它不是处理 Excel 的唯一方案但它是“用 Python 操作 xlsx 文件”这条路上最稳、最常用、生态最成熟的选择之一和 Pandas 搭配起来几乎是自动化办公的标配组合。这篇文章我会从最基础的单元格读写讲起一路聊到样式、公式、图表、大数据量优化最后用 Pandas 联动收尾并附一个可以直接改改拿去用的自动化日报汇总脚本。适合谁看刚接触 Python、想摆脱手工复制粘贴报表的办公族已经会用 Pandas 但总觉得“导出 Excel 后格式太丑”的同学以及想系统了解 openpyxl 能力边界、准备用它做内部工具的开发者。下面的内容不会绕弯子都是我自己在项目里反复用过的写法也包含一些网上教程不太会提到的坑。1. 选型逻辑为啥是 openpyxl而不是 xlrd、xlwt 或 xlsxwriter1.1 Excel 处理库的江湖关系先理清这些库之间的关系不然新手很容易装错包。Python 处理 Excel 的库不少但各自定位差异很大库名支持格式主要能力适合场景openpyxl.xlsx / .xlsm读写、样式、图表、公式、条件格式日常办公自动化功能最均衡xlrd.xls旧版只读读取老式 .xls 文件新版不再支持 .xlsxxlwt.xls旧版只写生成旧版 .xls 文件xlsxwriter.xlsx只写功能丰富生成样式复杂的报表不支持读取pandas依赖底层引擎读取/写入 DataFrame数据处理、批量整理现在的 .xlsx 文件本质上是一个 zip 压缩包里面是一堆 XML 定义的工作表内容。openpyxl 做的事情就是把这些 XML 解析成我们熟悉的“工作簿 - 工作表 - 单元格”对象结构然后让用户像操作普通 Python 对象一样去改内容。所以如果格式是 .xls 老古董openpyxl 处理不了需要 xlrd/xlwt 补位如果只需要写不需要读xlsxwriter 也够用。但大多数人的办公场景是“既要读别人发来的文件又要生成自己的报表”这种时候 openpyxl 就是最省事的选择。1.2 安装和最小对象模型安装没什么门槛pip install openpyxlopenpyxl 的对象模型很直观Workbook 是一个 Excel 文件Worksheet 是其中一个 SheetCell 是某个格子。日常操作就是“打开工作簿 - 选工作表 - 定位单元格 - 读写值”。先看一个最小示例from openpyxl import Workbook wb Workbook() ws wb.active ws.title 数据报表 ws[A1] 你好openpyxl ws[A2] 42 wb.save(demo.xlsx)这段代码创建了一个工作簿给 A1 写字符串、A2 写数字然后保存成文件。这三行就是 openpyxl 的全部核心逻辑后面所有复杂操作都是在这个基础上叠加的。反过来读取文件from openpyxl import load_workbook wb load_workbook(demo.xlsx) ws wb.active print(ws[A1].value) # 你好openpyxl print(ws[A2].value) # 42这里有个使用习惯建议写代码前想清楚这个文件是给人看的报表还是机器之间交换数据的中间产物。如果是前者样式、列宽、冻结窗格这些必须处理如果是后者直接写值就够了样式只会拖慢速度。2. 日常高频操作单元格、区域遍历和样式控制的正确姿势2.1 单元格读写与区域遍历的几种惯用法单元格定位有两种方式一种是直接使用类似 Excel 坐标的字符串写法一种是行列号# 方式1坐标字符串可读性好 ws[B2] 销售额 # 方式2行列数字适合循环 for row in range(2, 10): ws.cell(rowrow, column2, valuerow * 100)区域遍历最常用的三个 API 是 iter_rows、iter_cols 和 ws.iter_rows(values_onlyTrue)。比如要读取一个表格的所有内容for row in ws.iter_rows(min_row1, max_rowws.max_row, min_col1, max_colws.max_column): for cell in row: print(cell.coordinate, cell.value)写入一整块二维数据时直接用 append 最省事rows [ [产品, 销量, 单价], [A, 120, 35.5], [B, 98, 42.0], [C, 150, 28.8], ] for row in rows: ws.append(row)append 会在当前工作表底部追加不需要自己维护行号。我自己的习惯是需要精确控制某个位置的写入用坐标定位批量导入数据用 append遍历数据用 iter_rows。这三个操作掌握了基本办公场景就覆盖了大半。2.2 样式与格式能改的和必须小心的openpyxl 的样式系统包括字体、填充、边框、对齐、数字格式等。给表头加个样式是经典需求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) for cell in ws[1]: cell.font header_font cell.fill header_fill cell.alignment center_align这里有个新手容易踩的坑openpyxl 的样式对象是“复制后赋值”的机制。也就是说你不能先创建一个 Font 对象后面改了它的属性期待之前引用它的单元格自动更新。每个单元格拿到的是一个独立的样式副本。所以批量设置样式时样式对象要定义在循环外面然后把同一个对象赋给不同单元格这样是没问题的但如果想要不同单元格用不同颜色就得在循环里分别创建新对象。数字格式是另一个高频需求。把日期显示成“2025-06-18”把数字显示成千分位写法如下cell ws[C2] cell.value 1234567.891 cell.number_format #,##0.00 from datetime import date dcell ws[A2] dcell.value date(2025, 6, 18) dcell.number_format YYYY-MM-DD注意 number_format 使用的是 Excel 的格式代码不是 Python 的格式化语法。这个习惯必须养成不然日期写入后常会变成一坨数字序列。2.3 合并单元格、冻结窗格、分组和打印设置合并单元格是制作报表表头的必经之路ws.merge_cells(A1:D1) ws[A1] 季度销售汇总 ws.unmerge_cells(A1:D1)合并后只有左上角的单元格能正常写入内容和样式其他区域的单元格是“被合并状态”读写要绕过。取消合并时把 merge_cells 换成 unmerge_cells 即可注意取消后原被覆盖的单元格是空的。冻结窗格适合表头固定场景。想让第一行冻结不动ws.freeze_panes A2这里赋值的坐标表示“从这个单元格左上角开始冻结”A2 意味着第一行固定。想让第一行和第一列都冻住就写 B2。行和列的分组在 openpyxl 里对应的是 Excel 的“组合”功能适合做明细折叠ws.row_dimensions.group(2, 10, hiddenFalse) ws.column_dimensions.group(B, F, hiddenTrue)打印设置这块容易被忽略但真要把报表打印出来时默认打印区域可能乱七八糟。简单设置一下ws.print_area A1:H50 ws.page_setup.orientation landscape ws.page_setup.paperSize ws.PAPERSIZE_A4 ws.oddHeader.center.text 季度报表3. 报表进阶公式、条件格式、图表和数据验证3.1 公式的写入策略openpyxl 不计算但可以给你写公式往单元格里写公式其实就是一个字符串ws[D2] SUM(B2:C2)需要注意的关键点是openpyxl 只负责把公式文本写进文件它本身不会去计算结果。如果之后你用 openpyxl 读取这个文件的公式值默认读到的是公式字符串不是数值。所以写公式前你要想清楚这个文件最终是给谁看的。如果是给 Excel/WPS 用户打开看的公式写完保存用户打开时软件会自动计算没问题。如果要用程序读取计算结果就需要在生成文件时用带公式的软件打开并保存一遍或者干脆用 Python 自己把结果算出来再写。读取已存在文件中的公式值时load_workbook 有个 data_only 参数wb load_workbook(report.xlsx, data_onlyTrue)开启后读取到的是公式的“缓存值”。但这个缓存值只有在文件最后被 Excel 之类的软件保存过时才存在如果文件是由 openpyxl 写的、从未被 Excel 打开过data_onlyTrue 读出来的公式位置是 None。这点我在项目里吃过亏脚本生成一批带 SUM 公式的报表下游系统读取时全是 None排查了半天才意识到 openpyxl 写的公式没有缓存结果。后来改成在脚本里直接计算 SUM 写入静态值或者用一套本地计算逻辑才彻底解决。3.2 条件格式规则引擎的正确打开方式条件格式适合做“数据高亮”比如把销量低于某个阈值的单元格标红。openpyxl 里用 Rule 对象来定义from openpyxl.formatting.rule import CellIsRule green_fill PatternFill(start_colorC6EFCE, end_colorC6EFCE, fill_typesolid) red_fill PatternFill(start_colorFFC7CE, end_colorFFC7CE, fill_typesolid) ws.conditional_formatting.add( B2:B100, CellIsRule(operatorgreaterThan, formula[100], fillgreen_fill) ) ws.conditional_formatting.add( B2:B100, CellIsRule(operatorlessThan, formula[50], fillred_fill) )公式参数要写成列表形式因为条件格式支持公式数组。实际项目里我用得更多的其实是 FormulaRule它支持任意表达式灵活性大很多。比如高亮整行中符合条件的记录from openpyxl.formatting.rule import FormulaRule # B列为状态值为“已完成”时A到D全部标绿 ws.conditional_formatting.add( A1:D100, FormulaRule(formula[$B1已完成], fillgreen_fill) )注意 FormulaRule 里的公式是相对引用思维需要按 Excel 规则写行号。第一行对应公式里的 1这就是为什么 $B1 里的“1”要跟着范围起点走。3.3 图表不用 VBA 也能出折线图和柱状图openpyxl 的图表模块支持柱状图、条形图、折线图、饼图等常用类型。创建图表时不看工具栏里的图标是大坑代码非常简单from openpyxl.chart import BarChart, Reference chart BarChart() chart.type col chart.title 各产品销售对比 chart.y_axis.title 销量 chart.x_axis.title 产品 data Reference(ws, min_col2, min_row1, max_row5, max_col2) # 数据区域 cats Reference(ws, min_col1, min_row2, max_row5) # 类别区域 chart.add_data(data, titles_from_dataTrue) chart.set_categories(cats) ws.add_chart(chart, F2)关键点是 Reference 的坐标理解。add_data 的数据区域如果包含表头行加 titles_from_dataTrue 可以自动把首行当系列名。图表插入后会生成一个独立的图表对象位置在他的坐标Excel 打开时图表是活着的。折线图、饼图就是换个类名的问题用法一致。说实话日常自动化办公里图表能做到这一步已经超过大半手工报表了。3.4 下拉列表与数据验证给报表加下拉选项用 DataValidation。比如给“状态”列加一个“已完成/进行中/未开始”的下拉from openpyxl.worksheet.datavalidation import DataValidation dv DataValidation(typelist, formula1已完成,进行中,未开始, allow_blankTrue) dv.add(C2:C20) ws.add_data_validation(dv)注意公式里用逗号分隔选项整个字符串要用英文双引号包裹。如果选项很多公式里可以引用一个隐藏 Sheet 的区域。这个功能做数据录入工具时很实用但注意 DataValidation 的列表选项公式长度不能超过 Excel 的限制特别长的选项列表建议改成引用区域。4. 别让几十万行拖垮内存write_only 和 read_only 模式4.1 openpyxl 藏不住的内存问题openpyxl 的默认行为是把整个工作表对象构建在内存中操作。平时几千行、几百列完全没问题但到了十万行以上的级别内存占用开始明显飙升甚至报 MemoryError。原因很简单每个单元格都是一个完整对象里面有值、样式、坐标等属性一行几百列几十万行的对象数量非常庞大。这时候需要的是 write_only 模式。它牺牲了“能回头改数据、能读已有文件”的能力换取了极速写入和几乎恒定的内存占用。4.2 write_only 模式写入几十万行from openpyxl import Workbook wb Workbook(write_onlyTrue) ws wb.create_sheet(big_data) # 一次性写入表头 ws.append([id, name, value, create_time]) # 模拟批量写入来自数据库或文件 for i in range(1, 300001): ws.append([i, f用户{i}, i * 1.5, 2025-01-01]) wb.save(big_data.xlsx)write_only 模式下工作表没有行号的概念只能用 append 顺序写入也不能在写完后修改前面已写入的行。对“程序生成新文件”的场景完全够用写入几十万行数据通常几秒钟就能完成。4.3 read_only 模式配合高效读取读取超大 Excel 文件时用普通模式加载容易把机器搞崩。read_only 模式配合 iter_rows 可以边读边丢内存占用很低wb load_workbook(big_data.xlsx, read_onlyTrue, data_onlyTrue) ws wb.active for row in ws.iter_rows(min_row2, values_onlyTrue): # 逐行处理这里可以做一些过滤、统计 print(row) wb.close()read_only 模式下拿到的不是完整对象读取性能也不错但要注意它的限制不能修改内容对单元格样式不可读写。单纯做数据抽取完全够用。如果发现读取还是很慢直接上 Pandas 更省心Pandas 的 read_excel 底层引擎就是 openpyxl后面会细说。5. Pandas 联动DataFrame 和 Excel 之间的桥5.1 读取和写入的默认引擎其实是 openpyxlPandas 并没有自己实现 Excel 解析读 xlsx 时默认用的是 openpyxl。也就是说Pandas 读写 Excel 的每一行代码底层都有 openpyxl 在跑。日常最常见的读取方式import pandas as pd df pd.read_excel(销售明细.xlsx, sheet_nameSheet1, header0)这一步背后就是 openpyxl 解析文件再把数据转成 DataFrame。所以 Pandas 读不了的诡异文件openpyxl 大概率也读不了反过来同理。写入时把 DataFrame 变成 Exceldf.to_excel(output.xlsx, indexFalse)这里要重点提 indexFalse。不写的话DataFrame 的行索引会被当作第一列写进表格变成一列莫名其妙的“0、1、2、3”这是新手最容易遇到的“表情失控”问题。5.2 用 Pandas 写入时精确控制格式Pandas 的 to_excel 决定数据openpyxl 决定格式。两者可以混合使用先用 Pandas 把数据写进文件然后用 openpyxl 重新打开加样式。import pandas as pd from openpyxl import load_workbook from openpyxl.styles import Font, PatternFill from openpyxl.utils.dataframe import dataframe_to_rows df pd.DataFrame({ 产品: [A, B, C], 销量: [120, 98, 150], 单价: [35.5, 42.0, 28.8], }) df.to_excel(report.xlsx, indexFalse, sheet_name汇总) wb load_workbook(report.xlsx) ws wb[汇总] for cell in ws[1]: cell.font Font(boldTrue, colorFFFFFF) cell.fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) wb.save(report.xlsx)这样既省去手动逐格写数据的体力活又能拿到想要的外观。另一个更精细的姿势是用 dataframe_to_rows 把 DataFrame 转成行迭代器手动写入这样每个单元格都能单独控制ws wb.create_sheet(数据) for r_idx, row in enumerate(dataframe_to_rows(df, indexFalse, headerTrue), start1): for c_idx, value in enumerate(row, start1): ws.cell(rowr_idx, columnc_idx, valuevalue)这种方式适合需要写合并单元格、公式、跨 Sheet 引用的复杂模板。5.3 追加 Sheet 而不破坏原文件实际工作中经常遇到“已有报表要追加一个新的 Sheet”。直接用 to_excel 会覆盖原文件正确做法是借助 openpyxl 打开原工作簿挂到 ExcelWriter 上from openpyxl import load_workbook import pandas as pd wb load_workbook(report.xlsx) with pd.ExcelWriter(report.xlsx, engineopenpyxl, modea, if_sheet_existsoverlay) as writer: writer.book wb writer.sheets {ws.title: ws for ws in wb.worksheets} df2.to_excel(writer, sheet_name月度明细, indexFalse)这里三行挂载很关键writer.book 是原工作簿对象writer.sheets 把工作表的名字和对象对应起来。不做这两行Pandas 不知道工作簿里已经有哪些 Sheet会直接覆盖。6. 完整实战一个自动化日报汇总小项目6.1 需求与文件结构我之前的项目里有个高频需求每天从多个业务表格中提取关键指标汇总成一张日报表发出去给人看。在做完整流程前我们先明确目录结构daily_report/ ├── data/ │ ├── 华东.xlsx │ ├── 华南.xlsx │ └── 华北.xlsx ├── scripts/ │ └── make_report.py └── output/每个分表的格式大概是这样第一行是标题第二行是表头下面是具体数据| 日期 | 订单量 | 销售额 | 退款金额 |汇总逻辑是读取每个文件加总订单量、销售额、退款金额最后输出带样式的日报表再加上一张本月趋势折线图。6.2 核心汇总逻辑实现from openpyxl import load_workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.chart import LineChart, Reference from datetime import date import os, glob def extract_stats(file_path): wb load_workbook(file_path, data_onlyTrue) ws wb.active total_orders 0 total_sales 0.0 total_refund 0.0 for row in ws.iter_rows(min_row3, values_onlyTrue): if row[0] is None: continue total_orders row[1] total_sales row[2] total_refund row[3] return total_orders, total_sales, total_refund file_list glob.glob(data/*.xlsx) summary [] for f in file_list: name os.path.basename(f).split(.)[0] orders, sales, refunds extract_stats(f) summary.append([name, orders, sales, refunds])代码里从第三行开始遍历是为了跳过标题和表头。values_onlyTrue 拿到的是一行元组直接按下标取值速度快也不会产生大量 Cell 对象。6.3 样式美化与趋势图输出汇总数据拿到后生成最终报表wb Workbook() ws wb.active ws.title 日报汇总 ws.append([区域, 订单量, 销售额, 退款金额]) for row in summary: ws.append(row) # 添加汇总行 ws.append([合计, sum(r[1] for r in summary), sum(r[2] for r in summary), sum(r[3] for r in summary)]) # 表头样式 header_style Font(name微软雅黑, size11, boldTrue, colorFFFFFF) header_fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) for cell in ws[1]: cell.font header_style cell.fill header_fill cell.alignment Alignment(horizontalcenter, verticalcenter) # 列宽 ws.column_dimensions[A].width 12 ws.column_dimensions[B].width 16 ws.column_dimensions[C].width 16 ws.column_dimensions[D].width 16 # 生成折线图 chart LineChart() chart.title 各区域订单量对比 chart.style 13 data Reference(ws, min_col2, min_row1, max_rowws.max_row) cats Reference(ws, min_col1, min_row2, max_rowws.max_row - 1) chart.add_data(data, titles_from_dataTrue) chart.set_categories(cats) ws.add_chart(chart, F2) wb.save(output/日报汇总.xlsx)这个脚本在真实项目中可以直接定时任务跑起来每天早上自动生成。需要注意的细节是合计行不能出现在图表的数据区域之外所以图表数据区域用 ws.max_row 时类别区域要排除合计行。6.4 流程复盘与可以复用的封装跑完这个流程后我会再做一次复盘。第一数据源表如果有合并单元格、空行iter_rows 时要做元素为 None 的判断。第二金额列建议用 number_format 设置千分位。第三所有文件路径全部用 os 或 pathlib 处理硬编码斜杠在 Windows 和 Linux 上都会埋雷。如果这个任务要重复使用可以把它封装成一个函数def generate_daily_report(data_path, output_path, report_title日报汇总): # 原逻辑全部收进来数据路径和输出路径做成参数 pass建议也把“读取分表”和“生成汇总表”拆成两个模块前者以后可以换成从数据库读后者可以换成发邮件时附件转发改动量都受控。7. 我踩过的坑与排查思路7.1 公式读出来是 None这是最容易让人崩溃的问题。读别人发来的 Excel代码写着 values_onlyTrue公式单元格对应的值却是 None。真相是openpyxl 读公式单元格返回的是公式字符串除非你同时满足两个条件——文件在 Excel/WPS 里保存过产生了缓存值且 load_workbook 加 data_onlyTrue。判断办法很简单打印 ws[A1].value如果字符串以 开头情况就是这样。解决办法也直白要么要求上游在导出时用 Excel 另存一遍要么自己在 Python 里计算。openpyxl 官方不打算内置计算引擎所以别纠结。7.2 日期变成了数字序列写入日期后打开 Excel 看到的是一串像 45562 的数字几乎人人都会遇到。原因是 openpyxl 写入日期时如果没有设置 number_formatExcel 显示的是日期序列号。解决办法就是在写入 datetime 后立即设置单元格的数字格式往回带 number_formatYYYY-MM-DD HH:MM:SS。多列日期时我是用循环统一设置的ws[fC{row}].number_format YYYY-MM-DD7.3 文件路径导致 PermissionErrorWindows 下最常见的报错是 PermissionError: [Errno 13]十有八九是目标文件正在被 Excel 打开。openpyxl 的 save 本质上是创建一个新文件然后覆盖如果原文件被占用写入权限会被拒绝。排查思路先关掉所有 Excel 窗口确认没有进程占用如果程序要反复读写同一个文件数据库或缓存层才是正确解法不要硬刚同一个文件。7.4 写入报错非法字符有时候明明写的是简单字符串保存却报 IllegalCharacterError。原因是字符串里带了一些 Excel 不支持的不可见控制字符比如 \x00、\x0b。处理办法是写入前做一次清洗把 ASCII 控制字符全部剔除import re def clean_excel_text(text): if not isinstance(text, str): return text return re.sub(r[\x00-\x1f], , text)这个函数我现在所有写 Excel 的脚本都会带着省得到处找 bug。最后说点实在的。我用 openpyxl 将近四年最大的体会是这个库的功能比大多数人想象的多但边界也清楚——它不适合当一个数据分析引擎也不适合替代数据库。真正的定位是“让 Excel 在自动化流程里当一个合格的输入/输出角色”。把数据计算、清洗交给 Pandas把格式、图表、呈现交给 openpyxl两者配合才是自动化办公最高效的组合。希望这篇内容对你有用也欢迎你拿着自己的场景去试一遍跑通了你会回来感谢这个库的。