1. 项目概述为什么Pandas的read_excel()是数据处理的第一道工序如果你经常和数据打交道尤其是那些躺在Excel表格里的数据那你对pandas.read_excel()这个函数一定不陌生。它几乎是所有Python数据分析、自动化办公脚本的起点就像你要做一顿大餐第一步总是得去菜市场把食材买回来一样。这个函数就是那个帮你把.xlsx或.xls文件里的“数据食材”整整齐齐搬进Python“厨房”的工具。我见过太多新手包括几年前的我在处理Excel数据时第一反应是去网上搜“Python 读取 Excel”然后被各种库的名字搞晕xlrd,openpyxl,xlsxwriter… 该用哪个实际上对于绝大多数“读取数据并进行分析”的场景你只需要记住pandas.read_excel()就够了。Pandas作为一个强大的数据分析库它底层已经帮你整合了这些引擎提供了一个统一、高级的接口。你不需要关心底层是openpyxl还是xlrd在干活你只需要告诉Pandas“帮我把这个文件读进来数据要这样处理”它就能给你一个规整的DataFrame这是Pandas的核心数据结构你可以把它想象成一个功能超级强大的Excel表格接下来所有的筛选、计算、可视化操作都在它上面进行。这个项目标题“python | Pandas库导入Excel数据xlsx格式文件函数read_excel()”看似简单就是一个函数的使用说明。但它的深层价值在于这是打通“静态数据文件”和“动态数据分析”之间壁垒的关键一步。掌握了它你才能把Excel里沉睡的数据唤醒用Python进行更复杂、更自动化的处理无论是批量处理成百上千个报表还是进行机器学习前的数据清洗都从这里开始。接下来我会结合我踩过的无数个坑把这个函数的里里外外、从入门到精通给你讲透。2. 核心需求解析我们到底想从Excel里读出什么在动手写代码之前我们得先想清楚目标。直接调用pd.read_excel(‘file.xlsx’)是最简单的但现实中的数据往往没那么“干净”。理解你的核心需求才能用好这个函数的数十个参数。2.1 读取指定范围的数据而非整个工作表很多时候Excel表格的前几行可能是标题、说明或者空行有效数据可能从第5行才开始。或者你只需要表格中的某一块特定区域的数据而不是整个Sheet。需求场景公司月度销售报表前3行是公司Logo、标题和制表人信息第4行是表头真正的数据从第5行A列开始。核心参数skiprows和usecols。skiprows3告诉Pandas跳过前3行0-indexed即第1,2,3行从第4行开始读取。usecols“A:F”指定只读取A到F列的数据。你也可以传入一个列索引列表如[0, 2, 4]或者列名列表需要先有表头。注意skiprows跳过的行是永久性的这些行不会进入后续的数据处理流程。如果你需要保留这些信息比如报表标题可能需要考虑其他方式比如分两次读取。2.2 处理复杂表头和多重索引有些Excel表格的表头不止一行。比如第一行是大类“华东区”、“华北区”第二行才是具体的指标“销售额”、“利润”。需求场景财务合并报表表头有两行需要将它们组合成多层索引MultiIndex。核心参数header。header[0, 1]这将把第0行和第1行Excel中的第1行和第2行共同作为列索引表头形成一个两层的列索引DataFrame。实操心得读取后你可以通过df.columns查看这个多层索引。对于后续的分析你可能需要用到.xs(),.loc[]等方法来精确选择数据。如果觉得多层索引太复杂也可以在读取后用字符串拼接等方式将其扁平化。2.3 指定数据类型避免自动误判这是最容易踩坑的地方之一。Pandas会尝试自动推断每一列的数据类型但经常“自作聪明”导致问题。需求场景1一列产品编码如“001”、“002”Pandas默认会将其读成整数1,2丢失了前面的零。解决方案使用dtype参数强制指定该列为字符串类型。dtype{‘产品编码’: str}。需求场景2一列本是字符串的数字如身份证号、银行卡号或者一列混合了数字和文本的列被误判为浮点数或对象类型导致后续计算错误或排序混乱。解决方案同样使用dtype参数预先定义。对于身份证号必须用str类型。对于可能包含非数字字符的“数量”列如“10件”、“5套”可以先按str读入再进行文本清洗。需求场景3日期列被识别为字符串无法进行日期运算。解决方案使用parse_dates参数。parse_dates[‘订单日期’]会尝试将该列解析为日期时间类型。对于格式特殊的日期可能需要配合date_parser参数使用自定义解析函数。2.4 处理大型文件与内存优化当Excel文件有几十万行、上百列时直接读取可能会耗尽内存。核心策略分块读取或仅读取需要的部分。参数应用nrows1000仅读取前1000行用于快速查看数据结构。usecols严格限制读取的列这是最有效的优化手段。只读你需要的列。分块读取read_excel本身不支持像read_csv那样的chunksize参数。对于超大Excel文件一个变通方案是使用openpyxl引擎的只读模式进行迭代但代码较复杂。更常见的做法是如果数据量真的巨大应该考虑是否从源头如数据库获取或者将Excel转换为CSV后用read_csv(chunksize50000)处理。3. read_excel()函数参数深度解析与实战配置了解了核心需求我们来深入这个函数的“武器库”。pd.read_excel()的参数多达二十几个但常用的也就十来个。掌握它们你就能应对90%的场景。3.1 基础必会参数这些参数是你每次调用几乎都会考虑或使用的。io: 文件路径或类文件对象。可以是字符串路径‘data.xlsx’也可以是URL以http://开头或者一个已打开的二进制文件对象如open(‘data.xlsx’, ‘rb’)。这是唯一一个必须提供的参数。sheet_name: 指定读取哪个工作表。默认为0读取第一个工作表。可以是字符串‘Sheet1’读取指定名称的工作表。可以是整数列表[0, 2]读取多个工作表返回一个以sheet_name为键的字典{‘Sheet1’: df1, ‘Sheet3’: df2}。可以是None读取所有工作表返回上述字典。header: 指定哪一行作为列名表头。默认为0即第一行。可以是None表示没有表头Pandas会自动生成整数列名0,1,2…。可以是整数列表用于创建多层索引如前文所述。names: 当headerNone或者你想覆盖原有表头时可以传入一个列表来指定列名。例如names[‘姓名’ ‘年龄’ ‘城市’]。index_col: 指定哪一列作为行索引。默认为NonePandas会自动生成一个从0开始的整数索引。可以是整数0将第一列作为索引。可以是列名字符串‘员工ID’。可以是整数/字符串列表创建多层行索引。3.2 数据清洗与预处理相关参数这些参数帮助你在读取阶段就完成初步清洗。skiprows: 跳过文件开头指定的行数整数或一个需要跳过的行号列表从0开始。例如skiprows[0, 2, 3]跳过第1, 3, 4行。skipfooter: 跳过文件末尾指定的行数整数。常用于跳过表格底部的备注、合计行等。usecols: 限制读取的列。这是提升读取性能和聚焦关键数据的利器。可以是字符串‘A:C, E’表示读取A到C列以及E列。可以是整数列表[0, 2]表示读取第1列和第3列。可以是列名列表[‘姓名’ ‘销售额’]需要表头存在。可以是可调用函数如lambda x: x.isalpha()但此用法较少。dtype: 指定列的数据类型。传入一个字典如{‘产品ID’: str, ‘数量’: np.int32}。强烈建议对已知类型的列进行预设避免后续麻烦。converters: 比dtype更灵活允许你为指定列提供一个转换函数。例如有一列“金额”带有人民币符号“¥”你可以写converters{‘金额’: lambda x: float(x.strip(‘¥’))}。true_values/false_values: 指定哪些字符串应该被解析为布尔值True/False。例如你的数据里用“是”、“完成”代表True可以设置true_values[‘是’ ‘完成’]。3.3 日期时间处理参数日期时间数据是分析中的常客也是易错点。parse_dates: 尝试将指定列解析为日期时间。布尔值True尝试解析所有列不推荐效率低且易错。列表[‘生日’ ‘入职日期’]解析指定列。列表的列表[[‘年’ ‘月’ ‘日’]]将多列合并解析为一个日期列。date_parser: 一个用于解析日期的函数通常与parse_dates配合使用处理非标准格式。例如日期格式是“2023年12月01日”你可以from datetime import datetime date_parser lambda x: datetime.strptime(x, “%Y年%m月%d日”) df pd.read_excel(‘file.xlsx’, parse_dates[‘日期’], date_parserdate_parser)keep_date_col: 如果使用列表的列表方式合并解析日期设置keep_date_colTrue可以保留原始的用于解析的列。3.4 引擎与其他高级参数engine: 指定底层读写引擎。通常Pandas会自动选择.xlsx用openpyxl旧.xls用xlrd。但如果你安装了多个引擎可以手动指定例如engine‘openpyxl’。注意新版xlrd2.0只支持.xls不支持.xlsx。处理.xlsx必须确保openpyxl已安装。na_values: 指定哪些字符串应被视为缺失值NaN。例如你的表格里用“N/A”、“-”、“空”表示缺失可以设置na_values[‘N/A’ ‘-’ ‘空’]。thousands: 指定千位分隔符。例如数据为“1,234.5”设置thousands‘,’Pandas会将其正确解析为数字1234.5。decimal: 指定小数点符号。某些地区用逗号,作小数点。设置decimal‘,’即可。4. 完整实操流程从一个混乱的销售报表到整洁的DataFrame光说不练假把式。我们假设有一个名为sales_report_q3.xlsx的季度销售报表它非常“真实”存在各种典型问题。我们一步步把它读进来并处理好。文件sales_report_q3.xlsx结构预览在Excel中看A1单元格“2023年第三季度销售业绩报告未经审计”A2单元格“制表部门销售部”A3单元格空行第4行A4:E4真正的表头 -[“销售员” “地区” “产品” “销售额(万元)” “完成日期”]第5行开始是数据。问题1“销售额(万元)”列数据有千位分隔符如“1,234.5”。问题2“完成日期”列格式是“2023-07-15”。问题3最后两行第104, 105行是“总计”和“备注数据截至9月30日”。问题4我们只需要“销售员”、“地区”和“销售额(万元)”这三列进行分析。我们的目标是跳过无关行和尾注只读取需要的三列正确处理数字和日期格式得到一个干净的DataFrame。4.1 步骤一环境准备与库导入首先确保你的环境里安装了pandas和openpyxl。openpyxl是处理.xlsx文件的引擎。# 在终端或命令提示符中安装 pip install pandas openpyxl在你的Python脚本或Jupyter Notebook中导入Pandas这是标准做法。import pandas as pd # 通常也会导入numpy但本例中非必须 import numpy as np4.2 步骤二制定读取策略并执行根据文件情况我们确定参数skiprows3跳过前3行标题、部门、空行。skipfooter2跳过最后2行总计和备注。usecols[0, 1, 3]根据表头位置我们需要第1列(A/“销售员”)、第2列(B/“地区”)、第4列(D/“销售额(万元)”)。索引从0开始。thousands‘,’处理千位分隔符。parse_dates[‘完成日期’]虽然我们最终不需要这列但这里演示一下如何解析。实际上因为我们用usecols没选这列它不会被读入。如果我们需要可以调整usecols。开始读取file_path ‘sales_report_q3.xlsx’ # 假设文件在当前目录 try: df pd.read_excel( iofile_path, skiprows3, skipfooter2, usecols[0, 1, 3], # 读取A, B, D列 thousands‘,’, # parse_dates[‘完成日期’] # 本次不读取日期列故注释掉 ) print(“数据读取成功”) print(f”数据形状{df.shape}”) # 查看行数和列数 print(df.head()) # 查看前5行数据 except FileNotFoundError: print(f”错误找不到文件 {file_path}请检查路径。”) except Exception as e: print(f”读取文件时发生未知错误{e}”)执行结果预期数据读取成功 数据形状(100, 3) 销售员 地区 销售额(万元) 0 张三 华东 1234.5 1 李四 华北 987.6 2 王五 华南 2456.7 ...可以看到“销售额(万元)”列的数字“1,234.5”已经被正确转换为浮点数1234.5无关行已被剔除我们得到了一个100行、3列的干净DataFrame。4.3 步骤三读取后的数据检查与清洗即使读取时做了处理读取后仍应进行例行检查。# 1. 查看信息概览 print(df.info()) # 检查各列数据类型是否正确。‘销售员’和‘地区’应为object字符串‘销售额(万元)’应为float64。 # 2. 检查缺失值 print(“\n每列缺失值数量”) print(df.isnull().sum()) # 如果‘销售额’有缺失需要决定是删除、填充还是插值。 # 3. 查看基本统计信息针对数值列 print(df[‘销售额(万元)’].describe()) # 查看均值、标准差、最小最大值排查异常值比如负数销售额。 # 4. 查看唯一值针对分类列 print(“\n地区分布”) print(df[‘地区’].value_counts()) print(“\n销售员人数”) print(df[‘销售员’].nunique())4.4 步骤四处理多工作表与合并数据假设我们的报表有多个工作表分别叫“Q1”、“Q2”、“Q3”结构相同。我们需要读取所有季度数据并合并。# 方法1分别读取再合并 df_q1 pd.read_excel(file_path, sheet_name‘Q1’, skiprows3, skipfooter2, usecols[0,1,3], thousands‘,’) df_q2 pd.read_excel(file_path, sheet_name‘Q2’, skiprows3, skipfooter2, usecols[0,1,3], thousands‘,’) df_q3 pd.read_excel(file_path, sheet_name‘Q3’, skiprows3, skipfooter2, usecols[0,1,3], thousands‘,’) # 添加一个‘季度’列以便区分 df_q1[‘季度’] ‘Q1’ df_q2[‘季度’] ‘Q2’ df_q3[‘季度’] ‘Q3’ # 纵向合并沿行方向拼接 df_all pd.concat([df_q1, df_q2, df_q3], ignore_indexTrue) print(df_all.shape) # 方法2一次性读取所有Sheet返回字典 data_dict pd.read_excel(file_path, sheet_nameNone, skiprows3, skipfooter2, usecols[0,1,3], thousands‘,’) # 此时 data_dict 是 {‘Q1’: df1, ‘Q2’: df2, ‘Q3’: df3} # 然后遍历字典为每个df添加季度列再用concat合并5. 高级技巧与性能优化实战当数据量变大或需求变复杂时一些高级技巧能让你事半功倍。5.1 使用Openpyxl引擎进行只读模式与流式处理对于非常大的.xlsx文件read_excel一次性加载可能内存不足。虽然Pandas层面没有完美的分块读取但我们可以利用openpyxl的只读模式手动实现近似效果。from openpyxl import load_workbook import pandas as pd def read_large_excel_in_chunks(filepath, chunk_size1000): “”” 模拟分块读取大型Excel文件。 注意此方法要求工作表结构规整且表头在固定行。 “”” wb load_workbook(filenamefilepath, read_onlyTrue, data_onlyTrue) ws wb.active # 获取第一个工作表也可通过名称获取 data [] headers None start_row 5 # 假设数据从第5行开始表头在第4行 for i, row in enumerate(ws.iter_rows(min_rowstart_row, values_onlyTrue), startstart_row): if i start_row: # 假设表头在 start_row-1 行 headers [cell for cell in ws[start_row-1] if cell.value is not None] # 将行数据转换为字典并过滤掉不需要的列这里简单示例 row_dict {headers[j]: cell for j, cell in enumerate(row) if j len(headers)} data.append(row_dict) if len(data) chunk_size: # 将积累的数据块转换为DataFrame并处理 chunk_df pd.DataFrame(data) process_chunk(chunk_df) # 你的处理函数 data [] # 清空列表准备下一个数据块 # 处理最后不足一个块的数据 if data: chunk_df pd.DataFrame(data) process_chunk(chunk_df) wb.close() def process_chunk(df): # 在这里进行你的数据处理比如筛选、计算、写入数据库等 print(f”处理了 {len(df)} 行数据”) # 例如df.to_sql(‘table_name’, conengine, if_exists‘append’, indexFalse)注意这种方法比直接用read_excel更底层、更复杂且openpyxl的read_only模式对某些单元格格式支持有限。它适用于数据提取和简单转换不适合需要复杂Pandas操作如跨行计算的场景。对于真正的大数据源头优化或转换格式仍是首选。5.2 动态确定数据范围有时你并不知道数据的确切行数或列范围。你可以先快速读取少量行探查或者用openpyxl获取最大行/列。import openpyxl # 快速探查只读前10行不设表头看看数据什么样 df_preview pd.read_excel(‘file.xlsx’, nrows10, headerNone) print(df_preview) # 用openpyxl获取最大行列更准确但慢 wb openpyxl.load_workbook(‘file.xlsx’, read_onlyTrue, data_onlyTrue) ws wb.active max_row ws.max_row max_column ws.max_column print(f”最大行{max_row}, 最大列{max_column}”) wb.close() # 然后你可以用 max_row 来设置 skipfooter或者结合其他逻辑。5.3 处理合并单元格Pandas的read_excel默认会只将合并单元格的值读取到左上角的单元格其他位置为NaN。这通常不是我们想要的。策略读取后使用ffill()向前填充方法填充合并单元格造成的NaN。df pd.read_excel(‘file_with_merged_cells.xlsx’) # 假设‘部门’列存在因合并单元格导致的NaN df[‘部门’] df[‘部门’].ffill() # 沿着列向下填充 # 如果需要横向填充可以用 axis1但较少见。6. 常见问题排查与避坑指南实录这一部分是我多年踩坑经验的结晶很多问题搜索引擎都不一定能给你直接答案。6.1 编码与引擎问题问题ImportError: Missing optional dependency ‘openpyxl‘. Use pip or conda to install openpyxl.原因与解决Pandas默认用openpyxl引擎读.xlsx但你没安装它。pip install openpyxl即可。对于旧版.xls需要xlrd1.2.0注意xlrd2.0不再支持.xlsx。问题读取文件时抛出UnicodeDecodeError或中文字符显示为乱码。原因与解决Excel文件本身编码问题较少见但如果你读取的是CSV另存为的xlsx或者文件路径/工作表名包含特殊字符可能出错。确保文件路径使用英文或正确编码。工作表名乱码可以尝试用sheet_name0索引而非名称访问。6.2 数据类型与数值错误问题长数字如身份证号、信用卡号被读成科学计数法或丢失精度。原因Excel和Pandas默认将长数字识别为浮点数float而浮点数有精度限制。解决必须在读取时就用dtype{‘身份证号’: str}指定为字符串。如果已经读成了科学计数法再转换就晚了原始信息已丢失。问题以0开头的数字如工号“00123”前面的0消失了。解决同上指定该列为str类型。问题含有百分号“%”的列读进来后变成了小数如“95%”变成了0.95但有时我们想要的是字符串“95%”。解决如果希望保留字符串用converters参数converters{‘完成率’: str}。如果希望得到数值0.95用于计算则无需处理Pandas会自动转换。6.3 表头与数据错位问题数据读进来后发现第一行数据变成了表头或者表头变成了第一行数据。原因header参数设置错误。默认header0认为第一行是表头。如果你的数据没有表头第一行就是数据应设置headerNone。排查先用pd.read_excel(‘file.xlsx’, headerNone, nrows5)查看原始数据布局再确定header值。问题使用skiprows后表头也被跳过了。解决skiprows跳过的行是物理行号。如果你的表头在第4行你skiprows3那么表头行第4行就变成了新的第0行。此时要么不设置header默认为0即新的第0行作为表头要么用header0明确指定。如果跳过后想用自定义表头则设置headerNone并用names参数指定。6.4 性能与内存问题问题读取一个50MB的Excel文件非常慢甚至内存溢出。排查与解决检查usecols这是最有效的优化。你真的需要所有列吗只读必要的列。检查dtype指定数据类型可以节省内存并加速。特别是将大文本列明确设为‘category’类型如果分类数远小于行数。考虑文件本身Excel文件可能包含大量格式、图表等非数据内容这些都会增加文件体积和读取开销。尝试将文件另存为“纯数据”的.xlsx或.csv。终极方案对于超过百万行的数据Excel本身就不是合适的存储介质。应推动数据源如数据库直接对接或使用pyarrow、parquet等列式存储格式。6.5 其他疑难杂症问题读取的日期时间列变成了Timestamp对象但只想要日期部分。解决读取后使用.dt.date属性提取日期。df[‘日期列’] df[‘日期列’].dt.date。或者在读取时使用parse_dates配合一个只返回日期的date_parser函数。问题Excel文件中使用了公式read_excel读出来的是公式本身如“A1B1”而不是计算结果。解决确保你保存的Excel文件是“值”的形式或者使用openpyxl引擎时设置data_onlyTrue。但pd.read_excel的engine_kwargs参数可以传递到底层引擎pd.read_excel(‘file.xlsx’, engine‘openpyxl’, engine_kwargs{‘data_only’: True})。问题如何读取受密码保护的Excel文件解决Pandas的read_excel原生不支持。你需要先用其他库如msoffcrypto-tool或openpyxl在内存中解密文件再将文件对象传给Pandas。这是一个相对小众且复杂的场景通常需要联系文件提供者获取密码或未加密版本。最后我的个人体会是pd.read_excel就像一把瑞士军刀功能繁多但你需要清楚知道你手上的这把刀每个工具是干什么用的。最好的学习方式不是记住所有参数而是在遇到实际问题时知道该查哪个参数。建立一个自己的“数据读取配置模板”文档把常用的参数组合比如处理公司标准报表的skiprows,usecols,dtype设置保存下来下次直接复制粘贴再微调效率会高很多。数据处理的第一步走稳了后面的分析之路才会顺畅。