自动发邮件,execl读取,发邮件,表格

本文介绍了一种方法,通过读取Excel表格中的数据,自动发送电子邮件。该过程涉及将Excel文件作为数据源,提取收件人、主题和正文等信息,然后利用编程语言(如Python的pandas和smtplib库)实现邮件的自动化发送。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

# -*- coding: utf-8 -*-
"""
Created on Sun May  5 10:13:11 2019

@author: Administrator
"""

# -*- coding: utf-8 -*-
"""
Created on Thu Mar 14 16:35:00 2019

@author: Administrator
"""

import pandas as pd

import pymysql

import time
import datetime

from openpyxl import Workbook
from openpyxl import load_workbook

from openpyxl.utils import get_column_letter
from openpyxl.writer.excel import ExcelWriter 

con = pymysql.connect(host='XXX', user='XXX',
                     password='XXX', database='XXX', charset="XXX", port=XXX)

#信贷
zhuce=pd.read_sql_query(sql="""
SELECT
a.`时间`,ifnull(b.`人数`,0)人数
from 
(
SELECT
concat(HOUR ( a.create_time ),"点", '--', HOUR ( a.create_time )+1,"点" )时间,COUNT(a.account) 人数
from futan_login_record a
where date( a.create_time )="2019-05-01"
GROUP BY concat( date( a.create_time ), ' ', HOUR ( a.create_time ), "点", '--',HOUR ( a.create_time )+1,"点")
ORDER BY  HOUR ( a.create_time )
) a
LEFT JOIN
(
SELECT
concat( HOUR ( a.create_time ),"点", '--', HOUR ( a.create_time )+1,"点" )时间,COUNT(a.account) 人数
from futan_users a
where date( a.create_time )=CURDATE()
GROUP BY concat( date( a.create_time ), ' ', HOUR ( a.create_time ), "点", '--',HOUR ( a.create_time )+1,"点")
) b on a.`时间`=b.`时间`
;""" ,con=con)

gwq=pd.read_sql_query(sql="""
SELECT
a.`时间`,ifnull(b.`人数`,0)人数,ifnull(b.张数,0) 张数,ifnull(b.金额,0) 金额
from 
(
SELECT
concat(HOUR ( a.create_time ),"点", '--', HOUR ( a.create_time )+1,"点" )时间,COUNT(a.account) 人数
from futan_login_record a
where date( a.create_time )="2019-05-01"
GROUP BY concat( date( a.create_time ), ' ', HOUR ( a.create_time ), "点", '--',HOUR ( a.create_time )+1,"点")
ORDER BY  HOUR ( a.create_time )
) a
LEFT JOIN
(
SELECT
concat(  HOUR ( a.create_time ),"点", '--', HOUR ( a.create_time )+1,"点" )时间,COUNT(DISTINCT a.uid)人数,COUNT( a.uid) 张数,SUM(a.favor_money)金额
from  futan_users_coupon a
where a.is_used="USABLE"
and date( a.create_time )=CURDATE()
GROUP BY concat( HOUR ( a.create_time ),"点", '--', HOUR ( a.create_time )+1,"点" )
) b on a.`时间`=b.`时间`
;""" ,con=con)

con.close()

today = datetime.date.today().strftime('%Y-%m-%d')

writer = pd.ExcelWriter("D:\\XXX\\XXX\\XXX\\"+today+"XXX.xlsx")

zhuce.to_excel(writer,sheet_name='每小时注册')
gwq.to_excel(writer,sheet_name='购物券')

writer.save()

today1=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) #当前时间,用于excel上

wb = load_workbook("D:\\XXX\\XXX\\XXX\\"+today+"XXX.xlsx",data_only=True)
ws = wb.get_sheet_by_name('每小时注册')

shijian = []
renshu = []

for rown in range(2,30):
        for coln in range(2,4):
            value = ws.cell(row=rown,column=coln).value
            if coln == 2:
                shijian.append(value)
            elif coln == 3:
                renshu.append(value)

print(shijian)
print(renshu)

wb1= load_workbook("D:\\XXX\\XXX\\XXX\\"+today+"XXX.xlsx",data_only=True)
ws1 = wb1.get_sheet_by_name('XXX')

