Realize the idea ：
1、 from excle Read the contents of the file in the table
2、 Will read the data , As a parameter , write in word file , Email address is not written into it
3、 Write the body of the message , Read the attachment
4、 Building links
5、 Send E-mail
#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
# Open the workbook
excel = xlrd.open_workbook("salary.xlsx")
# Read sheet page
book = excel.sheet_by_name(" R & D payroll ")
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):
# Read table contents by row
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):
# Set font and size and format
style = self.doc.styles['Normal']
style.font.name = " Microsoft YaHei "
style.element.rPr.rFonts.set(qn('w:eastAsia'), u' Microsoft YaHei ')
style.font.size = Pt(11)
# Set the title format and text size
title = self.doc.add_heading("", 0)
title.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
_t = title.add_run("5 Monthly payroll ")
_t.font.name = " Microsoft YaHei "
_t.element.rPr.rFonts.set(qn('w:eastAsia'), u' Microsoft YaHei ')
_t.bold = True
# Define payroll mail function
def create_salarg(self,content_all):
# self.word_style()
# Create a two line ,X List of tables
table = self.doc.add_table(rows=2, cols=book.ncols-1, )
# Set the header
title_cells = table.rows[0].cells
for index, salary in enumerate(content_all[0][0:-1]):
title_cells[index].text = salary
# Fill in cells
for data in content_all[1:]:
# Set the number of cells in this line
# add_row: Add a row of data on the basis of the original table
row_cells = table.rows[1].cells
for index, content in enumerate(data[0:-1]):
# Data type conversion , Floating point numbers cannot be written directly to cells , Transform the data
content_to_str = str(content)
row_cells[index].text = content_to_str
self.doc.save("{}5 Monthly payroll .docx".format(data[0]))
# Custom mail sending function
def send_mail(self, mail_user, mail_pass, mail_host, content_all):
sender = '[email protected]' # Sender email
for receiver_name in content_all[1:]:
path = glob.os.path.join(glob.os.getcwd(), '{}5 Monthly payroll .docx'.format(receiver_name[0]))
# Read the attachment contents
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 Monthly payroll .docx"))
message = MIMEMultipart() # Define the mail object
message['From'] = Header(sender)
message['subject'] = Header(' Payroll ') # Email subject
receiver = receiver_name[-1]
# Add attachments to the message
message.attach(attr)
msg_content = """
<p > dear
""" + receiver_name[0] + """
：</p>
<p > Thank you for your contribution to the company ！</p>
<p > The salary of this month has arrived , Please remember to check . If not received , Please visit 5 Contact the human resources department within working days .</p>
<p > Please check the email attachment for details of your payroll ！</p>
<p > If you have any objection, please contact the human resources department .</p></p>
<br /><br /><br />
<p > Human Resources Department <br />2021 year 5 month 31 Japan </p>
"""
message.attach(MIMEText(msg_content, 'html', 'utf-8'))
try:
# Email protocol
smtobj = smtplib.SMTP()
# Building links ,port The general default is 25
smtobj.connect(mail_host, port)
# Log in to email
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__':
# User login
mail_user = 'xxxx'
# Set personal password
mail_pass = 'xxxxx‘
mail_host = 'x x x x'
Email_test(mail_user, mail_pass, mail_host)
The code needs to be optimized
