Python 自动化办公实战：Excel 批量处理 + 邮件自动发送（附完整源码）

在日常办公中，重复的 Excel 数据整理、报表生成与邮件分发工作占据大量时间。本文将手把手教你用 Python 实现 “Excel 数据批量处理 + 自动化邮件发送” 全流程，涵盖数据读取、清洗、格式美化、报表生成及邮件带附件发送，支持自定义模板与批量操作，大幅提升办公效率，适合行政、运营、财务等岗位人员学习使用。

一、项目整体介绍

1.1 项目背景与价值

无论是月度业绩报表分发、客户数据统计通知，还是员工薪资条发放，都需要先整理 Excel 数据，再手动复制粘贴到邮件中发送 —— 过程繁琐且易出错。本项目通过 Python 自动化完成以下工作：

• 批量读取 Excel 数据（支持多工作表 / 多文件）；
• 数据清洗（缺失值填充、格式统一、重复数据剔除）；
• Excel 报表美化（单元格样式、条件格式、图表生成）；
• 自动生成个性化邮件内容（按收件人匹配对应数据）；
• 批量发送带 Excel 附件的邮件，支持抄送 / 密送。

1.2 技术栈选型

1.3 核心功能模块

系统采用 “模块化设计”，可按需组合使用，核心功能如下：

1. Excel 数据处理模块：
   • 多文件 / 多工作表批量读取；
   • 数据清洗（缺失值填充、重复项删除、数据格式转换）；
   • 报表美化（表头样式、数据对齐、条件格式高亮）；
   • 自动生成数据统计图表（柱状图 / 饼图）。
2. 邮件发送模块：
   • 支持 QQ 邮箱、企业邮箱 SMTP 配置；
   • 自定义邮件主题、正文模板（支持变量替换，如 “XXX 先生 / 女士，您的月度业绩如下”）；
   • 自动添加 Excel 附件（单个 / 多个）；
   • 批量发送 + 失败重试机制，日志记录发送结果。

二、环境搭建与准备

2.1 开发环境配置

1. 基础环境：Python 3.8+（推荐 3.9 版本，兼容性更佳）；
2. 依赖库安装：

bash

运行

# 核心依赖库 pip install pandas openpyxl # Excel读取/写入/美化 pip install python-dotenv # 环境变量管理（存储邮箱密码等敏感信息） pip install logging # 日志记录

1. 邮箱 SMTP 配置（关键步骤）：
   • 以 QQ 邮箱为例：登录 QQ 邮箱 → Settings → 账户 → 开启 “POP3/SMTP 服务” → 生成授权码（替代密码登录）；
   • 企业邮箱（如网易企业邮）：开启 SMTP 服务，确认服务器地址（如smtp.ym.163.com）与端口（通常 465，SSL 加密）。

2.2 项目目录结构

plaintext

auto_office_system/ ├── main.py # 项目入口文件（整合Excel处理与邮件发送） ├── .env # 环境变量配置（邮箱账号、授权码、SMTP服务器） ├── excel_processor.py # Excel数据处理模块（读取/清洗/美化/图表） ├── email_sender.py # 邮件发送模块（SMTP连接/邮件构造/批量发送） ├── templates/ # 模板文件 │ ├── email_template.html # 邮件正文HTML模板 │ └── excel_template.xlsx # Excel报表模板（可选） ├── input/ # 输入文件目录（待处理的Excel文件） │ └── 客户数据.xlsx ├── output/ # 输出文件目录（处理后的Excel报表） └── logs/ # 日志文件目录（记录操作过程与发送结果）

2.3 环境变量配置（.env 文件）

env

# 邮箱配置 SMTP_SERVER=smtp.qq.com # SMTP服务器地址（QQ邮箱：smtp.qq.com；企业邮：如smtp.ym.163.com） SMTP_PORT=465 # SMTP端口（SSL加密通常为465） EMAIL_ACCOUNT=your_email@qq.com # 发件人邮箱 EMAIL_PASSWORD=your_auth_code # 邮箱授权码（非登录密码） # 邮件默认配置 DEFAULT_CC=cc_email@qq.com # 默认抄送邮箱（可选） DEFAULT_SUBJECT=【月度报表】客户数据统计通知 # 默认邮件主题

