Code Duplication    Length = 295-302 lines in 4 locations

excelexporters/virtualmetercost.py 1 location

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

excelexporters/metercost.py 1 location

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

excelexporters/offlinemetercost.py 1 location

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

excelexporters/virtualmeterenergy.py 1 location

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