【python 数据报表】发送带有样式和附件的邮件

给运营和产品同事定期发送一些用于运营和产品决策的统计报表邮件，邮件通常是表格为主体，一般也会带有一个或者多个附件。让邮件带有css样式，让读邮件也成为一件愉快的事情。

修改版效果：

版本1：

# coding=utf-8

import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import os
import csv
import smtplib
from email.header import Header as _Header
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.utils import parseaddr, formataddr


from mako.template import Template
from mako.lookup import TemplateLookup

HERE = os.path.abspath(os.path.dirname(__file__))
SMTP_SERVER = 'smtp.qq.com'

# 使用标准的25端口连接SMTP服务器是明文传输，发送过程中可能会被窃听。
# 这里选择加密SMTP会话， 更安全地发送邮件
SMTP_PORT = 587

############################发件人###########################
FROM_ADDR = '61966225@qq.com'
PASSWORD = 'XXXXXXXXXXXXXXXXXXX'


###########################收件人设置#########################
TO_ADDRS = ['defa.lai@cgtz.com','1973536419@qq.com']







rows_data = [
    [34, 72, 38, 30, 75, 48, 75],
    [6, 24, 1, 84, 54, 62, 60],
    [28, 79, 97, 13, 85, 93, 93],
    [27, 71, 40, 17, 18, 79, 90],
    [88, 25, 33, 23, 67, 1, 59],
    [24, 100, 20, 88, 29, 33, 38],
    [6, 57, 88, 28, 10, 26, 37],
    [52, 78, 1, 96, 26, 45, 47],
    [60, 54, 81, 66, 81, 90, 80],
    [70, 5, 46, 14, 71, 19, 66],
]
col_headers = ['日期', '周一', '周二', '周三',
               '周四', '周五', '周六', '周日']
row_headers = ['用户{}'.format(i) for i in range(1, 11)]


def mako_render(data, mako_file, directories):
    mylookup = TemplateLookup(directories=directories, input_encoding='utf-8',
                              output_encoding='utf-8',
                              default_filters=['decode.utf_8'])
    mytemplate = Template('<%include file="{}"/>'.format(mako_file),
                          lookup=mylookup, input_encoding='utf-8',
                          default_filters=['decode.utf_8'],
                          output_encoding='utf-8')
    content = mytemplate.render(**data)
    return content


def Header(name):  # noqa
    return _Header(name, 'utf-8').encode()


def _format_addr(s):
    name, addr = parseaddr(s)
    return formataddr((Header(name), addr))


def gen_msg(content, subject, attachments, nick_from=None, nick_to=None):
    if nick_from is None:
        nick_from = FROM_ADDR

    if nick_to is None:
        nick_to=TO_ADDRS

    msg = MIMEMultipart()
    msg['From'] = _format_addr('{} <{}>'.format(nick_from, FROM_ADDR))

    for TO_ADDRS1 in TO_ADDRS:
        #########隐藏收件人
        # msg['To'] = _format_addr('{} <{}>'.format(nick_to, TO_ADDRS1))

        ########显示收件人###################################
        msg['To'] = _format_addr('{} <{}>'.format(TO_ADDRS1, TO_ADDRS1))

    msg['Subject'] = Header(subject)
    msg.attach(MIMEText(content, 'html', 'utf-8'))

    for attachment in attachments:
        attach = MIMEText(open(attachment, 'rb').read(), 'base64', 'utf-8')
        attach['Content-Type'] = 'application/octet-stream'
        attach['Content-Disposition'] = 'attachment; filename="{}"'.format(
        os.path.basename(attachment))
        msg.attach(attach)
    return msg


def sendmail(content, subject, attachments, nick_from=None):
    msg = gen_msg(content, subject, attachments, nick_from)
    server = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
    server.starttls()
    server.login(FROM_ADDR, PASSWORD)
    server.sendmail(FROM_ADDR, TO_ADDRS, msg.as_string())

    server.quit()


def write_csv(csv_file, headers, rows):
    f = open(csv_file, 'wt')
    writer = csv.writer(f)
    writer.writerow(headers)
    for index, row in enumerate(rows):
        writer.writerow([row_headers[index]] + row)
    f.close()


