Issues (1577)

myems-api/excelexporters/equipmentcarbon.py (4 issues)

1
import base64
2
from core.utilities import get_translation
3
import os
4
import re
5
import uuid
6
from decimal import Decimal
7
import openpyxl.utils.cell as format_cell
8
from openpyxl import Workbook
9
from openpyxl.chart import PieChart, LineChart, Reference
10
from openpyxl.chart.label import DataLabelList
11
from openpyxl.drawing.image import Image
12
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font
13
from core.utilities import round2
14
15
########################################################################################################################
16
# PROCEDURES
17
# Step 1: Validate the report data
18
# Step 2: Generate excel file
19
# Step 3: Encode the excel file to Base64
20
########################################################################################################################
21
22
23 View Code Duplication
def export(report,
0 ignored issues
show
This code seems to be duplicated in your project.
Loading history...
24
           name,
25
           base_period_start_datetime_local,
26
           base_period_end_datetime_local,
27
           reporting_start_datetime_local,
28
           reporting_end_datetime_local,
29
           period_type,
30
           language):
31
    ####################################################################################################################
32
    # Step 1: Validate the report data
33
    ####################################################################################################################
34
    if report is None:
35
        return None
36
37
    ####################################################################################################################
38
    # Step 2: Generate excel file from the report data
39
    ####################################################################################################################
40
    filename = generate_excel(report,
41
                              name,
42
                              base_period_start_datetime_local,
43
                              base_period_end_datetime_local,
44
                              reporting_start_datetime_local,
45
                              reporting_end_datetime_local,
46
                              period_type,
47
                              language)
48
    ####################################################################################################################
49
    # Step 3: Encode the excel file to Base64
50
    ####################################################################################################################
51
    binary_file_data = b''
52
    try:
53
        with open(filename, 'rb') as binary_file:
54
            binary_file_data = binary_file.read()
55
    except IOError as ex:
56
        print(str(ex))
57
58
    # Base64 encode the bytes
59
    base64_encoded_data = base64.b64encode(binary_file_data)
60
    # get the Base64 encoded data using human-readable characters.
61
    base64_message = base64_encoded_data.decode('utf-8')
62
    # delete the file from server
63
    try:
64
        os.remove(filename)
65
    except NotImplementedError as ex:
66
        print(str(ex))
67
    return base64_message
68
69
70 View Code Duplication
def generate_excel(report,
0 ignored issues
show
This code seems to be duplicated in your project.
Loading history...
71
                   name,
72
                   base_period_start_datetime_local,
73
                   base_period_end_datetime_local,
74
                   reporting_start_datetime_local,
75
                   reporting_end_datetime_local,
76
                   period_type,
77
                   language):
78
    trans = get_translation(language)
79
    trans.install()
80
    _ = trans.gettext
81
    wb = Workbook()
82
    ws = wb.active
83
    ws.title = "EquipmentCarbon"
84
85
    # Row height
86
    ws.row_dimensions[1].height = 102
87
88
    for i in range(2, 2000 + 1):
89
        ws.row_dimensions[i].height = 42
90
91
    # Col width
92
    ws.column_dimensions['A'].width = 1.5
93
94
    ws.column_dimensions['B'].width = 25.0
95
96
    for i in range(ord('C'), ord('Z')):
97
        ws.column_dimensions[chr(i)].width = 15.0
98
99
    # Font
100
    name_font = Font(name='Arial', size=15, bold=True)
101
    title_font = Font(name='Arial', size=15, bold=True)
102
103
    table_fill = PatternFill(fill_type='solid', fgColor='90ee90')
104
    f_border = Border(left=Side(border_style='medium'),
105
                      right=Side(border_style='medium'),
106
                      bottom=Side(border_style='medium'),
107
                      top=Side(border_style='medium')
108
                      )
109
    b_border = Border(bottom=Side(border_style='medium'),)
110
111
    b_c_alignment = Alignment(vertical='bottom',
112
                              horizontal='center',
113
                              text_rotation=0,
114
                              wrap_text=True,
115
                              shrink_to_fit=False,
116
                              indent=0)
117
    c_c_alignment = Alignment(vertical='center',
118
                              horizontal='center',
119
                              text_rotation=0,
120
                              wrap_text=True,
121
                              shrink_to_fit=False,
122
                              indent=0)
123
    b_r_alignment = Alignment(vertical='bottom',
124
                              horizontal='right',
125
                              text_rotation=0,
126
                              wrap_text=True,
127
                              shrink_to_fit=False,
128
                              indent=0)
129
130
    # Img
131
    img = Image("excelexporters/myems.png")
132
    ws.add_image(img, 'A1')
133
134
    # Title
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'] = name
140
141
    ws['D3'].alignment = b_r_alignment
142
    ws['D3'] = _('Period Type') + ':'
143
    ws['E3'].border = b_border
144
    ws['E3'].alignment = b_c_alignment
145
    ws['E3'] = period_type
146
147
    ws['B4'].alignment = b_r_alignment
148
    ws['B4'] = _('Reporting Start Datetime') + ':'
149
    ws['C4'].border = b_border
150
    ws['C4'].alignment = b_c_alignment
151
    ws['C4'] = reporting_start_datetime_local
152
153
    ws['D4'].alignment = b_r_alignment
154
    ws['D4'] = _('Reporting End Datetime') + ':'
155
    ws['E4'].border = b_border
156
    ws['E4'].alignment = b_c_alignment