三、核心模块实现

3.1 Excel 数据处理模块（excel_processor.py）

实现 Excel 数据的读取、清洗、美化与图表生成，支持多文件批量处理。

python

运行

import pandas as pd from openpyxl import load_workbook from openpyxl.styles import Font, PatternFill, Border, Side, Alignment from openpyxl.chart import BarChart, Reference from openpyxl.chart.series import DataPoint import os from datetime import datetime class ExcelProcessor: def __init__(self, input_dir="input", output_dir="output"): self.input_dir = input_dir # 输入文件目录 self.output_dir = output_dir # 输出文件目录 # 创建输出目录（不存在则创建） if not os.path.exists(output_dir): os.makedirs(output_dir) def read_excel_files(self): """批量读取input目录下所有Excel文件（支持.xlsx）""" excel_files = [f for f in os.listdir(self.input_dir) if f.endswith(".xlsx")] if not excel_files: raise FileNotFoundError("input目录下未找到Excel文件（.xlsx格式）") all_data = [] for file in excel_files: file_path = os.path.join(self.input_dir, file) # 读取所有工作表 xls = pd.ExcelFile(file_path) for sheet_name in xls.sheet_names: df = pd.read_excel(file_path, sheet_name=sheet_name) df["来源文件"] = file # 新增列标记数据来源 df["来源工作表"] = sheet_name # 新增列标记工作表 all_data.append(df) # 合并所有数据 combined_df = pd.concat(all_data, ignore_index=True) print(f"成功读取{len(excel_files)}个Excel文件，共{len(combined_df)}条数据") return combined_df def clean_data(self, df): """数据清洗：处理缺失值、重复项、格式统一""" # 1. 删除完全重复的行 df_clean = df.drop_duplicates() # 2. 填充缺失值（数值列填0，文本列填"未知"） for col in df_clean.columns: if df_clean[col].dtype in ["int64", "float64"]: df_clean[col] = df_clean[col].fillna(0) else: df_clean[col] = df_clean[col].fillna("未知") # 3. 统一日期格式（若存在"日期"列） if "日期" in df_clean.columns: df_clean["日期"] = pd.to_datetime(df_clean["日期"], errors="coerce").dt.strftime("%Y-%m-%d") # 4. 去除字符串前后空格 for col in df_clean.select_dtypes(include=["object"]).columns: df_clean[col] = df_clean[col].astype(str).str.strip() print(f"数据清洗完成：原始{len(df)}条 → 清洗后{len(df_clean)}条") return df_clean def beautify_excel(self, df, output_filename="processed_report.xlsx"): """Excel报表美化：设置样式、条件格式、添加图表""" output_path = os.path.join(self.output_dir, output_filename) # 保存DataFrame到Excel df.to_excel(output_path, index=False, sheet_name="清洗后数据") # 加载工作簿进行样式美化 wb = load_workbook(output_path) ws = wb["清洗后数据"] # 1. 设置表头样式（蓝色背景、白色字体、加粗、居中） header_font = Font(color="FFFFFF", bold=True) header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") header_alignment = Alignment(horizontal="center", vertical="center") # 边框样式 thin_border = Border( left=Side(style="thin"), right=Side(style="thin"), top=Side(style="thin"), bottom=Side(style="thin") ) # 应用表头样式 for col in range(1, ws.max_column + 1): cell = ws.cell(row=1, column=col) cell.font = header_font cell.fill = header_fill cell.alignment = header_alignment cell.border = thin_border # 2. 应用数据区样式（居中、边框） for row in range(2, ws.max_row + 1): for col in range(1, ws.max_column + 1): cell = ws.cell(row=row, column=col) cell.alignment = Alignment(horizontal="center", vertical="center") cell.border = thin_border # 3. 自动调整列宽 for col in ws.columns: max_length = 0 column = col[0].column_letter # 获取列字母（如A、B） for cell in col: try: if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) except: pass adjusted_width = min(max_length + 2, 20) # 最大宽度限制为20 ws.column_dimensions[column].width = adjusted_width # 4. 添加数据统计图表（以"客户类型"和"成交金额"为例） if "客户类型" in df.columns and "成交金额" in df.columns: # 统计各客户类型成交金额 chart_data = df.groupby("客户类型")["成交金额"].sum().reset_index() # 创建柱状图 chart = BarChart() chart.title = "各客户类型成交金额统计" chart.x_axis.title = "客户类型" chart.y_axis.title = "成交金额（元）" # 准备图表数据 x_data = Reference(ws, min_col=chart_data.columns.get_loc("客户类型") + 1, min_row=2, max_row=len(chart_data) + 1) y_data = Reference(ws, min_col=chart_data.columns.get_loc("成交金额") + 1, min_row=2, max_row=len(chart_data) + 1) # 添加数据系列 series = chart.add_data(y_data, titles_from_data=False) chart.set_categories(x_data) # 设置图表样式（高亮最大值） max_val = chart_data["成交金额"].max() for i, val in enumerate(chart_data["成交金额"]): if val == max_val: dp = DataPoint(idx=i, fill=PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")) series.dPt.append(dp) # 在新工作表插入图表 chart_ws = wb.create_sheet("数据统计图表") chart_ws.add_chart(chart, "B2") # 保存美化后的Excel wb.save(output_path) print(f"Excel报表美化完成，保存路径：{output_path}") return output_path def batch_process(self): """批量处理流程：读取→清洗→美化""" try: # 1. 读取数据 raw_df = self.read_excel_files() # 2. 数据清洗 clean_df = self.clean_data(raw_df) # 3. 报表美化（输出文件名包含当前日期） output_filename = f"客户数据报表_{datetime.now().strftime('%Y%m%d')}.xlsx" output_path = self.beautify_excel(clean_df, output_filename) return output_path except Exception as e: print(f"Excel处理失败：{str(e)}") raise

