day08-代码操作xls文件
01代码发送学生成绩
1. 准备数据
import openpyxl
import smtplib
from email.mime.multipart import MIMEMultipart
from email.header import Header
from email.mime.text import MIMEText
wb = openpyxl.open('xxx.xlsx')
stu_sheet = wb['学生信息']
con_sheet = wb['学生联系方式']
stu_info = []
m_r1, m_c1 = stu_sheet.max_row, stu_sheet.max_column
for row in range(2, m_r1 + 1):
stu = {
}
for col in range(1, m_c1 + 1):
stu[stu_sheet.cell(1, col).value] = stu_sheet.cell(row.col).value
stu_info.append(stu)
connect_info = []
m_r2, m_c2 = con_sheet.max_row, con_sheet.max_column
all_con = []
for row in range(2, m_r2 + 1):
con = {
}
name = con_sheet.cell(row, 1).value
address = con_sheet.cell(row, 4).value
con['name'] = address
for stu in stu_info:
if value['姓名'] == name:
con['msg'] = '%s同学你好, 以下是你本次期末考试各科成绩: \n%s' % (name, stu)
all_con.append(con)
2. 发邮件
connect = smtplib.SMTP_SSL('smtp.qq.com', 465)
connect.login('发件人邮箱账号', '授权码')
for con in all_con:
address = con['email']
message = con['msg']
email = MIMEMultipart()
email['Subject'] = Header('考试成绩', 'utf-8').encode()
email['From'] = 'xxx <发件人邮箱账号>'
email['To'] = address
msg = MIMEText(message