157
    ws['E4'] = reporting_end_datetime_local
158
159
    is_base_period_timestamp_exists_flag = is_base_period_timestamp_exists(report['base_period'])
160
161
    if is_base_period_timestamp_exists_flag:
162
        ws['B5'].alignment = b_r_alignment
163
        ws['B5'] = _('Base Period Start Datetime') + ':'
164
        ws['C5'].border = b_border
165
        ws['C5'].alignment = b_c_alignment
166
        ws['C5'] = base_period_start_datetime_local
167
168
        ws['D5'].alignment = b_r_alignment
169
        ws['D5'] = _('Base Period End Datetime') + ':'
170
        ws['E5'].border = b_border
171
        ws['E5'].alignment = b_c_alignment
172
        ws['E5'] = base_period_end_datetime_local
173
174
    if "reporting_period" not in report.keys() or \
175
            "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
176
        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...
177
        wb.save(filename)
178
179
        return filename
180
    ####################################################################################################################
181
    # First: Reporting Period Carbon Dioxide Emissions
182
    # 7: title
183
    # 8: table title
184
    # 9~11 table_data
185
    # Total: 5 rows
186
    # if has not energy data: set low height for rows
187
    ####################################################################################################################
188
    reporting_period_data = report['reporting_period']
189
    if "names" not in reporting_period_data.keys() or \
190
            reporting_period_data['names'] is None or \
191
            len(reporting_period_data['names']) == 0:
192
        for i in range(7, 9 + 1):
193
            ws.row_dimensions[i].height = 0.1
194
    else:
195
        ws['B7'].font = title_font
196
        ws['B7'] = name + ' ' + _('Reporting Period Carbon Dioxide Emissions')
197
198
        category = reporting_period_data['names']
199
        ca_len = len(category)
200
201
        ws.row_dimensions[8].height = 60
202
        ws['B8'].fill = table_fill
203
        ws['B8'].border = f_border
204
205
        ws['B9'].font = title_font
206
        ws['B9'].alignment = c_c_alignment
207
        ws['B9'] = _('Carbon Dioxide Emissions')
208
        ws['B9'].border = f_border
209
210
        ws['B10'].font = title_font
211
        ws['B10'].alignment = c_c_alignment
212
        ws['B10'] = _('Increment Rate')
213
        ws['B10'].border = f_border
214
215
        col = ''
216
217
        for i in range(0, ca_len):
218
            col = chr(ord('C') + i)
219
            ws[col + '8'].fill = table_fill
220
            ws[col + '8'].font = name_font
221
            ws[col + '8'].alignment = c_c_alignment
222
            ws[col + '8'] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
223
            ws[col + '8'].border = f_border
224
225
            ws[col + '9'].font = name_font
226
            ws[col + '9'].alignment = c_c_alignment
227
            ws[col + '9'] = round2(reporting_period_data['subtotals'][i], 2)
228
            ws[col + '9'].border = f_border
229
230
            ws[col + '10'].font = name_font
231
            ws[col + '10'].alignment = c_c_alignment
