做数据分析的人十个里有九个躲不开Excel文件。业务方交付数据常常是“把这个表给我处理一下”而这个表就是Excel你要交出去的结果也常常被要求“导成一个Excel”。所以Pandas数据的输入与输出最绕不开的就是Excel文件读写。这篇我就把read_excel和to_excel从参数到坑从环境到实战完整讲一遍。适合刚上手Pandas想系统弄清楚读写细节的人也适合已经被Excel脏数据折磨到想撞墙的分析师。老规矩我不喜欢只贴代码然后说“你看就这样”。我会把每个关键参数背后的为什么、什么时候用、以及我踩过的坑一起讲清楚。看完之后你手里的Excel文件至少能稳稳地读进来、干干净净地写出去。1. 先搞清楚Pandas读写Excel的底层依赖1.1 Excel文件在Pandas眼里到底是什么这和很多人直觉不一样Pandas本身并不会“解析Excel”。Excel文件不是纯文本.xlsx本质上是一个zip压缩包里面装着很多XML文件分别描述工作表、单元格、样式、公式等等。.xls则是另一种老的二进制格式规则更麻烦。Pandas没有打算自己实现这套解析逻辑因为已经有更专业的库在做这件事。它只提供一个统一接口背后调用第三方引擎去读取或写入。这就是为什么你只pip install pandas之后直接读Excel常常报错提示缺openpyxl或者xlrd。不是Pandas坏掉了而是它依赖的“翻译官”没装。这个依赖关系特别重要因为很多莫名其妙的问题都出在引擎选错上。比如.xls和.xlsx对应库不同同一个引擎在不同版本支持范围还会变。先把这个底摸清楚后面排错才有方向。1.2 引擎选型与最小环境安装一套能正常读写Excel的最小环境至少要装这几个包pandas主菜。openpyxl负责读写.xlsx格式是目前Pandas读写新格式Excel的主要引擎写和读都能干。xlrd负责读取老旧的.xls格式文件。xlsxwriter只负责写入.xlsx不负责读。如果你有特殊写需求比如写公式、条件格式可以考虑它。安装命令很简单pip install pandas openpyxl xlrd xlsxwriter如果你想控制版本我建议至少保证pandas1.3 openpyxl3.0 xlrd2.0但这里有一个极其经典的版本坑xlrd 2.0以后官方砍掉了对.xlsx的支持只保留老格式.xls的读取能力。所以如果你用xlrd去读.xlsx会直接报错。别装一个老掉牙的xlrd版本去凑合正确做法是让.xls走xlrd让.xlsx走openpyxl。Pandas在没有明确指定引擎时会根据文件扩展名自动选择。一般规则是文件格式读取引擎写入引擎.xlsxopenpyxlopenpyxl或xlsxwriter.xlsxlrd需要xlwt已停止维护尽量别写老格式.odsodfodf真正上手的时候大部分场景都是处理.xlsx所以你只要确保openpyxl装好就解决了80%的问题。文件打不开时如果报错提示“Missing optional dependency openpyxl”不需要怀疑人生补装它就行。2. 读取Excel用熟read_excel的核心参数2.1 最基础的读法以及表头和索引的坑读取一个Excel文件最基础的写法是import pandas as pd df pd.read_excel(data.xlsx)如果不加任何参数Pandas默认帮你做三件事读取第一个工作表、把第一行当作列名、自动生成一列从0开始的索引。听起来挺正常但实际项目中通常第一行不一定是表头或者第一列本来应该是索引但被当成了普通数据。常见的几个调整参数# 文件没有表头所有行都是数据 df pd.read_excel(data.xlsx, headerNone) # 第一列是行索引不要当成普通列 df pd.read_excel(data.xlsx, index_col0) # 前几行是说明文字跳过它们再找表头 df pd.read_excel(data.xlsx, skiprows2)index_col0的意思是把第一列作为DataFrame的索引这通常出现在导出时带了索引列或者Excel里本身有一列ID。这个一定要养成检查习惯不然后面聚合或者拼接容易出现重复索引。skiprows的坑在于它和header会互相影响。比如你跳过两行说明文字后第三行才是表头那写skiprows2其实已经把原先的表头行也当成“跳过”了Pandas会把原本的第三行当作表头。这个行为在多数情况下是正确的但如果你只想跳过说明行而保留原表头就得按实际行号算清楚。我的建议是先用Excel打开看一个大概结构再决定参数别靠猜。2.2 控制读取范围sheet、列、行Excel文件最容易让人头疼的是一个工作簿里塞了好几个表。想只读其中一部分依赖三个关键参数。sheet_name指定工作表。可以是工作表名称字符串也可以是位置数字比如0表示第一个sheet。如果传列表返回的是一个dictkey是sheet名value是对应DataFrame。# 按名称读取 df pd.read_excel(data.xlsx, sheet_name订单明细) # 读取多个sheet返回字典 dfs pd.read_excel(data.xlsx, sheet_name[订单明细, 退货明细]) print(dfs[订单明细]) # 读取所有sheet all_dfs pd.read_excel(data.xlsx, sheet_nameNone)关于sheet_nameNone的使用场景当你不确定工作簿里有哪些sheet或者想一口气全部读进来时很好用。但要注意如果各sheet结构不一样后面还要单独清洗别指望直接concat。usecols指定要读哪些列。它支持好几类写法最实用的是直接传列名列表或者传Excel列字母区间。# 只读指定的三列 df pd.read_excel(data.xlsx, usecols[姓名, 部门, 工资]) # 用列字母区间A到C df pd.read_excel(data.xlsx, usecolsA:C)这个参数在大表场景下非常关键。很多Excel文件动辄几十上百列实际分析只用其中几列不限制列数的话内存和时间都浪费在读没用的数据上。nrows只读取前N行。配合skiprows可以做抽样预览或者跳过表头后读取前面一部分数据来做格式探测。# 跳过头两行后只读10行 df pd.read_excel(data.xlsx, skiprows2, nrows10)注意nrows是读取行数上限不是从第N行读到第M行。如果你需要读取中间的某一段办法是skiprows加nrows组合但这样会把前段全部读一遍再丢弃性能上不一定划算。Excel格式本身就不支持随机跳读这种场景比较少。2.3 数据类型控制dtype、parse_dates、na_valuesExcel单元格里的格式是很“虚”的东西。一个单元格显示为日期底层可能存储的是日期对象也可能是一串文本甚至是一个数字序列号。一个单元格看起来是数字也可能因为混入了空格或逗号被Pandas读成字符串。所以读Excel后第一件事永远是检查dtypes。dtype参数可以强制指定列的类型df pd.read_excel( data.xlsx, dtype{订单号: str, 金额: float} )订单号这类ID列最好显式指定为字符串不然很长一串数字可能被读成科学计数法后面匹配的时候对不上。而金额列如果本身有缺失或文本dtypefloat可能会报错所以有时候我们宁愿先读进来再用pd.to_numeric(..., errorscoerce)处理。日期解析是另一个重头戏。Excel里最让人头疼的日期形态有三种真正的Excel日期单元格openpyxl读出来是datetime。单元格里是一段标准文本比如2024-05-18 14:30需要用parse_dates处理。单元格里是纯数字比如45064这是Excel日期序列号需要特殊转换。parse_dates可以接一个列名列表把指定列尝试解析成日期df pd.read_excel( data.xlsx, parse_dates[下单时间] )如果日期列同时包含多种格式或者时间戳和日期分开两列你还可以传一个组合列名列表比如parse_dates{下单时间: [下单日期, 下单时刻]}要求Pandas把两列合并解析成一个时间列。这个不是特别常用但遇到时很好使。缺失值的处理也不能靠默认。实际Excel里经常用-、NULL、#N/A表示空值。Pandas有自己的默认缺失值列表但默认不包括-这种。你需要用na_values手动补充df pd.read_excel( data.xlsx, na_values[-, NULL, #N/A] )如果文件里大量使用其他符号表示空值可以在na_values里传一个列表或者字典。经常有业务数据把0也当成空值但直接加na_values[-, 0]会把真正的0也变NaN非常危险。我建议先看数据分布再决定是否把某个数字当成缺失值。3. 写入Excel用熟to_excel和ExcelWriter3.1 一行代码导出但记得关掉Index写入Excel的入门用法是df.to_excel(output.xlsx)这句代码确实能跑但我几乎从来不会这么直接写因为它默认会把DataFrame的索引也写进文件第一列会多出一列无名的索引列。如果你再把这文件读回来就会多出一列“Unnamed: 0”非常烦人。所以导出时第一件事记熟df.to_excel(output.xlsx, indexFalse)indexFalse的意思是“不把索引写入文件”。如果你的索引本身是有业务含义的列比如日期或ID那别急着关掉。你可以先用df.reset_index()把它变成普通列再导出这样列名和内容都清晰。除了index还有一个很实用的参数是float_formatdf.to_excel(output.xlsx, indexFalse, float_format%.2f)这能保证浮点列保留两位小数。很多人说“我明明在DataFrame里是两位小数导出来怎么变成一堆小数位”原因就是浮点数的底层存储问题。float_format是导出时最省事的格式化手段。columns参数可以只导出DataFrame中的部分列df.to_excel(output.xlsx, indexFalse, columns[订单号, 金额, 状态])适合一个DataFrame里有很多中间计算列但最终交付只需要少数几列的场景。3.2 多Sheet写入ExcelWriter才是正解如果只写一个sheetdf.to_excel(output.xlsx)没问题。但如果你想在一个文件里写多个sheet千万别这样写df1.to_excel(output.xlsx, sheet_name汇总) df2.to_excel(output.xlsx, sheet_name明细)这样第二次写入会把整个文件覆盖掉最后只剩下df2的结果。正确做法是使用pd.ExcelWriterwith pd.ExcelWriter(report.xlsx) as writer: df1.to_excel(writer, sheet_name汇总, indexFalse) df2.to_excel(writer, sheet_name明细, indexFalse)ExcelWriter的with写法会自动帮你提交并关闭文件不用手动处理。在同一个writer下多个DataFrame可以写到不同sheet互相不覆盖。还有一种情况你想往一个已经存在的Excel文件里追加一个新sheet。Pandas从版本1.3以后支持modeawith pd.ExcelWriter(report.xlsx, modea, if_sheet_existsreplace) as writer: df3.to_excel(writer, sheet_name新增表, indexFalse)其中if_sheet_exists可以设置如果同名sheet已经存在时的处理策略replace覆盖new新建一个带数字后缀的sheet。注意追加时引擎要和原文件格式匹配一般都要显式指定engineopenpyxl因为追加写入只有openpyxl支持得比较好。3.3 让导出的Excel更美观列宽、冻结窗格、颜色Pandas本身只负责“把数据写进格子”不负责排版。但实际交付报表时如果文件打开列宽都挤在一起观感会差很多。好在ExcelWriter可以拿到底层的openpyxl对象二次加工很灵活。with pd.ExcelWriter(report.xlsx, engineopenpyxl) as writer: df.to_excel(writer, sheet_name汇总, indexFalse) ws writer.sheets[汇总] ws.column_dimensions[A].width 12 ws.column_dimensions[B].width 20 ws.freeze_panes A2freeze_panes是冻结窗格A2表示把第1行冻结滚动时始终能看到表头。如果你同时想出“第一列也冻结”就设置为B2。还可以设置单元格格式比如把表头加粗from openpyxl.styles import Font with pd.ExcelWriter(report.xlsx, engineopenpyxl) as writer: df.to_excel(writer, sheet_name汇总, indexFalse) ws writer.sheets[汇总] ws.column_dimensions[A].width 20 for cell in ws[1]: cell.font Font(boldTrue)这里ws[1]是工作表第一行也就是表头。如果你不想做太复杂的样式用openpyxl动手改比在Pandas层硬凑方便得多。4. 实战一份脏Excel从清洗到报表输出的完整流程4.1 场景是这样的假设你拿到一份“销售明细_2024.xlsx”里面只有一个sheet“原始数据”。数据大概长这样下单时间有的单元格是真正的日期时间有的是文本2024/1/5 9:30。金额有的数值是1234.56有的是带千分位的1,234.56。缺失值手工填了-。订单号是一长串数字必须按字符串处理。目标很明确把这份脏数据清洗成规整的DataFrame然后按月份汇总销售额输出一个包含“月度汇总”和“清洗明细”两个sheet的报表文件。这种场景太常见了常见到几乎每次做项目都会遇到。下面是我习惯的写法。4.2 代码和关键点解析import pandas as pd # 读取原始数据 df pd.read_excel( 销售明细_2024.xlsx, sheet_name原始数据, dtype{订单号: str}, thousands,, na_values[-], parse_dates[下单时间], ) # 如果金额列仍有非数值内容强制转换 df[金额] pd.to_numeric(df[金额], errorscoerce) # 把时间列标准化成字符串月份 df[月份] df[下单时间].dt.strftime(%Y-%m) # 按月汇总销售额 report df.groupby(月份, as_indexFalse)[金额].sum() report report.rename(columns{金额: 销售额}) # 输出报表 with pd.ExcelWriter(销售报表_2024.xlsx) as writer: df.to_excel(writer, sheet_name清洗明细, indexFalse) report.to_excel(writer, sheet_name月度汇总, indexFalse)几个容易忽略的细节thousands,解决的是千分位字符串问题。如果金额列里混了逗号不写这个参数Pandas会把它当成字符串后面求和全乱。dtype{订单号: str}保证订单号读进来是字符串避免长数字被科学计数法。na_values[-]把业务中手工填的短横线转成NaN。parse_dates直接解析下单时间。但这里要注意如果文件里既有真正的日期时间对象又有文本日期parse_dates大多数情况下能处理但偶尔还是会解析失败。稳妥的做法是先检查再决定要不要用errorscoerce。4.3 输出以后一定要读回来看一眼很多人导出完文件就完事了觉得“反正代码没报错”。但Excel类文件最坑的地方就在于运行没报错不代表结果对。我强烈建议养成一个回读校验的习惯# 回读检查 rename_check pd.read_excel(销售报表_2024.xlsx, sheet_name月度汇总) detail_check pd.read_excel( 销售报表_2024.xlsx, sheet_name清洗明细, dtype{订单号: str}, ) print(detail_check.shape) print(detail_check.dtypes) print(report.head())这一步至少能确认三件事行数对不对有没有因为错误的缺失值处理把有效行变成NaN然后被drop掉。列类型对不对订单号有没有变回数字或科学计数法。汇总结果和业务口径是否一致比如月份数量是否符合预期。我见过太多人因为某个列在导出的Excel里被Excel软件自动改成日期格式导致回读后数据完全变样。回读校验不能保证所有细节都对但至少能拦住大部分低级错误。5. 常见报错和排查手册5.1 引擎相关报错装是装了怎么还是报错读Excel报错里最经典的一类就是明明装了库却还是在报引擎错。常见报错长这样ValueError: Excel file format cannot be determined, you must specify an engine manually.这通常不是你代码写错而是文件扩展名和真实格式不匹配。比如一个文件实际内容是.xlsx但后缀被人改成了.xls或者反过来。Pandas拿不到可信的格式信息只能让你手动指定引擎。df pd.read_excel(奇怪的文件.xls, engineopenpyxl)遇到这种文件先别急着写死引擎用Excel打开看看到底是什么格式。如果只是扩展名不对可以重命名如果不确定可以用文件头判断。但最省事的方法是打开文件另存为正确的.xlsx再给Pandas处理。另一个高频报错ImportError: Missing optional dependency openpyxl. Use pip or conda to install openpyxl.这个最简单直接装缺的库。但还有一种隐蔽情况报错里的库你明明装了却仍然找不到大概率是当前Python环境和Pandas不在同一个环境。用pip list确认一下别装错了虚拟环境。5.2 读出来的数据全是NaN或者类型不对“我Excel里明明白白写着123读出来变成NaN”这种事每个做数据处理的人应该都经历过。排查思路一般按以下顺序是不是那一列里混了不可见字符比如空格、换行符。123旁边有个空格会变成字符串再与其他数字混合整列类型变object。是不是某些单元格里其实存的是公式但公式结果为空openpyxl默认读的是公式结果如果缓存里没有结果可能读到None。是不是把自己定义的缺失值符号误加了。比如na_values[-, 0]然后真实的0也被当成缺失值。类型不对的另一个经典场景是日期列。你明明看到2024-05-18读进来却是数字45065这是因为Excel底层存储日期用的是“从1900年1月1日开始的天数”。Pandas在读老格式或某些特殊引擎时可能没有正确识别单元格的日期格式把它当普通数字读了。处理办法df[下单时间] pd.to_datetime(df[下单时间], origin1899-12-30, unitD)这里unitD表示单位是天origin取1899-12-30是为了对齐Excel的序列号规则。具体差多少天和Excel版本有关但上面的组合是Excel日期序列号最常用的转换方式。5.3 Excel文件太大读得很慢怎么办Excel本身不是为大数据设计的。.xlsx里即使只有20万行读起来可能也要十几秒和CSV完全不是一个量级。更大的问题是Pandas的read_excel没有read_csv里的chunksize参数没法直接分块迭代读取。如果只是想要快速看看数据可以用nrows抽样df_sample pd.read_excel(big.xlsx, nrows5000)这样至少能先了解列名和大致内容。如果确实需要全量读取又嫌慢有几个优化思路用usecols只保留分析的列减少数据量。用dtype显式指定数值列避免Pandas做类型推断开销。考虑先把Excel转成CSV再用read_csv的chunksize分块处理。Excel转CSV可以用Excel本身也可以用小工具批量处理。如果是.xlsx也可以用openpyxl的read_onlyTrue模式自己去迭代但这就绕过了Pandas的接口适合特别精通底层的人。我个人的经验是超过一定体量后的Excel文件最好的处理方式不是硬用Pandas读而是找源头导出CSV或者数据库。Excel作为交付格式没问题但作为大数据的“传输格式”真的不合适。5.4 写出的Excel日期、数字看着不对写入时最常见的两个问题一是日期变成了“一串数字”二是浮点数小数位失控。日期变成数字通常是因为DataFrame里的日期类型是datetime64但写出来后openpyxl把它当普通对象处理没设置成Excel日期单元格格式。多数情况下to_excel会自动识别datetime类型但在某些版本或引擎下会失效。解决办法是在写入前先把时间列转为Python原生的datetime.datetimedf[下单时间] df[下单时间].dt.to_pydatetime()浮点数小数位失控前面提到过用float_format解决。如果还是不行检查一下是不是列里有文本型数字混合类型会导致整个列被Pandas判定为objectfloat_format根本不起作用。这时候先清洗列类型再导出。6. 进阶玩法批量合并Excel和模板化输出6.1 批量读取一个文件夹下所有Excel实际工作中经常要处理一堆格式相同的Excel文件比如每个部门交一份表每个月一个文件。手动read_excel再concat也能做但循环写得不好会非常慢。我的标准做法是from pathlib import Path import pandas as pd files list(Path(./data).glob(*.xlsx)) df_list [] for file in files: df pd.read_excel(file, sheet_name数据, dtype{订单号: str}) df[文件名] file.stem # 记录来源方便排查 df_list.append(df) result pd.concat(df_list, ignore_indexTrue)有几个细节值得说glob(*.xlsx)不会匹配.xls如果有两种格式需要分别处理。每个文件读取后要加一列文件名作为来源否则后面发现问题时根本不知道这条数据来自哪个文件。concat时不要用默认索引设置ignore_indexTrue保证最终索引连续。如果文件数量很大建议先读一个文件了解结构保持一致后再批量处理。如果各文件的sheet名或列名不同批量处理会出大问题。6.2 写Excel时可以控制起始单元格to_excel里有两个不算太显眼但很实用的参数startrow和startcol。它们决定从哪个位置开始写入。with pd.ExcelWriter(output.xlsx) as writer: report.to_excel(writer, sheet_name汇总, indexFalse, startrow0, startcol0) df.to_excel(writer, sheet_name汇总, indexFalse, startrowlen(report) 3, startcol0)这种方法可以在同一个sheet里上下堆叠两个表中间留空行。虽然不常见但做那种“一页纸报表”时挺有用比如上面放汇总下面放明细。要注意的是startrow是从0开始计的不是从1。如果你希望Excel的第1行不写数据从第2行开始startrow应该等于1。我在这上面犯过错导出的表头跑到第二行去了。6.3 写入公式而非硬编码数值Pandas本身不鼓励你往DataFrame里塞公式因为公式是Excel层的东西。但有时候报表需要在Excel里保留动态计算比如“合计”这一格。你可以在用to_excel写出后再用openpyxl往特定单元格写公式from openpyxl import load_workbook with pd.ExcelWriter(output.xlsx, engineopenpyxl, modea, if_sheet_existsoverlay) as writer: df.to_excel(writer, sheet_name汇总, indexFalse, startcol0) wb load_workbook(output.xlsx) ws wb[汇总] ws[E10] SUM(E2:E9) wb.save(output.xlsx)用xlsxwriter引擎也可以直接写公式with pd.ExcelWriter(output.xlsx, enginexlsxwriter) as writer: df.to_excel(writer, sheet_name汇总, indexFalse) workbook writer.book worksheet writer.sheets[汇总] worksheet.write_formula(E10, SUM(E2:E9))这里要注意公式在Python读取时不会自动计算除非Excel软件打开并重新计算过。所以如果你用代码回读这个文件那个公式单元格的值大概率是0或者None。验证公式结果的时候别直接用Pandas读要先用Excel打开保存一次再读或者接受这个限制。我个人在实际操作中的体会是Excel的读写从来不是“API背熟”就能做好的事真正的功夫在于对文件本身的理解。拿到一个Excel第一件事不是写代码而是先搞清楚有几个sheet、表头在哪几行、列里混了什么脏值、日期是真是假。把这几个问题确认完代码往往就是几行read_excel和to_excel的事。最后再分享一个小习惯每次导出Excel后我都会立刻用pd.read_excel读回来至少看一眼shape和dtypes。这个习惯帮我在很多项目里提前发现过“索引列混进去了”“日期变成序列号”“金额列多出一堆NaN”等低级问题。你在这一步栽过一次跟头就会明白这个回读动作有多值钱。 
长河君 · 2026/10/24 1:21:22 · 阅读 1.2k Pandas读写Excel实战指南：read_excel与to_excel核心参数与避坑
做数据分析的人十个里有九个躲不开Excel文件。业务方交付数据常常是“把这个表给我处理一下”而这个表就是Excel你要交出去的结果也常常被要求“导成一个Excel”。所以Pandas数据的输入与输出最绕不开的就是Excel文件读写。这篇我就把read_excel和to_excel从参数到坑从环境到实战完整讲一遍。适合刚上手Pandas想系统弄清楚读写细节的人也适合已经被Excel脏数据折磨到想撞墙的分析师。老规矩我不喜欢只贴代码然后说“你看就这样”。我会把每个关键参数背后的为什么、什么时候用、以及我踩过的坑一起讲清楚。看完之后你手里的Excel文件至少能稳稳地读进来、干干净净地写出去。1. 先搞清楚Pandas读写Excel的底层依赖1.1 Excel文件在Pandas眼里到底是什么这和很多人直觉不一样Pandas本身并不会“解析Excel”。Excel文件不是纯文本.xlsx本质上是一个zip压缩包里面装着很多XML文件分别描述工作表、单元格、样式、公式等等。.xls则是另一种老的二进制格式规则更麻烦。Pandas没有打算自己实现这套解析逻辑因为已经有更专业的库在做这件事。它只提供一个统一接口背后调用第三方引擎去读取或写入。这就是为什么你只pip install pandas之后直接读Excel常常报错提示缺openpyxl或者xlrd。不是Pandas坏掉了而是它依赖的“翻译官”没装。这个依赖关系特别重要因为很多莫名其妙的问题都出在引擎选错上。比如.xls和.xlsx对应库不同同一个引擎在不同版本支持范围还会变。先把这个底摸清楚后面排错才有方向。1.2 引擎选型与最小环境安装一套能正常读写Excel的最小环境至少要装这几个包pandas主菜。openpyxl负责读写.xlsx格式是目前Pandas读写新格式Excel的主要引擎写和读都能干。xlrd负责读取老旧的.xls格式文件。xlsxwriter只负责写入.xlsx不负责读。如果你有特殊写需求比如写公式、条件格式可以考虑它。安装命令很简单pip install pandas openpyxl xlrd xlsxwriter如果你想控制版本我建议至少保证pandas1.3 openpyxl3.0 xlrd2.0但这里有一个极其经典的版本坑xlrd 2.0以后官方砍掉了对.xlsx的支持只保留老格式.xls的读取能力。所以如果你用xlrd去读.xlsx会直接报错。别装一个老掉牙的xlrd版本去凑合正确做法是让.xls走xlrd让.xlsx走openpyxl。Pandas在没有明确指定引擎时会根据文件扩展名自动选择。一般规则是文件格式读取引擎写入引擎.xlsxopenpyxlopenpyxl或xlsxwriter.xlsxlrd需要xlwt已停止维护尽量别写老格式.odsodfodf真正上手的时候大部分场景都是处理.xlsx所以你只要确保openpyxl装好就解决了80%的问题。文件打不开时如果报错提示“Missing optional dependency openpyxl”不需要怀疑人生补装它就行。2. 读取Excel用熟read_excel的核心参数2.1 最基础的读法以及表头和索引的坑读取一个Excel文件最基础的写法是import pandas as pd df pd.read_excel(data.xlsx)如果不加任何参数Pandas默认帮你做三件事读取第一个工作表、把第一行当作列名、自动生成一列从0开始的索引。听起来挺正常但实际项目中通常第一行不一定是表头或者第一列本来应该是索引但被当成了普通数据。常见的几个调整参数# 文件没有表头所有行都是数据 df pd.read_excel(data.xlsx, headerNone) # 第一列是行索引不要当成普通列 df pd.read_excel(data.xlsx, index_col0) # 前几行是说明文字跳过它们再找表头 df pd.read_excel(data.xlsx, skiprows2)index_col0的意思是把第一列作为DataFrame的索引这通常出现在导出时带了索引列或者Excel里本身有一列ID。这个一定要养成检查习惯不然后面聚合或者拼接容易出现重复索引。skiprows的坑在于它和header会互相影响。比如你跳过两行说明文字后第三行才是表头那写skiprows2其实已经把原先的表头行也当成“跳过”了Pandas会把原本的第三行当作表头。这个行为在多数情况下是正确的但如果你只想跳过说明行而保留原表头就得按实际行号算清楚。我的建议是先用Excel打开看一个大概结构再决定参数别靠猜。2.2 控制读取范围sheet、列、行Excel文件最容易让人头疼的是一个工作簿里塞了好几个表。想只读其中一部分依赖三个关键参数。sheet_name指定工作表。可以是工作表名称字符串也可以是位置数字比如0表示第一个sheet。如果传列表返回的是一个dictkey是sheet名value是对应DataFrame。# 按名称读取 df pd.read_excel(data.xlsx, sheet_name订单明细) # 读取多个sheet返回字典 dfs pd.read_excel(data.xlsx, sheet_name[订单明细, 退货明细]) print(dfs[订单明细]) # 读取所有sheet all_dfs pd.read_excel(data.xlsx, sheet_nameNone)关于sheet_nameNone的使用场景当你不确定工作簿里有哪些sheet或者想一口气全部读进来时很好用。但要注意如果各sheet结构不一样后面还要单独清洗别指望直接concat。usecols指定要读哪些列。它支持好几类写法最实用的是直接传列名列表或者传Excel列字母区间。# 只读指定的三列 df pd.read_excel(data.xlsx, usecols[姓名, 部门, 工资]) # 用列字母区间A到C df pd.read_excel(data.xlsx, usecolsA:C)这个参数在大表场景下非常关键。很多Excel文件动辄几十上百列实际分析只用其中几列不限制列数的话内存和时间都浪费在读没用的数据上。nrows只读取前N行。配合skiprows可以做抽样预览或者跳过表头后读取前面一部分数据来做格式探测。# 跳过头两行后只读10行 df pd.read_excel(data.xlsx, skiprows2, nrows10)注意nrows是读取行数上限不是从第N行读到第M行。如果你需要读取中间的某一段办法是skiprows加nrows组合但这样会把前段全部读一遍再丢弃性能上不一定划算。Excel格式本身就不支持随机跳读这种场景比较少。2.3 数据类型控制dtype、parse_dates、na_valuesExcel单元格里的格式是很“虚”的东西。一个单元格显示为日期底层可能存储的是日期对象也可能是一串文本甚至是一个数字序列号。一个单元格看起来是数字也可能因为混入了空格或逗号被Pandas读成字符串。所以读Excel后第一件事永远是检查dtypes。dtype参数可以强制指定列的类型df pd.read_excel( data.xlsx, dtype{订单号: str, 金额: float} )订单号这类ID列最好显式指定为字符串不然很长一串数字可能被读成科学计数法后面匹配的时候对不上。而金额列如果本身有缺失或文本dtypefloat可能会报错所以有时候我们宁愿先读进来再用pd.to_numeric(..., errorscoerce)处理。日期解析是另一个重头戏。Excel里最让人头疼的日期形态有三种真正的Excel日期单元格openpyxl读出来是datetime。单元格里是一段标准文本比如2024-05-18 14:30需要用parse_dates处理。单元格里是纯数字比如45064这是Excel日期序列号需要特殊转换。parse_dates可以接一个列名列表把指定列尝试解析成日期df pd.read_excel( data.xlsx, parse_dates[下单时间] )如果日期列同时包含多种格式或者时间戳和日期分开两列你还可以传一个组合列名列表比如parse_dates{下单时间: [下单日期, 下单时刻]}要求Pandas把两列合并解析成一个时间列。这个不是特别常用但遇到时很好使。缺失值的处理也不能靠默认。实际Excel里经常用-、NULL、#N/A表示空值。Pandas有自己的默认缺失值列表但默认不包括-这种。你需要用na_values手动补充df pd.read_excel( data.xlsx, na_values[-, NULL, #N/A] )如果文件里大量使用其他符号表示空值可以在na_values里传一个列表或者字典。经常有业务数据把0也当成空值但直接加na_values[-, 0]会把真正的0也变NaN非常危险。我建议先看数据分布再决定是否把某个数字当成缺失值。3. 写入Excel用熟to_excel和ExcelWriter3.1 一行代码导出但记得关掉Index写入Excel的入门用法是df.to_excel(output.xlsx)这句代码确实能跑但我几乎从来不会这么直接写因为它默认会把DataFrame的索引也写进文件第一列会多出一列无名的索引列。如果你再把这文件读回来就会多出一列“Unnamed: 0”非常烦人。所以导出时第一件事记熟df.to_excel(output.xlsx, indexFalse)indexFalse的意思是“不把索引写入文件”。如果你的索引本身是有业务含义的列比如日期或ID那别急着关掉。你可以先用df.reset_index()把它变成普通列再导出这样列名和内容都清晰。除了index还有一个很实用的参数是float_formatdf.to_excel(output.xlsx, indexFalse, float_format%.2f)这能保证浮点列保留两位小数。很多人说“我明明在DataFrame里是两位小数导出来怎么变成一堆小数位”原因就是浮点数的底层存储问题。float_format是导出时最省事的格式化手段。columns参数可以只导出DataFrame中的部分列df.to_excel(output.xlsx, indexFalse, columns[订单号, 金额, 状态])适合一个DataFrame里有很多中间计算列但最终交付只需要少数几列的场景。3.2 多Sheet写入ExcelWriter才是正解如果只写一个sheetdf.to_excel(output.xlsx)没问题。但如果你想在一个文件里写多个sheet千万别这样写df1.to_excel(output.xlsx, sheet_name汇总) df2.to_excel(output.xlsx, sheet_name明细)这样第二次写入会把整个文件覆盖掉最后只剩下df2的结果。正确做法是使用pd.ExcelWriterwith pd.ExcelWriter(report.xlsx) as writer: df1.to_excel(writer, sheet_name汇总, indexFalse) df2.to_excel(writer, sheet_name明细, indexFalse)ExcelWriter的with写法会自动帮你提交并关闭文件不用手动处理。在同一个writer下多个DataFrame可以写到不同sheet互相不覆盖。还有一种情况你想往一个已经存在的Excel文件里追加一个新sheet。Pandas从版本1.3以后支持modeawith pd.ExcelWriter(report.xlsx, modea, if_sheet_existsreplace) as writer: df3.to_excel(writer, sheet_name新增表, indexFalse)其中if_sheet_exists可以设置如果同名sheet已经存在时的处理策略replace覆盖new新建一个带数字后缀的sheet。注意追加时引擎要和原文件格式匹配一般都要显式指定engineopenpyxl因为追加写入只有openpyxl支持得比较好。3.3 让导出的Excel更美观列宽、冻结窗格、颜色Pandas本身只负责“把数据写进格子”不负责排版。但实际交付报表时如果文件打开列宽都挤在一起观感会差很多。好在ExcelWriter可以拿到底层的openpyxl对象二次加工很灵活。with pd.ExcelWriter(report.xlsx, engineopenpyxl) as writer: df.to_excel(writer, sheet_name汇总, indexFalse) ws writer.sheets[汇总] ws.column_dimensions[A].width 12 ws.column_dimensions[B].width 20 ws.freeze_panes A2freeze_panes是冻结窗格A2表示把第1行冻结滚动时始终能看到表头。如果你同时想出“第一列也冻结”就设置为B2。还可以设置单元格格式比如把表头加粗from openpyxl.styles import Font with pd.ExcelWriter(report.xlsx, engineopenpyxl) as writer: df.to_excel(writer, sheet_name汇总, indexFalse) ws writer.sheets[汇总] ws.column_dimensions[A].width 20 for cell in ws[1]: cell.font Font(boldTrue)这里ws[1]是工作表第一行也就是表头。如果你不想做太复杂的样式用openpyxl动手改比在Pandas层硬凑方便得多。4. 实战一份脏Excel从清洗到报表输出的完整流程4.1 场景是这样的假设你拿到一份“销售明细_2024.xlsx”里面只有一个sheet“原始数据”。数据大概长这样下单时间有的单元格是真正的日期时间有的是文本2024/1/5 9:30。金额有的数值是1234.56有的是带千分位的1,234.56。缺失值手工填了-。订单号是一长串数字必须按字符串处理。目标很明确把这份脏数据清洗成规整的DataFrame然后按月份汇总销售额输出一个包含“月度汇总”和“清洗明细”两个sheet的报表文件。这种场景太常见了常见到几乎每次做项目都会遇到。下面是我习惯的写法。4.2 代码和关键点解析import pandas as pd # 读取原始数据 df pd.read_excel( 销售明细_2024.xlsx, sheet_name原始数据, dtype{订单号: str}, thousands,, na_values[-], parse_dates[下单时间], ) # 如果金额列仍有非数值内容强制转换 df[金额] pd.to_numeric(df[金额], errorscoerce) # 把时间列标准化成字符串月份 df[月份] df[下单时间].dt.strftime(%Y-%m) # 按月汇总销售额 report df.groupby(月份, as_indexFalse)[金额].sum() report report.rename(columns{金额: 销售额}) # 输出报表 with pd.ExcelWriter(销售报表_2024.xlsx) as writer: df.to_excel(writer, sheet_name清洗明细, indexFalse) report.to_excel(writer, sheet_name月度汇总, indexFalse)几个容易忽略的细节thousands,解决的是千分位字符串问题。如果金额列里混了逗号不写这个参数Pandas会把它当成字符串后面求和全乱。dtype{订单号: str}保证订单号读进来是字符串避免长数字被科学计数法。na_values[-]把业务中手工填的短横线转成NaN。parse_dates直接解析下单时间。但这里要注意如果文件里既有真正的日期时间对象又有文本日期parse_dates大多数情况下能处理但偶尔还是会解析失败。稳妥的做法是先检查再决定要不要用errorscoerce。4.3 输出以后一定要读回来看一眼很多人导出完文件就完事了觉得“反正代码没报错”。但Excel类文件最坑的地方就在于运行没报错不代表结果对。我强烈建议养成一个回读校验的习惯# 回读检查 rename_check pd.read_excel(销售报表_2024.xlsx, sheet_name月度汇总) detail_check pd.read_excel( 销售报表_2024.xlsx, sheet_name清洗明细, dtype{订单号: str}, ) print(detail_check.shape) print(detail_check.dtypes) print(report.head())这一步至少能确认三件事行数对不对有没有因为错误的缺失值处理把有效行变成NaN然后被drop掉。列类型对不对订单号有没有变回数字或科学计数法。汇总结果和业务口径是否一致比如月份数量是否符合预期。我见过太多人因为某个列在导出的Excel里被Excel软件自动改成日期格式导致回读后数据完全变样。回读校验不能保证所有细节都对但至少能拦住大部分低级错误。5. 常见报错和排查手册5.1 引擎相关报错装是装了怎么还是报错读Excel报错里最经典的一类就是明明装了库却还是在报引擎错。常见报错长这样ValueError: Excel file format cannot be determined, you must specify an engine manually.这通常不是你代码写错而是文件扩展名和真实格式不匹配。比如一个文件实际内容是.xlsx但后缀被人改成了.xls或者反过来。Pandas拿不到可信的格式信息只能让你手动指定引擎。df pd.read_excel(奇怪的文件.xls, engineopenpyxl)遇到这种文件先别急着写死引擎用Excel打开看看到底是什么格式。如果只是扩展名不对可以重命名如果不确定可以用文件头判断。但最省事的方法是打开文件另存为正确的.xlsx再给Pandas处理。另一个高频报错ImportError: Missing optional dependency openpyxl. Use pip or conda to install openpyxl.这个最简单直接装缺的库。但还有一种隐蔽情况报错里的库你明明装了却仍然找不到大概率是当前Python环境和Pandas不在同一个环境。用pip list确认一下别装错了虚拟环境。5.2 读出来的数据全是NaN或者类型不对“我Excel里明明白白写着123读出来变成NaN”这种事每个做数据处理的人应该都经历过。排查思路一般按以下顺序是不是那一列里混了不可见字符比如空格、换行符。123旁边有个空格会变成字符串再与其他数字混合整列类型变object。是不是某些单元格里其实存的是公式但公式结果为空openpyxl默认读的是公式结果如果缓存里没有结果可能读到None。是不是把自己定义的缺失值符号误加了。比如na_values[-, 0]然后真实的0也被当成缺失值。类型不对的另一个经典场景是日期列。你明明看到2024-05-18读进来却是数字45065这是因为Excel底层存储日期用的是“从1900年1月1日开始的天数”。Pandas在读老格式或某些特殊引擎时可能没有正确识别单元格的日期格式把它当普通数字读了。处理办法df[下单时间] pd.to_datetime(df[下单时间], origin1899-12-30, unitD)这里unitD表示单位是天origin取1899-12-30是为了对齐Excel的序列号规则。具体差多少天和Excel版本有关但上面的组合是Excel日期序列号最常用的转换方式。5.3 Excel文件太大读得很慢怎么办Excel本身不是为大数据设计的。.xlsx里即使只有20万行读起来可能也要十几秒和CSV完全不是一个量级。更大的问题是Pandas的read_excel没有read_csv里的chunksize参数没法直接分块迭代读取。如果只是想要快速看看数据可以用nrows抽样df_sample pd.read_excel(big.xlsx, nrows5000)这样至少能先了解列名和大致内容。如果确实需要全量读取又嫌慢有几个优化思路用usecols只保留分析的列减少数据量。用dtype显式指定数值列避免Pandas做类型推断开销。考虑先把Excel转成CSV再用read_csv的chunksize分块处理。Excel转CSV可以用Excel本身也可以用小工具批量处理。如果是.xlsx也可以用openpyxl的read_onlyTrue模式自己去迭代但这就绕过了Pandas的接口适合特别精通底层的人。我个人的经验是超过一定体量后的Excel文件最好的处理方式不是硬用Pandas读而是找源头导出CSV或者数据库。Excel作为交付格式没问题但作为大数据的“传输格式”真的不合适。5.4 写出的Excel日期、数字看着不对写入时最常见的两个问题一是日期变成了“一串数字”二是浮点数小数位失控。日期变成数字通常是因为DataFrame里的日期类型是datetime64但写出来后openpyxl把它当普通对象处理没设置成Excel日期单元格格式。多数情况下to_excel会自动识别datetime类型但在某些版本或引擎下会失效。解决办法是在写入前先把时间列转为Python原生的datetime.datetimedf[下单时间] df[下单时间].dt.to_pydatetime()浮点数小数位失控前面提到过用float_format解决。如果还是不行检查一下是不是列里有文本型数字混合类型会导致整个列被Pandas判定为objectfloat_format根本不起作用。这时候先清洗列类型再导出。6. 进阶玩法批量合并Excel和模板化输出6.1 批量读取一个文件夹下所有Excel实际工作中经常要处理一堆格式相同的Excel文件比如每个部门交一份表每个月一个文件。手动read_excel再concat也能做但循环写得不好会非常慢。我的标准做法是from pathlib import Path import pandas as pd files list(Path(./data).glob(*.xlsx)) df_list [] for file in files: df pd.read_excel(file, sheet_name数据, dtype{订单号: str}) df[文件名] file.stem # 记录来源方便排查 df_list.append(df) result pd.concat(df_list, ignore_indexTrue)有几个细节值得说glob(*.xlsx)不会匹配.xls如果有两种格式需要分别处理。每个文件读取后要加一列文件名作为来源否则后面发现问题时根本不知道这条数据来自哪个文件。concat时不要用默认索引设置ignore_indexTrue保证最终索引连续。如果文件数量很大建议先读一个文件了解结构保持一致后再批量处理。如果各文件的sheet名或列名不同批量处理会出大问题。6.2 写Excel时可以控制起始单元格to_excel里有两个不算太显眼但很实用的参数startrow和startcol。它们决定从哪个位置开始写入。with pd.ExcelWriter(output.xlsx) as writer: report.to_excel(writer, sheet_name汇总, indexFalse, startrow0, startcol0) df.to_excel(writer, sheet_name汇总, indexFalse, startrowlen(report) 3, startcol0)这种方法可以在同一个sheet里上下堆叠两个表中间留空行。虽然不常见但做那种“一页纸报表”时挺有用比如上面放汇总下面放明细。要注意的是startrow是从0开始计的不是从1。如果你希望Excel的第1行不写数据从第2行开始startrow应该等于1。我在这上面犯过错导出的表头跑到第二行去了。6.3 写入公式而非硬编码数值Pandas本身不鼓励你往DataFrame里塞公式因为公式是Excel层的东西。但有时候报表需要在Excel里保留动态计算比如“合计”这一格。你可以在用to_excel写出后再用openpyxl往特定单元格写公式from openpyxl import load_workbook with pd.ExcelWriter(output.xlsx, engineopenpyxl, modea, if_sheet_existsoverlay) as writer: df.to_excel(writer, sheet_name汇总, indexFalse, startcol0) wb load_workbook(output.xlsx) ws wb[汇总] ws[E10] SUM(E2:E9) wb.save(output.xlsx)用xlsxwriter引擎也可以直接写公式with pd.ExcelWriter(output.xlsx, enginexlsxwriter) as writer: df.to_excel(writer, sheet_name汇总, indexFalse) workbook writer.book worksheet writer.sheets[汇总] worksheet.write_formula(E10, SUM(E2:E9))这里要注意公式在Python读取时不会自动计算除非Excel软件打开并重新计算过。所以如果你用代码回读这个文件那个公式单元格的值大概率是0或者None。验证公式结果的时候别直接用Pandas读要先用Excel打开保存一次再读或者接受这个限制。我个人在实际操作中的体会是Excel的读写从来不是“API背熟”就能做好的事真正的功夫在于对文件本身的理解。拿到一个Excel第一件事不是写代码而是先搞清楚有几个sheet、表头在哪几行、列里混了什么脏值、日期是真是假。把这几个问题确认完代码往往就是几行read_excel和to_excel的事。最后再分享一个小习惯每次导出Excel后我都会立刻用pd.read_excel读回来至少看一眼shape和dtypes。这个习惯帮我在很多项目里提前发现过“索引列混进去了”“日期变成序列号”“金额列多出一堆NaN”等低级问题。你在这一步栽过一次跟头就会明白这个回读动作有多值钱。