[toc]
# 1. 系统主要功能
1. 读取Excel文件的内容，判定其中的责任人，责任时间和完成情况，将完成情况on going和责任时间临期的项目的责任人筛选出来
2. 对读取到的责任人列表进行加工，去除重复项，如果责任人有多个的且是/分割开的，进行分割，
3. 对加工好的责任人列表，在Department表中查询他们的邮箱和Position里，并生成Email——list
4. 通过Email_list逐个给其发送邮件，邮件中需包含待办事项清单
5. 如果系统中存在问题，需要向管理员发送告警邮件
6. 需要包含日志功能，将系统运行信息记录到logging.txt文件中
7. 所有任务通过Email_Tracing_task查询
8. 每一个Tracing_task都是一个独立线程，线程失败，不影响其他的跟踪
# 2. 系统难点
1. pandas日期比较，偏差日期之后，可以用大小于号比较，不能日期加减之后与整数对比
2. 读取Excel文件时有些文件是加密的有些没有加密，需要判断文件是否加密，否则未加密的文件用加密的方法打开会报错。
3. 加密Excel文件的读取用msoffcrypto模块打开
# 3. 系统源代码
```python
#! /user/bin/python
# author Gang ZHou
# date 2022-05-25
# version 2.0
# 项目功能
# 1. 读取Excel文件的内容，判定其中的责任人，责任时间和完成情况，将完成情况on going和责任时间临期的项目的责任人筛选出来
# 2. 对读取到的责任人列表进行加工，去除重复项，如果责任人有多个的且是/分割开的，进行分割，
# 3. 对加工好的责任人列表，在Department表中查询他们的邮箱和Position里，并生成Email——list
# 4. 通过Email_list逐个给其发送邮件，邮件中需包含待办事项清单
# 5. 如果系统中存在问题，需要向管理员发送告警邮件
# 6. 需要包含日志功能，将系统运行信息记录到logging.txt文件中
# 7. 所有任务通过Email_Tracing_task查询
# 8. 每一个Tracing_task都是一个独立线程，线程失败，不影响其他的跟踪
import pandas as pd
import xlrd # pandas read_excel默认引用
import logging # 日志文件
from email.mime.text import MIMEText # 邮件格式
import smtplib # 发送邮件
import datetime
import pymssql # 读取数据库中的邮箱和任务列表
import threading # 多线程
import msoffcrypto # 读取加密的Excel文件
import io # 将文件写入到内存中
# 定义一个函数，用来导入Excel文件，并将获取的dataFrame数据结构返回
def read_excel(path, sheet_name, title, header=0, password=''):
decrypted = io.BytesIO() # 用来后来将读取到的Excel文件解密之后写入内存中。
try:
with open(path, 'rb') as f: # 用上下文将文件打开
# 判断Excel文件是否加密，未加密直接pandas读取，加密的就先解密到内存中然后pandas读取。
if not msoffcrypto.olefile.isOleFile(f): # mscoffcrypto有可以判断文件是否加密的功能。
pd_excel = pd.read_excel(f, sheet_name, header=header)
logging.info("{title}文件读取成功".format(title=title))
else:
xlf = msoffcrypto.OfficeFile(f) # 用msoffcrypto打开加密文件，未加密文件用这个打开会报错
xlf.load_key(password=password) # 传入文件的密码
xlf.decrypt(decrypted) # 将文件解密并保存到内存中，定义descrypted为io.BytesIO() ，这是msoffcrypto常用方法。
pd_excel = pd.read_excel(decrypted, sheet_name, header=header) # 将保存到内存中的文件用pandas打开。
# 通过pandas读取Excel的内容，指定文件路径名称以及要读取的sheet的名称
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) # 将pandas输出设置为显示所有列。在辅助print打印时使用
# 将责任时间的数据格式转换成datetime类型，如此在后期可以参与实践比较
pd_excel['责任时间'] = pd.to_datetime(pd_excel['责任时间'])
return pd_excel
# 定义一个函数，用以查询Excel文件中所有状态为on going 并且责任时间临期或超期的项，并提取出责任人列表
def general_user_list(pd_excel, title):
# pandas的数据结构中必须包含责任人，责任时间，完成情况
# 设置一个时间偏移量，此处偏移量为1.也就是说如果责任时间是2022-05-26， 那么如果今天是2022-05-25及之后的时间，就符合筛选条件
delta = datetime.timedelta(days=1)
# 通过loc[]筛选出完成情况为on going并且当前日期偏差一天之后大于责任时间，& 两边的条件都要用小括号()括起来。
try:
pd_loc1 = pd_excel.loc[(pd_excel["完成情况"] == "on going") &
(datetime.datetime.now()+delta > pd_excel['责任时间'])]
# 将获取到的责任人列表进行去重，并复制给send_owners
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) # 通过split函数将责任人分割成一个列表
receive_list_user.extend(x) # 将分割后的列表与筛选列表进行合并
else: # 否则的话，就将用户直接加入到筛选列表中
receive_list_user.append(receiver)
receive_list_user = list(set(receive_list_user))
user_list = [] #创建列表保存清洗过后的用户信息
for user in receive_list_user:
user =user.strip() # 去除用户列表中的空格，并保存到user_list中
user_list.append(user)
user_list = list(set(user_list)) # 对user_list进行去重
receive_list_user = user_list
print('user_list', receive_list_user)
logging.info('{title}要发送的用户列表{user_list}'.format(title=title, user_list=receive_list_user))
return receive_list_user
# 定义一个函数，通过责任人列表查找对应的邮箱地址和Position
def general_email_list(receive_list, title):
if not receive_list:
return
server_name = '172.24.2.XX'
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)
return
cursor = conn.cursor()
print(receive_list)
email_list = [] # 创建一个列表，用来保存用户名和邮箱的键值对。
for user in receive_list:
# 通过经理姓名查找经理对应的邮箱，并将姓名和邮箱一起保存到email_list中。
sql_mail = "select email,Position from [FineReport].[dbo].[Department] where username='{}'".format(user)
cursor.execute(sql_mail)
email = cursor.fetchone()
if not email: # 如果数据库中未查询到email信息，那么就向管理员发送告警邮件，并生成日志。
error_msg = "用户{}的邮箱，请维护邮箱表".format(user)
logging.warning("用户{}的邮箱，请维护邮箱表".format(user))
send_waring_mail(error_msg, title)
continue
email_list.append((user, email[0], email[1]), ) # 查到数据就加入到email_list列表中
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):
# 创建邮箱连接，采用匿名发送邮件的方法，不需要使用登录名和密码，发送邮箱的名称可以自定义，不需要exchange服务器创建。
smtp = smtplib.SMTP()
try:
smtp.connect('172.26.2.10', 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', 'gang.zhou@XXX.com', msg.as_string())
logging.info("向管理员发送警告邮件成功。")
except Exception as e:
logging.warning("向管理员发送邮件失败，请检查其邮箱配置！")
logging.warning(e)
# 定义一个发送邮件的函数，遍历email_list,向里面的所有邮箱账号单独发送一次邮件。
def send_mail(email_list, pd_excel, url, title, owner):
# 创建邮箱连接，采用匿名发送邮件的方法，不需要使用登录名和密码，发送邮箱的名称可以自定义，不需要exchange服务器创建。
smtp = smtplib.SMTP()
try:
smtp.connect('172.26.2.10', 587)
except Exception as e:
logging.critical('邮件服务器连接失败，请检查邮箱服务器和网络！')
logging.critical(e)
for user in email_list:
# 查找数据列表包含责任人为ALL和责任人中包含该用户的所有临期信息。
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) # 读取Excel文件
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) # 生成邮箱列表
# email_list_encap = [('王波', 'gang.zhou@XXX.com', '工程师'), ] # 测试数据，可删除
send_mail(email_list=email_list_encap, pd_excel=pd_excel_encap, url=path, title=title, owner=owner)
# 定义一个函数，查询数据表[Email_Tracing_task]，并且采用多线程的运行程序。
def read_task():
server_name = '172.24.2.XX'
username = 'sa'
password = 'XXX
最低0.47元/天 解锁文章
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
