前言
工作中有没有遇到这种情况：
老板发来一个文件夹，里面100个Excel，让你汇总成一张表。你打开第一个文件，复制，粘贴，再打开第二个……
3个小时后，你把第27个文件的数据粘贴错位了，全部推倒重来。
这是典型的"体力活"。Python可以在2分钟内替你做完，而且不会出错。
本文从零开始，手把手教你用Python批量处理Excel文件。代码每一行都有注释，照着写就能跑。
一、环境准备
1.1 安装依赖库
本文用到两个核心库：
✅ openpyxl：读写.xlsx格式（推荐，功能最全）
✅ pandas：数据处理（必装，数据分析标配）
# 打开终端，执行以下命令
pip install openpyxl pandas xlrd xlwt
验证安装是否成功：
import pandas as pd
import openpyxl
print(pd.__version__) # 正常输出版本号则安装成功
print(openpyxl.__version__)
1.2 准备测试文件
本文所有示例基于以下目录结构：
excel_demo/
├── raw/ # 原始数据文件夹
│ ├── 2024_01_sales.xlsx # 各月销售数据
│ ├── 2024_02_sales.xlsx
│ └── ...
├── output/ # 处理结果输出
└── process.py # 处理脚本
二、场景一：批量读取多个Excel并合并汇总
这是最高频的需求：把一个文件夹里所有Excel合并成一张表。
2.1 核心代码
import pandas as pd
import os
import glob
# ============================================================
# 批量读取合并 - 核心函数
# 公众号：船长Talk（更多数据处理技巧，关注公众号）
# ============================================================
def merge_excel_files(input_dir, output_file, sheet_name=0):
"""
批量合并文件夹内所有Excel文件
参数说明：
- input_dir: 输入文件夹路径（字符串）
- output_file: 输出文件路径（字符串）
- sheet_name: 要读取的Sheet名或索引，默认读第一个Sheet
返回值：合并后的DataFrame
"""
# 第一步：获取文件夹内所有.xlsx文件路径
# glob.glob支持通配符，** 匹配任意字符
xlsx_files = glob.glob(os.path.join(input_dir, "*.xlsx"))
# 如果还有旧版.xls格式，也一起处理
xls_files = glob.glob(os.path.join(input_dir, "*.xls"))
all_files = xlsx_files + xls_files
# 如果没有找到文件，提前返回
if not all_files:
print(f"❌ 未在 {input_dir} 找到Excel文件")
return None
print(f"✅ 找到 {len(all_files)} 个Excel文件")
# 第二步：逐个读取，存入列表
df_list = []
for file_path in all_files:
try:
# 读取Excel文件
df = pd.read_excel(file_path, sheet_name=sheet_name)
# 重要：添加来源文件名列，方便溯源
df['来源文件'] = os.path.basename(file_path)
df_list.append(df)
print(f" ✅ 已读取: {os.path.basename(file_path)} ({len(df)}行)")
except Exception as e:
# 读取失败不中断，记录错误继续处理
print(f" ❌ 读取失败: {os.path.basename(file_path)} - 错误: {e}")
# 第三步：合并所有DataFrame
if not df_list:
print("❌ 没有成功读取任何文件")
return None
# pd.concat：纵向拼接多个DataFrame
# ignore_index=True：重新生成从0开始的索引
merged_df = pd.concat(df_list, ignore_index=True)
print(f"\n📊 合并完成：总计 {len(merged_df)} 行数据")
# 第四步：保存合并结果
merged_df.to_excel(output_file, index=False, engine='openpyxl')
print(f"✅ 结果已保存至: {output_file}")
return merged_df
# 使用示例
if __name__ == "__main__":
result = merge_excel_files(
input_dir="excel_demo/raw",
output_file="excel_demo/output/merged_result.xlsx"
)
运行效果：
✅ 找到 12 个Excel文件
✅ 已读取: 2024_01_sales.xlsx (1240行)
✅ 已读取: 2024_02_sales.xlsx (1089行)
...
✅ 已读取: 2024_12_sales.xlsx (1456行)
📊 合并完成：总计 14328 行数据
✅ 结果已保存至: excel_demo/output/merged_result.xlsx
三、场景二：批量格式清洗（日期/金额格式统一）
合并后的数据，最常见的问题是格式不统一。比如日期有的是"2024-01-05"，有的是"20240105"，有的是"2024/1/5"。
3.1 日期格式统一
import pandas as pd
# ============================================================
# 日期格式清洗 - 处理多种格式混合的情况
# 公众号：船长Talk（数据清洗实战，关注公众号）
# ============================================================
def clean_date_column(df, col_name, output_format='%Y-%m-%d'):
"""
统一日期列格式
参数：
- df: DataFrame
- col_name: 日期列名称
- output_format: 输出格式，默认'%Y-%m-%d'
"""
# pd.to_datetime的errors='coerce'参数：
# - 无法解析的日期会变成NaT（Not a Time），不会报错
# - infer_datetime_format=True：自动推断格式，速度更快
df[col_name] = pd.to_datetime(
df[col_name],
errors='coerce',
infer_datetime_format=True
)
# 检查有多少日期解析失败
null_count = df[col_name].isnull().sum()
if null_count > 0:
print(f"⚠️ {col_name}列有 {null_count} 个日期无法解析，已设为NaT")
# 转换为统一字符串格式（可选，看需求）
# df[col_name] = df[col_name].dt.strftime(output_format)
return df
# 使用示例
df = pd.read_excel("data.xlsx")
df = clean_date_column(df, col_name='订单日期')
print(df['订单日期'].dtype) # 输出: datetime64[ns]
3.2 金额格式清洗（去除逗号、货币符号）
def clean_amount_column(df, col_name):
"""
清洗金额列：去除¥、$符号和千位分隔符逗号
'¥1,234.56' → 1234.56
'$1,234.56' → 1234.56
"""
# 先转成字符串，防止混合类型出错
df[col_name] = df[col_name].astype(str)
# str.replace支持正则表达式
# [¥$,\s] 匹配人民币符号、美元符号、逗号、空白字符
df[col_name] = df[col_name].str.replace(r'[¥$,\s]', '', regex=True)
# 转换为float
df[col_name] = pd.to_numeric(df[col_name], errors='coerce')
return df
四、场景三：批量生成带格式的Excel报表
用openpyxl可以生成带样式的Excel——颜色、字体、边框、条件格式全部支持。
4.1 生成带颜色标题行的报表
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
import pandas as pd
# ============================================================
# 生成格式化Excel报表 - 带颜色和样式
# 公众号：船长Talk（自动生成美观报表，关注公众号）
# ============================================================
def create_formatted_report(df, output_path, title="数据报表"):
"""
生成带格式的Excel报表
参数：
- df: 要写入的DataFrame
- output_path: 输出文件路径
- title: 报表标题
"""
# 创建新工作簿
wb = Workbook()
ws = wb.active
ws.title = "数据汇总"
# ---- 第一行：报表标题 ----
ws.merge_cells(f'A1:{get_column_letter(len(df.columns))}1')
title_cell = ws['A1']
title_cell.value = title
title_cell.font = Font(name='微软雅黑', size=14, bold=True, color='FFFFFF')
title_cell.fill = PatternFill(fill_type='solid', fgColor='1565C0') # 深蓝色背景
title_cell.alignment = Alignment(horizontal='center', vertical='center')
ws.row_dimensions[1].height = 35
# ---- 第二行：列标题 ----
header_fill = PatternFill(fill_type='solid', fgColor='4776E6') # 蓝色背景
header_font = Font(name='微软雅黑', bold=True, color='FFFFFF')
for col_idx, col_name in enumerate(df.columns, start=1):
cell = ws.cell(row=2, column=col_idx, value=col_name)
cell.font = header_font
cell.fill = header_fill
cell.alignment = Alignment(horizontal='center', vertical='center')
ws.row_dimensions[2].height = 25
# ---- 数据行：写入数据 + 斑马纹 ----
fill_light = PatternFill(fill_type='solid', fgColor='EEF2FF') # 浅蓝色
fill_white = PatternFill(fill_type='solid', fgColor='FFFFFF') # 白色
# 定义边框样式
thin_border = Border(
left=Side(style='thin', color='CCCCCC'),
right=Side(style='thin', color='CCCCCC'),
top=Side(style='thin', color='CCCCCC'),
bottom=Side(style='thin', color='CCCCCC')
)
for row_idx, row_data in enumerate(df.itertuples(index=False), start=3):
# 斑马纹：奇数行浅色，偶数行白色
row_fill = fill_light if row_idx % 2 == 1 else fill_white
for col_idx, value in enumerate(row_data, start=1):
cell = ws.cell(row=row_idx, column=col_idx, value=value)
cell.fill = row_fill
cell.border = thin_border
cell.alignment = Alignment(horizontal='left', vertical='center')
# ---- 自动调整列宽 ----
for col_idx in range(1, len(df.columns) + 1):
col_letter = get_column_letter(col_idx)
max_length = 0
for cell in ws[col_letter]:
try:
cell_len = len(str(cell.value)) if cell.value else 0
# 中文字符占2个字符宽度
max_length = max(max_length, cell_len)
except:
pass
# 设置列宽（最小10，最大50）
adjusted_width = max(10, min(50, max_length * 1.5))
ws.column_dimensions[col_letter].width = adjusted_width
# 保存文件
wb.save(output_path)
print(f"✅ 报表已生成: {output_path}")
return output_path
# 使用示例
data = {
'姓名': ['张三', '李四', '王五'],
'销售额': [128000, 95000, 234000],
'完成率': ['85%', '63%', '156%'],
'所属区域': ['华东', '华南', '华北']
}
df = pd.DataFrame(data)
create_formatted_report(df, "output/sales_report.xlsx", title="2024年销售汇总报表")
五、场景四：条件格式高亮异常数据
数据分析最常用的操作之一：把超标的数据标红。
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font
# ============================================================
# 条件格式高亮 - 自动标红异常值
# 公众号：船长Talk（数据质量监控实战，关注公众号）
# ============================================================
def highlight_anomalies(file_path, sheet_name, col_name, threshold, color='FF0000'):
"""
高亮显示超过阈值的异常数据
参数：
- file_path: Excel文件路径
- sheet_name: Sheet名称
- col_name: 要检查的列名
- threshold: 阈值，超过此值则标红
- color: 高亮颜色（16进制，默认红色）
"""
# 加载已有Excel文件（保留原有样式）
wb = load_workbook(file_path)
ws = wb[sheet_name]
# 找到目标列的索引
header_row = [cell.value for cell in ws[1]] # 第一行为表头
if col_name not in header_row:
print(f"❌ 列 '{col_name}' 不存在")
return
col_idx = header_row.index(col_name) + 1 # openpyxl列索引从1开始
# 定义高亮样式
red_fill = PatternFill(fill_type='solid', fgColor=color)
red_font = Font(color='FFFFFF', bold=True) # 白色粗体字
highlight_count = 0
# 从第二行开始（第一行是表头）
for row in ws.iter_rows(min_row=2, min_col=col_idx, max_col=col_idx):
for cell in row:
# 只处理数字类型的值
if isinstance(cell.value, (int, float)) and cell.value > threshold:
cell.fill = red_fill
cell.font = red_font
highlight_count += 1
wb.save(file_path)
print(f"✅ 高亮完成：共 {highlight_count} 个超阈值数据已标红")
# 使用示例：高亮销售额超过200000的数据
highlight_anomalies(
file_path="output/sales_report.xlsx",
sheet_name="数据汇总",
col_name="销售额",
threshold=200000,
color="FF4444" # 浅红色
)
六、实战：把这些功能串起来
一个完整的月报自动化流程：
# ============================================================
# 完整月报自动化流程
# 公众号：船长Talk（全套自动化方案，关注公众号）
# ============================================================
import pandas as pd
import glob
import os
def monthly_report_pipeline(raw_dir, output_dir):
"""
月报自动化完整流程：
1. 批量读取原始数据
2. 格式清洗（日期/金额）
3. 数据汇总（按区域/月份）
4. 生成格式化报表
"""
print("=" * 50)
print("📊 月报自动化开始...")
print("=" * 50)
# Step 1: 批量合并
print("\n【Step 1】读取原始数据...")
merged = merge_excel_files(raw_dir, f"{output_dir}/temp_merged.xlsx")
if merged is None:
return
# Step 2: 格式清洗
print("\n【Step 2】数据清洗...")
merged = clean_date_column(merged, '订单日期')
merged = clean_amount_column(merged, '销售金额')
# Step 3: 分析汇总
print("\n【Step 3】生成汇总统计...")
summary = merged.groupby(['区域', merged['订单日期'].dt.month]).agg(
总销售额=('销售金额', 'sum'),
订单数量=('订单号', 'count'),
平均客单价=('销售金额', 'mean')
).round(2).reset_index()
# Step 4: 生成报表
print("\n【Step 4】生成格式化报表...")
create_formatted_report(
summary,
f"{output_dir}/月报汇总_{pd.Timestamp.now().strftime('%Y%m')}.xlsx",
title=f"{pd.Timestamp.now().strftime('%Y年%m月')} 销售汇总报表"
)
print("\n✅ 月报自动化完成！")
print(f"📁 输出目录: {output_dir}")
# 执行
monthly_report_pipeline("data/raw", "data/output")
七、常见坑和解决方案
✅ 坑1：中文乱码
原因：文件编码不一致。解法：pd.read_excel(file, encoding='utf-8') 或 engine='openpyxl'
✅ 坑2：日期变成数字
原因：Excel内部存储日期是数字（天数）。解法：pd.to_datetime(df['日期'], unit='D', origin='1899-12-30')
✅ 坑3：内存溢出（大文件）
原因：百万行数据一次性加载。解法：pd.read_excel(file, chunksize=10000) 分批读取
✅ 坑4：合并后索引错乱
原因：原文件各有自己的索引。解法：pd.concat(df_list, ignore_index=True) 重置索引
✅ 坑5：文件被占用无法保存
原因：文件在Excel中打开着。解法：关闭Excel后再运行脚本
八、总结
本文覆盖了4个核心场景：
✅ 批量合并100个Excel → 2分钟搞定，3行核心代码
✅ 日期/金额格式清洗 → 彻底消灭格式不一致问题
✅ 生成带颜色格式的报表 → 不再手动调格式
✅ 条件格式高亮异常值 → 数据质量检查自动化
下一篇：Python自动化发送邮件——定时把Excel报表发给老板，关注公众号「船长Talk」不错过。
📌 更多资源
本文只讲解了核心用法，更多实战案例和源码：
👉 关注公众号：船长Talk
👉 回复关键词【Python办公】获取完整源码包
👉 加入技术交流群，与500+数据分析师一起成长
