根据excel数据过期发送邮件提醒

#!/usr/bin/python3
# @Date: 2020/8/18 9:23
# @Author: zhangcheng
# @email: 3359957053@qq.com
# -*- coding: utf-8 -*-


"""
各部门虚拟机申请到期前，发送邮件提醒，避免遗漏处理
"""

import os
import xlrd
import xlwt
import smtplib
import openpyxl
import datetime
import pythoncom
import uuid
from PIL import ImageGrab,Image
from openpyxl import load_workbook
from win32com.client import Dispatch
from email.mime.text import MIMEText
from email.utils import formataddr


# 发件人邮箱账号
sender = "xxxxx@utry.cn"
# 发件人邮箱的密码（授权码，即客户端密码),腾讯邮箱的登录密码也能登录成功
password = "xxx"
# 抄送给哪些人员
cc_receiver = ["xxxx@qq.com"]

def main():
    # 获取当天的日期
    sNow = datetime.datetime.now().date()
    todayToexcel(sNow)
    just_open(filePath())

    # 打开excel
    wb = xlrd.open_workbook(filename=filePath())
    # 获取sheet的数量
    sheetNumbers = len(wb.sheet_names())
    for j in range(0, sheetNumbers):
        sheet1 = wb.sheet_by_index(j)
        sheet1Name = load_workbook(filename = filePath()).worksheets[j]
        sheet1Name = str(sheet1Name).strip("<Worksheet>")
        # 获取行数
        nrows1 = sheet1.nrows
        for iRow in range(1, nrows1):
            # 获取指定单元格数据
            starttime = dateFormate(sheet1.cell(iRow, 5).value)
            endtime = dateFormate(sheet1.cell(iRow, 6).value)
            reportstart = datetime.datetime.now() + datetime.timedelta(days=7)
            reportstart = str(reportstart.strftime('%Y-%m-%d'))
            reportend = datetime.datetime.now() + datetime.timedelta(days=3)
            reportend = str(reportend.strftime('%Y-%m-%d'))

            if endtime == reportstart or endtime == reportend:
                #从excel中提取收件人邮箱
                to_receiver = [ sheet1.cell(iRow, 4).value ]
                # if sheet1Name == "虚拟机":
                # else:
                s1 = "虚拟机IP地址:" + "".join(str(sheet1.cell(iRow, 0).value).ljust(17))
                s2 = "虚拟机配置:" + "".join(str(sheet1.cell(iRow, 1).value).ljust(17))
                s3 = "归属部门:" + "".join(str(sheet1.cell(iRow, 2).value).ljust(17))
                s4 = "责任人:" + "".join(str(sheet1.cell(iRow, 3).value).ljust(17))
                s5 = "开始时间:" + "".join(str(starttime).ljust(17))
                s6 = "到期时间:" + "".join(str(endtime).ljust(17))
                s7 = "距离到期天数:" + "".join(str(int(sheet1.cell(iRow, 10).value)).ljust(17))
                s = s1 + s2 + s3 + s4 + s5 + s6 + s7
                # 距离到期时间为第三天和第七天发送邮件
                ret = sendEmail(to_receiver,s,sheet1Name)
                if ret:
                    print("邮件发送成功")
                else:
                    print("邮件发送失败")

def sendEmail(to_receiver,content,Name):
    """
    发送邮件
    :param to_receiver: 收件人
    :param content: 添加邮件正文描述
    :param Name: sheetName
    :return:
    """

    # 打开excel
    ret = True
    try:
        mail_body = "邮件提醒：\r\n 请注意如下{}即将到期，请确认是否续期，如已处理可忽略此封邮件，如过期将予以清除".format(Name) + "\r\n" + content + " \r\n (此邮件由系统自动发送,请勿回复)"
        # 邮件内容
        msg = MIMEText(mail_body, 'plain', 'utf-8')
        # 括号里的对应发件人邮箱昵称、发件人邮箱账号
        msg['From'] = sender
        # 括号里的对应收件人邮箱昵称、收件人邮箱账号
        msg['To'] = ";".join(to_receiver)
        # 括号里的对应抄送人邮箱昵称、收件人邮箱账号
        msg['Cc'] = ";".join(cc_receiver)
        # 邮件的主题
        msg['Subject'] = "{}到期提醒".format(Name)
        receiver = to_receiver + cc_receiver
        # SMTP服务器，腾讯企业邮箱端口是465，腾讯邮箱支持SSL(不强制)， 不支持TLS
        server = smtplib.SMTP_SSL("smtp.exmail.qq.com", 465)
        # 登录服务器，括号中对应的是发件人邮箱账号、邮箱密码
        server.login(sender, password)
        # 发送邮件，括号中对应的是发件人邮箱账号、收件人邮箱账号、发送邮件
        server.sendmail(sender, receiver, msg.as_string())
        # 关闭连接
        server.quit()
        # 如果 try 中的语句没有执行，则会执行下面的 ret=False
    except Exception:
        ret = False
    return ret

def filePath():
    """
    获取需要读取的excel文件路径
    :return:
    """
    sPath = os.getcwd() #获取当前路径
    sFile = "测试.xlsx"  #文件名
    sExcelFile = sPath + "\\" + sFile  #拼接完整路径
    return sExcelFile

def todayToexcel(today):
    """
    修改excel中today时间为今天
    :param today: 今天的日期
    :return:
    """
    wb = openpyxl.load_workbook(filePath())
    sheetNumber = len(wb.sheetnames)
    for j in range(0, sheetNumber):
        outSheet = wb.worksheets[j]
        nRows = outSheet.max_row

        for i in range(2, nRows+1):
            outSheet.cell(row=i, column=11).value = today
    wb.save(filePath())

def just_open(filename):
    """
    用于读取excel中的公式结果，解决xlrd读取公式单元格内容为空的问题
    :param filename: excel文件路径
    :return:
    """
    xlApp = Dispatch("Excel.Application")
    xlApp.Visible = False
    xlBook = xlApp.Workbooks.Open(filename)
    xlBook.Save()
    xlBook.Close()

def dateFormate(timestamp):
    """
    格式化excel时间格式
    :param timestamp: 从excel获取的时间格式
    :return:
    """
    delta = datetime.timedelta(days=timestamp)
    timestamp = datetime.datetime.strptime('1899-12-30', '%Y-%m-%d') + delta
    timestamp = timestamp.strftime('%Y-%m-%d')
    return timestamp

if __name__ == '__main__':
    main()

excel如下：