232
            ws[col + '10'] = str(round2(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
233
                if reporting_period_data['increment_rates'][i] is not None else "-"
234
            ws[col + '10'].border = f_border
235
236
        col = chr(ord(col) + 1)
237
238
        ws[col + '8'].fill = table_fill
239
        ws[col + '8'].font = name_font
240
        ws[col + '8'].alignment = c_c_alignment
241
        ws[col + '8'] = _("Total") + " (" + reporting_period_data['total_unit'] + ")"
242
        ws[col + '8'].border = f_border
243
244
        ws[col + '9'].font = name_font
245
        ws[col + '9'].alignment = c_c_alignment
246
        ws[col + '9'] = round2(reporting_period_data['total'], 2)
247
        ws[col + '9'].border = f_border
248
249
        ws[col + '10'].font = name_font
250
        ws[col + '10'].alignment = c_c_alignment
251
        ws[col + '10'] = str(round2(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \
252
            if reporting_period_data['total_increment_rate'] is not None else "-"
253
        ws[col + '10'].border = f_border
254
255
    ####################################################################################################################
256
    # Second: Electricity Carbon Dioxide Emissions by Time-Of-Use
257
    # 12: title
258
    # 13: table title
259
    # 14~17 table_data
260
    # Total: 6 rows
261
    ####################################################################################################################
262
    if "toppeaks" not in reporting_period_data.keys() or \
263
            reporting_period_data['toppeaks'] is None or \
264
            len(reporting_period_data['toppeaks']) == 0:
265
        for i in range(12, 18 + 1):
266
            ws.row_dimensions[i].height = 0.1
267
    else:
268
        electricity_index = -1
269
        for i in range(len(reporting_period_data['energy_category_ids'])):
270
            if reporting_period_data['energy_category_ids'][i] == 1:
271
                electricity_index = i
272
                break
273
274
        ws['B12'].font = title_font
275
        ws['B12'] = name + ' ' + _('Electricity Carbon Dioxide Emissions by Time-Of-Use')
276
277
        ws.row_dimensions[13].height = 60
278
        ws['B13'].fill = table_fill
279
        ws['B13'].font = name_font
280
        ws['B13'].alignment = c_c_alignment
281
        ws['B13'].border = f_border
282
283
        ws['C13'].fill = table_fill
284
        ws['C13'].font = name_font
285
        ws['C13'].alignment = c_c_alignment
286
        ws['C13'].border = f_border
287
        ws['C13'] = _('Electricity Carbon Dioxide Emissions by Time-Of-Use')
288
289
        ws['B14'].font = title_font
290
        ws['B14'].alignment = c_c_alignment
291
        ws['B14'] = _('TopPeak')
292
        ws['B14'].border = f_border
293
294
        ws['C14'].font = title_font
295
        ws['C14'].alignment = c_c_alignment
296
        ws['C14'].border = f_border
297
        ws['C14'] = round2(reporting_period_data['toppeaks'][electricity_index], 2) if electricity_index >= 0 else "-"
298
299
        ws['B15'].font = title_font
300
        ws['B15'].alignment = c_c_alignment
301
        ws['B15'] = _('OnPeak')
302
        ws['B15'].border = f_border
303
304
        ws['C15'].font = title_font
305
        ws['C15'].alignment = c_c_alignment
306
        ws['C15'].border = f_border
307
        ws['C15'] = round2(reporting_period_data['onpeaks'][electricity_index], 2) if electricity_index >= 0 else "-"
308
309
        ws['B16'].font = title_font
310
        ws['B16'].alignment = c_c_alignment
311
        ws['B16'] = _('MidPeak')
312
        ws['B16'].border = f_border
313
314
        ws['C16'].font = title_font
315
        ws['C16'].alignment = c_c_alignment
316
        ws['C16'].border = f_border
317
        ws['C16'] = round2(reporting_period_data['midpeaks'][electricity_index], 2) if electricity_index >= 0 else "-"
318
319
        ws['B17'].font = title_font
320
        ws['B17'].alignment = c_c_alignment
321
        ws['B17'] = _('OffPeak')
322
        ws['B17'].border = f_border
323
324
        ws['C17'].font = title_font
325
        ws['C17'].alignment = c_c_alignment
326
        ws['C17'].border = f_border
327
        ws['C17'] = round2(reporting_period_data['offpeaks'][electricity_index], 2) if electricity_index >= 0 else "-"
328
329
        pie = PieChart()
330
        pie.title = name + ' ' + _('Electricity Carbon Dioxide Emissions by Time-Of-Use')
331
        labels = Reference(ws, min_col=2, min_row=14, max_row=17)
332
        pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
333
        pie.add_data(pie_data, titles_from_data=True)
334
        pie.set_categories(labels)
335
        pie.height = 7.25
336
        pie.width = 9
337
        s1 = pie.series[0]
338
        s1.dLbls = DataLabelList()
339
        s1.dLbls.showCatName = False  # 标签显示
340
        s1.dLbls.showVal = False  # 数量显示
341
        s1.dLbls.showPercent = True  # 百分比显示
342
343
        ws.add_chart(pie, "D13")
344
345
    ####################################################################################################################
346
    # Second: Carbon Dioxide Emissions Proportion by Energy Category
347
    ####################################################################################################################
348
    current_row_number = 19
349
    if 'subtotals' not in reporting_period_data.keys() or \
350
            reporting_period_data['subtotals'] is None:
351
        pass
352
    else:
353
        ws['B' + str(current_row_number)].font = title_font
354
        ws['B' + str(current_row_number)] = name + ' ' + _('Carbon Dioxide Emissions Proportion')
355
356
        current_row_number += 1
357
        table_start_row_number = current_row_number
358
359
        ws['B' + str(current_row_number)].fill = table_fill
360
        ws['B' + str(current_row_number)].font = name_font
361
        ws['B' + str(current_row_number)].alignment = c_c_alignment
362
        ws['B' + str(current_row_number)].border = f_border
363
364
        ws['C' + str(current_row_number)].fill = table_fill
365
        ws['C' + str(current_row_number)].font = name_font
366
        ws['C' + str(current_row_number)].alignment = c_c_alignment
367
        ws['C' + str(current_row_number)].border = f_border
368
        ws['C' + str(current_row_number)] = _('Carbon Dioxide Emissions Proportion')
369
370
        current_row_number += 1
371
372
        category = reporting_period_data['names']
373
        ca_len = len(category)
374
375
        for i in range(0, ca_len):
376
            ws['B' + str(current_row_number)].font = title_font
377
            ws['B' + str(current_row_number)].alignment = c_c_alignment
378
            ws['B' + str(current_row_number)] = reporting_period_data['names'][i] + \
379
                ' (' + reporting_period_data['units'][i] + ')'
380
            ws['B' + str(current_row_number)].border = f_border
381
382
            ws['C' + str(current_row_number)].font = title_font
383
            ws['C' + str(current_row_number)].alignment = c_c_alignment
384
            ws['C' + str(current_row_number)] = round2(reporting_period_data['subtotals'][i], 3)
385
            ws['C' + str(current_row_number)].border = f_border
386
387
            current_row_number += 1
388
389
        table_end_row_number = current_row_number - 1
390
391
        pie = PieChart()
392
        pie.title = name + ' ' + _('Carbon Dioxide Emissions Proportion')
393
        labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number)
394
        pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number)
395
        pie.add_data(pie_data, titles_from_data=True)
396
        pie.set_categories(labels)
397
        pie.height = 6.6
398
        pie.width = 9
399
        s1 = pie.series[0]
400
        s1.dLbls = DataLabelList()
401
        s1.dLbls.showCatName = False
402
        s1.dLbls.showVal = False
403
        s1.dLbls.showPercent = True
404
405
        ws.add_chart(pie, 'D' + str(table_start_row_number))
406
407
        if ca_len < 4:
408
            current_row_number = current_row_number - ca_len + 4
409
410
        current_row_number += 1
411
412
    ####################################################################################################################
413
    # Fourth: Detailed Data
414
    # current_row_number: title
415
    # current_row_number+1 ~ current_row_number+1+ca_len*6: line
416
    # current_row_number+ca_len*6+real_timestamps_len*6+1+2: table title
417
    # current_row_number+ca_len*6+real_timestamps_len*6+1+2~: table_data
418
    ####################################################################################################################
419
    table_start_draw_flag = current_row_number + 1
420
421
    if "timestamps" not in reporting_period_data.keys() or \
422
            reporting_period_data['timestamps'] is None or \
423
            len(reporting_period_data['timestamps']) == 0:
424
        pass
425
    else:
426
        if not is_base_period_timestamp_exists_flag:
427
            reporting_period_data = report['reporting_period']
428
            times = reporting_period_data['timestamps']
429
            ca_len = len(report['reporting_period']['names'])
430
            real_timestamps_len = timestamps_data_not_equal_0(report['parameters']['timestamps'])
431
            ws['B' + str(current_row_number)].font = title_font
432
            ws['B' + str(current_row_number)] = name + ' ' + _('Detailed Data')
433
434
            current_row_number += 1
435
            # 1: Stand for blank line  2: Stand for title
436
            current_row_number += ca_len * 6 + real_timestamps_len * 6 + 1 + 2
437
            table_start_row_number = current_row_number
438
439
            time = times[0]
440
            has_data = False
441
442
            if len(time) > 0:
443
                has_data = True
444
445
            if has_data:
446
447
                ws.row_dimensions[current_row_number].height = 60
448
                current_col_number = 2
449
                col = format_cell.get_column_letter(current_col_number)
450
                ws[col + str(current_row_number)].fill = table_fill
451
                ws[col + str(current_row_number)].font = title_font
452
                ws[col + str(current_row_number)].border = f_border
453
                ws[col + str(current_row_number)].alignment = c_c_alignment
454
                ws[col + str(current_row_number)] = _('Datetime')
455
456
                for i in range(0, ca_len):
457
                    current_col_number += 1
458
                    col = format_cell.get_column_letter(current_col_number)
459
460
                    ws[col + str(current_row_number)].fill = table_fill
461
                    ws[col + str(current_row_number)].font = title_font
462
                    ws[col + str(current_row_number)].alignment = c_c_alignment
463
                    ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \
464
                        " (" + reporting_period_data['units'][i] + ")"
465
                    ws[col + str(current_row_number)].border = f_border
466
467
                current_col_number += 1
468
                col = format_cell.get_column_letter(current_col_number)
469
                ws[col + str(current_row_number)].fill = table_fill
470
                ws[col + str(current_row_number)].font = title_font
471
                ws[col + str(current_row_number)].alignment = c_c_alignment
472
                ws[col + str(current_row_number)] = _('Total') + '(' + report['reporting_period']['total_unit'] + ')'
473
                ws[col + str(current_row_number)].border = f_border
474
475
                current_row_number += 1
476
477
                for i in range(0, len(time)):
478
                    current_col_number = 2
479
                    col = format_cell.get_column_letter(current_col_number)
480
481
                    ws[col + str(current_row_number)].font = title_font
482
                    ws[col + str(current_row_number)].alignment = c_c_alignment
483
                    ws[col + str(current_row_number)] = time[i]
484
                    ws[col + str(current_row_number)].border = f_border
485
486
                    total = Decimal(0.0)
487
488
                    for j in range(0, ca_len):
489
                        current_col_number += 1
490
                        col = format_cell.get_column_letter(current_col_number)
491
492
                        ws[col + str(current_row_number)].font = title_font
493
                        ws[col + str(current_row_number)].alignment = c_c_alignment
494
                        ws[col + str(current_row_number)] = round2(reporting_period_data['values'][j][i], 2)
495
                        total += reporting_period_data['values'][j][i]
496
                        ws[col + str(current_row_number)].border = f_border
497
498
                    current_col_number += 1
499
                    col = format_cell.get_column_letter(current_col_number)
500
                    ws[col + str(current_row_number)].font = title_font
501
                    ws[col + str(current_row_number)].alignment = c_c_alignment
502
                    ws[col + str(current_row_number)] = round2(total, 2)
503
                    ws[col + str(current_row_number)].border = f_border
504
505
                    current_row_number += 1
506
507
                table_end_row_number = current_row_number - 1
508
509
                current_col_number = 2
510
                col = format_cell.get_column_letter(current_col_number)
511
512
                ws[col + str(current_row_number)].font = title_font
513
                ws[col + str(current_row_number)].alignment = c_c_alignment
514
                ws[col + str(current_row_number)] = _('Subtotal')
515
                ws[col + str(current_row_number)].border = f_border
516
517
                subtotals = Decimal(0.0)
518
519
                for i in range(0, ca_len):
520
                    current_col_number += 1
521
                    col = format_cell.get_column_letter(current_col_number)
522
                    ws[col + str(current_row_number)].font = title_font
523
                    ws[col + str(current_row_number)].alignment = c_c_alignment
524
                    ws[col + str(current_row_number)] = round2(reporting_period_data['subtotals'][i], 2)
525
                    subtotals += reporting_period_data['subtotals'][i]
526
                    ws[col + str(current_row_number)].border = f_border
527
528
                    # line
529
                    line = LineChart()
530
                    line.title = _('Reporting Period Carbon Dioxide Emissions') + ' - ' \
531
                        + reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
532
                    labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number)
533
                    line_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number,
534
                                          max_row=table_end_row_number)
