【Pytest框架参数化】识别EXCEL测试用例

首先导入需要使用的模块：现在Python有很多识别EXCEL模块，例如：pandans，xlrd 本篇介绍xlrd

import xlrd
from xlutils import copy

class ReadExcel(object):
    # '''
    # 此类专门用于读取Excel
    # '''
    # __workbook用于存放excel文件的对象
    __workbook = None
    __sheet = None
    __print = False  # 用于开启全局打印

    def __init__(self, file_name, sheet_name):
        # '''
        # 类的初始化方法，在类似初始化的时候被调用
        # :param file_name: excel的文件名
        # :param sheet_name: excel中需要访问的sheet名
        # '''
        self.file_name = file_name
        ReadExcel.__workbook = xlrd.open_workbook(file_name)
        # 根据sheet名称获取sheet内容
        ReadExcel.__sheet = ReadExcel.__workbook.sheet_by_name(sheet_name)

    def get_number_of_rows(self):
        # '''
        #  获取表格中内容的最大行数
        # :return:__number_of_row
        # '''
        __rows_number = self.__sheet.nrows
        if ReadExcel.__print is True:
            print(__rows_number)
        return __rows_number

    def get_number_of_cols(self):
        # '''
        # 获取表格中内容最大的列数
        # :return: __number_of_cols
        # '''
        __cols_number = self.__sheet.ncols
        if ReadExcel.__print is True:
            print(__cols_number)
        return __cols_number

    def get_value_of_row(self, row_index):
        # """
        # 获取某一行的所有值构成列表
        # :param row_index: 行号
        # :return: 行内容组成的列表
        # """
        __row_value = self.__sheet.row_values(row_index)
        if ReadExcel.__print is True:
            print(__row_value)
        return __row_value

    def get_value_of_col(self, col_index):
        # """
        # 获取某一列的所有值构成的列表
        # :param col_index: 列号
        # :return: 列中内容组成的列表
        # """
        __col_value = self.__sheet.col_values(col_index)
        if ReadExcel.__print is True:
            print(__col_value)
        return __col_value

    def get_value_of_cell(self, row_index, col_index):
        # """
        # 获取某一个单元格中的值
        # :param row_index: 行号
        # :param col_index: 列号
        # :return: 单元格中的内容
        # """
        # 第row_index行 col_index列是内容
        __cell_value = self.__sheet.cell(row_index, col_index).value
        if ReadExcel.__print is True:
            print(__cell_value)
        return __cell_value

#实现从多少行到多少行识别EXCEL测试用例
    def get_dates(self, row_first: int, row_later: int):  # 获取想要行数对应列的值
        self.nrow = int(self.get_number_of_rows())
        self.row_first = row_first
        self.row_later = row_later
        self.row_index = self.get_value_of_row(0)
        if self.row_index == ['ID', 'NAME', 'URL', 'PATH', 'HEADERS', 'DATA', 'EXPECTED', 'ASSERT']:
            if type(self.row_first) == int and type(self.row_later) == int:
                if self.row_first > 0 or self.row_later <= self.nrow:
                    if self.row_first <= self.row_later:
                        data_list = []
                        for number in range(self.row_first, self.row_later + 1):
                            case_id = int(self.get_value_of_cell(number, 0))
                            case_name = self.get_value_of_cell(number, 1)
                            case_url = self.get_value_of_cell(number, 2)
                            case_path = self.get_value_of_cell(number, 3)
                            case_headers = (self.get_value_of_cell(number, 4))
                            case_data = self.get_value_of_cell(number, 5)
                            case_expected = self.get_value_of_cell(number, 6)
                            case_assert = self.get_value_of_cell(number, 7)
                            value = [case_id, case_name, case_url, case_path, case_headers, case_data, case_expected,
                                     case_assert]
                            res = tuple(value)
                            data_list.append(res)
                        return data_list
                        # print(data_list)
                    else:
                        return f"起始行：{row_first}不能大于最大行{row_later}"
                else:
                    return f"请检查数值大小应为：<=1<={self.nrow}"
            else:
                return f"请检查数据类型填写有误:{row_first}、{row_later}应均为整型数据"
        else:
            return f"请不要更改测试用例模板，您的模板为{self.row_index},与预期不符"

    def write(self, sheet, row, col, value):
        # ''''
        # 将数值写入到执行excel单元格中
        # :param new_workbook: 写入表名称
        # :param w_sheet：sheet栏名称
        # :row：行
        # :col：列
        # '''
        new_workbook = copy.copy(ReadExcel.__workbook)
        w_sheet = new_workbook.get_sheet(sheet)
        w_sheet.write(row, col, value)
        new_workbook.save(self.file_name)  # 保存文件