Code Duplication    Length = 427-428 lines in 2 locations

excelexporters/tenantload.py 1 location

@@ 65-492 (lines=428) @@
62
    return base64_message
63
64
65
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type):
66
    wb = Workbook()
67
68
    # todo
69
    ws = wb.active
70
71
    # Row height
72
    ws.row_dimensions[1].height = 102
73
    for i in range(2, 2000 + 1):
74
        ws.row_dimensions[i].height = 42
75
76
    # Col width
77
    ws.column_dimensions['A'].width = 1.5
78
79
    ws.column_dimensions['B'].width = 25.0
80
81
    for i in range(ord('C'), ord('L')):
82
        ws.column_dimensions[chr(i)].width = 15.0
83
84
    # Font
85
    name_font = Font(name='Constantia', size=15, bold=True)
86
    title_font = Font(name='宋体', size=15, bold=True)
87
    data_font = Font(name='Franklin Gothic Book', size=11)
88
89
    table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
90
    f_border = Border(left=Side(border_style='medium', color='00000000'),
91
                      right=Side(border_style='medium', color='00000000'),
92
                      bottom=Side(border_style='medium', color='00000000'),
93
                      top=Side(border_style='medium', color='00000000')
94
                      )
95
    b_border = Border(
96
        bottom=Side(border_style='medium', color='00000000'),
97
    )
98
99
    b_c_alignment = Alignment(vertical='bottom',
100
                              horizontal='center',
101
                              text_rotation=0,
102
                              wrap_text=True,
103
                              shrink_to_fit=False,
104
                              indent=0)
105
    c_c_alignment = Alignment(vertical='center',
106
                              horizontal='center',
107
                              text_rotation=0,
108
                              wrap_text=True,
109
                              shrink_to_fit=False,
110
                              indent=0)
111
    b_r_alignment = Alignment(vertical='bottom',
112
                              horizontal='right',
113
                              text_rotation=0,
114
                              wrap_text=True,
115
                              shrink_to_fit=False,
116
                              indent=0)
117
    c_r_alignment = Alignment(vertical='bottom',
118
                              horizontal='center',
119
                              text_rotation=0,
120
                              wrap_text=True,
121
                              shrink_to_fit=False,
122
                              indent=0)
123
124
    # Img
125
    img = Image("excelexporters/myems.png")
126
    img.width = img.width * 0.85
127
    img.height = img.height * 0.85
128
    # img = Image("myems.png")
129
    ws.add_image(img, 'B1')
130
131
    # Title
132
    ws.row_dimensions[3].height = 60
133
134
    ws['B3'].font = name_font
135
    ws['B3'].alignment = b_r_alignment
136
    ws['B3'] = 'Name:'
137
    ws['C3'].border = b_border
138
    ws['C3'].alignment = b_c_alignment
139
    ws['C3'].font = name_font
140
    ws['C3'] = name
141
142
    ws['D3'].font = name_font
143
    ws['D3'].alignment = b_r_alignment
144
    ws['D3'] = 'Period:'
145
    ws['E3'].border = b_border
146
    ws['E3'].alignment = b_c_alignment
147
    ws['E3'].font = name_font
148
    ws['E3'] = period_type
149
150
    ws['F3'].font = name_font
151
    ws['F3'].alignment = b_r_alignment
152
    ws['F3'] = 'Date:'
153
    ws['G3'].alignment = b_c_alignment
154
    ws['G3'].font = name_font
155
    ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
156
    ws.merge_cells("G3:H3")
157
158
    if "reporting_period" not in report.keys() or \
159
            "timestamps" not in report['reporting_period'].keys() or len(report['reporting_period']['timestamps']) == 0:
160
        filename = str(uuid.uuid4()) + '.xlsx'
161
        wb.save(filename)
162
163
        return filename
164
165
    ###############################
166
167
    has_names_data_flag = True
168
169
    if "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
170
        has_names_data_flag = False
171
172
    current_row_number = 6
173
    if has_names_data_flag:
174
        reporting_period_data = report['reporting_period']
175
        category = reporting_period_data['names']
176
        ca_len = len(category)
177
178
        ws['B' + str(current_row_number)].font = title_font
179
        ws['B' + str(current_row_number)] = name + '报告期平均负荷'
180
181
        current_row_number += 1
182
183
        ws.row_dimensions[current_row_number].height = 60
184
        ws['B' + str(current_row_number)].fill = table_fill
185
        ws['B' + str(current_row_number)].border = f_border
186
187
        for i in range(0, ca_len):
188
            col = chr(ord('C') + i)
189
            ws[col + str(current_row_number)].fill = table_fill
190
            ws[col + str(current_row_number)].font = name_font