def main():
    csv_file = os.path.join(HERE, u'statistics.csv')
    tmpl_directories = [os.path.join(HERE, 'c:/pic/tmpl')]
    write_csv(csv_file, col_headers, rows_data)
    data = {'rows_data': rows_data, 'row': col_headers,
            'row_headers': row_headers}


    content = mako_render(data, 'statistics.txt', directories=tmpl_directories)
    ########################
    sendmail(content, u'核心用户运营数据', [csv_file], nick_from=u'赖德发')

if __name__ == '__main__':
    main()
    print u'发送邮件完成！'

版本2：（用数据框）

# coding=utf-8

import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import os
import csv
import smtplib
from email.header import Header as _Header
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.utils import parseaddr, formataddr
import  numpy as np

from mako.template import Template
from mako.lookup import TemplateLookup
import pandas as pd
HERE = os.path.abspath(os.path.dirname(__file__))

print HERE
SMTP_SERVER = 'smtp.qq.com'

# 使用标准的25端口连接SMTP服务器是明文传输，发送过程中可能会被窃听。
# 这里选择加密SMTP会话， 更安全地发送邮件
SMTP_PORT = 587

############################发件人###########################
FROM_ADDR = '61966225@qq.com'
PASSWORD = 'qfqoywyxqghpbgjb'


###########################收件人设置#########################
TO_ADDRS = ['defa.lai@cgtz.com','1973536419@qq.com']



def mako_render(data, mako_file, directories):
    mylookup = TemplateLookup(directories=directories, input_encoding='utf-8',
                              output_encoding='utf-8',
                              default_filters=['decode.utf_8'])
    mytemplate = Template('<%include file="{}"/>'.format(mako_file),
                          lookup=mylookup, input_encoding='utf-8',
                          default_filters=['decode.utf_8'],
                          output_encoding='utf-8')
    content = mytemplate.render(**data)
    return content


def Header(name):  # noqa
    return _Header(name, 'utf-8').encode()


def _format_addr(s):
    name, addr = parseaddr(s)
    return formataddr((Header(name), addr))


def gen_msg(content, subject, attachments, nick_from=None, nick_to=None):
    if nick_from is None:
        nick_from = FROM_ADDR

    if nick_to is None:
        nick_to=TO_ADDRS

    msg = MIMEMultipart()
    msg['From'] = _format_addr('{} <{}>'.format(nick_from, FROM_ADDR))

    for TO_ADDRS1 in TO_ADDRS:
        #########隐藏收件人
        # msg['To'] = _format_addr('{} <{}>'.format(nick_to, TO_ADDRS1))

        ########显示收件人###################################
        msg['To'] = _format_addr('{} <{}>'.format(TO_ADDRS1, TO_ADDRS1))

    msg['Subject'] = Header(subject)
    msg.attach(MIMEText(content, 'html', 'utf-8'))

    for attachment in attachments:
        attach = MIMEText(open(attachment, 'rb').read(), 'base64', 'utf-8')
        attach['Content-Type'] = 'application/octet-stream'
        attach['Content-Disposition'] = 'attachment; filename="{}"'.format(
        os.path.basename(attachment))
        msg.attach(attach)
    return msg


def sendmail(content, subject, attachments, nick_from=None):
    msg = gen_msg(content, subject, attachments, nick_from)
    server = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
    server.starttls()
    server.login(FROM_ADDR, PASSWORD)
    server.sendmail(FROM_ADDR, TO_ADDRS, msg.as_string())

    server.quit()





def main():


    #########################读取csv文件############################


    data = pd.read_csv("c:/pic/statistics.csv")
    a = np.array(data.iloc[:, 1:8], dtype=int)
    rows_data = a.tolist()


    print  rows_data

    print type(rows_data)
    col_headers = ['日期', '周一', '周二', '周三',
                   '周四', '周五', '周六', '周日']
    row_headers = ['用户{}'.format(i) for i in range(1, 11)]

    data = {'rows_data': rows_data, 'row': col_headers,
            'row_headers': row_headers}




    tmpl_directories = 'c:/pic/tmpl'

    csv_file="c:/pic/statistics.csv"

    content = mako_render(data, 'statistics.txt', directories=tmpl_directories)
    #######################
    sendmail(content, u'核心用户运营数据', [csv_file], nick_from=u'赖德发')

