Python3.6读取excel指定数据并根据邮件列表群发

 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)