1. 系统主要功能
- 读取Excel文件的内容,判定其中的责任人,责任时间和完成情况,将完成情况on going和责任时间临期的项目的责任人筛选出来
- 对读取到的责任人列表进行加工,去除重复项,如果责任人有多个的且是/分割开的,进行分割,
- 对加工好的责任人列表,在Department表中查询他们的邮箱和Position里,并生成Email——list
- 通过Email_list逐个给其发送邮件,邮件中需包含待办事项清单
- 如果系统中存在问题,需要向管理员发送告警邮件
- 需要包含日志功能,将系统运行信息记录到logging.txt文件中
- 所有任务通过Email_Tracing_task查询
- 每一个Tracing_task都是一个独立线程,线程失败,不影响其他的跟踪
2. 系统难点
- pandas日期比较,偏差日期之后,可以用大小于号比较,不能日期加减之后与整数对比
delta = datetime.timedelta(days=1)
try:
pd_loc1 = pd_excel.loc[(pd_excel["完成情况"] == "on going") &
(datetime.datetime.now()+delta > pd_excel['责任时间'])]
- 读取Excel文件时有些文件是加密的有些没有加密,需要判断文件是否加密,否则未加密的文件用加密的方法打开会报错。
with open(path, 'rb') as f:
if not msoffcrypto.olefile.isOleFile(f):
pd_excel = pd.read_excel(f, sheet_name, header=header)
logging.info("{title}文件读取成功".format(title=title))
else:
xlf = msoffcrypto.OfficeFile(f)
xlf.load_key(password=password)
xlf.decrypt(decrypted)
pd_excel = pd.read_excel(decrypted, sheet_name, header=header)
logging.info("{title}文件读取成功".format(title=title))
- 加密Excel文件的读取用msoffcrypto模块打开
with open(path, 'rb') as f:
xlf = msoffcrypto.OfficeFile(f)
xlf.load_key(password=password)
xlf.decrypt(decrypted)
pd_excel = pd.read_excel(decrypted, sheet_name, header=header)
logging.info("{title}文件读取成功".format(title=title))
- 通过pandas读取Excel文件的内容并导入到数据库中,其中的日期需要特别处理
pd_DOM['实际完成时间'] = pd.to_datetime(pd_DOM['实际完成时间'])
- pandas 读取xlsx文件的话需要xlrd包的支持,这个而且这个包必须是1.2的版本,2.0以上的版本不支持xlsx文件读取。
pip install xlrd==1.2.0
- 获取责任人数据的清理,ALL要查询所有经理,还有就是一个事件有多个责任人
x = receiver[0].split("/",3) # 通过split函数将责任人分割成一个列表
receive_list.extend(x) # 将分割后的列表与筛选列表进行合并
- 邮件发送时获取待办事项列表,可以通过pandas读取列表,并通过pandas的to_html将读取的数据直接转换成html格式数据表
data_list = pd.read_sql(sql_search,conn) # 通过pandas读取sql数据库信息
df_html = data_list.to_html(escape=False) # 通过pandas的to_html函数将查询结果转换成html表格。
- 日志文件的处理,通过导入logging包。然后在关键节点使用logging.info 或者logging.critical logging.debug等级别的消息。
logging的日志文件在pyinstaller之后,运行exe文件的时候,会在exe本地目录下自动生成一个logging.txt的日志文件。如果是加入到计划任务,执行之后的日志文件会在C:\windows\system32文件夹下。
logging.basicConfig(format='%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s',
level=logging.DEBUG, filename='logging.txt')
- 在将程序打包成exe文件之后,在本地运行正常,在添加到计划任务之后,日志文件就提示无法打开Excel文件。改进方法:
将访问Excel文件的访问路径变更为由Q:\ 变更为 \\172.24.2.*\*\meeting-files\....
3. 系统源代码
import pandas as pd
import xlrd
import logging
from email.mime.text import MIMEText
import smtplib
import datetime
import pymssql
import threading
import msoffcrypto
import io
def read_excel(path, sheet_name, title, header=0, password=''):
decrypted = io.BytesIO()
try:
with open(path, 'rb') as f:
if not msoffcrypto.olefile.isOleFile(f):
pd_excel = pd.read_excel(f, sheet_name, header=header)
logging.info("{title}文件读取成功".format(title=title))
else:
xlf = msoffcrypto.OfficeFile(f)
xlf.load_key(password=password)
xlf.decrypt(decrypted)
pd_excel = pd.read_excel(decrypted, sheet_name, header=header)
logging.info("{title}文件读取成功".format(title=title))
except Exception as e:
error_msg = "Excel文件{title}打开失败,请检查文件路径及权限。".format(title=title)
logging.critical(error_msg)
logging.critical(e)
send_waring_mail(error_msg, title)
return
pd.set_option("display.max_columns", None)
pd_excel['责任时间'] = pd.to_datetime(pd_excel['责任时间'])
return pd_excel
def general_user_list(pd_excel, title):
delta = datetime.timedelta(days=1)
try:
pd_loc1 = pd_excel.loc[(pd_excel["完成情况"] == "on going") &
(datetime.datetime.now()+delta > pd_excel['责任时间'])]
send_owners = pd_loc1['责任人'].drop_duplicates()
except Exception as e:
error_msg = "{}对Excel的‘责任人’查询失败".format(title)
logging.info(error_msg)
logging.info(title, ' ', e)
return
receive_list_user = []
for receiver in send_owners:
if receiver.find('/'):
x = receiver.split("/", 3)
receive_list_user.extend(x)
else:
receive_list_user.append(receiver)
receive_list_user = list(set(receive_list_user))
print('user_list', receive_list_user)
logging.info('{title}要发送的用户列表{user_list}'.format(title=title, user_list=receive_list_user))
return receive_list_user
def general_email_list(receive_list, title):
if not receive_list:
return
server_name = '172.24.2.*'
username = 'sa'
password = '****@2022'
try:
conn = pymssql.connect(server_name, username, password, "FineReport")
except Exception as e:
error_msg = "数据库连接失败,请检查数据库连接"
logging.critical(error_msg)
logging.critical(e)
send_waring_mail(error_msg, title)
return
cursor = conn.cursor()
print(receive_list)
email_list = []
for user in receive_list:
sql_mail = "select email,Position from [FineReport].[dbo].[Department] where username='{}'".format(user)
cursor.execute(sql_mail)
email = cursor.fetchone()
if not email:
error_msg = "用户{}的邮箱,请维护邮箱表".format(user)
logging.warning("用户{}的邮箱,请维护邮箱表".format(user))
send_waring_mail(error_msg, title)
continue
email_list.append((user, email[0], email[1]), )
logging.info(email_list)
cursor.close()
conn.close()
print('email_list', email_list)
logging.info("{title}需要发送邮件的列表为:{list}".format(title=title, list=email_list))
return email_list
def send_waring_mail(error_msg, title):
smtp = smtplib.SMTP()
try:
smtp.connect('172.26.2.*', 587)
except Exception as e:
logging.critical('邮件服务器连接失败,请检查邮箱服务器和网络!')
logging.critical(e)
return
mail_msg = """
管理员您好:
跟踪系统出现问题,问题消息如下:
{error}
请检查。<br/>
对应excel跟踪文件为:{title}
""".format(error=error_msg, title=title)
msg = MIMEText(mail_msg, 'html', 'utf-8')
msg['Subject'] = "DOM Tracing ERROR message"
msg['From'] = 'CQ_DOM_OIL@XXX.com'
try:
smtp.sendmail('CQ_OIL@XXX.com', '*.*@qq.com', msg.as_string())
logging.info("向管理员发送警告邮件成功。")
except Exception as e:
logging.warning("向管理员发送邮件失败,请检查其邮箱配置!")
logging.warning(e)
def send_mail(email_list, pd_excel, url, title, owner):
smtp = smtplib.SMTP()
try:
smtp.connect('172.26.2.*', 587)
except Exception as e:
logging.critical('邮件服务器连接失败,请检查邮箱服务器和网络!')
logging.critical(e)
for user in email_list:
pd_excel['责任时间'] = pd.to_datetime(pd_excel['责任时间'])
delta = datetime.timedelta(days=1)
try:
pd_loc1 = pd_excel.loc[(pd_excel["完成情况"] == "on going") &
(datetime.datetime.now() + delta > pd_excel['责任时间']) &
(pd_excel['责任人'].str.contains(user[0]))]
df_html = pd_loc1.to_html(escape=False)
except Exception as e:
logging.info("{}查询责任人{}未办事项信息失败!".format(title, user[0]))
logging.info(title, ' ', e)
df_html = ''
mail_msg = """
<p style = "font:bold 14px/1em '微软雅黑'">
{name} {position},您好,<br/>
<span style="color:red">{title}</span> 有您需要跟踪实施的项目即将临期或已经超期,请尽快实施并更新状态!<br/> 谢谢!<br/>
跟踪表路径如下:
{URL}
<p style = "color:Blue;font: bold 14px/1em 'Aril'"></p></p>
<p style = "color:Grey;font: italic 14px/1em 'Aril'">若有问题请联系{owner},谢谢!</p>
具体清单如下:
{df_html}
<h3>系统自动提示邮件,无需回复!</h3>
<hr />
""".format(name=user[0], position=user[2], df_html=df_html, URL=url, title=title, owner=owner)
msg = MIMEText(mail_msg, 'html', 'utf-8')
msg['Subject'] = title
msg['From'] = 'CQ_Daily_Tracing@XXX.com'
try:
smtp.sendmail('CQ_Daily_Tracing@XXX.com', user[1], msg.as_string())
logging.info("{}向用户{}发送邮件成功。".format(title, user))
except Exception as e:
error_msg = "{}向用户{}发送邮件失败,请检查其邮箱配置!".format(title, user)
logging.warning(error_msg)
logging.warning(e)
send_waring_mail(error_msg, title)
def encapsulation_send_mail(path, sheet_name, header, title, password, owner):
pd_excel_encap = read_excel(path=path, sheet_name=sheet_name,
title=title, header=header, password=password)
receive_list_encap = general_user_list(pd_excel=pd_excel_encap, title=title)
email_list_encap = general_email_list(receive_list=receive_list_encap, title=title)
send_mail(email_list=email_list_encap, pd_excel=pd_excel_encap, url=path, title=title, owner=owner)
def read_task():
server_name = '172.24.2.*'
username = 'sa'
password = 'XXX@2022'
try:
conn = pymssql.connect(server_name, username, password, "FineReport")
except Exception as e:
error_msg = "数据库连接失败,请检查数据库连接"
logging.critical(error_msg)
logging.critical(e)
send_waring_mail(error_msg, title="hello")
return
finally:
cursor = conn.cursor()
sql = "select * from dbo.[Email_Tracing_task]"
cursor.execute(sql)
task_list = cursor.fetchall()
for task in task_list:
print(task)
title = task[0]
path = task[1]
sheet_name = task[2]
header = task[3]
password = task[4]
owner = task[5]
threading.Thread(target=encapsulation_send_mail,
args=(path, sheet_name, header, title, password, owner)).start()
cursor.close()
conn.close()
if __name__ == '__main__':
logging.basicConfig(format='%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s',
level=logging.DEBUG, filename='logging.txt')
read_task()