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