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"