自动化测试怎样剖析excel文件?

2019年7月2日10:57:25自动化测试怎样剖析excel文件?已关闭评论 241

媒介

  自动化测试中我们寄存数据无非是运用文件或许数据库,那末文件可以是csv,xlsx,xml,以至是txt文件,一般excel文件往往是我们的首选,无论是编写测试用例照样寄存测试数据,excel都是很轻易的。那末本日我们就把分歧模块处置惩罚excel文件的要领做个总结,直接做封装,轻易我们今后直接运用,增添工作效率。

openpyxl

openpyxl是个第三方库,起首我们运用敕令 pip install openpyxl 直接装置

注:openpyxl操纵excel时,行号和列号都是从1最先盘算的

封装代码

"""
------------------------------------
@Time : 2019/5/13 18:00
@Auth : linux超
@File : ParseExcel.py
@IDE  : PyCharm
@Motto: Real warriors,dare to face the bleak warning,dare to face the incisive error!
------------------------------------
"""
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.styles.colors import BLACK
from collections import namedtuple


class ParseExcel(object):
    """剖析excel文件"""

    def __init__(self, filename, sheet_name=None):
        try:
            self.filename = filename
            self.sheet_name = sheet_name
            self.wb = load_workbook(self.filename)
            if self.sheet_name is None:
                self.work_sheet = self.wb.active
            else:
                self.work_sheet = self.wb[self.sheet_name]
        except FileNotFoundError as e:
            raise e

    def get_max_row_num(self):
        """猎取最大行号"""
        max_row_num = self.work_sheet.max_row
        return max_row_num

    def get_max_column_num(self):
        """猎取最大列号"""
        max_column = self.work_sheet.max_column
        return max_column

    def get_cell_value(self, coordinate=None, row=None, column=None):
        """猎取指定单元格的数据"""
        if coordinate is not None:
            try:
                return self.work_sheet[coordinate].value
            except Exception as e:
                raise e
        elif coordinate is None and row is not None and column is not None:
            if isinstance(row, int) and isinstance(column, int):
                return self.work_sheet.cell(row=row, column=column).value
            else:
                raise TypeError('row and column must be type int')
        else:
            raise Exception("Insufficient Coordinate of cell!")

    def get_row_value(self, row):
        """猎取某一行的数据"""
        column_num = self.get_max_column_num()
        row_value = []
        if isinstance(row, int):
            for column in range(1, column_num + 1):
                values_row = self.work_sheet.cell(row, column).value
                row_value.append(values_row)
            return row_value
        else:
            raise TypeError('row must be type int')

    def get_column_value(self, column):
        """猎取某一列数据"""
        row_num = self.get_max_column_num()
        column_value = []
        if isinstance(column, int):
            for row in range(1, row_num + 1):
                values_column = self.work_sheet.cell(row, column).value
                column_value.append(values_column)
            return column_value
        else:
            raise TypeError('column must be type int')

    def get_all_value_1(self):
        """猎取指定表单的一切数据(撤除表头)"""
        max_row_num = self.get_max_row_num()
        max_column = self.get_max_column_num()
        values = []
        for row in range(2, max_row_num + 1):
            value_list = []
            for column in range(1, max_column + 1):
                value = self.work_sheet.cell(row, column).value
                value_list.append(value)
            values.append(value_list)
        return values

    def get_all_value_2(self):
        """猎取指定表单的一切数据(撤除表头)"""
        rows_obj = self.work_sheet.iter_rows(min_row=2, max_row=self.work_sheet.max_row,
                                             values_only=True)  # 指定values_only 会直接提取数据不须要再运用cell().value
        values = []
        for row_tuple in rows_obj:
            value_list = []
            for value in row_tuple:
                value_list.append(value)
            values.append(value_list)
        return values

    def get_excel_title(self):
        """猎取sheet表头"""
        title_key = tuple(self.work_sheet.iter_rows(max_row=1, values_only=True))[0]
        return title_key

    def get_listdict_all_value(self):
        """猎取一切数据,返回嵌套字典的列表"""
        sheet_title = self.get_excel_title()
        all_values = self.get_all_value_2()
        value_list = []
        for value in all_values:
            value_list.append(dict(zip(sheet_title, value)))
        return value_list

    def get_list_nametuple_all_value(self):
        """猎取一切数据,返回嵌套定名元组的列表"""
        sheet_title = self.get_excel_title()
        values = self.get_all_value_2()

        excel = namedtuple('excel', sheet_title)
        value_list = []
        for value in values:
            e = excel(*value)
            value_list.append(e)
        return value_list

    def write_cell(self, row, column, value=None, bold=True, color=BLACK):
        """
        指定单元格写入数据
        :param work_sheet:
        :param row: 行号
        :param column: 列号
        :param value: 待写入数据
        :param bold: 加粗, 默许加粗
        :param color: 字体色彩,默许黑色
        :return:
        """
        try:
            if isinstance(row, int) and isinstance(column, int):
                cell_obj = self.work_sheet.cell(row, column)
                cell_obj.font = Font(color=color, bold=bold)
                cell_obj.value = value
                self.wb.save(self.filename)
            else:
                raise TypeError('row and column must be type int')
        except Exception as e:
            raise e