shijian1 = []
renshu1 = []
zhangshu=[]
jine=[]

for rown in range(2,30):
        for coln in range(2,6):
            value = ws1.cell(row=rown,column=coln).value
            if coln == 2:
                shijian1.append(value)
            elif coln == 3:
                renshu1.append(value)
            elif coln == 4:
                zhangshu.append(value)
            elif coln == 5:
                jine.append(value)





import smtplib
from email.header import Header
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
#from email.mime.application import MIMEApplication 附件专用

def send_mail():
    # 定义变量
    from_addr = "XXX"
    password = "XXX"
    to_addr = "XXX"
    #acc = "XXX"

    message = MIMEMultipart()
    message['From'] = from_addr
    message['To'] = to_addr 
    #message['Cc'] = acc
    message['subject'] = Header('%s' % ('每小时注册人数'))
    html = """\
<!DOCTYPE html>
<html>
<meta charset="utf-8">
<head>
    <title>iOS - Bugly崩溃日报</title>
</head>
<body>
<div id="container">
    <div id="content">
        <p>
            """ +today1+ """每小时注册人数:
            <table width="800" border="2" bordercolor="black" cellspacing="2">
                <tr>
                    <td><strong>时间</strong></td>
                    <td><strong>人数</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian[0]+ """</strong></td>
                    <td><strong>""" +str(renshu[0])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[1]+ """</strong></td>
                    <td><strong>""" +str(renshu[1])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[2]+ """</strong></td>
                    <td><strong>""" +str(renshu[2])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[3]+ """</strong></td>
                    <td><strong>""" +str(renshu[3])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[4]+ """</strong></td>
                    <td><strong>""" +str(renshu[4])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[5]+ """</strong></td>
                    <td><strong>""" +str(renshu[5])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[6]+ """</strong></td>
                    <td><strong>""" +str(renshu[6])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[7]+ """</strong></td>
                    <td><strong>""" +str(renshu[7])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[8]+ """</strong></td>
                    <td><strong>""" +str(renshu[8])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[9]+ """</strong></td>
                    <td><strong>""" +str(renshu[9])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[10]+ """</strong></td>
                    <td><strong>""" +str(renshu[10])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[11]+ """</strong></td>
                    <td><strong>""" +str(renshu[11])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[12]+ """</strong></td>
                    <td><strong>""" +str(renshu[12])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[13]+ """</strong></td>
                    <td><strong>""" +str(renshu[13])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[14]+ """</strong></td>
                    <td><strong>""" +str(renshu[14])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[15]+ """</strong></td>
                    <td><strong>""" +str(renshu[15])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[16]+ """</strong></td>
                    <td><strong>""" +str(renshu[16])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[17]+ """</strong></td>
                    <td><strong>""" +str(renshu[17])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[18]+ """</strong></td>
                    <td><strong>""" +str(renshu[18])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[19]+ """</strong></td>
                    <td><strong>""" +str(renshu[19])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[20]+ """</strong></td>
                    <td><strong>""" +str(renshu[20])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[21]+ """</strong></td>
                    <td><strong>""" +str(renshu[21])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[22]+ """</strong></td>
                    <td><strong>""" +str(renshu[22])+ """</strong></td>

                </tr>
                <tr>
                    <td><strong>""" +shijian[23]+ """</strong></td>
                    <td><strong>""" +str(renshu[23])+ """</strong></td>

                </tr>
            </table>
        </p>
        <p>
            """ +today1+ """购物券情况:
            <table width="800" border="2" bordercolor="black" cellspacing="2">
                <tr>
                    <td><strong>时间</strong></td>
                    <td><strong>人数</strong></td>
                    <td><strong>张数</strong></td>
                    <td><strong>金额</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[0]+ """</strong></td>
                    <td><strong>""" +str(renshu1[0])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[0])+ """</strong></td>
                    <td><strong>""" +str(jine[0])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[1]+ """</strong></td>
                    <td><strong>""" +str(renshu1[1])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[1])+ """</strong></td>
                    <td><strong>""" +str(jine[1])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[2]+ """</strong></td>
                    <td><strong>""" +str(renshu1[2])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[2])+ """</strong></td>
                    <td><strong>""" +str(jine[2])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[3]+ """</strong></td>
                    <td><strong>""" +str(renshu1[3])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[3])+ """</strong></td>
                    <td><strong>""" +str(jine[3])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[4]+ """</strong></td>
                    <td><strong>""" +str(renshu1[4])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[4])+ """</strong></td>
                    <td><strong>""" +str(jine[4])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[5]+ """</strong></td>
                    <td><strong>""" +str(renshu1[5])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[5])+ """</strong></td>
                    <td><strong>""" +str(jine[5])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[6]+ """</strong></td>
                    <td><strong>""" +str(renshu1[6])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[6])+ """</strong></td>
                    <td><strong>""" +str(jine[6])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[7]+ """</strong></td>
                    <td><strong>""" +str(renshu1[7])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[7])+ """</strong></td>
                    <td><strong>""" +str(jine[7])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[8]+ """</strong></td>
                    <td><strong>""" +str(renshu1[8])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[8])+ """</strong></td>
                    <td><strong>""" +str(jine[8])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[9]+ """</strong></td>
                    <td><strong>""" +str(renshu1[9])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[9])+ """</strong></td>
                    <td><strong>""" +str(jine[9])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[10]+ """</strong></td>
                    <td><strong>""" +str(renshu1[10])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[10])+ """</strong></td>
                    <td><strong>""" +str(jine[10])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[11]+ """</strong></td>
                    <td><strong>""" +str(renshu1[11])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[11])+ """</strong></td>
                    <td><strong>""" +str(jine[11])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[12]+ """</strong></td>
                    <td><strong>""" +str(renshu1[12])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[12])+ """</strong></td>
                    <td><strong>""" +str(jine[12])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[13]+ """</strong></td>
                    <td><strong>""" +str(renshu1[13])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[13])+ """</strong></td>
                    <td><strong>""" +str(jine[13])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[14]+ """</strong></td>
                    <td><strong>""" +str(renshu1[14])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[14])+ """</strong></td>
                    <td><strong>""" +str(jine[14])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[15]+ """</strong></td>
                    <td><strong>""" +str(renshu1[15])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[15])+ """</strong></td>
                    <td><strong>""" +str(jine[15])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[16]+ """</strong></td>
                    <td><strong>""" +str(renshu1[16])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[16])+ """</strong></td>
                    <td><strong>""" +str(jine[16])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[17]+ """</strong></td>
                    <td><strong>""" +str(renshu1[17])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[17])+ """</strong></td>
                    <td><strong>""" +str(jine[17])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[18]+ """</strong></td>
                    <td><strong>""" +str(renshu1[18])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[18])+ """</strong></td>
                    <td><strong>""" +str(jine[18])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[19]+ """</strong></td>
                    <td><strong>""" +str(renshu1[19])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[19])+ """</strong></td>
                    <td><strong>""" +str(jine[19])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[20]+ """</strong></td>
                    <td><strong>""" +str(renshu1[20])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[20])+ """</strong></td>
                    <td><strong>""" +str(jine[20])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[21]+ """</strong></td>
                    <td><strong>""" +str(renshu1[21])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[21])+ """</strong></td>
                    <td><strong>""" +str(jine[21])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[22]+ """</strong></td>
                    <td><strong>""" +str(renshu1[22])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[22])+ """</strong></td>
                    <td><strong>""" +str(jine[22])+ """</strong></td>
                </tr>
                <tr>
                    <td><strong>""" +shijian1[23]+ """</strong></td>
                    <td><strong>""" +str(renshu1[23])+ """</strong></td>
                    <td><strong>""" +str(zhangshu[23])+ """</strong></td>
                    <td><strong>""" +str(jine[23])+ """</strong></td>
                </tr>
            </table>
    </div>
</div>
</body>
</html>
    """
    
    message.attach(MIMEText(html, 'html', 'utf-8'))  # 邮件正文内容;
    # 设置端口
    smtpobj = smtplib.SMTP('smtp.qq.com', 25)
    smtpobj.login(from_addr, password)
    smtpobj.sendmail(from_addr, to_addr, message.as_string())
    smtpobj.quit()


# 发送邮件
send_mail()

效果

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值