pandas读写Excel：引擎选型、参数配置与报表格式化指南

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主要负责读旧格式.xls，openpyxl负责读和写.xlsx，xlsxwriter只负责写.xlsx，而且写格式化内容时能力最强。pandas 在不指定engine参数的时候，会根据文件扩展名自动选一个能用的引擎，但“能用”不等于“正确”，尤其当你的环境里同时装了好几个版本库时，默认选择可能会踩到版本坑。

我整理了一张对照表，基本覆盖了日常用到的选择：

.xlsx文件的读和写，默认大概率会落到openpyxl身上，所以这个库基本是必装的。xlsxwriter则是在你准备交付一份带格式的报表时才更有存在感，它擅长设置列宽、单元格背景色、数字格式、冻结首行这些操作，而这些恰好是to_excel原生不支持的。.xls格式本身现在已经很少见了，但老系统、老接口里偶尔还会出现，这时候就需要xlrd==1.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 xlrd==1.2.0

然后在read_excel里养成显式指定引擎的习惯，哪怕只是engine="openpyxl"，也能把不确定性提前消除掉。排查报错的思路很简单：先确认文件真实格式，再确认对应引擎是否安装，最后确认 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_name="Sheet1", header=1, # 表头在第二行 usecols="A:G", # 只读 A 到 G 列 dtype={"工单号": str, "门店编号": str}, # 防止长数字变科学计数法 parse_dates=["创建时间"], )

如果表头上方不是一行标题，而是好几行备注，可以先用skiprows一次性跳过：skiprows=3表示前面 3 行完全不读。这里要注意，skiprows和header同时设置的顺序感很多人会混淆。我的理解是：pandas 先按skiprows跳过指定行，再在剩下的数据里用header找表头。所以如果文件前 4 行是废料，第 5 行才是表头，应该写skiprows=4, header=0，而不是header=4。

读完之后养成一个习惯，先看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_name=None是一种更偷懒的写法，等于一口气读全部 sheet，但文件大时内存压力会明显上来，我一般只在做临时探查时这么干。

类型陷阱是这个环节的重灾区。Excel 单元格里的数字在底层存的是 IEEE 754 浮点，长 ID 会被自动转成科学计数法，比如工单号1234567890123读进来可能变成1.23457E+12；带前导零的编码也会直接丢零，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="明细", index=False)

这里index=False是我强烈建议写上的。如果不写，pandas 会把 DataFrame 的行索引作为第一列一并写进 Excel，最后表格里多出一列没名字的 0、1、2、3……看起来非常业余。另一个值得关注的参数是columns，它可以控制写入列的顺序：

df.to_excel("output.xlsx", sheet_name="明细", index=False, columns=["日期", "门店", "销售额", "订单数"])

当df里有一堆临时辅助列时，这个参数能让你只输出最终要呈现的字段，不用提前del半天。

还有一个被忽略但很好用的参数是float_format。默认情况下3.14159265358979会原样写进单元格，报表里看就是一长串数字，设置float_format="%.2f"可以统一保留两位小数。当然，这只改了显示效果，源头数据还是完整的，适合做报表展示但不适合做数据导出。

3.2 多 Sheet 报表的标准写法与追加模式

要在一个 Excel 文件里写多个 sheet，必须借助ExcelWriter。我最推荐的方式是用with上下文管理器，因为它会在代码块结束时自动关闭文件，避免你忘记调用writer.save()。下面是一个标准写法：

with pd.ExcelWriter("月度报表.xlsx", engine="openpyxl") as writer: df_detail.to_excel(writer, sheet_name="明细", index=False) df_summary.to_excel(writer, sheet_name="汇总", index=False)

注意传入to_excel的第一个参数是writer对象，而不是文件路径，这个细节最容易写错。如果你在with块外又额外调用了writer.save()，反而会报 “Workbook has already been saved” 一类的错误。

如果文件已经存在，想往里面追加一个 sheet，用 openpyxl 引擎的追加模式：

