import xlrd,requests,time,os from xlutils import copy from conf.settings import DATA_PATH def readCase(case_path):     case_list = [] # 存放所有的测试用例，给后面运行的时候使用     book = xlrd.open_workbook(case_path)     sheet = book.sheet_by_index(0)     for line in range(1,sheet.nrows):         case = [] # 存放每条用例的小case         line_case = sheet.row_values(line) # row_values是excel里面每一行的所有数据         project = line_case[0] # 项目名称         model = line_case[1] # 请求模块         case_id = line_case[2] # case_id         detail = line_case[3] # 用例描述         url = line_case[4] # 请求url         method = line_case[5] # 请求方式         req_data = line_case[6] # 请求数据         hope = line_case[7] # 预期结果         tester = line_case[11] # 测试人员         case = [project,model,case_id,detail,url,method,req_data,hope,tester]         case_list.append(case)     return case_list def strToDict(data): # 把传入的字符串data转成字典，my_request函数里的data是一个字典形式     dic = {}     #username=ssj,passwd=123456     dic_list = data.split(',') # ['username=ssj','passwd=123456']     if not dic_list[0]: # 如果dic_list[0]不为真，返回一个空字典         return dic     for k in dic_list:         k_list = k.split('=') # ['username','ssj']['passwd','123456']         dic_k = k_list[0] # 'username','passwd'         dic_v = k_list[1] # 'ssj','123456'         dic[dic_k] = dic_v # 生成字典格式：{'username':'ssj','passwd','123456'}     return dic def my_request(method,url,data): # 用这个函数来请求接口数据，返回实际结果     new_data = strToDict(data)     try:         if method.upper() == 'GET':             r = requests.get(url,new_data) # new_data必须是字典格式         else:             r = requests.post(url,new_data) # new_data必须是字典格式     except Exception as e:         return '出错了，错误结果是%s' % e     return r.text # r.text返回的是一个字符串，就是下面的response是一个字符串 def check_res(response,hope): # response是实际结果，hope是预期结果     new_response = response.replace('": "','=').replace('": ','=') # 将实际结果生成的json串的字符串替换成=     for check in hope.split(','): # 预期结果是error_code=0,userId=423这种格式，生成['error_code=0','userId=423']         if check not in new_response: # 循环error_code=0和userId=423,如果不在new_response里，返回False             return '失败'         return '通过' # 反写excel def write_excel(src_case_path,res_list): # res_list是一个二维数组，每个小list里面包含请求报文、返回报文和测试结果     src_book = xlrd.open_workbook(src_case_path) # 打开一个excel     new_book = copy.copy(src_book) # 复制一份     sheet = new_book.get_sheet(0) # 打开第0个sheet页     line = 1     for res in res_list: # res是每一行list         req = res.get('request') # 请求报文，因为main.py里面是字典，不能通过下标取值，这种方式可以获得对应的值         response = res.get('response') # 返回报文，因为main.py里面是字典，不能通过下标取值，这种方式可以获得对应的值         status = res.get('status') # 测试结果，因为main.py里面是字典，不能通过下标取值，这种方式可以获得对应的值         # req = res[0]         # response = res[1]         # status = res[2]         sheet.write(line,8,req)         sheet.write(line,9,response)         sheet.write(line,10,status)         line += 1     file_name = time.strftime('%Y%m%d%H%M%S')+os.path.basename(src_case_path) # 执行完excel后的文件名     abs_path = os.path.join(DATA_PATH,file_name).replace('xlsx','xls')     new_book.save(abs_path) if __name__ == '__main__':     # res = readCase(r'D:\20171216\测试用例1.xlsx')     # print(res)     # res = strToDict('username=sunsj123,passwd=aA123456')     # print(res)     # d = {'username': 'sunsj123', 'passwd': 'aA123456'}     # r = my_request('get','http://lanxia.fukc.com','a = 1')     # print(r)     #     res = '''     #     {
       #   "error_code": 0,     #   "login_info": {
       #     "login_time": "20171231220218",     #     "sign": "be8f92646e93a5e8c4ce215335b7e693",     #     "userId": 423     #   }     # }     # '''     #     r = check_res(res,'error_code=0,userId=423')     #     print(r)         res_list = [         [          'http://api.nnzhp.cn/api/user/login?username=sunsj123&passwd=aA123456',          '{"error_code":0,"msg":"成功"}',          '通过'         ]     ]         excel_path = r'D:\20171216\测试用例1.xlsx'         write_excel(excel_path,res_list)