import openpyxl
# 創(chuàng)建一個(gè)工作簿
wb = openpyxl.Workbook()
# 創(chuàng)建一個(gè)test_case的sheet表單
wb.create_sheet('test_case')
# 保存為一個(gè)xlsx格式的文件
wb.save('cases.xlsx')
# 讀取excel中的數(shù)據(jù)
# 第一步：打開工作簿
wb = openpyxl.load_workbook('cases.xlsx')
# 第二步：選取表單
sh = wb['Sheet1']
# 第三步：讀取數(shù)據(jù)
# 參數(shù) row:行  column：列
ce = sh.cell(row = 1,column = 1)   # 讀取第一行，第一列的數(shù)據(jù)
print(ce.value)
# 按行讀取數(shù)據(jù) list(sh.rows)
print(list(sh.rows)[1:])     # 按行讀取數(shù)據(jù)，去掉第一行的表頭信息數(shù)據(jù)
for cases in list(sh.rows)[1:]:
    case_id =  cases[0].value
    case_excepted = cases[1].value
    case_data = cases[2].value
    print(case_excepted,case_data)
# 關(guān)閉工作薄
wb.close()

import openpyxl
class Case: #這個(gè)類用來存儲(chǔ)用例的
    __slots__ = [] #特殊的類屬性，可以用來限制這個(gè)類創(chuàng)建的實(shí)例屬性添加 可寫可不寫
    pass

class ReadExcel(object): #讀取excel數(shù)據(jù)的類
    def __init__(self,file_name,sheet_name):
        """
        這個(gè)是用來初始化讀取對(duì)象的
        :param file_name: 文件名 ---> str類型
        :param sheet_name: 表單名 ———> str類型
        """
        # 打開文件
        self.wb = openpyxl.load_workbook(file_name)
        # 選擇表單
        self.sh = self.wb[sheet_name]
    def read_data_line(self):
        #按行讀取數(shù)據(jù)轉(zhuǎn)化為列表
        rows_data = list(self.sh.rows)
        # print(rows_data)
        # 獲取表單的表頭信息
        titles = []
        for title in rows_data[0]:
            titles.append(title.value)
        # print(titles)
        #定義一個(gè)空列表用來存儲(chǔ)測(cè)試用例
        cases = []
        for case in rows_data[1:]:
            # print(case)
            data = []
            for cell in case: #獲取一條測(cè)試用例數(shù)據(jù)
                # print(cell.value)
                data.append(cell.value)
                # print(data)
                #判斷該單元格是否為字符串，如果是字符串類型則需要使用eval();如果不是字符串類型則不需要使用eval()
                if isinstance(cell.value,str):
                    data.append(eval(cell.value))
                else:
                    data.append(cell.value)
                #將該條數(shù)據(jù)存放至cases中
            # print(dict(list(zip(titles,data))))
                case_data = dict(list(zip(titles,data)))
                cases.append(case_data)
        return cases
if __name__ == '__main__':
    r = ReadExcel('cases.xlsx','Sheet1')
    data1 = r.read_data_line()
    print(data1)

import openpyxl
class Case:
    pass
class ReadExcel(object):
    def __init__(self,filename,sheetname):
        self.wb = openpyxl.load_workbook(filename)
        self.sh = self.wb[sheetname]
    def read_data_obj(self):
        """
        按行讀取數(shù)據(jù)  每條用例存儲(chǔ)在一個(gè)對(duì)象中
        :return:
        """
        rows_data = list(self.sh.rows)
        # print(rows_data)
        # 獲取表單的表頭信息
        titles = []
        for title in rows_data[0]:
            titles.append(title.value)
        # print(titles)
        # 定義一個(gè)空列表用來存儲(chǔ)測(cè)試用例
        cases = []
        for case in rows_data[1:]:
            # print(case)
            #創(chuàng)建一個(gè)Case類的對(duì)象，用來保存用例數(shù)據(jù)
            case_obj = Case()
            data = []
            for cell in case:  # 獲取一條測(cè)試用例數(shù)據(jù)
                # print(cell.value)
                # data.append(cell.value)
                # print(data)
                if isinstance(cell.value,str):  # 判斷該單元格是否為字符串，如果是字符串類型則需要使用eval();如果不是字符串類型則不需要使用eval()
                    data.append(eval(cell.value))
                else:
                    data.append(cell.value)
            # 將該條數(shù)據(jù)存放至cases中
            # print(dict(list(zip(titles,data))))
            case_data = list(zip(titles, data))
            # print(case_data)
            for i in case_data:
                setattr(case_obj,i[0],i[1])
            # print(case_obj)
            # print(case_obj.case_id,case_obj.data,case_obj.excepted)
            cases.append(case_obj)
        return cases
if  __name__ == '__main__':
    r = ReadExcel('cases.xlsx','Sheet1')
    res = r.read_data_obj()
    for i in res:
        print(i.caseid, i.excepted, i.data)

