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

最新推荐文章于 2024-03-21 12:20:03 发布

wuqi356

最新推荐文章于 2024-03-21 12:20:03 发布

阅读量612

点赞数

分类专栏： python

本文链接：https://blog.youkuaiyun.com/wuqi356/article/details/89842375

版权

本文介绍了一种方法，通过读取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