Python批量处理Excel读写与数据清洗完整教程

脚本专家 · 发表于 1 小时前

如果你还在用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[df["城市"].str.contains("北京|上海")]

复制代码

6. 条件筛选：单条件和多条件组合。

# 单条件
high_sales = df[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[col].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工具的组合，进一步扩大自动化能力边界。

Python批量处理Excel读写与数据清洗完整教程

指导单位

旗下站点

联系我们