一到发薪水日，我们的人事忙得不可开交，给每位员工发送上月的薪水清单邮件，一个个从Excel表格复制粘贴到邮件，忙到晚上10点才结束，整得人精疲力尽。HR让我帮她解决这个难题，她说她用foxmail,网上搜索，应该有一些工具可以完成。但我对foxmail,outlook又不是很熟悉，想想还是使用自己擅长的脚本写。脚本这东西，长时间不练习，就生疏了，整整花了早上2小时，刚好200行代码：）为了学习python，这次硬着头皮用python写，在 windows xp, windows 8 , mac OS X 10.8.4 测试通过。
send_salary_mail.py:
# -*- coding: utf-8 -*-
#!/usr/bin/env python2.7
'''
Copyright 2013 IBOXPAY Inc
Description: this script help HR send mail to all staffs.
Dependency: xlrd, Install:
wget --no-check-certificate https://pypi.python.org/packages/source/x/xlrd/xlrd-0.9.2.tar.gz
tar xvf xlrd-0.9.2.tar.gz
cd xlrd-0.9.2
sudo python setup.py install
Written by Lytsing Huang 2013-09-12
refer: http://code.activestate.com/recipes/578150-sending-non-ascii-emails-from-python-3/
'''
import sys
import smtplib
from email.header import Header
from email.mime.text import MIMEText
from email.utils import formataddr
from datetime import datetime
import xlrd
reload(sys)
sys.setdefaultencoding('utf-8')
mail_host = 'mail.xxx.com' #发送邮件的smtp地址
mail_user = 'xxx@xxx.com' # 发送通知邮件的用户名
mail_pass = 'xxx' # 用户的密码
sender_name = 'xxx'
sender_addr = 'xxx@xxx.com'
subject = '2013年8月XXX公司薪水发放通知单' # ***邮件标题*** 每月需要手动修改年月
title = subject
html_template = """
<html>
<head>
<meta http-equiv="content-type" content="text/html;charset=utf-8" />
</head>
<body>
<h2 align="center">%s</h2>
<table border="1", cellpadding="2" cellspacing="0">
<thead>
<tr>
<th>姓名</th>
<th>基本工资</th>
<th>岗位津贴</th>
<th>交通补贴</th>
<th>补助天数</th>
<th>补助</th>
<th>绩效工资</th>
<th>其他</th>
<th>应发合计</th>
<th>公司承担社保
<th>公司承担住房公积金</th>
<th>代扣社保</th>
<th>代扣住房公积金</th>
<th>病假天数</th>
<th>病假</th>
<th>事假天数</th>
<th>事假</th>
<th>个税</th>
<th>其他扣款</th>
<th>实发工资<th>
</tr>
</thead>
<tbody>
<tr>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
<td> %s </td>
</tr>
</tbody>
</table>
<pre>
说明：
1、以上薪水将会转入您提供给公司的银行账户中，如银行账户有更改请及时通知人力资源部。
2、员工薪水信息属公司机密，请妥善保管，泄漏者将按公司有关制度处理。
3、如对薪水支付数额有异议的，请于一周内以邮件的形式向人力资源部提出。
人力资源部
%s
----------------------------------------------
%s 人事
</pre>
</body>
</html>
"""
def write_mail_content(header_title, name, base_salary, post_allowance,
transport_subsidies, grants_days, allowance, pay_for_performance,
other, total, company_pension_commitment, company_housing_fund_commitment,
withholding_social_security, withholding_housing_fund, sick_days,
sick_leave, leave_days, leave, personal_income_tax, other_withholding,
real_wages, today, sender):
content = html_template % (header_title, name, base_salary, post_allowance,
transport_subsidies, grants_days, allowance, pay_for_performance,
other, total, company_pension_commitment, company_housing_fund_commitment,
withholding_social_security, withholding_housing_fund, sick_days,
sick_leave, leave_days, leave, personal_income_tax, other_withholding,
real_wages, today, sender)
return content
def send_mail(msg, sender, recipient):
try:
s = smtplib.SMTP()
s.connect(mail_host)
s.ehlo()
s.starttls()
s.login(mail_user,mail_pass)
s.sendmail(sender, recipient, msg.as_string())
s.close()
return True
except Exception, e: print str(e)
return False
def write_mail(sender, recipient, sub, content):
name = Header(sender, 'utf-8').encode()
msg = MIMEText(content, _subtype = 'html', _charset='utf-8')
msg['Subject'] = Header(sub, 'utf-8')
msg['From'] = formataddr((name, sender_addr))
msg['To'] = recipient
return msg
def main():
if len(sys.argv) < 2:
print '错误，没有指定参数'
print '用法：python send_salary_mail.py xxx.xls'
sys.exit()
bk = xlrd.open_workbook(sys.argv[1])
#bk.sheets()返回一个列表
sh = bk.sheets()[0] #读取第一张sheet
#下面是按行读取excel表格内容
for row in range(3, sh.nrows):
name = sh.row(row)[2].value # 姓名
recipient_addr = sh.row(row)[3].value # Email
base_salary = sh.row(row)[7].value # 基本工资
post_allowance = sh.row(row)[8].value # 岗位津贴
transport_subsidies = sh.row(row)[9].value # 交通补贴
grants_days = sh.row(row)[10].value # 补助天数
allowance = sh.row(row)[11].value # 补助
pay_for_performance = sh.row(row)[12].value # 绩效工资
other = sh.row(row)[13].value # 其他
total = sh.row(row)[14].value # 应发合计
company_pension_commitment = sh.row(row)[15].value # 公司承担社保
company_housing_fund_commitment = sh.row(row)[16].value # 公司承担住房公积金
withholding_social_security = sh.row(row)[17].value # 代扣社保
withholding_housing_fund = sh.row(row)[18].value # 代扣住房公积金
sick_days = sh.row(row)[19].value # 病假天数
sick_leave = sh.row(row)[20].value # 病假
leave_days = sh.row(row)[21].value # 事假天数
leave = sh.row(row)[22].value # 事假
personal_income_tax = sh.row(row)[24].value # 个税
other_withholding = sh.row(row)[25].value # 其他扣款
real_wages = sh.row(row)[26].value # 实发工资
today = datetime.now().strftime('%Y/%m/%d')
content = write_mail_content(title, name, base_salary, post_allowance,
transport_subsidies, grants_days, allowance, pay_for_performance,
other, total, company_pension_commitment, company_housing_fund_commitment,
withholding_social_security, withholding_housing_fund, sick_days,
sick_leave, leave_days, leave, personal_income_tax, other_withholding,
real_wages, today, sender_name)
msg = write_mail(sender_name, recipient_addr, subject, content)
if send_mail(msg, sender_addr, recipient_addr):
print ' 姓名：' + name + ' 发送成功.'
else:
print ' 姓名：' + name + ' 发送失败.'
print 'Send all finished! please check out the failed records.'
if __name__ == '__main__':
main()
工资表测试样例：点这下载
有需要的朋友可以参考下。