def populate(self, ws: Worksheet, cg: ConnectGroup):
ws.title = cg.name
self.create_column_headers(ws)
for person in sorted(
cg.members, key=lambda p: p.personal_attributes[PERSONAL_ATTRIBUTE_NAME]
):
ws.append(self.person_as_row_values(person))
def write_samples_sheet(sheet: Worksheet, df_samples: pd.DataFrame,
min_coverage: int):
"""
Write and format samples sheet in workbook
"""
# write data
for r in dataframe_to_rows(df_samples, header=True, index=False):
sheet.append(r)
# apply conditional filling to perc_covered column
perc_column = next(x[0].column_letter for x in sheet.columns
if x[0].value == "perc_covered")
for i, row in enumerate(df_samples.itertuples(), start=2):
if pd.isna(row.perc_covered) or round(row.perc_covered) < min_coverage:
color = "fbc1c1"
else:
color = "cefbc1"
sheet[f"{perc_column}{i}"].fill = PatternFill(start_color=color,
fill_type="solid")
# improve style
for col in sheet.columns:
col[0].font = Font(name="Calibri", bold=True)
col[0].border = Border(
bottom=Side(border_style="medium", color="000000"))
col[0].alignment = Alignment(horizontal="center")
sheet.column_dimensions[col[0].column_letter].width = 13
def create_all_cards(sheet: Worksheet, index: ScryfallDataIndex) -> None:
"""Create all cards sheet from card_db."""
sheet.title = "All Cards"
sheet.append(ALL_CARDS_SHEET_HEADER)
for name in sorted(index.name_to_cards):
row = [name, get_references(index, name)]
sheet.append(row)
def _add_values_to_sheet(tag: SongTag,
song: Song,
sheet: Worksheet,
second_sheet: Union[Worksheet, None] = None):
"""Add the values from a speicifc tag to the indicated sheet.
If a second sheet is added,
add the second values from the tag.
Parameters
----------
tag : SongTag -
Which tag the data should be received from\n
song : Song -
Which song the data should be receieved from\n
sheet : Worksheet -
Which sheet the data should be added to\n
second_sheet : Union[Worksheet, None], optional -
Second sheet where data should be added, by default None
"""
values = tag.get_tag(song.id3)
if values is None:
return
for value in values:
if tag.value_length == 1:
value = cast(str, value)
sheet.append([value])
elif tag.value_length == 2:
value = cast(List[str], value)
role, person = value
sheet.append([role])
if second_sheet is not None:
second_sheet.append([person])
def __writeCurveChartData(sheet: Worksheet, tests: Iterable[str],
order: Iterable[str], data: Dict[str, dict]) -> dict:
from .TestSuite import _PSNR, _KBS, _TIME, parseSheetLayer
to_write_data = []
out_ref = {}
col = 2
ref_len = 4
def toRefFunc(sheet, cells):
return [
"=" +
__SR_FORMAT.format(sheet=parseSheetLayer(sheet)[0], cell=cell)
for cell in cells
]
for seq in order:
to_write_data.append([
seq,
])
out_ref[seq] = {}
to_write_data.append([
"Data Type", "Test", "Data Point 1", "Data Point 2",
"Data Point 3", "Data Point 4"
])
for test in tests:
out_ref[seq][test] = {}
to_write_data.append(
[_KBS, test, *toRefFunc(test, data[test][seq][_KBS])])
to_write_data.append(
[_PSNR, test, *toRefFunc(test, data[test][seq][_PSNR])])
to_write_data.append(
[_TIME, test, *toRefFunc(test, data[test][seq][_TIME])])
row = len(to_write_data)
out_ref[seq][test][__RATE] = Reference(sheet,
min_col=col,
max_col=col + ref_len,
min_row=row - 2)
out_ref[seq][test][__PSNR] = Reference(sheet,
min_col=col,
max_col=col + ref_len,
min_row=row - 1)
out_ref[seq][test][__TIME] = Reference(sheet,
min_col=col,
max_col=col + ref_len,
min_row=row)
for row in to_write_data:
sheet.append(row)
# hide chart data
sheet.column_dimensions.group('A', 'F', hidden=True)
return out_ref
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 sort_sheet_by(ws, column_of_order=0, descending=False, fixed_headers=True):
s_ws = Worksheet(ws.parent, title=ws.title)
rows = list(ws.rows)
if fixed_headers:
s_ws.append(list_row_values(rows.pop(0)))
for row in sorted(rows,
key=lambda x: x[column_of_order].value,
reverse=descending):
s_ws.append(list_row_values(row))
wb.remove(ws)
wb.copy_worksheet(s_ws)
wb.active.title = ws.title
def write_variants_sheet(sheet: Worksheet, df_variants: pd.DataFrame):
"""
Write and format variants sheet in workbook
"""
# write data
for r in dataframe_to_rows(df_variants, header=True, index=False):
sheet.append(r)
sheet.insert_rows(1)
# apply conditional filling depending of variant presence
for row in sheet.iter_rows(
min_col=8,
max_col=sheet.max_column,
min_row=3,
max_row=sheet.max_row,
):
for cell in row:
if cell.value == 1:
cell.fill = PatternFill(start_color="3bbf97",
fill_type="solid")
cell.value = ""
# improve style (common columns)
column_widths = [8, 8, 8, 18, 10, 14, 14]
for col, w in zip(sheet.iter_cols(max_row=2, max_col=7), column_widths):
colname = col[0].column_letter
col[0].value = col[1].value
col[0].font = Font(name="Calibri", bold=True)
col[0].border = Border(
bottom=Side(border_style="medium", color="000000"))
col[0].alignment = Alignment(horizontal="center")
sheet.column_dimensions[colname].width = w
sheet.merge_cells(f"{colname}1:{colname}2")
# improve style (samples columns)
sheet.row_dimensions[2].height = 60
sheet["H1"].value = "samples"
sheet["H1"].font = Font(name="Calibri", bold=True)
sheet["H1"].alignment = Alignment(horizontal="center")
sheet.merge_cells(start_row=1,
end_row=1,
start_column=8,
end_column=sheet.max_column)
for col in sheet.iter_cols(min_row=2, max_row=2, min_col=8):
col[0].font = Font(name="Calibri", bold=True)
col[0].border = Border(
bottom=Side(border_style="medium", color="000000"))
col[0].alignment = Alignment(horizontal="center", text_rotation=90)
sheet.column_dimensions[col[0].column_letter].width = 3
def to_excel(self, ws: Worksheet) -> None:
self._validate_col_specs(self._col_specs, self._df.columns)
ws.append([f'**{self.name}'])
ws.append([" ".join(str(x) for x in self.destinations)])
ws.append(self.col_names)
ws.append(self.col_units)
df = self._prepare_df_for_write()
for row in dataframe_to_rows(df, index=False, header=False):
ws.append(row)
def to_excel(self, ws: Worksheet) -> None:
self._validate_col_specs(self._col_specs, self._df.columns)
ws.append([f'**{self.name}'])
ws.append([" ".join(str(x) for x in self.destinations)])
ws.append(self.col_names)
ws.append(self.col_units)
for row in dataframe_to_rows(self._df.fillna(NO_DATA_MARKER_ON_WRITE),
index=False,
header=False):
ws.append(row)
def write_dataframe_to_worksheet(
*,
ws: Worksheet,
df: pd.DataFrame,
index: bool = False,
header: bool = True
) -> Worksheet:
"""
Write a dataframe to a worksheet.
Parameters
----------
ws : Worksheet
The worksheet to which the dataframe will be written.
df : pd.DataFrame
The dataframe of data.
index : bool = False
Boolean to determine if dataframe index is written to worksheet.
header : bool = True
Boolean to determine if dataframe header is written to worksheet.
Returns
-------
ws : Worksheet
The worksheet created.
Example
>>> import datasense as ds
>>> ws = ds.write_dataframe_to_worksheet(
>>> ws=ws,
>>> df=df,
>>> index=False,
>>> header=True
>>> )
"""
for row in dataframe_to_rows(
df=df,
index=index,
header=header
):
ws.append(row)
return ws
def write_cursor_to_sheet(sheet: worksheet.Worksheet,
cursor: cx_Oracle.Cursor):
"""
Description: Dumps a prepared cursor into a Excel sheet.
Parameters
----------
sheet: openpyxl worksheet A sheet to dump it into
cursor: cx_Oracle cursor prepared with the data
"""
header = [description[0] for description in cursor.description]
header.append('Customer No')
header.append('Customer Name')
sheet.append(header)
for row in cursor:
customer_details = main.get_customers(row[-1])
lst = list(row)
lst.append(customer_details[0])
lst.append(customer_details[1])
sheet.append(lst)
def create_all_sets(sheet: Worksheet, index: ScryfallDataIndex) -> None:
"""Create all sets sheet from card_db."""
sheet.title = "All Sets"
sheet.append(ALL_SETS_SHEET_HEADER)
sheet.append(ALL_SETS_SHEET_TOTALS)
for card_set in sorted(index.setcode_to_set.values(),
key=lambda cset: cset.released_at):
setcode = card_set.code.upper()
row = [
setcode,
card_set.name,
card_set.released_at,
card_set.block,
card_set.set_type.value,
len(index.setcode_to_cards[card_set.code]),
f"=COUNTIF('{setcode}'!A:A,\">0\")",
f"=COUNTIF('{setcode}'!A:A,\">=4\")",
f"=SUM('{setcode}'!A:A)",
]
sheet.append(row)
def create_set_sheet(sheet: Worksheet, collection: MagicCollection,
setcode: str) -> None:
"""Populate sheet with card information from a given set."""
index = collection.oracle.index
sheet.append(SET_SHEET_HEADER)
sheet.title = setcode.upper()
for card in index.setcode_to_cards[setcode]:
rownum = ROW_OFFSET + index.id_to_setindex[card.id]
row: List[Optional[Any]] = [
HAVE_TMPL.format(rownum=rownum),
card.name,
str(card.id),
card.collector_number,
card.artist,
]
card_counts = collection.counts.get(card.id, {})
for count_type in counts.CountType:
row.append(card_counts.get(count_type))
row.append(get_references(index, card.name, exclude_sets={setcode}))
sheet.append(row)
def fill_excel_sheet(self,
worksheet: Worksheet,
csv_list: list,
name: str = None,
title: str = None,
description: str = None) -> None:
"""
This method adds an additional sheet to the given workbook
:return:
"""
start_row = 1
name = name if name is not None else self._name
title = title if title is not None else self.title
description = description if description is not None else self.description
worksheet.title = name
if description:
csv_list.insert(0, [])
csv_list.insert(0, [description])
start_row += 2
if title:
csv_list.insert(0, [])
csv_list.insert(0, [title])
start_row += 2
for row in csv_list:
try:
worksheet.append(row)
except IllegalCharacterError:
print("ignoring row due to illegal character: {}".format(row),
file=sys.stderr)
except ValueError:
raise ValueError("cannot add row to sheet '{}': {}".format(
self._name, row))
dimension = worksheet.calculate_dimension()
dimension = "A{}:{}".format(start_row, dimension.split(":")[-1])
table = Table(displayName=self._name.replace(" ", ""), ref=dimension)
style = TableStyleInfo(name="TableStyleLight8")
table.tableStyleInfo = style
worksheet.add_table(table)
def append_row(self, sheet: Worksheet, row_data):
assert isinstance(row_data, (tuple, list))
sheet.append(row_data)
def _append_table_to_openpyxl_worksheet(table: Table,
ws: OpenpyxlWorksheet,
sep_lines: int,
na_rep: str = "-") -> None:
"""Write table at end of sheet, leaving sep_lines blank lines before."""
units = table.units
if table.metadata.transposed:
ws.append([f"**{table.name}*"])
ws.append([" ".join(str(x) for x in table.metadata.destinations)])
for col in table:
ws.append(
[str(col.name), str(col.unit)] +
list(_represent_col_elements(col.values, col.unit, na_rep)), )
else:
ws.append([f"**{table.name}"])
ws.append([" ".join(str(x) for x in table.metadata.destinations)])
ws.append(table.column_names)
ws.append(units)
for row in table.df.itertuples(index=False, name=None):
# TODO: apply format string specified in ColumnMetadata
ws.append(_represent_row_elements(row, units, na_rep))
for _ in range(sep_lines):
ws.append([]) # blank row marking table end
def _append_table_to_openpyxl_worksheet(table: Table,
ws: OpenpyxlWorksheet,
na_rep: str = "-") -> None:
units = table.units
ws.append([f"**{table.name}"])
ws.append([" ".join(str(x) for x in table.metadata.destinations)])
ws.append(table.column_names)
ws.append(units)
for row in table.df.itertuples(index=False, name=None):
# TODO: apply format string specified in ColumnMetadata
ws.append(_represent_row_elements(row, units, na_rep))
ws.append([]) # blank row marking table end
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
