Python自动化办公实战：用openpyxl批量处理Excel报表（附完整代码）

clawdashi

538人浏览 · 2026-05-01 00:06:46

clawdashi · 2026-05-01 00:06:46 发布

↵

每月花半天时间手动合并各部门Excel报表？Python几秒钟搞定。本文手把手教你用openpyxl实现多文件读取、数据清洗、合并汇总、自动输出，附完整可运行代码。

一、场景说明

做过数据汇总的人都知道这个痛：每到月底，销售部、市场部、运营部各交一份Excel报表，格式大同小异但又不完全一致——有的多了空行，有的表头拼写不统一，有的数值列混进了文本。你需要把它们合并成一张汇总表，再算出各部门的合计数据。

手动操作一次要半小时到一小时，而且容易出错。用Python + openpyxl，写一次脚本，以后每个月几秒钟跑完。

本文的目标场景：

输入：3个部门的月度销售报表（.xlsx），每个文件包含"姓名、部门、销售额、订单数"四列
处理：读取所有文件 → 清洗脏数据 → 合并到一张表 → 按部门汇总统计
输出：一份干净的汇总报表

二、环境准备

2.1 安装依赖

只需要一个库：

pip install openpyxl

openpyxl 是纯Python实现的Excel读写库，支持 .xlsx 格式，不依赖Office软件，Linux服务器上也能跑。

2.2 项目目录结构

project/
├── input/                # 存放各部门的原始报表
├── output/               # 输出汇总结果
├── generate_sample.py    # 生成示例数据（可选）
└── merge_reports.py      # 主脚本

三、读取多个Excel文件

核心思路：用 os.listdir() 扫描目录，筛选 .xlsx 文件，逐个用 openpyxl 打开读取。

import openpyxl
import os

def read_all_reports(input_dir):
    all_records = []
    
    for filename in sorted(os.listdir(input_dir)):
        if not filename.endswith(".xlsx"):
            continue
        
        filepath = os.path.join(input_dir, filename)
        wb = openpyxl.load_workbook(filepath, data_only=True)
        ws = wb.active
        
        print(f"正在读取: {filename}，共 {ws.max_row} 行")
        
        headers = [cell.value for cell in ws[1]]
        for row in ws.iter_rows(min_row=2, values_only=True):
            record = dict(zip(headers, row))
            all_records.append(record)
        
        wb.close()
    
    print(f"共读取 {len(all_records)} 条原始记录")
    return all_records

关键点说明：

data_only=True：读取单元格的计算结果值而非公式本身
ws.iter_rows(values_only=True)：直接返回值的元组，比逐个访问 cell.value 更高效
用 dict(zip(headers, row)) 把每行数据转成字典，后续处理更方便

四、数据清洗

真实报表中最常见的三类脏数据：空行、空值字段、数值列混入文本。我们逐一处理：

import re

def clean_records(records):
    cleaned = []
    skipped = 0
    
    for record in records:
        # 过滤空行
        if all(v is None for v in record.values()):
            skipped += 1
            continue
        
        # 过滤姓名为空的记录
        name = record.get("姓名")
        if not name or str(name).strip() == "":
            skipped += 1
            continue
        
        # 清洗销售额
        sales = record.get("销售额", 0)
        sales = parse_number(sales)
        
        # 清洗订单数
        orders = record.get("订单数", 0)
        orders = int(parse_number(orders))
        
        cleaned.append({
            "姓名": str(name).strip(),
            "部门": str(record.get("部门", "未知")).strip(),
            "销售额": round(sales, 2),
            "订单数": orders,
        })
    
    print(f"清洗完成: 有效 {len(cleaned)} 条，丢弃 {skipped} 条")
    return cleaned

def parse_number(value):
    if value is None:
        return 0.0
    if isinstance(value, (int, float)):
        return float(value)
    
    text = str(value).strip()
    match = re.search(r"[d.]+", text)
    if match:
        try:
            return float(match.group())
        except ValueError:
            return 0.0
    return 0.0

五、合并汇总

数据清洗完之后，我们做两件事：生成明细表 + 按部门汇总统计。

from collections import defaultdict

def summarize_by_department(records):
    dept_stats = defaultdict(lambda: {"人数": 0, "总销售额": 0.0, "总订单数": 0})
    
    for record in records:
        dept = record["部门"]
        dept_stats[dept]["人数"] += 1
        dept_stats[dept]["总销售额"] += record["销售额"]
        dept_stats[dept]["总订单数"] += record["订单数"]
    
    summary = []
    for dept, stats in sorted(dept_stats.items()):
        avg_sales = stats["总销售额"] / stats["人数"] if stats["人数"] > 0 else 0
        summary.append({
            "部门": dept,
            "人数": stats["人数"],
            "总销售额": round(stats["总销售额"], 2),
            "总订单数": stats["总订单数"],
            "人均销售额": round(avg_sales, 2),
        })
    
    return summary

六、输出结果到Excel

最后一步，把明细数据和汇总数据写入同一个Excel文件的两个Sheet：

from openpyxl.styles import Font, Alignment, PatternFill, Border, Side

def write_output(records, summary, output_path):
    wb = openpyxl.Workbook()
    
    # Sheet1: 明细数据
    ws_detail = wb.active
    ws_detail.title = "明细数据"
    
    header_font = Font(bold=True, color="FFFFFF", size=11)
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    header_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"),
    )
    
    detail_headers = ["姓名", "部门", "销售额", "订单数"]
    ws_detail.append(detail_headers)
    for cell in ws_detail[1]:
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_align
        cell.border = thin_border
    
    for record in records:
        ws_detail.append([record[h] for h in detail_headers])
    
    # Sheet2: 部门汇总
    ws_summary = wb.create_sheet(title="部门汇总")
    
    summary_headers = ["部门", "人数", "总销售额", "总订单数", "人均销售额"]
    ws_summary.append(summary_headers)
    for cell in ws_summary[1]:
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_align
        cell.border = thin_border
    
    for item in summary:
        ws_summary.append([item[h] for h in summary_headers])
    
    wb.save(output_path)
    print(f"汇总报表已保存: {output_path}")

七、主程序入口

把所有模块串起来：

def main():
    input_dir = "input"
    output_path = os.path.join("output", "月度汇总报表_202604.xlsx")
    
    # 第一步：读取所有报表
    raw_records = read_all_reports(input_dir)
    
    # 第二步：数据清洗
    clean_data = clean_records(raw_records)
    
    # 第三步：按部门汇总
    summary = summarize_by_department(clean_data)
    
    # 打印汇总预览
    print("\n========== 部门汇总 ==========")
    print(f"{'部门':<8} {'人数':<6} {'总销售额':>12} {'总订单数':>8} {'人均销售额':>12}")
    print("-" * 50)
    for item in summary:
        print(f"{item['部门']:<8} {item['人数']:<6} {item['总销售额']:>12,.2f} ...")
    
    # 第四步：输出到Excel
    write_output(clean_data, summary, output_path)

if __name__ == "__main__":
    main()

八、运行效果

正在读取: 市场部_202604.xlsx，共 8 行
正在读取: 销售部_202604.xlsx，共 7 行
正在读取: 运营部_202604.xlsx，共 7 行
共读取 19 条原始记录
清洗完成: 有效 14 条，丢弃 5 条

========== 部门汇总 ==========
部门     人数   总销售额       总订单数   人均销售额
--------------------------------------------------
市场部   4       98,234.56      312     24,558.64
销售部   5      156,789.01      523     31,357.80
运营部   5      134,567.89      445     26,913.58

汇总报表已保存: output/月度汇总报表_202604.xlsx