报表定时2 邮箱邮件截图功能实现

报表定时2 邮箱邮件截图功能实现
进行此步之前需完成前面两篇博文的功能，将报表每天定时发送至邮箱

注意：因为截图功能涉及模拟真人打开excel并截图，需要用到win32包，所以该任务只能部署在windows服务器下，或自己本地一直开机的电脑。
下面上代码
运行代码前要在代码同级目录新建好image文件夹存储截图，result文件夹存储从邮箱上下载的邮件。这里为wxlimage和wxlresult
整体流程为，从邮箱的未读邮件中找到邮件，下载附件到本地，本地截图，发送截图和带有附件的邮件

代码自动运行使用的windows自带的定时任务，直接在开始栏搜索框搜索任务自动程序即可

#!/usr/bin/python
# -*- coding: utf-8 -*-
import os
import re
import email
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.image import MIMEImage
from email.mime.application import MIMEApplication
from email.mime.base import MIMEBase
from email.header import Header
from email import encoders
import chardet
import imaplib
import getpass
import struct
import smtplib
import time
from datetime import datetime,timedelta
import win32com.client as win32 # 打开excel文件
from PIL import ImageGrab   #用于获取复制的图片
from PIL import Image
import boto.s3.connection
import requests
import hmac
import hashlib
import base64
import urllib.parse
import urllib.request
import json


# In[45]:


def receive_email(host, user_name, password, path='./', port='993', domain='Unseen', decoding='GBK', att_name='all'):
    '''
    host:服务器名
    user_name:用户名
    password:密码,有些服务器需要用授权码
    path:附件保存的路径
    domain:查看邮件的范围, 默认的unseen为未读邮件
    decoding:解码的语言,默认GBK为了正常下载EXCEL
    '''
    if host is None or user_name is None or password is None:
        print("未输入合法的服务器域名或者用户名或者密码")
    count = 0
    while count <= 5:
        try:
            m = imaplib.IMAP4_SSL(host, port)
            m.login(user_name, password)
            break
        except:
            count += 1
            print('登陆邮箱失败{}次'.format(count))
            time.sleep(600)
    if count != 6:
        m.select('INBOX')

        unseen = m.search(None, domain)
        unseen_list = unseen[1][0].split()
        file_name_content_dict = {}
        email_content = None
        for num in unseen_list:
            typ, data = m.fetch(num, 'RFC822')
            try:
                content = data[0][1].decode(decoding)
            except UnicodeDecodeError:
                content = data[0][1].decode('utf-8')
            except TypeError:
                continue
            msg = email.message_from_string(content)
            for part in msg.walk():
                if part.is_multipart():
                    continue
                if part.get('Content-Disposition') is None:
                    try:
                        email_content = part.get_payload(decode=True).decode('utf-8')
                        continue
                    except UnicodeDecodeError:
                        email_content = ''
                        continue
                file_data = part.get_payload(decode=True)
                filename = str(part.get_filename())
                dename = email.header.decode_header(filename)[0]

                name = dename[0]
                if dename[1] != None:
                    name = str(dename[0], dename[1])
                    file_name_content_dict[name] = [num, email_content]
                else:
                    file_name_content_dict[name] = [num, email_content] 
                if att_name == name or att_name == 'all':
                    if not os.path.exists(path):
                        os.makedirs(path)
                    att_path = os.path.join(path, name)

                    if os.path.isfile(att_path):
                        os.remove(att_path)
                    with open(att_path, mode='wb') as fb:
                        fb.write(file_data)
                else:
                    continue
        m.close()
        m.logout()
        return file_name_content_dict


# In[46]:


