在工作中处理 Excel 任务时我几乎每次都离不开两个 Python 库openpyxl 和 pandas。一个负责把数据读进来、洗干净、做聚合一个负责把最终结果塞回 Excel 文件里做成能直接交给业务方的漂亮表格。很多朋友问过我这两个库到底有什么区别、该先学哪个、遇到问题该查谁这篇就把我实际使用几年下来的经验整理一遍。不管你是刚接触 Python 的办公自动化新手还是已经写了半年脚本但经常在 Excel 边缘问题上卡住的人看完这篇应该都能把Python 操作 Excel这件事从头到尾理顺。1. 动手之前先想明白两个库各自负责哪一段1.1 pandas 不是 Excel 操作库是数据处理库我刚开始学的时候也犯过这个错误以为 pandas 是专门用来操作 Excel 的库。后来才意识到pandas 的核心是 DataFrame是一个二维表格数据结构。它从 Excel、CSV、数据库、接口里把数据加载进来然后专注于做一件事对表格数据进行清洗、筛选、分组、聚合、透视、排序、合并。Excel 文件只是它众多数据来源中的一种。这决定了它的强项和弱项。强项是数据转换效率极高比如一份一万行的明细表需要按月汇总、按产品分组统计pandas 一条 groupby 就完成了用 Excel 手动透视表还得拖半天。弱项是对 Excel 文件本身的格式控制能力非常粗糙——它只能做到把数据放进某个 sheet这种程度至于表头颜色、列宽、合并单元格、冻结窗格、条件格式这些pandas 写起来很别扭甚至根本做不了。import pandas as pd # 从 Excel 读取数据到 DataFrame df pd.read_excel(销售明细.xlsx, sheet_name2024) # 按月汇总销售额 monthly df.groupby(df[日期].dt.to_period(M))[金额].sum() print(monthly)这段代码做的事情换到 openpyxl 里会非常痛苦你得手动遍历每个单元格判断日期再维护一个累加字典。而 pandas 只用两行就搞定了。所以我的习惯是凡是跟数据计算、过滤、统计相关的活一律交给 pandas。1.2 openpyxl 能碰文件里每一个格子openpyxl 的定位是完全不同的。它处理的对象是 Excel 工作簿本身能直接读取和修改 xlsx 文件里的单元格、行、列、样式、图表等。每个工作簿Workbook、每个工作表Worksheet、每个单元格Cell都是它可以直接操作的对象。这意味着你可以做到非常精细的控制设置某个单元格的字体、背景色、边框、对齐方式指定某一行的高度、某一列的宽度把几个单元格合并起来给表格区域加筛选器甚至在单元格里写入公式。from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment wb Workbook() ws wb.active ws.title 月度汇总 # 设置表头 ws[A1] 月份 ws[B1] 销售额 header_font Font(boldTrue, colorFFFFFF) header_fill PatternFill(solid, fgColor4472C4) for cell in ws[A1:B1][0]: cell.font header_font cell.fill header_fill cell.alignment Alignment(horizontalcenter, verticalcenter) wb.save(报表.xlsx)但 openpyxl 几乎不帮你做数据分析。你要算合计得自己在 Python 里算好再填进去或者写一个 SUM 公式让它打开 Excel 时再算。你要做筛选也得先自己在代码里把逻辑写清楚。所以我的判断标准反过来凡是跟单元格长什么样、格式怎么设置、模板怎么填充相关的活一律交给 openpyxl。1.3 什么时候只用其中一个什么时候两个一起用经常有人问能不能只装一个库就把活干完分情况说只是想把 CSV 转成 Excel或者把数据库查询结果导出成 Excel数据量几百行不需要花哨格式 —— 只装 pandas 就够了。只是需要批量为一个已有的 Excel 模板填充数据格式已经做死了每次只需要改几个单元格的数值 —— 只装 openpyxl 就够了。要从几个明细表里统计数据再把汇总结果以漂亮的格式输出或者要做一份带表头样式、合并单元格、公式的月度报表 —— 必须两个库一起用pandas 处理数据openpyxl 负责呈现。两个库的配合方式很简单pandas 处理完数据后要么通过 ExcelWriter 直接写文件要么把结果传给 openpyxl 再额外加工格式。后者更常见因为你可能不想让 pandas 先写出来的文件格式白费自己一番功夫所以通常是把数据准备好之后直接用 openpyxl 在模板上填。2. 环境准备和第一段能跑的读写代码2.1 安装顺序和版本匹配安装本身没什么难度一条命令就可以pip install pandas openpyxl但我在这里踩过版本坑。早期 pandas 版本跑在较老的 openpyxl 上会出现ValueError: Invalid file path之类的怪问题还有一次是 pandas 读取 xlsx 时提示Missing optional dependency openpyxl明明我已经装过了一查是环境把两个包装到了不同的 Python 环境里。所以如果你用 Anaconda 或者系统里有多个 Python最好先确认python -m pip install --upgrade pandas openpyxl离线安装的场景也遇到过。内网机器上没法直接 pip 下载我一般是去 pypi 把对应版本的 whl 文件下载到 U 盘再在内网执行pip install openpyxl-3.1.2-py2.py3-none-any.whl pip install pandas-2.0.3-cp311-cp311-win_amd64.whl注意 pandas 的 whl 文件带有 Python 版本标记cp311 表示 Python 3.11别下载错版本。离线机器没有网络的话直接用这个方式就能装上。2.2 用 pandas 三行代码读一个 Excel安装好了我先给最常用的读取操作。假设有一个销售明细.xlsx第一个 sheet 叫明细import pandas as pd df pd.read_excel(销售明细.xlsx, sheet_name明细) print(df.head()) print(df.dtypes)read_excel返回的是 DataFramehead()打印前几行dtypes打印每一列的数据类型这两行输出基本能告诉你数据到底有没有被正确读进来。如果文件里第一个 sheet 就是要读的连sheet_name参数都可以不写。2.3 用 openpyxl 从零写一个文件对应的openpyxl 从零创建一个文件也很简单from openpyxl import Workbook wb Workbook() # 创建一个工作簿默认自带一个 sheet ws wb.active # 获取默认工作表 ws[A1] 项目 ws[B1] 金额 ws.append([办公用品, 1500]) # append 会把数据追加到下一行 ws.append([差旅费, 3200]) wb.save(支出表.xlsx)打开生成的支出表.xlsx你会看到两列三行的数据。append方法非常实用它不需要你手动计算下一行的行号自动在最后一行之后追加。2.4 验证文件是否正常的建议写完文件之后我建议都做一步验证不要直接当黑盒交付。用 pandas 再把刚生成的文件读一遍确认数据没有丢check pd.read_excel(支出表.xlsx) print(check)这一步能拦截掉八成的问题比如 sheet 名写错、数据根本没写进去、覆盖了已有文件等。我见过不少同事改了代码后不验证结果发给业务方的报表是上一次的旧数据。一旦脚本涉及覆盖写入验证就尤其重要。3. pandas 阶段读取参数、数据清洗和跨 Sheet 处理3.1 读取 Excel 的几个关键参数很多问题都出在这里read_excel的参数非常多但实际工作中最常用的就几个sheet_name、header、usecols、dtype、skiprows、parse_dates。绝大多数读取出来数据怪怪的问题都能在这几个参数里找到答案。先看header。默认情况下 pandas 会把 Excel 的第一行当作列名。如果文件前几行是标题说明、公司名称之类的内容直接读会导致第一列列名变成某某公司2024年度销售报表这种奇怪的字符串。解决办法是让 pandas 跳过指定行df pd.read_excel(销售明细.xlsx, header3)这里header3表示第 4 行作为列名也就是说表头并不一定在第一行跳过了前面 3 行。再看usecols。有些 Excel 列特别多比如附带了一堆备注、更新时间、操作人等无关列读进来既占内存又碍事。用usecols可以只读指定列df pd.read_excel(销售明细.xlsx, usecolsA:C,E)字符串A:C,E表示读取 A 到 C 列以及 E 列。如果习惯用列名也可以传列表usecols[订单号, 金额]。最后是skiprows。有些文件开头有几行空行或者说明文字在指定header之前建议先用skiprows咔嚓掉df pd.read_excel(上报数据.xlsx, skiprows2, header0)3.2 列类型处理身份证号、日期、数字精度这是我在实际项目里碰到最多的一类问题Excel 里看起来是对的pandas 读进来就变样了。典型场景是身份证号。Excel 本身对长度超过 15 位的数字会自动转成科学计数法比如110101199001011234会显示成1.10101E17。如果文件已经这样存了pandas 读进来就真的变成带 E 的字符串或浮点数回天无力。但更多时候是文件里存的就是文本可 pandas 默认会尝试把看起来像数字的列转换成 int64一转换身份证号就丢精度了。解法是在读取时就指定这一列要按字符串读df pd.read_excel(客户信息.xlsx, dtype{身份证号: str})如果有很多列都需要按文本处理可以直接dtypestr把整个表读成文本格式需要的时候再单独转换。这个操作在处理订单号、银行卡号、物料编码时很常用。日期列也有类似的坑。pandas 通常会把 Excel 日期读成Timestamp或datetime64输出时变成了2024-01-05 00:00:00。如果只需要日期部分可以在读取时指定df pd.read_excel(销售明细.xlsx, parse_dates[下单时间])然后用df[下单日] df[下单时间].dt.date转成纯日期格式。3.3 数据清洗三板斧空值、重复值、文本格式读进来的数据多半不干净我的固定流程是先检查再清洗三步走第一步处理空值。先看哪些列有空值print(df.isnull().sum())然后决定策略整行都空的直接删掉个别列的缺失根据不同业务来。数值列金额是空的可以用 0 填充文本列产品名称是空的可以填充未知。代码示例df df.dropna(howall) # 删掉全空行 df[金额] df[金额].fillna(0) # 金额空值补 0第二步处理重复。判断关键列是否有重复然后决定是删掉多出来的还是保留最后一个df df.drop_duplicates(subset[订单号], keepfirst)注意keepfirst保留第一条keeplast保留最后一条实际业务中按需求选。第三步清理文本。Excel 数据经常会带前后空格、全角空格、换行符比对的时候容易出问题df[客户名称] df[客户名称].astype(str).str.strip() df[备注] df[备注].str.replace(\n, , regexFalse)这里strip去掉字符串两端的空白replace去掉换行符。处理完之后再做匹配、汇总会稳很多。3.4 多 Sheet 一次读取和合并单元格的连带坑如果一个 Excel 里有多张 sheet比如一月二月三月逐个读取当然可以但更高效的方法是一次性读全部sheets pd.read_excel(2024销售.xlsx, sheet_nameNone) for name, df in sheets.items(): print(f{name}: {df.shape})sheet_nameNone返回一个字典key 是 sheet 名value 是对应的 DataFrame。在批量处理多个 sheet 时这个写法配合 for 循环非常顺手。合并单元格是最容易坑人的地方。Excel 左侧合并了几个单元格转成 DataFrame 后只有左上角的位置有值其余位置全是 NaN。比如 A2:A5 合并成了华东大区结果华东大区只在第一行出现下面 3 行都是 NaN。处理方式是用前向填充df[大区] df[大区].fillna(methodffill)ffill会用上一个非空值向下填充把合并单元格拆开后的空值补成同一个值。这个方法我在处理层级分类数据时几乎每次都会用到。4. openpyxl 阶段样式、合并、公式和模板填充4.1 表头和关键格子的样式设置用 pandas 写完的数据表是素面朝天的发给别人看其实也行但大部分业务场景都需要至少一点正式感表头加粗、换个底色、列宽调整一下。用 openpyxl 做这些非常直接。我写过一个小的表头美化函数每次生成报表都调用from openpyxl.styles import Font, PatternFill, Alignment, Border, Side def style_header(ws, columns, start_row1): thin_border Border( leftSide(stylethin), rightSide(stylethin), topSide(stylethin), bottomSide(stylethin), ) for idx, col in enumerate(columns, start1): cell ws.cell(rowstart_row, columnidx, valuecol) cell.font Font(boldTrue, size11, colorFFFFFF) cell.fill PatternFill(solid, fgColor305496) cell.alignment Alignment(horizontalcenter, verticalcenter) cell.border thin_border调用方式很简单给列名列表就行。注意PatternFill的第一个参数是填充类型固定写solid第二个参数fgColor是前景色填的是十六进制颜色码不带#。这个函数可以继续扩展比如调节行高、设置列宽等核心思想是把样式逻辑收拢在一个函数里免得每个脚本重复写。4.2 合并单元格和冻结窗格的操作细节报表里经常有跨列标题比如第一行写2024年度销售汇总报表下面一行才是字段名。合并单元格用merge_cellsws.merge_cells(A1:F1) ws[A1] 2024年度销售汇总报表 ws[A1].alignment Alignment(horizontalcenter, verticalcenter)注意合并之后值只保留在区域左上角单元格其他单元格变为空。如果你先填了值再合并最终只有左上角的值会显示。取消合并用unmerge_cells。冻结窗格也很有用。数据多的时候往下滚动就看不到表头了指定freeze_panes可以固定住顶部几行ws.freeze_panes A2freeze_panes的值表示从这个单元格开始它上面和左边的区域固定不动。A2表示冻结第 1 行下拉时表头始终可见B2表示冻结第 1 行和第 1 列。列宽和行高也是报表能不能看的关键。列宽用字母索引行高用行号ws.column_dimensions[A].width 15 ws.column_dimensions[B].width 20 ws.row_dimensions[1].height 224.3 公式写入与看不到计算结果问题openpyxl 可以在单元格里写 Excel 公式比如在 C10 写下求和公式ws[C10] SUM(C2:C9)公式本身会被正确写入 xlsx 文件打开 Excel 时也会正常计算。但有两点必须清楚。第一openpyxl 本身不计算公式所以如果你用 openpyxlload_workbook读回这个文件再读 C10拿到的不是数字而是字符串SUM(C2:C9)。这在程序里做进一步计算的时候很麻烦你需要自己解析或单独计算一遍。我通常会直接避免依赖公式结果需要数值就直接在 Python 里算好填进去。第二写入的公式要符合 Excel 的语法函数名是英文的参数里逗号是英文逗号。这个看起来简单但中文版 Excel 的某些本地化函数名如果用 openpyxl 写Excel 可能不认识或者用引号包住了整段公式。4.4 在固定模板上填数load_workbook 的正确用法很多企业有固定模板比如报销单、采购申请、月度经营报表。模板里已经画好了边框、底色、logo每次只需要替换数值。这种情况下千万不要用 pandas 重新生成一份文件那会把模板样式全毁掉。正确做法是用 openpyxl 加载模板然后填数from openpyxl import load_workbook wb load_workbook(月度报表模板.xlsx) ws wb[汇总] # 在固定位置填数 ws[B3] 125000 ws[B4] 8900 ws[B5] ws[B3].value - ws[B4].value wb.save(2024年12月月度报表.xlsx)这里load_workbook加载的是已有文件保留所有原有样式和结构wb.save到新路径避免覆盖原模板。我在项目里维护过一个固定报表模板每个月只需要更新三四个单元格的数值整个脚本不超过 20 行响应速度极快。5. 两个库联合作战的月度汇总报表实战5.1 实战需求和数据源设计下面用一个我经手次数最多的场景来说明两个库如何配合每周要生成一份门店销售周报数据源是每天导出的销售明细文件每个文件包含订单号、门店名、销售日期、商品名称、数量、金额等字段。要求是汇总出本周每家门店的总销售额、订单量并输出一份带表头样式、自动列宽、数据排序合理的 Excel 报表。数据源文件命名规则是销售明细_20241201.xlsx这样带日期的格式。目录里有 7 个文件需要全部读进来合并。5.2 pandas 汇总部分分组、透视、排序先做数据读取和汇总这是 pandas 的主场import pandas as pd import glob files glob.glob(销售明细_*.xlsx) all_data [] for f in files: df pd.read_excel(f, dtype{订单号: str}) all_data.append(df) # 合并所有明细 raw pd.concat(all_data, ignore_indexTrue) # 统一日期格式 raw[销售日期] pd.to_datetime(raw[销售日期]) # 按门店分组汇总 result raw.groupby(门店名).agg( 订单量(订单号, count), 销售总额(金额, sum), ).reset_index() # 按销售总额排序 result result.sort_values(销售总额, ascendingFalse) # 新增平均每单金额列 result[平均客单价] (result[销售总额] / result[订单量]).round(2) print(result)这个分组汇总的过程如果用 openpyxl 手动写光循环累加就得写几十行而且很容易出错。pandas 的groupby配合agg一次搞定多个统计指标。reset_index()把分组键从索引转回普通列方便后续写入 Excel。5.3 openpyxl 呈现部分把数据刷进报表模板汇总数据准备完后用一个已有的周报模板.xlsx来填充。模板的第一行是主标题第二行是字段名第三行起是空白数据区from openpyxl import load_workbook wb load_workbook(周报模板.xlsx) ws wb[门店排名] # 从第3行开始写入数据 start_row 3 for idx, row in result.iterrows(): r start_row idx ws.cell(rowr, column1, valuerow[门店名]) ws.cell(rowr, column2, valuerow[订单量]) ws.cell(rowr, column3, valuerow[销售总额]) ws.cell(rowr, column4, valuerow[平均客单价]) wb.save(门店销售周报_本周.xlsx)这里用的是load_workbook加载模板而不是新建 Workbook就是为了保留模板里已经设置好的边框、配色、标题格式。如果你还要追加总计行可以像这样写total_row start_row len(result) ws.cell(rowtotal_row, column1, value合计) ws.cell(rowtotal_row, column3, valueresult[销售总额].sum()) total_cell ws.cell(rowtotal_row, column3) total_cell.font Font(boldTrue)每次往模板里填数时记得确认 Excel 模板里是否含有之前填入的脏数据。如果模板是上一周已经用过的里面的数据区可能有旧数据残留这种情况下建议先清理或者在模板里预置一个清空数据区的操作读取数据区范围逐格设置值为 None。5.4 数据量变大时的优化write_only 与分批写入日常小文件用默认模式没问题但如果数据量上了几十万行openpyxl 的内存占用会非常夸张。默认模式下整个工作表的数据和样式都会加载到内存。这时候可以开启write_only模式。from openpyxl import Workbook wb Workbook(write_onlyTrue) ws wb.create_sheet(数据) ws.append([订单号, 金额, 日期]) for chunk in pd.read_excel(大文件.xlsx, chunksize5000): for row in chunk.itertuples(indexFalse): ws.append(row) wb.save(大文件_输出.xlsx)write_only模式下数据直接流式写入硬盘内存占用大幅下降代价是你不能读取已有文件内容也不能做复杂的样式设置。适合大批量数据不需要精细样式、只要结果文件能打开的场景。配合read_excel的chunksize参数还能做到边读边写内存压力更小。如果数据操作本身很重建议用 dtype 先收敛类型把不需要的列在读取时就丢掉df pd.read_excel(大文件.xlsx, usecols[订单号, 金额, 日期], dtype{订单号: str})这个文件如果原本有 50 列只保留 3 列内存直接降到原来的十分之一不到。6. 反复踩过的坑和我现在的处理习惯6.1 数据类型坑合集我整理了一个自己踩过的高频坑清单分享出来比单独讲逻辑更实用。场景现象解法读取订单号/身份证号变成科学计数法或丢失末位读取时dtype{订单号: str}读取日期列变成Timestamp带时分秒parse_dates后dt.date转换全角空格混入文本分组统计时相同文本被拆成两组str.strip()str.replace( , )金额列混入文本求和报错could not convert string to float先pd.to_numeric(..., errorscoerce)Excel 合并单元格下方空值分组结果漏数据fillna(methodffill)前向填充最后一行的处理逻辑要再说清楚一点。errorscoerce会把无法转换的值直接变成 NaN不会中断程序之后再用fillna(0)处理整套流程不会因为一个脏数据导致脚本崩溃。这是我处理金额列最稳定的方式。df[金额] pd.to_numeric(df[金额], errorscoerce).fillna(0)6.2 openpyxl 追加写与覆盖写傻傻分不清ExcelWriter配合 pandas 写多个 sheet 时有一个经典问题第二次运行时同一个 sheet 名会覆盖还是追加with pd.ExcelWriter(多表汇总.xlsx, engineopenpyxl) as writer: df1.to_excel(writer, sheet_name汇总, indexFalse) df2.to_excel(writer, sheet_name明细, indexFalse)这个写法是安全的。ExcelWriter在 with 块结束时统一保存两次运行会覆盖同名 sheet。但如果像下面这样分开写两次writer pd.ExcelWriter(多表汇总.xlsx, engineopenpyxl) df1.to_excel(writer, sheet_name汇总, indexFalse) writer.save() df2.to_excel(writer, sheet_name汇总, indexFalse) # 这里会覆盖掉 df1 writer.close()第二个to_excel写同名 sheet 时会把前一次的 sheet 内容整个覆盖掉。如果你想在同一个文件里追加多个 sheet务必在一个with块里写。如果文件本来不存在openpyxl 引擎也没问题ExcelWriter会自动创建但如果文件已存在且你不想动原文件的其他 sheet就需要注意modea参数with pd.ExcelWriter(已存在.xlsx, engineopenpyxl, modea) as writer: df_new.to_excel(writer, sheet_name新表, indexFalse)modea表示追加模式只添加新 sheet不干扰已有内容。这个参数我用到的工作流是每天跑一次程序同一个文件里不断追加当天的日报表。6.3 文件被占用的问题Windows 系统下最常见的坑用 Excel 打开了某个 xlsx 文件然后 Python 脚本去写这个文件会直接抛出PermissionError。这个错误提示很明确就是文件正在被占用。我的处理习惯是脚本运行前用 try 包裹写入动作捕获权限错误后用日志输出明确提示而不是让脚本直接崩溃import os output_path 报表.xlsx try: wb.save(output_path) except PermissionError: print(f文件 {output_path} 被占用请先关闭 Excel 再运行脚本。)另外脚本每次生成时不要覆盖同一个文件名建议加上日期后缀比如门店周报_20241209.xlsx这样既避免占用又保留了历史版本。这个习惯在业务上也有价值——万一某周数据有问题可以随时回溯到上一周的文件。6.4 公式结果读不出来别慌前面提到过 openpyxl 读公式拿到的是公式字符串而不是计算值。如果你需要的是别人生成的文件里公式计算后的结果有一个办法让 Excel 自己算一遍再保存或者使用 LibreOffice 进行无界面转换。但在实际工作中我基本不依赖这个路径——更推荐的做法是要求数据提供方在导出时用粘贴数值的方式给数据或者在公式之外额外输出一份数值列。如果确实要读取公式计算值还有一个笨但稳定的方式用 pandas 读取文件时指定openpyxl引擎读取到的仍然是公式字符串。所以千万别在这上面浪费时间早点跟上游确认数据格式比一切代码技巧都管用。最后说一点我的体会。openpyxl 和 pandas 的关系可以类比成厨房和厨师pandas 是强大的厨师负责洗菜、切菜、配菜、炒菜把食材变成一盘成品openpyxl 是摆盘师负责把成品摆成好看的造型端上桌。两者谁也替代不了谁但配合好了就是一套完整的出餐流水线。我自己的流程已经固定成pandas 读数据、洗数据、算数据openpyxl 套模板、调样式、填结果一个脚本跑下来从原始明细到带格式的正式报表一气呵成。如果你刚开始接触 Python 处理 Excel建议也从这个组合入手遇到数据问题先想 pandas 的 API遇到格式问题再翻 openpyxl 的文档走通一次之后你会发现所谓的办公自动化其实就是一个把重复劳动拆解成固定套路的过程。