# 核心依賴庫
pip install pandas openpyxl  # Excel讀取/寫入/美化
pip install python-dotenv    # 環(huán)境變量管理（存儲郵箱密碼等敏感信息）
pip install logging          # 日志記錄

auto_office_system/
├── main.py                # 項目入口文件（整合Excel處理與郵件發(fā)送）
├── .env                   # 環(huán)境變量配置（郵箱賬號、授權(quán)碼、SMTP服務(wù)器）
├── excel_processor.py     # Excel數(shù)據(jù)處理模塊（讀取/清洗/美化/圖表）
├── email_sender.py        # 郵件發(fā)送模塊（SMTP連接/郵件構(gòu)造/批量發(fā)送）
├── templates/             # 模板文件
│   ├── email_template.html  # 郵件正文HTML模板
│   └── excel_template.xlsx  # Excel報表模板（可選）
├── input/                 # 輸入文件目錄（待處理的Excel文件）
│   └── 客戶數(shù)據(jù).xlsx
├── output/                # 輸出文件目錄（處理后的Excel報表）
└── logs/                  # 日志文件目錄（記錄操作過程與發(fā)送結(jié)果）

# 郵箱配置
SMTP_SERVER=smtp.qq.com    # SMTP服務(wù)器地址（QQ郵箱：smtp.qq.com；企業(yè)郵：如smtp.ym.163.com）
SMTP_PORT=465              # SMTP端口（SSL加密通常為465）
EMAIL_ACCOUNT=your_email@qq.com  # 發(fā)件人郵箱
EMAIL_PASSWORD=your_auth_code    # 郵箱授權(quán)碼（非登錄密碼）
 
# 郵件默認(rèn)配置
DEFAULT_CC=cc_email@qq.com  # 默認(rèn)抄送郵箱（可選）
DEFAULT_SUBJECT=【月度報表】客戶數(shù)據(jù)統(tǒng)計通知  # 默認(rèn)郵件主題

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  # 輸出文件目錄
        # 創(chuàng)建輸出目錄（不存在則創(chuàng)建）
        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  # 新增列標(biāo)記數(shù)據(jù)來源
                df["來源工作表"] = sheet_name  # 新增列標(biāo)記工作表
                all_data.append(df)
        
        # 合并所有數(shù)據(jù)
        combined_df = pd.concat(all_data, ignore_index=True)
        print(f"成功讀取{len(excel_files)}個Excel文件，共{len(combined_df)}條數(shù)據(jù)")
        return combined_df
    
    def clean_data(self, df):
        """數(shù)據(jù)清洗：處理缺失值、重復(fù)項、格式統(tǒng)一"""
        # 1. 刪除完全重復(fù)的行
        df_clean = df.drop_duplicates()
        # 2. 填充缺失值（數(shù)值列填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. 統(tǒng)一日期格式（若存在"日期"列）
        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"數(shù)據(jù)清洗完成：原始{len(df)}條 → 清洗后{len(df_clean)}條")
        return df_clean
    
    def beautify_excel(self, df, output_filename="processed_report.xlsx"):
        """Excel報表美化：設(shè)置樣式、條件格式、添加圖表"""
        output_path = os.path.join(self.output_dir, output_filename)
        
        # 保存DataFrame到Excel
        df.to_excel(output_path, index=False, sheet_name="清洗后數(shù)據(jù)")
        
        # 加載工作簿進行樣式美化
        wb = load_workbook(output_path)
        ws = wb["清洗后數(shù)據(jù)"]
        
        # 1. 設(shè)置表頭樣式（藍色背景、白色字體、加粗、居中）
        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")
        )
        
        # 應(yīng)用表頭樣式
        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. 應(yīng)用數(shù)據(jù)區(qū)樣式（居中、邊框）
        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. 自動調(diào)整列寬
        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. 添加數(shù)據(jù)統(tǒng)計圖表（以"客戶類型"和"成交金額"為例）
        if "客戶類型" in df.columns and "成交金額" in df.columns:
            # 統(tǒng)計各客戶類型成交金額
            chart_data = df.groupby("客戶類型")["成交金額"].sum().reset_index()
            
            # 創(chuàng)建柱狀圖
            chart = BarChart()
            chart.title = "各客戶類型成交金額統(tǒng)計"
            chart.x_axis.title = "客戶類型"
            chart.y_axis.title = "成交金額（元）"
            
            # 準(zhǔn)備圖表數(shù)據(jù)
            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)
            
            # 添加數(shù)據(jù)系列
            series = chart.add_data(y_data, titles_from_data=False)
            chart.set_categories(x_data)
            
            # 設(shè)置圖表樣式（高亮最大值）
            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("數(shù)據(jù)統(tǒng)計圖表")
            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. 讀取數(shù)據(jù)
            raw_df = self.read_excel_files()
            # 2. 數(shù)據(jù)清洗
            clean_df = self.clean_data(raw_df)
            # 3. 報表美化（輸出文件名包含當(dāng)前日期）
            output_filename = f"客戶數(shù)據(jù)報表_{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

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

