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