对比两个excel文件的内容,将文件2中的人员在文件1中的各科成绩发送给他们
import os
import openpyxl
import os
import smtplib
from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart
if os.path.exists('file/python数据分析.xlsx'):
wb = openpyxl.load_workbook('file/python数据分析.xlsx')
else:
wb = openpyxl.Workbook
if os.path.exists('file/python数据分析2.xlsx'):
wb2 = openpyxl.load_workbook('file/python数据分析2.xlsx')
else:
wb2 = openpyxl.Workbook()
if '学生信息' not in wb.sheetnames:
sheet = wb.create_sheet('学生信息')
else:
sheet = wb['学生信息']
if '学生信息' not in wb2.sheetnames:
sheet2 = wb2.create_sheet('学生信息')
else:
sheet2 = wb2['学生信息']
data = {}
data2 = {}
data_all = []
for i in range(2,sheet2.max_row+1):
for j in range(2,sheet.max_row+1):
if sheet.cell(j, 1).value == sheet2.cell(i, 1).value:
data['英语'] = sheet.cell(j,2).value
data['办公软件操作'] =sheet.cell(j,3).value
data['电子商务'] = sheet.cell(j,4).value
data['计算机基础'] = sheet.cell(j,5).value
data['姓名'] = sheet2.cell(i,1).value
data['email'] = sheet2.cell(i,4).value
data['gender'] = sheet2.cell(i,2).value
data2 = data.copy()
data_all.append(data2)
wb.save('file/python数据分析.xlsx')
wb2.save('file/python数据分析2.xlsx')
connect = smtplib.SMTP_SSL('smtp.qq.com',465)
connect.login('31*******@qq.com','i********gc')
for _ in data_all:
email = MIMEMultipart()
email['Subject'] = Header('day07作业,发送成绩', 'utf-8').encode()
email['From'] = 'L**<3******36@qq.com>'
email['T0'] = '%s' % (_['email'])
if _['gender'] == '男':
gender = '先生'
else:
gender = '女士'
msg = MIMEText('%s%s,你的成绩是:英语 %s, 办公软件操作 %s, 电子商务 %s, 计算机基础 %s' % (_['姓名'][0],gender,_['英语'], _['办公软件操作'], _['电子商务'], _['计算机基础']), 'plain')
email.attach(msg)
connect.sendmail('31*******6@qq.com', '%s' % (_['email']), email.as_string())
connect.close()