Python自动化办公项目实例：批量处理Excel与邮件发送实战

项目概览：Excel批量数据清洗、动态拆分与SMTP邮件定向分发全流程

一、项目需求解构与技术架构

自动化办公脚本不仅要替代手工复制粘贴，更需要兼顾数据隐私安全与网络交付的稳定性。本项目的核心目标是：读取两份不同维度的企业原始Excel文件（员工基础名单与月度业绩明细），完成数据清洗、主键关联与分组汇总；随后为每一位员工动态生成专属的单人Excel明细附件；最后通过SMTP协议将个性化HTML正文及附件精准推送至员工邮箱。

整个项目由两个核心阶段构成：前半段是本地数据加工流水线，负责消除脏数据、统一主键并切片生成目标文件；后半段是网络投递流水线，负责封装MIME邮件实体、执行TLS握手验证并进行受控频率的循环发送。

图1：批量处理Excel与邮件分发四阶流水线架构

在技术选型上，本项目遵循“轻量、稳健、可维护”的原则：

• Pandas & OpenPyXL：作为数据处理核心库，处理结构化表格速度快，支持复杂条件筛选、关联运算（Merge）和分组（Groupby），并可直接导出带格式的标准 .xlsx 文件。
• smtplib & email：Python标准库内置模块，无需额外安装第三方不可控依赖，支持SSL加密与STARTTLS安全握手，具备对不同SMTP服务端的高兼容性。
• Logging & CSV状态记录器：用于记录每一封邮件的发送状态（时间戳、收件人、投递回执代码），确保脚本遇到突发中断时具备断点续发能力。

二、Excel批量读取、清洗与个性化切分实战

实际业务中的原始Excel表格通常存在列名空格、工号前导零缺失、数值列出现空字符串等典型问题。如果直接遍历原始单元格，极易引发程序异常退出。因此，必须先对数据进行标准化清洗。

图2：Excel数据源批量合并、数据清洗与拆分导出模型

1. 依赖库环境准备

确保开发环境中已安装 pandas 和 openpyxl。在命令行终端执行以下安装命令：

pip install pandas openpyxl

2. 数据加载与多表关联

假设业务目录存在两张表：staff_list.xlsx（存储工号、姓名、部门及邮箱）和 performance_details.xlsx（存储工号、销售订单号、成单金额及提成比例）。我们需要以“工号”为连接键执行左连接（Left Join），确保所有人员基础信息均被保留。

import os
import re
import pandas as pd

def load_and_clean_data(staff_file, detail_file):
    # 强制将工号读取为字符串，防止'0012'被转换为数字12导致前导零丢失
    df_staff = pd.read_excel(staff_file, dtype={"emp_id": str})
    df_details = pd.read_excel(detail_file, dtype={"emp_id": str})

    # 清洗列名与字段中的不可见空格
    df_staff.columns = [c.strip() for c in df_staff.columns]
    df_details.columns = [c.strip() for c in df_details.columns]
    df_staff["emp_id"] = df_staff["emp_id"].str.strip()
    df_details["emp_id"] = df_details["emp_id"].str.strip()

    # 剔除空行
    df_staff.dropna(subset=["emp_id", "email"], inplace=True)

    # 邮箱格式正则校验
    email_regex = re.compile(r"^[\w\.-]+@[\w\.-]+\.\w+$")
    valid_mask = df_staff["email"].apply(lambda x: bool(email_regex.match(str(x).strip())))
    df_staff = df_staff[valid_mask]

    # 合并业绩明细
    merged_df = pd.merge(df_staff, df_details, on="emp_id", how="left")
    
    # 缺失提成金额填充为0
    if "commission" in merged_df.columns:
        merged_df["commission"] = merged_df["commission"].fillna(0.0)

    return df_staff, merged_df

3. 按人员拆分独立Excel附件

为保障员工隐私，必须为每个人生成仅包含其自身记录的独立Excel文件，保存在临时输出文件夹中：

