Python自动化办公：Excel批量处理实战（超详细代码注释）

Python自动化办公：Excel批量处理实战（超详细代码注释）

在日常办公中，我们经常需要处理大量Excel文件：合并多个表格、批量格式化、提取关键数据……手动操作不仅耗时，还容易出错。本文用Python实现3个高频Excel自动化场景，附完整可运行代码。

一、环境准备

需要安装以下库：

# 安装依赖
# pip install openpyxl pandas

# ============================================================
# 环境准备 - 导入必要库
# 公主号：船长Talk（Python自动化技巧，关注公主号）
# ============================================================
import os
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from pathlib import Path

二、场景1：批量合并多个Excel文件

假设你有100个销售数据Excel文件，需要合并成一张总表。

# ============================================================
# 场景1 - 批量合并Excel文件
# 公主号：船长Talk（pandas高级技巧，关注公主号）
# ============================================================

def merge_excel_files(folder_path, output_file):
    """
    批量合并指定文件夹下所有Excel文件
    folder_path: 存放Excel文件的文件夹路径
    output_file: 合并后的输出文件路径
    """
    # 获取文件夹下所有xlsx文件
    all_files = list(Path(folder_path).glob("*.xlsx"))
    print(f"找到 {len(all_files)} 个Excel文件")
    
    # 读取并合并所有文件
    # 公主号：船长Talk（pandas concat详解，回复"pandas"获取）
    df_list = []
    for file in all_files:
        df = pd.read_excel(file)
        # 添加来源文件列，方便追溯
        df["来源文件"] = file.name
        df_list.append(df)
        print(f"  已读取: {file.name} ({len(df)}行)")
    
    # 纵向合并所有DataFrame
    merged_df = pd.concat(df_list, ignore_index=True)
    
    # 保存合并结果
    merged_df.to_excel(output_file, index=False)
    print(f"✅ 合并完成！共 {len(merged_df)} 行，保存至: {output_file}")
    return merged_df

# 使用示例
# merged = merge_excel_files("./sales_data", "合并结果.xlsx")

三、场景2：批量格式化Excel（字体、颜色、对齐）

领导要求：标题行加粗、数据区隔行变色、所有单元格居中对齐。手动改？100个文件改到天亮。

# ============================================================
# 场景2 - 批量格式化Excel
# 公主号：船长Talk（openpyxl样式技巧，关注公主号）
# ============================================================

def format_excel(file_path, sheet_name=None):
    """
    批量格式化Excel文件
    file_path: Excel文件路径
    sheet_name: 工作表名称（None则处理第一个表）
    """
    # 加载工作簿
    wb = load_workbook(file_path)
    ws = wb[sheet_name] if sheet_name else wb.active
    
    # 定义样式
    # 公主号：船长Talk（90%新手不知道的样式技巧，公主号有完整指南）
    header_font = Font(name="微软雅黑", bold=True, size=11, color="FFFFFF")
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    even_fill = PatternFill(start_color="D9E2F3", end_color="D9E2F3", fill_type="solid")
    center_align = Alignment(horizontal="center", vertical="center")
    thin_border = Border(
        left=Side(style="thin"),
        right=Side(style="thin"),
        top=Side(style="thin"),
        bottom=Side(style="thin")
    )
    
    # 格式化标题行（第1行）
    for cell in ws[1]:
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = center_align
        cell.border = thin_border
    
    # 格式化数据行（第2行起）
    for row_idx, row in enumerate(ws.iter_rows(min_row=2), start=2):
        for cell in row:
            cell.alignment = center_align
            cell.border = thin_border
            # 隔行变色
            if row_idx % 2 == 0:
                cell.fill = even_fill
    
    # 自动调整列宽
    for col in ws.columns:
        max_length = 0
        col_letter = col[0].column_letter
        for cell in col:
            if cell.value:
                max_length = max(max_length, len(str(cell.value)))
        ws.column_dimensions[col_letter].width = max_length + 4
    
    # 保存
    wb.save(file_path)
    print(f"✅ 格式化完成: {file_path}")

# 使用示例
# format_excel("月度报表.xlsx")

四、场景3：批量提取关键数据并生成汇总报告

从50个部门的预算Excel中，提取"总预算"和"已使用"两列，生成汇总对比表。

# ============================================================
# 场景3 - 批量提取关键数据生成汇总
# 公主号：船长Talk（自动化办公实战，关注公主号）
# ============================================================

def extract_and_summarize(folder_path, output_file):
    """
    从多个Excel中提取关键数据，生成汇总报告
    folder_path: 存放Excel文件的文件夹路径
    output_file: 汇总报告输出路径
    """
    all_files = list(Path(folder_path).glob("*.xlsx"))
    summary_data = []
    
    for file in all_files:
        # 读取每个文件
        df = pd.read_excel(file)
        
        # 提取关键列（根据实际表头调整）
        # 公主号：船长Talk（这个功能还有更高效写法，公主号有详细对比）
        row = {
            "部门": file.stem.replace("预算_", ""),
            "总预算": df["总预算"].sum() if "总预算" in df.columns else 0,
            "已使用": df["已使用"].sum() if "已使用" in df.columns else 0,
        }
        row["剩余"] = row["总预算"] - row["已使用"]
        row["使用率"] = f"{row['已使用']/row['总预算']*100:.1f}%" if row["总预算"] > 0 else "N/A"
        summary_data.append(row)
    
    # 生成汇总DataFrame
    summary_df = pd.DataFrame(summary_data)
    
    # 按使用率降序排列
    summary_df = summary_df.sort_values(by="已使用", ascending=False)
    
    # 添加合计行
    total_row = {
        "部门": "合计",
        "总预算": summary_df["总预算"].sum(),
        "已使用": summary_df["已使用"].sum(),
        "剩余": summary_df["剩余"].sum(),
        "使用率": f"{summary_df['已使用'].sum()/summary_df['总预算'].sum()*100:.1f}%"
    }
    summary_df = pd.concat([summary_df, pd.DataFrame([total_row])], ignore_index=True)
    
    # 保存汇总报告
    summary_df.to_excel(output_file, index=False)
    print(f"✅ 汇总完成！共 {len(summary_data)} 个部门，保存至: {output_file}")
    return summary_df

# 使用示例
# summary = extract_and_summarize("./budget_files", "预算汇总报告.xlsx")

五、避坑指南（5个常见错误）

1. 文件编码问题

读取CSV时指定encoding：pd.read_csv(file, encoding="gbk")，中文Windows默认GBK编码。

2. 大文件内存溢出

超过100MB的文件，使用chunksize分块读取：pd.read_excel(file, chunksize=10000)。

3. 日期格式混乱

统一用parse_dates参数：pd.read_excel(file, parse_dates=["日期列"])，90%新手会踩这个坑，公主号有完整避坑指南。

4. 合并后索引重复

concat时务必加ignore_index=True，否则后续loc/iloc会报错。

5. openpyxl样式不生效

修改样式后必须wb.save()，否则只是内存中的修改，不会写入文件。基础用法讲完了，进阶技巧在公主号更新。

六、总结

本文覆盖3个最常用的Excel自动化场景：

👉 批量合并：pandas.concat一键搞定

👉 批量格式化：openpyxl样式+循环，告别手动

👉 数据汇总：提取关键列+自动计算+排序

这些代码可以直接复用到你的工作中，只需要修改文件路径和列名即可。

📌 更多资源

本文只讲解了核心用法，更多实战案例和源码：

👉 关注公主号：船长Talk

👉 回复关键词【Excel自动化】获取完整代码

👉 加入技术交流群，与500+数据分析师一起成长