Monthly Archives: September 2013

写脚本群发薪水清单邮件通知

一到发薪水日,我们的人事忙得不可开交,给每位员工发送上月的薪水清单邮件,一个个从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()

工资表测试样例:点这下载

有需要的朋友可以参考下。