3.2 邮件发送模块（email_sender.py）

实现 SMTP 连接、邮件构造、批量发送，支持 HTML 模板与 Excel 附件。

python

运行

import smtplib from email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart from email.header import Header from dotenv import load_dotenv import os import logging from datetime import datetime # 加载环境变量 load_dotenv() # 配置日志 logging.basicConfig( filename=f"logs/email_send_{datetime.now().strftime('%Y%m%d')}.log", level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s" ) class EmailSender: def __init__(self): # 从环境变量获取邮箱配置 self.smtp_server = os.getenv("SMTP_SERVER") self.smtp_port = int(os.getenv("SMTP_PORT", 465)) self.email_account = os.getenv("EMAIL_ACCOUNT") self.email_password = os.getenv("EMAIL_PASSWORD") self.default_cc = os.getenv("DEFAULT_CC", "") self.default_subject = os.getenv("DEFAULT_SUBJECT", "自动化邮件通知") # 验证配置 self._validate_config() def _validate_config(self): """验证邮箱配置是否完整""" required_configs = ["SMTP_SERVER", "EMAIL_ACCOUNT", "EMAIL_PASSWORD"] for config in required_configs: if not os.getenv(config): raise ValueError(f".env文件中缺少配置：{config}") def _create_email(self, to_email, subject, content, attachments=None): """构造邮件内容（支持HTML正文、多个附件）""" # 创建邮件对象（带附件） msg = MIMEMultipart() msg["From"] = Header(self.email_account, "utf-8") # 发件人 msg["To"] = Header(to_email, "utf-8") # 收件人 msg["Subject"] = Header(subject, "utf-8") # 邮件主题 # 添加抄送（若有） if self.default_cc: msg["Cc"] = Header(self.default_cc, "utf-8") # 添加HTML正文 msg.attach(MIMEText(content, "html", "utf-8")) # 添加附件（支持多个文件） if attachments: attachments = attachments if isinstance(attachments, list) else [attachments] for file_path in attachments: if os.path.exists(file_path): # 读取文件内容 with open(file_path, "rb") as f: part = MIMEText(f.read(), "base64", "utf-8") part["Content-Type"] = "application/octet-stream" # 设置附件文件名 filename = os.path.basename(file_path) part["Content-Disposition"] = f'attachment; filename="{filename}"' msg.attach(part) logging.info(f"添加附件成功：{filename}") else: logging.warning(f"附件文件不存在：{file_path}") return msg def load_html_template(self, template_path="templates/email_template.html", **kwargs): """加载HTML邮件模板，替换变量（如{username}、{date}）""" if not os.path.exists(template_path): raise FileNotFoundError(f"邮件模板文件不存在：{template_path}") with open(template_path, "r", encoding="utf-8") as f: html_content = f.read() # 替换模板变量（kwargs中的key对应模板中的{key}） for key, value in kwargs.items(): html_content = html_content.replace(f"{{{key}}}", str(value)) return html_content def send_email(self, to_email, subject=None, content=None, attachments=None, retry=2): """发送单封邮件，支持失败重试""" subject = subject or self.default_subject if not content: # 使用默认文本内容（若未提供HTML模板） content = f"您好！这是一封自动化邮件通知，附件为相关报表，请查收。\n发送时间：{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}" # 构造收件人列表（含抄送） recipients = [to_email] if self.default_cc: recipients.extend(self.default_cc.split(",")) for attempt in range(retry + 1): try: # 连接SMTP服务器（SSL加密） with smtplib.SMTP_SSL(self.smtp_server, self.smtp_port) as server: server.login(self.email_account, self.email_password) # 构造邮件 msg = self._create_email(to_email, subject, content, attachments) # 发送邮件 server.sendmail(self.email_account, recipients, msg.as_string()) logging.info(f"邮件发送成功：收件人={to_email}，主题={subject}") return True except Exception as e: logging.error(f"第{attempt+1}次发送邮件失败（收件人={to_email}）：{str(e)}") if attempt >= retry: logging.error(f"邮件发送最终失败：收件人={to_email}") return False return False def batch_send_emails(self, email_data_list, attachments=None): """批量发送邮件：接收收件人数据列表，支持个性化内容""" """ email_data_list格式： [ {"to_email": "user1@qq.com", "username": "张三", "month": "2024-05"}, {"to_email": "user2@qq.com", "username": "李四", "month": "2024-05"} ] """ if not email_data_list: logging.warning("批量发送：未提供收件人数据") return success_count = 0 fail_count = 0 fail_emails = [] for data in email_data_list: to_email = data.get("to_email") if not to_email: logging.warning("跳过无效数据：未包含收件人邮箱") continue try: # 加载HTML模板并替换变量 content = self.load_html_template( username=data.get("username", "用户"), month=data.get("month", datetime.now().strftime("%Y-%m")), send_time=datetime.now().strftime("%Y-%m-%d %H:%M:%S") ) # 发送邮件 result = self.send_email( to_email=to_email, subject=f"{self.default_subject}（{data.get('month', '')}）", content=content, attachments=attachments ) if result: success_count += 1 else: fail_count += 1 fail_emails.append(to_email) except Exception as e: logging.error(f"批量发送失败（收件人={to_email}）：{str(e)}") fail_count += 1 fail_emails.append(to_email) # 打印批量发送统计 total = len(email_data_list) print(f"\n批量发送完成：总计{total}封 → 成功{success_count}封 → 失败{fail_count}封") if fail_emails: print(f"发送失败的邮箱：{', '.join(fail_emails)}") logging.info(f"批量发送统计：总计{total}封 → 成功{success_count}封 → 失败{fail_count}封，失败邮箱：{fail_emails}") return { "total": total, "success": success_count, "fail": fail_count, "fail_emails": fail_emails }

