Fix for #7, one work sheet per test suite

Signed-off-by: Frank Bryden Frank.Bryden@etsi.org

Merge request reports

Loading
+103 −23
Changes for write_excel.py: 103 added lines, 23 removed lines.
Original line number Original line Diff line number Diff line
@@ -8,6 +8,8 @@ from openpyxl.styles import Font
from openpyxl.styles.fills import PatternFill
from openpyxl.styles.fills import PatternFill
from openpyxl.chart import PieChart, Reference
from openpyxl.chart import PieChart, Reference


SUMMARY_TITLE = "Summary"

class ExcelWriter:
class ExcelWriter:
    """
    """
    Utility class which writes `TestEntry` objects to an excel file.
    Utility class which writes `TestEntry` objects to an excel file.
@@ -20,7 +22,8 @@ class ExcelWriter:
        self.work_book = self.get_workbook()
        self.work_book = self.get_workbook()
        self.work_sheet = self.work_book.active
        self.work_sheet = self.work_book.active


    def get_entry_with_id(self, test_id):
    @staticmethod
    def get_entry_with_id(work_sheet, test_id):
        """
        """
        When inserting an entry, it might be a test being re-run. In that case,
        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 row containing that test needs to be updated, as opposed to appending
@@ -28,7 +31,7 @@ class ExcelWriter:
        This method finds that row, returning the row number,
        This method finds that row, returning the row number,
        or -1 in the case where that test is not in the report yet.
        or -1 in the case where that test is not in the report yet.
        """
        """
        for cell in self.work_sheet["A"]:
        for cell in work_sheet["A"]:
            if cell.value is None:
            if cell.value is None:
                return -1
                return -1
            if cell.value == test_id:
            if cell.value == test_id:
@@ -36,23 +39,30 @@ class ExcelWriter:
                return cell.row
                return cell.row
        return -1
        return -1


    def get_last_row(self):
    @staticmethod
    def get_last_row(work_sheet, verbose=False):
        """
        """
        return index of first empty row
        return index of first empty row
        """
        """
        for cell in self.work_sheet["A"]:
        if verbose:
            print("HERE", work_sheet.title)
        for cell in work_sheet["A"]:
            if cell.value is None:
            if cell.value is None:
                return cell.row
                return cell.row
            if verbose:
                print(cell, cell.value)
        print("Early return with val {}".format(cell.row+1))
        return cell.row + 1
        return cell.row + 1


    def write_test_entry(self, test_entry):
    def write_test_entry(self, test_entry):
        """
        """
        Write a test entry to the work_sheet
        Write a test entry to the work_sheet
        """
        """
        existing_entry_row = self.get_entry_with_id(test_entry.test_id)
        existing_entry_row = self.get_entry_with_id(self.work_sheet, test_entry.test_id)
        last_row = self.get_last_row()

        # Use the above two values to pick a row
        # Use above value to determine if last row needs to be computed and used
        entry_row = existing_entry_row if existing_entry_row != -1 else last_row
        entry_row = (existing_entry_row if existing_entry_row != -1 else
                                          self.get_last_row(self.work_sheet))


        # Pick a cell color based on test outcome
        # Pick a cell color based on test outcome
        cell_col = ExcelWriter.PASS_COL if test_entry.result == "PASS" else ExcelWriter.FAIL_COL
        cell_col = ExcelWriter.PASS_COL if test_entry.result == "PASS" else ExcelWriter.FAIL_COL
