Python实现生成Excel并自动发邮件

由于在java上实现过这样的功能，加上最近在学python，所以也想通过python来实现这样比较实用的功能，此为背景。

一、生成excel文件

1、导入相关包

from openpyxl import Workbook
from pandas.core.frame import DataFrame

2、新建几个字典并加入到列表中

	xj1 = {'label': 'LOAN_1', 'num': 1, 'platform': '666', 'dt': '20190112', 'type': 'aa'}
    xj2 = {'label': 'LOAN_2', 'num': 2, 'platform': '931', 'dt': '20190112', 'type': 'bb'}
    xj3 = {'label': 'LOAN_3', 'num': 3, 'platform': '828', 'dt': '20190112', 'type': 'cc'}
    xj_list = [xj1, xj2, xj3]

3、把列表转换成DataFrame

xj_list_df = DataFrame(xj_list)

4、新建excel，获取当前活动的sheet

 	wb = Workbook()
    table = wb.active

5、遍历dataframe，逐行写到单元格中

    for i in range(0, xj_list_df.shape[0]):
        label = xj_list_df.iloc[i]['label']
        num = xj_list_df.iloc[i]['num']
        platform = xj_list_df.iloc[i]['platform']
        dt = xj_list_df.iloc[i]['dt']
        type = xj_list_df.iloc[i]['type']

        table["A%d" % (i + 1)].value = label
        table["B%d" % (i + 1)].value = num
        table["C%d" % (i + 1)].value = platform
        table["D%d" % (i + 1)].value = dt
        table["E%d" % (i + 1)].value = type

6、保存内容到excel文件

file_path = 'example.xlsx'
wb.save(file_path)

二、自动发邮件

1、相关包导入

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
from email.utils import parseaddr, formataddr

2、创建一个带附件的实例

 	message = MIMEMultipart()
    message['From'] = _format_addr(from_name +' <%s>' % from_mail)
    message['To'] = _format_addr(to_names[0] +' <%s>' % to_mails[0])
    message['Subject'] = Header('发邮件测试', 'utf-8')

注意此处From和To不要直接使用下面这种样式

    # message['From'] = Header('Python自动发邮件 <%s>' % sender, 'utf-8')
    # message['To'] = Header(u'wendy<%s>' % receivers[0], 'utf-8')

经测试，这种用header出来的from在邮箱列表中是无法看到发件人信息的，到邮件详情中才能看到，所以要使用formataddr

	def _format_addr(s):
    name, addr = parseaddr(s)
    return formataddr((Header(name, 'utf-8').encode(), addr))

3、添加正文

message.attach(MIMEText('这是正文', 'plain', 'utf-8'))

4、添加附件，传送当前目录下的filepath文件

	att1 = MIMEText(open(file_path, 'rb').read(), 'base64', 'utf-8')
    att1["Content-Type"] = 'application/octet-stream'
    att1["Content-Disposition"] = 'attachment;filename=%s' % file_path
    message.attach(att1)

5、发送邮件，我这里用的是腾讯企业邮箱，如果是其他邮箱，那是要改connect中的地址和端口

		smtp_obj = smtplib.SMTP_SSL()
        smtp_obj.connect('smtp.exmail.qq.com', 465)
        smtp_obj.login(from_mail, '发件人密码')
        smtp_obj.sendmail(from_mail, to_mails, message.as_string())

三、效果图

四、全部代码

import traceback

from openpyxl import Workbook

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
from email.utils import parseaddr, formataddr

from pandas.core.frame import DataFrame


from_mail = 'xmliu@liuxunming.com'
from_name = 'Python自动发邮件'
to_mails = ['123456789@qq.com', '123456789@163.com']
to_names = ['xiguaming', 'windy']
file_path = 'shine.xlsx'


def create_excel():
    # label,num,platform,dt,type
    xj1 = {'label': 'LOAN_1', 'num': 1, 'platform': 'dianxin', 'dt': '20190112', 'type': 'aa'}
    xj2 = {'label': 'LOAN_2', 'num': 2, 'platform': 'liantong', 'dt': '20190112', 'type': 'bb'}
    xj3 = {'label': 'LOAN_3', 'num': 3, 'platform': 'yidong', 'dt': '20190112', 'type': 'cc'}
    xj_list = [xj1, xj2, xj3]
    xj_list_df = DataFrame(xj_list)
    wb = Workbook()
    table = wb.active
    print(xj_list_df)
    for i in range(0, xj_list_df.shape[0]):
        label = xj_list_df.iloc[i]['label']
        num = xj_list_df.iloc[i]['num']
        platform = xj_list_df.iloc[i]['platform']
        dt = xj_list_df.iloc[i]['dt']
        type = xj_list_df.iloc[i]['type']

        table["A%d" % (i + 1)].value = label
        table["B%d" % (i + 1)].value = num
        table["C%d" % (i + 1)].value = platform
        table["D%d" % (i + 1)].value = dt
        table["E%d" % (i + 1)].value = type

    wb.save(file_path)


def _format_addr(s):
    name, addr = parseaddr(s)
    return formataddr((Header(name, 'utf-8').encode(), addr))


def send_mail():
    print('开始发邮件')
    # 创建一个带附件的实例
    message = MIMEMultipart()
    message['From'] = _format_addr(from_name +' <%s>' % from_mail)
    message['To'] = _format_addr(to_names[0] +' <%s>' % to_mails[0])
    message['Subject'] = Header('发邮件测试', 'utf-8')

    print('添加附件和正文')
    # 邮件正文
    message.attach(MIMEText('这是正文', 'plain', 'utf-8'))

    # 构造附件1，传送当前目录下的filepath文件
    att1 = MIMEText(open(file_path, 'rb').read(), 'base64', 'utf-8')
    att1["Content-Type"] = 'application/octet-stream'
    att1["Content-Disposition"] = 'attachment;filename=%s' % file_path
    message.attach(att1)

    try:
        print('连接smtp')
        smtp_obj = smtplib.SMTP_SSL()
        smtp_obj.connect('smtp.exmail.qq.com', 465)
        smtp_obj.login(from_mail, '发件人密码')
        smtp_obj.sendmail(from_mail, to_mails, message.as_string())
        print('邮件发送成功')
    except smtplib.SMTPException:
        traceback.print_exc()
        print('无法发送邮件')


def main():
    try:
        create_excel()
        send_mail()
    except:
        traceback.print_exc()
        pass


if __name__ == '__main__':
    main()