day08-代码操作xls文件

day08-代码操作xls文件

01代码发送学生成绩

1. 准备数据

# 导入openpyxl库
import openpyxl

# 导入smtplib库
import smtplib

from email.mime.multipart import MIMEMultipart
from email.header import Header
from email.mime.text import MIMEText

# 1) 获取excel文件中两张表中的数据
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)

# 2) 对数据按照有联系方式的学生的名字对前面那张表中的数据进行筛选
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