with pd.ExcelWriter("月度报表.xlsx", engine="openpyxl", mode="a") as writer: df_new.to_excel(writer, sheet_name="新增", index=False)

追加模式有几个限制需要提前知道：xlsxwriter不支持mode="a"，所以追加时引擎必须选openpyxl；追加模式下如果你想覆盖某个已有 sheet，需要用if_sheet_exists="replace"参数，否则默认会报错或者新建同名 sheet。实际项目中，我很少用追加模式往一个不断增长的报表里塞数据，因为文件会越来越大，读写的稳定性反而不如固定周期重新生成一份完整文件。

3.3 让报表能直接交付：xlsxwriter 的格式微调

如果只是自己跑数，to_excel的原始输出完全够用。但一旦要交付给业务方，就得分一行、加个表头、冻结首行、把金额加千分位。这些 pandas 原生不支持，需要用xlsxwriter引擎在写出后操作 workbook 和 worksheet 对象。

一段完整的格式化输出大概是这样的：

with pd.ExcelWriter("销售报表.xlsx", engine="xlsxwriter") as writer: df_sales.to_excel(writer, sheet_name="销售明细", index=False) 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", nrows=5))

nrows=5的作用是只读前 5 行当样例，内存开销很小，但能看到表头长什么样、数据大概有哪些列。这一步花 1 分钟，能避免后面因为header行选错而重跑 3 次。

4.2 读取、清洗到计算的完整流程

确认结构后，我按下面这个流程写清洗逻辑：

import pandas as pd raw = pd.read_excel( "某零售门店销售原始记录.xlsx", sheet_name="Sheet1", header=1, usecols="A:G", dtype={"销售单号": str}, parse_dates=["日期"], na_values=["空白", "N/A"], ) raw.columns = ["日期", "门店", "品类", "销售额", "成本", "订单数", "备注"] # 去掉小计、合计行 raw = raw[~raw["门店"].str.contains("合计", na=False)] # 删除完全没有日期或门店的空行 raw = raw.dropna(subset=["日期", "门店"]) # 确保金额列是数值类型 raw["销售额"] = pd.to_numeric(raw["销售额"], errors="coerce") raw["成本"] = pd.to_numeric(raw["成本"], errors="coerce") # 有些 Excel 的“订单数”是字符串，先清洗再转数值 raw["订单数"] = raw["订单数"].astype(str).str.replace(",", "") raw["订单数"] = pd.to_numeric(raw["订单数"], errors="coerce").fillna(0) summary = raw.groupby("门店", as_index=False).agg( 销售总额=("销售额", "sum"), 成本总额=("成本", "sum"), 订单总量=("订单数", "sum"), )

这段流程里的每个操作都有明确目的。na_values是为了把业务中常见的“空白”“N/A”统一识别成缺失值；errors="coerce"确保某个脏单元格不会让整列转换失败，而是变成NaN留给后续处理；str.replace(",", "")处理的是 Excel 里带千分位的文本数字，这种数据直接astype(float)会报错。

清洗完一定要看一眼结果分布，比如raw.isnull().sum()和每个门店的记录条数。如果某门店的销售额突然少了一大截，多半是上面有小计行没被过滤干净。

4.3 输出多 Sheet 并添加格式

最后一步是把明细和汇总写进一个文件，并加上标题格式、列宽、金额千分位和冻结首行：

with pd.ExcelWriter("某零售门店月度报表.xlsx", engine="xlsxwriter") as writer: raw.to_excel(writer, sheet_name="清洗明细", index=False) summary.to_excel(writer, sheet_name="门店汇总", index=False) 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 读写的问题，说来说去就是那么几类。我把踩过的坑整理成一个速查表，平时遇到类似现象可以直接对照。

5.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.23E+12，其实底层数值还在，只是单元格默认格式把显示压缩了。解决方式有两个：要么在读取时就把这列指定为字符串，要么在写入后通过 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 出去，中间真正值钱的是处理逻辑，而不是函数调用本身。把引擎选型、参数组合、写入流程摸透之后，你会发现这套读写能力可以复用到所有同类型任务里，而且越用越顺手。