为了更好的让openpyxl在工作中使用,将openpyxl的常用操作封装起来,这样不仅复用性高,而且阅读性好
1 #!/usr/bin/env python
2 # -*- coding: utf-8 -*-
3
4 """
5 __title__ = openpyxl操作Excel工具类
6 """
7
8 import openpyxl
9
10
11 class ExcelUtil:
12 workBook = None
13 workSheet = None
14
15 def load_excel(self, path):
16 """
17 加载Excel
18 :param path: 需要打开的Excel的路径
19 """
20 self.workBook = openpyxl.load_workbook(path)
21
22 def get_sheet_by_name(self, name):
23 """
24 获取sheet对象
25 :param name: sheet名
26 """
27 self.workSheet = self.workBook.get_sheet_by_name(name)
28
29 def get_sheet_by_index(self, index=0):
30 """
31 获取sheet对象
32 :param index: sheet的索引
33 """
34 # 获取workBook里所有的sheet名 -> list
35 sheet_names = self.workBook.get_sheet_names()
36 # 根据索引获取指定sheet
37 self.workSheet = self.workBook[sheet_names[index]]
38
39 def get_cell_value(self, col, row):
40 """
41 获取cell的值
42 :param col: 所在列
43 :param row: 所在行
44 """
45 try:
46 return self.workSheet.cell(column=col, row=row).value
47 except BaseException as e:
48 return None
49
50 def get_cell_value_by_xy(self, str):
51 """
52 获取cell的值
53 :param str: 坐标
54 """
55 try:
56 return self.workSheet[str].value
57 except BaseException as e:
58 return None
59
60 def get_sheet_rows(self):
61 """
62 获取最大行数
63 """
64 return self.workSheet.max_row
65
66 def get_sheet_cols(self):
67 """
68 获取最大列数
69 """
70 return self.workSheet.max_column
71
72 def write_data(self, row, col, value, path):
73 """
74 写入数据
75 """
76 try:
77 self.workSheet = self.workBook.active
78 self.workSheet.cell(column=col, row=row, value=value)
79 self.workBook.save(path)
80 except BaseException as e:
81 print(e)
82 return None
83
84 def get_excel_data(self):
85 """
86 获取表所有数据
87 :return: list
88 """
89 # 方式一
90 data_list = tuple(self.workSheet.values)
91 # 方式二
92 # data_list = []
93 # for i in range(self.get_sheet_rows()):
94 # data_list.append(self.get_row_value(i + 2))
95 return data_list
96
97 def get_row_value(self, row):
98 """
99 获取某一行的内容
100 :param row: 第几行 -> str **从1开始**
101 :return: list
102 """
103 # 方式一
104 row_list = self.get_excel_data()[row]
105 # 方式二
106 # row_list = []
107 # for i in self.workSheet[str(row + 1)]:
108 # row_list.append(i.value)
109 return row_list
110
111 def get_col_value(self, col='A'):
112 """
113 获取某一列的内容
114 :param col: 第几列 -> str
115 :return: list
116 """
117 col_list = []
118 for i in self.workSheet[col]:
119 col_list.append(i.value)
120 return col_list
121
122 def get_row_num(self, case_id):
123 """
124 获取行号
125 :param case_id: 用例编号
126 :return:
127 """
128 num = 1
129 col_data = self.get_col_value()
130 for data in col_data:
131 if case_id == data:
132 return num
133 num += 1
134 return 0
135
136
137 excelUtil = ExcelUtil()
138
139 if __name__ == '__main__':
140 path = 'F:/interface/case/test.xlsx'
141 # 读取excel文件
142 excelUtil.load_excel(path)
143 # 获取某个sheet
144 excelUtil.get_sheet_by_name("Sheet1")
145 excelUtil.get_sheet_by_index()
146 # 获取某个cell的值
147 data = excelUtil.get_cell_value(col=1, row=1)
148 print(data)
149 data = excelUtil.get_cell_value_by_xy("A3")
150 print(data)
151 # 获取sheet行数
152 data = excelUtil.get_sheet_rows()
153 print(data)
154 # 获取sheet列数
155 data = excelUtil.get_sheet_cols()
156 print(data)
157 # 获取某一行数据
158 data = excelUtil.get_row_value(0)
159 print(data)
160 # 获取某一列数据
161 data = excelUtil.get_col_value()
162 print(data)
163 # 写入数据
164 excelUtil.write_data(row=9, col=1, value="test", path=path)
165 # 获取全部数据
166 data = excelUtil.get_excel_data()
167 print(data)
168 # 获取行号
169 data = excelUtil.get_row_num('imooc_001')
170 print(data)