if __name__ == '__main__':
    pe = ParseExcel('testdata.xlsx')
    # sheet = pe.get_sheet_object('testcase')

    column_row = pe.get_max_column_num()
    print('最大列号:', column_row)
    max_row = pe.get_max_row_num()
    print('最大行号:', max_row)
    #
    cell_value_1 = pe.get_cell_value(row=2, column=3)
    print('第%d行, 第%d列的数据为: %s' % (2, 3, cell_value_1))

    cell_value_2 = pe.get_cell_value(coordinate='A5')
    print('A5单元格的数据为: {}'.format(cell_value_2))

    value_row = pe.get_row_value(3)
    print('第{}行的数据为:{}'.format(3, value_row))

    value_column = pe.get_column_value(2)
    print('第{}列的数据为:{}'.format(2, value_column))
    #
    values_1 = pe.get_all_value_1()
    print('第一种体式格局猎取一切数据\n', values_1)

    values_2 = pe.get_all_value_2()
    print('第二种体式格局猎取一切数据\n', values_2)

    title = pe.get_excel_title()
    print('表头为\n{}'.format(title))

    dict_value = pe.get_listdict_all_value()
    print('一切数据构成的嵌套字典的列表:\n', dict_value)
    #
    namedtuple_value = pe.get_list_nametuple_all_value()
    print('一切数据构成的嵌套定名元组的列表:\n', namedtuple_value)

    pe.write_cell(1, 2, 'Tc_title')

# add by linux超 at 2019/05/22 15:58

上面这个封装如若是用来同时操纵同一个excel文件的两个sheet写入数据时,会有点小bug(写完后你会发明两个表单有一个是没有数据的)

实在缘由很简单:分歧对象具有本身自力的属性, 当你写操纵的时刻实在每一个对象只针对本身的表单做了生存,以是末了一个对象写完数据后,只生存了本身的表单,其他的对象的表单现实是没有生存的。针对这个题目,对上面封装的代码举行了细微修改

"""
------------------------------------
@Time : 2019/5/22 9:11
@Auth : linux超
@File : ParseExcel.py
@IDE  : PyCharm
@Motto: Real warriors,dare to face the bleak warning,dare to face the incisive error!
------------------------------------
"""
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.styles.colors import BLACK
from collections import namedtuple


