1 | import base64 |
||
2 | import uuid |
||
3 | import os |
||
4 | import re |
||
5 | from openpyxl.chart import LineChart, Reference |
||
6 | from openpyxl.styles import PatternFill, Border, Side, Alignment, Font |
||
7 | from openpyxl.drawing.image import Image |
||
8 | from openpyxl import Workbook |
||
9 | from openpyxl.chart.label import DataLabelList |
||
10 | import openpyxl.utils.cell as format_cell |
||
11 | |||
12 | |||
13 | ######################################################################################################################## |
||
14 | # PROCEDURES |
||
15 | # Step 1: Validate the report data |
||
16 | # Step 2: Generate excel file |
||
17 | # Step 3: Encode the excel file bytes to Base64 |
||
18 | ######################################################################################################################## |
||
19 | |||
20 | |||
21 | View Code Duplication | def export(report, |
|
22 | name, |
||
23 | reporting_start_datetime_local, |
||
24 | reporting_end_datetime_local, |
||
25 | period_type): |
||
26 | #################################################################################################################### |
||
27 | # Step 1: Validate the report data |
||
28 | #################################################################################################################### |
||
29 | if report is None: |
||
30 | return None |
||
31 | |||
32 | #################################################################################################################### |
||
33 | # Step 2: Generate excel file from the report data |
||
34 | #################################################################################################################### |
||
35 | filename = generate_excel(report, |
||
36 | name, |
||
37 | reporting_start_datetime_local, |
||
38 | reporting_end_datetime_local, |
||
39 | period_type) |
||
40 | #################################################################################################################### |
||
41 | # Step 3: Encode the excel file to Base64 |
||
42 | #################################################################################################################### |
||
43 | binary_file_data = b'' |
||
44 | try: |
||
45 | with open(filename, 'rb') as binary_file: |
||
46 | binary_file_data = binary_file.read() |
||
47 | except IOError as ex: |
||
48 | pass |
||
49 | |||
50 | # Base64 encode the bytes |
||
51 | base64_encoded_data = base64.b64encode(binary_file_data) |
||
52 | # get the Base64 encoded data using human-readable characters. |
||
53 | base64_message = base64_encoded_data.decode('utf-8') |
||
54 | # delete the file from server |
||
55 | try: |
||
56 | os.remove(filename) |
||
57 | except NotImplementedError as ex: |
||
58 | pass |
||
59 | return base64_message |
||
60 | |||
61 | |||
62 | def generate_excel(report, |
||
63 | name, |
||
64 | reporting_start_datetime_local, |
||
65 | reporting_end_datetime_local, |
||
66 | period_type): |
||
67 | wb = Workbook() |
||
68 | ws = wb.active |
||
69 | ws.title = "EquipmentEfficiency" |
||
70 | |||
71 | # Row height |
||
72 | ws.row_dimensions[1].height = 102 |
||
73 | for i in range(2, 2000 + 1): |
||
74 | ws.row_dimensions[i].height = 42 |
||
75 | |||
76 | # Col width |
||
77 | ws.column_dimensions['A'].width = 1.5 |
||
78 | |||
79 | ws.column_dimensions['B'].width = 25.0 |
||
80 | |||
81 | for i in range(ord('C'), ord('L')): |
||
82 | ws.column_dimensions[chr(i)].width = 15.0 |
||
83 | |||
84 | # Font |
||
85 | name_font = Font(name='Arial', size=15, bold=True) |
||
86 | title_font = Font(name='Arial', size=15, bold=True) |
||
87 | |||
88 | table_fill = PatternFill(fill_type='solid', fgColor='1F497D') |
||
89 | f_border = Border(left=Side(border_style='medium', color='00000000'), |
||
90 | right=Side(border_style='medium', color='00000000'), |
||
91 | bottom=Side(border_style='medium', color='00000000'), |
||
92 | top=Side(border_style='medium', color='00000000') |
||
93 | ) |
||
94 | b_border = Border(bottom=Side(border_style='medium', color='00000000'),) |
||
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 | # Img |
||
115 | img = Image("excelexporters/myems.png") |
||
116 | ws.add_image(img, 'A1') |
||
117 | |||
118 | # Title |
||
119 | ws['B3'].alignment = b_r_alignment |
||
120 | ws['B3'] = 'Name:' |
||
121 | ws['C3'].border = b_border |
||
122 | ws['C3'].alignment = b_c_alignment |
||
123 | ws['C3'] = name |
||
124 | |||
125 | ws['D3'].alignment = b_r_alignment |
||
126 | ws['D3'] = 'Period:' |
||
127 | ws['E3'].border = b_border |
||
128 | ws['E3'].alignment = b_c_alignment |
||
129 | ws['E3'] = period_type |
||
130 | |||
131 | ws['B4'].alignment = b_r_alignment |
||
132 | ws['B4'] = 'Reporting Start Datetime:' |
||
133 | ws['C4'].border = b_border |
||
134 | ws['C4'].alignment = b_c_alignment |
||
135 | ws['C4'] = reporting_start_datetime_local |
||
136 | |||
137 | ws['D4'].alignment = b_r_alignment |
||
138 | ws['D4'] = 'Reporting End Datetime:' |
||
139 | ws['E4'].border = b_border |
||
140 | ws['E4'].alignment = b_c_alignment |
||
141 | ws['E4'] = reporting_end_datetime_local |
||
142 | |||
143 | if "reporting_period_efficiency" not in report.keys() or \ |
||
144 | "names" not in report['reporting_period_efficiency'].keys() or len( |
||
145 | report['reporting_period_efficiency']['names']) == 0: |
||
146 | filename = str(uuid.uuid4()) + '.xlsx' |
||
147 | wb.save(filename) |
||
148 | |||
149 | return filename |
||
150 | |||
151 | #################################################################################################################### |
||
152 | |||
153 | current_row_number = 6 |
||
154 | |||
155 | reporting_period_data = report['reporting_period_efficiency'] |
||
156 | |||
157 | has_names_data_flag = True |
||
158 | |||
159 | if "names" not in reporting_period_data.keys() or \ |
||
160 | reporting_period_data['names'] is None or \ |
||
161 | len(reporting_period_data['names']) == 0: |
||
162 | has_names_data_flag = False |
||
163 | |||
164 | View Code Duplication | if has_names_data_flag: |
|
0 ignored issues
–
show
Duplication
introduced
by
Loading history...
|
|||
165 | ws['B' + str(current_row_number)].font = title_font |
||
166 | ws['B' + str(current_row_number)] = name + ' ' + 'Reporting Period Cumulative Efficiency' |
||
167 | |||
168 | current_row_number += 1 |
||
169 | |||
170 | category = reporting_period_data['names'] |
||
171 | ca_len = len(category) |
||
172 | |||
173 | ws.row_dimensions[current_row_number].height = 80 |
||
174 | ws['B' + str(current_row_number)].fill = table_fill |
||
175 | ws['B' + str(current_row_number)].border = f_border |
||
176 | |||
177 | col = 'C' |
||
178 | |||
179 | for i in range(0, ca_len): |
||
180 | ws[col + str(current_row_number)].fill = table_fill |
||
181 | ws[col + str(current_row_number)].font = name_font |
||
182 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
183 | ws[col + str(current_row_number)].border = f_border |
||
184 | ws[col + str(current_row_number)] = \ |
||
185 | reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" |
||
186 | |||
187 | col = chr(ord(col) + 1) |
||
188 | |||
189 | current_row_number += 1 |
||
190 | |||
191 | ws['B' + str(current_row_number)].font = title_font |
||
192 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
193 | ws['B' + str(current_row_number)].border = f_border |
||
194 | ws['B' + str(current_row_number)] = 'Cumulative Efficiency' |
||
195 | |||
196 | col = 'C' |
||
197 | |||
198 | for i in range(0, ca_len): |
||
199 | ws[col + str(current_row_number)].font = name_font |
||
200 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
201 | ws[col + str(current_row_number)].border = f_border |
||
202 | ws[col + str(current_row_number)] = round(reporting_period_data['cumulations'][i], 2) |
||
203 | |||
204 | col = chr(ord(col) + 1) |
||
205 | |||
206 | current_row_number += 1 |
||
207 | |||
208 | ws['B' + str(current_row_number)].font = title_font |
||
209 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
210 | ws['B' + str(current_row_number)].border = f_border |
||
211 | ws['B' + str(current_row_number)] = 'Increment Rate' |
||
212 | |||
213 | col = 'C' |
||
214 | |||
215 | for i in range(0, ca_len): |
||
216 | ws[col + str(current_row_number)].font = name_font |
||
217 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
218 | ws[col + str(current_row_number)].border = f_border |
||
219 | ws[col + str(current_row_number)] = str( |
||
220 | round(reporting_period_data['increment_rates'][i] * 100, 2)) + '%' \ |
||
221 | if reporting_period_data['increment_rates'][i] is not None else '-' |
||
222 | |||
223 | col = chr(ord(col) + 1) |
||
224 | |||
225 | current_row_number += 2 |
||
226 | |||
227 | #################################################################################################################### |
||
228 | |||
229 | has_parameters_names_and_timestamps_and_values_data = True |
||
230 | current_sheet_parameters_row_number = current_row_number |
||
231 | |||
232 | if 'parameters' not in report.keys() or \ |
||
233 | report['parameters'] is None or \ |
||
234 | 'names' not in report['parameters'].keys() or \ |
||
235 | report['parameters']['names'] is None or \ |
||
236 | len(report['parameters']['names']) == 0 or \ |
||
237 | 'timestamps' not in report['parameters'].keys() or \ |
||
238 | report['parameters']['timestamps'] is None or \ |
||
239 | len(report['parameters']['timestamps']) == 0 or \ |
||
240 | 'values' not in report['parameters'].keys() or \ |
||
241 | report['parameters']['values'] is None or \ |
||
242 | len(report['parameters']['values']) == 0 or \ |
||
243 | timestamps_data_all_equal_0(report['parameters']['timestamps']): |
||
244 | |||
245 | has_parameters_names_and_timestamps_and_values_data = False |
||
246 | |||
247 | #################################################################################################################### |
||
248 | |||
249 | has_values_data = True |
||
250 | has_timestamps_data = True |
||
251 | |||
252 | if 'values' not in reporting_period_data.keys() or \ |
||
253 | reporting_period_data['values'] is None or \ |
||
254 | len(reporting_period_data['values']) == 0: |
||
255 | has_values_data = False |
||
256 | |||
257 | if 'timestamps' not in reporting_period_data.keys() or \ |
||
258 | reporting_period_data['timestamps'] is None or \ |
||
259 | len(reporting_period_data['timestamps']) == 0 or \ |
||
260 | len(reporting_period_data['timestamps'][0]) == 0: |
||
261 | has_timestamps_data = False |
||
262 | |||
263 | if has_values_data and has_timestamps_data: |
||
264 | ca_len = len(reporting_period_data['names']) |
||
265 | time = reporting_period_data['timestamps'][0] |
||
266 | |||
267 | ws['B' + str(current_row_number)].font = title_font |
||
268 | ws['B' + str(current_row_number)] = name + ' ' + 'Reporting Period Cumulative Efficiency' |
||
269 | |||
270 | current_row_number += 1 |
||
271 | |||
272 | chart_start_row_number = current_row_number |
||
273 | |||
274 | current_row_number += ca_len * 6 + 1 |
||
275 | |||
276 | if has_parameters_names_and_timestamps_and_values_data: |
||
277 | current_sheet_parameters_row_number = current_row_number |
||
278 | real_timestamps_len = timestamps_data_not_equal_0(report['parameters']['timestamps']) |
||
279 | current_row_number += 6*real_timestamps_len + 2 |
||
280 | |||
281 | ws['B' + str(current_row_number)].font = title_font |
||
282 | ws['B' + str(current_row_number)] = name + ' ' + 'Detailed Data' |
||
283 | |||
284 | current_row_number += 1 |
||
285 | |||
286 | table_start_row_number = current_row_number |
||
287 | |||
288 | ws.row_dimensions[current_row_number].height = 85 |
||
289 | ws['B' + str(current_row_number)].fill = table_fill |
||
290 | ws['B' + str(current_row_number)].font = title_font |
||
291 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
292 | ws['B' + str(current_row_number)].border = f_border |
||
293 | ws['B' + str(current_row_number)] = 'Datetime' |
||
294 | |||
295 | col = 'C' |
||
296 | |||
297 | for i in range(0, ca_len): |
||
298 | ws[col + str(current_row_number)].fill = table_fill |
||
299 | ws[col + str(current_row_number)].font = title_font |
||
300 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
301 | ws[col + str(current_row_number)].border = f_border |
||
302 | ws[col + str(current_row_number)] = \ |
||
303 | reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" |
||
304 | col = chr(ord(col) + 1) |
||
305 | |||
306 | current_row_number += 1 |
||
307 | |||
308 | for i in range(0, len(time)): |
||
309 | ws['B' + str(current_row_number)].font = title_font |
||
310 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
311 | ws['B' + str(current_row_number)].border = f_border |
||
312 | ws['B' + str(current_row_number)] = time[i] |
||
313 | |||
314 | col = 'C' |
||
315 | for j in range(0, ca_len): |
||
316 | ws[col + str(current_row_number)].font = title_font |
||
317 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
318 | ws[col + str(current_row_number)].border = f_border |
||
319 | ws[col + str(current_row_number)] = round(reporting_period_data['values'][j][i], 2) \ |
||
320 | if reporting_period_data['values'][j][i] is not None else 0.00 |
||
321 | col = chr(ord(col) + 1) |
||
322 | |||
323 | current_row_number += 1 |
||
324 | |||
325 | table_end_row_number = current_row_number - 1 |
||
326 | |||
327 | ws['B' + str(current_row_number)].font = title_font |
||
328 | ws['B' + str(current_row_number)].alignment = c_c_alignment |
||
329 | ws['B' + str(current_row_number)].border = f_border |
||
330 | ws['B' + str(current_row_number)] = 'Subtotal' |
||
331 | |||
332 | col = 'C' |
||
333 | |||
334 | for i in range(0, ca_len): |
||
335 | ws[col + str(current_row_number)].font = title_font |
||
336 | ws[col + str(current_row_number)].alignment = c_c_alignment |
||
337 | ws[col + str(current_row_number)].border = f_border |
||
338 | ws[col + str(current_row_number)] = round(reporting_period_data['cumulations'][i], 2) |
||
339 | col = chr(ord(col) + 1) |
||
340 | |||
341 | current_row_number += 2 |
||
342 | |||
343 | format_time_width_number = 1.0 |
||
344 | min_len_number = 1.0 |
||
345 | min_width_number = 11.0 |
||
346 | |||
347 | if period_type == 'hourly': |
||
348 | format_time_width_number = 4.0 |
||
349 | min_len_number = 2 |
||
350 | min_width_number = 12.0 |
||
351 | elif period_type == 'daily': |
||
352 | format_time_width_number = 2.5 |
||
353 | min_len_number = 4 |
||
354 | min_width_number = 14.0 |
||
355 | elif period_type == 'monthly': |
||
356 | format_time_width_number = 2.1 |
||
357 | min_len_number = 4 |
||
358 | min_width_number = 12.4 |
||
359 | elif period_type == 'yearly': |
||
360 | format_time_width_number = 1.5 |
||
361 | min_len_number = 5 |
||
362 | min_width_number = 11.5 |
||
363 | |||
364 | for i in range(0, ca_len): |
||
365 | line = LineChart() |
||
366 | line.title = 'Reporting Period Cumulative Efficiency - ' + \ |
||
367 | reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" |
||
368 | labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) |
||
369 | line_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number, max_row=table_end_row_number) |
||
370 | line.add_data(line_data, titles_from_data=True) |
||
371 | line.set_categories(labels) |
||
372 | line_data = line.series[0] |
||
373 | line_data.marker.symbol = "circle" |
||
374 | line_data.smooth = True |
||
375 | line.x_axis.crosses = 'min' |
||
376 | line.height = 8.25 |
||
377 | line.width = format_time_width_number * len(time) if len(time) > min_len_number else min_width_number |
||
378 | if line.width > 24: |
||
379 | line.width = 24 |
||
380 | line.dLbls = DataLabelList() |
||
381 | line.dLbls.dLblPos = 't' |
||
382 | line.dLbls.showVal = True |
||
383 | line.dLbls.showPercent = False |
||
384 | chart_col = 'B' |
||
385 | chart_cell = chart_col + str(chart_start_row_number) |
||
386 | chart_start_row_number += 6 |
||
387 | ws.add_chart(line, chart_cell) |
||
388 | |||
389 | #################################################################################################################### |
||
390 | |||
391 | if has_parameters_names_and_timestamps_and_values_data: |
||
392 | |||
393 | ################################################################################################################ |
||
394 | # new worksheet |
||
395 | ################################################################################################################ |
||
396 | |||
397 | parameters_data = report['parameters'] |
||
398 | parameters_names_len = len(parameters_data['names']) |
||
399 | |||
400 | file_name = (re.sub(r'[^A-Z]', '', ws.title))+'_' |
||
401 | parameters_ws = wb.create_sheet(file_name + 'Parameters') |
||
402 | |||
403 | parameters_timestamps_data_max_len = \ |
||
404 | get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps'])) |
||
405 | |||
406 | # Row height |
||
407 | parameters_ws.row_dimensions[1].height = 102 |
||
408 | for i in range(2, 7 + 1): |
||
409 | parameters_ws.row_dimensions[i].height = 42 |
||
410 | |||
411 | for i in range(8, parameters_timestamps_data_max_len + 10): |
||
412 | parameters_ws.row_dimensions[i].height = 60 |
||
413 | |||
414 | # Col width |
||
415 | parameters_ws.column_dimensions['A'].width = 1.5 |
||
416 | |||
417 | parameters_ws.column_dimensions['B'].width = 25.0 |
||
418 | |||
419 | for i in range(3, 12+parameters_names_len*3): |
||
420 | parameters_ws.column_dimensions[format_cell.get_column_letter(i)].width = 15.0 |
||
421 | |||
422 | # Img |
||
423 | img = Image("excelexporters/myems.png") |
||
424 | parameters_ws.add_image(img, 'A1') |
||
425 | |||
426 | # Title |
||
427 | |||
428 | parameters_ws['B3'].alignment = b_r_alignment |
||
429 | parameters_ws['B3'] = 'Name:' |
||
430 | parameters_ws['C3'].border = b_border |
||
431 | parameters_ws['C3'].alignment = b_c_alignment |
||
432 | parameters_ws['C3'] = name |
||
433 | |||
434 | parameters_ws['D3'].alignment = b_r_alignment |
||
435 | parameters_ws['D3'] = 'Period:' |
||
436 | parameters_ws['E3'].border = b_border |
||
437 | parameters_ws['E3'].alignment = b_c_alignment |
||
438 | parameters_ws['E3'] = period_type |
||
439 | |||
440 | parameters_ws['B4'].alignment = b_r_alignment |
||
441 | parameters_ws['B4'] = 'Reporting Start Datetime:' |
||
442 | parameters_ws['C4'].border = b_border |
||
443 | parameters_ws['C4'].alignment = b_c_alignment |
||
444 | parameters_ws['C4'] = reporting_start_datetime_local |
||
445 | |||
446 | parameters_ws['D4'].alignment = b_r_alignment |
||
447 | parameters_ws['D4'] = 'Reporting End Datetime:' |
||
448 | parameters_ws['E4'].border = b_border |
||
449 | parameters_ws['E4'].alignment = b_c_alignment |
||
450 | parameters_ws['E4'] = reporting_end_datetime_local |
||
451 | |||
452 | parameters_ws_current_row_number = 6 |
||
453 | |||
454 | parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font |
||
455 | parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' ' + 'Parameters' |
||
456 | |||
457 | parameters_ws_current_row_number += 1 |
||
458 | |||
459 | parameters_table_start_row_number = parameters_ws_current_row_number |
||
460 | |||
461 | parameters_ws.row_dimensions[parameters_ws_current_row_number].height = 80 |
||
462 | |||
463 | parameters_ws_current_row_number += 1 |
||
464 | |||
465 | table_current_col_number = 2 |
||
466 | |||
467 | for i in range(0, parameters_names_len): |
||
468 | |||
469 | if len(parameters_data['timestamps'][i]) == 0: |
||
470 | continue |
||
471 | |||
472 | col = format_cell.get_column_letter(table_current_col_number) |
||
473 | |||
474 | parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill |
||
475 | parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border |
||
476 | |||
477 | col = format_cell.get_column_letter(table_current_col_number + 1) |
||
478 | |||
479 | parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill |
||
480 | parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border |
||
481 | parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font |
||
482 | parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment |
||
483 | parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data['names'][i] |
||
484 | |||
485 | table_current_row_number = parameters_ws_current_row_number |
||
486 | |||
487 | for j, value in enumerate(list(parameters_data['timestamps'][i])): |
||
488 | col = format_cell.get_column_letter(table_current_col_number) |
||
489 | |||
490 | parameters_ws[col + str(table_current_row_number)].border = f_border |
||
491 | parameters_ws[col + str(table_current_row_number)].font = title_font |
||
492 | parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment |
||
493 | parameters_ws[col + str(table_current_row_number)] = value |
||
494 | |||
495 | col = format_cell.get_column_letter(table_current_col_number + 1) |
||
496 | |||
497 | parameters_ws[col + str(table_current_row_number)].border = f_border |
||
498 | parameters_ws[col + str(table_current_row_number)].font = title_font |
||
499 | parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment |
||
500 | parameters_ws[col + str(table_current_row_number)] = round(parameters_data['values'][i][j], 2) |
||
501 | |||
502 | table_current_row_number += 1 |
||
503 | |||
504 | table_current_col_number = table_current_col_number + 3 |
||
505 | |||
506 | ################################################################################################################ |
||
507 | # parameters chart and parameters table |
||
508 | ################################################################################################################ |
||
509 | |||
510 | ws['B' + str(current_sheet_parameters_row_number)].font = title_font |
||
511 | ws['B' + str(current_sheet_parameters_row_number)] = name + ' ' + 'Parameters' |
||
512 | |||
513 | current_sheet_parameters_row_number += 1 |
||
514 | |||
515 | chart_start_row_number = current_sheet_parameters_row_number |
||
516 | |||
517 | col_index = 0 |
||
518 | |||
519 | for i in range(0, parameters_names_len): |
||
520 | |||
521 | if len(parameters_data['timestamps'][i]) == 0: |
||
522 | continue |
||
523 | |||
524 | line = LineChart() |
||
525 | data_col = 3+col_index*3 |
||
526 | labels_col = 2+col_index*3 |
||
527 | col_index += 1 |
||
528 | line.title = 'Parameters - ' + \ |
||
529 | parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value |
||
530 | labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1, |
||
531 | max_row=(len(parameters_data['timestamps'][i])+parameters_table_start_row_number)) |
||
532 | line_data = Reference(parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number, |
||
533 | max_row=(len(parameters_data['timestamps'][i])+parameters_table_start_row_number)) |
||
534 | line.add_data(line_data, titles_from_data=True) |
||
535 | line.set_categories(labels) |
||
536 | line_data = line.series[0] |
||
537 | line_data.marker.symbol = "circle" |
||
538 | line_data.smooth = True |
||
539 | line.x_axis.crosses = 'min' |
||
540 | line.height = 8.25 |
||
541 | line.width = 24 |
||
542 | line.dLbls = DataLabelList() |
||
543 | line.dLbls.dLblPos = 't' |
||
544 | line.dLbls.showVal = False |
||
545 | line.dLbls.showPercent = False |
||
546 | chart_col = 'B' |
||
547 | chart_cell = chart_col + str(chart_start_row_number) |
||
548 | chart_start_row_number += 6 |
||
549 | ws.add_chart(line, chart_cell) |
||
550 | |||
551 | current_sheet_parameters_row_number = chart_start_row_number |
||
552 | |||
553 | current_sheet_parameters_row_number += 1 |
||
554 | |||
555 | filename = str(uuid.uuid4()) + '.xlsx' |
||
556 | wb.save(filename) |
||
557 | |||
558 | return filename |
||
559 | |||
560 | |||
561 | def get_parameters_timestamps_lists_max_len(parameters_timestamps_lists): |
||
562 | max_len = 0 |
||
563 | for i, value in enumerate(list(parameters_timestamps_lists)): |
||
564 | if len(value) > max_len: |
||
565 | max_len = len(value) |
||
566 | |||
567 | return max_len |
||
568 | |||
569 | |||
570 | def timestamps_data_all_equal_0(lists): |
||
571 | for i, value in enumerate(list(lists)): |
||
572 | if len(value) > 0: |
||
573 | return False |
||
574 | |||
575 | return True |
||
576 | |||
577 | |||
578 | def timestamps_data_not_equal_0(lists): |
||
579 | number = 0 |
||
580 | for i, value in enumerate(list(lists)): |
||
581 | if len(value) > 0: |
||
582 | number += 1 |
||
583 | return number |
||
584 |