191
            ws[col + str(current_row_number)].alignment = c_c_alignment
192
            ws[col + str(current_row_number)].border = f_border
193
            ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \
194
                " (" + reporting_period_data['units'][i] + "/H)"
195
196
        current_row_number += 1
197
198
        ws['B' + str(current_row_number)].font = title_font
199
        ws['B' + str(current_row_number)].alignment = c_c_alignment
200
        ws['B' + str(current_row_number)].border = f_border
201
        ws['B' + str(current_row_number)] = '平均负荷'
202
203
        for i in range(0, ca_len):
204
            col = chr(ord('C') + i)
205
            ws[col + str(current_row_number)].font = name_font
206
            ws[col + str(current_row_number)].alignment = c_c_alignment
207
            ws[col + str(current_row_number)].border = f_border
208
            ws[col + str(current_row_number)] = round(reporting_period_data['averages'][i], 2)
209
210
        current_row_number += 1
211
212
        ws['B' + str(current_row_number)].font = title_font
213
        ws['B' + str(current_row_number)].alignment = c_c_alignment
214
        ws['B' + str(current_row_number)].border = f_border
215
        ws['B' + str(current_row_number)] = '单位面积值'
216
217
        for i in range(0, ca_len):
218
            col = chr(ord('C') + i)
219
            ws[col + str(current_row_number)].font = name_font
220
            ws[col + str(current_row_number)].alignment = c_c_alignment
221
            ws[col + str(current_row_number)].border = f_border
222
            ws[col + str(current_row_number)] = round(reporting_period_data['averages_per_unit_area'][i], 2)
223
224
        current_row_number += 1
225
226
        ws['B' + str(current_row_number)].font = title_font
227
        ws['B' + str(current_row_number)].alignment = c_c_alignment
228
        ws['B' + str(current_row_number)].border = f_border
229
        ws['B' + str(current_row_number)] = '环比'
230
231
        for i in range(0, ca_len):
232
            col = chr(ord('C') + i)
233
            ws[col + str(current_row_number)].font = name_font
234
            ws[col + str(current_row_number)].alignment = c_c_alignment
235
            ws[col + str(current_row_number)].border = f_border
236
            ws[col + str(current_row_number)] = str(
237
                round(reporting_period_data['averages_increment_rate'][i] * 100, 2)) + "%" \
238
                if reporting_period_data['averages_increment_rate'][i] is not None else "-"
239
240
        current_row_number += 2
241
242
        ws['B' + str(current_row_number)].font = title_font
243
        ws['B' + str(current_row_number)] = name + '报告期最大负荷'
244
245
        current_row_number += 1
246
247
        ws.row_dimensions[current_row_number].height = 60
248
        ws['B' + str(current_row_number)].fill = table_fill
249
        ws['B' + str(current_row_number)].border = f_border
250
        for i in range(0, ca_len):
251
            col = chr(ord('C') + i)
252
            ws[col + str(current_row_number)].fill = table_fill
253
            ws[col + str(current_row_number)].font = name_font
254
            ws[col + str(current_row_number)].alignment = c_c_alignment
255
            ws[col + str(current_row_number)].border = f_border
256
            ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \
257
                " (" + reporting_period_data['units'][i] + "/H)"
258
259
        current_row_number += 1
260
261
        ws['B' + str(current_row_number)].font = title_font
262
        ws['B' + str(current_row_number)].alignment = c_c_alignment
263
        ws['B' + str(current_row_number)].border = f_border
264
        ws['B' + str(current_row_number)] = '最大负荷'
265
266
        for i in range(0, ca_len):
267
            col = chr(ord('C') + i)
268
            ws[col + str(current_row_number)].font = name_font
269
            ws[col + str(current_row_number)].alignment = c_c_alignment
270
            ws[col + str(current_row_number)].border = f_border
271
            ws[col + str(current_row_number)] = round(reporting_period_data['maximums'][i], 2)
272
273
        current_row_number += 1
274
275
        ws['B' + str(current_row_number)].font = title_font
276
        ws['B' + str(current_row_number)].alignment = c_c_alignment
277
        ws['B' + str(current_row_number)].border = f_border
278
        ws['B' + str(current_row_number)] = '单位面积值'
279
280
        for i in range(0, ca_len):
281
            col = chr(ord('C') + i)
282
            ws[col + str(current_row_number)].font = name_font
283
            ws[col + str(current_row_number)].alignment = c_c_alignment
284
            ws[col + str(current_row_number)].border = f_border
285
            ws[col + str(current_row_number)] = round(reporting_period_data['maximums_per_unit_area'][i], 2)
286
287
        current_row_number += 1
288
289
        ws['B' + str(current_row_number)].font = title_font
