Conditions | 34 |
Total Lines | 370 |
Code Lines | 271 |
Lines | 0 |
Ratio | 0 % |
Changes | 0 |
Small methods make your code easier to understand, in particular if combined with a good name. Besides, if your method is small, finding a good name is usually much easier.
For example, if you find yourself adding comments to a method's body, this is usually a good sign to extract the commented part to a new method, and use the comment as a starting point when coming up with a good name for this new method.
Commonly applied refactorings include:
If many parameters/temporary variables are present:
Complex classes like excelexporters.shopfloorload.generate_excel() often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
1 | import base64 |
||
62 | def generate_excel(report, |
||
63 | name, |
||
64 | reporting_start_datetime_local, |
||
65 | reporting_end_datetime_local, |
||
66 | period_type): |
||
67 | wb = Workbook() |
||
68 | ws = wb.active |
||
69 | |||
70 | # Row height |
||
71 | ws.row_dimensions[1].height = 102 |
||
72 | |||
73 | for i in range(2, 2000 + 1): |
||
74 | ws.row_dimensions[i].height = 42 |
||
75 | |||
76 | # Col width |
||
77 | ws.column_dimensions['A'].width = 1.5 |
||
78 | ws.column_dimensions['B'].width = 25.0 |
||
79 | |||
80 | for i in range(ord('C'), ord('L')): |
||
81 | ws.column_dimensions[chr(i)].width = 15.0 |
||
82 | |||
83 | # Font |
||
84 | name_font = Font(name='Constantia', size=15, bold=True) |
||
85 | title_font = Font(name='宋体', size=15, bold=True) |
||
86 | # data_font = Font(name='Franklin Gothic Book', size=11) |
||
87 | |||
88 | table_fill = PatternFill(fill_type='solid', fgColor='1F497D') |
||
89 | f_border = Border(left=Side(border_style='medium', color='00000000'), |
||
90 | right=Side(border_style='medium', color='00000000'), |
||
91 | bottom=Side(border_style='medium', color='00000000'), |
||
92 | top=Side(border_style='medium', color='00000000') |
||
93 | ) |
||
94 | b_border = Border( |
||
95 | bottom=Side(border_style='medium', color='00000000'), |
||
96 | ) |
||
97 | |||
98 | b_c_alignment = Alignment(vertical='bottom', |
||
99 | horizontal='center', |
||
100 | text_rotation=0, |
||
101 | wrap_text=False, |
||
102 | shrink_to_fit=False, |
||
103 | indent=0) |
||
104 | c_c_alignment = Alignment(vertical='center', |
||
105 | horizontal='center', |
||
106 | text_rotation=0, |
||
107 | wrap_text=True, |
||
108 | shrink_to_fit=False, |
||
109 | indent=0) |
||
110 | b_r_alignment = Alignment(vertical='bottom', |
||
111 | horizontal='right', |
||
112 | text_rotation=0, |
||
113 | wrap_text=False, |
||
114 | shrink_to_fit=False, |
||
115 | indent=0) |
||
116 | # c_r_alignment = Alignment(vertical='bottom', |
||
117 | # horizontal='center', |
||
118 | # text_rotation=0, |
||
119 | # wrap_text=False, |
||
120 | # shrink_to_fit=False, |
||
121 | # indent=0) |
||
122 | |||
123 | # Img |
||
124 | img = Image("excelexporters/myems.png") |
||
125 | # img = Image("myems.png") |
||
126 | img.width = img.width * 0.85 |
||
127 | img.height = img.height * 0.85 |
||
128 | ws.add_image(img, 'B1') |
||
129 | |||
130 | # Title |
||
131 | ws['B3'].font = name_font |
||
132 | ws['B3'].alignment = b_r_alignment |
||
133 | ws['B3'] = 'Name:' |
||
134 | ws['C3'].border = b_border |
||
135 | ws['C3'].alignment = b_c_alignment |
||
136 | ws['C3'].font = name_font |
||
137 | ws['C3'] = name |
||
138 | |||
139 | ws['D3'].font = name_font |
||
140 | ws['D3'].alignment = b_r_alignment |
||
141 | ws['D3'] = 'Period:' |
||
142 | ws['E3'].border = b_border |
||
143 | ws['E3'].alignment = b_c_alignment |
||
144 | ws['E3'].font = name_font |
||
145 | ws['E3'] = period_type |
||
146 | |||
147 | ws['F3'].font = name_font |
||
148 | ws['F3'].alignment = b_r_alignment |
||
149 | ws['F3'] = 'Date:' |
||
150 | ws['G3'].border = b_border |
||
151 | ws['G3'].alignment = b_c_alignment |
||
152 | ws['G3'].font = name_font |
||
153 | ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local |
||
154 | ws.merge_cells("G3:H3") |
||
155 | |||
156 | if "reporting_period" not in report.keys() or \ |
||
157 | "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: |
||
158 | filename = str(uuid.uuid4()) + '.xlsx' |
||
159 | wb.save(filename) |
||
160 | |||
161 | return filename |
||
162 | ################################################# |
||
163 | # First: 统计分析 |
||
164 | # 6: title |
||
165 | # 7: table title |
||
166 | # 8~ca_len table_data |
||
167 | ################################################# |
||
168 | reporting_period_data = report['reporting_period'] |
||
169 | |||
170 | has_energy_data_flag = True |
||
171 | |||
172 | if "names" not in reporting_period_data.keys() or \ |
||
173 | reporting_period_data['names'] is None or \ |
||
174 | len(reporting_period_data['names']) == 0: |
||
175 | has_energy_data_flag = False |
||
176 | |||
177 | filename = str(uuid.uuid4()) + '.xlsx' |
||
178 | wb.save(filename) |
||
179 | |||
180 | return filename |
||
181 | |||
182 | if has_energy_data_flag: |
||
183 | ws['B6'].font = title_font |
||
184 | ws['B6'] = name + ' 统计分析' |
||
185 | |||
186 | category = reporting_period_data['names'] |
||
187 | |||
188 | # table_title |
||
189 | ws['B7'].fill = table_fill |
||
190 | ws['B7'].font = title_font |
||
191 | ws['B7'].alignment = c_c_alignment |
||
192 | ws['B7'] = '报告期' |
||
193 | ws['B7'].border = f_border |
||
194 | |||
195 | ws['C7'].font = title_font |
||
196 | ws['C7'].alignment = c_c_alignment |
||
197 | ws['C7'] = '平均负荷' |
||
198 | ws['C7'].border = f_border |
||
199 | |||
200 | ws['D7'].font = title_font |
||
201 | ws['D7'].alignment = c_c_alignment |
||
202 | ws['D7'] = '最大负荷' |
||
203 | ws['D7'].border = f_border |
||
204 | |||
205 | ws['E7'].font = title_font |
||
206 | ws['E7'].alignment = c_c_alignment |
||
207 | ws['E7'] = '负荷系数' |
||
208 | ws['E7'].border = f_border |
||
209 | |||
210 | # table_data |
||
211 | |||
212 | for i, value in enumerate(category): |
||
213 | row = i * 2 + 8 |
||
214 | ws['B' + str(row)].font = name_font |
||
215 | ws['B' + str(row)].alignment = c_c_alignment |
||
216 | ws['B' + str(row)] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + "/H )" |
||
217 | ws['B' + str(row)].border = f_border |
||
218 | |||
219 | ws['B' + str(row + 1)].font = name_font |
||
220 | ws['B' + str(row + 1)].alignment = c_c_alignment |
||
221 | ws['B' + str(row + 1)] = "环比" |
||
222 | ws['B' + str(row + 1)].border = f_border |
||
223 | |||
224 | ws['C' + str(row)].font = name_font |
||
225 | ws['C' + str(row)].alignment = c_c_alignment |
||
226 | ws['C' + str(row)] = round(reporting_period_data['averages'][i], 2) \ |
||
227 | if reporting_period_data['averages'][i] is not None else '' |
||
228 | ws['C' + str(row)].border = f_border |
||
229 | ws['C' + str(row)].number_format = '0.00' |
||
230 | |||
231 | ws['C' + str(row + 1)].font = name_font |
||
232 | ws['C' + str(row + 1)].alignment = c_c_alignment |
||
233 | ws['C' + str(row + 1)] = str(round(reporting_period_data['averages_increment_rate'][i] * 100, 2)) + "%" \ |
||
234 | if reporting_period_data['averages_increment_rate'][i] is not None else '0.00%' |
||
235 | ws['C' + str(row + 1)].border = f_border |
||
236 | |||
237 | ws['D' + str(row)].font = name_font |
||
238 | ws['D' + str(row)].alignment = c_c_alignment |
||
239 | ws['D' + str(row)] = round(reporting_period_data['maximums'][i], 2) \ |
||
240 | if reporting_period_data['maximums'][i] is not None else '' |
||
241 | ws['D' + str(row)].border = f_border |
||
242 | ws['D' + str(row)].number_format = '0.00' |
||
243 | |||
244 | ws['D' + str(row + 1)].font = name_font |
||
245 | ws['D' + str(row + 1)].alignment = c_c_alignment |
||
246 | ws['D' + str(row + 1)] = str(round(reporting_period_data['maximums_increment_rate'][i] * 100, 2)) + "%" \ |
||
247 | if reporting_period_data['maximums_increment_rate'][i] is not None else '0.00%' |
||
248 | ws['D' + str(row + 1)].border = f_border |
||
249 | |||
250 | ws['E' + str(row)].font = name_font |
||
251 | ws['E' + str(row)].alignment = c_c_alignment |
||
252 | ws['E' + str(row)] = round(reporting_period_data['factors'][i], 2) \ |
||
253 | if reporting_period_data['factors'][i] is not None else '' |
||
254 | ws['E' + str(row)].border = f_border |
||
255 | ws['E' + str(row)].number_format = '0.00' |
||
256 | |||
257 | ws['E' + str(row + 1)].font = name_font |
||
258 | ws['E' + str(row + 1)].alignment = c_c_alignment |
||
259 | ws['E' + str(row + 1)] = str(round(reporting_period_data['factors_increment_rate'][i] * 100, 2)) + "%" \ |
||
260 | if reporting_period_data['factors_increment_rate'][i] is not None else '0.00%' |
||
261 | ws['E' + str(row + 1)].border = f_border |
||
262 | |||
263 | ################################################# |
||
264 | # Second: 报告期单位面积消耗 |
||
265 | # 9 + ca_len * 2: title |
||
266 | # 10 + ca_len * 2: table title |
||
267 | # row_title + 2 ~ row_title + 2 + ca_len : table_data |
||
268 | ################################################# |
||
269 | |||
270 | if has_energy_data_flag: |
||
271 | names = reporting_period_data['names'] |
||
272 | ca_len = len(names) |
||
273 | row_title = 9 + ca_len * 2 |
||
274 | |||
275 | for i in range(row_title + 2, row_title + 2 + ca_len): |
||
276 | ws.row_dimensions[i].height = 62 |
||
277 | |||
278 | ws['B' + str(row_title)].font = title_font |
||
279 | ws['B' + str(row_title)] = name + ' 单位面积值' |
||
280 | ws['D' + str(row_title)].font = title_font |
||
281 | ws['D' + str(row_title)] = str(report['shopfloor']['area']) + 'M²' |
||
282 | |||
283 | category = reporting_period_data['names'] |
||
284 | |||
285 | # table_title |
||
286 | ws['B' + str(row_title + 1)].fill = table_fill |
||
287 | ws['B' + str(row_title + 1)].font = title_font |
||
288 | ws['B' + str(row_title + 1)].alignment = c_c_alignment |
||
289 | ws['B' + str(row_title + 1)] = '报告期' |
||
290 | ws['B' + str(row_title + 1)].border = f_border |
||
291 | |||
292 | ws['C' + str(row_title + 1)].font = title_font |
||
293 | ws['C' + str(row_title + 1)].alignment = c_c_alignment |
||
294 | ws['C' + str(row_title + 1)] = '平均负荷' |
||
295 | ws['C' + str(row_title + 1)].border = f_border |
||
296 | |||
297 | ws['D' + str(row_title + 1)].font = title_font |
||
298 | ws['D' + str(row_title + 1)].alignment = c_c_alignment |
||
299 | ws['D' + str(row_title + 1)] = '最大负荷' |
||
300 | ws['D' + str(row_title + 1)].border = f_border |
||
301 | |||
302 | # table_data |
||
303 | |||
304 | for i, value in enumerate(category): |
||
305 | row_data = row_title + 2 + i |
||
306 | ws['B' + str(row_data)].font = name_font |
||
307 | ws['B' + str(row_data)].alignment = c_c_alignment |
||
308 | ws['B' + str(row_data)] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][ |
||
309 | i] + "/H/M²)" |
||
310 | ws['B' + str(row_data)].border = f_border |
||
311 | |||
312 | ws['C' + str(row_data)].font = name_font |
||
313 | ws['C' + str(row_data)].alignment = c_c_alignment |
||
314 | if reporting_period_data['averages_per_unit_area'][i] \ |
||
315 | or reporting_period_data['averages_per_unit_area'][i] == 0: |
||
316 | ws['C' + str(row_data)] = round(reporting_period_data['averages_per_unit_area'][i], 2) |
||
317 | ws['C' + str(row_data)].border = f_border |
||
318 | ws['C' + str(row_data)].number_format = '0.00' |
||
319 | |||
320 | ws['D' + str(row_data)].font = name_font |
||
321 | ws['D' + str(row_data)].alignment = c_c_alignment |
||
322 | if reporting_period_data['maximums_per_unit_area'][i] \ |
||
323 | or reporting_period_data['maximums_per_unit_area'][i] == 0: |
||
324 | ws['D' + str(row_data)] = round(reporting_period_data['maximums_per_unit_area'][i], 2) |
||
325 | ws['D' + str(row_data)].border = f_border |
||
326 | ws['D' + str(row_data)].number_format = '0.00' |
||
327 | |||
328 | ######################################################## |
||
329 | # Third: 详细数据 |
||
330 | # row_sat+row_title~ row_sat+row_title+time_len: line |
||
331 | # row_sat+1+row_title: table title |
||
332 | # i + row_sat + 2 + 6 * ca_len~: table_data |
||
333 | ######################################################## |
||
334 | has_timestamps_flag = True |
||
335 | if "timestamps" not in reporting_period_data.keys() or \ |
||
336 | reporting_period_data['timestamps'] is None or \ |
||
337 | len(reporting_period_data['timestamps']) == 0: |
||
338 | has_timestamps_flag = False |
||
339 | |||
340 | if has_timestamps_flag: |
||
341 | timestamps = reporting_period_data['timestamps'][0] |
||
342 | names = reporting_period_data['names'] |
||
343 | ca_len = len(names) |
||
344 | time_len = len(timestamps) |
||
345 | # title |
||
346 | row_title = 6 * ca_len |
||
347 | # row_st == row_statistical analysis table |
||
348 | row_sat = 12 + 3 * ca_len |
||
349 | |||
350 | ws['B' + str(row_sat + row_title)].font = title_font |
||
351 | ws['B' + str(row_sat + row_title)] = name + ' 详细数据' |
||
352 | # table_title |
||
353 | ws['B' + str(row_sat + 1 + row_title)].fill = table_fill |
||
354 | ws['B' + str(row_sat + 1 + row_title)].font = name_font |
||
355 | ws['B' + str(row_sat + 1 + row_title)].alignment = c_c_alignment |
||
356 | ws['B' + str(row_sat + 1 + row_title)] = "日期时间" |
||
357 | ws['B' + str(row_sat + 1 + row_title)].border = f_border |
||
358 | |||
359 | for i in range(0, ca_len): |
||
360 | col_average = chr(ord('C') + i) |
||
361 | col_maximum = chr(ord('C') + i + ca_len) |
||
362 | |||
363 | ws[col_average + str(row_sat + 1 + row_title)].font = name_font |
||
364 | ws[col_average + str(row_sat + 1 + row_title)].alignment = c_c_alignment |
||
365 | ws[col_average + str(row_sat + 1 + row_title)] = names[i] + " 平均负荷(" + reporting_period_data['units'][ |
||
366 | i] + "/H)" |
||
367 | ws[col_average + str(row_sat + 1 + row_title)].border = f_border |
||
368 | |||
369 | ws[col_maximum + str(row_sat + 1 + row_title)].font = name_font |
||
370 | ws[col_maximum + str(row_sat + 1 + row_title)].alignment = c_c_alignment |
||
371 | ws[col_maximum + str(row_sat + 1 + row_title)] = names[i] + " 最大负荷(" + reporting_period_data['units'][ |
||
372 | i] + "/H)" |
||
373 | ws[col_maximum + str(row_sat + 1 + row_title)].border = f_border |
||
374 | # table_date |
||
375 | for i in range(0, time_len): |
||
376 | rows = i + row_sat + 2 + 6 * ca_len |
||
377 | |||
378 | ws['B' + str(rows)].font = name_font |
||
379 | ws['B' + str(rows)].alignment = c_c_alignment |
||
380 | ws['B' + str(rows)] = timestamps[i] |
||
381 | ws['B' + str(rows)].border = f_border |
||
382 | |||
383 | for index in range(0, ca_len): |
||
384 | col_average = chr(ord('C') + index * 2) |
||
385 | col_maximum = chr(ord('C') + index * 2 + 1) |
||
386 | |||
387 | ws[col_average + str(rows)].font = name_font |
||
388 | ws[col_average + str(rows)].alignment = c_c_alignment |
||
389 | ws[col_average + str(rows)] = reporting_period_data['sub_averages'][index][i] \ |
||
390 | if reporting_period_data['sub_maximums'][index] is not None else '' |
||
391 | ws[col_average + str(rows)].number_format = '0.00' |
||
392 | ws[col_average + str(rows)].border = f_border |
||
393 | |||
394 | ws[col_maximum + str(rows)].font = name_font |
||
395 | ws[col_maximum + str(rows)].alignment = c_c_alignment |
||
396 | ws[col_maximum + str(rows)] = reporting_period_data['sub_maximums'][index][i] \ |
||
397 | if reporting_period_data['sub_maximums'][index] is not None else '' |
||
398 | ws[col_maximum + str(rows)].number_format = '0.00' |
||
399 | ws[col_maximum + str(rows)].border = f_border |
||
400 | |||
401 | # LineChart |
||
402 | for i in range(0, ca_len): |
||
403 | lc = LineChart() |
||
404 | lc.title = "报告期 最大负荷" |
||
405 | lc.style = 10 |
||
406 | lc.x_axis.majorTickMark = 'in' |
||
407 | lc.y_axis.majorTickMark = 'in' |
||
408 | lc.smooth = True |
||
409 | lc.x_axis.crosses = 'min' |
||
410 | lc.height = 8.25 |
||
411 | lc.width = 24 |
||
412 | lc.dLbls = DataLabelList() |
||
413 | lc.dLbls.dLblPos = 't' |
||
414 | lc.dLbls.showVal = True |
||
415 | times = Reference(ws, min_col=2, min_row=row_sat + 2 + row_title, |
||
416 | max_row=row_sat + 2 + row_title + time_len) |
||
417 | lc_data = Reference(ws, min_col=3 + ca_len, min_row=row_sat + 1 + row_title, |
||
418 | max_row=row_sat + 1 + row_title + time_len) |
||
419 | lc.add_data(lc_data, titles_from_data=True) |
||
420 | lc.set_categories(times) |
||
421 | ser = lc.series[0] |
||
422 | ser.marker.symbol = "diamond" |
||
423 | ser.marker.size = 5 |
||
424 | chart_col = 'B' |
||
425 | chart_cell = str(row_sat + 6 * i) |
||
426 | ws.add_chart(lc, chart_col + chart_cell) |
||
427 | |||
428 | filename = str(uuid.uuid4()) + '.xlsx' |
||
429 | wb.save(filename) |
||
430 | |||
431 | return filename |
||
432 |