excel表格读写

import xlrd
import xlwt
from xlutils import copy
# work_book = xlrd.open_workbook(r'D:\python文件\5.10\test.xls')
# print(work_book)
#
# work_sheet = work_book.sheets()[0]
# print(work_sheet)
# work_sheet = work_book.sheet_by_index(0)
# print(work_sheet)
# work_sheet = work_book.sheet_by_name('示例')
# print(work_sheet)
#
# names = work_book.sheet_names()
# print(names)
# print(work_book.sheet_loaded('示例'))
# #
# nrows = work_sheet.nrows
# print(nrows)
#
# row_hang = work_sheet.row(0)
# print(row_hang)
#
# row_obj = work_sheet.row_slice(0)
# print(row_obj)
#
# row_type = work_sheet.row_types(0,start_colx=0,end_colx=None)
# print(row_type)
# #
# row_values = work_sheet.row_values(0,start_colx=0,end_colx=None)
# print(row_values)
#
# print(work_sheet.row_len(0))
#
# ncols = work_sheet.ncols
# print(ncols)
# print(work_sheet.col(0, start_rowx=0, end_rowx=None))
# print(work_sheet.col_slice(0, start_rowx=0, end_rowx=None))
# print(work_sheet.col_types(0, start_rowx=0, end_rowx=None))
# col_value =work_sheet.col_values(0,start_rowx=0,end_rowx=None)
# print(col_value)
#
# value_obj = work_sheet.cell(1,2)
# print(value_obj)
# cell_type = work_sheet.cell_type(1,3)
# print(cell_type)
# cell_value = work_sheet.cell_value(1,2)
# print(cell_value)
# #
#
# excelpath = r'test.xls'
# workBook = xlrd.open_workbook(excelpath,formatting_info=True)
#
# # sheets = workBook.sheet_names()
#
# workSheet = workBook.sheet_by_name('示例')

# #
# cellData = workSheet.row_values(1)
# cellData = workSheet.cell(1,0)


# cellData = workSheet.cell(1,0)

# cellData = workSheet.cell(1,0).value
# print(cellData)
# print(workSheet.cell_value(1,0))
# #
# #
# print(workSheet.nrows)
# #
# newWorkBook = copy.copy(workBook)
# print(newWorkBook)
# #
# newSheet = newWorkBook.get_sheet(1)
# print(newWorkBook)
# #
# newSheet.write(1,9,'你好吗！！！')
#
# newWorkBook.save("./result.xls")


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


class MyTestCase:
    def __init__(self, method, url, header, data):
        self.method = method
        self.url = url
        self.header = header
        self.data = data

    def login(self):
        data = json.loads(self.data)
        response = requests.request(self.method, self.url,data=data)
        print('response.headers-----', response.cookies)
        print('response.json()-----', response.json())
        return response.cookies


    def test_01_cms(self,ret_cookie):
        data = json.loads(self.data)
        response = requests.request(self.method, self.url, data=data,cookies=ret_cookie)
        print(response.json())
        return response.json()


class ExcelSet:
    """专门读写excel"""

    def __init__(self):
        self.excel_path = './test.xls'
        self.work_book = xlrd.open_workbook(self.excel_path)
        self.work_sheet = self.work_book.sheet_by_name('示例')  # '示例'
        # 拷贝
        self.new_work_book = copy.copy(self.work_book)

    def read_excel_data(self):
        print(self.work_sheet.nrows)
        for case in range(1, self.work_sheet.nrows):
            case_id = self.work_sheet.cell_value(case, 0)
            desc = self.work_sheet.cell_value(case, 1)
            url = self.work_sheet.cell_value(case, 2)
            method = self.work_sheet.cell_value(case, 3)
            header = self.work_sheet.cell_value(case, 4)
            data = self.work_sheet.cell_value(case, 5)
            result = self.work_sheet.cell_value(case, 6)
            if not url or not data:
                print(f'测试用例{case_id},{desc}url或body为空')
                continue
            # print(1111,header,type(header))
            # print(222,result,type(result))
            if header:
                header = json.loads(header)
            if result:
                result = json.loads(result)
            res = MyTestCase(method, url, header, data)
            if desc == '登录cms':
                ret_cookie=res.login()
            else:
                resp = res.test_01_cms(ret_cookie)
                if resp==result:
                    print(1)
                else:
                    pass



if __name__ == '__main__':
    es = ExcelSet()
    es.read_excel_data()



# 优化版


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

class Test_Example_action:
    """实例化每条测试用例，并构造单个用例的所有性质"""
    def __init__(self, excle_path, sheet_index, rows):
        self.excel_path = excle_path
        self.work_book = xlrd.open_workbook(self.excel_path)
        self.work_sheet = self.work_book.sheet_by_index(sheet_index)
        self.case_id = self.work_sheet.cell_value(rows,0)#用例编号
        self.desc = self.work_sheet.cell_value(rows,1)#接口名
        self.url = self.work_sheet.cell_value(rows,2)#接口url
        self.method = self.work_sheet.cell_value(rows,3)#请求方式
        self.header = json.loads(self.work_sheet.cell_value(rows,4))#请求头
        self.data = json.loads(self.work_sheet.cell_value(rows,5))#表单数据
        self.result = json.loads(self.work_sheet.cell_value(rows,6))#预期结果
        # print(self.data,type(self.data))
        # print(self.method)
class Req_action:
    """定义一个请求动作的类"""
    def __init__(self,ck=None):
        self.ck = ck
    def req_action(self,test_object):
        response = requests.request(method=test_object.method,url=test_object.url,data=test_object.data,headers=test_object.header,cookies=self.ck)
        print(f'{test_object.case_id}已执行，响应状态{response.status_code},返回{response.json()}')
        return response.cookies


if __name__=='__main__':
    test_file_path=r'F:\BaiduYunDownload\爱软测测试学习\1课件\1讲义\2阶段\3接口自动化\test.xls'
    test_1= Test_Example_action(test_file_path,0,1)
    req_action_1=Req_action()#第一次登录要单独请求
    ck_1 = req_action_1.req_action(test_1)#拿到cookie
    for i in range(2,xlrd.open_workbook(test_file_path).sheet_by_index(0).nrows):
        test_all = Test_Example_action(test_file_path,0,i)
        req_action_havck = Req_action(ck=ck_1)
        req_action_havck.req_action(test_all)