@@ -80,45 +90,70 @@ class ExcelWriter:
            init_workbook(work_book)
            init_workbook(work_book)
            return work_book
            return work_book


    def get_chart_work_sheet(self):
    def get_chart_work_sheet(self, test_suite):
        """
        """
        Returns work sheet where charts will be drawn to.
        Returns work sheet where charts will be drawn to.
        Creates if does not exist
        Creates if does not exist
        """
        """
        try:
        try:
            chart_work_sheet = self.work_book["charts"]
            chart_work_sheet = self.work_book[test_suite]
            return chart_work_sheet
            return chart_work_sheet
        except KeyError:
        except KeyError:
            return self.work_book.create_sheet("charts")
            return self.work_book.create_sheet(test_suite)


    def write_pie_chart_data(self):
    def write_pie_chart_data(self):
        """
        """
        Writes pie chart data (the charts sheet) required to construct the pie chart later
        Writes pie chart data (the charts sheet) required to construct the pie chart later
        """
        """
        chart_work_sheet = self.get_chart_work_sheet()
        # Start from scratch by deleting all chart_sheets
        self.delete_chart_sheets()


        # Format data from main work sheet and insert into charts sheet
        # Format data from main work sheet and insert into charts sheet
        formatted_data = self.get_formatted_data()
        for test_suite, test_files in formatted_data.items():
            chart_work_sheet = self.get_chart_work_sheet(test_suite)
            for test_file, test_data in test_files.items():
                self.write_single_pie_chart(chart_work_sheet, test_file,
                                            test_data[1:], test_data[0])

    def write_single_pie_chart(self, chart_work_sheet, name, data, test_suite_id):
        """
        Writes a single pie chart given pie chart data and chart location
        """
        existing_entry_row = self.get_entry_with_id(chart_work_sheet, test_suite_id)

        # Use above value to determine if last row needs to be computed and used
        location = (existing_entry_row if existing_entry_row != -1 else
                    self.get_last_row(chart_work_sheet, True) - 1)

        print("Pie chart with name {}, data {} and location {}".format(name, data, location))


        # Titles
        # Titles
        chart_work_sheet["A2"] = "PASS"
        chart_work_sheet.cell(row=location + 1, column=1).value = test_suite_id
        chart_work_sheet["A3"] = "FAIL"
        chart_work_sheet.cell(row=location + 2, column=1).value = "PASS"
        chart_work_sheet.cell(row=location + 3, column=1).value = "FAIL"


        #Data
        #Data
        data = self.get_pass_fail_counts()
        chart_work_sheet.cell(row=location + 1, column=2).value = name
        chart_work_sheet.cell(row=2, column=2).value = data[0]
        chart_work_sheet.cell(row=location + 2, column=2).value = data[0]
        chart_work_sheet.cell(row=3, column=2).value = data[1]
        chart_work_sheet.cell(row=location + 3, column=2).value = data[1]


        # Get data from main work sheet
        # Get data from work sheet
        data = Reference(chart_work_sheet, min_col=2, max_col=2, min_row=2, max_row=3)
        data = Reference(chart_work_sheet, min_col=2, max_col=2,
        labels = Reference(chart_work_sheet, min_col=1, max_col=1, min_row=2, max_row=3)
                         min_row=location+2, max_row=location+3)
        labels = Reference(chart_work_sheet, min_col=1, max_col=1,
                           min_row=location+2, max_row=location+3)


        # Construct pie chart
        # Construct pie chart
        pie = PieChart()
        pie = PieChart()
        pie.add_data(data)#, titles_from_data=True)
        pie.add_data(data)#, titles_from_data=True)
        pie.set_categories(labels)
        pie.set_categories(labels)
        pie.title = "PASS/FAIL Distribution"
        pie.title = "PASS/FAIL Distribution for {}".format(name)


        chart_work_sheet.add_chart(pie, "E1")
        print("{}/3 = {}, *15 + 1 = {}".format(location, int(location/3), int(location/3)*15 +1))
        chart_location = int(location/3)*15 + 1
        chart_work_sheet.add_chart(pie, "E{}".format(chart_location))
        print("Wrote chart to E{}".format(chart_location))


    def get_pass_fail_counts(self):
    def get_pass_fail_counts(self):
        """
        """
@@ -126,7 +161,7 @@ class ExcelWriter:
        """
        """


        # Get data from main work sheet
        # Get data from main work sheet
        last_row = self.get_last_row()
        last_row = self.get_last_row(self.work_sheet)
        pass_count = 0
        pass_count = 0
        fail_count = 0
        fail_count = 0
        for row in self.work_sheet.iter_rows(min_col=3, max_col=3, min_row=2, max_row=last_row - 1):
        for row in self.work_sheet.iter_rows(min_col=3, max_col=3, min_row=2, max_row=last_row - 1):
@@ -139,6 +174,50 @@ class ExcelWriter:
                    print("ERROR: unknown value {}".format(cell.value))
                    print("ERROR: unknown value {}".format(cell.value))
        return (pass_count, fail_count)
        return (pass_count, fail_count)


    def get_formatted_data(self):
        """
        Return PASS/FAIL stats as dict of dicts each containing a list with
        [test ID, # of PASS, # of FAIL].
        For example,
        {
            'NSDManagement': {
                'NSDContent' : [5.3.1.3, 11, 7],
                'NSDescriptors': [2, 9, 4]
            },
            'NSLifecycleManagement': {
                'NSLCM': [0, 3, 8]
            }
        }
        """

        # Get data from main work sheet
        last_row = self.get_last_row(self.work_sheet)
        data = {}
        for row in self.work_sheet.iter_rows(min_col=1, max_col=6, min_row=2, max_row=last_row - 1):
            tuple_index = 1 if row[2].value == "PASS" else 2
            test_id = row[0].value
            data.setdefault(row[4].value, {}).setdefault(row[5].value,
                                                         [self.get_suite_id_from_test_id(test_id),
                                                          0, 0])[tuple_index] += 1
        print(data)
        return data

    @staticmethod
    def get_suite_id_from_test_id(test_id):
        """
        From a test ID such as 5.3.1.3.1,
        returns suite id 5.3.1.3
        """
        return ".".join(test_id.split(".")[:-1])

    def delete_chart_sheets(self):
        """
        Delete all the sheets in work book except for SUMMARY
        """
        for sheet in self.work_book.worksheets:
            if sheet.title != SUMMARY_TITLE:
                self.work_book.remove_sheet(sheet)

    def save(self):
    def save(self):
        """
        """
        Save workbook to disk.
        Save workbook to disk.
@@ -153,6 +232,7 @@ def init_workbook(work_book):
               ("NFV API", 25), ("Robot Test File", 25)]
               ("NFV API", 25), ("Robot Test File", 25)]
    header_font = Font(bold=True)
    header_font = Font(bold=True)
    work_sheet = work_book.active
    work_sheet = work_book.active
    work_sheet.title = SUMMARY_TITLE
    for col, header in zip(work_sheet.iter_cols(min_row=1, max_col=len(headers),
    for col, header in zip(work_sheet.iter_cols(min_row=1, max_col=len(headers),
                                                max_row=1), headers):
                                                max_row=1), headers):
        for cell in col:
        for cell in col:
Loading