释放双眼，带上耳机，听听看~！
前言
自动化测试中我们存放数据无非是使用文件或者数据库，那么文件可以是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\')
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种方法，都可以拿来直接使用，不需要再做封装了 ！
