Newer
Older
from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font
from openpyxl.styles.fills import Stop, PatternFill
fname = 'testResults.xlsx'
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):
self.wb = getWorkbook()
self.ws = self.wb.active
def getEntryWithId(self, 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.
"""
def getLastRow(self):
for cell in self.ws["A"]:
if cell.value is None:
return cell.row
return cell.row + 1
def writeTestEntry(self, testEntry):
lastRow = self.getLastRow()
cellCol = ExcelWriter.PASS_COL if testEntry.result == "PASS" else ExcelWriter.FAIL_COL
entryVals = testEntry.asList()
for col, cellValue in zip(self.ws.iter_cols(min_row=lastRow, max_col=len(entryVals), max_row=lastRow), entryVals):
for cell in col:
cell.value = cellValue
cell.fill = PatternFill("solid", fgColor=cellCol)# cellCol
def save(self):
self.wb.save(filename = fname)
def initWorkbook(wb):
"""
Writes column headers to ws
"""
headers = [("Test ID", 10), ("Test name", 80), ("Result", 6), ("Error Message", 100), ("NFV API", 25), ("Robot Test File", 25)]
headerFont = Font(bold=True)
ws = wb.active
for col, header in zip(ws.iter_cols(min_row=1, max_col=len(headers), max_row=1), headers):
for cell in col:
headerName = header[0]
colsize = header[1]
ws.column_dimensions[get_column_letter(cell.column)].width = colsize
cell.value = headerName
cell.font = headerFont
def getWorkbook():
try:
wb = load_workbook(filename = fname)
return wb
except FileNotFoundError:
wb = Workbook()
initWorkbook(wb)
return wb