python3.6版本
python下载地址:https://www.python.org/getit/
excel
工作簿名:shuju.xlsx;;(会上传附件)
sheet表1名称:数据
sheet表2名称:邮箱 (请下载附件后填上正确的邮箱)
实现目的
该程序功能主要是提取excel信息并发邮件,不发整张表,只提取当天的信息,可作为各位参考!
废话也不多说,代码有注释
#!/usr/bin/python
from email.header import Header
from email.mime.text import MIMEText
from email.utils import parseaddr,formataddr
import smtplib
import pandas as pd
import datetime
from email import encoders
io = 'shuju.xlsx'
global time #因为在其他函数有调用,设置一个全局变量
#当前时间获取函数
def getdatetime():
riqi = datetime.datetime.strftime(datetime.datetime.now(),'%Y-%m-%d')
nowtime = riqi[0:4]+'年'+riqi[5:7]+'月'+riqi[8:10]+'日' #这里是切割重组为年月日的格式
print(nowtime)
return (nowtime)
time = getdatetime()
#发送邮件内容获取函数
'''
def readdata():
data = pd.read_excel(io,sheet_name='数据',converters={'数据':str})
datatest = data.loc[data['日期'] == time]
datatest1 = datatest.reset_index(drop=True)
print(datatest1.loc[0])
data1 = str(datatest1.loc[0])
return data1
'''
def readdata():
data = pd.read_excel(io,sheet_name='数据',converters={'数据':str})
indexnum = data[data.日期 == time].index.values
num = indexnum[0]
print(num)
data1 = data[data.日期 == time].星期
data2 = data[data.日期 == time].白班
data3 = data[data.日期 == time].晚班
data4 = data[data.日期 == time].备注
#print(data)
#print(data1)
#print(data2)
#print(data3)
#print(data4)
#print(type(data1))
data5 = time+'--'+str(data1[num])+'\n'+'白班:'+str(data2[num])+'\n'+'晚班:'+str(data3[num])+'\n'+'备注:'+str(data4[num])
#print(data5)
#print(type(data1[indexnum]))
return str(data5)
def _format_addr(s):
name,addr = parseaddr(s)
return formataddr((Header(name,'utf-8').encode(),addr))
#password是你开启SMTP时给你的邮箱授权码
def send(from_addr,password,to_addr,smtp_server,msg_text,subject,title):
msg=MIMEText(msg_text,'plain','utf-8')
msg['From'] = _format_addr(title+'<%s>'%from_addr)
receivermessg = '000000@qq.com'
msg['to'] = _format_addr('帅气的你<%s>' % receivermessg) # 显示的收信人
msg['Subject'] = Header(subject,'utf-8').encode()
# 发送邮件
server = smtplib.SMTP_SSL(smtp_server, 465)
# 打印出和SMTP服务器交互的所有信息
server.set_debuglevel(1)
# 登录SMTP服务器
server.login(from_addr, password)
# sendmail():发送邮件,由于可以一次发给多个人,所以传入一个list邮件正文是一个str,as_string()把MIMEText对象变成str。
server.sendmail(from_addr, to_addr, msg.as_string())
print('邮件发送成功!')
server.quit()
def reademill():# 获取邮箱数据
emilldata = pd.read_excel(io,sheet_name='邮箱', converters={'邮箱': str})
emilldata1 = emilldata.邮箱
nrcows = emilldata1.shape[0]
#print(nrcows)
#print(emilldata)
#print(emilldata1)
for i in range(1,nrcows): #循环获取邮箱数据
emilldata1[i] = emilldata1[i-1]+","+emilldata1[i] #邮箱组成特定字符串
#print(emilldata1[i])
re_emilldate = str(emilldata1[i])
#print(re_emilldate)
return re_emilldate
if __name__=='__main__':
msg_text_wr = readdata()
print(readdata())
from_addr = '136619@163.com'
password = 'FQUZHPGHSYDHJA'
#giuzjvwajmxyeahd
print(reademill())
to_str = reademill()
print(to_str)
to_addr = to_str.split(',')
smtp_server = 'smtp.163.com'
send(from_addr,password,to_addr,smtp_server,msg_text_wr,'值班提醒邮件','值班助手')
exit(0)