290
        ws['B' + str(current_row_number)].alignment = c_c_alignment
291
        ws['B' + str(current_row_number)].border = f_border
292
        ws['B' + str(current_row_number)] = '环比'
293
294
        for i in range(0, ca_len):
295
            col = chr(ord('C') + i)
296
            ws[col + str(current_row_number)].font = name_font
297
            ws[col + str(current_row_number)].alignment = c_c_alignment
298
            ws[col + str(current_row_number)].border = f_border
299
            ws[col + str(current_row_number)] = str(
300
                round(reporting_period_data['maximums_increment_rate'][i] * 100, 2)) + "%" \
301
                if reporting_period_data['maximums_increment_rate'][i] is not None else "-"
302
303
        current_row_number += 2
304
305
        ws['B' + str(current_row_number)].font = title_font
306
        ws['B' + str(current_row_number)] = name + '报告期负荷系数'
307
308
        current_row_number += 1
309
310
        ws.row_dimensions[current_row_number].height = 60
311
        ws['B' + str(current_row_number)].fill = table_fill
312
        ws['B' + str(current_row_number)].border = f_border
313
        for i in range(0, ca_len):
314
            col = chr(ord('C') + i)
315
            ws[col + str(current_row_number)].fill = table_fill
316
            ws[col + str(current_row_number)].font = name_font
317
            ws[col + str(current_row_number)].alignment = c_c_alignment
318
            ws[col + str(current_row_number)].border = f_border
319
            ws[col + str(current_row_number)] = reporting_period_data['names'][i]
320
321
        current_row_number += 1
322
323
        ws['B' + str(current_row_number)].font = title_font
324
        ws['B' + str(current_row_number)].alignment = c_c_alignment
325
        ws['B' + str(current_row_number)].border = f_border
326
        ws['B' + str(current_row_number)] = '负荷系数'
327
328
        for i in range(0, ca_len):
329
            col = chr(ord('C') + i)
330
            ws[col + str(current_row_number)].font = name_font
331
            ws[col + str(current_row_number)].alignment = c_c_alignment
332
            ws[col + str(current_row_number)].border = f_border
333
            ws[col + str(current_row_number)] = round(reporting_period_data['factors'][i], 2) \
334
                if reporting_period_data['factors'][i] is not None else '-'
335
336
        current_row_number += 1
337
338
        ws['B' + str(current_row_number)].font = title_font
339
        ws['B' + str(current_row_number)].alignment = c_c_alignment
340
        ws['B' + str(current_row_number)].border = f_border
341
        ws['B' + str(current_row_number)] = '环比'
342
343
        for i in range(0, ca_len):
344
            col = chr(ord('C') + i)
345
            ws[col + str(current_row_number)].font = name_font
346
            ws[col + str(current_row_number)].alignment = c_c_alignment
347
            ws[col + str(current_row_number)].border = f_border
348
            ws[col + str(current_row_number)] = str(
349
                round(reporting_period_data['factors_increment_rate'][i] * 100, 2)) + "%" \
350
                if reporting_period_data['factors_increment_rate'][i] is not None else "-"
351
352
        current_row_number += 2
353
354
    has_sub_averages_data_flag = True
355
    has_sub_maximums_data_flag = True
356
357
    if "sub_averages" not in report['reporting_period'].keys() or len(report['reporting_period']['sub_averages']) == 0:
358
        has_sub_averages_data_flag = False
359
360
    if "sub_averages" not in report['reporting_period'].keys() or len(report['reporting_period']['sub_averages']) == 0:
361
        has_sub_maximums_data_flag = False
362
363
    if has_sub_averages_data_flag or has_sub_maximums_data_flag:
364
        reporting_period_data = report['reporting_period']
365
        category = reporting_period_data['names']
366
        ca_len = len(category)
367
        times = reporting_period_data['timestamps']
368
        time = times[0]
369
370
        ws['B' + str(current_row_number)].font = title_font
371
        ws['B' + str(current_row_number)] = name + '详细数据'
372
373
        current_row_number += 1
374
        chart_start_number = current_row_number
375
376
        if has_sub_averages_data_flag:
377
            current_row_number = (current_row_number + ca_len * 6)
378
379
        if has_sub_maximums_data_flag:
380
            current_row_number = (current_row_number + ca_len * 6)
381
382
        table_start_number = current_row_number
383
384
        ws.row_dimensions[current_row_number].height = 60
385
        ws['B' + str(current_row_number)].fill = table_fill
386
        ws['B' + str(current_row_number)].font = title_font
387
        ws['B' + str(current_row_number)].alignment = c_c_alignment
388
        ws['B' + str(current_row_number)].border = f_border
