目的：excel中维护接口用例数据，通过python中requests库进行读取用例，并把运行结果与excel中的预期结果对比，最后把执行情况写入到excel中去。

excel维护数据：

具体的接口名跟参数名根据你实际的测试的接口决定。我这边自己维护的excel列分别是编号，用例名称、请求方式、请求地址、请求参数、请求头、预期结果、实际结果、是否通过。

代码展示:

import xlrd
import xlwt
import requests
import json
from xlutils.copy import copy

def xls_open(filename):
workBook = xlrd.open_workbook(filename, formatting_info=True) # 1、打开文件
return workBook

def xls_getRow(sheet, row):
data = {}
data["method"] = sheet.cell_value(row, 2)
data["url"] = sheet.cell_value(row, 3)
data["param"] = sheet.cell_value(row, 4)
data["header"] = sheet.cell_value(row, 5)
data["expect"] = sheet.cell_value(row, 6)
return data

def xls_request(data):
if data['method'] == 'get':
result = requests.get(data['url'], data['param'])
elif data['method'] == 'post':
# headers = {"Content-Type": "application/x-www-form-urlencoded;charset=UTF-8"}
# headers = {"Content-Type": "application/json; charset=utf8"}
response = requests.post(data['url'], data=json.loads(data["param"]), headers=json.loads(data["header"]))
result = response.text
else:
print("Unknown method " + data["method"])

return result  

def xls_set(sheet, row, col, value, red=False):
style = "font:colour_index red;"
if not red:
sheet.write(row, col, value)
else:
sheet.write(row, col, value, xlwt.easyxf(style))

def xls_save(book, filepath):
book.save(filepath)

def doSheet(brd, bwt, sheetIndex):
brd_sheet = brd.sheets()[sheetIndex] # 获取excel单页
bwt_sheet = bwt.get_sheet(sheetIndex)
count_row = brd_sheet.nrows # 获取行数
for i in range(1, count_row):
data = xls_getRow(brd_sheet, i)
result = xls_request(data)

    if data\["expect"\] == result:  
        xls\_set(bwt\_sheet, i, 8, 'pass', False)  
        xls\_set(bwt\_sheet, i, 7, result)  
    else:  
        xls\_set(bwt\_sheet, i, 8, 'fail', True)  
        xls\_set(bwt\_sheet, i, 7, result)  

if __name__ == '__main__':
brd = xls_open(r'test_interface.xls') # 打开文件
bwt = copy(brd) # copy一份一样的excel文件
doSheet(brd, bwt, 0) # 执行文件
xls_save(bwt, 'test_result.xls') # 保存文件

**转载：https://www.jianshu.com/p/0c2d66c4a892**
*总结：首先感谢这个作者的分享，我实际在操作过程中发现，excel中若维护的header类型不同，则这代码整体是有问题的，这个代码只适合接口的请求头是一致的，大家需要注意的是从excel读取出的是参数，预期结果都是字符串类型，根据实际需要来进行转化字典。*