class ParseExcel(object):
    """剖析excel文件"""

    def __init__(self, filename):
        try:
            self.filename = filename
            self.__wb = load_workbook(self.filename)
        except FileNotFoundError as e:
            raise e

    def get_max_row_num(self, sheet_name):
        """猎取最大行号"""
        max_row_num = self.__wb[sheet_name].max_row
        return max_row_num

    def get_max_column_num(self, sheet_name):
        """猎取最大列号"""
        max_column = self.__wb[sheet_name].max_column
        return max_column

    def get_cell_value(self, sheet_name, coordinate=None, row=None, column=None):
        """猎取指定单元格的数据"""
        if coordinate is not None:
            try:
                return self.__wb[sheet_name][coordinate].value
            except Exception as e:
                raise e
        elif coordinate is None and row is not None and column is not None:
            if isinstance(row, int) and isinstance(column, int):
                return self.__wb[sheet_name].cell(row=row, column=column).value
            else:
                raise TypeError('row and column must be type int')
        else:
            raise Exception("Insufficient Coordinate of cell!")

    def get_row_value(self, sheet_name, row):
        """猎取某一行的数据"""
        column_num = self.get_max_column_num(sheet_name)
        row_value = []
        if isinstance(row, int):
            for column in range(1, column_num + 1):
                values_row = self.__wb[sheet_name].cell(row, column).value
                row_value.append(values_row)
            return row_value
        else:
            raise TypeError('row must be type int')

    def get_column_value(self, sheet_name, column):
        """猎取某一列数据"""
        row_num = self.get_max_column_num(sheet_name)
        column_value = []
        if isinstance(column, int):
            for row in range(1, row_num + 1):
                values_column = self.__wb[sheet_name].cell(row, column).value
                column_value.append(values_column)
            return column_value
        else:
            raise TypeError('column must be type int')

    def get_all_value_1(self, sheet_name):
        """猎取指定表单的一切数据(撤除表头)"""
        max_row_num = self.get_max_row_num(sheet_name)
        max_column = self.get_max_column_num(sheet_name)
        values = []
        for row in range(2, max_row_num + 1):
            value_list = []
            for column in range(1, max_column + 1):
                value = self.__wb[sheet_name].cell(row, column).value
                value_list.append(value)
            values.append(value_list)
        return values

    def get_all_value_2(self, sheet_name):
        """猎取指定表单的一切数据(撤除表头)"""
        rows_obj = self.__wb[sheet_name].iter_rows(min_row=2, max_row=self.__wb[sheet_name].max_row, values_only=True)
        values = []
        for row_tuple in rows_obj:
            value_list = []
            for value in row_tuple:
                value_list.append(value)
            values.append(value_list)
        return values

    def get_excel_title(self, sheet_name):
        """猎取sheet表头"""
        title_key = tuple(self.__wb[sheet_name].iter_rows(max_row=1, values_only=True))[0]
        return title_key

    def get_listdict_all_value(self, sheet_name):
        """猎取一切数据,返回嵌套字典的列表"""
        sheet_title = self.get_excel_title(sheet_name)
        all_values = self.get_all_value_2(sheet_name)
        value_list = []
        for value in all_values:
            value_list.append(dict(zip(sheet_title, value)))
        return value_list

    def get_list_nametuple_all_value(self, sheet_name):
        """猎取一切数据,返回嵌套定名元组的列表"""
        sheet_title = self.get_excel_title(sheet_name)
        values = self.get_all_value_2(sheet_name)
        excel = namedtuple('excel', sheet_title)
        value_list = []
        for value in values:
            e = excel(*value)
            value_list.append(e)
        return value_list

    def write_cell(self, sheet_name, row, column, value=None, bold=True, color=BLACK):
        if isinstance(row, int) and isinstance(column, int):
            try:
                cell_obj = self.__wb[sheet_name].cell(row, column)
                cell_obj.font = Font(color=color, bold=bold)
                cell_obj.value = value
                self.__wb.save(self.filename)
            except Exception as e:
                raise e
        else:
            raise TypeError('row and column must be type int')