def save_pic(excel_path, sheet_dict, output_path='./wxlimage/'):
    """
    excel_path: 需要excel的绝对路径
    sheet_dict: 选择截图的工作表和范围
    output_path: 默认路径为 ./wxlimage/
    """
    today = datetime.now().strftime('%Y%m%d')
    save_dir = '{}{}'.format(output_path,today)
    image_list =[]
    if not os.path.exists(save_dir):
        os.makedirs(save_dir)
    attempt = 0
    for sheet_name, sheet_range in sheet_dict.items():
    #if ws.name in sheet_dict.keys():
        while True:
            try:
                time.sleep(2)
                excel = win32.Dispatch('Excel.Application') #获取Excel
                excel.Visible = 0 #后台运行, 需要重启系统后生效
                excel.DisplayAlerts = 0
                wb = excel.Workbooks.Open(excel_path) # 打开excel文件,需要绝对路径
                ws = wb.Worksheets(sheet_name)        # 获取Sheet
                ws.select
                ws.Range(sheet_range).select
                ws.Range(sheet_range).CopyPicture()    # 复制图片区域
                time.sleep(0.5)
                ws.Paste(ws.Range('T2'))    # 将图片移动到T1
                time.sleep(0.5)
                new_shape_name = 'push'
                time.sleep(0.5)
                excel.Selection.ShapeRange.height /=  1.17210372229193    # 调整图片大小，去粗边框
                excel.Selection.ShapeRange.Name = new_shape_name    # 选择区域重命名
                time.sleep(0.5)
                ws.Shapes(new_shape_name).Copy()    # 复制移动的图片Picture
                time.sleep(0.5)
                img = ImageGrab.grabclipboard()  # 获取图片数据
                time.sleep(0.5)
                img.save('{}/{}.png'.format(save_dir,sheet_name)) # 图片另存为
                time.sleep(0.5)
                print('{}/{}.png is saved'.format(save_dir,sheet_name))                
                image_list.append('{}.png'.format(sheet_name))
                wb.Close()
                excel.Quit()
                break
            except:
                attempt += 1
                #激活当前excel，如何没有激活，无动作，若激活成功，则关闭Excel。避免锁定Excel，避免因无法打开新的Excel，使任务失败。
                try:
                    excel = win32.Dispatch('Excel.Application')
                    bookCount = excel.Workbooks.Count
                    if bookCount > 0:
                        for i in range(1, bookCount + 1):
                            excel.Workbooks(i).Close()
                    excel.Quit()
                except:
                    print('Excel无法呼叫,第{}次尝试'.format(attempt))
                    continue
                print('noBookCountError第{}次尝试'.format(attempt))
                if attempt > 10:
                    break
                continue
        
    return image_list
#wb.SaveAs('{}/{}_pic.xlsx'.format(path_dir, sheet_name)) # excel文件另存为copy.xlsx


# In[47]:


def send_mail(send_host, user_name, password, receiver, header, excel_path='',image_list='', txt='', image_path='./wxlimage/', port=465, attachment=1):
    today = datetime.now().strftime('%Y%m%d')
    #创建邮件对象，相当于信纸
    msg = MIMEMultipart()
    #邮件发送方
    msg['From'] = user_name
    #邮件接收方
    msg['To'] = ','.join(receiver)
    #邮件主题
    msg['Subject'] = Header('{}{}'.format(header, today), charset='UTF-8')#中文主题
    
    if excel_path != '' and attachment == 1:
        #加入excel附件
        part=MIMEApplication(open(excel_path,'rb').read())
        excel_name = os.path.basename(excel_path)
        part["Content-Type"] = 'application/octet-stream'
        part.add_header('Content-disposition','attachment',filename=('GBK','','{}'.format(excel_name)))
        msg.attach(part)

    if image_list != '':
        content = '<span style="font-size: 12px;"><div>{}<br>----------------------------------------</div></span>'.format(txt) #展示邮件中的文本内容，例如版本跌代、大事件等重要的信息
        for image_name in image_list:
            image_name = image_name.split('.')[0]
            #超文本内容，放有图片，注意src路径,此为放在同一目录中
            content += '<span style="font-size: 21px;"><b>{}</b></span><div><img src="cid:{}"></div>'.format(image_name, image_name.encode('utf-8'))
            #一定要设置邮件格式，否则可能会乱码，当时接手别人手中没加邮件格式的项目，坑了我半天
            #图片放到邮件中
            fp = open('{}{}.png'.format(image_path, image_name), 'rb')
            msgImage = MIMEImage(fp.read(), _subtype='png')
            fp.close()
            # 定义图片 ID，在 HTML 文本中引用
            msgImage.add_header('Content-ID', '<{}>'.format(image_name.encode('utf-8')))
            msgImage.add_header('Content-disposition','attachment',filename=('GBK','','{}'.format(image_name)))
            msg.attach(msgImage)
        part2 = MIMEText(content, 'html', 'utf-8')
        #添加到邮件中
        msg.attach(part2)

    #加入文本附件
#     att2 = MIMEText('这是附件里的文字', 'base64', 'utf-8')
#     att2["Content-Type"] = 'application/octet-stream'
#     #设置附件名为runoob.txt
#     att2["Content-Disposition"] = 'attachment; filename="01_basedata_v1.2.1.sql"'
#     #附件放在邮件中
#     msg.attach(att2)
    #非ssl方式的smtp(简单邮件发送协议),下面注释为ssl方式的smtp
    #smtp = smtplib.SMTP() 
    #smtp = smtplib.SMTP_SSL('smtp.exmail.qq.com',port=465) 
    count = 0
    while count <= 5:
        try: 
            #连接主机地址 
            smtp = smtplib.SMTP_SSL(send_host,port) 
            #smtp.connect('smtp.exmail.qq.com',port=465)
            #登录邮箱，需要邮箱账号，密码
            smtp.login(user_name, password) 
            #发送邮件，需要登录的邮箱号，发送方的邮箱号，发送内容 
            smtp.sendmail(user_name, receiver, msg.as_string())
            #退出邮箱 
            smtp.quit() 
            print('{} is sended successly.'.format(header))
            count = 999
        except:
            time.sleep(2)
            print('发送邮件等待{}次'.format(count))
            count += 1
            if count == 6:
                print ("{}无法发送邮件".format(header))
            continue