# 加載環(huán)境變量
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):
        # 從環(huán)境變量獲取郵箱配置
        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):
        """構(gòu)造郵件內(nèi)容（支持HTML正文、多個附件）"""
        # 創(chuàng)建郵件對象（帶附件）
        msg = MIMEMultipart()
        msg["From"] = Header(self.email_account, "utf-8")  # 發(fā)件人
        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):
                    # 讀取文件內(nèi)容
                    with open(file_path, "rb") as f:
                        part = MIMEText(f.read(), "base64", "utf-8")
                        part["Content-Type"] = "application/octet-stream"
                        # 設(shè)置附件文件名
                        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對應(yīng)模板中的{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):
        """發(fā)送單封郵件，支持失敗重試"""
        subject = subject or self.default_subject
        if not content:
            # 使用默認(rèn)文本內(nèi)容（若未提供HTML模板）
            content = f"您好！這是一封自動化郵件通知，附件為相關(guān)報表，請查收。\n發(fā)送時間：{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
        
        # 構(gòu)造收件人列表（含抄送）
        recipients = [to_email]
        if self.default_cc:
            recipients.extend(self.default_cc.split(","))
        
        for attempt in range(retry + 1):
            try:
                # 連接SMTP服務(wù)器（SSL加密）
                with smtplib.SMTP_SSL(self.smtp_server, self.smtp_port) as server:
                    server.login(self.email_account, self.email_password)
                    # 構(gòu)造郵件
                    msg = self._create_email(to_email, subject, content, attachments)
                    # 發(fā)送郵件
                    server.sendmail(self.email_account, recipients, msg.as_string())
                logging.info(f"郵件發(fā)送成功：收件人={to_email}，主題={subject}")
                return True
            except Exception as e:
                logging.error(f"第{attempt+1}次發(fā)送郵件失敗（收件人={to_email}）：{str(e)}")
                if attempt >= retry:
                    logging.error(f"郵件發(fā)送最終失?。菏占?{to_email}")
                    return False
        return False
    
    def batch_send_emails(self, email_data_list, attachments=None):
        """批量發(fā)送郵件：接收收件人數(shù)據(jù)列表，支持個性化內(nèi)容"""
        """
        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("批量發(fā)送：未提供收件人數(shù)據(jù)")
            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("跳過無效數(shù)據(jù)：未包含收件人郵箱")
                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")
                )
                
                # 發(fā)送郵件
                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"批量發(fā)送失?。ㄊ占?{to_email}）：{str(e)}")
                fail_count += 1
                fail_emails.append(to_email)
        
        # 打印批量發(fā)送統(tǒng)計
        total = len(email_data_list)
        print(f"\n批量發(fā)送完成：總計{total}封 → 成功{success_count}封 → 失敗{fail_count}封")
        if fail_emails:
            print(f"發(fā)送失敗的郵箱：{', '.join(fail_emails)}")
            logging.info(f"批量發(fā)送統(tǒng)計：總計{total}封 → 成功{success_count}封 → 失敗{fail_count}封，失敗郵箱：{fail_emails}")
        
        return {
            "total": total,
            "success": success_count,
            "fail": fail_count,
            "fail_emails": fail_emails
        }

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>客戶數(shù)據(jù)統(tǒng)計通知</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>【月度客戶數(shù)據(jù)統(tǒng)計報表】</h2>
        </div>
        <div class="content">
            <p>尊敬的<span class="highlight">{username}</span>先生/女士：</p>
            <p>您好！現(xiàn)將{month}月度客戶數(shù)據(jù)統(tǒng)計報表發(fā)送給您，具體說明如下：</p>
            <ul>
                <li>報表包含客戶基本信息、成交金額、跟進狀態(tài)等關(guān)鍵數(shù)據(jù)；</li>
                <li>報表已按客戶類型分類統(tǒng)計，并附數(shù)據(jù)可視化圖表；</li>
                <li>請您查收附件并核對數(shù)據(jù)，如有疑問請及時反饋。</li>
            </ul>
            <p>發(fā)送時間：<span class="highlight">{send_time}</span></p>
            <p>祝您工作順利！</p>
        </div>
        <div class="footer">
            <p>本郵件為自動化發(fā)送，請勿直接回復(fù) | 聯(lián)系電話：XXX-XXXXXXX</p>
        </div>
    </div>
</body>
</html>

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數(shù)據(jù)批量處理（讀取→清洗→美化）
        logging.info("="*50)
        logging.info("開始Excel數(shù)據(jù)處理...")
        excel_processor = ExcelProcessor()
        excel_report_path = excel_processor.batch_process()
        logging.info(f"Excel處理完成，報表路徑：{excel_report_path}")
        
        # 第二步：批量發(fā)送郵件（示例數(shù)據(jù)，實際可從Excel讀取收件人信息）
        logging.info("\n" + "="*50)
        logging.info("開始批量發(fā)送郵件...")
        email_sender = EmailSender()
        
        # 收件人數(shù)據(jù)列表（實際項目可從Excel讀?。喝鏿d.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"}
        ]
        
        # 批量發(fā)送郵件（添加Excel報表作為附件）
        send_result = email_sender.batch_send_emails(
            email_data_list=email_data_list,
            attachments=excel_report_path
        )
        
        # 輸出最終結(jié)果
        logging.info("\n" + "="*50)
        logging.info(f"自動化流程全部完成！")
        logging.info(f"Excel報表：{excel_report_path}")
        logging.info(f"郵件發(fā)送統(tǒng)計：成功{send_result['success']}封，失敗{send_result['fail']}封")
        
    except Exception as e:
        logging.error(f"自動化流程執(zhí)行失?。簕str(e)}", exc_info=True)
        print(f"執(zhí)行失?。簕str(e)}")

if __name__ == "__main__":
    main()

2024-05-20 14:30:00,123 - INFO - 開始Excel數(shù)據(jù)處理...
2024-05-20 14:30:02,456 - INFO - 成功讀取1個Excel文件，共100條數(shù)據(jù)
2024-05-20 14:30:02,789 - INFO - 數(shù)據(jù)清洗完成：原始100條 → 清洗后98條
2024-05-20 14:30:05,123 - INFO - Excel報表美化完成，保存路徑：output/客戶數(shù)據(jù)報表_20240520.xlsx
2024-05-20 14:30:05,123 - INFO - 開始批量發(fā)送郵件...
2024-05-20 14:30:08,456 - INFO - 郵件發(fā)送成功：收件人=recipient1@qq.com，主題=【月度報表】客戶數(shù)據(jù)統(tǒng)計通知（2024-05）
2024-05-20 14:30:10,789 - INFO - 郵件發(fā)送成功：收件人=recipient2@163.com，主題=【月度報表】客戶數(shù)據(jù)統(tǒng)計通知（2024-05）
2024-05-20 14:30:12,123 - ERROR - 第2次發(fā)送郵件失?。ㄊ占?recipient3@ym.163.com）：SMTPConnectError
2024-05-20 14:30:12,123 - INFO - 批量發(fā)送完成：總計3封 → 成功2封 → 失敗1封
2024-05-20 14:30:12,123 - INFO - 自動化流程全部完成！