1. 项目概述为什么要在Python里折腾Excel如果你经常和数据打交道尤其是那些躺在Excel表格里的数据那你肯定有过这样的经历手动复制粘贴到手抽筋用Excel公式处理复杂逻辑时卡到怀疑人生或者需要把几十个报表的数据合并分析时感觉自己在进行一场毫无胜算的体力劳动。我最初接触Python来处理Excel就是因为被这些重复、繁琐且容易出错的手工操作折磨得够呛。当时我想如果能用几行代码自动完成这些工作那该多好。Python处理Excel核心就是自动化和批量化。它能把我们从重复的“表哥表姐”工作中解放出来去处理更有价值的分析、建模和决策问题。无论是市场部门的销售日报汇总财务部门的凭证数据清洗还是技术部门的日志统计分析只要原始数据是Excel格式Python都能大显身手。这个项目标题“【Python处理EXCEL】基础操作篇在Python中导入EXCEL数据”看似简单却是整个数据工作流的基石。数据都导不进来后续的分析、可视化、建模全都是空谈。对于初学者而言可能会觉得“导入数据”不就是一行pd.read_excel()吗确实基础调用很简单但实际工作中遇到的Excel文件千奇百怪有带合并单元格的报表有分多个Sheet的工作簿有文件编码问题导致的中文乱码还有动辄几十上百兆、一打开就卡死的大型文件。如何高效、准确、稳健地把这些形态各异的数据“搬进”Python的内存中进行计算里面门道不少。这篇文章我就以一个过来人的身份带你从零开始深入浅出地搞定Python导入Excel数据的方方面面避开我当年踩过的那些坑。2. 环境准备与核心库选型工欲善其事必先利其器。在写第一行导入代码之前我们需要先把“厨房”收拾好。2.1 Python环境搭建Anaconda还是纯Python对于数据分析新手我强烈推荐直接安装Anaconda。它是一个集成了Python、众多科学计算库包括我们马上要用到的pandas、numpy以及包管理工具conda的发行版。它的优势在于开箱即用避免了令人头疼的库依赖和版本冲突问题。你可以去Anaconda官网下载对应操作系统的安装包一路下一步即可。如果你已经是资深开发者习惯使用纯Python和pip那也完全没问题。确保你的Python版本在3.6以上推荐3.8然后用pip安装必要的库即可。注意无论用哪种方式都建议创建一个独立的虚拟环境conda create或python -m venv来管理这个项目的依赖避免污染系统环境。2.2 核心库介绍pandas是绝对主力处理Excelpandas库是当之无愧的王者。它提供了DataFrame这种强大的二维表格数据结构以及read_excel这个万能的数据读取函数。pandas并非单独作战它依赖于两个底层引擎来处理不同格式的Excel文件.xlsx文件默认使用openpyxl库。这是处理现代Excel文件2007版及以后的主流选择功能全面。.xls文件默认使用xlrd库版本需1.2.0新版xlrd已不再支持xls格式的读取。对于老旧的.xls格式文件我们需要它。所以完整的安装命令如下在终端或Anaconda Prompt中执行# 如果你使用pip pip install pandas openpyxl xlrd # 如果你使用conda conda install pandas openpyxl xlrd安装完成后可以在Python中导入pandas并查看版本通常我们还会给它起一个别名pd这是业内的通用约定。import pandas as pd print(pd.__version__)2.3 编辑器选择Jupyter还是IDEJupyter Notebook / Jupyter Lab非常适合数据探索和交互式分析。你可以一段段地执行代码即时看到数据和图表结果是学习和演示的神器。Anaconda自带Jupyter。VSCode / PyCharm适合开发完整的脚本或项目。VSCode轻量且插件丰富如Python、Pylance、Jupyter插件PyCharm是专业的Python IDE功能更强大。它们对代码调试、版本管理支持更好。对于本篇“导入数据”的学习我建议从Jupyter开始直观感受每一步操作的结果。3. 单文件基础导入pd.read_excel()详解现在假设我们有一个名为sales_data.xlsx的销售数据文件放在和你的Python脚本同一目录下。最基础的导入代码如下df pd.read_excel(sales_data.xlsx) print(df.head()) # 查看前5行数据 print(df.shape) # 查看数据形状行数列数这一行代码背后pandas帮你完成了打开文件、解析工作表、识别表头、转换数据类型等一系列复杂操作。但实际文件往往没那么“标准”我们需要掌握read_excel的核心参数来应对各种情况。3.1 关键参数解析让你的导入更精准sheet_name指定读取哪个工作表默认是0即第一个Sheet。可以传工作表名称的字符串如sheet_nameSheet1。可以传工作表索引从0开始如sheet_name1表示第二个Sheet。如果想读取所有Sheet到一个字典里可以设sheet_nameNone。返回的字典以Sheet名为键对应的DataFrame为值。# 读取第二个工作表 df_sheet2 pd.read_excel(sales_data.xlsx, sheet_name1) # 读取所有工作表 all_sheets_dict pd.read_excel(sales_data.xlsx, sheet_nameNone)header指定哪一行作为列名表头默认是0即用第一行作为列名。如果文件没有表头需要设置headerNone此时pandas会用0, 1, 2...作为默认列名。如果表头在第3行前两行是标题或空行则设置header2。# 文件无表头 df_no_header pd.read_excel(data.xlsx, headerNone) # 表头在第3行 df_header_row2 pd.read_excel(report.xlsx, header2)usecols仅读取指定的列这是提升读取性能和聚焦目标数据的关键参数尤其对于列数很多的大文件。可以传入一个列字母的字符串如A:C, E表示A、B、C和E列或列索引的列表如[0, 2, 4]或一个可调用函数。# 只读取A列到C列以及E列 df_partial pd.read_excel(large_file.xlsx, usecolsA:C, E) # 只读取第1、3、5列索引从0开始 df_partial_idx pd.read_excel(large_file.xlsx, usecols[0, 2, 4])nrows和skiprows控制读取的行nrows仅读取文件开头的指定行数常用于快速查看大数据文件的结构。skiprows跳过文件开头的指定行数。可以是一个整数也可以是一个列表指定跳过多行。# 只读取前100行 df_sample pd.read_excel(huge_file.xlsx, nrows100) # 跳过前3行可能是文件说明或空行 df_skip3 pd.read_excel(file.xlsx, skiprows3) # 跳过第1行和第3行索引从0开始 df_skip_list pd.read_excel(file.xlsx, skiprows[0, 2])dtype和converters指定列的数据类型默认情况下pandas会推断每列的数据类型但有时会出错比如把以0开头的工号“001”推断为数字1。dtype参数可以指定某列为字符串类型。converters更强大可以为指定列提供一个转换函数。# 将‘员工ID’和‘电话’列强制读取为字符串 df pd.read_excel(data.xlsx, dtype{员工ID: str, 电话: str}) # 使用转换函数例如将某列金额字符串“1,000”转换为数字1000 def remove_comma(x): return float(str(x).replace(,, )) if pd.notna(x) else x df pd.read_excel(data.xlsx, converters{金额: remove_comma})3.2 实操心得性能与内存的权衡有同学在搜索热词里提到“python读取excel数据全部读取耗时5分钟仅读几列也是5分钟怎么回事”这很可能触及了pandas读取Excel的一个特点read_excel默认会先将整个Excel文件加载到内存中进行解析然后再根据usecols等参数进行筛选。所以如果文件本身非常大比如超过50MB即使你只读几列前面的完整加载过程依然耗时。解决方案对于.xlsx文件可以尝试使用openpyxl的只读模式通过read_onlyTrue参数进行流式读取。但这需要更底层的操作pandas的read_excel对此支持有限。终极方案如果Excel文件巨大且操作频繁考虑将其转换为更高效的格式如CSV或Parquet再用pandas读取速度会有数量级的提升。或者直接使用数据库来存储和管理数据。折中方案利用skiprows和nrows分块读取处理完一块再读下一块。4. 处理复杂结构与数据清洗现实中的Excel往往不是一张干净的表格。你可能遇到合并单元格、多级表头、空白行等“脏数据”。4.1 处理合并单元格与多级表头合并单元格被读取后通常只有第一个单元格有值后续单元格为NaN空值。我们需要进行向前填充ffill。df_filled df.ffill() # 沿着列方向用上一个非空值填充下面的空值对于多级表头跨行合并的表头在read_excel时可以通过header参数指定一个列表。例如如果表头占据了第2行和第3行可以设置header[1,2]这样会创建一个多级索引MultiIndex的列名。处理起来稍复杂通常需要df.columns来查看和调整。4.2 处理空白行与非法值导入后经常需要清洗数据# 1. 删除所有值都为NaN的行 df_cleaned df.dropna(howall) # 2. 删除指定列如‘备注’为NaN的行 df_cleaned df.dropna(subset[备注]) # 3. 将特定的占位符如‘-’ ‘N/A’替换为NaN df_replace df.replace([-, N/A, ], pd.NA) # 4. 填充NaN例如用该列的平均值填充 df_filled df.fillna(df.mean())4.3 设置正确的索引默认的索引是0开始的整数。我们可以将数据中的唯一标识列如ID、日期设为索引方便后续查询。df_indexed df.set_index(员工ID)5. 批量导入与自动化实战单个文件处理只是开始真正的威力在于批量处理。5.1 批量读取同一目录下的所有Excel文件假设某个文件夹./monthly_reports/下存放着2024年每个月的销售报告sales_202401.xlsx,sales_202402.xlsx...import os import pandas as pd folder_path ./monthly_reports/ all_files [f for f in os.listdir(folder_path) if f.endswith(.xlsx)] df_list [] for file in all_files: file_path os.path.join(folder_path, file) # 可以在读取时添加一列记录来源文件名 temp_df pd.read_excel(file_path) temp_df[source_file] file df_list.append(temp_df) # 将所有DataFrame合并成一个 combined_df pd.concat(df_list, ignore_indexTrue) print(f合并后的总数据量{combined_df.shape})5.2 读取多个指定工作表并合并有时一个工作簿的多个Sheet结构相同存放着不同类别的数据如不同产品线。excel_path product_lines.xlsx # 先获取所有工作表名 xls pd.ExcelFile(excel_path) # 这种方式比多次read_excel效率稍高 sheet_names xls.sheet_names combined_by_sheet pd.DataFrame() for sheet in sheet_names: temp_df pd.read_excel(xls, sheet_namesheet) temp_df[product_line] sheet # 添加一列标识产品线 combined_by_sheet pd.concat([combined_by_sheet, temp_df], ignore_indexTrue)5.3 动态构建文件路径与参数化为了使脚本更通用我们可以使用input函数或命令行参数argparse库来让用户动态指定文件路径、工作表名等。# 简单示例用户输入文件名 file_name input(请输入Excel文件名包含.xlsx后缀: ) try: df pd.read_excel(file_name) print(文件读取成功) except FileNotFoundError: print(f错误找不到文件 {file_name}) except Exception as e: print(f读取文件时发生错误{e})6. 常见问题排查与性能优化技巧这里汇总了我在实际工作中遇到的一些典型问题及其解决方法。6.1 编码问题与中文乱码这个问题在读取包含中文的.xls文件或者由某些旧版系统生成的.csv有时被误存为.xlsx时可能出现。虽然read_excel本身没有encoding参数因为Excel文件是二进制格式编码已内定但乱码可能源于文件本身损坏或底层引擎问题。尝试更换引擎对于.xls指定enginexlrd对于.xlsx指定engineopenpyxl。检查文件完整性用Excel软件打开文件另存为一个新文件有时能修复潜在问题。终极方法如果文件能打开但pandas读不了可以尝试用Excel将其另存为CSV格式再用pd.read_csv(encodinggbk或utf-8)读取。6.2 依赖库缺失或版本冲突报错ModuleNotFoundError: No module named openpyxl或ImportError: Missing optional dependency xlrd。解决使用pip install openpyxl xlrd安装缺失的库。注意xlrd版本新版本xlrd2.0只支持.xls文件的读取且需要指定enginexlrd。如果遇到.xls文件读取问题可以尝试降级到经典版本pip install xlrd1.2.0。6.3 数据类型推断错误最常见的是将数字字符串如身份证号、电话号码读成了浮点数或整数导致前面的0丢失。预防在读取时使用dtype参数直接指定列为str类型。补救读取后使用df[列名] df[列名].astype(str)进行转换但可能无法恢复丢失的0最好在读取时就指定。6.4 读取大型文件内存不足这是性能问题的核心。除了前面提到的转换格式、分块读取还有以下技巧指定dtype明确指定每列的数据类型尤其是将可能被误判为object字符串的列指定为更节省空间的类型如category分类数据、int32/float32。使用low_memory参数pd.read_csv有这个参数但pd.read_excel没有。这再次说明对于超大文件转成CSV是更优选择。使用chunksize仅CSVpd.read_csv可以分块读取但read_excel不行。这是考虑更换数据格式的强有力理由。6.5 日期时间解析问题Excel中的日期可能被读成整数Excel的序列日期值或字符串。使用parse_dates参数在读取时指定需要解析为日期的列。df pd.read_excel(data.xlsx, parse_dates[订单日期, 发货日期])手动转换如果读取后日期列是数字可以使用pd.to_datetime配合unitd和origin1899-12-30Windows Excel的默认起始日期进行转换。df[日期列] pd.to_datetime(df[日期列], unitd, origin1899-12-30)7. 从导入到入库数据管道初探将Excel数据导入Python的DataFrame往往只是第一步。更常见的场景是我们需要把这些清洗好的数据存入数据库供后续应用或BI工具使用。7.1 连接数据库这里以SQLite轻量级单文件数据库和MySQL为例。# 连接SQLite数据库 import sqlite3 conn_sqlite sqlite3.connect(my_database.db) # 连接MySQL数据库需要安装pymysql或mysql-connector-python # pip install pymysql import pymysql conn_mysql pymysql.connect( hostlocalhost, useryour_username, passwordyour_password, databaseyour_database, charsetutf8mb4 )7.2 将DataFrame写入数据库表pandas提供了非常方便的to_sql方法。# 假设df是我们已经清洗好的DataFrame table_name sales_records # 写入SQLite df.to_sql(nametable_name, conconn_sqlite, if_existsreplace, indexFalse) # if_exists: fail(如果表存在则报错), replace(替换), append(追加) # index: 是否将DataFrame的索引作为一列写入 # 写入MySQL df.to_sql(nametable_name, conconn_mysql, if_existsappend, indexFalse) # 操作完毕后记得关闭连接 conn_sqlite.close() conn_mysql.close()7.3 构建一个简单的自动化导入管道我们可以将上述步骤组合成一个脚本实现“监测文件夹 - 读取新Excel - 清洗 - 入库”的自动化流程。这里给出一个简化版的框架import pandas as pd import os import sqlite3 from datetime import datetime def process_excel_to_db(excel_file_path, db_connection): 处理单个Excel文件并入库 try: # 1. 读取 df pd.read_excel(excel_file_path, dtype{员工ID: str}) # 2. 简单清洗示例 df.dropna(subset[订单号], inplaceTrue) # 删除订单号为空的记录 df[导入时间] datetime.now() # 添加时间戳 # 3. 入库 df.to_sql(raw_sales_data, condb_connection, if_existsappend, indexFalse) print(f成功处理文件{excel_file_path}) # 4. (可选) 将处理完的文件移动到“已处理”文件夹 # os.rename(...) return True except Exception as e: print(f处理文件 {excel_file_path} 时出错{e}) return False # 主程序 if __name__ __main__: watch_folder ./incoming_data/ db_conn sqlite3.connect(./data_warehouse.db) for file in os.listdir(watch_folder): if file.endswith((.xlsx, .xls)): file_path os.path.join(watch_folder, file) process_excel_to_db(file_path, db_conn) db_conn.close()这个框架可以进一步扩展加入日志记录、错误重试、邮件通知等功能就构成了一个可靠的生产级数据摄入微服务。
