Python批量处理Excel读写与数据清洗完整教程
如果你还在用VBA手工复制粘贴、处理大批量Excel时卡顿，或写一长串宏代码只为合并几个文件，那么Python配合pandas是更工程化的选择。pandas不仅能完成VBA能做的绝大多数操作，还能轻松应对几十万行数据、批量处理上百个文件，代码可读性和维护性也明显更优。一、环境准备
推荐使用Anaconda，自带pandas。需要额外安装Excel读写引擎：
conda install pandas openpyxl xlsxwriter
也可以使用pip：
pip install pandas openpyxl xlsxwriter
三个库的分工：pandas负责数据处理；openpyxl负责读写.xlsx；xlsxwriter负责高性能写出Excel，并支持图表和工作表格式化。
二、pandas读写Excel基础
读取Excel文件，可以通过sheet_name指定工作表，也可以用索引、列范围和跳行参数：
import pandas as pd
df = pd.read_excel("data.xlsx", sheet_name="Sheet1")
# sheet_name=0 表示第一个工作表
# usecols="A:C" 只读取A到C列
# skiprows=2 跳过前两行
print(df.head())
写入Excel时，用to_excel并设置index=False避免写出无意义的索引列：
df.to_excel("output.xlsx", index=False, sheet_name="结果")
三、批量合并一个文件夹下的所有Excel
这是办公自动化里最常用的场景。10行左右代码即可完成VBA几十行才能搞定的功能：
import os
import pandas as pd
folder_path = "./excels/"
all_dfs = []
for file in os.listdir(folder_path):
if file.endswith(".xlsx"):
df = pd.read_excel(os.path.join(folder_path, file))
df["来源文件"] = file# 标记数据来源
all_dfs.append(df)
result = pd.concat(all_dfs, ignore_index=True)
result.to_excel("合并结果.xlsx", index=False)
注意：文件夹下只能存在需要合并的Excel文件，否则需要增加文件名校验逻辑。
四、数据清洗高频操作
1. 删除整行全为空的数据
df.dropna(how="all", inplace=True)
2. 填充缺失值
df["销售额"].fillna(0, inplace=True)
df["城市"].fillna("未知", inplace=True)
3. 按指定列去重，保留第一条记录
df.drop_duplicates(subset="订单号", keep="first", inplace=True)
4. 类型转换：这是最常见的坑。Excel里看着是数字，读进pandas可能变成字符串；日期也可能被读成字符串或序列号。
df["日期"] = pd.to_datetime(df["日期"])
df["金额"] = df["金额"].astype(float)
如果金额列含逗号或货币符号，需要先清洗：
df["金额"] = df["金额"].astype(str).str.replace(",", "").astype(float)
5. 字符串清洗：去空格、去分隔符，以及城市字段模糊筛选。
df["姓名"] = df["姓名"].str.strip()
df["手机号"] = df["手机号"].str.replace("-", "")
df = df.str.contains("北京|上海")]
6. 条件筛选：单条件和多条件组合。
# 单条件
high_sales = df > 10000]
# 多条件：注意括号和位运算符 &
vip_beijing = df[(df["会员"] == "是") & (df["城市"] == "北京")]
五、新增计算字段和分组统计
新增利润列直接用加减法：
df["利润"] = df["销售额"] - df["成本"]
用apply实现类似Excel IF的多层嵌套逻辑，代码更清爽：
df["等级"] = df["销售额"].apply(
lambda x: "高" if x > 10000 else ("中" if x > 5000 else "低")
)
分组统计可以替代Excel透视表，且结果可以直接复用：
summary = df.groupby("城市")["销售额"].agg(
总销售额="sum",
平均销售额="mean",
订单数="count"
).reset_index()
六、Excel美化与高级输出
通过pandas的ExcelWriter可以同时输出多个工作表：
with pd.ExcelWriter("报表.xlsx", engine="xlsxwriter") as writer:
df.to_excel(writer, sheet_name="原始数据", index=False)
summary.to_excel(writer, sheet_name="汇总", index=False)
在xlsxwriter引擎下，还能自动调整列宽并冻结首行：
workbook = writer.book
worksheet = writer.sheets["汇总"]
worksheet.freeze_panes(1, 0)# 冻结首行
for i, col in enumerate(summary.columns):
width = max(summary.astype(str).map(len).max(), len(col)) + 2
worksheet.set_column(i, i, width)
添加图表也是xlsxwriter的强项，比VBA简单得多：
chart = workbook.add_chart({"type": "column"})
chart.add_series({
"categories": "=汇总!$A$2:$A$10",
"values": "=汇总!$B$2:$B$10",
"name": "销售额"
})
worksheet.insert_chart("E2", chart)
七、定时自动运行
完成脚本后，可以配合Windows任务计划程序或Linux crontab定时执行：
python excel_auto.py
这样就可以实现每天自动拉取数据库、清洗数据、生成报表并发送邮件，财务日报、运营周报全自动产出，无需人工干预。
八、常见错误避坑指南
实际使用中经常遇到以下问题，对应处理方式如下：
- Excel文件被占用导致无法保存：关闭Excel程序后重新运行。
- 日期列读出来是数字：用pd.to_datetime()转换。
- 数字被显示为科学计数法：在写入Excel时设置单元格数字格式，或先将列转为字符串。
- 中文乱码：确认文件编码，读取时指定encoding，如encoding="utf-8"。
- 数据量过大导致内存不足：使用chunksize参数分批读取，或先用usecols只读需要的列。
九、总结
VBA是“办公技巧”，Python是“生产力工具”。批量处理不再靠复制粘贴，数据清洗逻辑清晰可复用，报表自动生成零人工干预。当你第一次用Python把100个Excel合并成一张表，就会明白：不是Excel不行，而是该换工具了。掌握pandas基础读写、筛选和数据清洗，再学会批量文件处理和定时任务，就能覆盖大多数日常Excel自动化需求。后续还可以继续深入学习SQL、Python和BI工具的组合，进一步扩大自动化能力边界。
Re: Python批量处理Excel读写与数据清洗完整教程
楼主的教程写得很实在，正好解决了最近我被Excel折磨的痛点。尤其是批量合并文件和数据类型转换那块，以前用VBA写半天还容易出错，pandas几行就搞定了。我补充一个小经验：如果遇到老版本的.xls文件，记得装一下xlrd库，不然read_excel会报错；另外合并大量文件时，建议用glob配合正则筛选，能避免读入无关文件。希望对大家有用，再次感谢楼主！Re: Python批量处理Excel读写与数据清洗完整教程
感谢楼主整理，内容很实用，特别是批量合并和数据清洗那几段，直接拿来改了就可以用。 补充一个小经验：如果文件很多，用 `glob` 匹配路径比 `os.listdir` 加后缀判断更灵活；读大文件时先 `nrows` 预览几行，再用 `dtype` 指定列类型，能省不少内存和踩坑时间。 再次感谢分享，期待更多实战案例。Re: Python批量处理Excel读写与数据清洗完整教程
这篇教程写得很实用，正好解决了最近批量合并Excel的痛点。之前用VBA写循环又慢又容易崩，换成pandas之后几十个文件几秒就搞定了。特别是类型转换和字符串清洗那块，真的都是实战里经常踩的坑。感谢整理，已收藏！
页:
[1]