定时执行任务，发送带excel附件及excel内容展示邮件

 

具体参看如下代码

 

import requests,codecs
import pandas as pd
import os

import smtplib,os,json
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication

from openpyxl import load_workbook
import readConfig as Rc



import time
from apscheduler.schedulers.blocking import BlockingScheduler


import pgSqlQuery as Pq






def mailWrite(filepath,sheet):
    '''写邮件，读取excel文件内容作为邮件正文'''
    df2 = pd.read_excel(filepath, sheet_name=sheet)
    # 设置html存放目录
    htmlfiles = 'pgResultHtml'
    if not os.path.exists(htmlfiles):
        os.mkdir(htmlfiles)
    htmlName = sheet + '.html'
    htmlPath = os.path.join(htmlfiles,htmlName)
    # 生成html结果文件
    with codecs.open(htmlPath, 'w', 'utf-8') as html_file:
        html_file.write(df2.to_html(header=True, index=False))
    # 打开并读取html结果文件内容
    with open(html_file.name,'r') as html_f:
        htmlContent = html_f.read()
        # print(htmlContent)
    # 返回文件内容
    return htmlContent


def sendEmail(fileName):
    '''发送带附近及展示附件内容邮件'''
    rc = Rc.ReadConfig()
    Smtp_Server = rc.getEmailInfo('Smtp_Server')
    sender = rc.getEmailInfo('Smtp_Sender')
    
    pwd = rc.getEmailInfo('Password')
    receiver = rc.getEmailInfo('Pre_Receiver')
    Msg_Title = rc.getEmailInfo('Msg_Title')
    Text_description = rc.getEmailInfo('Text_description')

    Receiver = []
    for receiver in receiver.split(','):
        Receiver.append(receiver)
    _user = sender
    _pwd = pwd
    _to = Receiver
    print(_to)
    # 如名字所示Multipart就是分多个部分
    msg = MIMEMultipart()
    msg["Subject"] = Msg_Title
    msg["From"] = _user
    msg["To"] = ",".join(_to)
    print(msg['To'])

    # ---这是附件部分---

    currentPath = os.getcwd()
    sqldataFile = os.path.join(currentPath, 'sqlDataFiles')

    targetPath = os.path.join(sqldataFile, fileName)
    # 获取目标目录下的邮件附件
    resultFileList = os.listdir(targetPath)

    #  发送多个附件的邮件，这里发送指定目录下所有类型一致的文件
    for resultFileName in resultFileList:
        #  获取结果文件的绝对路径
        resultFilePath = os.path.join(targetPath, resultFileName)

        # ---这是文字部分---

        # 邮件正文内容

        # 打开文件
        # filepath设置详细的文件地址
        filepath = resultFilePath
        # 打开结果exel文件
        wb = load_workbook(filepath)
        # 获取excel sheet名称
        sheets = wb.sheetnames
        # 定义一个存放读取结果的dict
        htmlData = {}

        for sheet in sheets:
            # 调用excel转化html 函数
            content = mailWrite(filepath,sheet)
            # 根据sheet页存储各个sheet的内容
            htmlData[sheet] = content

        contents = ''
        #  遍历取出各个sheet的内容
        for ct in htmlData.keys():
            contents += (ct + ':\n' + htmlData[ct])
        # 设置邮件正文
        Text_description = Text_description + contents
        html = MIMEText(Text_description, 'html', 'utf-8')
        msg.attach(html)

        #  设置邮件附件
        with open(resultFilePath, 'rb') as f:
            part = MIMEApplication(f.read())
            part.add_header('Content-Disposition', 'attachment', filename=resultFileName)
            msg.attach(part)

    s = smtplib.SMTP(Smtp_Server, 25)  # 连接smtp邮件服务器,端口默认是25
    s.login(_user, _pwd)  # 登陆服务器
    s.sendmail(_user, _to, msg.as_string())  # 发送邮件
    s.close()




# 设置初始值
i =46

def pgQuery(text):

    
    global i
    # 设置文件名
    weekFileName = 'W' + str(i)
    #  执行pg 查询，传递文件名，告知执行哪个文件下的sql
    sqlquery = Pq.PgSqlQuery(weekFileName)
    sqlquery.readSql()

    t = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    #  因为每周执行一次，每周执行目录不同，所以这里累加1，到下一个目录下
    i = i+ 1
    #  发送邮件
    sendEmail(weekFileName)
    print('{} --- {}'.format(text, t))





def pgQueryTask(text):
    ''' 定时任务，保证当前headers 持续有效'''
    currentPath = os.getcwd()

    rc = Rc.ReadConfig()
    payload = rc.getPgSQLInfo('payload')
    payload = json.loads(payload)
    url = rc.getPgSQLInfo('url')
    headers = rc.getPgSQLInfo('headers')
    headers = json.loads(headers)

    sqlPath = os.path.join(currentPath,'sql.txt')


    response = requests.request("POST", url, data=payload, headers=headers)

    print(response)
    print(response.json())
    t = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    print('{} --- {}'.format(text, t))


scheduler = BlockingScheduler()

# 设置每周5 上午 10：30执行一次
scheduler.add_job(pgQuery, 'cron', day_of_week=2, hour=16,minute=32, args=['pgQuery'])
scheduler.add_job(pgQueryTask, 'interval', hours = 1,minutes = 56, args=['pgQueryTask'])
scheduler.start()