Conditions | 44 |
Total Lines | 427 |
Code Lines | 336 |
Lines | 427 |
Ratio | 100 % |
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.spaceload.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 |
||
65 | View Code Duplication | def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): |
|
66 | wb = Workbook() |
||
67 | |||
68 | # todo |
||
69 | ws = wb.active |
||
70 | |||
71 | # Row height |
||
72 | ws.row_dimensions[1].height = 102 |
||
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 | |||
79 | ws.column_dimensions['B'].width = 25.0 |
||
80 | |||
81 | for i in range(ord('C'), ord('L')): |
||
82 | ws.column_dimensions[chr(i)].width = 15.0 |
||
83 | |||
84 | # Font |
||
85 | name_font = Font(name='Constantia', size=15, bold=True) |
||
86 | title_font = Font(name='宋体', size=15, bold=True) |
||
87 | data_font = Font(name='Franklin Gothic Book', size=11) |
||
88 | |||
89 | table_fill = PatternFill(fill_type='solid', fgColor='1F497D') |
||
90 | f_border = Border(left=Side(border_style='medium', color='00000000'), |
||
91 | right=Side(border_style='medium', color='00000000'), |
||
92 | bottom=Side(border_style='medium', color='00000000'), |
||
93 | top=Side(border_style='medium', color='00000000') |
||
94 | ) |
||
95 | b_border = Border( |
||
96 | bottom=Side(border_style='medium', color='00000000'), |
||
97 | ) |
||
98 | |||
99 | b_c_alignment = Alignment(vertical='bottom', |
||
100 | horizontal='center', |
||
101 | text_rotation=0, |
||
102 | wrap_text=True, |
||
103 | shrink_to_fit=False, |
||
104 | indent=0) |
||
105 | c_c_alignment = Alignment(vertical='center', |
||
106 | horizontal='center', |
||
107 | text_rotation=0, |
||
108 | wrap_text=True, |
||
109 | shrink_to_fit=False, |
||
110 | indent=0) |
||
111 | b_r_alignment = Alignment(vertical='bottom', |
||
112 | horizontal='right', |
||
113 | text_rotation=0, |
||
114 | wrap_text=True, |
||
115 | shrink_to_fit=False, |
||
116 | indent=0) |
||
117 | c_r_alignment = Alignment(vertical='bottom', |
||
118 | horizontal='center', |
||
119 | text_rotation=0, |
||
120 | wrap_text=True, |
||
121 | shrink_to_fit=False, |
||
122 | indent=0) |
||
123 | |||
124 | # Img |
||
125 | img = Image("excelexporters/myems.png") |
||
126 | img.width = img.width * 0.85 |
||
127 | img.height = img.height * 0.85 |
||
128 | # img = Image("myems.png") |
||
129 | ws.add_image(img, 'B1') |
||
130 | |||
131 | # Title |
||
132 | ws.row_dimensions[3].height = 60 |
||
133 | |||
134 | ws['B3'].font = name_font |
||
135 | ws['B3'].alignment = b_r_alignment |
||
136 | ws['B3'] = 'Name:' |
||
137 | ws['C3'].border = b_border |
||
138 | ws['C3'].alignment = b_c_alignment |
||
139 | ws['C3'].font = name_font |
||
140 | ws['C3'] = name |
||
141 | |||
142 | ws['D3'].font = name_font |
||
143 | ws['D3'].alignment = b_r_alignment |
||
144 | ws['D3'] = 'Period:' |
||
145 | ws['E3'].border = b_border |
||
146 | ws['E3'].alignment = b_c_alignment |
||
147 | ws['E3'].font = name_font |
||
148 | ws['E3'] = period_type |
||
149 | |||
150 | ws['F3'].font = name_font |
||
151 | ws['F3'].alignment = b_r_alignment |
||
152 | ws['F3'] = 'Date:' |
||
153 | ws['G3'].alignment = b_c_alignment |
||
154 | ws['G3'].font = name_font |
||
155 | ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local |
||
156 | ws.merge_cells("G3:H3") |
||
157 | |||
158 | if "reporting_period" not in report.keys() or \ |
||
159 | "timestamps" not in report['reporting_period'].keys() or len(report['reporting_period']['timestamps']) == 0: |
||
160 | filename = str(uuid.uuid4()) + '.xlsx' |
||
161 | wb.save(filename) |
||
162 | |||
163 | return filename |
||
164 | |||
165 | ############################### |
||
166 | |||
167 | has_names_data_flag = True |
||
168 | |||
169 | if "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: |
||
170 | has_names_data_flag = False |
||
171 | |||
172 | current_row_number = 6 |
||
173 | if has_names_data_flag: |
||
174 | reporting_period_data = report['reporting_period'] |
||
175 | category = reporting_period_data['names'] |
||
176 | ca_len = len(category) |
||
177 | |||
178 | ws['B' + str(current_row_number)].font = title_font |
||
179 | ws['B' + str(current_row_number)] = name + '报告期平均负荷' |
||
180 | |||
181 | current_row_number += 1 |
||
182 | |||
183 | ws.row_dimensions[current_row_number].height = 60 |
||
184 | ws['B' + str(current_row_number)].fill = table_fill |
||
185 | ws['B' + str(current_row_number)].border = f_border |
||
186 | for i in range(0, ca_len): |
||
187 | col = chr(ord('C') + i) |
||
188 | ws[col + str(current_row_number)].fill = table_fill |
||
189 | ws[col + str(current_row_number)].font = name_font |
||
190 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
191 | ws[col + str(current_row_number)].border = f_border |
||
192 | ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \ |
||
193 | " (" + reporting_period_data['units'][i] + "/H)" |
||
194 | |||
195 | current_row_number += 1 |
||
196 | |||
197 | ws['B' + str(current_row_number)].font = title_font |
||
198 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
199 | ws['B' + str(current_row_number)].border = f_border |
||
200 | ws['B' + str(current_row_number)] = '平均负荷' |
||
201 | |||
202 | for i in range(0, ca_len): |
||
203 | col = chr(ord('C') + i) |
||
204 | ws[col + str(current_row_number)].font = name_font |
||
205 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
206 | ws[col + str(current_row_number)].border = f_border |
||
207 | ws[col + str(current_row_number)] = round(reporting_period_data['averages'][i], 2) |
||
208 | |||
209 | current_row_number += 1 |
||
210 | |||
211 | ws['B' + str(current_row_number)].font = title_font |
||
212 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
213 | ws['B' + str(current_row_number)].border = f_border |
||
214 | ws['B' + str(current_row_number)] = '单位面积值' |
||
215 | |||
216 | for i in range(0, ca_len): |
||
217 | col = chr(ord('C') + i) |
||
218 | ws[col + str(current_row_number)].font = name_font |
||
219 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
220 | ws[col + str(current_row_number)].border = f_border |
||
221 | ws[col + str(current_row_number)] = round(reporting_period_data['averages_per_unit_area'][i], 2) |
||
222 | |||
223 | current_row_number += 1 |
||
224 | |||
225 | ws['B' + str(current_row_number)].font = title_font |
||
226 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
227 | ws['B' + str(current_row_number)].border = f_border |
||
228 | ws['B' + str(current_row_number)] = '环比' |
||
229 | |||
230 | for i in range(0, ca_len): |
||
231 | col = chr(ord('C') + i) |
||
232 | ws[col + str(current_row_number)].font = name_font |
||
233 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
234 | ws[col + str(current_row_number)].border = f_border |
||
235 | ws[col + str(current_row_number)] = str( |
||
236 | round(reporting_period_data['averages_increment_rate'][i] * 100, 2)) + "%" \ |
||
237 | if reporting_period_data['averages_increment_rate'][i] is not None else "-" |
||
238 | |||
239 | current_row_number += 2 |
||
240 | |||
241 | ws['B' + str(current_row_number)].font = title_font |
||
242 | ws['B' + str(current_row_number)] = name + '报告期最大负荷' |
||
243 | |||
244 | current_row_number += 1 |
||
245 | |||
246 | ws.row_dimensions[current_row_number].height = 60 |
||
247 | ws['B' + str(current_row_number)].fill = table_fill |
||
248 | ws['B' + str(current_row_number)].border = f_border |
||
249 | for i in range(0, ca_len): |
||
250 | col = chr(ord('C') + i) |
||
251 | ws[col + str(current_row_number)].fill = table_fill |
||
252 | ws[col + str(current_row_number)].font = name_font |
||
253 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
254 | ws[col + str(current_row_number)].border = f_border |
||
255 | ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \ |
||
256 | " (" + reporting_period_data['units'][i] + "/H)" |
||
257 | |||
258 | current_row_number += 1 |
||
259 | |||
260 | ws['B' + str(current_row_number)].font = title_font |
||
261 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
262 | ws['B' + str(current_row_number)].border = f_border |
||
263 | ws['B' + str(current_row_number)] = '最大负荷' |
||
264 | |||
265 | for i in range(0, ca_len): |
||
266 | col = chr(ord('C') + i) |
||
267 | ws[col + str(current_row_number)].font = name_font |
||
268 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
269 | ws[col + str(current_row_number)].border = f_border |
||
270 | ws[col + str(current_row_number)] = round(reporting_period_data['maximums'][i], 2) |
||
271 | |||
272 | current_row_number += 1 |
||
273 | |||
274 | ws['B' + str(current_row_number)].font = title_font |
||
275 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
276 | ws['B' + str(current_row_number)].border = f_border |
||
277 | ws['B' + str(current_row_number)] = '单位面积值' |
||
278 | |||
279 | for i in range(0, ca_len): |
||
280 | col = chr(ord('C') + i) |
||
281 | ws[col + str(current_row_number)].font = name_font |
||
282 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
283 | ws[col + str(current_row_number)].border = f_border |
||
284 | ws[col + str(current_row_number)] = round(reporting_period_data['maximums_per_unit_area'][i], 2) |
||
285 | |||
286 | current_row_number += 1 |
||
287 | |||
288 | ws['B' + str(current_row_number)].font = title_font |
||
289 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
290 | ws['B' + str(current_row_number)].border = f_border |
||
291 | ws['B' + str(current_row_number)] = '环比' |
||
292 | |||
293 | for i in range(0, ca_len): |
||
294 | col = chr(ord('C') + i) |
||
295 | ws[col + str(current_row_number)].font = name_font |
||
296 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
297 | ws[col + str(current_row_number)].border = f_border |
||
298 | ws[col + str(current_row_number)] = str( |
||
299 | round(reporting_period_data['maximums_increment_rate'][i] * 100, 2)) + "%" \ |
||
300 | if reporting_period_data['maximums_increment_rate'][i] is not None else "-" |
||
301 | |||
302 | current_row_number += 2 |
||
303 | |||
304 | ws['B' + str(current_row_number)].font = title_font |
||
305 | ws['B' + str(current_row_number)] = name + '报告期负荷系数' |
||
306 | |||
307 | current_row_number += 1 |
||
308 | |||
309 | ws.row_dimensions[current_row_number].height = 60 |
||
310 | ws['B' + str(current_row_number)].fill = table_fill |
||
311 | ws['B' + str(current_row_number)].border = f_border |
||
312 | for i in range(0, ca_len): |
||
313 | col = chr(ord('C') + i) |
||
314 | ws[col + str(current_row_number)].fill = table_fill |
||
315 | ws[col + str(current_row_number)].font = name_font |
||
316 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
317 | ws[col + str(current_row_number)].border = f_border |
||
318 | ws[col + str(current_row_number)] = reporting_period_data['names'][i] |
||
319 | |||
320 | current_row_number += 1 |
||
321 | |||
322 | ws['B' + str(current_row_number)].font = title_font |
||
323 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
324 | ws['B' + str(current_row_number)].border = f_border |
||
325 | ws['B' + str(current_row_number)] = '负荷系数' |
||
326 | |||
327 | for i in range(0, ca_len): |
||
328 | col = chr(ord('C') + i) |
||
329 | ws[col + str(current_row_number)].font = name_font |
||
330 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
331 | ws[col + str(current_row_number)].border = f_border |
||
332 | ws[col + str(current_row_number)] = round(reporting_period_data['factors'][i], 2) \ |
||
333 | if reporting_period_data['factors'][i] is not None else '-' |
||
334 | |||
335 | current_row_number += 1 |
||
336 | |||
337 | ws['B' + str(current_row_number)].font = title_font |
||
338 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
339 | ws['B' + str(current_row_number)].border = f_border |
||
340 | ws['B' + str(current_row_number)] = '环比' |
||
341 | |||
342 | for i in range(0, ca_len): |
||
343 | col = chr(ord('C') + i) |
||
344 | ws[col + str(current_row_number)].font = name_font |
||
345 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
346 | ws[col + str(current_row_number)].border = f_border |
||
347 | ws[col + str(current_row_number)] = str( |
||
348 | round(reporting_period_data['factors_increment_rate'][i] * 100, 2)) + "%" \ |
||
349 | if reporting_period_data['factors_increment_rate'][i] is not None else "-" |
||
350 | |||
351 | current_row_number += 2 |
||
352 | |||
353 | has_sub_averages_data_flag = True |
||
354 | has_sub_maximums_data_flag = True |
||
355 | |||
356 | if "sub_averages" not in report['reporting_period'].keys() or len(report['reporting_period']['sub_averages']) == 0: |
||
357 | has_sub_averages_data_flag = False |
||
358 | |||
359 | if "sub_averages" not in report['reporting_period'].keys() or len(report['reporting_period']['sub_averages']) == 0: |
||
360 | has_sub_maximums_data_flag = False |
||
361 | |||
362 | if has_sub_averages_data_flag or has_sub_maximums_data_flag: |
||
363 | reporting_period_data = report['reporting_period'] |
||
364 | category = reporting_period_data['names'] |
||
365 | ca_len = len(category) |
||
366 | times = reporting_period_data['timestamps'] |
||
367 | time = times[0] |
||
368 | |||
369 | ws['B' + str(current_row_number)].font = title_font |
||
370 | ws['B' + str(current_row_number)] = name + '详细数据' |
||
371 | |||
372 | current_row_number += 1 |
||
373 | chart_start_number = current_row_number |
||
374 | |||
375 | if has_sub_averages_data_flag: |
||
376 | current_row_number = (current_row_number + ca_len * 6) |
||
377 | |||
378 | if has_sub_maximums_data_flag: |
||
379 | current_row_number = (current_row_number + ca_len * 6) |
||
380 | |||
381 | table_start_number = current_row_number |
||
382 | |||
383 | ws.row_dimensions[current_row_number].height = 60 |
||
384 | ws['B' + str(current_row_number)].fill = table_fill |
||
385 | ws['B' + str(current_row_number)].font = title_font |
||
386 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
387 | ws['B' + str(current_row_number)].border = f_border |
||
388 | ws['B' + str(current_row_number)] = '日期时间' |
||
389 | |||
390 | col = 'C' |
||
391 | |||
392 | for i in range(0, ca_len): |
||
393 | if has_sub_averages_data_flag: |
||
394 | ws[col + str(current_row_number)].fill = table_fill |
||
395 | ws[col + str(current_row_number)].font = title_font |
||
396 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
397 | ws[col + str(current_row_number)].border = f_border |
||
398 | ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \ |
||
399 | " 平均负荷(" + reporting_period_data['units'][i] + "/H)" |
||
400 | col = chr(ord(col) + 1) |
||
401 | |||
402 | if has_sub_maximums_data_flag: |
||
403 | ws[col + str(current_row_number)].fill = table_fill |
||
404 | ws[col + str(current_row_number)].font = title_font |
||
405 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
406 | ws[col + str(current_row_number)].border = f_border |
||
407 | ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \ |
||
408 | " 最大负荷(" + reporting_period_data['units'][i] + "/H)" |
||
409 | col = chr(ord(col) + 1) |
||
410 | |||
411 | current_row_number += 1 |
||
412 | |||
413 | for i in range(0, len(time)): |
||
414 | ws['B' + str(current_row_number)].font = title_font |
||
415 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
416 | ws['B' + str(current_row_number)].border = f_border |
||
417 | ws['B' + str(current_row_number)] = time[i] |
||
418 | |||
419 | col = 'C' |
||
420 | for j in range(0, ca_len): |
||
421 | |||
422 | if has_sub_averages_data_flag: |
||
423 | ws[col + str(current_row_number)].font = title_font |
||
424 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
425 | ws[col + str(current_row_number)].border = f_border |
||
426 | ws[col + str(current_row_number)] = round(reporting_period_data['sub_averages'][j][i], 2) \ |
||
427 | if reporting_period_data['sub_averages'][j][i] is not None else 0.00 |
||
428 | col = chr(ord(col) + 1) |
||
429 | |||
430 | if has_sub_maximums_data_flag: |
||
431 | ws[col + str(current_row_number)].font = title_font |
||
432 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
433 | ws[col + str(current_row_number)].border = f_border |
||
434 | ws[col + str(current_row_number)] = round(reporting_period_data['sub_maximums'][j][i], 2) \ |
||
435 | if reporting_period_data['sub_maximums'][j][i] is not None else 0.00 |
||
436 | col = chr(ord(col) + 1) |
||
437 | |||
438 | current_row_number += 1 |
||
439 | |||
440 | table_end_number = current_row_number - 1 |
||
441 | |||
442 | current_chart_col_number = 3 |
||
443 | current_chart_row_number = chart_start_number |
||
444 | |||
445 | for i in range(0, ca_len): |
||
446 | labels = Reference(ws, min_col=2, min_row=table_start_number + 1, max_row=table_end_number) |
||
447 | |||
448 | if has_sub_averages_data_flag: |
||
449 | line = LineChart() |
||
450 | line.title = '报告期 平均负荷 - ' + ws.cell(column=current_chart_col_number, row=table_start_number).value |
||
451 | datas = Reference(ws, min_col=current_chart_col_number, min_row=table_start_number, |
||
452 | max_row=table_end_number) |
||
453 | line.add_data(datas, titles_from_data=True) |
||
454 | line.set_categories(labels) |
||
455 | line_data = line.series[0] |
||
456 | line_data.marker.symbol = "circle" |
||
457 | line_data.smooth = True |
||
458 | line.x_axis.crosses = 'min' |
||
459 | line.height = 8.25 |
||
460 | line.width = 24 |
||
461 | line.dLbls = DataLabelList() |
||
462 | line.dLbls.dLblPos = 't' |
||
463 | line.dLbls.showVal = True |
||
464 | ws.add_chart(line, "B" + str(current_chart_row_number)) |
||
465 | current_chart_row_number += 6 |
||
466 | current_chart_col_number += 1 |
||
467 | |||
468 | if has_sub_maximums_data_flag: |
||
469 | line = LineChart() |
||
470 | line.title = '报告期 最大负荷 - ' + ws.cell(column=current_chart_col_number, row=table_start_number).value |
||
471 | datas = Reference(ws, min_col=current_chart_col_number, min_row=table_start_number, |
||
472 | max_row=table_end_number) |
||
473 | line.add_data(datas, titles_from_data=True) |
||
474 | line.set_categories(labels) |
||
475 | line_data = line.series[0] |
||
476 | line_data.marker.symbol = "circle" |
||
477 | line_data.smooth = True |
||
478 | line.x_axis.crosses = 'min' |
||
479 | line.height = 8.25 |
||
480 | line.width = 24 |
||
481 | line.dLbls = DataLabelList() |
||
482 | line.dLbls.dLblPos = 't' |
||
483 | line.dLbls.showVal = True |
||
484 | ws.add_chart(line, "B" + str(current_chart_row_number)) |
||
485 | current_chart_row_number += 6 |
||
486 | current_chart_col_number += 1 |
||
487 | |||
488 | filename = str(uuid.uuid4()) + '.xlsx' |
||
489 | wb.save(filename) |
||
490 | |||
491 | return filename |
||
492 |