excel读取实现Jenkins集成自动发送邮件

excel读取

1.接口用例设计

2.单个请求发送成功

login_url = "http://123.11.111.111:8081/erp-5.7/loginIn"
data = {
    "name":"wefwf",
    "pwd":"1232312"
}

def login(login_url,data):
    respones = requests.post(login_url,json=data)
    print(respones.json())
login(login_url,data)

3.读取excel第一行每一列的值

   

#打开excel赋值给wb
wb = openpyxl.load_workbook("test_data.xlsx")
#找到login表单赋值给sheetl
sheet1 = wb["login"]
#取出每行每列的值
case_id = sheet1.cell(row=2,column=1).value,
case_url = sheet1.cell(row=2,column=2).value,
case_data = sheet1.cell(row=2,column=5).value,
case_expected = sheet1.cell(row=2,column=6).value,
print(case_id,case_url,case_data,case_expected)

4.转换为字典格式

#转化为字典模式
case_dict = dict(
case_id = sheet1.cell(row=2,column=1).value,
case_url = sheet1.cell(row=2,column=2).value,
case_data = sheet1.cell(row=2,column=5).value,
case_expected = sheet1.cell(row=2,column=6).value
)
print(case_dict)

5.取出excel所有内容

sheet1 = wb["login"]
#sheet1.max_row获取有效最大行数 max+1是为了获取最后一行数据
max = sheet1.max_row
#定义一个空的列表将遍历出来的字典添加进去
list_data = []
for i in range(2,max+1):
    case_dict = dict(
    case_id = sheet1.cell(i,column=1).value,
    case_url = sheet1.cell(i,column=2).value,
    case_data = sheet1.cell(i,column=5).value,
    case_expected = sheet1.cell(i,column=6).value
    )
    list_data.append(case_dict)
print(list_data)

写入excel函数

def write_data(filename,sheetname,row,column,result_value):
    wb = openpyxl.load_workbook(filename,sheetname)
    sheet1 = wb["login"]
    #将读取的值写给result_value
    sheet1.cell(row,column).value=result_value
    #一定要保存
    wb.save(filename)

1.遍历excel写入整合

import openpyxl
import requests


def login(login_url,data):
    respones = requests.post(login_url,json=data)
    html = respones.json()
    return html
def read_data(filename,sheetname):
    wb = openpyxl.load_workbook(filename)
    sheet1 = wb[sheetname]
    max = sheet1.max_row
    list_data = []
    for i in range(2,max+1):
        case_dict = dict(
        case_id = sheet1.cell(i,column=1).value,
        case_url = sheet1.cell(i,column=2).value,
        case_data = sheet1.cell(i,column=5).value,
        case_expected = sheet1.cell(i,column=6).value
        )
        list_data.append(case_dict)
    return list_data

def write_data(filename,sheetname,row,column,result_value):
    wb = openpyxl.load_workbook(filename,sheetname)
    sheet1 = wb["login"]
    sheet1.cell(row,column).value=result_value
    wb.save(filename)


def excel(filename,sheetname):
    #对每行读取的excel进行遍历取值
    case_excel = read_data(filename,sheetname)
    for case in case_excel:
        #取出有用的值
        case_id = case["case_id"]
        print(case_id)
        case_url = case["case_url"]
        print(case_url)
        case_data = case["case_data"]
        print(case_data)
        expect = case["case_expected"]
        print(expect)
excel("test_data.xlsx","login")

2.整个代码

import openpyxl
import requests

def login(login_url,data):
    respones = requests.post(login_url,json=data)
    html = respones.json()
    return html

def read_data(filename,sheetname):
    wb = openpyxl.load_workbook(filename)
    sheet1 = wb[sheetname]
    max = sheet1.max_row
    list_data = []
    for i in range(2,max+1):
        case_dict = dict(
        case_id = sheet1.cell(i,column=1).value,
        case_url = sheet1.cell(i,column=2).value,
        case_data = sheet1.cell(i,column=5).value,
        case_expected = sheet1.cell(i,column=6).value
        )
        list_data.append(case_dict)
    return list_data

def write_data(filename,sheetname,row,column,result_value):
    wb = openpyxl.load_workbook(filename)
    sheet1 = wb[sheetname]
    sheet1.cell(row,column).value=result_value
    wb.save(filename)

def excel(filename,sheetname):
    #对每行读取的excel进行遍历取值
    case_excel = read_data(filename,sheetname)
    for case in case_excel:
        case_id = case["case_id"]
        case_url = case["case_url"]
        case_data = case["case_data"]
        expect = case["case_expected"]
        print("预期值是：{}".format(expect))
        loginn = login(case_url,eval(case_data))
        message = loginn.get("message")
        print("期望值是：{}".format(message))
        if expect == message:
            value = "成功通过"
        else:
            value = "失败咯"
        write_data(filename,sheetname,case_id+1,7,value)
excel("test_data.xlsx","login")

 整合Jenkins自动发送邮件

1.新建工程

 

 2.配置SVN

        url这个地址可以通过本地的svn客户端查看。选中一个文件夹右键找到第一个框

 

 cd 文件路径可以在服务器上用 find / -name api_test查找文件夹

点击应用-保存  ------点击立即构建

3.配置发送邮件

https://www.cnblogs.com/imyalost/p/8781759.html