535
                    line.add_data(line_data, titles_from_data=True)
536
                    line.set_categories(labels)
537
                    line_data = line.series[0]
538
                    line_data.marker.symbol = "auto"
539
                    line_data.smooth = True
540
                    line.x_axis.crosses = 'min'
541
                    line.height = 8.25
542
                    line.width = 24
543
                    chart_col = 'B'
544
                    chart_cell = chart_col + str(table_start_draw_flag + 6 * i)
545
                    ws.add_chart(line, chart_cell)
546
547
                current_col_number += 1
548
                col = format_cell.get_column_letter(current_col_number)
549
                ws[col + str(current_row_number)].font = title_font
550
                ws[col + str(current_row_number)].alignment = c_c_alignment
551
                ws[col + str(current_row_number)] = round2(subtotals, 2)
552
                ws[col + str(current_row_number)].border = f_border
553
554
                current_row_number += 2
555
        else:
556
            base_period_data = report['base_period']
557
            reporting_period_data = report['reporting_period']
558
            base_period_timestamps = base_period_data['timestamps']
559
            reporting_period_timestamps = reporting_period_data['timestamps']
560
            # Tip:
561
            #     base_period_data['names'] == reporting_period_data['names']
562
            #     base_period_data['units'] == reporting_period_data['units']
