Skip to content
Snippets Groups Projects
writeExcel.py 2.2 KiB
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