[python]使用python抓取sonar首页项目指标信息转存Excel并自动发送邮件

# json数据处理（处理接口返回数据）
import json
# http请求处理
import urllib.request
#excel写入
import xlsxwriter
#时间
import datetime
#邮件发送
import smtplib
from email.mime.text import MIMEText
from email.header import Header

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import json
import urllib.request
import xlsxwriter
import datetime
import smtplib
from email.mime.text import MIMEText
from email.header import Header


#获取项目清单接口
url_server_host="http://10.110.164.28:9000"
url_get_projects=url_server_host+"/api/components/search_projects"
# 获取分析结果接口
url_get_analyse=url_server_host+"/api/measures/search?metricKeys=alert_status%2Cbugs%2Creliability_rating%2Cvulnerabilities%2Csecurity_rating%2Csecurity_hotspots_reviewed%2Csecurity_review_rating%2Ccode_smells%2Csqale_rating%2Cduplicated_lines_density%2Ccoverage%2Cncloc%2Cncloc_language_distribution&projectKeys="
# Excel 表头映射
tableheadMapping={'alert_status':'告警状态',
                  'code_smells':'代码异味',
                  'coverage':'覆盖率',
                  'duplicated_lines_density':'重复率',
                  'ncloc':'扫描行数',
                  'ncloc_language_distribution':'扫描行数分布',
                  'reliability_rating':'新代码可靠率',
                  'security_hotspots_reviewed':'复审热点',
                  'security_rating':'安全比率',
                  'security_review_rating':'新代码安全复审率',
                  'sqale_rating':'SQALE评级',
                  'vulnerabilities':'新增漏洞',
                  }
# 发件人
email_sender = 'master336@163.com'
email_login_name = 'master336@163.com'
# smtp登录密码
email_login_pwd = ''
# 收件人
email_receivers = []
# 服务地址
email_smtp_server = "smtp.163.com"
email_smtp_port = 465
context='<h3>今日代码扫描已完成，执行结果如下</h3><br/><table border="1" align="center">'

# 获取所有项目的key
projectKeys={}

res = urllib.request.urlopen(url_get_projects)
res = res.read()
projects=json.loads(res)
for compentvar in projects['components'] :
    if compentvar['key'] not in projectKeys:
        projectKeys[compentvar['key']]=compentvar['name']

reqparams=""
for projectkey in projectKeys:
    reqparams = reqparams + ','+ projectkey
print('projects------>',reqparams)
url_get_analyse =  url_get_analyse + urllib.parse.quote(reqparams[1:])

# 请求分析结果
res = urllib.request.urlopen(url_get_analyse)
res = res.read()
projects_analyse=json.loads(res)
analysedata={}
analysetitle={}
for analy in projects_analyse['measures']:
    projectkey=analy['component']
    if projectkey in analysedata:
        project=analysedata[projectkey]
    else:
        project= {}
    keyparam=analy['metric']
    if keyparam not in analysetitle:
        if keyparam in tableheadMapping:
            analysetitle[keyparam]=tableheadMapping[keyparam];
        else:
            analysetitle[keyparam]=keyparam;
    project[keyparam]=analy['value']
    analysedata[projectkey]=project
workbook = xlsxwriter.Workbook('sonar分析结果（增量'+datetime.datetime.now().strftime('%Y%m%d-%H%M%s')+'）.xlsx')
worksheet = workbook.add_worksheet(u'分析结果')
# 处理表头
context=context+"<thead><tr>"
worksheet.write(0,0,'projectkey')
worksheet.write(0,1,'项目名称')
context=context+"<td>projectkey</td>"
context=context+"<td>项目名称</td>"
i=2;

for title in analysetitle.items():
    context=context+"<td>"+title[1]+"</td>"
    worksheet.write(0,i,title[1])
    i = i+1
context=context+"</tr></thead>"
# 处理内容
row=1
context=context+"<tbody>"
for keyval in analysedata:
    context=context+"<tr>"
    val = analysedata[keyval]
    worksheet.write(row,0,keyval)
    worksheet.write(row,1,projectKeys[keyval])
    context=context+"<td>"+keyval+"</td>"
    context=context+"<td>"+projectKeys[keyval]+"</td>"
    i=2;
    for title in analysetitle.items():
        titleval=title[0]
        if titleval in val:
            worksheet.write(row,i,val[titleval])
            context=context+"<td>"+val[titleval]+"</td>"
        i = i+1
    row=row+1
    context=context+"</tr>"
context=context+"</tbody>"
workbook.close()
print("开始发送邮件")
# 发送邮件

context=context+'</table><br/><h4>以上邮件来自系统自动发送，请勿回复或转发！</h4>'
message = MIMEText(context, 'html', 'utf-8')
message['From'] = email_sender #Header(email_sender, 'utf-8')     # 发送者
message['To'] =  ",".join(email_receivers) #Header(",".join(email_receivers), 'utf-8')          # 接收者
message['Subject'] = Header('sonar代码扫描日志'+datetime.datetime.now().strftime('%Y-%m-%d'), 'utf-8')

# 非ssl
# smtpObj = smtplib.SMTP()
# smtpObj.connect(email_smtp_server,465)
# ssl
try:
    smtpObj=smtplib.SMTP_SSL(email_smtp_server, email_smtp_port)
    # 括号中对应的是发件人邮箱账号、邮箱密码
    smtpObj.login(email_login_name, email_login_pwd)
    smtpObj.sendmail(email_sender, email_receivers, message.as_string())
    print ("邮件发送成功")
except smtplib.SMTPException as e:
    print(e)