389
        ws['B' + str(current_row_number)] = '日期时间'
390
391
        col = 'C'
392
393
        for i in range(0, ca_len):
394
            if has_sub_averages_data_flag:
395
                ws[col + str(current_row_number)].fill = table_fill
396
                ws[col + str(current_row_number)].font = title_font
397
                ws[col + str(current_row_number)].alignment = c_c_alignment
398
                ws[col + str(current_row_number)].border = f_border
399
                ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \
400
                    " 平均负荷(" + reporting_period_data['units'][i] + "/H)"
401
                col = chr(ord(col) + 1)
402
403
            if has_sub_maximums_data_flag:
404
                ws[col + str(current_row_number)].fill = table_fill
405
                ws[col + str(current_row_number)].font = title_font
406
                ws[col + str(current_row_number)].alignment = c_c_alignment
407
                ws[col + str(current_row_number)].border = f_border
408
                ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \
409
                    " 最大负荷(" + reporting_period_data['units'][i] + "/H)"
410
                col = chr(ord(col) + 1)
411
412
        current_row_number += 1
413
414
        for i in range(0, len(time)):
415
            ws['B' + str(current_row_number)].font = title_font
416
            ws['B' + str(current_row_number)].alignment = c_c_alignment
417
            ws['B' + str(current_row_number)].border = f_border
418
            ws['B' + str(current_row_number)] = time[i]
419
420
            col = 'C'
421
            for j in range(0, ca_len):
422
423
                if has_sub_averages_data_flag:
424
                    ws[col + str(current_row_number)].font = title_font
425
                    ws[col + str(current_row_number)].alignment = c_c_alignment
426
                    ws[col + str(current_row_number)].border = f_border
427
                    ws[col + str(current_row_number)] = round(reporting_period_data['sub_averages'][j][i], 2) \
428
                        if reporting_period_data['sub_averages'][j][i] is not None else 0.00
429
                    col = chr(ord(col) + 1)
430
431
                if has_sub_maximums_data_flag:
432
                    ws[col + str(current_row_number)].font = title_font
433
                    ws[col + str(current_row_number)].alignment = c_c_alignment
434
                    ws[col + str(current_row_number)].border = f_border
435
                    ws[col + str(current_row_number)] = round(reporting_period_data['sub_maximums'][j][i], 2) \
436
                        if reporting_period_data['sub_maximums'][j][i] is not None else 0.00
437
                    col = chr(ord(col) + 1)
438
439
            current_row_number += 1
440
441
        table_end_number = current_row_number - 1
442
443
        current_chart_col_number = 3
444
        current_chart_row_number = chart_start_number
445
446
        for i in range(0, ca_len):
447
            labels = Reference(ws, min_col=2, min_row=table_start_number + 1, max_row=table_end_number)
448
449
            if has_sub_averages_data_flag:
450
                line = LineChart()
451
                line.title = '报告期 平均负荷 - ' + ws.cell(column=current_chart_col_number, row=table_start_number).value
452
                datas = Reference(ws, min_col=current_chart_col_number, min_row=table_start_number,
453
                                  max_row=table_end_number)
454
                line.add_data(datas, titles_from_data=True)
455
                line.set_categories(labels)
456
                line_data = line.series[0]
457
                line_data.marker.symbol = "circle"
458
                line_data.smooth = True
459
                line.x_axis.crosses = 'min'
460
                line.height = 8.25
461
                line.width = 24
462
                line.dLbls = DataLabelList()
463
                line.dLbls.dLblPos = 't'
464
                line.dLbls.showVal = True
465
                ws.add_chart(line, "B" + str(current_chart_row_number))
466
                current_chart_row_number += 6
467
                current_chart_col_number += 1
468
469
            if has_sub_maximums_data_flag:
470
                line = LineChart()
471
                line.title = '报告期 最大负荷 - ' + ws.cell(column=current_chart_col_number, row=table_start_number).value
472
                datas = Reference(ws, min_col=current_chart_col_number, min_row=table_start_number,
473
                                  max_row=table_end_number)
474
                line.add_data(datas, titles_from_data=True)
475
                line.set_categories(labels)
476
                line_data = line.series[0]
477
                line_data.marker.symbol = "circle"
478
                line_data.smooth = True
479
                line.x_axis.crosses = 'min'
480
                line.height = 8.25
481
                line.width = 24
482
                line.dLbls = DataLabelList()
483
                line.dLbls.dLblPos = 't'
484
                line.dLbls.showVal = True
485
                ws.add_chart(line, "B" + str(current_chart_row_number))
486
                current_chart_row_number += 6
487
                current_chart_col_number += 1
488
489
    filename = str(uuid.uuid4()) + '.xlsx'
490
    wb.save(filename)
