Python E化-利用pandas.dataframe.to_html生成邮件

最新推荐文章于 2024-05-25 22:57:15 发布

i see the future

最新推荐文章于 2024-05-25 22:57:15 发布

阅读量1.7k

点赞数 1

分类专栏：自动化办公文章标签： python html css pandas

本文链接：https://blog.csdn.net/m0_46629123/article/details/116010399

版权

自动化办公专栏收录该内容

18 篇文章 0 订阅

订阅专栏

思路：

因为在邮件中插入表格比较困难，故使用dataframe.to_html。但dataframe.to_html默认样式很丑，故用str.replace(old,new)来替代样式。

import win32com.client as win32
from openpyxl import load_workbook
import openpyxl


#获取cc对象
excel_file2=r'Mail_config.xlsx'
cc_wb=load_workbook(excel_file2)
cc_sheet=cc_wb.active
cc_rows=cc_sheet.max_row
cc_list=[]
for i in range(2,cc_rows+1):
    cc_list.append(cc_sheet.cell(row=i,column=1).value)


#获取To对象
excel_file3=r'V1.xlsx'
To_wb=load_workbook(excel_file3)
To_sheet=To_wb.active
To_rows=To_sheet.max_row
To_list=[]
for j in range(2,To_rows+1):
    To_list.append(To_sheet.cell(row=j,column=2).value)



def send_mail():
    import datetime
    import pandas as pd

    #NEW20210422修改：增加表格内容到正文
    df = pd.read_excel(excel_file3)
    table1_html = df.to_html(index=False)
    table1_html = table1_html.replace('<tr style="text-align: right;">', '<tr align="center" style="color:White;background-color:#1874CD;font-family:微軟正黑體,Tahoma,Arial,微軟雅黑體;font-size:16px;">')
    table1_html = table1_html.replace('<tr>', '<tr align="center" valign="middle" style="color:Black;background-color:#FFFFFF;font-family:微軟正黑體,Tahoma,Arial,微軟雅黑體;font-size:16px;border-color:#E0E0E0;border-width:1px;border-style:solid;height:26px;"')


    now=datetime.datetime.now()
    date=now.strftime('%Y')+now.strftime('%m')+now.strftime('%d')


    outlook = win32.Dispatch('Outlook.Application')
    mail_item = outlook.CreateItem(0) # 0: olMailItem

    addressee = ';'.join(To_list) #收件人邮箱列表
    cc = ';'.join(cc_list) #抄送人邮件列表
    mail_item.To=addressee
    mail_item.CC=cc

    mail_item.Subject = '主题'
    mail_path=r'V1.xlsx'
    mail_item.Attachments.Add(mail_path,1,1, "V1.xlsx") #1,1不可变

    mail_item.BodyFormat = 2          # 2: Html format
    mail_item.HTMLBody  = '''
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Document</title>
<style>
</style>
</head>

<body>
<div style="line-height:1.7;color:#000000;font-size:17px;font-family:Arial">
<bodybgcolor="LightBlue">
<div>To：someone</div>
<div>CC:someone</div>
<br>
<div>content</div> 
<div>content</div> 
<div>content</div> 
<div>content</div> 
<div>content</div> 
<div>content</div> 
<br>
<div>content</div> 
<div>content</div>
{0}
<br>
<br>
<div>content</div>
</body>
</html>
        '''.format(table1_html)

    mail_item.Display() # 这一步非常重要，没了这一步，在网页版邮箱就无法显示图片
    mail_item.Send()
    print('发送成功')
    #print(table1_html)

if __name__ == '__main__':
    send_mail()