业务场景:公司加班较重,每月初需要统计每个人的加班天数,并与每个人核实。写一小程序给每个人发送自己的加班记录,方便核实。
解决思路:两份数据源,打卡记录表和邮件表,通过姓名关联。
用一层循环,筛选出每个人的数据,调用邮件发送函数,通过附件形式将数据发送给相应的人。
操作步骤:
setp1: 获取数据源:
用pandas包读取excle表
pd.read_excel(io, sheet_name=0, header=0, names=None, index_col=None,
usecols=None, squeeze=False,dtype=None, engine=None,
converters=None, true_values=None, false_values=None,
skiprows=None, nrows=None, na_values=None, parse_dates=False,
date_parser=None, thousands=None, comment=None, skipfooter=0,
convert_float=True, **kwds)
setp2:定义邮件发送函数:
import smtplib
smtpObj = smtplib.SMTP( [host [, port [, local_hostname]]] )
- host: SMTP 服务器主机。 可以指定主机的ip地址或者域名如:runoob.com,这个是可选参数。
- port: 如果你提供了 host 参数, 你需要指定 SMTP 服务使用的端口号,一般情况下SMTP端口号为25。
- local_hostname: 如果SMTP在你的本机上,你只需要指定服务器地址为 localhost 即可。
Python SMTP对象使用sendmail方法发送邮件,语法如下:
SMTP.sendmail(from_addr, to_addrs, msg[, mail_options, rcpt_options]
- from_addr: 邮件发送者地址。
- to_addrs: 字符串列表,邮件发送地址。
- msg: 发送消息
-
这里要注意一下第三个参数,msg是字符串,表示邮件。我们知道邮件一般由标题,发信人,收件人,邮件内容,附件等构成,发送邮件的时候,要注意msg的格式。这个格式就是smtp协议中定义的
奉上代码:
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Mon Apr 1 12:17:10 2019
"""
import pandas as pd
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
import traceback
from email.mime.application import MIMEApplication
import os
#需要优化: 1.循环生成附件后删除文件
def get_xlsx(senddata,fileName):
sendExcel = senddata.to_excel(fileName)
return sendExcel
def create_email(email_from,email_to,email_subject,
email_text,annex_path,annex_name):
message =MIMEMultipart()
message.attach(MIMEText(email_text,'plain','gbk'))
message['From'] = Header(email_from,'gbk')
message['To'] = Header(email_to,'utf-8')
message['subject'] = Header(email_subject, 'utf-8')
#att1 = MIMEText(open(annex_path,'rb').read(),'base64','gbk')
#att1["Content-Type"] = 'application/octet-stream'
#att1["Content-Disposition"] ='attachment;filename=' +annex_name
#message.attach(att1)
basename = "考勤记录.xlsx"
xlsxpart = MIMEApplication(open(annex_path, 'rb').read())
xlsxpart.add_header('Content-Disposition', 'attachment', filename=('gbk', '', basename))
message.attach(xlsxpart)
return message
def main():
online_data = pd.read_excel('XXXX/数据表.xlsx'
,sheet_name="原表",encoding='gbk')
mail_data = pd.read_excel('XXX/数据表.xlsx'
,sheet_name="邮箱",encoding='gbk')
my_email_from ='IT部机器人'
my_sender = 'XXXX'
my_password ='XXXX'
for name in mail_data['姓名']:
rec_mail = mail_data['邮箱'][mail_data['姓名']==name]
send_data = online_data[online_data['姓名']==name]
file_path = '/Users/17098/Desktop/打卡记录发送/'+name+'本月考勤记录.xlsx'
get_xls(send_data,file_path)
my_email_subject =name + '月度考勤报表'
my_email_text ="Dear all ,\n\t附件为本月考勤数据,请查收"
my_annex_path = file_path
my_annex_name = name+"本月考勤记录.xlsx"
my_receiver =[]
my_receiver.append(rec_mail.values[0])
my_email_to = name+"<"+my_receiver[0]+">"
my_msg = create_email(my_email_from, my_email_to, my_email_subject,
my_email_text, my_annex_path, my_annex_name)
try:
# server =smtplib.SMTP_SSL("smtp.exmail.qq.com",465)
server = smtplib.SMTP_SSL("smtp.qq.com", 465)
server.ehlo()
server.login(my_sender, my_password)
server.sendmail(my_sender, my_receiver, my_msg.as_string())
print(name+"邮件发送成功")
server.quit()
os.remove(file_path)
except Exception:
print(traceback.print_exc())
print(name+"邮件发送失败")
if __name__ == "__main__":
main();