563
            base_period_data_ca_len = len(base_period_data['names'])
564
            reporting_period_data_ca_len = len(reporting_period_data['names'])
565
            real_timestamps_len = timestamps_data_not_equal_0(report['parameters']['timestamps'])
566
            ws['B' + str(current_row_number)].font = title_font
567
            ws['B' + str(current_row_number)] = name + ' ' + _('Detailed Data')
568
569
            current_row_number += 1
570
            # 1: Stand for blank line  2: Stand for title
571
            current_row_number += reporting_period_data_ca_len * 6 + real_timestamps_len * 6 + 1 + 2
572
            table_start_row_number = current_row_number
573
574
            has_data = False
575
576
            if len(base_period_timestamps[0]) or len(reporting_period_timestamps[0]) > 0:
577
                has_data = True
578
579
            if has_data:
580
                ws.row_dimensions[current_row_number].height = 60
581
                current_col_number = 2
582
                col = format_cell.get_column_letter(current_col_number)
583
                ws[col + str(current_row_number)].fill = table_fill
584
                ws[col + str(current_row_number)].font = title_font
585
                ws[col + str(current_row_number)].border = f_border
586
                ws[col + str(current_row_number)].alignment = c_c_alignment
587
                ws[col + str(current_row_number)] = _('Base Period') + " - " + _('Datetime')
588
589
                for i in range(0, base_period_data_ca_len):
590
                    current_col_number += 1
591
                    col = format_cell.get_column_letter(current_col_number)
592
593
                    ws[col + str(current_row_number)].fill = table_fill
594
                    ws[col + str(current_row_number)].font = title_font
595
                    ws[col + str(current_row_number)].alignment = c_c_alignment
596
                    ws[col + str(current_row_number)] = _('Base Period') + " - " + base_period_data['names'][i] + \
597
                        " (" + base_period_data['units'][i] + ")"
598
                    ws[col + str(current_row_number)].border = f_border
599
600
                current_col_number += 1
601
                col = format_cell.get_column_letter(current_col_number)
602
                ws[col + str(current_row_number)].fill = table_fill
603
                ws[col + str(current_row_number)].font = title_font
604
                ws[col + str(current_row_number)].alignment = c_c_alignment
605
                ws[col + str(current_row_number)] = _('Base Period') + " - " \
606
                    + _('Total') + '(' + report['reporting_period']['total_unit'] + ')'
607
                ws[col + str(current_row_number)].border = f_border
608
609
                current_col_number += 1
610
                col = format_cell.get_column_letter(current_col_number)
611
612
                ws[col + str(current_row_number)].fill = table_fill
613
                ws[col + str(current_row_number)].font = title_font
614
                ws[col + str(current_row_number)].border = f_border
615
                ws[col + str(current_row_number)].alignment = c_c_alignment
616
                ws[col + str(current_row_number)] = _('Reporting Period') + " - " + _('Datetime')
617
618
                for i in range(0, reporting_period_data_ca_len):
619
                    current_col_number += 1
620
                    col = format_cell.get_column_letter(current_col_number)
621
                    ws[col + str(current_row_number)].fill = table_fill
622
                    ws[col + str(current_row_number)].font = title_font
623
                    ws[col + str(current_row_number)].alignment = c_c_alignment
624
                    ws[col + str(current_row_number)] = _('Reporting Period') + " - " \
625
                        + reporting_period_data['names'][i] + " (" + \
626
                        reporting_period_data['units'][i] + ")"
627
                    ws[col + str(current_row_number)].border = f_border
628
629
                current_col_number += 1
630
                col = format_cell.get_column_letter(current_col_number)
631
                ws[col + str(current_row_number)].fill = table_fill
632
                ws[col + str(current_row_number)].font = title_font
633
                ws[col + str(current_row_number)].alignment = c_c_alignment
634
                ws[col + str(current_row_number)] = _('Reporting Period') + " - " \
635
                    + _('Total') + '(' + report['reporting_period']['total_unit'] + ')'