491
492
    return filename
493

excelexporters/spaceload.py 1 location

@@ 65-491 (lines=427) @@
62
    return base64_message
63
64
65
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type):
66
    wb = Workbook()
67
68
    # todo
69
    ws = wb.active
70
71
    # Row height
72
    ws.row_dimensions[1].height = 102
73
    for i in range(2, 2000 + 1):
74
        ws.row_dimensions[i].height = 42
75
76
    # Col width
77
    ws.column_dimensions['A'].width = 1.5
78
79
    ws.column_dimensions['B'].width = 25.0
80
81
    for i in range(ord('C'), ord('L')):
82
        ws.column_dimensions[chr(i)].width = 15.0
83
84
    # Font
85
    name_font = Font(name='Constantia', size=15, bold=True)
86
    title_font = Font(name='宋体', size=15, bold=True)
87
    data_font = Font(name='Franklin Gothic Book', size=11)
88
89
    table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
90
    f_border = Border(left=Side(border_style='medium', color='00000000'),
91
                      right=Side(border_style='medium', color='00000000'),
92
                      bottom=Side(border_style='medium', color='00000000'),
93
                      top=Side(border_style='medium', color='00000000')
94
                      )
95
    b_border = Border(
96
        bottom=Side(border_style='medium', color='00000000'),
97
    )
98
99
    b_c_alignment = Alignment(vertical='bottom',
100
                              horizontal='center',
101
                              text_rotation=0,
102
                              wrap_text=True,
103
                              shrink_to_fit=False,
104
                              indent=0)
105
    c_c_alignment = Alignment(vertical='center',
106
                              horizontal='center',
107
                              text_rotation=0,
108
                              wrap_text=True,
109
                              shrink_to_fit=False,
110
                              indent=0)
111
    b_r_alignment = Alignment(vertical='bottom',
112
                              horizontal='right',
113
                              text_rotation=0,
114
                              wrap_text=True,
115
                              shrink_to_fit=False,
116
                              indent=0)
117
    c_r_alignment = Alignment(vertical='bottom',
118
                              horizontal='center',
119
                              text_rotation=0,
120
                              wrap_text=True,
121
                              shrink_to_fit=False,
122
                              indent=0)
123
124
    # Img
125
    img = Image("excelexporters/myems.png")
126
    img.width = img.width * 0.85
127
    img.height = img.height * 0.85
128
    # img = Image("myems.png")
129
    ws.add_image(img, 'B1')
130
131
    # Title
132
    ws.row_dimensions[3].height = 60
133
134
    ws['B3'].font = name_font
135
    ws['B3'].alignment = b_r_alignment
136
    ws['B3'] = 'Name:'
137
    ws['C3'].border = b_border
138
    ws['C3'].alignment = b_c_alignment
139
    ws['C3'].font = name_font
140
    ws['C3'] = name
141
142
    ws['D3'].font = name_font
143
    ws['D3'].alignment = b_r_alignment
144
    ws['D3'] = 'Period:'
145
    ws['E3'].border = b_border
146
    ws['E3'].alignment = b_c_alignment
147
    ws['E3'].font = name_font
148
    ws['E3'] = period_type
149
150
    ws['F3'].font = name_font
151
    ws['F3'].alignment = b_r_alignment
152
    ws['F3'] = 'Date:'
153
    ws['G3'].alignment = b_c_alignment
154
    ws['G3'].font = name_font
155
    ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
156
    ws.merge_cells("G3:H3")
157
158
    if "reporting_period" not in report.keys() or \
159
            "timestamps" not in report['reporting_period'].keys() or len(report['reporting_period']['timestamps']) == 0:
160
        filename = str(uuid.uuid4()) + '.xlsx'
161
        wb.save(filename)
162
163
        return filename
164
165
    ###############################
166
167
    has_names_data_flag = True
168
169
    if "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
170
        has_names_data_flag = False
171
172
    current_row_number = 6
173
    if has_names_data_flag:
174
        reporting_period_data = report['reporting_period']
175
        category = reporting_period_data['names']
176
        ca_len = len(category)
177
178
        ws['B' + str(current_row_number)].font = title_font
179
        ws['B' + str(current_row_number)] = name + '报告期平均负荷'
180
181
        current_row_number += 1
182
183
        ws.row_dimensions[current_row_number].height = 60
184
        ws['B' + str(current_row_number)].fill = table_fill
185
        ws['B' + str(current_row_number)].border = f_border
186
        for i in range(0, ca_len):
187
            col = chr(ord('C') + i)
188
            ws[col + str(current_row_number)].fill = table_fill
189
            ws[col + str(current_row_number)].font = name_font
190
            ws[col + str(current_row_number)].alignment = c_c_alignment