3.3 邮件 HTML 模板（templates/email_template.html）

支持个性化变量替换，样式美观，适合正式办公场景。

html

预览

<!DOCTYPE html> <html lang="zh-CN"> <head> <meta charset="UTF-8"> <title>客户数据统计通知</title> <style> body { font-family: "Microsoft YaHei", sans-serif; line-height: 1.6; color: #333; } .container { width: 80%; margin: 0 auto; padding: 20px; border: 1px solid #eee; border-radius: 8px; } .header { background-color: #4472C4; color: white; padding: 15px; border-radius: 8px 8px 0 0; text-align: center; } .content { padding: 20px; } .highlight { color: #4472C4; font-weight: bold; } .footer { margin-top: 30px; padding-top: 15px; border-top: 1px solid #eee; text-align: center; color: #666; } </style> </head> <body> <div class="container"> <div class="header"> <h2>【月度客户数据统计报表】</h2> </div> <div class="content"> <p>尊敬的<span class="highlight">{username}</span>先生/女士：</p> <p>您好！现将{month}月度客户数据统计报表发送给您，具体说明如下：</p> <ul> <li>报表包含客户基本信息、成交金额、跟进状态等关键数据；</li> <li>报表已按客户类型分类统计，并附数据可视化图表；</li> <li>请您查收附件并核对数据，如有疑问请及时反馈。</li> </ul> <p>发送时间：<span class="highlight">{send_time}</span></p> <p>祝您工作顺利！</p> </div> <div class="footer"> <p>本邮件为自动化发送，请勿直接回复 | 联系电话：XXX-XXXXXXX</p> </div> </div> </body> </html>

3.4 项目入口文件（main.py）

整合 Excel 处理与邮件发送流程，支持命令行运行。

python

运行

from excel_processor import ExcelProcessor from email_sender import EmailSender import logging # 配置日志 logging.basicConfig(level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s") def main(): try: # 第一步：Excel数据批量处理（读取→清洗→美化） logging.info("="*50) logging.info("开始Excel数据处理...") excel_processor = ExcelProcessor() excel_report_path = excel_processor.batch_process() logging.info(f"Excel处理完成，报表路径：{excel_report_path}") # 第二步：批量发送邮件（示例数据，实际可从Excel读取收件人信息） logging.info("\n" + "="*50) logging.info("开始批量发送邮件...") email_sender = EmailSender() # 收件人数据列表（实际项目可从Excel读取：如pd.read_excel("收件人列表.xlsx")） email_data_list = [ {"to_email": "recipient1@qq.com", "username": "张三", "month": "2024-05"}, {"to_email": "recipient2@163.com", "username": "李四", "month": "2024-05"}, {"to_email": "recipient3@ym.163.com", "username": "王五", "month": "2024-05"} ] # 批量发送邮件（添加Excel报表作为附件） send_result = email_sender.batch_send_emails( email_data_list=email_data_list, attachments=excel_report_path ) # 输出最终结果 logging.info("\n" + "="*50) logging.info(f"自动化流程全部完成！") logging.info(f"Excel报表：{excel_report_path}") logging.info(f"邮件发送统计：成功{send_result['success']}封，失败{send_result['fail']}封") except Exception as e: logging.error(f"自动化流程执行失败：{str(e)}", exc_info=True) print(f"执行失败：{str(e)}") if __name__ == "__main__": main()

四、系统运行与效果展示

4.1 运行步骤

1. 准备工作：在input目录下放入待处理的 Excel 文件（如 “客户数据.xlsx”），确保包含 “客户类型”“成交金额”“日期” 等关键列；
2. 配置.env 文件：填写发件人邮箱、授权码、SMTP 服务器等信息；
3. 运行程序：python main.py；
4. 查看结果：
   • 处理后的 Excel 报表保存在output目录（含美化样式与统计图表）；
   • 邮件发送结果记录在logs目录的日志文件中；
   • 收件人将收到带 HTML 格式正文与 Excel 附件的邮件。

4.2 核心效果展示

1. Excel 报表美化效果

• 表头：蓝色背景、白色加粗字体、居中对齐；
• 数据区：统一居中、添加边框、自动调整列宽；
• 条件格式：统计图表中高亮显示成交金额最大值；
• 额外工作表：包含客户类型成交金额柱状图，直观呈现数据趋势。

2. 邮件接收效果

• 正文：HTML 格式，包含个性化问候（如 “尊敬的张三先生 / 女士”）、月度信息、发送时间；
• 附件：自动添加处理后的 Excel 报表，支持在线预览或下载；
• 抄送：默认抄送邮箱可收到相同邮件，便于存档。

4.3 日志记录效果

日志文件将详细记录每一步操作：

plaintext

2024-05-20 14:30:00,123 - INFO - 开始Excel数据处理... 2024-05-20 14:30:02,456 - INFO - 成功读取1个Excel文件，共100条数据 2024-05-20 14:30:02,789 - INFO - 数据清洗完成：原始100条 → 清洗后98条 2024-05-20 14:30:05,123 - INFO - Excel报表美化完成，保存路径：output/客户数据报表_20240520.xlsx 2024-05-20 14:30:05,123 - INFO - 开始批量发送邮件... 2024-05-20 14:30:08,456 - INFO - 邮件发送成功：收件人=recipient1@qq.com，主题=【月度报表】客户数据统计通知（2024-05） 2024-05-20 14:30:10,789 - INFO - 邮件发送成功：收件人=recipient2@163.com，主题=【月度报表】客户数据统计通知（2024-05） 2024-05-20 14:30:12,123 - ERROR - 第2次发送邮件失败（收件人=recipient3@ym.163.com）：SMTPConnectError 2024-05-20 14:30:12,123 - INFO - 批量发送完成：总计3封 → 成功2封 → 失败1封 2024-05-20 14:30:12,123 - INFO - 自动化流程全部完成！

五、项目拓展与优化方向

1. 功能拓展：

   • 支持读取 CSV/CSV 文件，扩大输入格式范围；
   • 实现 Excel 数据筛选与拆分（如按部门 / 区域拆分报表，分别发送给对应负责人）；
   • 增加邮件发送状态回执（确认收件人已读）；
   • 支持多附件发送（如同时发送 Excel 报表 + PDF 说明文档）。

2. 系统优化：

   • 引入 Redis 缓存频繁使用的数据（如收件人列表），提升效率；
   • 增加异常监控（如邮件发送失败后自动发送告警通知给管理员）；
   • 支持定时任务（使用 APScheduler），每月自动执行报表生成与邮件发送；
   • 优化 Excel 处理性能，支持超大文件（10 万行 + 数据）批量处理。

3. 交互优化：

   • 开发简单 GUI 界面（使用 tkinter/pyqt），无需编写代码即可操作；
   • 支持自定义邮件模板与 Excel 样式模板，适配不同办公场景；
   • 增加数据预览功能，处理前可查看原始数据与清洗效果。

六、总结

本项目基于 Python 实现了 “Excel 批量处理 + 自动化邮件发送” 的完整办公流程，技术栈简洁实用，代码结构清晰，可直接应用于实际工作场景。通过本项目的学习，你可以掌握：

• pandas/openpyxl 的 Excel 数据处理与美化技巧；
• smtplib/email 的邮件构造与批量发送方法；
• 模块化编程与环境变量管理（敏感信息保护）；
• 日志记录与异常处理的工业级编程思维。

项目可根据实际办公需求灵活拓展，例如财务薪资条发放、运营业绩报表分发、行政通知推送等场景。建议在使用过程中结合自身工作流程调整代码，积累自动化办公经验，逐步实现 “一键搞定重复工作” 的高效办公模式。