前言
自动化测试中我们存放数据无非是使用文件或者数据库，那么文件可以是csv，xlsx，xml，甚至是txt文件，通常excel文件往往是我们的首选，无论是编写测试用例还是存放测试数据，excel都是很方便的。那么今天我们就把不同模块处理excel文件的方法做个总结，直接做封装，方便我们以后直接使用，增加工作效率。
openpyxl
openpyxl是个第三方库，首先我们使用命令 pip install openpyxl 直接安装
注：openpyxl操作excel时，行号和列号都是从1开始计算的
封装代码
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
|
"""
------------------------------------
@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（写完后你会发现两个表单有一个是没有数据的）
其实原因很简单：不同对象拥有自己独立的属性， 当你写操作的时候其实每个对象只针对自己的表单做了保存，所以最后一个对象写完数据后，只保存了自己的表单，其他的对象的表单实际是没有保存的。针对这个问题，对上面封装的代码进行了轻微改动
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
"""
------------------------------------
@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开始计算的
封装代码
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
|
"""
------------------------------------
@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
封装代码
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
|
"""
------------------------------------
@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种方法，都可以拿来直接使用，不需要再做封装了 ！
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：https://www.cnblogs.com/linuxchao/p/linuxchao-parseExcel.html