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

Python 专栏收录该内容
2 篇文章 0 订阅

由于在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()

  • 2
    点赞
  • 0
    评论
  • 8
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值