191
            ws[col + str(current_row_number)].border = f_border
192
            ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \
193
                " (" + reporting_period_data['units'][i] + "/H)"
194
195
        current_row_number += 1
196
197
        ws['B' + str(current_row_number)].font = title_font
198
        ws['B' + str(current_row_number)].alignment = c_c_alignment
199
        ws['B' + str(current_row_number)].border = f_border
200
        ws['B' + str(current_row_number)] = '平均负荷'
201
202
        for i in range(0, ca_len):
203
            col = chr(ord('C') + i)
204
            ws[col + str(current_row_number)].font = name_font
205
            ws[col + str(current_row_number)].alignment = c_c_alignment
206
            ws[col + str(current_row_number)].border = f_border
207
            ws[col + str(current_row_number)] = round(reporting_period_data['averages'][i], 2)
208
209
        current_row_number += 1
210
211
        ws['B' + str(current_row_number)].font = title_font
212
        ws['B' + str(current_row_number)].alignment = c_c_alignment
213
        ws['B' + str(current_row_number)].border = f_border
214
        ws['B' + str(current_row_number)] = '单位面积值'
215
216
        for i in range(0, ca_len):
217
            col = chr(ord('C') + i)
218
            ws[col + str(current_row_number)].font = name_font
219
            ws[col + str(current_row_number)].alignment = c_c_alignment
220
            ws[col + str(current_row_number)].border = f_border
221
            ws[col + str(current_row_number)] = round(reporting_period_data['averages_per_unit_area'][i], 2)
222
223
        current_row_number += 1
224
225
        ws['B' + str(current_row_number)].font = title_font
226
        ws['B' + str(current_row_number)].alignment = c_c_alignment
227
        ws['B' + str(current_row_number)].border = f_border
228
        ws['B' + str(current_row_number)] = '环比'
229
230
        for i in range(0, ca_len):
231
            col = chr(ord('C') + i)
232
            ws[col + str(current_row_number)].font = name_font
233
            ws[col + str(current_row_number)].alignment = c_c_alignment
234
            ws[col + str(current_row_number)].border = f_border
235
            ws[col + str(current_row_number)] = str(
236
                round(reporting_period_data['averages_increment_rate'][i] * 100, 2)) + "%" \
237
                if reporting_period_data['averages_increment_rate'][i] is not None else "-"
238
239
        current_row_number += 2
240
241
        ws['B' + str(current_row_number)].font = title_font
242
        ws['B' + str(current_row_number)] = name + '报告期最大负荷'
243
244
        current_row_number += 1
245
246
        ws.row_dimensions[current_row_number].height = 60
247
        ws['B' + str(current_row_number)].fill = table_fill
248
        ws['B' + str(current_row_number)].border = f_border
249
        for i in range(0, ca_len):
250
            col = chr(ord('C') + i)
251
            ws[col + str(current_row_number)].fill = table_fill
252
            ws[col + str(current_row_number)].font = name_font
253
            ws[col + str(current_row_number)].alignment = c_c_alignment
254
            ws[col + str(current_row_number)].border = f_border
255
            ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \
256
                " (" + reporting_period_data['units'][i] + "/H)"
257
258
        current_row_number += 1
259
260
        ws['B' + str(current_row_number)].font = title_font
261
        ws['B' + str(current_row_number)].alignment = c_c_alignment
262
        ws['B' + str(current_row_number)].border = f_border
263
        ws['B' + str(current_row_number)] = '最大负荷'
264
265
        for i in range(0, ca_len):
266
            col = chr(ord('C') + i)
267
            ws[col + str(current_row_number)].font = name_font
268
            ws[col + str(current_row_number)].alignment = c_c_alignment
269
            ws[col + str(current_row_number)].border = f_border
270
            ws[col + str(current_row_number)] = round(reporting_period_data['maximums'][i], 2)
271
272
        current_row_number += 1
273
274
        ws['B' + str(current_row_number)].font = title_font
275
        ws['B' + str(current_row_number)].alignment = c_c_alignment
276
        ws['B' + str(current_row_number)].border = f_border
277
        ws['B' + str(current_row_number)] = '单位面积值'
278
279
        for i in range(0, ca_len):
280
            col = chr(ord('C') + i)
281
            ws[col + str(current_row_number)].font = name_font
282
            ws[col + str(current_row_number)].alignment = c_c_alignment
283
            ws[col + str(current_row_number)].border = f_border
284
            ws[col + str(current_row_number)] = round(reporting_period_data['maximums_per_unit_area'][i], 2)
285
286
        current_row_number += 1
287
288
        ws['B' + str(current_row_number)].font = title_font
