python实现从excel存取svn账号密码并发送邮件通知

功能初衷

• 开发人员比较多，每次都需要为开发人员发送用户名和密码
• svn密码没有统一的规则，svn账号没有统一的规则

准备工作

• 部门员工通信录，通信录包括姓名、电话、电子邮件

  
  image.png
  

  B4列为员工真实姓名
  C列为员工电话号码
  E列为员工svn拼音，python代码生成
  F列为员工svn账号，pyton代码生成，拼音首字符大写,中间为3个任意特殊字符，最后为手机号码后四位
  H列为员工电子邮件，发送用户名和密码使用

• python2.7
• openpyxl库实现excel的存取操作
• pypinyin实现拼音转换

源码分析

任意目录创建pw_gen.py文件

# -*- coding:utf8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook
import sys
import string
import smtplib
from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart
from pypinyin import pinyin, lazy_pinyin, Style
import random
# https://www.cnblogs.com/sun-haiyu/p/7096423.html

reload(sys)
sys.setdefaultencoding('utf-8')


def usage():
    '''usage'''
    print 'Usage: %s %s' % (
        sys.argv[0], 'genpwd|sendmail')
    sys.exit()


def load_mail():
    wb = load_workbook('./txl3.xlsx')
    # ws = wb["员工信息表1"]
    ws = wb.active
    for row_num in range(4, 30):
        row_num_str = str(row_num)
        B_name = ws['B'+row_num_str].value
        E_username = ws['E'+row_num_str].value
        F_password = ws['F'+row_num_str].value
        H_mail = ws['H'+row_num_str].value
        content = "用户名:"+E_username+"密码:"+F_password+"\n"+"访问地址:http://127.0.01:1080/*,*替换成具体的项目路径"
        
        print content
        print B_name
        print H_mail
        send_mail(B_name, H_mail, content)


def send_mail(name, mail, content):
    # 第三方 SMTP 服务
    mail_host = "smtp.163.com"  # 设置服务器
    mail_user = "***@163.com"  # 用户名
    mail_pass = "*****"  # 口令

    sender = '***@163.com'
    receivers = mail  # 接收邮件，可设置为你的QQ邮箱或者其他邮箱
    # message = MIMEMultipart()
    message = MIMEText(content, 'plain', 'utf-8')
    # result=open(file, 'rb').read()
    # message.attach(MIMEText(result, 'plain', 'utf-8'))
    message['From'] = sender
    message['To'] = receivers
    # message['To'] = ','.join(receivers)
    subject = name+'SVN信息改变通知'
    message['Subject'] = Header(subject, 'utf-8')
    try:
        smtpObj = smtplib.SMTP()
        smtpObj.connect(mail_host, 25)    # 25 为 SMTP 端口号
        smtpObj.login(mail_user, mail_pass)
        smtpObj.sendmail(sender, receivers, message.as_string())
        print "邮件发送成功"
    except Exception, e:
        print "邮件发送失败"
        print str(e)


def save_excel():
    wb = load_workbook('./txl3.xlsx')
    # ws = wb["员工信息表1"]
    ws = wb.active
    print ws
    for row_num in range(4, 30):
        row_num_str = str(row_num)
        B_row_value = ws['B'+row_num_str].value
        cell_value = ws['C'+row_num_str].value
        third_str = cell_value[-4:]
        s = lazy_pinyin(B_row_value)
        username = ''.join(s)
        first_str = username.title()
        # 生成密码
        second_str = ''.join(random.sample('#@$!', 3))
        pwd = first_str+second_str+third_str
        print row_num_str
        print pwd
        print username
        # 保存svn用户名
        ws['E'+row_num_str] = username
        # 保存svn密码
        ws['F'+row_num_str] = pwd
    wb.save('./txl3.xlsx')


def main():
    if len(sys.argv) != 2:
        usage()
    operations = sys.argv[1]
    if operations == "genpwd":
        save_excel()
    elif operations == "sendmail":
        print operations
        load_mail()
    else:
        print "操作参数不正确"


if __name__ == '__main__':
    main()

操作命令

#生成svn账号和密码保存到excel
python pw_gen.py genpwd
#从excel读取svn账号密码发送到员工邮箱
python pw_gen.py sendmail