636
                ws[col + str(current_row_number)].border = f_border
637
638
                current_row_number += 1
639
640
                max_timestamps_len = len(base_period_timestamps[0]) \
641
                    if len(base_period_timestamps[0]) >= len(reporting_period_timestamps[0]) \
642
                    else len(reporting_period_timestamps[0])
643
644
                for i in range(0, max_timestamps_len):
645
                    current_col_number = 2
646
                    col = format_cell.get_column_letter(current_col_number)
647
                    ws[col + str(current_row_number)].font = title_font
648
                    ws[col + str(current_row_number)].alignment = c_c_alignment
649
                    ws[col + str(current_row_number)] = base_period_timestamps[0][i] \
650
                        if i < len(base_period_timestamps[0]) else None
651
                    ws[col + str(current_row_number)].border = f_border
652
653
                    base_period_total = Decimal(0.0)
654
655
                    for j in range(0, base_period_data_ca_len):
656
                        current_col_number += 1
657
                        col = format_cell.get_column_letter(current_col_number)
658
659
                        ws[col + str(current_row_number)].font = title_font
660
                        ws[col + str(current_row_number)].alignment = c_c_alignment
661
                        ws[col + str(current_row_number)] = round2(base_period_data['values'][j][i], 2) \
662
                            if i < len(base_period_data['values'][j]) else None
663
                        if i < len(base_period_timestamps[0]):
664
                            base_period_total += base_period_data['values'][j][i]
665
                        ws[col + str(current_row_number)].border = f_border
666
667
                    current_col_number += 1
668
                    col = format_cell.get_column_letter(current_col_number)
669
                    ws[col + str(current_row_number)].font = title_font
670
                    ws[col + str(current_row_number)].alignment = c_c_alignment
671
                    ws[col + str(current_row_number)] = round2(base_period_total, 2) \
672
                        if i < len(base_period_timestamps[0]) else None
673
                    ws[col + str(current_row_number)].border = f_border
674
675
                    current_col_number += 1
676
                    col = format_cell.get_column_letter(current_col_number)
677
678
                    ws[col + str(current_row_number)].font = title_font
679
                    ws[col + str(current_row_number)].alignment = c_c_alignment
680
                    ws[col + str(current_row_number)] = reporting_period_timestamps[0][i] \
681
                        if i < len(reporting_period_timestamps[0]) else None
682
                    ws[col + str(current_row_number)].border = f_border
683
684
                    reporting_period_total = Decimal(0.0)
685
686
                    for j in range(0, reporting_period_data_ca_len):
687
                        current_col_number += 1
688
                        col = format_cell.get_column_letter(current_col_number)
689
690
                        ws[col + str(current_row_number)].font = title_font
691
                        ws[col + str(current_row_number)].alignment = c_c_alignment
692
                        ws[col + str(current_row_number)] = round2(reporting_period_data['values'][j][i], 2) \
693
                            if i < len(reporting_period_data['values'][j]) else None
694
                        if i < len(reporting_period_timestamps[0]):
695
                            reporting_period_total += reporting_period_data['values'][j][i]
696
                        ws[col + str(current_row_number)].border = f_border
697
698
                    current_col_number += 1
699
                    col = format_cell.get_column_letter(current_col_number)
700
                    ws[col + str(current_row_number)].font = title_font
701
                    ws[col + str(current_row_number)].alignment = c_c_alignment
702
                    ws[col + str(current_row_number)] = round2(reporting_period_total, 2) \
703
                        if i < len(reporting_period_timestamps[0]) else None
704
                    ws[col + str(current_row_number)].border = f_border
705
706
                    current_row_number += 1
707
708
                current_col_number = 2
709
                col = format_cell.get_column_letter(current_col_number)
710
                ws[col + str(current_row_number)].font = title_font
711
                ws[col + str(current_row_number)].alignment = c_c_alignment
712
                ws[col + str(current_row_number)] = _('Subtotal')
713
                ws[col + str(current_row_number)].border = f_border
714
715
                base_period_subtotals = Decimal(0.0)
716
717
                for i in range(0, base_period_data_ca_len):
718
                    current_col_number += 1
719
                    col = format_cell.get_column_letter(current_col_number)
720
                    ws[col + str(current_row_number)].font = title_font
721
                    ws[col + str(current_row_number)].alignment = c_c_alignment
722
                    ws[col + str(current_row_number)] = round2(base_period_data['subtotals'][i], 2)
723
                    base_period_subtotals += base_period_data['subtotals'][i]
724
                    ws[col + str(current_row_number)].border = f_border
725
726
                current_col_number += 1
727
                col = format_cell.get_column_letter(current_col_number)
728
                ws[col + str(current_row_number)].font = title_font
729
                ws[col + str(current_row_number)].alignment = c_c_alignment
730
                ws[col + str(current_row_number)] = round2(base_period_subtotals, 2)
731
                ws[col + str(current_row_number)].border = f_border
732
733
                current_col_number += 1
734
                col = format_cell.get_column_letter(current_col_number)
735
736
                ws[col + str(current_row_number)].font = title_font
737
                ws[col + str(current_row_number)].alignment = c_c_alignment
738
                ws[col + str(current_row_number)] = _('Subtotal')
739
                ws[col + str(current_row_number)].border = f_border
740
741
                reporting_period_subtotals = Decimal(0.0)
742
743
                for i in range(0, reporting_period_data_ca_len):
744
                    current_col_number += 1
745
                    col = format_cell.get_column_letter(current_col_number)
