Passed
Push — master ( 5b2513...456fb9 )
by Guangyu
02:12 queued 11s
created

excelexporters.virtualmeterenergy   A

Complexity

Total Complexity 29

Size/Duplication

Total Lines 342
Duplicated Lines 92.69 %

Importance

Changes 0
Metric Value
eloc 245
dl 317
loc 342
rs 10
c 0
b 0
f 0
wmc 29

2 Functions

Rating   Name   Duplication   Size   Complexity  
F generate_excel() 283 283 24
B export() 34 34 5

How to fix   Duplicated Code   

Duplicated Code

Duplicate code is one of the most pungent code smells. A rule that is often used is to re-structure code once it is duplicated in three or more places.

Common duplication problems, and corresponding solutions are:

1
import base64
2
import os
3
import uuid
4
5
from openpyxl import Workbook
6
from openpyxl.chart import (
7
    BarChart,
8
    Reference,
9
    Series,
10
)
11
from openpyxl.chart.label import DataLabelList
12
from openpyxl.drawing.image import Image
13
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font
14
15
16
####################################################################################################################
17
# PROCEDURES
18
# Step 1: Validate the report data
19
# Step 2: Generate excelexporters file
20
# Step 3: Encode the excelexporters file to Base64
21
####################################################################################################################
22
23 View Code Duplication
def export(result, 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...
24
    ####################################################################################################################
25
    # Step 1: Validate the report data
26
    ####################################################################################################################
27
    if result is None:
28
        return None
29
30
    ####################################################################################################################
31
    # Step 2: Generate excel file from the report data
32
    ####################################################################################################################
33
    filename = generate_excel(result,
34
                              name,
35
                              reporting_start_datetime_local,
36
                              reporting_end_datetime_local,
37
                              period_type)
38
    ####################################################################################################################
39
    # Step 3: Encode the excel file to Base64
40
    ####################################################################################################################
41
    try:
42
        with open(filename, 'rb') as binary_file:
43
            binary_file_data = binary_file.read()
44
    except IOError as ex:
45
        pass
46
47
    # Base64 encode the bytes
48
    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...
49
    # get the Base64 encoded data using human-readable characters.
50
    base64_message = base64_encoded_data.decode('utf-8')
51
    # delete the file from server
52
    try:
53
        os.remove(filename)
54
    except NotImplementedError as ex:
55
        pass
56
    return base64_message
57
58
59 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...
60
    wb = Workbook()
61
    # todo
62
    ws = wb.active
63
64
    # Row height
65
    ws.row_dimensions[1].height = 118
66
    for i in range(2, 11 + 1):
67
        ws.row_dimensions[i].height = 30
68
69
    for i in range(12, 43 + 1):
70
        ws.row_dimensions[i].height = 30
71
72
    # Col width
73
    ws.column_dimensions['A'].width = 1.5
74
75
    for i in range(ord('B'), ord('I')):
76
        ws.column_dimensions[chr(i)].width = 15.0
77
78
    # Font
79
    name_font = Font(name='Constantia', size=15, bold=True)
80
    title_font = Font(name='宋体', size=15, bold=True)
81
    data_font = Font(name='Franklin Gothic Book', size=11)
82
83
    table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
84
    f_border = Border(left=Side(border_style='medium', color='00000000'),
85
                      right=Side(border_style='medium', color='00000000'),
86
                      bottom=Side(border_style='medium', color='00000000'),
87
                      top=Side(border_style='medium', color='00000000')
88
                      )
89
    b_border = Border(
90
        bottom=Side(border_style='medium', color='00000000'),
91
    )
92
93
    b_c_alignment = Alignment(vertical='bottom',
94
                              horizontal='center',
95
                              text_rotation=0,
96
                              wrap_text=False,
97
                              shrink_to_fit=False,
98
                              indent=0)
99
    c_c_alignment = Alignment(vertical='center',
100
                              horizontal='center',
101
                              text_rotation=0,
102
                              wrap_text=False,
103
                              shrink_to_fit=False,
104
                              indent=0)
105
    b_r_alignment = Alignment(vertical='bottom',
106
                              horizontal='right',
107
                              text_rotation=0,
108
                              wrap_text=False,
109
                              shrink_to_fit=False,
110
                              indent=0)
111
    c_r_alignment = Alignment(vertical='bottom',
112
                              horizontal='center',
113
                              text_rotation=0,
114
                              wrap_text=False,
115
                              shrink_to_fit=False,
116
                              indent=0)
117
118
    # Img
119
    img = Image("excelexporters/myems.png")
120
    ws.add_image(img, 'B1')
121
122
    # Title
123
    ws['B3'].font = name_font
124
    ws['B3'].alignment = b_r_alignment
125
    ws['B3'] = 'Name:'
126
    ws['C3'].border = b_border
127
    ws['C3'].alignment = b_c_alignment
128
    ws['C3'].font = name_font
129
    ws['C3'] = name
130
131
    ws['D3'].font = name_font
132
    ws['D3'].alignment = b_r_alignment
133
    ws['D3'] = 'Period:'
134
    ws['E3'].border = b_border
135
    ws['E3'].alignment = b_c_alignment
136
    ws['E3'].font = name_font
137
    ws['E3'] = period_type
138
139
    ws['F3'].font = name_font
140
    ws['F3'].alignment = b_r_alignment
141
    ws['F3'] = 'Date:'
142
    ws.merge_cells("G3:H3")
143
    ws['G3'].border = b_border
144
    ws['G3'].alignment = b_c_alignment
145
    ws['G3'].font = name_font
146
    ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
147
148
    if "reporting_period" not in report.keys() or \
149
            "values" not in report['reporting_period'].keys() or len(report['reporting_period']['values']) == 0:
150
        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...
151
        wb.save(filename)
152
153
        return filename
154
155
    ###############################
156
157
    has_cost_data_flag = True
158
159
    if "values" not in report['reporting_period'].keys() or len(report['reporting_period']['values']) == 0:
160
        has_cost_data_flag = False
161
162
    if has_cost_data_flag:
163
        ws['B6'].font = title_font
164
        ws['B6'] = name + '报告期消耗'
165
166
        reporting_period_data = report['reporting_period']
167
        category = report['virtual_meter']['energy_category_name']
168
        ca_len = len(category)
169
170
        ws['B7'].fill = table_fill
171
172
        ws['B8'].font = title_font
173
        ws['B8'].alignment = c_c_alignment
174
        ws['B8'] = '能耗'
175
        ws['B8'].border = f_border
176
177
        ws['B9'].font = title_font
178
        ws['B9'].alignment = c_c_alignment
179
        ws['B9'] = '环比'
180
        ws['B9'].border = f_border
181
182
        col = 'B'
183
184
        for i in range(0, ca_len):
185
            col = chr(ord('C') + i)
186
187
            ws[col + '7'].fill = table_fill
188
            ws[col + '7'].font = name_font
189
            ws[col + '7'].alignment = c_c_alignment
190
            ws[col + '7'] = report['virtual_meter']['energy_category_name'] + \
191
                " (" + report['virtual_meter']['unit_of_measure'] + ")"
192
            ws[col + '7'].border = f_border
193
194
            ws[col + '8'].font = name_font
195
            ws[col + '8'].alignment = c_c_alignment
196
            ws[col + '8'] = round(reporting_period_data['total_in_category'], 0)
197
            ws[col + '8'].border = f_border
198
199
            ws[col + '9'].font = name_font
200
            ws[col + '9'].alignment = c_c_alignment
201
            ws[col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
202
                if reporting_period_data['increment_rate'] is not None else "-"
203
            ws[col + '9'].border = f_border
204
205
        # TCE TCO2E
206
        end_col = col
207
        # TCE
208
        tce_col = chr(ord(end_col) + 1)
209
        ws[tce_col + '7'].fill = table_fill
210
        ws[tce_col + '7'].font = name_font
211
        ws[tce_col + '7'].alignment = c_c_alignment
212
        ws[tce_col + '7'] = "TCE"
213
        ws[tce_col + '7'].border = f_border
214
215
        ws[tce_col + '8'].font = name_font
216
        ws[tce_col + '8'].alignment = c_c_alignment
217
        ws[tce_col + '8'] = round(reporting_period_data['total_in_kgce'], 0)
218
        ws[tce_col + '8'].border = f_border
219
220
        ws[tce_col + '9'].font = name_font
221
        ws[tce_col + '9'].alignment = c_c_alignment
222
        ws[tce_col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
223
            if reporting_period_data['increment_rate'] is not None else "-"
224
        ws[tce_col + '9'].border = f_border
225
226
        # TCO2E
227
        tco2e_col = chr(ord(end_col) + 2)
228
        ws[tco2e_col + '7'].fill = table_fill
229
        ws[tco2e_col + '7'].font = name_font
230
        ws[tco2e_col + '7'].alignment = c_c_alignment
231
        ws[tco2e_col + '7'] = "TCO2E"
232
        ws[tco2e_col + '7'].border = f_border
233
234
        ws[tco2e_col + '8'].font = name_font
235
        ws[tco2e_col + '8'].alignment = c_c_alignment
236
        ws[tco2e_col + '8'] = round(reporting_period_data['total_in_kgco2e'], 0)
237
        ws[tco2e_col + '8'].border = f_border
238
239
        ws[tco2e_col + '9'].font = name_font
240
        ws[tco2e_col + '9'].alignment = c_c_alignment
241
        ws[tco2e_col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
242
            if reporting_period_data['increment_rate'] is not None else "-"
243
        ws[tco2e_col + '9'].border = f_border
244
245
    else:
246
        for i in range(6, 9 + 1):
247
            ws.rows_dimensions[i].height = 0.1
248
249
    ######################################
250
251
    has_cost_detail_flag = True
252
    reporting_period_data = report['reporting_period']
253
    category = report['virtual_meter']['energy_category_name']
254
    ca_len = len(category)
255
    times = reporting_period_data['timestamps']
256
257
    if "values" not in reporting_period_data.keys() or len(reporting_period_data['values']) == 0:
258
        has_cost_detail_flag = False
259
260
    if has_cost_detail_flag:
261
        ws['B11'].font = title_font
262
        ws['B11'] = name + '详细数据'
263
264
        ws['B18'].fill = table_fill
265
        ws['B18'].font = title_font
266
        ws['B18'].border = f_border
267
        ws['B18'].alignment = c_c_alignment
268
        ws['B18'] = '日期时间'
269
        time = times
270
        has_data = False
271
        max_row = 0
272
        if len(time) > 0:
273
            has_data = True
274
            max_row = 18 + len(time)
275
276
        if has_data:
277
278
            end_data_flag = 19
279
280
            for i in range(0, len(time)):
281
                col = 'B'
282
                end_data_flag = 19 + i
283
                row = str(end_data_flag)
284
285
                ws[col + row].font = title_font
286
                ws[col + row].alignment = c_c_alignment
287
                ws[col + row] = time[i]
288
                ws[col + row].border = f_border
289
290
            ws['B' + str(end_data_flag + 1)].font = title_font
291
            ws['B' + str(end_data_flag + 1)].alignment = c_c_alignment
292
            ws['B' + str(end_data_flag + 1)] = '总计'
293
            ws['B' + str(end_data_flag + 1)].border = f_border
294
295
            bar = BarChart()
296
297
            for i in range(0, ca_len):
298
299
                col = chr(ord('C') + i)
300
301
                ws[col + '18'].fill = table_fill
302
                ws[col + '18'].font = title_font
303
                ws[col + '18'].alignment = c_c_alignment
304
                ws[col + '18'] = report['virtual_meter']['energy_category_name'] + \
305
                    " (" + report['virtual_meter']['unit_of_measure'] + ")"
306
                ws[col + '18'].border = f_border
307
308
                time = times
309
                time_len = len(time)
310
311
                for j in range(0, time_len):
312
                    row = str(19 + j)
313
314
                    ws[col + row].font = title_font
315
                    ws[col + row].alignment = c_c_alignment
316
                    ws[col + row] = round(reporting_period_data['values'][j], 0)
317
                    ws[col + row].border = f_border
318
319
                ws[col + str(end_data_flag + 1)].font = title_font
320
                ws[col + str(end_data_flag + 1)].alignment = c_c_alignment
321
                ws[col + str(end_data_flag + 1)] = round(reporting_period_data['total_in_category'], 0)
322
                ws[col + str(end_data_flag + 1)].border = f_border
323
324
                bar_data = Reference(ws, min_col=3 + i, min_row=18, max_row=max_row)
325
                bar.series.append(Series(bar_data, title_from_data=True))
326
327
            labels = Reference(ws, min_col=2, min_row=19, max_row=max_row)
328
            bar.set_categories(labels)
329
            bar.dLbls = DataLabelList()
330
            bar.dLbls.showVal = True
331
            bar.height = 5.25
332
            bar.width = len(time)
333
            ws.add_chart(bar, "B12")
334
    else:
335
        for i in range(11, 43 + 1):
336
            ws.row_dimensions[i].height = 0.0
337
338
    filename = str(uuid.uuid4()) + '.xlsx'
339
    wb.save(filename)
340
341
    return filename
342