前言

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

一、openpyxl

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

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

封装代码

"""nfrom openpyxl import load_workbooknfrom openpyxl.styles import Fontnfrom openpyxl.styles.colors import BLACKnfrom collections import namedtuplennnclass ParseExcel(object):n """解析excel文件"""nn def __init__(self, filename, sheet_name=None):n try:n self.filename = filenamen self.sheet_name = sheet_namen self.wb = load_workbook(self.filename)n if self.sheet_name is None:n self.work_sheet = self.wb.activen else:n self.work_sheet = self.wb[self.sheet_name]n except FileNotFoundError as e:n raise enn def get_max_row_num(self):n """获取最大行号"""n max_row_num = self.work_sheet.max_rown return max_row_numnn def get_max_column_num(self):n """获取最大列号"""n max_column = self.work_sheet.max_columnn return max_columnnn def get_cell_value(self, coordinate=None, row=None, column=None):n """获取指定单元格的数据"""n if coordinate is not None:n try:n return self.work_sheet[coordinate].valuen except Exception as e:n raise en elif coordinate is None and row is not None and column is not None:n if isinstance(row, int) and isinstance(column, int):n return self.work_sheet.cell(row=row, column=column).valuen else:n raise TypeError('row and column must be type int')n else:n raise Exception("Insufficient Coordinate of cell!")nn def get_row_value(self, row):n """获取某一行的数据"""n column_num = self.get_max_column_num()n row_value = []n if isinstance(row, int):n for column in range(1, column_num + 1):n values_row = self.work_sheet.cell(row, column).valuen row_value.append(values_row)n return row_valuen else:n raise TypeError('row must be type int')nn def get_column_value(self, column):n """获取某一列数据"""n row_num = self.get_max_column_num()n column_value = []n if isinstance(column, int):n for row in range(1, row_num + 1):n values_column = self.work_sheet.cell(row, column).valuen column_value.append(values_column)n return column_valuen else:n raise TypeError('column must be type int')nn def get_all_value_1(self):n """获取指定表单的所有数据(除去表头)"""n max_row_num = self.get_max_row_num()n max_column = self.get_max_column_num()n values = []n for row in range(2, max_row_num + 1):n value_list = []n for column in range(1, max_column + 1):n value = self.work_sheet.cell(row, column).valuen value_list.append(value)n values.append(value_list)n return valuesnn def get_all_value_2(self):n """获取指定表单的所有数据(除去表头)"""n rows_obj = self.work_sheet.iter_rows(min_row=2, max_row=self.work_sheet.max_row,n values_only=True) # 指定values_only 会直接提取数据不需要再使用cell().valuen values = []n for row_tuple in rows_obj:n value_list = []n for value in row_tuple:n value_list.append(value)n values.append(value_list)n return valuesnn def get_excel_title(self):n """获取sheet表头"""n title_key = tuple(self.work_sheet.iter_rows(max_row=1, values_only=True))[0]n return title_keynn def get_listdict_all_value(self):n """获取所有数据，返回嵌套字典的列表"""n sheet_title = self.get_excel_title()n all_values = self.get_all_value_2()n value_list = []n for value in all_values:n value_list.append(dict(zip(sheet_title, value)))n return value_listnn def get_list_nametuple_all_value(self):n """获取所有数据，返回嵌套命名元组的列表"""n sheet_title = self.get_excel_title()n values = self.get_all_value_2()nn excel = namedtuple('excel', sheet_title)n value_list = []n for value in values:n e = excel(*value)n value_list.append(e)n return value_listnn def write_cell(self, row, column, value=None, bold=True, color=BLACK):n """n 指定单元格写入数据n :param work_sheet:n :param row: 行号n :param column: 列号n :param value: 待写入数据n :param bold: 加粗， 默认加粗n :param color: 字体颜色，默认黑色n :return:n """n try:n if isinstance(row, int) and isinstance(column, int):n cell_obj = self.work_sheet.cell(row, column)n cell_obj.font = Font(color=color, bold=bold)n cell_obj.value = valuen self.wb.save(self.filename)n else:n raise TypeError('row and column must be type int')n except Exception as e:n raise ennif __name__ == '__main__':n pe = ParseExcel('testdata.xlsx')n # sheet = pe.get_sheet_object('testcase')nn column_row = pe.get_max_column_num()n print('最大列号:', column_row)n max_row = pe.get_max_row_num()n print('最大行号:', max_row)n #n cell_value_1 = pe.get_cell_value(row=2, column=3)n print('第%d行, 第%d列的数据为: %s' % (2, 3, cell_value_1))nn cell_value_2 = pe.get_cell_value(coordinate='A5')n print('A5单元格的数据为: {}'.format(cell_value_2))nn value_row = pe.get_row_value(3)n print('第{}行的数据为:{}'.format(3, value_row))nn value_column = pe.get_column_value(2)n print('第{}列的数据为:{}'.format(2, value_column))n #n values_1 = pe.get_all_value_1()n print('第一种方式获取所有数据n', values_1)nn values_2 = pe.get_all_value_2()n print('第二种方式获取所有数据n', values_2)nn title = pe.get_excel_title()n print('表头为n{}'.format(title))nn dict_value = pe.get_listdict_all_value()n print('所有数据组成的嵌套字典的列表:n', dict_value)n #n namedtuple_value = pe.get_list_nametuple_all_value()n print('所有数据组成的嵌套命名元组的列表:n', namedtuple_value)nn pe.write_cell(1, 2, 'Tc_title')

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

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

"""nfrom openpyxl import load_workbooknfrom openpyxl.styles import Fontnfrom openpyxl.styles.colors import BLACKnfrom collections import namedtuplennnclass ParseExcel(object):n """解析excel文件"""nn def __init__(self, filename):n try:n self.filename = filenamen self.__wb = load_workbook(self.filename)n except FileNotFoundError as e:n raise enn def get_max_row_num(self, sheet_name):n """获取最大行号"""n max_row_num = self.__wb[sheet_name].max_rown return max_row_numnn def get_max_column_num(self, sheet_name):n """获取最大列号"""n max_column = self.__wb[sheet_name].max_columnn return max_columnnn def get_cell_value(self, sheet_name, coordinate=None, row=None, column=None):n """获取指定单元格的数据"""n if coordinate is not None:n try:n return self.__wb[sheet_name][coordinate].valuen except Exception as e:n raise en elif coordinate is None and row is not None and column is not None:n if isinstance(row, int) and isinstance(column, int):n return self.__wb[sheet_name].cell(row=row, column=column).valuen else:n raise TypeError('row and column must be type int')n else:n raise Exception("Insufficient Coordinate of cell!")nn def get_row_value(self, sheet_name, row):n """获取某一行的数据"""n column_num = self.get_max_column_num(sheet_name)n row_value = []n if isinstance(row, int):n for column in range(1, column_num + 1):n values_row = self.__wb[sheet_name].cell(row, column).valuen row_value.append(values_row)n return row_valuen else:n raise TypeError('row must be type int')nn def get_column_value(self, sheet_name, column):n """获取某一列数据"""n row_num = self.get_max_column_num(sheet_name)n column_value = []n if isinstance(column, int):n for row in range(1, row_num + 1):n values_column = self.__wb[sheet_name].cell(row, column).valuen column_value.append(values_column)n return column_valuen else:n raise TypeError('column must be type int')nn def get_all_value_1(self, sheet_name):n """获取指定表单的所有数据(除去表头)"""n max_row_num = self.get_max_row_num(sheet_name)n max_column = self.get_max_column_num(sheet_name)n values = []n for row in range(2, max_row_num + 1):n value_list = []n for column in range(1, max_column + 1):n value = self.__wb[sheet_name].cell(row, column).valuen value_list.append(value)n values.append(value_list)n return valuesnn def get_all_value_2(self, sheet_name):n """获取指定表单的所有数据(除去表头)"""n rows_obj = self.__wb[sheet_name].iter_rows(min_row=2, max_row=self.__wb[sheet_name].max_row, values_only=True)n values = []n for row_tuple in rows_obj:n value_list = []n for value in row_tuple:n value_list.append(value)n values.append(value_list)n return valuesnn def get_excel_title(self, sheet_name):n """获取sheet表头"""n title_key = tuple(self.__wb[sheet_name].iter_rows(max_row=1, values_only=True))[0]n return title_keynn def get_listdict_all_value(self, sheet_name):n """获取所有数据，返回嵌套字典的列表"""n sheet_title = self.get_excel_title(sheet_name)n all_values = self.get_all_value_2(sheet_name)n value_list = []n for value in all_values:n value_list.append(dict(zip(sheet_title, value)))n return value_listnn def get_list_nametuple_all_value(self, sheet_name):n """获取所有数据，返回嵌套命名元组的列表"""n sheet_title = self.get_excel_title(sheet_name)n values = self.get_all_value_2(sheet_name)n excel = namedtuple('excel', sheet_title)n value_list = []n for value in values:n e = excel(*value)n value_list.append(e)n return value_listnn def write_cell(self, sheet_name, row, column, value=None, bold=True, color=BLACK):n if isinstance(row, int) and isinstance(column, int):n try:n cell_obj = self.__wb[sheet_name].cell(row, column)n cell_obj.font = Font(color=color, bold=bold)n cell_obj.value = valuen self.__wb.save(self.filename)n except Exception as e:n raise en else:n raise TypeError('row and column must be type int')nnnif __name__ == '__main__':n pe = ParseExcel('testdata.xlsx')n print(pe.get_all_value_2('division'))n print(pe.get_list_nametuple_all_value('division'))n column_row = pe.get_max_column_num('division')n print('最大列号:', column_row)n max_row = pe.get_max_row_num('division')n print('最大行号:', max_row)n cell_value_1 = pe.get_cell_value('division', row=2, column=3)n print('第%d行, 第%d列的数据为: %s' % (2, 3, cell_value_1))n cell_value_2 = pe.get_cell_value('division', coordinate='A5')n print('A5单元格的数据为: {}'.format(cell_value_2))n value_row = pe.get_row_value('division', 3)n print('第{}行的数据为:{}'.format(3, value_row))n value_column = pe.get_column_value('division', 2)n print('第{}列的数据为:{}'.format(2, value_column))n values_1 = pe.get_all_value_1('division')n print('第一种方式获取所有数据n', values_1)n values_2 = pe.get_all_value_2('division')n print('第二种方式获取所有数据n', values_2)n title = pe.get_excel_title('division')n print('表头为n{}'.format(title))n dict_value = pe.get_listdict_all_value('division')n print('所有数据组成的嵌套字典的列表:n', dict_value)n namedtuple_value = pe.get_list_nametuple_all_value('division')n print('所有数据组成的嵌套命名元组的列表:n', namedtuple_value)n 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开始计算的

