1. 为什么我最终选择了openpyxl来处理Excel1.1 从一次真实的数据整理需求说起去年年底朋友所在的一家小型贸易公司遇到了一件麻烦事。他们每天需要从ERP系统导出十几张Excel报表然后人工把关键数据汇总到一张总表里再根据总表生成周报和月报。这个流程听起来不复杂但问题在于报表的格式每周都会微调列的顺序会变表头偶尔多一行少一行人工复制粘贴不仅慢还特别容易出错。他们试过用Excel自带的宏但维护起来很痛苦换一台电脑就可能跑不起来。我接手这个需求之后第一反应是用Python来做自动化。Python处理Excel的库有好几个比如pandas、xlrd、xlwt、xlsxwriter还有今天要重点聊的openpyxl。这几个库各有各的脾气选哪个取决于你要干什么。如果只是读取数据做分析pandas确实方便但如果要保留原有格式、操作单元格样式、插入图表、设置数据验证甚至生成带条件格式的报表openpyxl是绕不开的选择。openpyxl是一个专门用来读写XLSX格式文件的Python库。注意它只支持XLSX不支持老旧的XLS格式。这一点很关键因为现在绝大多数场景下我们用的都是XLSX。它能做的事情包括读取和写入单元格数据、设置字体和颜色、合并单元格、调整行高列宽、添加公式、创建图表、设置条件格式、保护工作表等等。换句话说你在Excel界面里能手动做的很多操作openpyxl都能用代码完成。这篇文章适合谁看如果你是一个刚接触Python的初学者想用代码替代重复的Excel操作那这篇内容会从安装开始一步步带你走。如果你已经有一定Python基础但没怎么用过openpyxl那你可以重点看后面的实战部分和避坑经验。如果你是个数据分析师或者运营人员每天要处理大量表格那这篇文章里的自动化思路应该能帮你省下不少时间。1.2 openpyxl和其他Excel库的对比在正式动手之前我觉得有必要把几个常见的Excel处理库放在一起比一比这样你心里有个底知道什么场景该用什么工具。库名称支持格式读取写入格式操作图表适用场景openpyxlXLSX支持支持非常丰富支持报表生成、格式复杂的场景pandasXLSX/XLS/CSV支持支持有限不支持数据分析、快速读写xlrdXLS/XLSX支持不支持有限不支持老版本XLS读取xlwtXLS不支持支持一般不支持老版本XLS写入xlsxwriterXLSX不支持支持非常丰富支持只写不读的报表生成从这张表可以看出来openpyxl最大的优势是“读写兼备且格式操作丰富”。pandas虽然用起来很爽一行代码就能读一个Excel但它对格式的控制能力很弱你没法用它来设置某个单元格的背景色或者字体加粗。xlsxwriter写格式很强但它不能读取已有的Excel文件这意味着你没法在现有模板上修改。我个人的经验是如果只是做数据分析pandas足够了但如果要做“填模板”“生成固定格式报表”“批量修改现有表格”这类工作openpyxl是更合适的选择。当然两者也可以配合使用比如用pandas做数据处理用openpyxl做最终的格式输出。1.3 安装openpyxl的正确姿势安装openpyxl本身很简单一条命令的事pip install openpyxl但就是这条看起来简单的命令在实际操作中会卡住不少人。我见过太多人在这一步报错然后卡半天。下面我把常见的几种情况和解决办法都说清楚。情况一pip命令找不到。在Windows的命令提示符里输入pip结果提示“pip不是内部或外部命令”。这通常是因为Python安装的时候没有勾选“Add Python to PATH”。解决办法有两个一是重新安装Python并勾选那个选项二是手动把Python的Scripts目录加到系统环境变量里。具体路径一般是C:\Users\你的用户名\AppData\Local\Programs\Python\Python3x\Scripts把这个路径加到PATH里就行了。情况二下载速度极慢或者超时。这是因为默认的包源在国外网络不稳定。解决办法是换用国内的镜像源。常用的有清华源、中科大源、阿里源等。命令格式如下pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple如果你经常需要安装包可以把这个镜像源设置为默认这样以后就不用每次都加-i参数了pip config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple情况三公司内网无法访问外网。有些公司的开发环境是隔离的根本连不上外网。这种情况下你需要用离线安装的方式。首先在一台能上网的机器上下载openpyxl的安装包pip download openpyxl -d ./packages然后把整个packages文件夹拷贝到目标机器上执行pip install openpyxl --no-index --find-links./packages注意openpyxl有一个依赖库叫et_xmlfile下载的时候会一起下下来不要只拷贝openpyxl那个文件。提示如果你用的是Anaconda环境也可以直接用conda install openpyxl来安装不过conda源里的版本可能不是最新的。安装完成之后验证一下是否成功import openpyxl print(openpyxl.__version__)能打印出版本号就说明安装没问题了。截至我写这篇文章的时候openpyxl的最新稳定版本是3.1.x系列建议用这个版本兼容性和功能都比较完善。2. openpyxl核心概念与基础操作拆解2.1 工作簿、工作表与单元格的关系刚接触openpyxl的时候最容易搞混的就是Workbook、Worksheet和Cell这三个概念。我用一个生活化的类比来解释把Workbook想象成一个Excel文件本身也就是你双击打开的那个.xlsx文件Worksheet就是这个文件里的一个个标签页比如“Sheet1”“一月数据”“汇总表”Cell就是每个标签页里的具体格子比如A1、B2、C3。在openpyxl里创建一个新工作簿的代码是这样的from openpyxl import Workbook wb Workbook() ws wb.active ws.title 销售数据 ws[A1] 产品名称 ws[B1] 销售数量 wb.save(demo.xlsx)这段代码做了几件事新建了一个工作簿拿到默认的活动工作表把工作表改名为“销售数据”在A1和B1单元格写入了表头最后保存为demo.xlsx文件。注意如果你不调用save方法所有的操作都只存在于内存中文件不会真正生成。打开一个已有的Excel文件也很简单from openpyxl import load_workbook wb load_workbook(demo.xlsx) ws wb[销售数据] print(ws[A1].value)这里有个细节需要注意load_workbook默认是以只读模式之外的方式打开的也就是说你可以读取也可以修改。如果你只需要读取数据可以加上read_onlyTrue参数这样打开大文件的速度会快很多内存占用也小很多。2.2 单元格数据的读取与写入技巧单元格的读写是openpyxl最基础的操作但里面有不少细节值得说。写入数据的方式有好几种。最常见的是通过坐标直接赋值ws[A1] Hello ws[B2] 123 ws[C3] 3.14也可以通过cell方法来写入这种方式在循环里特别方便ws.cell(row1, column1, valueHello)两种方式效果一样但如果你要在循环里按行按列写入用cell方法配合行列号会更灵活。读取数据的时候要注意空单元格。如果你读取一个没有内容的单元格返回的是None而不是空字符串。这一点在做数据判断的时候要特别注意value ws[A1].value if value is None: print(这个格子是空的)遍历数据是高频操作。openpyxl提供了几种遍历方式我常用的有这几种# 按行遍历 for row in ws.iter_rows(min_row1, max_row5, min_col1, max_col3): for cell in row: print(cell.value) # 按列遍历 for col in ws.iter_cols(min_row1, max_row5, min_col1, max_col3): for cell in col: print(cell.value) # 遍历所有有数据的行 for row in ws.iter_rows(values_onlyTrue): print(row)values_onlyTrue这个参数很实用它直接返回单元格的值组成的元组而不是Cell对象省去了.value的调用代码更简洁。注意用iter_rows遍历的时候如果指定了max_row和max_col即使某些单元格没有数据也会被遍历到返回None。如果不指定范围默认遍历的是有数据的区域。2.3 工作表的新建、复制与删除一个工作簿里通常不止一个工作表openpyxl对工作表的操作也很灵活。新建工作表ws_new wb.create_sheet(新工作表) ws_new2 wb.create_sheet(指定位置, 0) # 插入到第一个位置复制工作表ws_copy wb.copy_worksheet(ws) ws_copy.title 副本删除工作表del wb[工作表名] # 或者 wb.remove(wb[工作表名])获取所有工作表名称print(wb.sheetnames)这里有个坑要提醒一下copy_worksheet只能在同一工作簿内复制不能跨工作簿复制。如果你需要把一个工作表从一个文件复制到另一个文件需要手动遍历单元格逐个复制或者用一些变通的方法。另外工作表的顺序可以通过wb.move_sheet()来调整不过这个方法的参数在不同版本里有些差异用之前最好查一下对应版本的文档。3. 实战从零生成一份带格式的销售报表3.1 需求分析与数据结构设计光说不练假把式。下面我用一个完整的实战案例来演示openpyxl的实际用法。需求是这样的生成一份月度销售报表包含表头、数据区域、汇总行要求表头有背景色和加粗字体数据区域有边框金额列有千分位格式最后还要加一个简单的柱状图。先设计数据结构。假设我们从数据库或者CSV里拿到了这样一组数据data [ [产品A, 华东, 1200, 35.5], [产品B, 华北, 850, 42.0], [产品C, 华南, 1500, 28.8], [产品D, 华东, 620, 55.2], [产品E, 华北, 980, 38.6], ]每一行代表一条销售记录四个字段分别是产品名称、区域、销售数量和单价。我们需要计算每条记录的销售金额数量乘以单价然后在最后加一行汇总。3.2 表头样式与数据写入的完整代码下面我把完整的代码贴出来然后逐段解释关键点from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter from openpyxl.chart import BarChart, Reference wb Workbook() ws wb.active ws.title 月度销售报表 # 定义样式 header_font Font(name微软雅黑, size11, boldTrue, colorFFFFFF) header_fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) header_align Alignment(horizontalcenter, verticalcenter) thin_border Border( leftSide(stylethin), rightSide(stylethin), topSide(stylethin), bottomSide(stylethin) ) # 写入表头 headers [产品名称, 销售区域, 销售数量, 单价, 销售金额] for col_idx, header in enumerate(headers, start1): cell ws.cell(row1, columncol_idx, valueheader) cell.font header_font cell.fill header_fill cell.alignment header_align cell.border thin_border # 写入数据 data [ [产品A, 华东, 1200, 35.5], [产品B, 华北, 850, 42.0], [产品C, 华南, 1500, 28.8], [产品D, 华东, 620, 55.2], [产品E, 华北, 980, 38.6], ] for row_idx, row_data in enumerate(data, start2): for col_idx, value in enumerate(row_data, start1): cell ws.cell(rowrow_idx, columncol_idx, valuevalue) cell.border thin_border cell.alignment Alignment(horizontalcenter, verticalcenter) # 计算销售金额 amount_cell ws.cell(rowrow_idx, column5) amount_cell.value fC{row_idx}*D{row_idx} amount_cell.number_format #,##0.00 amount_cell.border thin_border # 写入汇总行 summary_row len(data) 2 ws.cell(rowsummary_row, column1, value合计).font Font(boldTrue) ws.cell(rowsummary_row, column3, valuefSUM(C2:C{summary_row-1})).font Font(boldTrue) ws.cell(rowsummary_row, column5, valuefSUM(E2:E{summary_row-1})).font Font(boldTrue) ws.cell(rowsummary_row, column5).number_format #,##0.00 # 调整列宽 column_widths [15, 12, 12, 12, 15] for i, width in enumerate(column_widths, start1): ws.column_dimensions[get_column_letter(i)].width width # 添加柱状图 chart BarChart() chart.type col chart.title 各产品销售额对比 chart.y_axis.title 销售金额 chart.x_axis.title 产品名称 data_ref Reference(ws, min_col5, min_row1, max_rowsummary_row-1) cats_ref Reference(ws, min_col1, min_row2, max_rowsummary_row-1) chart.add_data(data_ref, titles_from_dataTrue) chart.set_categories(cats_ref) chart.width 18 chart.height 10 ws.add_chart(chart, G2) wb.save(销售报表.xlsx) print(报表生成完成)3.3 样式设置中的关键参数解读上面这段代码里涉及了不少样式参数我挑几个重点说一下。Font字体的常用参数包括name字体名称、size字号、bold是否加粗、italic是否斜体、color颜色。颜色用十六进制字符串表示比如FF0000是红色FFFFFF是白色。注意不要加井号。PatternFill填充用来设置单元格背景色。start_color和end_color通常设成一样的值就是纯色填充。fill_type设为solid表示实心填充。如果你想要其他图案填充可以改成darkGray之类的值但实际用得最多的还是solid。Alignment对齐控制文本在单元格里的位置。horizontal可以是left、center、rightvertical可以是top、center、bottom。还有一个wrap_text参数设为True可以自动换行处理长文本的时候很有用。Border边框需要分别指定四个边的样式。Side的style参数可以是thin、medium、thick、dashed等。如果你只想给某一边加边框其他边不设就行。number_format数字格式这个特别实用。#,##0.00表示千分位分隔加两位小数。0%表示百分比。yyyy-mm-dd表示日期格式。这些格式代码和Excel里手动设置单元格格式时用的一样。实操心得设置样式的时候尽量把样式对象定义在循环外面然后在循环里引用。如果在循环里每次都新建一个Font对象数据量大的时候会明显变慢。我试过一万行数据样式对象复用和不复用差了将近一倍的时间。4. 常见问题排查与避坑经验实录4.1 安装与导入阶段的典型报错报错一ModuleNotFoundError: No module named openpyxl这个报错说明openpyxl没有安装成功或者你安装到了另一个Python环境里。如果你电脑上有多个Python版本比如同时装了Python3.8和Python3.11用pip安装的时候可能装到了其中一个但你的代码用的是另一个。解决办法是指定Python版本安装python3.11 -m pip install openpyxl或者用虚拟环境来隔离这也是我推荐的做法。每个项目建一个独立的虚拟环境依赖互不干扰。报错二pip install 报SSL证书错误有些公司的网络环境会拦截SSL证书导致pip下载失败。可以临时加上信任主机的参数pip install openpyxl --trusted-host pypi.tuna.tsinghua.edu.cn不过这只是权宜之计长期来看还是建议找IT部门解决证书问题。报错三权限不足在Linux或者Mac上如果不用虚拟环境直接pip install可能会提示Permission denied。这时候不要用sudo pip install那样会把包装到系统目录里容易搞乱系统环境。正确做法是用pip install --user openpyxl装到用户目录或者用虚拟环境。4.2 读写Excel时的常见异常处理问题一打开文件时报“Permission denied”这通常是因为这个Excel文件正在被Excel程序打开着。Windows下文件被占用的时候Python没法写入。解决办法很简单关掉Excel再运行代码。如果你需要程序自动处理可以加一个重试机制import time def save_with_retry(wb, filename, max_retries5): for i in range(max_retries): try: wb.save(filename) return True except PermissionError: print(f文件被占用第{i1}次重试...) time.sleep(2) return False问题二读取大文件时内存爆了openpyxl默认会把整个文件加载到内存里如果文件有几十万行内存占用会很大。解决办法是用只读模式wb load_workbook(大文件.xlsx, read_onlyTrue) ws wb.active for row in ws.iter_rows(values_onlyTrue): # 处理每一行 pass wb.close()只读模式下不能修改单元格但读取速度会快很多内存占用也小很多。记得用完要调用close方法释放资源。问题三公式计算结果读出来是公式本身openpyxl读取单元格的时候如果单元格里是公式默认返回的是公式字符串而不是计算结果。比如你读到的是SUM(A1:A10)而不是具体的数值。这是因为openpyxl不负责计算公式它只是读取文件里存储的内容。如果你需要计算结果有两个办法一是用data_onlyTrue参数打开文件这样会读取Excel上次保存时缓存的计算结果二是用其他库比如formulas来计算。wb load_workbook(文件.xlsx, data_onlyTrue)但要注意data_onlyTrue只有在Excel打开并保存过这个文件之后才有效如果文件是程序生成的且从未用Excel打开过缓存值可能是None。4.3 性能优化与大批量数据处理建议当你需要处理几万甚至几十万行数据的时候openpyxl的默认写入方式会比较慢。下面是我总结的几个优化技巧。技巧一用write_only模式写入。如果你只需要写入数据不需要读取可以用write_only模式这种方式是流式写入内存占用极低from openpyxl import Workbook wb Workbook(write_onlyTrue) ws wb.create_sheet(数据) for i in range(100000): ws.append([i, f名称{i}, i*1.5]) wb.save(大数据文件.xlsx)write_only模式下不能用cell方法逐个设置单元格只能用append按行追加。而且不能设置单个单元格的样式只能通过设置整列的样式来间接控制。技巧二批量设置样式。如果非要在普通模式下设置大量单元格的样式尽量用循环外定义好的样式对象避免重复创建。技巧三合理使用列维度和行维度。如果你需要设置整列的宽度或者整行的样式用column_dimensions和row_dimensions比逐个单元格设置要高效得多。技巧四避免频繁save。每次save都会把整个工作簿写一遍如果你在循环里反复save效率会非常低。正确的做法是全部操作完成之后只save一次。下面这张表总结了我遇到过的典型问题和解决思路问题现象可能原因解决办法pip安装超时网络访问国外源慢换清华源或中科大源导入报ModuleNotFoundError装到了其他Python环境用python -m pip指定版本安装保存时报PermissionError文件被Excel占用关闭Excel或加重试逻辑读取大文件内存高默认模式全量加载用read_onlyTrue公式读出来是字符串openpyxl不计算公式用data_onlyTrue读缓存值写入速度慢逐单元格操作开销大用write_only模式或批量操作中文显示乱码字体设置问题指定中文字体如微软雅黑日期格式不对未设置number_format设置yyyy-mm-dd格式4.4 那些文档里不会写的实操细节最后分享几个我在实际项目中踩过的坑这些在官方文档里通常不会提到。第一个坑合并单元格的读写。合并单元格之后只有左上角的单元格有值其他单元格的值是None。如果你要读取合并区域的值需要先判断这个单元格是不是合并区域的一部分from openpyxl.utils import range_boundaries for merged_range in ws.merged_cells.ranges: min_col, min_row, max_col, max_row range_boundaries(str(merged_range)) value ws.cell(rowmin_row, columnmin_col).value print(f合并区域{merged_range}的值是{value})第二个坑插入和删除行之后公式不会自动调整。如果你用openpyxl插入了一行原来公式里引用的单元格范围不会自动更新。比如原来是SUM(A1:A5)你在中间插入一行之后公式还是SUM(A1:A5)但实际数据已经变成了6行。这个问题需要你在代码里手动处理公式的引用范围。第三个坑不同版本的openpyxl API有差异。比如get_column_letter这个函数在2.x版本里是从openpyxl.cell导入的在3.x版本里是从openpyxl.utils导入的。如果你在网上搜到的代码跑不通很可能是版本不匹配。建议锁定一个版本使用不要频繁升级。第四个坑图表的中文显示。openpyxl生成的图表标题和坐标轴标签如果包含中文在某些Excel版本里可能显示为方框。解决办法是在设置图表标题的时候指定字体或者生成之后用Excel手动调整一下。这个问题和Excel的字体渲染有关不是openpyxl本身的bug。第五个坑条件格式的优先级。如果你给同一个区域设置了多个条件格式规则它们的优先级顺序会影响最终效果。openpyxl里可以通过rule的priority属性来调整但文档里对这个属性的说明比较简略需要自己多试几次才能搞明白。我在实际使用openpyxl的这几年里最大的体会是它不是一个“万能”的库但在“用代码操作Excel格式”这个细分领域里它确实是最成熟的选择。很多看似复杂的需求拆解开来无非就是“找到单元格、设置值、设置样式”这三步的重复组合。把基础操作练熟了再复杂的报表也能一步步搭出来。