實現思路：
1、從excle表格中讀取文件內容
2、將讀取到的數據，作為參數，寫入word文檔，郵箱地址不寫入其中
3、編寫郵件正文，讀取附件
4、建立鏈接
5、發送郵件
#coding:utf-8
import docx, xlrd, glob, smtplib, time
from docx.enum.style import WD_STYLE_TYPE
from docx.shared import Pt
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT, WD_ALIGN_PARAGRAPH
from docx.oxml.ns import qn
from docx.enum.table import WD_TABLE_ALIGNMENT
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
# 打開工作薄
excel = xlrd.open_workbook("salary.xlsx")
# 讀取sheet頁
book = excel.sheet_by_name("研發部工資表")
class Email_test(object):
def __init__(self, mail_user, mail_pass, mail_host):
self.mail_user = mail_user
self.mail_pass = mail_pass
self.mail_host = mail_host
resp = self.read_excel()
self.word_style()
self.create_salarg(resp)
self.send_mail(mail_user, mail_pass, mail_host, resp)
def read_excel(self):
# 按行讀取表格內容
content_all = []
for row_value in book.get_rows():
content = []
for cell_value in row_value:
content.append(cell_value.value)
content_all.append(content)
return content_all
doc = docx.Document()
def word_style(self):
# 設置字體和大小和格式
style = self.doc.styles['Normal']
style.font.name = "微軟雅黑"
style.element.rPr.rFonts.set(qn('w:eastAsia'), u'微軟雅黑')
style.font.size = Pt(11)
# 設置標題格式和文字大小
title = self.doc.add_heading("", 0)
title.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
_t = title.add_run("5月工資單")
_t.font.name = "微軟雅黑"
_t.element.rPr.rFonts.set(qn('w:eastAsia'), u'微軟雅黑')
_t.bold = True
# 定義工資單郵件函數
def create_salarg(self,content_all):
# self.word_style()
# 創建一個兩行，X列的表格
table = self.doc.add_table(rows=2, cols=book.ncols-1, )
# 設置表頭
title_cells = table.rows[0].cells
for index, salary in enumerate(content_all[0][0:-1]):
title_cells[index].text = salary
# 填充單元格
for data in content_all[1:]:
# 設置本行單元格的數值
# add_row:在原有表格的基礎上增加一行數據
row_cells = table.rows[1].cells
for index, content in enumerate(data[0:-1]):
# 數據類型轉換，浮點數不能直接寫入單元格，對數據進行轉換
content_to_str = str(content)
row_cells[index].text = content_to_str
self.doc.save("{}5月工資單.docx".format(data[0]))
# 自定義郵件發送函數
def send_mail(self, mail_user, mail_pass, mail_host, content_all):
sender = '[email protected]' # 發件人郵箱
for receiver_name in content_all[1:]:
path = glob.os.path.join(glob.os.getcwd(), '{}5月工資單.docx'.format(receiver_name[0]))
# 讀取附件內容
attr = MIMEText(open(path, 'rb').read(), 'base64', 'utf-8')
attr['Content-Type'] = 'application/octet-stream'
attr.add_header("Content-Disposition", "attachment", filename=("gbk", "", receiver_name[0] + "5月工資單.docx"))
message = MIMEMultipart() # 定義郵件對象
message['From'] = Header(sender)
message['subject'] = Header('工資單') # 郵件主題
receiver = receiver_name[-1]
# 添加附件到郵件
message.attach(attr)
msg_content = """
<p >親愛的
""" + receiver_name[0] + """
：</p>
<p >感謝您為公司做出的貢獻！</p>
<p >本月工資已經到賬，請注意查收。如未收到，請您在5個工作日內聯系人力資源部。</p>
<p >您的工資單詳情請查看郵件附件！</p>
<p >如有異議請聯系人力資源部。</p></p>
<br /><br /><br />
<p >人力資源部<br />2021年5月31日</p>
"""
message.attach(MIMEText(msg_content, 'html', 'utf-8'))
try:
# 郵件協議
smtobj = smtplib.SMTP()
# 建立鏈接，port一般默認為25
smtobj.connect(mail_host, port)
# 登陸郵箱
smtobj.login(mail_user, mail_pass)
smtobj.sendmail(sender, [receiver], message.as_string())
except smtplib.SMTPException as e:
print("error: %s" % e)
if __name__ == '__main__':
# 用戶登錄名
mail_user = 'xxxx'
# 設置個人口令
mail_pass = 'xxxxx‘
mail_host = 'x x x x'
Email_test(mail_user, mail_pass, mail_host)
代碼有待優化