if __name__ == '__main__':
    pe = ParseExcel('testdata.xlsx')
    print(pe.get_all_value_2('division'))
    print(pe.get_list_nametuple_all_value('division'))
    column_row = pe.get_max_column_num('division')
    print('最大列号:', column_row)
    max_row = pe.get_max_row_num('division')
    print('最大行号:', max_row)
    cell_value_1 = pe.get_cell_value('division', row=2, column=3)
    print('第%d行, 第%d列的数据为: %s' % (2, 3, cell_value_1))
    cell_value_2 = pe.get_cell_value('division', coordinate='A5')
    print('A5单元格的数据为: {}'.format(cell_value_2))
    value_row = pe.get_row_value('division', 3)
    print('第{}行的数据为:{}'.format(3, value_row))
    value_column = pe.get_column_value('division', 2)
    print('第{}列的数据为:{}'.format(2, value_column))
    values_1 = pe.get_all_value_1('division')
    print('第一种体式格局猎取一切数据\n', values_1)
    values_2 = pe.get_all_value_2('division')
    print('第二种体式格局猎取一切数据\n', values_2)
    title = pe.get_excel_title('division')
    print('表头为\n{}'.format(title))
    dict_value = pe.get_listdict_all_value('division')
    print('一切数据构成的嵌套字典的列表:\n', dict_value)
    namedtuple_value = pe.get_list_nametuple_all_value('division')
    print('一切数据构成的嵌套定名元组的列表:\n', namedtuple_value)
    pe.write_cell('division', 1, 2, 'Tc_title')

xlrd

装置xlrd,此模块只支撑读操纵, 若是要写须要运用xlwt或许运用xlutils合营xlrd, 然则运用xlwt只能对新的excel文件举行写操纵,没法对原有文件举行写, 以是这里挑选是用xlutils

然则另有一个题目就是,若是运用xlutils, 那末我们的excel文件须要以.xls 为后缀。由于以xlsx为后缀没法完成写,会报错(亲测,由于formatting_info参数还没有对新版本的xlsx的花样完成兼容)

注:xlrd操纵excel时,行号和列号都是从0最先盘算的

封装代码

"""
------------------------------------
@Time : 2019/5/13 21:22
@Auth : linux超
@File : ParseExcel_xlrd.py
@IDE  : PyCharm
@Motto: Real warriors,dare to face the bleak warning,dare to face the incisive error!
------------------------------------
"""
import xlrd
from xlutils import copy
from collections import namedtuple


class ParseExcel(object):
    # xlrd 剖析excel, 行号和列号都是从0最先的
    def __init__(self, filename, sheet):
        try:
            self.filename = filename
            self.sheet = sheet
            self.wb = xlrd.open_workbook(self.filename, formatting_info=True)
            if isinstance(sheet, str):
                self.sheet = self.wb.sheet_by_name(sheet)
            elif isinstance(sheet, int):
                self.sheet = self.wb.sheet_by_index(sheet)
            else:
                raise TypeError('sheet must be int or str')
        except Exception as e:
            raise e

    def get_max_row(self):
        """猎取表单的最大行号"""
        max_row_num = self.sheet.nrows
        return max_row_num

    def get_max_column(self):
        """猎取表单的最大列号"""
        min_row_num = self.sheet.ncols
        return min_row_num

    def get_cell_value(self, row, column):
        """猎取某个单元格的数据"""
        if isinstance(row, int) and isinstance(column, int):
            values = self.sheet.cell(row-1, column-1).value
            return values
        else:
            raise TypeError('row and column must be type int')

    def get_row_values(self, row):
        """猎取某一行的数据"""
        if isinstance(row, int):
            values = self.sheet.row_values(row-1)
            return values
        else:
            raise TypeError('row must be type int')

    def get_column_values(self, column):
        """猎取某一列的数据"""

        if isinstance(column, int):
            values = self.sheet.col_values(column-1)
            return values
        else:
            raise TypeError('column must be type int')

    def get_table_title(self):
        """猎取表头"""
        table_title = self.get_row_values(1)
        return table_title

    def get_all_values_dict(self):
        """猎取一切的数据,不包括表头,返回一个嵌套字典的列表"""
        max_row = self.get_max_row()
        table_title = self.get_table_title()
        value_list = []
        for row in range(2, max_row):
            values = self.get_row_values(row)
            value_list.append(dict(zip(table_title, values)))
        return value_list

    def get_all_values_nametuple(self):
        """猎取一切的数据,不包括表头,返回一个嵌套定名元组的列表"""
        table_title = self.get_table_title()
        max_row = self.get_max_row()
        excel = namedtuple('excel', table_title)
        value_list = []
        for row in range(2, max_row):
            values = self.get_row_values(row)
            e = excel(*values)
            value_list.append(e)
        return value_list

    def write_value(self, sheet_index, row, column, value):
        """写入某个单元格数据"""
        if isinstance(row, int) and isinstance(column, int):
            if isinstance(sheet_index, int):
                wb = copy.copy(self.wb)
                worksheet = wb.get_sheet(sheet_index)
                worksheet.write(row-1, column-1, value)
                wb.save(self.filename)
            else:
                raise TypeError('{} must be int'.format(sheet_index))
        else:
            raise TypeError('{} and {} must be int'.format(row, column))

