Python自动生成excel并每天发送邮件

作者：我在等风等你

数据分析师平时有一项工作是必不可少的，那就是向领导或企业发送每周和每日的数据报告派对。每天重复对相同消息进行排序的工作既浪费精力，又毫无意义。让我们把这种简单重复的工作交给Python吧。

这里使用python时使用的主要模块有pymysql、openpyxl、email和smtplib。pymysql用于连接数据库并检索数据，openpyxl用于写入excel，email和smtplib用于处理和创建电子邮件。链接到服务器，发送电子邮件。

# -*- coding: utf-8 -*-

import pymysql,openpyxl
import pandas as pd
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.image import MIMEImage
from email.mime.application import MIMEApplication
from email.mime.base import MIMEBase
from email.header import Header
from email import encoders
import smtplib
import time
import datetime
import win32com.client as win32 # 打开excel文件
from PIL import ImageGrab   #用于获取复制的图片

now = datetime.datetime.now()
date=datetime.datetime.strftime(now-datetime.timedelta(days = 1), '%Y%m%d')#获取日期

第一步是建立与数据库的连接并加载数据。 host是要连接的MySQL服务器地址，port是服务器端口号，user和passwd是用户名和密码，db是数据库名称。字符集是连接编码，通常是utf8。

def connectDatabase(sql): #连接mysql数据库
    db = pymysql.connect(
        host = 'xxxxxx',
        port = xxx,
        user = 'xxxxxxx',
        passwd = 'xxxxxxx',
        db = 'xxxxxxxxx',
        charset = "utf8")
    conn = db.cursor()  #创建并返回游标
    conn.execute(sql)  #执行数据库的查询命令
    results = conn.fetchall() #获取结果
    conn.close()
    db.close()
    return results
    
def get_data(sql): #获取数据
    results = connectDatabase(sql)
    return results

 #sql为你在mysql中的取数脚本,脚本中的日期用函数代替
sql=''' 
select * from table
 '''    

第二步，将获得的数据输入到事先格式化好的Excel模板中。这里我需要将4条数据（date1~date4）排列到一个excel中。 openpyxl可以读写Excel 2010文档，是一个非常好用的模块。详细功能大家可以百度一下。工作簿是您打开或创建的文件。您可以通过表名称搜索每个工作表，并通过坐标获取每个单元格（单元格对象）。

def to_excel(sql_1,sql_2,sql_3,sql_4):
    data1=get_data(sql_1)
    data2=get_data(sql_2)
    data3=get_data(sql_3)
    data4=get_data(sql_4)
    workbook=openpyxl.load_workbook('C:\\Users\\Administrator\\Desktop\\日报.xlsx') #打开文件
    ws1=workbook["Sheet1"] #获取worksheet
    ws2=workbook["Sheet2"]
    ws3=workbook['all']
    for i in range(len(data1)):
        for j in range(len(data1[0])):
            ws1.cell(i+2,j+1,data1[i][j])
    for i in range(len(data2)):
        for j in range(len(data2[0])):
            ws2.cell(i+2,j+1,data2[i][j])
    for i in range(5):
        for j in range(6):
            if i<4:
                ws3.cell(i+29,j+2,data3[i][j])
            elif  j in (1,2):
                ws3.cell(i+29,j+2,(data3[i-1][j]-data3[i-2][j])/data3[i-2][j])
            elif  j>2:
                ws3.cell(i+29,j+2,(data3[i-1][j]-data3[i-2][j]))
        for j in range(5):
            if i<4:
                ws3.cell(i+60,j+2,data4[i][j])
            elif i==4 and j>0:
                ws3.cell(i+60,j+2,(data4[i-1][j]-data4[i-2][j])/data4[i-2][j])
    workbook.save(filename='C:\\Users\\Administrator\\Desktop\\日报.xlsx') #保存文件

保存文件后，有些领导喜欢将数据内容直接显示在邮件正文中，空荡荡的邮件正文不好看。所以我们可以在Excel中截取一些基本数据并将其插入到电子邮件正文中。这里使用的模块是win32com.client，可以直接调用VBA库，还可以用于截图，可以弥补openpyxl无法实现的一些东西。功能。

    excel = win32.Dispatch('Excel.Application') #获取Excel
    wb = excel.Workbooks.Open('C:\\Users\\Administrator\\Desktop\\日报.xlsx') # 打开excel文件
    ws = wb.Worksheets('all')        # 获取Sheet
    ws.Range('A1:I20').CopyPicture()    # 复制A1:I20图片区域
    ws.Paste(ws.Range('T1'))    # 将图片移动到T1
    new_shape_name = 'push'
    excel.Selection.ShapeRange.Name = new_shape_name    # 选择区域重命名
    ws.Shapes(new_shape_name).Copy()    # 复制移动的图片Picture 1
    img = ImageGrab.grabclipboard()  # 获取图片数据
    img.save('C:\\Users\\Administrator\\Desktop\\p1.png') # 图片另存为
    wb.SaveAs('C:\\Users\\Administrator\\Desktop\\copy'+str(date)+'.xlsx') # excel文件另存为copy.xlsx
    wb.Close()

第三步，编写邮件内容并发送。 smtpserver是你自己登录账号的目的服务器，可以是163、QQ，也可以是你公司的企业邮箱等，比如qq的邮箱是http://smtp.qq.com。 MIMEMultipart 表示一个电子邮件对象，该对象可以分为文本电子邮件对象 MIMEText 和作为附件的图像对象 MIMEImage。我们使用 MIMEMultipart 附件功能以 html 形式将数据图像插入到电子邮件文本中。

def sent_mail():
    smtpserver = 'smtp.xxxx.com'
    user='xxxx@xxxx.com'
    pwd='xxxxxxx'
    receivers=['xxxx1@xxx.com','xxxx2@xxx.com']#收件人
    msg=MIMEMultipart() 
    title='日报_'+date
    msg.attach(MIMEText('<p><img ></p>', 'html', 'utf-8'))
    fp1 = open('C:\\Users\\Administrator\\Desktop\\p1.png', 'rb')
    msgImage1 = MIMEImage(fp1.read())
    fp1.close()
    msgImage1.add_header('Content-ID', '<1>')
    msg.attach(msgImage1)
    
    #添加附件
    att=MIMEApplication(open('C:\\Users\\Administrator\\Desktop\\日报.xlsx','rb').read())
    att.add_header('Content-Disposition', 'attachment', filename=Header('日报'+date+'.xlsx','utf-8').encode())
    msg.attach(att)

    #添加收件发件人信息
    msg['From'] = "{}".format(user)
    msg['To'] = ",".join(receivers)
    msg['Subject'] = title
    
    #发送邮件
    smtp = smtplib.SMTP()
    smtp.connect(smtpserver)
    #smtp.starttls()
    smtp.login(user,pwd)
    smtp.sendmail(user, receivers, msg.as_string())
    print('发送成功')
    smtp.close()
    time.sleep(10)

在完成函数末尾添加触发器，程序就完成了。

to_excel(sql_1,sql_2,sql_3,sql_4) 
sent_mail()

测试程序没有报错后，就可以进行最后一步：计时。在这里，我使用 Windows 中的计划任务工具 - 任务计划程序。

打开任务计划程序，点击创建基本任务，然后根据需要填写名称，然后点击下一步，进入运行界面，可以选择每天、每周或每月。设置时间和频率并选择您之前编写的脚本，然后单击“下一步”。由于上传图片到知乎太麻烦，这里就不一一截图了。