746
                    ws[col + str(current_row_number)].font = title_font
747
                    ws[col + str(current_row_number)].alignment = c_c_alignment
748
                    ws[col + str(current_row_number)] = round2(reporting_period_data['subtotals'][i], 2)
749
                    reporting_period_subtotals += reporting_period_data['subtotals'][i]
750
                    ws[col + str(current_row_number)].border = f_border
751
752
                current_col_number += 1
753
                col = format_cell.get_column_letter(current_col_number)
754
                ws[col + str(current_row_number)].font = title_font
755
                ws[col + str(current_row_number)].alignment = c_c_alignment
756
                ws[col + str(current_row_number)] = round2(reporting_period_subtotals, 2)
757
                ws[col + str(current_row_number)].border = f_border
758
759
                for i in range(0, reporting_period_data_ca_len):
760
                    # line
761
                    line = LineChart()
762
                    line.title = _('Base Period Carbon Dioxide Emissions') + " / " \
763
                        + _('Reporting Period Carbon Dioxide Emissions') + ' - ' \
764
                        + reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
765
                    labels = Reference(ws, min_col=2 + base_period_data_ca_len + 1 + 1,
766
                                       min_row=table_start_row_number + 1,
767
                                       max_row=table_start_row_number + len(reporting_period_timestamps[0]))
768
                    base_line_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number,
769
                                               max_row=table_start_row_number + len(reporting_period_timestamps[0]))
770
                    reporting_line_data = Reference(ws, min_col=3 + base_period_data_ca_len + 1 + 1 + i,
771
                                                    min_row=table_start_row_number,
772
                                                    max_row=table_start_row_number
773
                                                    + len(reporting_period_timestamps[0]))
774
                    line.add_data(base_line_data, titles_from_data=True)
775
                    line.add_data(reporting_line_data, titles_from_data=True)
776
                    line.set_categories(labels)
777
                    for j in range(len(line.series)):
778
                        line.series[j].marker.symbol = "auto"
779
                        line.series[j].smooth = True
780
                    line.x_axis.crosses = 'min'
781
                    line.height = 8.25
782
                    line.width = 24
783
                    chart_col = 'B'
784
                    chart_cell = chart_col + str(table_start_draw_flag + 6 * i)
785
                    ws.add_chart(line, chart_cell)
786
787
                current_row_number += 2
788
789
    ####################################################################################################################
790
    current_sheet_parameters_row_number = table_start_draw_flag + len(reporting_period_data['names']) * 6 + 1
791
    if 'parameters' not in report.keys() or \
792
            report['parameters'] is None or \
793
            'names' not in report['parameters'].keys() or \
794
            report['parameters']['names'] is None or \
795
            len(report['parameters']['names']) == 0 or \
796
            'timestamps' not in report['parameters'].keys() or \
797
            report['parameters']['timestamps'] is None or \
798
            len(report['parameters']['timestamps']) == 0 or \
799
            'values' not in report['parameters'].keys() or \
800
            report['parameters']['values'] is None or \
801
            len(report['parameters']['values']) == 0 or \
802
            timestamps_data_all_equal_0(report['parameters']['timestamps']):
803
        pass
804
    else:
805
806
        ################################################################################################################
807
        # new worksheet
808
        ################################################################################################################
809
810
        parameters_data = report['parameters']
811
        parameters_names_len = len(parameters_data['names'])
812
813
        file_name = (re.sub(r'[^A-Z]', '', ws.title))+'_'
814
        parameters_ws = wb.create_sheet(file_name + _('Parameters'))
815
816
        parameters_timestamps_data_max_len = \
817
            get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
818
819
        # Row height
820
        parameters_ws.row_dimensions[1].height = 102
821
        for i in range(2, 7 + 1):
822
            parameters_ws.row_dimensions[i].height = 42
823
824
        for i in range(8, parameters_timestamps_data_max_len + 10):
825
            parameters_ws.row_dimensions[i].height = 60
826
827
        # Col width
828
        parameters_ws.column_dimensions['A'].width = 1.5
829
830
        parameters_ws.column_dimensions['B'].width = 25.0
831
832
        for i in range(3, 12+parameters_names_len*3):
833
            parameters_ws.column_dimensions[format_cell.get_column_letter(i)].width = 15.0
834
835
        # Img
836
        img = Image("excelexporters/myems.png")
837
        parameters_ws.add_image(img, 'A1')
838
839
        # Title
840
841
        parameters_ws['B3'].alignment = b_r_alignment
842
        parameters_ws['B3'] = _('Name') + ':'
843
        parameters_ws['C3'].border = b_border
844
        parameters_ws['C3'].alignment = b_c_alignment
845
        parameters_ws['C3'] = name
846
847
        parameters_ws['D3'].alignment = b_r_alignment
848
        parameters_ws['D3'] = _('Period Type') + ':'
849
        parameters_ws['E3'].border = b_border
850
        parameters_ws['E3'].alignment = b_c_alignment
851
        parameters_ws['E3'] = period_type
852
853
        parameters_ws['B4'].alignment = b_r_alignment
854
        parameters_ws['B4'] = _('Reporting Start Datetime') + ':'
855
        parameters_ws['C4'].border = b_border
856
        parameters_ws['C4'].alignment = b_c_alignment
857
        parameters_ws['C4'] = reporting_start_datetime_local
858
859
        parameters_ws['D4'].alignment = b_r_alignment
