openpyxl及python SMTP使用记录

背景

模拟处理一个学校的疫情调查问卷，将问卷反馈通过不同的班级汇总，再邮件发送到班级的辅导员手中。

使用环境

python 3.7
import openpyxl
import xlsxwriter
import os
import smtplib
from email.header import Header
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import time

开始

第一步 获取班级名单

名单

计划使用班级进行分类

读入班级并保存到数组

import openpyxl
wb=openpyxl.load_workbook('student.xlsx')  #打开excel文件
sheet = wb['Sheet1']
banji = []
for cell in sheet['B']:
    banji.append(cell.value)
print(banji)
wb.close()

使用班级对统计后的表单进行分类并保存到不同的新建表中

os.mkdir('day2_12')

# 打开表单文件
wb1 = openpyxl.load_workbook('day.xlsx')  # 打开excel文件
sheet1 = wb1['Sheet1']

for i in banji:
    name = i
    file_name = "G:\ccf_code\sdjmxyxy\day2_12\{}疫情统计.xlsx".format(i)
    wb2 = openpyxl.Workbook()
    sheets2 = wb2.get_sheet_names()
    sheet2 = wb2.get_sheet_by_name(sheets2[0])
    max_row = sheet1.max_row  # 最大行数
    max_column = sheet1.max_column  # 最大列数
    kkk = 0
    for m in range(1, max_row + 1):
        if m < 3 or str(sheet1['E{}'.format(m)].value.split(' ')[4]) == name:
            for n in range(97, 97 + max_column):  # chr(97)='a'
                n = chr(n)  # ASCII字符
                ii = '%s%d' % (n, m)  # 单元格编号
                cell1 = sheet1[ii].value  # 获取data单元格数据
                iii = '%s%d' % (n, m-kkk)
                sheet2[iii].value = cell1  # 赋值到test单元格
        else:
            kkk = kkk + 1
    wb2.save(file_name)
    wb2.close()
    wb1.close()

发送邮件的方法

def sendemail(filepath, name, to, From, pw):
    # 创建一个带附件的邮件实例
    message = MIMEMultipart()
    # 邮件的其他属性
    message['From'] = '<{}>'.format(From)
    message['Subject'] = Header(u'{}疫情报表'.format(name), 'utf8').encode()
    message['To'] = u'<{}>'.format(to)
    attr2 = MIMEText('查收今日{}报表'.format(name), 'plain', 'utf-8')
    message.attach(attr2)
    attr1 = MIMEText(open(r'{}'.format(filepath), 'rb').read(), 'base64', 'utf-8')
    attr1["content_Type"] = 'application/octet-stream'
    filename = '{}.xlsx'.format(name)
    kkk = 'attachment; filename="{}"'.format(filename)
    attr1.add_header('Content-Disposition', 'attachment', filename='{}.xlsx'.format(name))
    message.attach(attr1)
    server = smtplib.SMTP('gmail-smtp-in.l.google.com')
    server.login('{}'.format(From), '{}'.format(pw))
    server.sendmail('{}'.format(From), ['{}'.format(to)], message.as_string())
    print("邮件发送成功")

读取辅导员班级表，对照相关邮箱进行发送

for youxiang in sheet:
    if youxiang[1].value != None:
        to = youxiang[1].value
        name = youxiang[0].value
        file_path = "G:\ccf_code\sdjmxyxy\day2_12\{}疫情统计.xlsx".format(name)
        sendemail(file_path, name, to)
        time.sleep(1)

后记

失败之处：

·1 国内普通邮箱一天限制发送次数，一般为50封
·2 没有实现表单的自动爬取，仅仅实现了半自动化