import asyncio
import pandas as pd
import os
import math
from datetime import datetime
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
import aiosmtplib
import time
def safe_path_join(base: str, *parts) -> str:
"""
安全路径拼接函数,处理空单元格和数值类型
:param base: 基础路径
:param parts: 路径组件
:return: 拼接后的完整路径
"""
processed = [base]
for part in parts:
# 处理NaN值
if pd.isna(part):
continue
# 处理浮点数和整数
if isinstance(part, float) and math.isnan(part):
continue
elif isinstance(part, float) and part.is_integer():
processed.append(str(int(part)))
elif isinstance(part, (float, int)):
processed.append(str(part))
# 处理字符串
elif isinstance(part, str) and part.strip():
processed.append(part)
# 拼接并规范化路径
path = os.path.join(*processed)
return os.path.normpath(path)
async def send_email_async(to_addrs, subject, body, attachments, smtp_config):
"""
异步发送邮件,包含附件检查和重发机制
:param to_addrs: 收件人列表
:param subject: 邮件主题
:param body: 邮件正文
:param attachments: 附件路径列表
:param smtp_config: SMTP服务器配置
:return: 发送是否成功
"""
# 1. 附件检查 - 无附件或附件不存在则不发送
valid_attachments = []
for file_path in attachments:
if file_path and os.path.exists(file_path):
valid_attachments.append(file_path)
elif file_path:
print(f"警告:附件不存在 - {file_path}")
if not valid_attachments:
print(f"跳过发送: {subject} - 无有效附件")
return False
# 2. 创建邮件消息
msg = MIMEMultipart()
msg["From"] = smtp_config['username']
msg["To"] = ", ".join(to_addrs)
msg["Subject"] = subject
msg.attach(MIMEText(body, "plain"))
# 3. 添加有效附件
for file_path in valid_attachments:
with open(file_path, "rb") as f:
part = MIMEApplication(f.read(), Name=os.path.basename(file_path))
part['Content-Disposition'] = f'attachment; filename="{os.path.basename(file_path)}"'
msg.attach(part)
# 4. 带重试机制的发送
max_retries = 3 # 最大重试次数
retry_delay = 5 # 初始重试延迟(秒)
for attempt in range(1, max_retries + 1):
try:
await aiosmtplib.send(
msg,
hostname=smtp_config['host'],
port=smtp_config['port'],
username=smtp_config['username'],
password=smtp_config['password'],
use_tls=True,
timeout=10 # 设置超时时间
)
print(f"邮件发送成功: {subject} (尝试 {attempt})")
return True
except (aiosmtplib.SMTPException, asyncio.TimeoutError, OSError) as e:
error_type = type(e).__name__
print(f"邮件发送失败: {subject} | 尝试 {attempt}/{max_retries} | 错误: {error_type} - {str(e)}")
if attempt < max_retries:
# 指数退避策略:重试延迟逐步增加
wait_time = retry_delay * (2 ** (attempt - 1))
print(f"等待 {wait_time}秒后重试...")
await asyncio.sleep(wait_time)
print(f"错误: {subject} - 达到最大重试次数仍失败")
return False
async def process_group_async(group, date_folder, smtp_config):
"""
处理分组数据并异步发送邮件
:param group: 同一分类的数据组
:param date_folder: 当前日期文件夹路径
:param smtp_config: SMTP配置
"""
today = datetime.today()
F = today.strftime('%Y{y}%m{m}%d{d}').format(y='年', m='月', d='日')
tasks = []
for _, row in group.iterrows():
# 获取数据
customer = row.iloc[2] # 第三列:客户名称
subject = f"{row.iloc[3]} - {customer}" # 第四列:邮件主题
# 安全生成附件路径
attachments = []
for col_index in range(4, 7): # 处理第五、六、七列
filename = row.iloc[col_index]
if pd.isna(filename) or not str(filename).strip():
continue
# 使用安全路径拼接
file_path = safe_path_join(date_folder, filename)
if file_path != date_folder: # 确保不是空路径
attachments.append(file_path)
# 提取邮箱地址
to_addrs = []
for col_index in range(7, len(row)): # 从第八列开始
email = row.iloc[col_index]
if isinstance(email, str) and "@" in email:
to_addrs.append(email)
if not to_addrs:
print(f"跳过: {customer} - 无有效邮箱")
continue
# 生成邮件正文
body = f"您好:\n 附件为{F}当天结算数据及下一交易日交易提示,请查收!"
# 创建异步任务
tasks.append(
send_email_async(to_addrs, subject, body, attachments, smtp_config)
)
# 并发执行当前分组的所有邮件任务
await asyncio.gather(*tasks)
async def main_async(excel_path, smtp_config):
"""
主异步函数
:param excel_path: Excel文件路径
:param smtp_config: SMTP服务器配置
"""
# 读取Excel数据
df = pd.read_excel(excel_path, header=0)
print(f"成功读取Excel数据,共{len(df)}条记录")
# 预处理空值 - 将第五、六、七列的NaN转换为空字符串
for col_index in [4, 5, 6]: # 第五、六、七列
if col_index < len(df.columns):
df.iloc[:, col_index] = df.iloc[:, col_index].fillna('')
# 创建当前日期文件夹
today = datetime.now().strftime("%Y%m%d")
date_folder = os.path.join(os.getcwd(), today)
os.makedirs(date_folder, exist_ok=True)
print(f"附件目录: {date_folder}")
# 按第二列(类别标识符)分组
grouped = df.groupby(df.columns[1])
groups = [(name, group) for name, group in grouped]
# 分批处理配置
batch_size = 5 # 每批任务组数
batch_delay = 1 # 批间延迟秒数
print(f"开始分批处理邮件任务,每批{batch_size}组,批间延迟{batch_delay}秒")
# 分批处理任务组
for i in range(0, len(groups), batch_size):
batch = groups[i:i+batch_size]
batch_tasks = []
# 创建当前批次的所有任务
for _, group in batch:
batch_tasks.append(
process_group_async(group, date_folder, smtp_config)
)
# 执行当前批次的所有任务
print(f"开始执行批次 {i//batch_size + 1},包含{len(batch_tasks)}组任务")
await asyncio.gather(*batch_tasks)
# 添加批间延迟(除最后一批外)
if i + batch_size < len(groups):
print(f"批次 {i//batch_size + 1} 完成,等待{batch_delay}秒后继续...")
await asyncio.sleep(batch_delay)
print("所有邮件处理完成")
if __name__ == "__main__":
# 配置参数(需根据实际情况修改)
CONFIG = {
'excel_path': 'recipients_test.xlsx', # Excel文件路径
'smtp': {
'host': 'smtp.qiye.163.com', # SMTP服务器
'port': 994, # 端口号
'username': 'jhqhjsb@zjlnqh.com',
'password': 'zvfLmHbWbhwHUqwg'
}
}
# 运行异步主程序
asyncio.run(main_async(CONFIG['excel_path'], CONFIG['smtp']))
现在会有重复发送的问题,请改进
最新发布