import openpyxl
class Case:
    pass
class ReadExcelZy(object):
    def __init__(self,filename,sheetname):
        self.wb = openpyxl.load_workbook(filename)
        self.sheet = self.wb[sheetname]
        # list1 參數(shù)為一個(gè)列表，傳入的是指定讀取數(shù)據(jù)的列,比如[1,2,3]
        # 每一行[1,3,5]列的數(shù)據(jù)，讀取出來就作為一條測(cè)試用例，放在字典中
        # 所有的用例放在列表中并且進(jìn)行返回
    def read_data(self,list1):
        """
        :param list1:  list--->要讀取列   list類型
        :return:    返回一個(gè)列表，每一個(gè)元素為一個(gè)用例（用例為dict類型）
        """
        # 獲取最大的行數(shù)
        max_r = self.sheet.max_row
        cases = []   #定義一個(gè)空列表，用來存放所有的用例數(shù)據(jù)
        titles = []   #定義一個(gè)空列表，用來存放表頭
        # 遍歷所有的行數(shù)據(jù)
        for row in range(1,max_r+1):
            if row != 1:      #判斷是否是第一行
                case_data = [] #定義一個(gè)空列表，用來存放該行的用例數(shù)據(jù)
                for column in list1:
                    info = self.sheet.cell(row,column).value
                    # print(info)
                    case_data.append(info)
                    # print(list(zip(titles,case_data)))
                case = dict(zip(titles,case_data))  #將該條數(shù)據(jù)和表頭進(jìn)行打包組合,作用相當(dāng)于dict(list(zip(titles,case_data)))
                # print(case)
                cases.append(case)
                # print(cases)
            else:   #獲取表頭數(shù)據(jù)
                for column in list1:
                    title = self.sheet.cell(row,column).value
                    titles.append(title)
                # print(titles)
        return cases
if __name__ == '__main__':
    r = ReadExcelZy("cases.xlsx","Sheet1")
    res = r.read_data([1,2,3])
    for o in res:
        print(o['caseid'],o['data'],o['excepted'])

import openpyxl
class Case:
    pass
class ReadExcelZy(object):
    def __init__(self,filename,sheetname):
        self.wb = openpyxl.load_workbook(filename)
        self.sheet = self.wb[sheetname]

        # list1 參數(shù)為一個(gè)列表，傳入的是指定讀取數(shù)據(jù)的列,比如[1,2,3]
        # 每一行[1,3,5]列的數(shù)據(jù)，讀取出來就作為一條測(cè)試用例，放在字典中
        # 所有的用例放在對(duì)象中并且進(jìn)行返回
    def read_data_obj(self,list2):
        max_r1 = self.sheet.max_row      #獲取最大行數(shù)
        cases = []
        titles = []      #用來存放表頭數(shù)據(jù)
        for row in range(1,max_r1+1):
            if row != 1:
                case_data = []
                for column in list2:
                    info = self.sheet.cell(row,column).value
                    # print(info)
                    case_data.append(info)
                cases_data = list(zip(titles,case_data))
                #將一條用例存到一個(gè)對(duì)象中（每一列對(duì)應(yīng)對(duì)象的一個(gè)屬性）
                case_obj = Case()
                for i in cases_data:
                    # print(i)
                    setattr(case_obj,i[0],i[1])
                # print(case_obj.caseid,case_obj.excepted,case_obj.data)
                cases.append(case_obj)
            else:
                for column in list2:
                    title = self.sheet.cell(row,column).value
                    titles.append(title)
        return cases
if __name__ == '__main__':
    r = ReadExcelZy("cases.xlsx","Sheet1")
    res = r.read_data_obj([1,2,3])
    for i in res:
        print(i.caseid,i.data,i.excepted)

import openpyxl
class Case:  # 這個(gè)類用來存儲(chǔ)用例的
    def __init__(self, attrs):
        """
        初始化用例
        :param attrs:zip類型——>[{key,value},(key1,value1)......]
        """
        for i in attrs:
            setattr(self, i[0], i[1])
class ReadExcel(object):
    def __init__(self, filename, sheetname):
        """
        定義需要打開的文件及表名
        :param filename:   文件名
        :param sheetname:  表名
        """
        self.wb = openpyxl.load_workbook(filename)
        self.sheet = self.wb[sheetname]
    def read_data_obj_new(self, list2):
        # 獲取最大行數(shù)
        max_r1 = self.sheet.max_row
        cases = []
        # 用來存放表頭數(shù)據(jù)
        titles = []
        for row in range(1, max_r1 + 1):
            if row != 1:
                case_data = []
                for column in list2:
                    info = self.sheet.cell(row, column).value
                    # print(info)
                    case_data.append(info)
                case = list(zip(titles, case_data))
                # 新建對(duì)象時(shí)，將對(duì)象傳給Case類
                case_obj = Case(case)
                # print(case_obj.caseid,case_obj.excepted,case_obj.data)
                cases.append(case_obj)
            else:
                # 獲取表頭
                for column in list2:
                    title = self.sheet.cell(row, column).value
                    titles.append(title)
                if None in titles:
                    raise ValueError("傳入的表頭的數(shù)據(jù)有顯示為空")
        return cases
