excelexporters.spaceload.generate_excel()   F
last analyzed

Complexity

Conditions 44

Size

Total Lines 427
Code Lines 336

Duplication

Lines 427
Ratio 100 %

Importance

Changes 0
Metric Value
cc 44
eloc 336
nop 5
dl 427
loc 427
rs 0
c 0
b 0
f 0

How to fix   Long Method    Complexity   

Long Method

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:

Complexity

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
2
import uuid
3
import os
4
from openpyxl.chart import (
5
    PieChart,
6
    LineChart,
7
    BarChart,
8
    Reference,
9
)
10
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font
11
from openpyxl.drawing.image import Image
12
from openpyxl import Workbook
13
from openpyxl.chart.label import DataLabelList
14
15
16
####################################################################################################################
17
# PROCEDURES
18
# Step 1: Validate the report data
19
# Step 2: Generate excel file
20
# Step 3: Encode the excel file bytes to Base64
21
####################################################################################################################
22
23
24
def export(report,
25
           name,
26
           reporting_start_datetime_local,
27
           reporting_end_datetime_local,
28
           period_type):
29
    ####################################################################################################################
30
    # Step 1: Validate the report data
31
    ####################################################################################################################
32
    if report is None:
33
        return None
34
    print(report)
35
36
    ####################################################################################################################
37
    # Step 2: Generate excel file from the report data
38
    ####################################################################################################################
39
    filename = generate_excel(report,
40
                              name,
41
                              reporting_start_datetime_local,
42
                              reporting_end_datetime_local,
43
                              period_type)
44
    ####################################################################################################################
45
    # Step 3: Encode the excel file to Base64
46
    ####################################################################################################################
47
    try:
48
        with open(filename, 'rb') as binary_file:
49
            binary_file_data = binary_file.read()
50
    except IOError as ex:
51
        pass
52
53
    # Base64 encode the bytes
54
    base64_encoded_data = base64.b64encode(binary_file_data)
0 ignored issues
show
introduced by
The variable binary_file_data does not seem to be defined for all execution paths.
Loading history...
55
    # get the Base64 encoded data using human-readable characters.
56
    base64_message = base64_encoded_data.decode('utf-8')
57
    # delete the file from server
58
    try:
59
        os.remove(filename)
60
    except NotImplementedError as ex:
61
        pass
62
    return base64_message
63
64
65 View Code Duplication
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type):
0 ignored issues
show
Duplication introduced by
This code seems to be duplicated in your project.
Loading history...
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'
0 ignored issues
show
Comprehensibility Best Practice introduced by
The variable str does not seem to be defined.
Loading history...
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