#!/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 = "邮件提醒： 请注意如下{}即将到期，请确认是否续期，如已处理可忽略此封邮件，如过期将予以清除".format(Name) + " " + content + " (此邮件由系统自动发送,请勿回复)" # 邮件内容 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如下：