if __name__ == '__main__':
    pe = ParseExcel('testdata.xls', 'testcase')
    print('最大行号:', pe.get_max_row())
    print('最大列号:', pe.get_max_column())
    print('第2行第3列数据:', pe.get_cell_value(2, 3))
    print('第2行数据', pe.get_row_values(2))
    print('第3列数据', pe.get_column_values(3))
    print('表头:', pe.get_table_title())
    print('一切的数据返回嵌套字典的列表:', pe.get_all_values_dict())
    print('一切的数据返回嵌套定名元组的列表:', pe.get_all_values_nametuple())
   pe.write_value(0, 1, 3, 'test')

pandas

pandas是一个做数据剖析的库, 老是觉得在自动化测试中运用pandas剖析excel文件读取数据有点牛鼎烹鸡,不论怎样吧,照样把pandas剖析excel文件写一下把

我这里只封装了读,写的话我这有点小题目,背面改好再追加代码吧。

请先pip install pandas装置pandas

封装代码

"""
------------------------------------
@Time : 2019/5/13 14:00
@Auth : linux超
@File : ParseExcel_pandas.py
@IDE  : PyCharm
@Motto: Real warriors,dare to face the bleak warning,dare to face the incisive error!
------------------------------------
"""
import pandas as pd


class ParseExcel(object):
    def __init__(self, filename, sheet_name=None):
        try:
            self.filename = filename
            self.sheet_name = sheet_name
            self.df = pd.read_excel(self.filename, self.sheet_name)
        except Exception as e:
            raise e

    def get_row_num(self):
        """猎取行号构成的列表, 从0最先的"""
        row_num_list = self.df.index.values
        return row_num_list

    def get_cell_value(self, row, column):
        """猎取某一个单元格的数据"""
        try:
            if isinstance(row, int) and isinstance(column, int):
                cell_value = self.df.ix[row-2, column-1] # ix的行参数是依照有用数据行,且从0最先
                return cell_value
            else:
                raise TypeError('row and column must be type int')
        except Exception as e:
            raise e

    def get_table_title(self):
        """猎取表头, 返回列表"""
        table_title = self.df.columns.values
        return table_title

    def get_row_value(self, row):
        """猎取某一行的数据, 行号从1最先"""
        try:
            if isinstance(row, int):
                row_data = self.df.ix[row-2].values
                return row_data
            else:
                raise TypeError('row must be type int')
        except Exception as e:
            raise e

    def get_column_value(self, col_name):
        """猎取某一列数据"""
        try:
            if isinstance(col_name, str):
                col_data = self.df[col_name].values
                return col_data
            else:
                raise TypeError('col_name must be type str')
        except Exception as e:
            raise e
        
    def get_all_value(self):
        """猎取一切的数据,不包括表头, 返回嵌套字典的列表"""
        rows_num = self.get_row_num()
        table_title = self.get_table_title()
        values_list = []
        for i in rows_num:
            row_data = self.df.ix[i, table_title].to_dict()
            values_list.append(row_data)
        return values_list


if __name__ == '__main__':
    pe = ParseExcel('testdata.xlsx', 'testcase')
    print(pe.get_row_num())
    print(pe.get_table_title())
    print(pe.get_all_value())
    print(pe.get_row_value(2))
    print(pe.get_cell_value(2, 3))
    print(pe.get_column_value('Tc_title'))

总结

运用了3种要领,4个库 xlrd,openpyxl,xlwt,pandas 操纵excel文件,个人觉得照样运用openpyxl对照适合在自动化中运用,固然分歧人有分歧挑选,用哪一个区分也不是很大。

以上3种要领,都可以拿来直接运用,不须要再做封装了 !

avatar