• 一篇文章教你自动化测试如何解析excel文件？

    前言
    自动化测试中我们存放数据无非是使用文件或者数据库，那么文件可以是csv，xlsx，xml，甚至是txt文件，通常excel文件往往是我们的首选，无论是编写测试用例还是存放测试数据，excel都是很方便的。那么今天我们就把不同模块处理excel文件的方法做个总结，直接做封装，方便我们以后直接使用，增加工作效率。

    一、openpyxl
    openpyxl是个第三方库，首先我们使用命令 pip install openpyxl 直接安装

    注：openpyxl操作excel时，行号和列号都是从1开始计算的

    封装代码

    1. """
    2. from openpyxl import load_workbook
    3. from openpyxl.styles import Font
    4. from openpyxl.styles.colors import BLACK
    5. from collections import namedtuple
    6. class ParseExcel(object):
    7.     """解析excel文件"""
    8.     def __init__(self, filename, sheet_name=None):
    9.         try:
    10.             self.filename = filename
    11.             self.sheet_name = sheet_name
    12.             self.wb = load_workbook(self.filename)
    13.             if self.sheet_name is None:
    14.                 self.work_sheet = self.wb.active
    15.             else:
    16.                 self.work_sheet = self.wb[self.sheet_name]
    17.         except FileNotFoundError as e:
    18.             raise e
    19.     def get_max_row_num(self):
    20.         """获取最大行号"""
    21.         max_row_num = self.work_sheet.max_row
    22.         return max_row_num
    23.     def get_max_column_num(self):
    24.         """获取最大列号"""
    25.         max_column = self.work_sheet.max_column
    26.         return max_column
    27.     def get_cell_value(self, coordinate=None, row=None, column=None):
    28.         """获取指定单元格的数据"""
    29.         if coordinate is not None:
    30.             try:
    31.                 return self.work_sheet[coordinate].value
    32.             except Exception as e:
    33.                 raise e
    34.         elif coordinate is None and row is not None and column is not None:
    35.             if isinstance(row, int) and isinstance(column, int):
    36.                 return self.work_sheet.cell(row=row, column=column).value
    37.             else:
    38.                 raise TypeError('row and column must be type int')
    39.         else:
    40.             raise Exception("Insufficient Coordinate of cell!")
    41.     def get_row_value(self, row):
    42.         """获取某一行的数据"""
    43.         column_num = self.get_max_column_num()
    44.         row_value = []
    45.         if isinstance(row, int):
    46.             for column in range(1, column_num + 1):
    47.                 values_row = self.work_sheet.cell(row, column).value
    48.                 row_value.append(values_row)
    49.             return row_value
    50.         else:
    51.             raise TypeError('row must be type int')
    52.     def get_column_value(self, column):
    53.         """获取某一列数据"""
    54.         row_num = self.get_max_column_num()
    55.         column_value = []
    56.         if isinstance(column, int):
    57.             for row in range(1, row_num + 1):
    58.                 values_column = self.work_sheet.cell(row, column).value
    59.                 column_value.append(values_column)
    60.             return column_value
    61.         else:
    62.             raise TypeError('column must be type int')
    63.     def get_all_value_1(self):
    64.         """获取指定表单的所有数据(除去表头)"""
    65.         max_row_num = self.get_max_row_num()
    66.         max_column = self.get_max_column_num()
    67.         values = []
    68.         for row in range(2, max_row_num + 1):
    69.             value_list = []
    70.             for column in range(1, max_column + 1):
    71.                 value = self.work_sheet.cell(row, column).value
    72.                 value_list.append(value)
    73.             values.append(value_list)
    74.         return values
    75.     def get_all_value_2(self):
    76.         """获取指定表单的所有数据(除去表头)"""
    77.         rows_obj = self.work_sheet.iter_rows(min_row=2, max_row=self.work_sheet.max_row,
    78.                                              values_only=True)  # 指定values_only 会直接提取数据不需要再使用cell().value
    79.         values = []
    80.         for row_tuple in rows_obj:
    81.             value_list = []
    82.             for value in row_tuple:
    83.                 value_list.append(value)
    84.             values.append(value_list)
    85.         return values
    86.     def get_excel_title(self):
    87.         """获取sheet表头"""
    88.         title_key = tuple(self.work_sheet.iter_rows(max_row=1, values_only=True))[0]
    89.         return title_key
    90.     def get_listdict_all_value(self):
    91.         """获取所有数据，返回嵌套字典的列表"""
    92.         sheet_title = self.get_excel_title()
    93.         all_values = self.get_all_value_2()
    94.         value_list = []
    95.         for value in all_values:
    96.             value_list.append(dict(zip(sheet_title, value)))
    97.         return value_list
    98.     def get_list_nametuple_all_value(self):
    99.         """获取所有数据，返回嵌套命名元组的列表"""
    100.         sheet_title = self.get_excel_title()
    101.         values = self.get_all_value_2()
    102.         excel = namedtuple('excel', sheet_title)
    103.         value_list = []
    104.         for value in values:
    105.             e = excel(*value)
    106.             value_list.append(e)
    107.         return value_list
    108.     def write_cell(self, row, column, value=None, bold=True, color=BLACK):
    109.         """
    110.         指定单元格写入数据
    111.         :param work_sheet:
    112.         :param row: 行号
    113.         :param column: 列号
    114.         :param value: 待写入数据
    115.         :param bold: 加粗， 默认加粗
    116.         :param color: 字体颜色，默认黑色
    117.         :return:
    118.         """
    119.         try:
    120.             if isinstance(row, int) and isinstance(column, int):
    121.                 cell_obj = self.work_sheet.cell(row, column)
    122.                 cell_obj.font = Font(color=color, bold=bold)
    123.                 cell_obj.value = value
    124.                 self.wb.save(self.filename)
    125.             else:
    126.                 raise TypeError('row and column must be type int')
    127.         except Exception as e:
    128.             raise e
    129. if __name__ == '__main__':
    130.     pe = ParseExcel('testdata.xlsx')
    131.     # sheet = pe.get_sheet_object('testcase')
    132.     column_row = pe.get_max_column_num()
    133.     print('最大列号:', column_row)
    134.     max_row = pe.get_max_row_num()
    135.     print('最大行号:', max_row)
    136.     #
    137.     cell_value_1 = pe.get_cell_value(row=2, column=3)
    138.     print('第%d行, 第%d列的数据为: %s' % (2, 3, cell_value_1))
    139.     cell_value_2 = pe.get_cell_value(coordinate='A5')
    140.     print('A5单元格的数据为: {}'.format(cell_value_2))
    141.     value_row = pe.get_row_value(3)
    142.     print('第{}行的数据为:{}'.format(3, value_row))
    143.     value_column = pe.get_column_value(2)
    144.     print('第{}列的数据为:{}'.format(2, value_column))
    145.     #
    146.     values_1 = pe.get_all_value_1()
    147.     print('第一种方式获取所有数据\n', values_1)
    148.     values_2 = pe.get_all_value_2()
    149.     print('第二种方式获取所有数据\n', values_2)
    150.     title = pe.get_excel_title()
    151.     print('表头为\n{}'.format(title))
    152.     dict_value = pe.get_listdict_all_value()
    153.     print('所有数据组成的嵌套字典的列表:\n', dict_value)
    154.     #
    155.     namedtuple_value = pe.get_list_nametuple_all_value()
    156.     print('所有数据组成的嵌套命名元组的列表:\n', namedtuple_value)
    157.     pe.write_cell(1, 2, 'Tc_title')

    上面这个封装如如果用来同时操作同一个excel文件的两个sheet写入数据时，会有点小bug（写完后你会发现两个表单有一个是没有数据的）

    其实原因很简单：不同对象拥有自己独立的属性， 当你写操作的时候其实每个对象只针对自己的表单做了保存，所以最后一个对象写完数据后，只保存了自己的表单，其他的对象的表单实际是没有保存的。针对这个问题，对上面封装的代码进行了轻微改动

    1. """
    2. from openpyxl import load_workbook
    3. from openpyxl.styles import Font
    4. from openpyxl.styles.colors import BLACK
    5. from collections import namedtuple
    6.  
    7.  
    8. class ParseExcel(object):
    9.     """解析excel文件"""
    10.  
    11.     def __init__(self, filename):
    12.         try:
    13.             self.filename = filename
    14.             self.__wb = load_workbook(self.filename)
    15.         except FileNotFoundError as e:
    16.             raise e
    17.  
    18.     def get_max_row_num(self, sheet_name):
    19.         """获取最大行号"""
    20.         max_row_num = self.__wb[sheet_name].max_row
    21.         return max_row_num
    22.  
    23.     def get_max_column_num(self, sheet_name):
    24.         """获取最大列号"""
    25.         max_column = self.__wb[sheet_name].max_column
    26.         return max_column
    27.  
    28.     def get_cell_value(self, sheet_name, coordinate=None, row=None, column=None):
    29.         """获取指定单元格的数据"""
    30.         if coordinate is not None:
    31.             try:
    32.                 return self.__wb[sheet_name][coordinate].value
    33.             except Exception as e:
    34.                 raise e
    35.         elif coordinate is None and row is not None and column is not None:
    36.             if isinstance(row, int) and isinstance(column, int):
    37.                 return self.__wb[sheet_name].cell(row=row, column=column).value
    38.             else:
    39.                 raise TypeError('row and column must be type int')
    40.         else:
    41.             raise Exception("Insufficient Coordinate of cell!")
    42.  
    43.     def get_row_value(self, sheet_name, row):
    44.         """获取某一行的数据"""
    45.         column_num = self.get_max_column_num(sheet_name)
    46.         row_value = []
    47.         if isinstance(row, int):
    48.             for column in range(1, column_num + 1):
    49.                 values_row = self.__wb[sheet_name].cell(row, column).value
    50.                 row_value.append(values_row)
    51.             return row_value
    52.         else:
    53.             raise TypeError('row must be type int')
    54.  
    55.     def get_column_value(self, sheet_name, column):
    56.         """获取某一列数据"""
    57.         row_num = self.get_max_column_num(sheet_name)
    58.         column_value = []
    59.         if isinstance(column, int):
    60.             for row in range(1, row_num + 1):
    61.                 values_column = self.__wb[sheet_name].cell(row, column).value
    62.                 column_value.append(values_column)
    63.             return column_value
    64.         else:
    65.             raise TypeError('column must be type int')
    66.  
    67.     def get_all_value_1(self, sheet_name):
    68.         """获取指定表单的所有数据(除去表头)"""
    69.         max_row_num = self.get_max_row_num(sheet_name)
    70.         max_column = self.get_max_column_num(sheet_name)
    71.         values = []
    72.         for row in range(2, max_row_num + 1):
    73.             value_list = []
    74.             for column in range(1, max_column + 1):
    75.                 value = self.__wb[sheet_name].cell(row, column).value
    76.                 value_list.append(value)
    77.             values.append(value_list)
    78.         return values
    79.  
    80.     def get_all_value_2(self, sheet_name):
    81.         """获取指定表单的所有数据(除去表头)"""
    82.         rows_obj = self.__wb[sheet_name].iter_rows(min_row=2, max_row=self.__wb[sheet_name].max_row, values_only=True)
    83.         values = []
    84.         for row_tuple in rows_obj:
    85.             value_list = []
    86.             for value in row_tuple:
    87.                 value_list.append(value)
    88.             values.append(value_list)
    89.         return values
    90.  
    91.     def get_excel_title(self, sheet_name):
    92.         """获取sheet表头"""
    93.         title_key = tuple(self.__wb[sheet_name].iter_rows(max_row=1, values_only=True))[0]
    94.         return title_key
    95.  
    96.     def get_listdict_all_value(self, sheet_name):
    97.         """获取所有数据，返回嵌套字典的列表"""
    98.         sheet_title = self.get_excel_title(sheet_name)
    99.         all_values = self.get_all_value_2(sheet_name)
    100.         value_list = []
    101.         for value in all_values:
    102.             value_list.append(dict(zip(sheet_title, value)))
    103.         return value_list
    104.  
    105.     def get_list_nametuple_all_value(self, sheet_name):
    106.         """获取所有数据，返回嵌套命名元组的列表"""
    107.         sheet_title = self.get_excel_title(sheet_name)
    108.         values = self.get_all_value_2(sheet_name)
    109.         excel = namedtuple('excel', sheet_title)
    110.         value_list = []
    111.         for value in values:
    112.             e = excel(*value)
    113.             value_list.append(e)
    114.         return value_list
    115.  
    116.     def write_cell(self, sheet_name, row, column, value=None, bold=True, color=BLACK):
    117.         if isinstance(row, int) and isinstance(column, int):
    118.             try:
    119.                 cell_obj = self.__wb[sheet_name].cell(row, column)
    120.                 cell_obj.font = Font(color=color, bold=bold)
    121.                 cell_obj.value = value
    122.                 self.__wb.save(self.filename)
    123.             except Exception as e:
    124.                 raise e
    125.         else:
    126.             raise TypeError('row and column must be type int')
    127.  
    128.  
    129. if __name__ == '__main__':
    130.     pe = ParseExcel('testdata.xlsx')
    131.     print(pe.get_all_value_2('division'))
    132.     print(pe.get_list_nametuple_all_value('division'))
    133.     column_row = pe.get_max_column_num('division')
    134.     print('最大列号:', column_row)
    135.     max_row = pe.get_max_row_num('division')
    136.     print('最大行号:', max_row)
    137.     cell_value_1 = pe.get_cell_value('division', row=2, column=3)
    138.     print('第%d行, 第%d列的数据为: %s' % (2, 3, cell_value_1))
    139.     cell_value_2 = pe.get_cell_value('division', coordinate='A5')
    140.     print('A5单元格的数据为: {}'.format(cell_value_2))
    141.     value_row = pe.get_row_value('division', 3)
    142.     print('第{}行的数据为:{}'.format(3, value_row))
    143.     value_column = pe.get_column_value('division', 2)
    144.     print('第{}列的数据为:{}'.format(2, value_column))
    145.     values_1 = pe.get_all_value_1('division')
    146.     print('第一种方式获取所有数据\n', values_1)
    147.     values_2 = pe.get_all_value_2('division')
    148.     print('第二种方式获取所有数据\n', values_2)
    149.     title = pe.get_excel_title('division')
    150.     print('表头为\n{}'.format(title))
    151.     dict_value = pe.get_listdict_all_value('division')
    152.     print('所有数据组成的嵌套字典的列表:\n', dict_value)
    153.     namedtuple_value = pe.get_list_nametuple_all_value('division')
    154.     print('所有数据组成的嵌套命名元组的列表:\n', namedtuple_value)
    155.     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开始计算的

    封装代码

    1. """
    2. import xlrd
    3. from xlutils import copy
    4. from collections import namedtuple
    5.  
    6.  
    7. class ParseExcel(object):
    8.     # xlrd 解析excel， 行号和列号都是从0开始的
    9.     def __init__(self, filename, sheet):
    10.         try:
    11.             self.filename = filename
    12.             self.sheet = sheet
    13.             self.wb = xlrd.open_workbook(self.filename, formatting_info=True)
    14.             if isinstance(sheet, str):
    15.                 self.sheet = self.wb.sheet_by_name(sheet)
    16.             elif isinstance(sheet, int):
    17.                 self.sheet = self.wb.sheet_by_index(sheet)
    18.             else:
    19.                 raise TypeError('sheet must be int or str')
    20.         except Exception as e:
    21.             raise e
    22.  
    23.     def get_max_row(self):
    24.         """获取表单的最大行号"""
    25.         max_row_num = self.sheet.nrows
    26.         return max_row_num
    27.  
    28.     def get_max_column(self):
    29.         """获取表单的最大列号"""
    30.         min_row_num = self.sheet.ncols
    31.         return min_row_num
    32.  
    33.     def get_cell_value(self, row, column):
    34.         """获取某个单元格的数据"""
    35.         if isinstance(row, int) and isinstance(column, int):
    36.             values = self.sheet.cell(row-1, column-1).value
    37.             return values
    38.         else:
    39.             raise TypeError('row and column must be type int')
    40.  
    41.     def get_row_values(self, row):
    42.         """获取某一行的数据"""
    43.         if isinstance(row, int):
    44.             values = self.sheet.row_values(row-1)
    45.             return values
    46.         else:
    47.             raise TypeError('row must be type int')
    48.  
    49.     def get_column_values(self, column):
    50.         """获取某一列的数据"""
    51.  
    52.         if isinstance(column, int):
    53.             values = self.sheet.col_values(column-1)
    54.             return values
    55.         else:
    56.             raise TypeError('column must be type int')
    57.  
    58.     def get_table_title(self):
    59.         """获取表头"""
    60.         table_title = self.get_row_values(1)
    61.         return table_title
    62.  
    63.     def get_all_values_dict(self):
    64.         """获取所有的数据，不包括表头，返回一个嵌套字典的列表"""
    65.         max_row = self.get_max_row()
    66.         table_title = self.get_table_title()
    67.         value_list = []
    68.         for row in range(2, max_row):
    69.             values = self.get_row_values(row)
    70.             value_list.append(dict(zip(table_title, values)))
    71.         return value_list
    72.  
    73.     def get_all_values_nametuple(self):
    74.         """获取所有的数据，不包括表头，返回一个嵌套命名元组的列表"""
    75.         table_title = self.get_table_title()
    76.         max_row = self.get_max_row()
    77.         excel = namedtuple('excel', table_title)
    78.         value_list = []
    79.         for row in range(2, max_row):
    80.             values = self.get_row_values(row)
    81.             e = excel(*values)
    82.             value_list.append(e)
    83.         return value_list
    84.  
    85.     def write_value(self, sheet_index, row, column, value):
    86.         """写入某个单元格数据"""
    87.         if isinstance(row, int) and isinstance(column, int):
    88.             if isinstance(sheet_index, int):
    89.                 wb = copy.copy(self.wb)
    90.                 worksheet = wb.get_sheet(sheet_index)
    91.                 worksheet.write(row-1, column-1, value)
    92.                 wb.save(self.filename)
    93.             else:
    94.                 raise TypeError('{} must be int'.format(sheet_index))
    95.         else:
    96.             raise TypeError('{} and {} must be int'.format(row, column))
    97.  
    98. if __name__ == '__main__':
    99.     pe = ParseExcel('testdata.xls', 'testcase')
    100.     print('最大行号:', pe.get_max_row())
    101.     print('最大列号:', pe.get_max_column())
    102.     print('第2行第3列数据:', pe.get_cell_value(2, 3))
    103.     print('第2行数据', pe.get_row_values(2))
    104.     print('第3列数据', pe.get_column_values(3))
    105.     print('表头:', pe.get_table_title())
    106.     print('所有的数据返回嵌套字典的列表:', pe.get_all_values_dict())
    107.     print('所有的数据返回嵌套命名元组的列表:', pe.get_all_values_nametuple())
    108. 　　 pe.write_value(0, 1, 3, 'test')

    三、pandas
    pandas是一个做数据分析的库， 总是感觉在自动化测试中使用pandas解析excel文件读取数据有点大材小用，不论怎样吧，还是把pandas解析excel文件写一下把

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

    请先pip install pandas安装pandas

    封装代码

    1. """
    2. import pandas as pd
    3. class ParseExcel(object):
    4.     def __init__(self, filename, sheet_name=None):
    5.         try:
    6.             self.filename = filename
    7.             self.sheet_name = sheet_name
    8.             self.df = pd.read_excel(self.filename, self.sheet_name)
    9.         except Exception as e:
    10.             raise e
    11.     def get_row_num(self):
    12.         """获取行号组成的列表, 从0开始的"""
    13.         row_num_list = self.df.index.values
    14.         return row_num_list
    15.     def get_cell_value(self, row, column):
    16.         """获取某一个单元格的数据"""
    17.         try:
    18.             if isinstance(row, int) and isinstance(column, int):
    19.                 cell_value = self.df.ix[row-2, column-1] # ix的行参数是按照有效数据行，且从0开始
    20.                 return cell_value
    21.             else:
    22.                 raise TypeError('row and column must be type int')
    23.         except Exception as e:
    24.             raise e
    25.     def get_table_title(self):
    26.         """获取表头， 返回列表"""
    27.         table_title = self.df.columns.values
    28.         return table_title
    29.     def get_row_value(self, row):
    30.         """获取某一行的数据， 行号从1开始"""
    31.         try:
    32.             if isinstance(row, int):
    33.                 row_data = self.df.ix[row-2].values
    34.                 return row_data
    35.             else:
    36.                 raise TypeError('row must be type int')
    37.         except Exception as e:
    38.             raise e
    39.     def get_column_value(self, col_name):
    40.         """获取某一列数据"""
    41.         try:
    42.             if isinstance(col_name, str):
    43.                 col_data = self.df[col_name].values
    44.                 return col_data
    45.             else:
    46.                 raise TypeError('col_name must be type str')
    47.         except Exception as e:
    48.             raise e
    49.         
    50.     def get_all_value(self):
    51.         """获取所有的数据，不包括表头, 返回嵌套字典的列表"""
    52.         rows_num = self.get_row_num()
    53.         table_title = self.get_table_title()
    54.         values_list = []
    55.         for i in rows_num:
    56.             row_data = self.df.ix[i, table_title].to_dict()
    57.             values_list.append(row_data)
    58.         return values_list
    59. if __name__ == '__main__':
    60.     pe = ParseExcel('testdata.xlsx', 'testcase')
    61.     print(pe.get_row_num())
    62.     print(pe.get_table_title())
    63.     print(pe.get_all_value())
    64.     print(pe.get_row_value(2))
    65.     print(pe.get_cell_value(2, 3))
    66.     print(pe.get_column_value('Tc_title'))

    总结
    使用了3种方法，4个库 xlrd，openpyxl，xlwt，pandas 操作excel文件，个人感觉还是使用openpyxl比较适合在自动化中使用，当然不同人有不同选择，用哪个区别也不是很大。

    以上3种方法，都可以拿来直接使用，不需要再做封装了 ！

    总结：

    感谢每一个认真阅读我文章的人！！！

    作为一位过来人也是希望大家少走一些弯路，如果你不想再体验一次学习时找不到资料，没人解答问题，坚持几天便放弃的感受的话，在这里我给大家分享一些自动化测试的学习资源，希望能给你前进的路上带来帮助。

     文档获取方式：

    加入我的软件测试交流群：632880530免费获取~（同行大佬一起学术交流，每晚都有大佬直播分享技术知识点）

    这份文档，对于想从事【软件测试】的朋友来说应该是最全面最完整的备战仓库，这个仓库也陪伴我走过了最艰难的路程，希望也能帮助到你！

    以上均可以分享，只需要你搜索vx公众号：程序员雨果，即可免费领取

  • 相关阅读:
    Linux文件管理知识:文本处理
    SMOKE 单目相机 3D目标检测【训练模型】
    IEEE802.11v协议介绍
    经典卷积神经网络LeNet-5、AlexNet、VGG-16
    【计算机视觉】相机
    React Hook实现多列picker-view组件
    qt中d指针
    力扣第700题 二叉搜索树中的搜索 c++ 在搜索中搜索
    硬件加速绘制基础知识
    力扣hot100——第6天：32最长有效括号、33搜索旋转排序数组、34在排序数组中查找元素的第一个和最后一个位置
  • 原文地址：https://blog.csdn.net/2301_77645834/article/details/133933263