if __name__ == '__main__':
    r = ReadExcel('cases.xlsx', 'Sheet1')
    res1 = r.read_data_obj_new([1, 2, 3])
    for i in res1:
        print(i.caseid, i.data, i.excepted)

import unittest
from python.register_new.register import register
from python.register_new.register_testcase_new import RegisterTestCase
from HTMLTestRunnerNew import HTMLTestRunner
class RegisterTestCase(unittest.TestCase):
    # 初始化測(cè)試用例
    def __init__(self,modethod_name,excepted,data):
        # modethod_name 測(cè)試用例方法名
        super().__init__(modethod_name)
        # excepted 測(cè)試用例的預(yù)期結(jié)果
        self.excepted = excepted
        # data 測(cè)試用例參數(shù)值
        self.data = data

    def setUp(self):
        print("準(zhǔn)備測(cè)試環(huán)境，執(zhí)行測(cè)試用例之前會(huì)執(zhí)行此操作")

    def tearDown(self):
        print("還原測(cè)試環(huán)境，執(zhí)行完測(cè)試用例之后會(huì)執(zhí)行此操作")

    def test_register(self):
        res = register(*self.data)
        try:
            self.assertEquals(self.excepted,res)
        except AssertionError as e:
            print("該條測(cè)試用例執(zhí)行未通通過")
            raise e
        else:
            print("該條測(cè)試用例執(zhí)行通過")

# 創(chuàng)建測(cè)試套件
suite = unittest.TestSuite()

# 將測(cè)試用例添加至測(cè)試套件中
case = [{'excepted':'{"code": 1, "msg": "注冊(cè)成功"}','data':'('python1', '123456','123456')'},
        {'excepted':'{"code": 0, "msg": "兩次密碼不一致"}','data':'('python1', '1234567','123456')'}]
for case in cases:
    suite.addTest(RegisterTestCase('test_register',case['excepted'],case['data']))

# 執(zhí)行測(cè)試套件，生成測(cè)試報(bào)告
with open("report.html",'wb') as f:
    runner = HTMLTestRunner(
        stream = f,
        verbosity = 2,
        title = 'python_test_report',
        description = '這是一份測(cè)試報(bào)告',
        tester = 'WL'
    )
    runner.run(suite)

import unittest
from python.register_new.register import register
from python.register_new.register_testcase_new import RegisterTestCase
from HTMLTestRunnerNew import HTMLTestRunner
from python.readexcel import ReadExcel


class RegisterTestCase(unittest.TestCase):
    # 初始化測(cè)試用例
    def __init__(self, modethod_name, excepted, data):
        # modethod_name 測(cè)試用例方法名
        super().__init__(modethod_name)
        # excepted 測(cè)試用例的預(yù)期結(jié)果
        self.excepted = excepted
        # data 測(cè)試用例參數(shù)值
        self.data = data

    def setUp(self):
        print("準(zhǔn)備測(cè)試環(huán)境，執(zhí)行測(cè)試用例之前會(huì)執(zhí)行此操作")

    def tearDown(self):
        print("還原測(cè)試環(huán)境，執(zhí)行完測(cè)試用例之后會(huì)執(zhí)行此操作")

    def test_register(self):
        res = register(*self.data)
        try:
            self.assertEquals(self.excepted, res)
        except AssertionError as e:
            print("該條測(cè)試用例執(zhí)行未通通過")
            raise e
        else:
            print("該條測(cè)試用例執(zhí)行通過")


# 創(chuàng)建測(cè)試套件
suite = unittest.TestSuite()
# 調(diào)用封裝好的讀取數(shù)據(jù)的Excel類，獲取測(cè)試數(shù)據(jù)
r1 = ReadExcel('cases.xlsx', 'Sheet1')
cases = r1.read_data_obj_new([2, 3])
# 將測(cè)試用例添加至測(cè)試套件中
for case in cases:
    # 需要使用eval()函數(shù)對(duì)except和data進(jìn)行自動(dòng)識(shí)別
    suite.addTest(RegisterTestCase('test_register', eval(case.excepted), eval(case.data)))
# 執(zhí)行測(cè)試套件，生成測(cè)試報(bào)告
with open("report.html", 'wb') as f:
    runner = HTMLTestRunner(
        stream=f,
        verbosity=2,
        title='python_test_report',
        description='這是一份測(cè)試報(bào)告',
        tester='WL')
    runner.run(suite)