封装代码

"""nimport xlrdnfrom xlutils import copynfrom collections import namedtuplennnclass ParseExcel(object):n # xlrd 解析excel， 行号和列号都是从0开始的n def __init__(self, filename, sheet):n try:n self.filename = filenamen self.sheet = sheetn self.wb = xlrd.open_workbook(self.filename, formatting_info=True)n if isinstance(sheet, str):n self.sheet = self.wb.sheet_by_name(sheet)n elif isinstance(sheet, int):n self.sheet = self.wb.sheet_by_index(sheet)n else:n raise TypeError('sheet must be int or str')n except Exception as e:n raise enn def get_max_row(self):n """获取表单的最大行号"""n max_row_num = self.sheet.nrowsn return max_row_numnn def get_max_column(self):n """获取表单的最大列号"""n min_row_num = self.sheet.ncolsn return min_row_numnn def get_cell_value(self, row, column):n """获取某个单元格的数据"""n if isinstance(row, int) and isinstance(column, int):n values = self.sheet.cell(row-1, column-1).valuen return valuesn else:n raise TypeError('row and column must be type int')nn def get_row_values(self, row):n """获取某一行的数据"""n if isinstance(row, int):n values = self.sheet.row_values(row-1)n return valuesn else:n raise TypeError('row must be type int')nn def get_column_values(self, column):n """获取某一列的数据"""nn if isinstance(column, int):n values = self.sheet.col_values(column-1)n return valuesn else:n raise TypeError('column must be type int')nn def get_table_title(self):n """获取表头"""n table_title = self.get_row_values(1)n return table_titlenn def get_all_values_dict(self):n """获取所有的数据，不包括表头，返回一个嵌套字典的列表"""n max_row = self.get_max_row()n table_title = self.get_table_title()n value_list = []n for row in range(2, max_row):n values = self.get_row_values(row)n value_list.append(dict(zip(table_title, values)))n return value_listnn def get_all_values_nametuple(self):n """获取所有的数据，不包括表头，返回一个嵌套命名元组的列表"""n table_title = self.get_table_title()n max_row = self.get_max_row()n excel = namedtuple('excel', table_title)n value_list = []n for row in range(2, max_row):n values = self.get_row_values(row)n e = excel(*values)n value_list.append(e)n return value_listnn def write_value(self, sheet_index, row, column, value):n """写入某个单元格数据"""n if isinstance(row, int) and isinstance(column, int):n if isinstance(sheet_index, int):n wb = copy.copy(self.wb)n worksheet = wb.get_sheet(sheet_index)n worksheet.write(row-1, column-1, value)n wb.save(self.filename)n else:n raise TypeError('{} must be int'.format(sheet_index))n else:n raise TypeError('{} and {} must be int'.format(row, column))nnif __name__ == '__main__':n pe = ParseExcel('testdata.xls', 'testcase')n print('最大行号:', pe.get_max_row())n print('最大列号:', pe.get_max_column())n print('第2行第3列数据:', pe.get_cell_value(2, 3))n print('第2行数据', pe.get_row_values(2))n print('第3列数据', pe.get_column_values(3))n print('表头:', pe.get_table_title())n print('所有的数据返回嵌套字典的列表:', pe.get_all_values_dict())n print('所有的数据返回嵌套命名元组的列表:', pe.get_all_values_nametuple())n　　 pe.write_value(0, 1, 3, 'test')

三、pandas

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

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

请先pip install pandas安装pandas

封装代码

"""nimport pandas as pdnnnclass ParseExcel(object):n def __init__(self, filename, sheet_name=None):n try:n self.filename = filenamen self.sheet_name = sheet_namen self.df = pd.read_excel(self.filename, self.sheet_name)n except Exception as e:n raise enn def get_row_num(self):n """获取行号组成的列表, 从0开始的"""n row_num_list = self.df.index.valuesn return row_num_listnn def get_cell_value(self, row, column):n """获取某一个单元格的数据"""n try:n if isinstance(row, int) and isinstance(column, int):n cell_value = self.df.ix[row-2, column-1] # ix的行参数是按照有效数据行，且从0开始n return cell_valuen else:n raise TypeError('row and column must be type int')n except Exception as e:n raise enn def get_table_title(self):n """获取表头， 返回列表"""n table_title = self.df.columns.valuesn return table_titlenn def get_row_value(self, row):n """获取某一行的数据， 行号从1开始"""n try:n if isinstance(row, int):n row_data = self.df.ix[row-2].valuesn return row_datan else:n raise TypeError('row must be type int')n except Exception as e:n raise enn def get_column_value(self, col_name):n """获取某一列数据"""n try:n if isinstance(col_name, str):n col_data = self.df[col_name].valuesn return col_datan else:n raise TypeError('col_name must be type str')n except Exception as e:n raise en n def get_all_value(self):n """获取所有的数据，不包括表头, 返回嵌套字典的列表"""n rows_num = self.get_row_num()n table_title = self.get_table_title()n values_list = []n for i in rows_num:n row_data = self.df.ix[i, table_title].to_dict()n values_list.append(row_data)n return values_listnnnif __name__ == '__main__':n pe = ParseExcel('testdata.xlsx', 'testcase')n print(pe.get_row_num())n print(pe.get_table_title())n print(pe.get_all_value())n print(pe.get_row_value(2))n print(pe.get_cell_value(2, 3))n print(pe.get_column_value('Tc_title'))

总结

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

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

最后喜欢的小伙伴可以点赞收藏评论加关注哟。