Loading write_excel.py +103 −23 Viewed Changes for write_excel.py: 103 added lines, 23 removed lines. Original line number Original line Diff line number Diff line Loading @@ -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. Loading @@ -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 Loading @@ -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: Loading @@ -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 Loading Loading @@ -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): """ """ Loading @@ -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): Loading @@ -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. Loading @@ -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 Loading
write_excel.py +103 −23 Viewed Changes for write_excel.py: 103 added lines, 23 removed lines. Original line number Original line Diff line number Diff line Loading @@ -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. Loading @@ -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 Loading @@ -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: Loading @@ -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 Loading Loading @@ -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): """ """ Loading @@ -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): Loading @@ -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. Loading @@ -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