Issues (1577)

myems-api/excelexporters/equipmentload.py (3 issues)

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