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

excelexporters.offlinemeterenergy   A

Complexity

Total Complexity 29

Size/Duplication

Total Lines 343
Duplicated Lines 92.71 %

Importance

Changes 0
Metric Value
eloc 245
dl 318
loc 343
rs 10
c 0
b 0
f 0
wmc 29

2 Functions

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