289
        ws['B' + str(current_row_number)].alignment = c_c_alignment
290
        ws['B' + str(current_row_number)].border = f_border
291
        ws['B' + str(current_row_number)] = '环比'
292
293
        for i in range(0, ca_len):
294
            col = chr(ord('C') + i)
295
            ws[col + str(current_row_number)].font = name_font
296
            ws[col + str(current_row_number)].alignment = c_c_alignment
297
            ws[col + str(current_row_number)].border = f_border
298
            ws[col + str(current_row_number)] = str(
299
                round(reporting_period_data['maximums_increment_rate'][i] * 100, 2)) + "%" \
300
                if reporting_period_data['maximums_increment_rate'][i] is not None else "-"
301
302
        current_row_number += 2
303
304
        ws['B' + str(current_row_number)].font = title_font
305
        ws['B' + str(current_row_number)] = name + '报告期负荷系数'
306
307
        current_row_number += 1
308
309
        ws.row_dimensions[current_row_number].height = 60
310
        ws['B' + str(current_row_number)].fill = table_fill
311
        ws['B' + str(current_row_number)].border = f_border
312
        for i in range(0, ca_len):
313
            col = chr(ord('C') + i)
314
            ws[col + str(current_row_number)].fill = table_fill
315
            ws[col + str(current_row_number)].font = name_font
316
            ws[col + str(current_row_number)].alignment = c_c_alignment
317
            ws[col + str(current_row_number)].border = f_border
318
            ws[col + str(current_row_number)] = reporting_period_data['names'][i]
319
320
        current_row_number += 1
321
322
        ws['B' + str(current_row_number)].font = title_font
323
        ws['B' + str(current_row_number)].alignment = c_c_alignment
324
        ws['B' + str(current_row_number)].border = f_border
325
        ws['B' + str(current_row_number)] = '负荷系数'
326
327
        for i in range(0, ca_len):
328
            col = chr(ord('C') + i)
329
            ws[col + str(current_row_number)].font = name_font
330
            ws[col + str(current_row_number)].alignment = c_c_alignment
331
            ws[col + str(current_row_number)].border = f_border
332
            ws[col + str(current_row_number)] = round(reporting_period_data['factors'][i], 2) \
333
                if reporting_period_data['factors'][i] is not None else '-'
334
335
        current_row_number += 1
336
337
        ws['B' + str(current_row_number)].font = title_font
338
        ws['B' + str(current_row_number)].alignment = c_c_alignment
339
        ws['B' + str(current_row_number)].border = f_border
340
        ws['B' + str(current_row_number)] = '环比'
341
342
        for i in range(0, ca_len):
343
            col = chr(ord('C') + i)
344
            ws[col + str(current_row_number)].font = name_font
345
            ws[col + str(current_row_number)].alignment = c_c_alignment
346
            ws[col + str(current_row_number)].border = f_border
347
            ws[col + str(current_row_number)] = str(
348
                round(reporting_period_data['factors_increment_rate'][i] * 100, 2)) + "%" \
349
                if reporting_period_data['factors_increment_rate'][i] is not None else "-"
350
351
        current_row_number += 2
352
353
    has_sub_averages_data_flag = True
354
    has_sub_maximums_data_flag = True
355
356
    if "sub_averages" not in report['reporting_period'].keys() or len(report['reporting_period']['sub_averages']) == 0:
357
        has_sub_averages_data_flag = False
358
359
    if "sub_averages" not in report['reporting_period'].keys() or len(report['reporting_period']['sub_averages']) == 0:
360
        has_sub_maximums_data_flag = False
361
362
    if has_sub_averages_data_flag or has_sub_maximums_data_flag:
363
        reporting_period_data = report['reporting_period']
364
        category = reporting_period_data['names']
365
        ca_len = len(category)
366
        times = reporting_period_data['timestamps']
367
        time = times[0]
368
369
        ws['B' + str(current_row_number)].font = title_font
370
        ws['B' + str(current_row_number)] = name + '详细数据'
371
372
        current_row_number += 1
373
        chart_start_number = current_row_number
374
375
        if has_sub_averages_data_flag:
376
            current_row_number = (current_row_number + ca_len * 6)
377
378
        if has_sub_maximums_data_flag:
379
            current_row_number = (current_row_number + ca_len * 6)
380
381
        table_start_number = current_row_number
382
383
        ws.row_dimensions[current_row_number].height = 60
384
        ws['B' + str(current_row_number)].fill = table_fill
385
        ws['B' + str(current_row_number)].font = title_font
386
        ws['B' + str(current_row_number)].alignment = c_c_alignment
387
        ws['B' + str(current_row_number)].border = f_border
388
        ws['B' + str(current_row_number)] = '日期时间'
