Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
"""
Contains utility class for writing to xlsx
"""
from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font
from openpyxl.styles.fills import PatternFill
class ExcelWriter:
"""
Utility class which writes `TestEntry` objects to an excel file.
Creates file if it does not exist.
"""
PASS_COL = "00FF00"
FAIL_COL = "FF0000"
def __init__(self, output_file):
self.work_book = self.get_workbook()
self.work_sheet = self.work_book.active
self.output_file = output_file
def get_entry_with_id(self, test_id):
"""
When inserting an entry, it might be a test being re-run. In that case,
the row containing that test needs to be updated, as opposed to appending
the entry to the end of the file.
This method finds that row, returning the row number,
or -1 in the case where that test is not in the report yet.
"""
for cell in self.work_sheet["A"]:
if cell.value is None:
return -1
if cell.value == test_id:
print("MATCH with id {} at row {}".format(test_id, cell.row))
return cell.row
return -1
def get_last_row(self):
"""
return index of first empty row
"""
for cell in self.work_sheet["A"]:
if cell.value is None:
return cell.row
return 1
def write_test_entry(self, test_entry):
"""
Write a test entry to the work_sheet
"""
existing_entry_row = self.get_entry_with_id(test_entry.id)
last_row = self.get_last_row()
# Use the above two values to pick a row
entry_row = existing_entry_row if existing_entry_row != -1 else last_row
# Pick a cell color based on test outcome
cell_col = ExcelWriter.PASS_COL if test_entry.result == "PASS" else ExcelWriter.FAIL_COL
# Test entry as a list
entry_vals = test_entry.as_list()
for col, cell_vale in zip(self.work_sheet.iter_cols(min_row=entry_row,
max_col=len(entry_vals),
max_row=entry_row), entry_vals):
for cell in col:
cell.value = cell_vale
cell.fill = PatternFill("solid", fgColor=cell_col)# cellCol
def get_workbook(self):
"""
Returns workbook at output_file/.
Creates and initialises it (adds headers) if it doesn't exist yet.
"""
try:
work_book = load_workbook(filename=self.output_file)
return work_book
except FileNotFoundError:
work_book = Workbook()
init_workbook(work_book)
return work_book
def save(self):
"""
Save workbook to disk.
"""
self.work_book.save(filename=self.output_file)
def init_workbook(work_book):
"""
Writes column headers to ws.
"""
headers = [("Test ID", 10), ("Test name", 80), ("Result", 6), ("Error Message", 100),
("NFV API", 25), ("Robot Test File", 25)]
header_font = Font(bold=True)
work_sheet = work_book.active
for col, header in zip(work_sheet.iter_cols(min_row=1, max_col=len(headers),
max_row=1), headers):
for cell in col:
header_name = header[0]
colsize = header[1]
work_sheet.column_dimensions[get_column_letter(cell.column)].width = colsize
cell.value = header_name
cell.font = header_font