人力资源部每个月发工资条,以前靠人工手动一条一条的复制,200多号员工,差不多需要耗费一天时间。
用python写个脚本,分分钟的事情就可以全部发送完。
我这边工资表如下图
姓名在C列,邮箱在B列。
发送完,这样显示
收到的邮件是这样
还有这样
# !/usr/bin/env python
# -*- coding:utf-8 -*-
# Author:Hiuhung Wan
from openpyxl import load_workbook
from email.mime.text import MIMEText # 邮件正文
from email.header import Header # 邮件头
import smtplib, time
# 登录
def login(smtp_server, ssl_port, sender, passwd):
smtp_obj = smtplib.SMTP_SSL(smtp_server, ssl_port)
smtp_obj.login(sender, passwd)
return smtp_obj
# 发邮件
def send_email(smtp_obj, sender, rec_list, msg):
smtp_obj.sendmail(sender, rec_list, msg)
def op_excel(smtp_obj, filename:str):
# 加载excel文件 data_only=True,自动计算公式
wb = load_workbook(filename, data_only=True) # 使用xlsx文件,不要使用xls
sheet = wb.active
# 先取列名
table_col_name = []
for row in sheet.rows:
for cell in row:
# print(cell.value)
table_col_name.append(cell.value)
break
# 循环excel
for row in sheet.iter_rows(min_row=2):
table_col_html = '<thead>' # 表头
row_text = '<tr>' # 开始一行
list_del = [] # 准备减掉的字段
list_finally = table_col_name[:] # 最终的字段
for cell in row:
# print(cell.value, type(cell.value)) # None <class 'NoneType'> 0 <class 'int'>
if cell.value == None or cell.value == 0: # 数据为零或空
# print(cell.col_idx) # 列数,从1开始数
list_del.append(table_col_name[cell.col_idx - 1])
else:
row_text += f'<td>{cell.value}</td>'
row_text += '</tr>' # 结束一行
# 去掉值为零或空的字段
for i in list_del:
list_finally.remove(i)
# 重写首行
for i in list_finally:
table_col_html += f'<th>{i}</th>'
table_col_html += '</thead>'
# 员工姓名
name = row[2].value # 从0开始数,第2列
staff_email = row[1].value # 员工邮箱地址 从0开始数,第1列
mail_body_context = f'''<h3>{name}:你好!</h3>
<p>请查收你2020年5月的工资条。</p>
<table border="1px solid black">
{table_col_html}
{row_text}
</table>
'''
msg = MIMEText(mail_body_context, 'html', 'utf-8')
msg['From'] = Header('HHHH人力资源部', 'utf-8') # 发送者
msg['To'] = Header('HHHH员工', 'utf-8') # 接收者
msg['Subject'] = Header('HHHH 2020年5月工资条', 'utf-8') # 主题
# 发邮件
try:
send_email(smtp_obj, sender, [staff_email], msg.as_string())
print(f'成功发送工资条到{staff_email}--{name}...')
except smtplib.SMTPException as e:
print('Error:无法发送邮件.Case:%s' % e)
if __name__ == '__main__':
time_start = time.time()
smtp_server = 'smtp.qq.com' # SMTP服务器
ssl_port = 465 # SMTP端口
sender = '123456789@qq.com' # 发件人邮箱
passwd = '**********' # 发件人密码
filename = "2020年5月员工工资表.xlsx"
smtp_obj = login(smtp_server, ssl_port, sender, passwd)
op_excel(smtp_obj, filename)
time_end = time.time()
print('脚本执行完毕,耗时%.3f秒。' % (time_end - time_start))
支持自动去掉值为空或为零的列。如奖金、请假等。
当然,也可以竖向排列工资条。
需要修改一下op_excel方法
def op_excel(smtp_obj, filename: str): # 操作表格
# 加载excel文件 data_only=True,自动计算公式
wb = load_workbook(filename, data_only=True) # 使用xlsx文件,不要使用xls
sheet = wb.active
# 先取列名
table_col_name = []
for row in sheet.rows:
for cell in row:
# print(cell.value)
table_col_name.append(cell.value)
break
# 循环excel
for row in sheet.iter_rows(min_row=2):
# table_col_html = '<thead>' # 表头
row_text = ''
list_del = [] # 准备减掉的字段
list_finally = table_col_name[:] # 最终的字段
list_money = [] # 金额
for cell in row:
# print(cell.value, type(cell.value)) # None <class 'NoneType'>
# 0 <class 'int'>
if cell.value is None or cell.value == 0: # 数据为零或空
# print(cell.col_idx) # 列数,从1开始数
list_del.append(table_col_name[cell.col_idx - 1])
else:
list_money.append(cell.value)
# 去掉值为零或空的字段
for i in list_del:
list_finally.remove(i)
# 员工姓名
name = row[1].value # 从0开始数,第2列
staff_email = row[2].value # 员工邮箱地址 从0开始数,第1列
# <tr>添充
for index in range(len(list_finally)):
# print(list_finally[index],list_money[index])
row_text += '<tr>' # 开始一行
row_text += f'<td>{list_finally[index]}</td>'
row_text += f'<td>{list_money[index]}</td>'
row_text += '</tr>' # 结束一行
mail_body_context = f'''<h3>{name}:你好!</h3>
<p>请查收你2020年5月的工资条。</p>
<table border="1px solid black">
<thead>
<th>项目</th>
<th>值</th>
</thead>
{row_text}
</table>
'''
msg = MIMEText(mail_body_context, 'html', 'utf-8')
msg['From'] = Header('HHHH人力资源部', 'utf-8') # 发送者
msg['To'] = Header('HHHH员工', 'utf-8') # 接收者
msg['Subject'] = Header('HHHH 2020年5月工资条', 'utf-8') # 主题
# 发邮件
try:
send_email(smtp_obj, sender, [staff_email], msg.as_string())
print(f'成功发送工资条到{staff_email}--{name}...')
except smtplib.SMTPException as e:
print('Error:无法发送邮件.Case:%s' % e)
效果如下
源码2
# 员工发工资的脚本
from openpyxl import load_workbook
import smtplib
from email.mime.text import MIMEText #邮件正文
from email.header import Header # 邮件头
#加载excel文件
wb = load_workbook('python使用工资条.xlsx',data_only=True)
sheet1 = wb.active
# print(sheet1)
#登陆邮箱
smtp_obj = smtplib.SMTP('smtp.***qq.com') # 邮箱发送服务器(ssL 485报错去掉端口)
smtp_obj.login('XXXX@qq.com','*************') # 邮箱用户名,密码(授权码)
count =0
table_col_html = '<thead>' # 表头
for row in sheet1.iter_rows(min_row=1):
count+=1
if count ==1:
for col in row:
table_col_html +=f"<th>{col.value}<t/th>"
table_col_html += '</thead>'
continue
else:
row_test ='<tr>' #开始一行
for cell in row:
# print(cell.value,end=',')
row_test += f"<td>{cell.value}</td>"
row_test +="</tr>"# 结束一行
name = row[2]
staff_email =row[1].value
print(staff_email,name)
mail_body_context = f"""
<h3>{name.value},你好:</h3>
<p>请查收2020年5月的工资条。。。</p>
<table border="1px solid black">
{table_col_html}
{row_test}
</table>
"""
msg_body = MIMEText(mail_body_context,'html','utf-8')
msg_body['From'] = Header('测试人事部','utf-8') #发送者
msg_body['To'] = staff_email
# msg_body['To'] = Header(f'{staff_email}','utf-8') # 接受者
msg_body['Subject'] = Header('三国公司2020年5月份工资条','utf-8') # 主题
# 发邮件
smtp_obj.sendmail('发送者邮箱',[staff_email,],msg_body.as_string())
print(f"成功发送工资条到{staff_email}-{name.value}.....")
链接:https://pan.baidu.com/s/15YFKiehjJS7lZA8OBFjOKQ
提取码:qcwh