简单实现Excel方式以Python+request接口测试，进行Excel读写处理测试用例

简单看下一下代码：

#Excel文件读取函数定义
def Excel_Read(filename, sheetnum):
#Excel文件读取路径
    case_dir = UrlConfig.ExportDataUrl + filename + '.xls'
#打开Excel文件
    open_wb = xlrd.open_workbook(case_dir)
#获取Excel文件第N个sheet
    sheet = open_wb.sheets()[sheetnum]
#获取Excel文件sheet名称
    sheetname = open_wb.sheet_names()[sheetnum]
#sheet行列定义
    nor = sheet.nrows
    nol = sheet.ncols
    return nor, sheet, sheetname

#Excel文件写入函数定义
def Excel_Write_Data(worksheet, workbook,  sheetname, nor, id, title, method, url, data, case_code, result, i):
    alignment = xlwt.Alignment()
    alignment.horz = alignment.HORZ_CENTER
    alignment.vert = alignment.VERT_CENTER
    style = xlwt.XFStyle()
    style.alignment = alignment
    nowl = 2
    norl = nor
    i = i + 1
#合并第1行8列，并写入'测试报告(StoreManage)'
    worksheet.write_merge(0, 0, 0, 8, '测试报告(StoreManage)', style)
#合并第1行norl列，并写入[sheetname]
    worksheet.write_merge(2, norl, 0, 0, sheetname, style)
'''
    固定第2行每一列一次写入
    mode，id，title，method，url，data，case_code，result_code,result_end
'''
    worksheet.write(1, 0, 'mode')
    worksheet.write(1, 1, 'id')
    worksheet.write(1, 2, 'title')
    worksheet.write(1, 3, 'method')
    worksheet.write(1, 4, 'url')
    worksheet.write(1, 5, 'data')
    worksheet.write(1, 6, 'case_code')
    worksheet.write(1, 7, 'result_code')
    worksheet.write(1, 8, 'result_end')
'''
   动态写入每一行每一列参数
'''
    worksheet.write(i, 1, id)
    worksheet.write(i, 2, title)
    worksheet.write(i, 3, method)
    worksheet.write(i, 4, url)
    worksheet.write(i, 5, data)
    worksheet.write(i, 6, case_code)
   #判断接口执行后返回结果
    if result['data']['code'] == 200:
        worksheet.write(i, 7, result['data']['desc'])
        worksheet.write(i, 8, 'pass')
    elif result['data']['code'] is None:
        worksheet.write(i, 7, result['data']['desc'])
        worksheet.write(i, 8, 'pass')
    else:
        worksheet.write(i, 7, result['data']['desc'])
        worksheet.write(i, 8, 'fail')
    return worksheet, workbook, nowl, norl

#包装请求方法，设置全局变量cookie
cookie = None
def test_request(req_url, req_headers, req_data, method):
    global cookie
#获取method参数是否get或post请求方式
    if method == 'get':
        request = requests.get(url=req_url, headers=req_headers, cookies=cookie)
    else:
        request = requests.post(url=req_url, headers=req_headers, data=req_data, cookies=cookie)
    if request.cookies != {}:
#获取cookies
        cookie = request.cookies
#获取接口请求后返回的json串
    result = request.json()
    return result

#包装读取Excel参数
def Excel_Read_Data(ReadFile, sheetnum):
#调用读取Excel方法
    nor, sheet, sheetname = Excel_Read(ReadFile, sheetnum)
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet(ReadFile, cell_overwrite_ok=True)
#存储excel文件路径
    filenames = UrlConfig.ReportDataUrl + sheetname + 'Report.xls'
#定义一个空列表
    data_list = []
#循环遍历excel每一个单元格参数
    for i in range(1, nor):
        data_dict = {}
        data_dict['case_id'] = sheet.cell_value(i, 0)
        data_dict['case_title'] = sheet.cell_value(i, 1)
        data_dict['method'] = sheet.cell_value(i, 2)
        data_dict['MainUrl'] = sheet.cell_value(i, 3)
        data_dict['Request_Url'] = sheet.cell_value(i, 4)
        data_dict['Headers'] = eval(sheet.cell_value(i, 5))
        data_dict['Quest_data'] = sheet.cell_value(i, 6)
        data_dict['case_code'] = sheet.cell_value(i, 7)
#把Excel遍历的单元格参数添加到list
        data_list.append(data_dict)
#获取对应的list参数
        for item in data_list:
            id = item['case_id']
            title = item['case_title']
            url = item['MainUrl'] + item['Request_Url']
            header = item['Headers']
            data = item['Quest_data']
            method = item['method']
            case_code = item['case_code']
#调用包装过的request方法
            result = test_request(req_url=url, req_headers=header, req_data=data, method=method)
#调用写入Excel方法
            Excel_Write_Data(worksheet, workbook, sheetnum, sheetname, nor, id, title, method, url, data, case_code,result, i)
#保存写入的Excel
        workbook.save(filenames)
#重置list
        data_list = []
    return data_list

#执行case
class testcase(unittest.TestCase):

    def test_case(self):
        sheetnum = 0
        try:
           #根据Excelsheet的数量循环执行，没有得时候结束跳出循环
            for i in range(1, 100):
                if sheetnum < 100:
                    Excel_Read_Data('TestCaseData', sheetnum)
                    sheetnum = sheetnum + i
                else:
                    break
        except :
            print(sheetnum-1)
#指定执行某一个sheet
    def test_case_one(self):
        Excel_Read_Data('TestCaseData', 0)

if __name__ == '__main__':
#开始执行testcase
    unittest.TestCase(testcase)
#创建测试用例集
    #suite = unittest.TestSuite()
#添加测试用例
    #suite.addTests(testcase("test_case_one"))

总结：看了以上代码，是不是觉得实现很简单？但这种方式耗时耗力，前期的case积累要充分，不然维护会很麻烦，每日一总结，欢迎大家评论