Excel 文件读写几乎是每个跟数据打交道的人都绕不过去的事pandas 里的read_excel和to_excel就是处理这件事的标准入口。以前我在做数据清洗和报表输出的时候用这两个函数最多但真正让我觉得“顺手”的不是记住多少个参数而是搞明白 Excel 文件从磁盘到 DataFrame、再从 DataFrame 回到工作簿中间到底经过了几层转换哪些坑是库版本造成的哪些坑是 Excel 本身的文件格式带来的。这篇文章会按照读取、写入、实战、排障四个层次来梳理适合刚把 pandas 基础语法过完、正想拿 Excel 练手的同学也适合写给报表脚本总觉得不对劲的同行参考。1. 先搞懂 Excel 读写的底层引擎很多人在read_excel报错的时候第一反应是去检查文件路径但问题往往出在底层引擎上。pandas 本身并不直接解析 Excel 的二进制结构它只是提供了一个统一接口真正在背后干活的是xlrd、openpyxl、xlsxwriter这几套库。搞清楚它们的分工后续很多诡异报错都能一眼定位。1.1 xlrd、openpyxl、xlsxwriter 到底谁负责什么先记住一个结论pandas 的 Excel 功能里xlrd主要负责读旧格式.xlsopenpyxl负责读和写.xlsxxlsxwriter只负责写.xlsx而且写格式化内容时能力最强。pandas 在不指定engine参数的时候会根据文件扩展名自动选一个能用的引擎但“能用”不等于“正确”尤其当你的环境里同时装了好几个版本库时默认选择可能会踩到版本坑。我整理了一张对照表基本覆盖了日常用到的选择引擎支持的格式能读能写典型限制xlrd.xls是否2.0 以后放弃.xls之外格式需锁 1.2.0openpyxl.xlsx、.xlsm是是写入格式化能力弱于 xlsxwriterxlsxwriter.xlsx否是不能读文件也不能追加已有 sheet.xlsx文件的读和写默认大概率会落到openpyxl身上所以这个库基本是必装的。xlsxwriter则是在你准备交付一份带格式的报表时才更有存在感它擅长设置列宽、单元格背景色、数字格式、冻结首行这些操作而这些恰好是to_excel原生不支持的。.xls格式本身现在已经很少见了但老系统、老接口里偶尔还会出现这时候就需要xlrd1.2.0。注意不是新版不行而是新版作者主动砍掉了旧格式支持属于上游的策略变化不是我们代码的问题。1.2 引擎选错时的典型报错与安装对策我见过最多的报错是这种ImportError: Missing optional dependency openpyxl. Use pip install openpyxl.这个提示已经很良心直接把解决方案写在报错里了但很多人会忽略一个前提即使安装了openpyxl如果文件是.xls仍然可能继续报错。还有一种更迷惑的情况同样的代码在同事电脑上跑得好好的到你电脑上就报ValueError: Excel file format cannot be determined, you must specify an engine manually。这多半是因为两边环境里装的 Excel 相关库组合不一样文件后缀和实际格式对不上。比如一个文件明明叫“xxx.xlsx”但实际是从旧系统导出、内部结构还是.xls这时指定引擎会更快暴露问题。我的建议是凡是写 Excel 相关脚本先把这几个库一次性装齐pip install openpyxl xlsxwriter pip install xlrd1.2.0然后在read_excel里养成显式指定引擎的习惯哪怕只是engineopenpyxl也能把不确定性提前消除掉。排查报错的思路很简单先确认文件真实格式再确认对应引擎是否安装最后确认 pandas 版本是否支持该引擎。三步下来绝大多数环境问题都能收干净。2. read_excel 参数的实际组合不同场景下的最优配置read_excel的参数很多但没必要全记真正高频使用、能救命的就那几个。我按使用频率和踩坑概率排了个优先级sheet_name、header、skiprows、usecols、dtype、parse_dates、na_values。下面拆开讲。2.1 表头不在第 1 行header 与 skiprows 怎么配合真实业务里的 Excel 很少像测试文件那样规规矩矩最常见的是第一行写着大标题比如“某门店 2026 年度销售数据”第二行才是真正的列名。这时候直接pd.read_excel(file)会把“某门店 2026 年度销售数据”当成表头整个数据结构就歪了。正确姿势是告诉 pandas 表头在第几行import pandas as pd df pd.read_excel( 销售数据.xlsx, sheet_nameSheet1, header1, # 表头在第二行 usecolsA:G, # 只读 A 到 G 列 dtype{工单号: str, 门店编号: str}, # 防止长数字变科学计数法 parse_dates[创建时间], )如果表头上方不是一行标题而是好几行备注可以先用skiprows一次性跳过skiprows3表示前面 3 行完全不读。这里要注意skiprows和header同时设置的顺序感很多人会混淆。我的理解是pandas 先按skiprows跳过指定行再在剩下的数据里用header找表头。所以如果文件前 4 行是废料第 5 行才是表头应该写skiprows4, header0而不是header4。读完之后养成一个习惯先看df.head()和df.columns确认表头名不是“Unnamed: 0”之类的东西。出现“Unnamed”列多半是原始表里有合并单元格或尾部空列后面清洗时会很烦。2.2 多 Sheet 读取与类型陷阱sheet_name是read_excel里灵活性最高的参数。传字符串读单个 sheet传整数按位置读传列表则返回一个以 sheet 名为 key 的字典data_dict pd.read_excel(销售数据.xlsx, sheet_name[0, 汇总])这个功能在分析多表结构时特别有用。你不确定文件里有多少个 sheet 时可以先拿ExcelFile探一遍excel_file pd.ExcelFile(销售数据.xlsx) print(excel_file.sheet_names)返回sheet_nameNone是一种更偷懒的写法等于一口气读全部 sheet但文件大时内存压力会明显上来我一般只在做临时探查时这么干。类型陷阱是这个环节的重灾区。Excel 单元格里的数字在底层存的是 IEEE 754 浮点长 ID 会被自动转成科学计数法比如工单号1234567890123读进来可能变成1.23457E12带前导零的编码也会直接丢零00123变成123。解决办法是读取时用dtype把不需要参与计算的列指定成字符串df pd.read_excel(工单.xlsx, dtype{工单号: str, 客户编码: str})还有一类隐蔽问题是 Excel 的日期。单元格看着是“2026-03-15”读进来可能是字符串也可能是datetime取决于原始录入方式。不统一的时候直接在读取阶段用parse_dates[日期]让 pandas 帮你转。万一日期列需要拆成年、月、季度做汇总转换后会更顺手。合并单元格也是一个经典坑。pandas 读合并单元格时通常只有左上角那个单元格有值其余位置是NaN。网上有人建议用ffill()向下填充但要注意如果合并区域下方紧接着新的一条记录这种填充容易串数据。我的处理原则是能不改原始表就不改读取后先把这种“伪 NaN”单独标记出来确认填充逻辑没问题再做。3. 写入 Excel从最简单的单表到多 Sheet 报表读取只是把数据装进来真正见真章的是输出。很多人的脚本在to_excel这一步翻车不是因为函数不会用而是不知道写出文件也需要上下文管理、不知道多 sheet 要靠ExcelWriter、不知道格式化要交给 xlsxwriter。3.1 单 Sheet 写入与两个必设参数最基础的写法一行就能搞定df.to_excel(output.xlsx, sheet_name明细, indexFalse)这里indexFalse是我强烈建议写上的。如果不写pandas 会把 DataFrame 的行索引作为第一列一并写进 Excel最后表格里多出一列没名字的 0、1、2、3……看起来非常业余。另一个值得关注的参数是columns它可以控制写入列的顺序df.to_excel(output.xlsx, sheet_name明细, indexFalse, columns[日期, 门店, 销售额, 订单数])当df里有一堆临时辅助列时这个参数能让你只输出最终要呈现的字段不用提前del半天。还有一个被忽略但很好用的参数是float_format。默认情况下3.14159265358979会原样写进单元格报表里看就是一长串数字设置float_format%.2f可以统一保留两位小数。当然这只改了显示效果源头数据还是完整的适合做报表展示但不适合做数据导出。3.2 多 Sheet 报表的标准写法与追加模式要在一个 Excel 文件里写多个 sheet必须借助ExcelWriter。我最推荐的方式是用with上下文管理器因为它会在代码块结束时自动关闭文件避免你忘记调用writer.save()。下面是一个标准写法with pd.ExcelWriter(月度报表.xlsx, engineopenpyxl) as writer: df_detail.to_excel(writer, sheet_name明细, indexFalse) df_summary.to_excel(writer, sheet_name汇总, indexFalse)注意传入to_excel的第一个参数是writer对象而不是文件路径这个细节最容易写错。如果你在with块外又额外调用了writer.save()反而会报 “Workbook has already been saved” 一类的错误。如果文件已经存在想往里面追加一个 sheet用 openpyxl 引擎的追加模式with pd.ExcelWriter(月度报表.xlsx, engineopenpyxl, modea) as writer: df_new.to_excel(writer, sheet_name新增, indexFalse)追加模式有几个限制需要提前知道xlsxwriter不支持modea所以追加时引擎必须选openpyxl追加模式下如果你想覆盖某个已有 sheet需要用if_sheet_existsreplace参数否则默认会报错或者新建同名 sheet。实际项目中我很少用追加模式往一个不断增长的报表里塞数据因为文件会越来越大读写的稳定性反而不如固定周期重新生成一份完整文件。3.3 让报表能直接交付xlsxwriter 的格式微调如果只是自己跑数to_excel的原始输出完全够用。但一旦要交付给业务方就得分一行、加个表头、冻结首行、把金额加千分位。这些 pandas 原生不支持需要用xlsxwriter引擎在写出后操作 workbook 和 worksheet 对象。一段完整的格式化输出大概是这样的with pd.ExcelWriter(销售报表.xlsx, enginexlsxwriter) as writer: df_sales.to_excel(writer, sheet_name销售明细, indexFalse) workbook writer.book worksheet writer.sheets[销售明细] header_format workbook.add_format({ bold: True, bg_color: #F2F2F2, border: 1, }) money_format workbook.add_format({num_format: #,##0.00}) worksheet.set_row(0, 24, header_format) worksheet.set_column(A:A, 14) worksheet.set_column(B:B, 12) worksheet.set_column(C:D, 16, money_format) worksheet.freeze_panes(1, 0)writer.sheets[销售明细]是在 DataFrame 写入后拿到实际 worksheet 对象的唯一入口。set_row(0, 24, header_format)是给第一行表头设置高度和样式set_column用来设置列宽第三列和第四列还叠加了金额格式。freeze_panes(1, 0)表示冻结第一行往下滚动时表头始终可见。这个小样板能覆盖大部分交付场景。如果你还想加一个汇总标题行可以用startrow参数把 DataFrame 下移几行再用worksheet.write(0, 0, 标题, title_format)手动写标题。需要设置打印区域、页眉页脚时worksheet对象上也有对应方法多翻翻 xlsxwriter 文档就能找到。4. 实战把一份脏乱的原始 Excel 变成可交付的多 Sheet 报表理论讲再多不如走一遍完整流程。我拿一个常见的业务场景举例某零售门店每周发来一份销售原始记录文件格式混乱第一行是标题第二行是表头中间夹杂着“小计”“合计”行日期有的是文本、有的是时间还有一列数字被 Excel 存成了科学计数法。需求是产出一份新的多 Sheet 报表一个 sheet 放清洗后的明细一个 sheet 放按门店汇总的统计写出的文件要带格式直接能发出去。4.1 先探查文件再读取拿到文件后我不会急着读全量而是先用ExcelFile看结构和 sheet 数量excel_file pd.ExcelFile(某零售门店销售原始记录.xlsx) print(excel_file.sheet_names) print(excel_file.parse(Sheet1, nrows5))nrows5的作用是只读前 5 行当样例内存开销很小但能看到表头长什么样、数据大概有哪些列。这一步花 1 分钟能避免后面因为header行选错而重跑 3 次。4.2 读取、清洗到计算的完整流程确认结构后我按下面这个流程写清洗逻辑import pandas as pd raw pd.read_excel( 某零售门店销售原始记录.xlsx, sheet_nameSheet1, header1, usecolsA:G, dtype{销售单号: str}, parse_dates[日期], na_values[空白, N/A], ) raw.columns [日期, 门店, 品类, 销售额, 成本, 订单数, 备注] # 去掉小计、合计行 raw raw[~raw[门店].str.contains(合计, naFalse)] # 删除完全没有日期或门店的空行 raw raw.dropna(subset[日期, 门店]) # 确保金额列是数值类型 raw[销售额] pd.to_numeric(raw[销售额], errorscoerce) raw[成本] pd.to_numeric(raw[成本], errorscoerce) # 有些 Excel 的“订单数”是字符串先清洗再转数值 raw[订单数] raw[订单数].astype(str).str.replace(,, ) raw[订单数] pd.to_numeric(raw[订单数], errorscoerce).fillna(0) summary raw.groupby(门店, as_indexFalse).agg( 销售总额(销售额, sum), 成本总额(成本, sum), 订单总量(订单数, sum), )这段流程里的每个操作都有明确目的。na_values是为了把业务中常见的“空白”“N/A”统一识别成缺失值errorscoerce确保某个脏单元格不会让整列转换失败而是变成NaN留给后续处理str.replace(,, )处理的是 Excel 里带千分位的文本数字这种数据直接astype(float)会报错。清洗完一定要看一眼结果分布比如raw.isnull().sum()和每个门店的记录条数。如果某门店的销售额突然少了一大截多半是上面有小计行没被过滤干净。4.3 输出多 Sheet 并添加格式最后一步是把明细和汇总写进一个文件并加上标题格式、列宽、金额千分位和冻结首行with pd.ExcelWriter(某零售门店月度报表.xlsx, enginexlsxwriter) as writer: raw.to_excel(writer, sheet_name清洗明细, indexFalse) summary.to_excel(writer, sheet_name门店汇总, indexFalse) workbook writer.book header_format workbook.add_format({ bold: True, bg_color: #F2F2F2, border: 1, }) money_format workbook.add_format({num_format: #,##0.00}) for name in [清洗明细, 门店汇总]: worksheet writer.sheets[name] worksheet.set_row(0, 24, header_format) worksheet.freeze_panes(1, 0) sheet1 writer.sheets[清洗明细] sheet1.set_column(A:A, 12) sheet1.set_column(B:C, 14) sheet1.set_column(D:E, 16, money_format) sheet2 writer.sheets[门店汇总] sheet2.set_column(A:A, 16) sheet2.set_column(B:D, 16, money_format)这种写法看起来多其实都是固定套路。把“格式化”和“写数据”分开先写数据再用 writer 拿到 worksheet 做样式逻辑上非常清晰。输出完成后我还会顺手用pd.read_excel把生成的报表读回来做一次“回读校验”确认 sheet 数量和关键汇总值都对得上。5. 常见问题与排查技巧速查表Excel 读写的问题说来说去就是那么几类。我把踩过的坑整理成一个速查表平时遇到类似现象可以直接对照。现象常见原因解决思路写文件报 PermissionError目标文件正被 Excel 打开关闭文件或让程序先写临时文件再替换生成的文件大小为 0忘掉 writer.save() / 没使用 with 块统一用with pd.ExcelWriter管理读.xls报 not supportedxlrd 版本太高安装xlrd1.2.0工单号变成科学计数法读取时被当作数值列dtype{工单号: str}日期列读出来是字符串原始单元格本身是文本加parse_dates[日期]再复查表头变成“Unnamed: 0”有合并单元格或多余空列用usecols限定范围再手动 rename读大文件慢到无法忍受一次性读所有 sheet 和列用usecols、nrows限制或要求源头转 CSV5.1 文件被占用、生成空文件Windows 下最常见的场景是报表文件在 Excel 里开着脚本一运行to_excel直接抛PermissionError。这不是代码逻辑问题而是操作系统文件锁。我的处理习惯是让脚本先把结果写到带时间戳的临时文件比如report_20260315_1800.xlsx这样即使原文件被占用至少不会整个流程中断。生成空文件的另一个原因是没用with管理ExcelWriter。因为ExcelWriter是在close()时才真正把内容写入磁盘如果提前抛了异常文件可能只剩一个空壳。上下文管理器不仅简化代码还能在异常时正确关闭对象。5.2 读大文件慢、内存暴涨怎么办Excel 本身就不是为几十万行数据设计的格式pandas 读这种文件时内存会成倍放大。我的建议是分三层处理先看能不能只读一部分列用usecols把用不到的字段砍掉再看能不能只读前 N 行做抽样用nrows很快能验证清洗逻辑如果数据量大到必须全量处理最好让上游导出 CSV 或 Parquet读取速度会比 Excel 快一个量级。还有一个容易忽略的点同一个 Excel 文件里的多个 sheet 会共享文件结构一次读全部 sheet 的内存不是简单相加但确实会让可用内存变得很紧非必要不这么做。5.3 写回后“变样”了科学计数法与日期乱码数据写回 Excel 后“变样”通常不是 pandas 的问题而是 Excel 的显示方式。长数字在单元格里显示成1.23E12其实底层数值还在只是单元格默认格式把显示压缩了。解决方式有两个要么在读取时就把这列指定为字符串要么在写入后通过 xlsxwriter 给整列设置num_format为0强制按整数显示。日期乱码也分两种一种是把NaT写成了NaN或空字符串通常要用na_rep参数控制空值显示另一种是日期变成了 45000 这样的序列数说明这列在写入前还是整数或对象类型没有先转成datetime。我的经验是写文件之前先统一执行一遍pd.to_datetime让 pandas 明确知道这列是时间再交给to_excel基本不会出现序列数问题。最后分享几个我常年保留的实操习惯第一个习惯是所有脚本都必须能“回读自检”写完 Excel 后用pd.read_excel再读一遍校验行数、列名、汇总值是否和预期一致。这个动作成本很低却能抓住大多数低级事故。第二个习惯是文件名带上日期和版本号避免每次覆盖同一个文件导致历史数据无法追溯。第三个习惯是凡是给业务方的报表我都用 xlsxwriter 显式设置打印区域和列宽而不是把原始 DataFrame 直接裸写出去。多花 3 分钟做的格式化能让看报表的人少问十句“这列是什么意思”。数据从 Excel 进来再回到 Excel 出去中间真正值钱的是处理逻辑而不是函数调用本身。把引擎选型、参数组合、写入流程摸透之后你会发现这套读写能力可以复用到所有同类型任务里而且越用越顺手。