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
# coding:utf-8
import os
from openpyxl import Workbook, load_workbook
class SheetIndexError(Exception):
"""Sheet数目下标获取异常"""
message = "sheet下标超过范围"
class Excel(object):
"""Excel 基类"""
@staticmethod
def _load_file(file_name):
if not os.path.exists(file_name):
raise FileNotFoundError(
"file '{file_name}' is not found.".format(file_name=file_name))
return load_workbook(file_name)
def write_header(self, head: list):
"""生成excel头部信息。
:param head: excel头部列表
:type head: list
:return:
"""
self.write_row(1, head)
def write_row(self, row_num, values):
"""写一行。"""
for column, value in enumerate(values):
self._cell(row=row_num, column=column + 1, value=value)
def write_rows(self, row_start, rows):
"""写多行。"""
row_num = row_start
for row in rows:
self.write_row(row_num, row)
row_num += 1
def save(self):
self.save2file(self._file)
def save2file(self, file_name):
"""保存到指定文件。"""
self._excel.save(file_name)
def set_sheet_title(self, title):
self._current_sheet.title = title
def _cell(self, row, column, value=None):
return self._current_sheet.cell(row, column, value)
def sheet_select(self, index):
"""选取某个sheet作为当前活动sheet"""
try:
self._current_sheet = self._excel.get_index(index)
except IndexError:
raise SheetIndexError(SheetIndexError.message)
@property
def row_number(self):
"""获取当前excel的row。"""
return self._current_sheet.max_row
@property
def column_number(self):
"""获取excel的column。"""
return self._current_sheet.max_column
def read_rows(self, min_row=None, max_row=None, max_col=None):
"""读取第row行的数据。"""
# iter_rows(self, range_string=None, min_row=None, max_row=None, min_col=None, max_col=None,
# row_offset=0, column_offset=0):
rows = []
for row in self._current_sheet.iter_rows(min_row=min_row, max_row=max_row, max_col=max_col):
row_data = []
for cell in row:
row_data.append(cell.value)
rows.append(row_data)
return rows
def read_column(self, col):
"""读取第column列的数据。"""
# iter_cols(self, min_col=None, max_col=None, min_row=None, max_row=None):
data = self._current_sheet.iter_cols(min_col=col, max_col=col)
res = []
for item in data:
for i in item:
res.append(i.value)
return res
class ExcelReader(Excel):
def __init__(self, file_name):
self._file = file_name
self._excel = self._load_file(self._file)
self._current_sheet = self._excel.active
class ExcelWriter(Excel):
def __init__(self, file_name):
self._file = file_name
self._excel = Workbook()
self._current_sheet = self._excel.active