def split_and_export_reports(df_staff, merged_df, output_dir="temp_reports"):
    if not os.path.exists(output_dir):
        os.makedirs(output_dir)

    report_manifest = []

    # 按员工循环切分
    for _, staff in df_staff.iterrows():
        emp_id = staff["emp_id"]
        emp_name = staff["name"]
        emp_email = staff["email"]

        # 筛选单人明细记录
        personal_df = merged_df[merged_df["emp_id"] == emp_id].copy()
        
        # 计算关键统计摘要供邮件正文展示
        total_commission = personal_df["commission"].sum() if "commission" in personal_df else 0.0
        order_count = len(personal_df) if not personal_df["commission"].isna().all() else 0

        # 导出为单人Excel附件
        filename = f"{emp_id}_{emp_name}_2026年3月结算明细.xlsx"
        filepath = os.path.join(output_dir, filename)
        
        # 只保留明细展示列导出
        export_cols = ["emp_id", "name", "order_id", "order_date", "commission"]
        valid_cols = [c for c in export_cols if c in personal_df.columns]
        personal_df[valid_cols].to_excel(filepath, index=False)

        report_manifest.append({
            "emp_id": emp_id,
            "name": emp_name,
            "email": emp_email,
            "total_commission": round(total_commission, 2),
            "order_count": order_count,
            "attachment_path": filepath,
            "attachment_name": filename
        })

    return report_manifest

三、MIME邮件结构封装与安全SMTP协议投递

一封专业的企业通知邮件包含三个要素：清晰的发件人展示、排版整洁的HTML正文、以及经过正确编码的中文名称附件。Python内置的 email.mime 提供了完整的对象化封装机制。

图3：Python客户端与企业SMTP服务器交互时序与状态流转

1. 邮件协议与端口选择

2. 构建包含HTML正文与多语言附件的MIMEMultipart消息

import os
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from email.header import Header
from email.utils import formataddr

def build_email_message(sender_email, sender_name, item):
    msg = MIMEMultipart("mixed")
    
    # 构建发件人与收件人Header
    msg["From"] = formataddr((Header(sender_name, "utf-8").encode(), sender_email))
    msg["To"] = formataddr((Header(item["name"], "utf-8").encode(), item["email"]))
    msg["Subject"] = Header(f"【月度通知】{item['name']}您好，2026年3月绩效明细已送达", "utf-8")

    # 构建HTML富文本正文
    html_content = f"""
    <div style="font-family:'Microsoft YaHei',sans-serif;color:#2C3E50;line-height:1.6;">
      <h3 style="color:#456581;">尊敬的 {item['name']} 同事：</h3>
      <p>您好！本月度业务数据核算工作已经完成，您的个人结算摘要如下：</p>
      <table style="border-collapse:collapse;width:380px;margin:15px 0;">
        <tr style="background:#F1F5F9;">
          <td style="padding:8px 12px;border:1px solid #CBD5E1;font-weight:bold;">有效成单笔数</td>
          <td style="padding:8px 12px;border:1px solid #CBD5E1;">{item['order_count']} 笔</td>
        </tr>
        <tr style="background:#FFFFFF;">
          <td style="padding:8px 12px;border:1px solid #CBD5E1;font-weight:bold;color:#438A80;">本月提成总计</td>
          <td style="padding:8px 12px;border:1px solid #CBD5E1;font-weight:bold;color:#438A80;">¥ {item['total_commission']}</td>
        </tr>
      </table>
      <p>详细清单请查阅本邮件附件：<strong>{item['attachment_name']}</strong>。如对数据有疑问，请于3个工作日内联系财务核对。</p>
      <hr style="border:none;border-top:1px solid #E2E8F0;margin:20px 0;"/>
      <p style="font-size:12px;color:#94A3B8;">本邮件由系统自动发出，请勿直接原路回复。</p>
    </div>
    """
    msg.attach(MIMEText(html_content, "html", "utf-8"))

    # 读取Excel附件并以RFC2231规范封装中文文件名
    if os.path.exists(item["attachment_path"]):
        with open(item["attachment_path"], "rb") as f:
            part = MIMEApplication(f.read())
            part.add_header(
                "Content-Disposition", 
                "attachment", 
                filename=("utf-8", "", item["attachment_name"])
            )
            msg.attach(part)

    return msg

