1 #!/usr/bin/env python3
2 # -*- coding: utf-8 -*-
3 # @Time : 2019/10/14 23:37
4 # @Author : Tang Yiwei
5 # @Email : 892398433@qq.com
6 # @File : ParseExcel.py
7 # @Software: PyCharm
8
9 import openpyxl
10 from openpyxl.styles import Border,Side,Alignment,Font
11 import time
12
13 class ParseExcel():
14 def __init__(self):
15 self.workbook = None
16 self.excelFile = None
17 self.worksheet = None #add
18 self.fontColor = Font(color = None) # 设置字体的颜色
19 self.fontStyle = Font(name = None) # 设置字体的样式
20 # 设置字体样式
21 self.fontStyleDict = {
22 "microsoft_accor_black":"微软雅黑",
23 "regular_script":"楷体",
24 "song_typeface":"宋体"
25 }
26 # 颜色对应的RGB值
27 self.RGBDict = {"red":"FFFF3030",
28 "green":"FF008B00",
29 "black":"000000",
30 }
31
32 def createExcel(self,excelPathName):
33 # 新建excel
34 try:
35 self.workbook = openpyxl.Workbook()
36 except Exception as e:
37 raise e
38 self.excelFile = excelPathName
39 return self.workbook
40
41 def active(self):
42 # 获取活跃的表格
43 self.worksheet = self.workbook.active
44 return self.worksheet
45
46 def addTitle(self,title):
47 # 修改title
48 self.active.title = title
49
50 def addSheet(self,sheetName):
51 # 增加sheet
52 self.worksheet_n = self.workbook.create_sheet(sheetName)
53
54 def loadWordBook(self,excelPathName):
55 # 将excel加载到内存中，并获取其workbook对象
56 try:
57 self.workbook = openpyxl.load_workbook(excelPathName)
58 except Exception as e:
59 raise e
60 self.excelFile = excelPathName
61 return self.workbook
62
63 def getSheetByName(self,sheetName):
64 # 根据sheet名获取该sheet对象
65 try:
66 sheet = self.workbook.get_sheet_by_name(sheetName)
67 return sheet
68 except Exception as e:
69 raise e
70
71 def getSheetByIndex(self,sheetIndex):
72 # 根据sheet的索引号获取该sheet对象
73 try:
74 sheetname = self.workbook.get_sheet_names()[sheetIndex]
75 except Exception as e:
76 raise e
77 sheet = self.workbook.get_sheet_by_name(sheetname)
78 return sheet
79
80 def getRowsNumber(self,sheet):
81 # 获取sheet中有数据区域的结束行号
82 return sheet.max_row
83
84 def getColsNumber(self,sheet):
85 # 获取sheet中有数据区域的结束列号
86 return sheet.max_column
87
88 def getStartRowNumber(self,sheet):
89 # 获取sheet中有数据区域的开始行号
90 return sheet.min_row
91
92 def getStartColNumber(self,sheet):
93 # 获取sheet中有数据区域开始的列号
94 return sheet.min_column
95
96 def detele_rows(self,sheet,startRow,N):
97 # 删除行，删除startRow开始以后的N行
98 return sheet.delete_rows(startRow,N)
99
100 def getRow(self,sheet,rowNo):
101 # 获取sheet中某一行，返回的是这一行所有的数据内容组成tuple
102 # 下标从1开始，sheet,row[1]表示第一行
103 dataList = []
104 rows = self.getRowsNumber(sheet)
105 cols = self.getColsNumber(sheet)
106 if rowNo - 1 >= rows:
107 print('row out of max range! Please Check excel row number!')
108 try:
109 for line in sheet.rows:
110 tmpList = []
111 for i in range(cols):
112 tmpList.append(line[i].value)
113 dataList.append(tmpList)
114 return dataList[rowNo - 1]
115 except Exception as e:
116 raise e
117
118 def getColumn(self,sheet,colNo):
119 # 获取sheet中某一列，返回的是这一列所有的数据内容组成tuple
120 # 下标从1开始，sheet,row[1]表示第一列
121 dataList = []
122 rows = self.getRowsNumber(sheet)
123 cols = self.getColsNumber(sheet)
124 if colNo - 1 >= cols:
125 print('column out of max range! Please Check excel column number!')
126 try:
127 for line in sheet.columns:
128 tmpList = []
129 for i in range(1,rows):#过滤掉第一列的标题数据
130 tmpList.append(line[i].value)
131 dataList.append(tmpList)
132 return tuple(dataList[colNo - 1])
133 except Exception as e:
134 raise e
135
136 def getCellOfValue(self,sheet,coordinate = None,rowNo = None,colsNo = None):
137 # 根据单元格所在位置索引获取该单元格中的值，下标从1开始
138 # sheet.cell(row = 1,column = 1).value,表示Excel中第一行第一列的值
139 if coordinate != None:
140 try:
141 return sheet.cell(coordinate = coordinate).value
142 except Exception as e:
143 raise e
144 elif coordinate is None and rowNo is not None and colsNo is not None:
145 try:
146 return sheet.cell(row = rowNo,column = colsNo).value
147 except Exception as e:
148 raise e
149 else:
150 raise Exception("Insufficient Coordinates of cell")
151
152 def getCellOfObject(self,sheet,coordinate = None,rowNo = None,colsNo = None):
153 # 获取某个单元格对的对象，可以根据单元格所在位置的数字索引
154 # 也可以直接根据excel中单元格的编码及坐标
155 # 如getCellOfObject(sheet,coordinate = 'A1',) or
156 # getCellOfObject(sheet,rowNo = 1,colsNo = 2)
157 if coordinate != None:
158 try:
159 return sheet.cell(coordinate = coordinate)
160 except Exception as e:
161 raise e
162
163 elif coordinate == None and rowNo is not None and colsNo is not None:
164 try:
165 return sheet.cell(row = rowNo,column = colsNo)
166 except Exception as e:
167 raise e
168 else:
169 raise Exception('Insufficient Coordinates of cell')
170
171 def writeCell(self,sheet,content,coordinate = None,rowNo = None,colsNo = None,colorStyle=None,fontStyle=None):
172 # 根据单元格在Excel中的编码坐标或者数字索引坐标向单元格中写入数据
173 # 下标从1开始，参数style表示字体的颜色的名字，比如red,green
174 if coordinate is not None:
175 try:
176 sheet.cell(coordinate = coordinate).value = content
177 if colorStyle is not None:
178 sheet.cell(coordinate = coordinate).font = Font(color = self.RGBDict[colorStyle])
179 if fontStyle is not None:
180 sheet.cell(coordinate = coordinate).font = Font(name = self.fontStyleDict[fontStyle])
181 self.workbook.save(self.excelFile)
182 except Exception as e:
183 raise e
184 elif coordinate == None and rowNo is not None and colsNo is not None:
185 try:
186 sheet.cell(row = rowNo,column = colsNo).value = content
187 if colorStyle:
188 sheet.cell(row = rowNo,column = colsNo).font = Font(color = self.RGBDict[colorStyle])
189 if fontStyle:
190 sheet.cell(coordinate=coordinate).font = Font(name=self.fontStyleDict[fontStyle])
191 self.workbook.save(self.excelFile)
192 except PermissionError as e:
193 print("file is open or Please check file permissions!")
194 except Exception as e:
195 raise Exception('Insufficient Coordinates of cell')
196 else:
197 raise Exception('Insufficient Coordinates of cell')
198
199 def writeCellCurrentTime(self,sheet,coordinate = None,rowNo = None,colsNo = None):
200 # 写入当前时间，下标从1开始
201 now = int(time.time()) #显示未时间戳
202 timeArray = time.localtime(now)
203 currentTime = time.strftime("%Y-%m-%d %H:%M:%S",timeArray)
204 if coordinate is not None:
205 try:
206 sheet.cell(coordinate = coordinate).value = currentTime
207 self.workbook.save(self.excelFile)
208 except Exception as e:
209 raise e
210 elif coordinate == None and rowNo is not None and colsNo is not None:
211 try:
212 sheet.cell(row = rowNo,column = colsNo).value = currentTime
213 self.workbook.save(self.excelFile)
214 except PermissionError as e:
215 print("file is open or Please check file permissions!")
216 except Exception as e:
217 raise e
218 else:
219 raise Exception('Insufficient Coordinates of cell')
220
221 def writeCelltmp(self, sheet, content, coordinate=None, rowNo=None, colsNo=None, colorStyle=None,fontStyle=None):
222 # 只写入数据，不保存数据，保存数据统一调用saveTable()方法
223 # 根据单元格在Excel中的编码坐标或者数字索引坐标向单元格中写入数据
224 # 下标从1开始，参数style表示字体的颜色的名字，比如red,green
225 if coordinate is not None:
226 try:
227 sheet.cell(coordinate=coordinate).value = content
228 if colorStyle is not None:
229 sheet.cell(coordinate=coordinate).font = Font(color=self.RGBDict[colorStyle])
230 if fontStyle is not None:
231 sheet.cell(coordinate=coordinate).font = Font(name=self.fontStyleDict[fontStyle])
232 except Exception as e:
233 raise e
234 elif coordinate == None and rowNo is not None and colsNo is not None:
235 try:
236 sheet.cell(row=rowNo, column=colsNo).value = content
237 if colorStyle:
238 sheet.cell(row=rowNo, column=colsNo).font = Font(color=self.RGBDict[colorStyle])
239 if fontStyle:
240 sheet.cell(row=rowNo, column=colsNo).font = Font(name=self.fontStyleDict[fontStyle])
241 except PermissionError as e:
242 print("file is open or Please check file permissions!")
243 except Exception as e:
244 raise Exception('Insufficient Coordinates of cell')
245 else:
246 raise Exception('Insufficient Coordinates of cell')
247
248 def saveTable(self):
249 # 保存表格
250 self.workbook.save(self.excelFile)
251
252
253
254 if __name__ == "__main__":
255 pass
浙公网安备 33010602011771号