if __name__ == '__main__':
    main()
    print u'发送邮件完成！'

版本3：

# coding=utf-8

import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import os
import csv
import smtplib
from email.header import Header as _Header
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.utils import parseaddr, formataddr
import  numpy as np

from mako.template import Template
from mako.lookup import TemplateLookup
import pandas as pd
HERE = os.path.abspath(os.path.dirname(__file__))

print HERE
SMTP_SERVER = 'smtp.qq.com'

# 使用标准的25端口连接SMTP服务器是明文传输，发送过程中可能会被窃听。
# 这里选择加密SMTP会话， 更安全地发送邮件
SMTP_PORT = 587

############################发件人###########################
FROM_ADDR = '61966225@qq.com'
PASSWORD = 'qfqoywyxqghpbgjb'


###########################收件人设置#########################
TO_ADDRS = ['defa.lai@cgtz.com','1973536419@qq.com']



def mako_render(data, mako_file, directories):
    mylookup = TemplateLookup(directories=directories, input_encoding='utf-8',
                              output_encoding='utf-8',
                              default_filters=['decode.utf_8'])
    mytemplate = Template('<%include file="{}"/>'.format(mako_file),
                          lookup=mylookup, input_encoding='utf-8',
                          default_filters=['decode.utf_8'],
                          output_encoding='utf-8')
    content = mytemplate.render(**data)
    return content


def Header(name):  # noqa
    return _Header(name, 'utf-8').encode()


def _format_addr(s):
    name, addr = parseaddr(s)
    return formataddr((Header(name), addr))


def gen_msg(content, subject, attachments, nick_from=None, nick_to=None):
    if nick_from is None:
        nick_from = FROM_ADDR

    if nick_to is None:
        nick_to=TO_ADDRS

    msg = MIMEMultipart()
    msg['From'] = _format_addr('{} <{}>'.format(nick_from, FROM_ADDR))

    for TO_ADDRS1 in TO_ADDRS:
        #########隐藏收件人
        # msg['To'] = _format_addr('{} <{}>'.format(nick_to, TO_ADDRS1))

        ########显示收件人###################################
        msg['To'] = _format_addr('{} <{}>'.format(TO_ADDRS1, TO_ADDRS1))

    msg['Subject'] = Header(subject)
    msg.attach(MIMEText(content, 'html', 'utf-8'))

    for attachment in attachments:
        attach = MIMEText(open(attachment, 'rb').read(), 'base64', 'utf-8')
        attach['Content-Type'] = 'application/octet-stream'
        attach['Content-Disposition'] = 'attachment; filename="{}"'.format(
        os.path.basename(attachment))
        msg.attach(attach)
    return msg


def sendmail(content, subject, attachments, nick_from=None):
    msg = gen_msg(content, subject, attachments, nick_from)
    server = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
    server.starttls()
    server.login(FROM_ADDR, PASSWORD)
    server.sendmail(FROM_ADDR, TO_ADDRS, msg.as_string())

    server.quit()





def main():


    #########################读取csv文件############################


    data = pd.read_excel("c:/pic/wang.xlsx")
    kk = len(data)
    a = np.array(data.iloc[:, 1:7], dtype=str)
    print a.shape
    rows_data = a.tolist()

    # print rows_data
    # print type(rows_data)

    row_headers=[]

    col_headers = ['日期','排名','平台名称','成交量(万元)','平均利率(%)','平均借款期限(月)','累计待还款金额(万元)']

    for i in range(0,kk):
        # print data.iloc[:,0]
        row_headers.append('2016-05-29')
    data = {'rows_data': rows_data, 'row': col_headers,
            'row_headers': row_headers}


    tmpl_directories = 'c:/pic/tmpl'

    csv_file="c:/pic/wang.xlsx"

    content = mako_render(data, 'statistics.txt', directories=tmpl_directories)
    #######################
    sendmail(content, u'核心用户运营数据', [csv_file], nick_from=u'赖德发')

if __name__ == '__main__':
    main()
    print u'发送邮件完成！'