四、生产级批量投递整合脚本

将各个模块整合成可以直接运行的主流程脚本。在真实生产运行中，单次循环中增加适量延时（例如 1.5 秒）并用 try...except 包裹单人投递过程，能够有效避免被邮件运营商误判为垃圾邮件或因单点故障中断全局。

import smtplib
import time
import logging
from datetime import datetime

# 配置控制台日志
logging.basicConfig(level=logging.INFO, format="%(asctime)s [%(levelname)s] %(message)s")

def batch_send_pipeline(manifest, smtp_config, log_csv="send_result_log.csv"):
    server = None
    results = []

    try:
        logging.info(f"正在连接 SMTP 服务器 {smtp_config['host']}:{smtp_config['port']}...")
        server = smtplib.SMTP_SSL(smtp_config["host"], smtp_config["port"], timeout=20)
        server.login(smtp_config["user"], smtp_config["password"])
        logging.info("SMTP 身份认证成功，开始批量投递任务。")

        for idx, item in enumerate(manifest, 1):
            recipient = item["email"]
            emp_name = item["name"]
            logging.info(f"[{idx}/{len(manifest)}] 正在处理: {emp_name} <{recipient}>")

            try:
                msg = build_email_message(smtp_config["user"], smtp_config["sender_name"], item)
                server.send_message(msg)
                status = "SUCCESS"
                err_msg = ""
                logging.info(f"-> 投递成功: {recipient}")
            except Exception as e:
                status = "FAILED"
                err_msg = str(e)
                logging.error(f"-> 投递失败: {recipient}，原因: {err_msg}")

            results.append({
                "emp_id": item["emp_id"],
                "name": emp_name,
                "email": recipient,
                "status": status,
                "error": err_msg,
                "timestamp": datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            })

            # 频率控制，间隔1.5秒，避免触发反垃圾拦截
            time.sleep(1.5)

    except Exception as e:
        logging.critical(f"SMTP 通道异常中断: {e}")
    finally:
        if server:
            try:
                server.quit()
                logging.info("SMTP 会话已安全关闭。")
            except Exception:
                pass

        # 将投递结果固化到CSV文件，便于复盘与断点续发
        df_res = pd.DataFrame(results)
        df_res.to_csv(log_csv, index=False, encoding="utf-8-sig")
        logging.info(f"批处理完成，发送记录已保存至: {log_csv}")

五、生产环境避坑指南与容错总结

将代码从个人实验环境迁移到企业正式运行时，常会遭遇各种因安全策略或复杂网络引发的非预期行为。以下总结了核心控制要素：

图4：生产级自动化办公系统关键控制要素总结清单

1. 敏感凭据隔离管理： 绝对不要将邮箱授权码、服务器地址直接硬编码在代码仓库中。推荐使用操作系统的环境变量（通过 os.getenv("SMTP_AUTH_CODE") 获取）或经过权限保护的外部配置文件（如仅自身可读的 .env 文件）注入。
2. 长列表断点续发机制： 当一次性需要发送数千封邮件时，若在第500封出现网络抖动，盲目重启会导致前500人收到重复邮件。建议在开始循环前，先读取 send_result_log.csv 中标记为 SUCCESS 的工号集合，直接跳过已完成项，实现幂等断点续发。
3. 中文附件名防乱码： 部分较旧的邮件客户端不支持通用的UTF-8文件名直接解析。采用标准 part.add_header('Content-Disposition', 'attachment', filename=('utf-8', '', '中文名称.xlsx')) 能够最大程度兼容移动端与老旧客户端。
4. 发件人地址与From头保持一致： 多数主流企业反垃圾系统（如SPF与DMARC规则）会严格核验 msg["From"] 中的发件人邮箱是否与实际登录SMTP的账号完全一致。如果不一致，邮件可能被直接拒收（550 Mailbox unavailable）或直接进入垃圾箱。