389
390
        col = 'C'
391
392
        for i in range(0, ca_len):
393
            if has_sub_averages_data_flag:
394
                ws[col + str(current_row_number)].fill = table_fill
395
                ws[col + str(current_row_number)].font = title_font
396
                ws[col + str(current_row_number)].alignment = c_c_alignment
397
                ws[col + str(current_row_number)].border = f_border
398
                ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \
399
                    " 平均负荷(" + reporting_period_data['units'][i] + "/H)"
400
                col = chr(ord(col) + 1)
401
402
            if has_sub_maximums_data_flag:
403
                ws[col + str(current_row_number)].fill = table_fill
404
                ws[col + str(current_row_number)].font = title_font
405
                ws[col + str(current_row_number)].alignment = c_c_alignment
406
                ws[col + str(current_row_number)].border = f_border
407
                ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \
408
                    " 最大负荷(" + reporting_period_data['units'][i] + "/H)"
409
                col = chr(ord(col) + 1)
410
411
        current_row_number += 1
412
413
        for i in range(0, len(time)):
414
            ws['B' + str(current_row_number)].font = title_font
415
            ws['B' + str(current_row_number)].alignment = c_c_alignment
416
            ws['B' + str(current_row_number)].border = f_border
417
            ws['B' + str(current_row_number)] = time[i]
418
419
            col = 'C'
420
            for j in range(0, ca_len):
421
422
                if has_sub_averages_data_flag:
423
                    ws[col + str(current_row_number)].font = title_font
424
                    ws[col + str(current_row_number)].alignment = c_c_alignment
425
                    ws[col + str(current_row_number)].border = f_border
426
                    ws[col + str(current_row_number)] = round(reporting_period_data['sub_averages'][j][i], 2) \
427
                        if reporting_period_data['sub_averages'][j][i] is not None else 0.00
428
                    col = chr(ord(col) + 1)
429
430
                if has_sub_maximums_data_flag:
431
                    ws[col + str(current_row_number)].font = title_font
432
                    ws[col + str(current_row_number)].alignment = c_c_alignment
433
                    ws[col + str(current_row_number)].border = f_border
434
                    ws[col + str(current_row_number)] = round(reporting_period_data['sub_maximums'][j][i], 2) \
435
                        if reporting_period_data['sub_maximums'][j][i] is not None else 0.00
436
                    col = chr(ord(col) + 1)
437
438
            current_row_number += 1
439
440
        table_end_number = current_row_number - 1
441
442
        current_chart_col_number = 3
443
        current_chart_row_number = chart_start_number
444
445
        for i in range(0, ca_len):
446
            labels = Reference(ws, min_col=2, min_row=table_start_number + 1, max_row=table_end_number)
447
448
            if has_sub_averages_data_flag:
449
                line = LineChart()
450
                line.title = '报告期 平均负荷 - ' + ws.cell(column=current_chart_col_number, row=table_start_number).value
451
                datas = Reference(ws, min_col=current_chart_col_number, min_row=table_start_number,
452
                                  max_row=table_end_number)
453
                line.add_data(datas, titles_from_data=True)
454
                line.set_categories(labels)
455
                line_data = line.series[0]
456
                line_data.marker.symbol = "circle"
457
                line_data.smooth = True
458
                line.x_axis.crosses = 'min'
459
                line.height = 8.25
460
                line.width = 24
461
                line.dLbls = DataLabelList()
462
                line.dLbls.dLblPos = 't'
463
                line.dLbls.showVal = True
464
                ws.add_chart(line, "B" + str(current_chart_row_number))
465
                current_chart_row_number += 6
466
                current_chart_col_number += 1
467
468
            if has_sub_maximums_data_flag:
469
                line = LineChart()
470
                line.title = '报告期 最大负荷 - ' + ws.cell(column=current_chart_col_number, row=table_start_number).value
471
                datas = Reference(ws, min_col=current_chart_col_number, min_row=table_start_number,
472
                                  max_row=table_end_number)
473
                line.add_data(datas, titles_from_data=True)
474
                line.set_categories(labels)
475
                line_data = line.series[0]
476
                line_data.marker.symbol = "circle"
477
                line_data.smooth = True
478
                line.x_axis.crosses = 'min'
479
                line.height = 8.25
480
                line.width = 24
481
                line.dLbls = DataLabelList()
482
                line.dLbls.dLblPos = 't'
483
                line.dLbls.showVal = True
484
                ws.add_chart(line, "B" + str(current_chart_row_number))
485
                current_chart_row_number += 6
486
                current_chart_col_number += 1
487
488
    filename = str(uuid.uuid4()) + '.xlsx'
489
    wb.save(filename)
490
491
    return filename
492