def style_all_cards(sheet: Worksheet) -> None: """Apply styles to the all cards sheet.""" sheet.freeze_panes = sheet["B2"] col_width_hidden = [("A", 24, False), ("B", 32, False)] for col, width, hidden in col_width_hidden: cdim = sheet.column_dimensions[col] cdim.width = width cdim.hidden = hidden
def create_tab(worksheet: Worksheet, rows: list, formatting: Formatter): """ Function to specifically create the Non Compliant Resource worksheet. Modified passed in workbook. Parameters: worksheet (Worksheet): The worksheet to modify. rows (list): Data for the rows of each item formatting (object): Object containing various formatting information """ # add header worksheet.append(formatting.get_header_names()) # sort data, since adding a sort to the filter has no effect until excel sorts it sort_header = ([h for h in formatting.get_headers() if h.sort] + [None])[0] if sort_header: rows = list(sorted(rows, key=sort_header.get_value)) for row in rows: worksheet.append(formatting.format_resource(row)) # no footer worksheet.title = formatting.title worksheet.freeze_panes = formatting.freeze # add filtering capability if formatting.excel_filter: worksheet.auto_filter.ref = 'A1:{}{:d}'.format( get_column_letter(len(formatting.get_header_names())), len(rows) + 1 # one header row + number of rows ) # set column widths for idx, header in enumerate(formatting.get_headers()): worksheet.column_dimensions[get_column_letter(idx + 1)].width = header.width # set column conditional formatting for idx, header in enumerate(formatting.get_headers()): if header.conditional_formatting: column_range = '{0}2:{0}{1:d}'.format( get_column_letter(idx + 1), # excel is 0 based indexing len(rows) + 1 # number of rows + 1 header row ) worksheet.conditional_formatting.add(column_range, header.conditional_formatting) # bold header row for header_cell in worksheet[1]: header_cell.font = Font(bold=True) return worksheet
def style_set_sheet(sheet: Worksheet) -> None: """Apply styles to a set sheet.""" sheet.freeze_panes = sheet["C2"] col_width_hidden = [ ("A", 5, False), ("B", 24, False), ("C", 10, True), ("D", 8, True), ("E", 20, True), ("F", 8, False), ("G", 6, False), ("H", 10, False), ] for col, width, hidden in col_width_hidden: cdim = sheet.column_dimensions[col] cdim.width = width cdim.hidden = hidden
def style_all_sets(sheet: Worksheet) -> None: """Apply styles to the all sets sheet.""" sheet.freeze_panes = sheet["C3"] col_width_hidden = [ ("A", 8, False), ("B", 30, False), ("C", 12, True), ("D", 22, True), ("E", 15, True), ("F", 6, False), ("G", 7, False), ("H", 8, False), ("I", 7, False), ] for col, width, hidden in col_width_hidden: cdim = sheet.column_dimensions[col] cdim.width = width cdim.hidden = hidden
def create_matrix_tab( worksheet: Worksheet, matrix_rows: list, account_overall_scores: dict, accounts: dict ) -> Worksheet: """ Function to generate the workbook based data already gatered and parsed. Parameters: matrix_rows (list): Direct input for the itemized worksheet. account_overall_scores (dict): Mapping from account id to account overall score accounts (list): List of accounts. Returns: Workbook: The workbook object ready to be saved. """ formatting = MatrixTabFormatting() ### Add data ### # header rows account_header = [] for account in accounts: if 'account_name' in account: account_header.append(account['account_name']) else: account_header.append(account['accountId']) # add header row worksheet.append(formatting.HEADERS + account_header) # add account score rows worksheet.append([formatting.ACCOUNT_SCORE, '', ''] + list(account_overall_scores.values())) # add requirement rows rows = sorted(matrix_rows, key=lambda row: row['description']) # sort by description field for row in rows: worksheet.append([ row['description'], row['requirementId'], row['severity'] ] + row['numFailing']) if all(score == scores_table.NOT_APPLICABLE for score in row['numFailing']): worksheet.row_dimensions[worksheet.max_row].hidden = True # add footer worksheet.append(['']) # empty row worksheet.append([f'Scored Against CSS Version: {formatting.version}']) worksheet.append([f'Report Generated at {datetime.now()} GMT']) ### Apply formatting ### worksheet.title = formatting.TITLE # bold headers for header_cell in worksheet[1][:len(formatting.HEADERS)]: header_cell.font = Font(bold=True, size=11) # vertically align account names for readability for account_name in worksheet[1][len(formatting.HEADERS):]: account_name.alignment = Alignment(text_rotation=45) # word wrap long descriptions for description in worksheet['A']: description.alignment = Alignment(wrap_text=True) # freeze first column and first row worksheet.freeze_panes = formatting.FREEZE # bold overall scores overall_score_row = 2 for grade_cell in worksheet[overall_score_row][:worksheet.max_column]: grade_cell.font = Font(bold=True, size=11) # right align ACCOUNT_SCORE cell worksheet[overall_score_row][1].alignment = Alignment(horizontal='right') # set appropriate font size for row in worksheet.iter_rows(min_row=overall_score_row + 1): for cell in row: cell.font = Font(size=9) # set Description column width worksheet.column_dimensions['A'].width = 80 # set other column widths for col_index in range(len(formatting.HEADERS) + 1, worksheet.max_column + 1): worksheet.column_dimensions[get_column_letter(col_index)].width = 8 # hide requirement id column worksheet.column_dimensions['B'].hidden = True # cell coloring/conditional formatting # format account scores colors_ordered_by_weight = list(reversed(sorted(formatting.severity_formatting.values(), key=lambda severity: severity['weight']))) for account_score in worksheet[overall_score_row][len(formatting.HEADERS):]: try: score = int(account_score.value) except: # pylint: disable=bare-except score = 0 account_score.number_format = '0' # colors in reverse order by weight so first one encountered is correct for color in colors_ordered_by_weight: if score >= color['weight']: account_score.fill = PatternFill(start_color=color['fill'], end_color=color['fill'], fill_type='solid') account_score.font = Font(color=color['font_color'], bold=True) break # add conditional formatting for error scores score_cell_range = '{}3:{}{:d}'.format( get_column_letter(len(formatting.HEADERS) + 1), get_column_letter(worksheet.max_column), len(matrix_rows) + 2 ) score_cell_top_left = '{}3'.format(get_column_letter(len(formatting.HEADERS) + 1)) for error_format in formatting.error_formatting: # convert python string to excel string if isinstance(error_format['value'], str): check_value = formatting.excel_string(error_format['value']) else: check_value = error_format['value'] worksheet.conditional_formatting.add( score_cell_range, Rule( type='expression', formula=[f'{check_value}={score_cell_top_left}'], priority=worksheet.conditional_formatting.max_priority + 1, stopIfTrue=True, dxf=DifferentialStyle( font=Font( color=error_format['font_color'] ), fill=PatternFill( start_color=error_format['fill'], end_color=error_format['fill'], fill_type='solid', ) ) ) ) severity_column_reference = '${}3'.format(get_column_letter(formatting.SEVERITY_COLUMN)) for severity, severity_format in formatting.severity_formatting.items(): # convert python string to excel string check_value = formatting.excel_string(severity) worksheet.conditional_formatting.add( score_cell_range, Rule( type='expression', formula=[f'{check_value}={severity_column_reference}'], priority=worksheet.conditional_formatting.max_priority + 1, stopIfTrue=True, dxf=DifferentialStyle( font=Font( color=severity_format['font_color'] ), fill=PatternFill( start_color=severity_format['fill'], end_color=severity_format['fill'], fill_type='solid', ) ) ) ) return worksheet
def create_ncr_tab(worksheet: Worksheet, ncr_data: list): """ Function to specifically create the Non Compliant Resource worksheet. Modified passed in workbook. Parameters: worksheet (Worksheet): The worksheet to modify. ncr_data (list): The ncr data to be dropped into the worksheet. """ formatting = NcrTabFormatting() # add header worksheet.append(formatting.get_header_names()) # sort data, since adding a sort to the filter has no effect until excel sorts it sort_header = ([h for h in formatting.headers if h.get('sort')] + [None])[0] if sort_header: ncrs = sorted(ncr_data, key=lambda ncr: get_value(sort_header, ncr)) else: ncrs = ncr_data for ncr in ncrs: if ncr.get('isHidden'): # We've marked this one for hiding continue worksheet.append(formatting.format_resource(ncr)) # no footer worksheet.title = formatting.TITLE worksheet.freeze_panes = formatting.FREEZE # starting_column = ord('A') + scorecard.NCR_STARTING_COLUMN for idx, header in enumerate(formatting.headers): worksheet.column_dimensions[get_column_letter(idx + 1)].width = header['width'] # bold header row for header_cell in worksheet[1]: header_cell.font = Font(bold=True) starting_column = 'A' # add filtering capability worksheet.auto_filter.ref = 'A1:{}{:d}'.format( get_column_letter(worksheet.max_column), worksheet.max_row ) italics_max_row = max(worksheet.max_row, 3) # add conditional formatting for resource rows with a valid exclusion (italics) cell_range = '{}2:{}{:d}'.format(starting_column, get_column_letter(worksheet.max_column), italics_max_row) exclusion_valid_column = get_column_letter(formatting.get_exclusion_applied_header_index() + 1) worksheet.conditional_formatting.add( cell_range, FormulaRule( formula=['AND(${0}2=TRUE, NOT(ISBLANK(${0}2)))'.format(exclusion_valid_column)], font=formatting.VALID_EXCLUSION_FONT ) ) # add conditional formatting for resource rows with an expired exclusion (italic red text) worksheet.conditional_formatting.add( cell_range, FormulaRule( formula=['AND(${0}2=FALSE, NOT(ISBLANK(${0}2)))'.format(exclusion_valid_column)], font=formatting.INVALID_EXCLUSION_FONT, ) ) return worksheet
def _freeze_header(sheet: Worksheet): """Freezes the first row in the sheet""" _log.debug("freezing header in sheet: %s", sheet) sheet.freeze_panes = "A2"