[开发测试]读写xls测试用例

import xlrd
import xlwt
from xlutils.copy import copy


class Case:

    """
    测试用例封装类
    """

    def __init__(self):
        self.id = None
        self.url = None
        self.data = None
        self.title = None
        self.method = None
        self.expected = None
        self.actual = None
        self.result = None


class DoExcel:
    file_name = None

    def __init__(self, file_name):
        try:
            # 操作的文件名称
            self.file_name = file_name
            # 实例化一个workbooK对象===表单对象
            self.workbook = xlrd.open_workbook(filename=file_name, formatting_info=True)
            # 异常处理如何做
        except FileNotFoundError as e:
            # 文件未找到异常处理
            print('{0} not found, please check file path'.format(file_name))
            raise e

    def get_cases(self, sheet_name):
        """
        获取测试用例
        """
        sheet_name = sheet_name
        sheet = self.workbook.sheet_by_name(sheet_name)  # 获取sheet
        max_row = sheet.nrows  # 获取sheet最大行数
        # print(max_row,sheet.row_values(1))
        cases = []  # 定义一个列表，用来存放即将要放进去的测试用例
        for r in range(1, max_row):  # for 循环，从第二行开始遍历===并且获取行数+1 左开又闭
            case = Case()  # 实例化一个case对象，用来存放测试数据===（之前用字典 需要dict键值对获取，现在优化一下用实例 用case.id都可以获取）
            case.id = sheet.cell(rowx=r, colx=0).value  # 取第r行，第1格的值
            case.title = sheet.cell(rowx=r, colx=1).value  # 取第r行，第2格的值
            case.url = sheet.cell(rowx=r, colx=2).value  # 取第r行，第3格的值
            case.data = sheet.cell(rowx=r, colx=3).value  # 取第r行，第4格的值
            case.method = sheet.cell(rowx=r, colx=4).value  # 取第r行，第5格的值
            case.expected = sheet.cell(rowx=r, colx=5).value  # 取第r行，第6格的值
            if type(case.expected) == int:
                case.expected = str(case.expected)
            cases.append(case)  # 将case放到cases 列表里面

        # return max_row, cases  # for 循环结束后返回cases列表===取值为cases[0].id
        return cases

    def write_result(self, sheet_name, row, actual, result):
        """
        写入excel测试用例对应结果
        """
        oldwb = xlrd.open_workbook(self.file_name, formatting_info=True)  # 先打开已存在的表
        newwb = copy(oldwb)  # 复制
        newws = newwb.get_sheet(sheet_name)
        newws.write(row, 6, label=actual)  # 写入实际结果
        if result == 'FAIL':
            pattern = xlwt.Pattern()  # Create the pattern
            pattern.pattern = xlwt.Pattern.SOLID_PATTERN  # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
            pattern.pattern_fore_colour = 5  # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red,
            # 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta,
            # 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue,
            # 19 = Dark Yellow , almost brown), 20 = Dark Magenta,
            # 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
            style = xlwt.XFStyle()  # Create the pattern
            style.pattern = pattern  # Add pattern to style
            newws.write(row, 7, label=result, style=style)  # 给错误单元加颜色
        else:
            newws.write(row, 7, label=result)  # 写入执行结果，PASS or FAIL
        newwb.save(self.file_name)


# if __name__ == '__main__':
    # from utilities.contants import case_file
    # import json
    # excel = DoExcel(case_file)
    # excel.write_result('GetBusinessErpEngFees', 1, 'response.message', 'FAIL')
    # max_row, body = excel.get_cases('GetDataByCustom')
    # json_str = json.loads(body[3].data)                     # python格式转变为字典格式
    # print(json_str['dbName'])