前言
自动化测试中我们存放数据无非是使用文件或者数据库，那么文件可以是csv，xlsx，xml，甚至是txt文件，通常excel文件往往是我们的首选，无论是编写测试用例还是存放测试数据，excel都是很方便的。那么今天我们就把不同模块处理excel文件的方法做个总结，直接做封装，方便我们以后直接使用，增加工作效率。
一、openpyxl
openpyxl是个第三方库，首先我们使用命令 pip install openpyxl 直接安装
注：openpyxl操作excel时，行号和列号都是从1开始计算的
封装代码
-
""" -
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')
上面这个封装如如果用来同时操作同一个excel文件的两个sheet写入数据时，会有点小bug（写完后你会发现两个表单有一个是没有数据的）
其实原因很简单：不同对象拥有自己独立的属性， 当你写操作的时候其实每个对象只针对自己的表单做了保存，所以最后一个对象写完数据后，只保存了自己的表单，其他的对象的表单实际是没有保存的。针对这个问题，对上面封装的代码进行了轻微改动
-
""" -
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开始计算的
封装代码
-
""" -
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
封装代码
-
""" -
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种方法，都可以拿来直接使用，不需要再做封装了 ！
总结：
感谢每一个认真阅读我文章的人！！！
作为一位过来人也是希望大家少走一些弯路，如果你不想再体验一次学习时找不到资料，没人解答问题，坚持几天便放弃的感受的话，在这里我给大家分享一些自动化测试的学习资源，希望能给你前进的路上带来帮助。
软件测试面试文档
我们学习必然是为了找到高薪的工作，下面这些面试题是来自阿里、腾讯、字节等一线互联网大厂最新的面试资料，并且有字节大佬给出了权威的解答，刷完这一套面试资料相信大家都能找到满意的工作。
视频文档获取方式：
这份文档和视频资料，对于想从事【软件测试】的朋友来说应该是最全面最完整的备战仓库，这个仓库也陪伴我走过了最艰难的路程，希望也能帮助到你！以上均可以分享，点下方小卡片即可自行领取。
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