860
        parameters_ws['D4'] = _('Reporting End Datetime') + ':'
861
        parameters_ws['E4'].border = b_border
862
        parameters_ws['E4'].alignment = b_c_alignment
863
        parameters_ws['E4'] = reporting_end_datetime_local
864
865
        parameters_ws_current_row_number = 6
866
867
        parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font
868
        parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' ' + _('Parameters')
869
870
        parameters_ws_current_row_number += 1
871
872
        parameters_table_start_row_number = parameters_ws_current_row_number
873
874
        parameters_ws.row_dimensions[parameters_ws_current_row_number].height = 80
875
876
        parameters_ws_current_row_number += 1
877
878
        table_current_col_number = 2
879
880
        for i in range(0, parameters_names_len):
881
882
            if len(parameters_data['timestamps'][i]) == 0:
883
                continue
884
885
            col = format_cell.get_column_letter(table_current_col_number)
886
887
            parameters_ws[col + str(parameters_ws_current_row_number-1)].fill = table_fill
888
            parameters_ws[col + str(parameters_ws_current_row_number-1)].border = f_border
889
890
            col = format_cell.get_column_letter(table_current_col_number + 1)
891
892
            parameters_ws[col + str(parameters_ws_current_row_number-1)].fill = table_fill
893
            parameters_ws[col + str(parameters_ws_current_row_number-1)].border = f_border
894
            parameters_ws[col + str(parameters_ws_current_row_number-1)].font = name_font
895
            parameters_ws[col + str(parameters_ws_current_row_number-1)].alignment = c_c_alignment
896
            parameters_ws[col + str(parameters_ws_current_row_number-1)] = parameters_data['names'][i]
897
898
            table_current_row_number = parameters_ws_current_row_number
899
900
            for j, value in enumerate(list(parameters_data['timestamps'][i])):
901
                col = format_cell.get_column_letter(table_current_col_number)
902
903
                parameters_ws[col + str(table_current_row_number)].border = f_border
904
                parameters_ws[col + str(table_current_row_number)].font = title_font
905
                parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
906
                parameters_ws[col + str(table_current_row_number)] = value
907
908
                col = format_cell.get_column_letter(table_current_col_number + 1)
909
910
                parameters_ws[col + str(table_current_row_number)].border = f_border
911
                parameters_ws[col + str(table_current_row_number)].font = title_font
912
                parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
913
                parameters_ws[col + str(table_current_row_number)] = round2(parameters_data['values'][i][j], 2)
914
915
                table_current_row_number += 1
916
917
            table_current_col_number = table_current_col_number + 3
918
919
        ################################################################################################################
920
        # parameters chart and parameters table
921
        ################################################################################################################
922
923
        ws['B' + str(current_sheet_parameters_row_number)].font = title_font
924
        ws['B' + str(current_sheet_parameters_row_number)] = name + ' ' + _('Parameters')
925
926
        current_sheet_parameters_row_number += 1
927
928
        chart_start_row_number = current_sheet_parameters_row_number
929
930
        col_index = 0
931
932
        for i in range(0, parameters_names_len):
933
934
            if len(parameters_data['timestamps'][i]) == 0:
935
                continue
936
937
            line = LineChart()
938
            data_col = 3+col_index*3
939
            labels_col = 2+col_index*3
940
            col_index += 1
941
            line.title = _('Parameters') + ' - ' + \
942
                parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
943
            labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1,
944
                               max_row=(len(parameters_data['timestamps'][i])+parameters_table_start_row_number))
945
            line_data = Reference(parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number,
946
                                  max_row=(len(parameters_data['timestamps'][i])+parameters_table_start_row_number))
947
            line.add_data(line_data, titles_from_data=True)
948
            line.set_categories(labels)
949
            line_data = line.series[0]
950
            line_data.marker.symbol = "auto"
951
            line_data.smooth = True
952
            line.x_axis.crosses = 'min'
953
            line.height = 8.25
954
            line.width = 24
955
            chart_col = 'B'
956
            chart_cell = chart_col + str(chart_start_row_number)
957
            chart_start_row_number += 6
958
            ws.add_chart(line, chart_cell)
959
960
        current_sheet_parameters_row_number = chart_start_row_number
961
962
        current_sheet_parameters_row_number += 1
963
    ####################################################################################################################
964
    filename = str(uuid.uuid4()) + '.xlsx'
965
    wb.save(filename)
966
967
    return filename
968
969
970
def timestamps_data_all_equal_0(lists):
971
    for i, value in enumerate(list(lists)):
972
        if len(value) > 0:
973
            return False
974
975
    return True
976
977
978
def get_parameters_timestamps_lists_max_len(parameters_timestamps_lists):
979
    max_len = 0
980
    for i, value in enumerate(list(parameters_timestamps_lists)):
981
        if len(value) > max_len:
982
            max_len = len(value)
983
984
    return max_len
985
986
987
def timestamps_data_not_equal_0(lists):
988
    number = 0
989
    for i, value in enumerate(list(lists)):
990
        if len(value) > 0:
991
            number += 1
992
    return number
993
994
995 View Code Duplication
def is_base_period_timestamp_exists(base_period_data):
0 ignored issues
show
This code seems to be duplicated in your project.
Loading history...
996
    timestamps = base_period_data['timestamps']
997
998
    if len(timestamps) == 0:
999
        return False
1000
1001
    for timestamp in timestamps:
1002
        if len(timestamp) > 0:
1003
            return True
1004
1005
    return False
1006