@@ 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 |
@@ 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 |
@@ 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 |
@@ 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 |