【2022.05.19】根据excel内容变化发送文件
前言
根据一个excel（xlsx）文件中的某一个格子颜色发生变化，发送定制的信息的邮件
奇怪的需求XD
因为那个excel的内容是实时变化的
最后的结果如下
代码
#!/usr/bin/python
# -*- coding: UTF-8 -*-
# 调用SMTP服务器
import smtplib
from email.mime.text import MIMEText
from email.header import Header
# 正则表达式
import re
# 用于读取目录下的所有文件
import os
# 用于读取颜色
import openpyxl
import datetime
import time
import json
# 全局变量
with open('config.json', 'r', encoding='utf-8') as f:
JsonFile = json.load(f)
ColorSheetName = JsonFile['ColorSheetName']
# ColorRow = JsonFile['ColorRow']
# ColorColumn = JsonFile['ColorColumn']
ColorCell = JsonFile['ColorCell']
Delay = JsonFile['Delay']
# SMTP 服务
mail_host = JsonFile['mail_host'] # 设置服务器
mail_user = JsonFile['mail_user'] # 用户名
mail_pass = JsonFile['mail_pass'] # 口令
sender = JsonFile['sender']
# 收件邮箱列表
receivers = JsonFile['receivers'] # 接收邮件列表，可设置为你的QQ邮箱或者其他邮箱
# 邮件主题
SendSubject = JsonFile['SendSubject']
# 发送内容
SendContext = JsonFile['SendContext']
# 发件人名称
SendHeaderSender = JsonFile['SendHeaderSender']
# 收件人名称
SendHeaderReceiver = JsonFile['SendHeaderReceiver']
# 数据定制
DataSheet = JsonFile['DataSheet']
DataName = JsonFile['DataName']
DataRecord = JsonFile['DataRecord']
# 邮箱服务
def send_email(SendCon):
message = MIMEText(SendCon, 'plain', 'utf-8')
message['From'] = Header(SendHeaderSender, 'utf-8')
message['To'] = Header(SendHeaderReceiver, 'utf-8')
message['Subject'] = Header(SendSubject, 'utf-8')
try:
smtpObj = smtplib.SMTP()
smtpObj.connect(mail_host, 25) # 25 为 SMTP 端口号
smtpObj.login(mail_user, mail_pass)
smtpObj.sendmail(sender, receivers, message.as_string())
print("邮件发送成功")
except smtplib.SMTPException:
print("Error: 无法发送邮件")
# 获取当前工作路径和文件名
def get_filename():
path = os.getcwd() # 文件夹目录
# print("当前工作目录为", path)
files = os.listdir(path) # 得到文件夹下的所有文件名称
# print("当前目录下文件有", files) # 打印结果
# 筛选出不带~的文件
list1 = ['~']
newlist = [x for x in files if all(y not in x for y in list1)]
# 筛选出剩余文件中的xlsx文件
r = re.compile(".*xlsx")
newlist = list(filter(r.match, newlist))
file = newlist[0]
file = path + '\\' + file
print("当前读取的文件是 ", file)
return file
# 邮件内容处理
def build_context():
filename = get_filename()
result = SendContext + '\n'
# print(result)
for i in range(len(DataSheet)):
workbooks = openpyxl.load_workbook(filename)
worksheets = workbooks[DataSheet[i]]
result = result + worksheets[DataName[i]].value + "的当前值为" + worksheets[DataRecord[i]].value + "\n"
# print(result)
result = result + str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
print(result)
return result
if __name__ == '__main__':
file = get_filename()
while True:
workbook = openpyxl.load_workbook(file)
worksheet = workbook[ColorSheetName]
color = str(worksheet[ColorCell].fill.fgColor.rgb)
if color != "00000000":
print(str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')) + "监测到变色，发送邮件")
text = build_context()
send_email(text)
break
else:
print(str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')) + " 无变化")
time.sleep(Delay)
os.system("Pause")
配置
{
"？ColorSheetName": "要检测的颜色变化格子所在表名",
"ColorSheetName": "Sheet2",
"？ColorCell": "监测颜色变化所在位置（若合并以左上角为准）",
"ColorCell": "A1",
"？Delay": "监测延时，单位秒",
"Delay": 2,
"？receivers": "收件人列表，最后一个人不用加逗号，加逗号为英语逗号",
"receivers": [
"",
""
],
"？mail": "以下为邮件内容",
"SendSubject": "监测到颜色发生变化",
"SendContext": "这里是发送内容",
"SendHeaderSender": "邮件提醒",
"SendHeaderReceiver": "收件人",
"？smtp服务器配置": "不会的话可以不用修改",
"mail_host": "",
"mail_user": "",
"mail_pass": "VVSRGCETMIATYQEN",
"sender": "",
"？Data": "数据定制",
"DataSheet": [
"Sheet2",
"Sheet2"
],
"DataName": [
"A1",
"A2"
],
"DataRecord": [
"B1",
"B2"
]
}
参考链接
(22条消息) 如何在Python中过滤字符串列表_深蓝旭的博客-CSDN博客_python过滤字符串
python 根据excel中颜色区分读取的操作_Python_脚本之家 (zzvips.com)