# In[48]:


def sendmail_product(header, receiver_all, sheet_dict, receiver_one=['邮箱'], txt='',test=1, wait_times=0, stop_wait_times=12, today=''
                     , receive_host = 'imap.exmail.qq.com',  send_host = 'smtp.exmail.qq.com', mailUser = '邮箱', mailPassWord = '授权码', yesterday='', attachment=1):
    if today == '':
        today = datetime.now().strftime('%Y%m%d')
    path = '{}/'.format(os.getcwd())
    excel_dir = "{}/wxlresult/".format(path)
    if today != '':
        image_dir = '{}/wxlimage/{}/'.format(path, datetime.now().strftime('%Y%m%d'))
    target_file = '{}{}.xlsx'.format(header,today)
    receive_result = receive_email(receive_host, user_name=mailUser, password=mailPassWord, path=excel_dir, att_name=target_file)
    att_list = list(receive_result.keys())
    if target_file in att_list:
        excel_path = "{}/wxlresult/{}".format(path, target_file)
    elif wait_times == stop_wait_times:
        print('未从邮箱中找到{}'.format(target_file))
        send_mail(receive_host, mailUser, mailPassWord, receiver_one, header='{}截图转发失败'.format(header), txt='未从邮箱中找到{}'.format(header))
        return 0
    else:
        return 0
    image_list = save_pic(excel_path, sheet_dict)
    if txt == '':
        receive_content = re.sub('\\n', '', receive_result[target_file][1])#删除文本内容中多余的换行符
    #是否测试
    if test == 1:
        #是否转发文本内容
        if txt != '':
            send_mail(send_host=send_host, user_name=mailUser, password=mailPassWord, receiver=receiver_one, 
                      header=header,excel_path=excel_path, image_path=image_dir, image_list=image_list, txt=txt, attachment=attachment)
        else:
            send_mail(send_host=send_host, user_name=mailUser, password=mailPassWord, receiver=receiver_one, 
                      header=header,excel_path=excel_path, image_path=image_dir, image_list=image_list, txt=receive_content, attachment=attachment)
    else:
        if txt != '':
            send_mail(send_host=send_host, user_name=mailUser, password=mailPassWord, receiver=receiver_all, 
                      header=header,excel_path=excel_path, image_path=image_dir, image_list=image_list, txt=txt, attachment=attachment)
        else:
            send_mail(send_host=send_host, user_name=mailUser, password=mailPassWord, receiver=receiver_all, 
                      header=header,excel_path=excel_path, image_path=image_dir, image_list=image_list, txt=receive_content, attachment=attachment)
    return 1


# In[49]:


def main():
    today = datetime.now().strftime('%Y%m%d') 
    test = 0 #是否是测试
    resend = 0 #补发邮件
    if test == 1 or resend == 1:
        mail_num = 1 #每日需要转发的邮件数
        wait_times = -1 #记录等待的次数,用于超时停止循环
        stop_wait_times = 0 #限定等待的次数http://localhost:8888/notebooks/send_mail_PH.ipynb#
        #选择要转发的报表
        ph_email = 1 

    else:
        mail_num = 1 #每日需要转发的邮件数
        wait_times = -1 #记录等待的次数,用于超时停止循环
        stop_wait_times = 0 #限定等待的次数
        ph_email = 1 
    
    while mail_num > 0 and wait_times < stop_wait_times:
        wait_times += 1
        print('等待{}次'.format(wait_times))
        if ph_email > 0:
            header = 'KP无多头_有多头模型监控'
            txt = ' '
            receiver_one = ['邮箱']
            receiver_all = ['邮箱','邮箱']
            #
            sheet_dict = {'sheet名':'A1:N20（截图区域）'}
            result = sendmail_product(header, receiver_one, sheet_dict, receiver_one=receiver_one,txt=txt, test=test, wait_times=wait_times, stop_wait_times=stop_wait_times
                                     , receive_host = 'imap.exmail.qq.com' ,  send_host = 'smtp.exmail.qq.com', mailUser = '邮箱', mailPassWord = '授权码')
            ph_email = ph_email - result
            mail_num = mail_num - result
   
                
        if mail_num > 0 and test != 1 and resend != 1:
            print('开始等待')
            time.sleep(300)


# In[50]:


if __name__ == "__main__":
   main()
   print('截图转发结束')

新建一个脚本文件
内容输入 python -u “.\上面代码文件名字.py” >log.txt 2>&1&
log.txt存储代码运行print的东西可以查看日志

后面比较简单就不贴了