Total Complexity | 74 |
Total Lines | 662 |
Duplicated Lines | 28.55 % |
Changes | 0 |
Duplicate code is one of the most pungent code smells. A rule that is often used is to re-structure code once it is duplicated in three or more places.
Common duplication problems, and corresponding solutions are:
Complex classes like excelexporters.shopfloorload often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
1 | import base64 |
||
2 | import uuid |
||
3 | import os |
||
4 | from openpyxl.chart import ( |
||
5 | LineChart, |
||
6 | Reference, |
||
7 | ) |
||
8 | from openpyxl.chart.label import DataLabelList |
||
9 | from openpyxl.styles import PatternFill, Border, Side, Alignment, Font |
||
10 | from openpyxl.drawing.image import Image |
||
11 | from openpyxl import Workbook |
||
12 | import openpyxl.utils.cell as format_cell |
||
13 | |||
14 | |||
15 | #################################################################################################################### |
||
16 | # PROCEDURES |
||
17 | # Step 1: Validate the report data |
||
18 | # Step 2: Generate excel file |
||
19 | # Step 3: Encode the excel file bytes to Base64 |
||
20 | #################################################################################################################### |
||
21 | |||
22 | |||
23 | View Code Duplication | def export(report, |
|
|
|||
24 | name, |
||
25 | reporting_start_datetime_local, |
||
26 | reporting_end_datetime_local, |
||
27 | period_type): |
||
28 | #################################################################################################################### |
||
29 | # Step 1: Validate the report data |
||
30 | #################################################################################################################### |
||
31 | if report is None: |
||
32 | return None |
||
33 | |||
34 | #################################################################################################################### |
||
35 | # Step 2: Generate excel file from the report data |
||
36 | #################################################################################################################### |
||
37 | filename = generate_excel(report, |
||
38 | name, |
||
39 | reporting_start_datetime_local, |
||
40 | reporting_end_datetime_local, |
||
41 | period_type) |
||
42 | #################################################################################################################### |
||
43 | # Step 3: Encode the excel file to Base64 |
||
44 | #################################################################################################################### |
||
45 | try: |
||
46 | with open(filename, 'rb') as binary_file: |
||
47 | binary_file_data = binary_file.read() |
||
48 | except IOError as ex: |
||
49 | pass |
||
50 | |||
51 | # Base64 encode the bytes |
||
52 | base64_encoded_data = base64.b64encode(binary_file_data) |
||
53 | # get the Base64 encoded data using human-readable characters. |
||
54 | base64_message = base64_encoded_data.decode('utf-8') |
||
55 | # delete the file from server |
||
56 | try: |
||
57 | os.remove(filename) |
||
58 | except NotImplementedError as ex: |
||
59 | pass |
||
60 | return base64_message |
||
61 | |||
62 | |||
63 | View Code Duplication | def decimal_to_column(num=65): |
|
64 | string = '' |
||
65 | num = num - 64 |
||
66 | # The column number is not greater than 90 |
||
67 | if num <= 26: |
||
68 | return chr(num + 64) |
||
69 | # The column number is greater than 90 |
||
70 | while num // 26 > 0: |
||
71 | if num % 26 == 0: |
||
72 | string += 'Z' |
||
73 | num = num // 26 - 1 |
||
74 | else: |
||
75 | string += chr(num % 26 + 64) |
||
76 | num //= 26 |
||
77 | # Avoid conversion errors that might occur between 741 and 766 |
||
78 | if num > 0: |
||
79 | string += chr(num + 64) |
||
80 | |||
81 | return string[::-1] |
||
82 | |||
83 | |||
84 | def column_to_decimal(string='A'): |
||
85 | num = 0 |
||
86 | for index, key in enumerate(string[::-1]): |
||
87 | num += (ord(key) - 64) * (26 ** index) |
||
88 | |||
89 | return num + 64 |
||
90 | |||
91 | |||
92 | def get_parameters_timestamps_lists_max_len(parameters_timestamps_lists): |
||
93 | max_len = 0 |
||
94 | for i, value in enumerate(list(parameters_timestamps_lists)): |
||
95 | if len(value) > max_len: |
||
96 | max_len = len(value) |
||
97 | |||
98 | return max_len |
||
99 | |||
100 | |||
101 | def timestamps_data_all_equal_0(lists): |
||
102 | for i, value in enumerate(list(lists)): |
||
103 | if len(value) > 0: |
||
104 | return False |
||
105 | |||
106 | return True |
||
107 | |||
108 | |||
109 | def generate_excel(report, |
||
110 | name, |
||
111 | reporting_start_datetime_local, |
||
112 | reporting_end_datetime_local, |
||
113 | period_type): |
||
114 | wb = Workbook() |
||
115 | ws = wb.active |
||
116 | |||
117 | # Row height |
||
118 | ws.row_dimensions[1].height = 102 |
||
119 | |||
120 | for i in range(2, 2000 + 1): |
||
121 | ws.row_dimensions[i].height = 42 |
||
122 | |||
123 | # Col width |
||
124 | ws.column_dimensions['A'].width = 1.5 |
||
125 | ws.column_dimensions['B'].width = 25.0 |
||
126 | |||
127 | for i in range(ord('C'), ord('L')): |
||
128 | ws.column_dimensions[chr(i)].width = 15.0 |
||
129 | |||
130 | # Font |
||
131 | name_font = Font(name='Constantia', size=15, bold=True) |
||
132 | title_font = Font(name='宋体', size=15, bold=True) |
||
133 | # data_font = Font(name='Franklin Gothic Book', size=11) |
||
134 | |||
135 | table_fill = PatternFill(fill_type='solid', fgColor='1F497D') |
||
136 | f_border = Border(left=Side(border_style='medium', color='00000000'), |
||
137 | right=Side(border_style='medium', color='00000000'), |
||
138 | bottom=Side(border_style='medium', color='00000000'), |
||
139 | top=Side(border_style='medium', color='00000000') |
||
140 | ) |
||
141 | b_border = Border( |
||
142 | bottom=Side(border_style='medium', color='00000000'), |
||
143 | ) |
||
144 | |||
145 | b_c_alignment = Alignment(vertical='bottom', |
||
146 | horizontal='center', |
||
147 | text_rotation=0, |
||
148 | wrap_text=False, |
||
149 | shrink_to_fit=False, |
||
150 | indent=0) |
||
151 | c_c_alignment = Alignment(vertical='center', |
||
152 | horizontal='center', |
||
153 | text_rotation=0, |
||
154 | wrap_text=True, |
||
155 | shrink_to_fit=False, |
||
156 | indent=0) |
||
157 | b_r_alignment = Alignment(vertical='bottom', |
||
158 | horizontal='right', |
||
159 | text_rotation=0, |
||
160 | wrap_text=False, |
||
161 | shrink_to_fit=False, |
||
162 | indent=0) |
||
163 | |||
164 | # Img |
||
165 | img = Image("excelexporters/myems.png") |
||
166 | # img = Image("myems.png") |
||
167 | img.width = img.width * 0.85 |
||
168 | img.height = img.height * 0.85 |
||
169 | ws.add_image(img, 'B1') |
||
170 | |||
171 | # Title |
||
172 | ws['B3'].font = name_font |
||
173 | ws['B3'].alignment = b_r_alignment |
||
174 | ws['B3'] = 'Name:' |
||
175 | ws['C3'].border = b_border |
||
176 | ws['C3'].alignment = b_c_alignment |
||
177 | ws['C3'].font = name_font |
||
178 | ws['C3'] = name |
||
179 | |||
180 | ws['D3'].font = name_font |
||
181 | ws['D3'].alignment = b_r_alignment |
||
182 | ws['D3'] = 'Period:' |
||
183 | ws['E3'].border = b_border |
||
184 | ws['E3'].alignment = b_c_alignment |
||
185 | ws['E3'].font = name_font |
||
186 | ws['E3'] = period_type |
||
187 | |||
188 | ws['F3'].font = name_font |
||
189 | ws['F3'].alignment = b_r_alignment |
||
190 | ws['F3'] = 'Date:' |
||
191 | ws['G3'].border = b_border |
||
192 | ws['G3'].alignment = b_c_alignment |
||
193 | ws['G3'].font = name_font |
||
194 | ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local |
||
195 | ws.merge_cells("G3:H3") |
||
196 | |||
197 | if "reporting_period" not in report.keys() or \ |
||
198 | "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: |
||
199 | filename = str(uuid.uuid4()) + '.xlsx' |
||
200 | wb.save(filename) |
||
201 | |||
202 | return filename |
||
203 | ################################################# |
||
204 | # First: 统计分析 |
||
205 | # 6: title |
||
206 | # 7: table title |
||
207 | # 8~ca_len table_data |
||
208 | ################################################# |
||
209 | reporting_period_data = report['reporting_period'] |
||
210 | |||
211 | has_energy_data_flag = True |
||
212 | |||
213 | if "names" not in reporting_period_data.keys() or \ |
||
214 | reporting_period_data['names'] is None or \ |
||
215 | len(reporting_period_data['names']) == 0: |
||
216 | has_energy_data_flag = False |
||
217 | |||
218 | filename = str(uuid.uuid4()) + '.xlsx' |
||
219 | wb.save(filename) |
||
220 | |||
221 | return filename |
||
222 | |||
223 | View Code Duplication | if has_energy_data_flag: |
|
224 | ws['B6'].font = title_font |
||
225 | ws['B6'] = name + ' 统计分析' |
||
226 | |||
227 | category = reporting_period_data['names'] |
||
228 | |||
229 | # table_title |
||
230 | ws['B7'].fill = table_fill |
||
231 | ws['B7'].font = title_font |
||
232 | ws['B7'].alignment = c_c_alignment |
||
233 | ws['B7'] = '报告期' |
||
234 | ws['B7'].border = f_border |
||
235 | |||
236 | ws['C7'].font = title_font |
||
237 | ws['C7'].alignment = c_c_alignment |
||
238 | ws['C7'] = '平均负荷' |
||
239 | ws['C7'].border = f_border |
||
240 | |||
241 | ws['D7'].font = title_font |
||
242 | ws['D7'].alignment = c_c_alignment |
||
243 | ws['D7'] = '最大负荷' |
||
244 | ws['D7'].border = f_border |
||
245 | |||
246 | ws['E7'].font = title_font |
||
247 | ws['E7'].alignment = c_c_alignment |
||
248 | ws['E7'] = '负荷系数' |
||
249 | ws['E7'].border = f_border |
||
250 | |||
251 | # table_data |
||
252 | |||
253 | for i, value in enumerate(category): |
||
254 | row = i * 2 + 8 |
||
255 | ws['B' + str(row)].font = name_font |
||
256 | ws['B' + str(row)].alignment = c_c_alignment |
||
257 | ws['B' + str(row)] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + "/H )" |
||
258 | ws['B' + str(row)].border = f_border |
||
259 | |||
260 | ws['B' + str(row + 1)].font = name_font |
||
261 | ws['B' + str(row + 1)].alignment = c_c_alignment |
||
262 | ws['B' + str(row + 1)] = "环比" |
||
263 | ws['B' + str(row + 1)].border = f_border |
||
264 | |||
265 | ws['C' + str(row)].font = name_font |
||
266 | ws['C' + str(row)].alignment = c_c_alignment |
||
267 | ws['C' + str(row)] = round(reporting_period_data['averages'][i], 2) \ |
||
268 | if reporting_period_data['averages'][i] is not None else '' |
||
269 | ws['C' + str(row)].border = f_border |
||
270 | ws['C' + str(row)].number_format = '0.00' |
||
271 | |||
272 | ws['C' + str(row + 1)].font = name_font |
||
273 | ws['C' + str(row + 1)].alignment = c_c_alignment |
||
274 | ws['C' + str(row + 1)] = str(round(reporting_period_data['averages_increment_rate'][i] * 100, 2)) + "%" \ |
||
275 | if reporting_period_data['averages_increment_rate'][i] is not None else '0.00%' |
||
276 | ws['C' + str(row + 1)].border = f_border |
||
277 | |||
278 | ws['D' + str(row)].font = name_font |
||
279 | ws['D' + str(row)].alignment = c_c_alignment |
||
280 | ws['D' + str(row)] = round(reporting_period_data['maximums'][i], 2) \ |
||
281 | if reporting_period_data['maximums'][i] is not None else '' |
||
282 | ws['D' + str(row)].border = f_border |
||
283 | ws['D' + str(row)].number_format = '0.00' |
||
284 | |||
285 | ws['D' + str(row + 1)].font = name_font |
||
286 | ws['D' + str(row + 1)].alignment = c_c_alignment |
||
287 | ws['D' + str(row + 1)] = str(round(reporting_period_data['maximums_increment_rate'][i] * 100, 2)) + "%" \ |
||
288 | if reporting_period_data['maximums_increment_rate'][i] is not None else '0.00%' |
||
289 | ws['D' + str(row + 1)].border = f_border |
||
290 | |||
291 | ws['E' + str(row)].font = name_font |
||
292 | ws['E' + str(row)].alignment = c_c_alignment |
||
293 | ws['E' + str(row)] = round(reporting_period_data['factors'][i], 2) \ |
||
294 | if reporting_period_data['factors'][i] is not None else '' |
||
295 | ws['E' + str(row)].border = f_border |
||
296 | ws['E' + str(row)].number_format = '0.00' |
||
297 | |||
298 | ws['E' + str(row + 1)].font = name_font |
||
299 | ws['E' + str(row + 1)].alignment = c_c_alignment |
||
300 | ws['E' + str(row + 1)] = str(round(reporting_period_data['factors_increment_rate'][i] * 100, 2)) + "%" \ |
||
301 | if reporting_period_data['factors_increment_rate'][i] is not None else '0.00%' |
||
302 | ws['E' + str(row + 1)].border = f_border |
||
303 | |||
304 | ################################################# |
||
305 | # Second: 报告期单位面积消耗 |
||
306 | # 9 + ca_len * 2: title |
||
307 | # 10 + ca_len * 2: table title |
||
308 | # per_unit_area_start_row_number + 2 ~ per_unit_area_start_row_number + 2 + ca_len : table_data |
||
309 | ################################################# |
||
310 | |||
311 | View Code Duplication | if has_energy_data_flag: |
|
312 | names = reporting_period_data['names'] |
||
313 | ca_len = len(names) |
||
314 | per_unit_area_start_row_number = 9 + ca_len * 2 |
||
315 | |||
316 | ws['B' + str(per_unit_area_start_row_number)].font = title_font |
||
317 | ws['B' + str(per_unit_area_start_row_number)] = name + ' 单位面积值' + str(report['shopfloor']['area']) + 'M²' |
||
318 | |||
319 | category = reporting_period_data['names'] |
||
320 | |||
321 | # table_title |
||
322 | ws['B' + str(per_unit_area_start_row_number + 1)].fill = table_fill |
||
323 | ws['B' + str(per_unit_area_start_row_number + 1)].font = title_font |
||
324 | ws['B' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment |
||
325 | ws['B' + str(per_unit_area_start_row_number + 1)] = '报告期' |
||
326 | ws['B' + str(per_unit_area_start_row_number + 1)].border = f_border |
||
327 | |||
328 | ws['C' + str(per_unit_area_start_row_number + 1)].font = title_font |
||
329 | ws['C' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment |
||
330 | ws['C' + str(per_unit_area_start_row_number + 1)] = '平均负荷' |
||
331 | ws['C' + str(per_unit_area_start_row_number + 1)].border = f_border |
||
332 | |||
333 | ws['D' + str(per_unit_area_start_row_number + 1)].font = title_font |
||
334 | ws['D' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment |
||
335 | ws['D' + str(per_unit_area_start_row_number + 1)] = '最大负荷' |
||
336 | ws['D' + str(per_unit_area_start_row_number + 1)].border = f_border |
||
337 | |||
338 | # table_data |
||
339 | |||
340 | for i, value in enumerate(category): |
||
341 | row_data = per_unit_area_start_row_number + 2 + i |
||
342 | ws['B' + str(row_data)].font = name_font |
||
343 | ws['B' + str(row_data)].alignment = c_c_alignment |
||
344 | ws['B' + str(row_data)] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][ |
||
345 | i] + "/H/M²)" |
||
346 | ws['B' + str(row_data)].border = f_border |
||
347 | |||
348 | ws['C' + str(row_data)].font = name_font |
||
349 | ws['C' + str(row_data)].alignment = c_c_alignment |
||
350 | if reporting_period_data['averages_per_unit_area'][i] \ |
||
351 | or reporting_period_data['averages_per_unit_area'][i] == 0: |
||
352 | ws['C' + str(row_data)] = round(reporting_period_data['averages_per_unit_area'][i], 2) |
||
353 | ws['C' + str(row_data)].border = f_border |
||
354 | ws['C' + str(row_data)].number_format = '0.00' |
||
355 | |||
356 | ws['D' + str(row_data)].font = name_font |
||
357 | ws['D' + str(row_data)].alignment = c_c_alignment |
||
358 | if reporting_period_data['maximums_per_unit_area'][i] \ |
||
359 | or reporting_period_data['maximums_per_unit_area'][i] == 0: |
||
360 | ws['D' + str(row_data)] = round(reporting_period_data['maximums_per_unit_area'][i], 2) |
||
361 | ws['D' + str(row_data)].border = f_border |
||
362 | ws['D' + str(row_data)].number_format = '0.00' |
||
363 | |||
364 | ######################################################## |
||
365 | # Third: 详细数据 |
||
366 | # analysis_end_row_number~ analysis_end_row_number + 6*cal_len: line |
||
367 | # detailed_start_row_number: table title |
||
368 | # detailed_start_row_number + 1~: table_data |
||
369 | ######################################################## |
||
370 | has_timestamps_flag = True |
||
371 | if "timestamps" not in reporting_period_data.keys() or \ |
||
372 | reporting_period_data['timestamps'] is None or \ |
||
373 | len(reporting_period_data['timestamps']) == 0: |
||
374 | has_timestamps_flag = False |
||
375 | |||
376 | timestamps = reporting_period_data['timestamps'][0] |
||
377 | names = reporting_period_data['names'] |
||
378 | ca_len = len(names) |
||
379 | time_len = len(timestamps) |
||
380 | parameters_names_len = len(report['parameters']['names']) |
||
381 | parameters_parameters_datas_len = 0 |
||
382 | analysis_end_row_number = 12 + 3 * ca_len |
||
383 | current_row_number = analysis_end_row_number |
||
384 | if has_timestamps_flag: |
||
385 | for i in range(0, parameters_names_len): |
||
386 | if len(report['parameters']['timestamps'][i]) == 0: |
||
387 | continue |
||
388 | parameters_parameters_datas_len += 1 |
||
389 | detail_data_table_start_row_number = current_row_number + (ca_len + parameters_parameters_datas_len) * 6 + 2 |
||
390 | |||
391 | ws['B' + str(current_row_number)].font = title_font |
||
392 | ws['B' + str(current_row_number)] = name + ' 详细数据' |
||
393 | # table_title |
||
394 | ws['B' + str(detail_data_table_start_row_number)].fill = table_fill |
||
395 | ws['B' + str(detail_data_table_start_row_number)].font = name_font |
||
396 | ws['B' + str(detail_data_table_start_row_number)].alignment = c_c_alignment |
||
397 | ws['B' + str(detail_data_table_start_row_number)] = "日期时间" |
||
398 | ws['B' + str(detail_data_table_start_row_number)].border = f_border |
||
399 | |||
400 | for i in range(0, ca_len): |
||
401 | col_average = chr(ord('C') + 2 * i) |
||
402 | col_maximum = chr(ord('D') + 2 * i) |
||
403 | |||
404 | ws[col_average + str(detail_data_table_start_row_number)].font = name_font |
||
405 | ws[col_average + str(detail_data_table_start_row_number)].alignment = c_c_alignment |
||
406 | ws[col_average + str(detail_data_table_start_row_number)] = \ |
||
407 | names[i] + " 平均负荷(" + reporting_period_data['units'][i] + "/H)" |
||
408 | ws[col_average + str(detail_data_table_start_row_number)].border = f_border |
||
409 | |||
410 | ws[col_maximum + str(detail_data_table_start_row_number)].font = name_font |
||
411 | ws[col_maximum + str(detail_data_table_start_row_number)].alignment = c_c_alignment |
||
412 | ws[col_maximum + str(detail_data_table_start_row_number)] = \ |
||
413 | names[i] + " 最大负荷(" + reporting_period_data['units'][i] + "/H)" |
||
414 | ws[col_maximum + str(detail_data_table_start_row_number)].border = f_border |
||
415 | # table_date |
||
416 | for i in range(0, time_len): |
||
417 | rows = i + detail_data_table_start_row_number + 1 |
||
418 | |||
419 | ws['B' + str(rows)].font = name_font |
||
420 | ws['B' + str(rows)].alignment = c_c_alignment |
||
421 | ws['B' + str(rows)] = timestamps[i] |
||
422 | ws['B' + str(rows)].border = f_border |
||
423 | |||
424 | for index in range(0, ca_len): |
||
425 | col_average = chr(ord('C') + 2 * index) |
||
426 | col_maximum = chr(ord('D') + 2 * index) |
||
427 | |||
428 | ws[col_average + str(rows)].font = name_font |
||
429 | ws[col_average + str(rows)].alignment = c_c_alignment |
||
430 | ws[col_average + str(rows)] = reporting_period_data['sub_averages'][index][i] \ |
||
431 | if reporting_period_data['sub_maximums'][index] is not None else '' |
||
432 | ws[col_average + str(rows)].number_format = '0.00' |
||
433 | ws[col_average + str(rows)].border = f_border |
||
434 | |||
435 | ws[col_maximum + str(rows)].font = name_font |
||
436 | ws[col_maximum + str(rows)].alignment = c_c_alignment |
||
437 | ws[col_maximum + str(rows)] = reporting_period_data['sub_maximums'][index][i] \ |
||
438 | if reporting_period_data['sub_maximums'][index] is not None else '' |
||
439 | ws[col_maximum + str(rows)].number_format = '0.00' |
||
440 | ws[col_maximum + str(rows)].border = f_border |
||
441 | |||
442 | ######################################################## |
||
443 | # third: LineChart |
||
444 | # LineChart requires data from the detailed data table in the Excel file |
||
445 | # so print the detailed data table first and then print LineChart |
||
446 | ######################################################## |
||
447 | for i in range(0, ca_len): |
||
448 | line = LineChart() |
||
449 | line.title = "报告期 最大负荷 - " + names[i] + "(" + reporting_period_data['units'][i] + ")" |
||
450 | line.style = 10 |
||
451 | line.x_axis.majorTickMark = 'in' |
||
452 | line.y_axis.majorTickMark = 'in' |
||
453 | line.smooth = True |
||
454 | line.x_axis.crosses = 'min' |
||
455 | line.height = 8.25 |
||
456 | line.width = 24 |
||
457 | line.dLbls = DataLabelList() |
||
458 | line.dLbls.dLblPos = 't' |
||
459 | line.dLbls.showVal = True |
||
460 | times = Reference(ws, min_col=2, min_row=detail_data_table_start_row_number + 1, |
||
461 | max_row=detail_data_table_start_row_number + 1 + time_len) |
||
462 | line_data = Reference(ws, min_col=2 + 2 * (i + 1), min_row=detail_data_table_start_row_number, |
||
463 | max_row=detail_data_table_start_row_number + time_len) |
||
464 | line.add_data(line_data, titles_from_data=True) |
||
465 | line.set_categories(times) |
||
466 | ser = line.series[0] |
||
467 | ser.marker.symbol = "diamond" |
||
468 | ser.marker.size = 5 |
||
469 | chart_col = 'B' |
||
470 | chart_cell = str(analysis_end_row_number + 6 * i) |
||
471 | ws.add_chart(line, chart_col + chart_cell) |
||
472 | |||
473 | ########################################## |
||
474 | has_parameters_names_and_timestamps_and_values_data = True |
||
475 | |||
476 | ca_len = len(report['reporting_period']['names']) |
||
477 | current_sheet_parameters_row_number = current_row_number + ca_len * 6 |
||
478 | if 'parameters' not in report.keys() or \ |
||
479 | report['parameters'] is None or \ |
||
480 | 'names' not in report['parameters'].keys() or \ |
||
481 | report['parameters']['names'] is None or \ |
||
482 | len(report['parameters']['names']) == 0 or \ |
||
483 | 'timestamps' not in report['parameters'].keys() or \ |
||
484 | report['parameters']['timestamps'] is None or \ |
||
485 | len(report['parameters']['timestamps']) == 0 or \ |
||
486 | 'values' not in report['parameters'].keys() or \ |
||
487 | report['parameters']['values'] is None or \ |
||
488 | len(report['parameters']['values']) == 0 or \ |
||
489 | timestamps_data_all_equal_0(report['parameters']['timestamps']): |
||
490 | has_parameters_names_and_timestamps_and_values_data = False |
||
491 | if has_parameters_names_and_timestamps_and_values_data: |
||
492 | |||
493 | ############################### |
||
494 | # new worksheet |
||
495 | ############################### |
||
496 | |||
497 | parameters_data = report['parameters'] |
||
498 | |||
499 | parameters_names_len = len(parameters_data['names']) |
||
500 | |||
501 | parameters_ws = wb.create_sheet('相关参数') |
||
502 | |||
503 | parameters_timestamps_data_max_len = \ |
||
504 | get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps'])) |
||
505 | |||
506 | # Row height |
||
507 | parameters_ws.row_dimensions[1].height = 102 |
||
508 | for i in range(2, 7 + 1): |
||
509 | parameters_ws.row_dimensions[i].height = 42 |
||
510 | |||
511 | for i in range(8, parameters_timestamps_data_max_len + 10): |
||
512 | parameters_ws.row_dimensions[i].height = 60 |
||
513 | |||
514 | # Col width |
||
515 | parameters_ws.column_dimensions['A'].width = 1.5 |
||
516 | |||
517 | parameters_ws.column_dimensions['B'].width = 25.0 |
||
518 | |||
519 | for i in range(3, 12 + parameters_names_len * 3): |
||
520 | parameters_ws.column_dimensions[format_cell.get_column_letter(i)].width = 15.0 |
||
521 | |||
522 | # Img |
||
523 | img = Image("excelexporters/myems.png") |
||
524 | img.width = img.width * 0.85 |
||
525 | img.height = img.height * 0.85 |
||
526 | # img = Image("myems.png") |
||
527 | parameters_ws.add_image(img, 'B1') |
||
528 | |||
529 | # Title |
||
530 | parameters_ws.row_dimensions[3].height = 60 |
||
531 | |||
532 | parameters_ws['B3'].font = name_font |
||
533 | parameters_ws['B3'].alignment = b_r_alignment |
||
534 | parameters_ws['B3'] = 'Name:' |
||
535 | parameters_ws['C3'].border = b_border |
||
536 | parameters_ws['C3'].alignment = b_c_alignment |
||
537 | parameters_ws['C3'].font = name_font |
||
538 | parameters_ws['C3'] = name |
||
539 | |||
540 | parameters_ws['D3'].font = name_font |
||
541 | parameters_ws['D3'].alignment = b_r_alignment |
||
542 | parameters_ws['D3'] = 'Period:' |
||
543 | parameters_ws['E3'].border = b_border |
||
544 | parameters_ws['E3'].alignment = b_c_alignment |
||
545 | parameters_ws['E3'].font = name_font |
||
546 | parameters_ws['E3'] = period_type |
||
547 | |||
548 | parameters_ws['F3'].font = name_font |
||
549 | parameters_ws['F3'].alignment = b_r_alignment |
||
550 | parameters_ws['F3'] = 'Date:' |
||
551 | parameters_ws['G3'].border = b_border |
||
552 | parameters_ws['G3'].alignment = b_c_alignment |
||
553 | parameters_ws['G3'].font = name_font |
||
554 | parameters_ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local |
||
555 | parameters_ws.merge_cells("G3:H3") |
||
556 | |||
557 | parameters_ws_current_row_number = 6 |
||
558 | |||
559 | parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font |
||
560 | parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' 相关参数' |
||
561 | |||
562 | parameters_ws_current_row_number += 1 |
||
563 | |||
564 | parameters_table_start_row_number = parameters_ws_current_row_number |
||
565 | |||
566 | parameters_ws.row_dimensions[parameters_ws_current_row_number].height = 80 |
||
567 | |||
568 | parameters_ws_current_row_number += 1 |
||
569 | |||
570 | table_current_col_number = 'B' |
||
571 | |||
572 | for i in range(0, parameters_names_len): |
||
573 | |||
574 | if len(parameters_data['timestamps'][i]) == 0: |
||
575 | continue |
||
576 | |||
577 | parameters_ws[table_current_col_number + str(parameters_ws_current_row_number - 1)].fill = table_fill |
||
578 | parameters_ws[table_current_col_number + str(parameters_ws_current_row_number - 1)].border = f_border |
||
579 | |||
580 | col = decimal_to_column(column_to_decimal(table_current_col_number) + 1) |
||
581 | |||
582 | parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill |
||
583 | parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border |
||
584 | parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font |
||
585 | parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment |
||
586 | parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data['names'][i] |
||
587 | |||
588 | table_current_row_number = parameters_ws_current_row_number |
||
589 | |||
590 | for j, value in enumerate(list(parameters_data['timestamps'][i])): |
||
591 | col = table_current_col_number |
||
592 | |||
593 | parameters_ws[col + str(table_current_row_number)].border = f_border |
||
594 | parameters_ws[col + str(table_current_row_number)].font = title_font |
||
595 | parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment |
||
596 | parameters_ws[col + str(table_current_row_number)] = value |
||
597 | |||
598 | col = decimal_to_column(column_to_decimal(col) + 1) |
||
599 | |||
600 | parameters_ws[col + str(table_current_row_number)].border = f_border |
||
601 | parameters_ws[col + str(table_current_row_number)].font = title_font |
||
602 | parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment |
||
603 | parameters_ws[col + str(table_current_row_number)] = round(parameters_data['values'][i][j], 2) |
||
604 | |||
605 | table_current_row_number += 1 |
||
606 | |||
607 | table_current_col_number = decimal_to_column(column_to_decimal(table_current_col_number) + 3) |
||
608 | |||
609 | ######################################################## |
||
610 | # parameters chart and parameters table |
||
611 | ######################################################## |
||
612 | |||
613 | ws['B' + str(current_sheet_parameters_row_number)].font = title_font |
||
614 | ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数' |
||
615 | |||
616 | current_sheet_parameters_row_number += 1 |
||
617 | |||
618 | chart_start_row_number = current_sheet_parameters_row_number |
||
619 | |||
620 | col_index = 0 |
||
621 | |||
622 | for i in range(0, parameters_names_len): |
||
623 | |||
624 | if len(parameters_data['timestamps'][i]) == 0: |
||
625 | continue |
||
626 | |||
627 | line = LineChart() |
||
628 | data_col = 3 + col_index * 3 |
||
629 | labels_col = 2 + col_index * 3 |
||
630 | col_index += 1 |
||
631 | line.title = '相关参数 - ' + \ |
||
632 | parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value |
||
633 | labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1, |
||
634 | max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) |
||
635 | line_data = Reference(parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number, |
||
636 | max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) |
||
637 | line.add_data(line_data, titles_from_data=True) |
||
638 | line.set_categories(labels) |
||
639 | line_data = line.series[0] |
||
640 | line_data.marker.symbol = "circle" |
||
641 | line_data.smooth = True |
||
642 | line.x_axis.crosses = 'min' |
||
643 | line.height = 8.25 |
||
644 | line.width = 24 |
||
645 | line.dLbls = DataLabelList() |
||
646 | line.dLbls.dLblPos = 't' |
||
647 | line.dLbls.showVal = False |
||
648 | line.dLbls.showPercent = False |
||
649 | chart_col = 'B' |
||
650 | chart_cell = chart_col + str(chart_start_row_number) |
||
651 | chart_start_row_number += 6 |
||
652 | ws.add_chart(line, chart_cell) |
||
653 | |||
654 | current_sheet_parameters_row_number = chart_start_row_number |
||
655 | |||
656 | current_sheet_parameters_row_number += 1 |
||
657 | |||
658 | filename = str(uuid.uuid4()) + '.xlsx' |
||
659 | wb.save(filename) |
||
660 | |||
661 | return filename |
||
662 |