def highlight_excel(): workbook = load_workbook(filename=excel_file) sheet = workbook.active row_count = sheet.max_row dimensions = sheet.dimensions sheet.auto_filter.ref = dimensions red_background = PatternFill(bgColor='ff4d4d') yellow_background = PatternFill(bgColor='ffff80') green_background = PatternFill(bgColor='ccffcc') diff_style1 = DifferentialStyle(fill=red_background) diff_style2 = DifferentialStyle(fill=yellow_background) diff_style3 = DifferentialStyle(fill=green_background) rule3 = Rule(type="expression", dxf=diff_style3) rule3.formula = ["$D1>=7"] sheet.conditional_formatting.add('A1:C' + str(row_count), rule3) sheet.conditional_formatting.add('E1:J' + str(row_count), rule3) rule1 = Rule(type="expression", dxf=diff_style1) rule1.formula = ["$D1<5"] sheet.conditional_formatting.add('A1:C' + str(row_count), rule1) sheet.conditional_formatting.add('E1:J' + str(row_count), rule1) rule2 = Rule(type="expression", dxf=diff_style2) rule2.formula = (["$D1<7"]) sheet.conditional_formatting.add('A1:C' + str(row_count), rule2) sheet.conditional_formatting.add('E1:J' + str(row_count), rule2) data = pd.read_csv(csv_collection) start_value = min(data['SCORE']) end_value = max(data['SCORE']) data_bar_rule = DataBarRule(start_type="num", start_value=start_value, end_type="num", end_value=end_value, color='53ff4d') sheet.conditional_formatting.add('D2:D' + str(row_count), data_bar_rule) red_text = Font(color='a70000') green_text = Font(color='00bb00') for element in range(2, row_count + 1): sheet['F' + str(element)].number_format = numbers.FORMAT_CURRENCY_USD sheet['G' + str(element)].number_format = numbers.FORMAT_CURRENCY_USD sheet['H' + str(element)].number_format = numbers.FORMAT_CURRENCY_USD cell_value = sheet.cell(element, 8).value if int(cell_value) < 0: sheet.cell(element, 8).font = red_text else: sheet.cell(element, 8).font = green_text workbook.save(excel_file)
def create_xls(args, final_acl): print('Creating the spreadsheet...') filename = os.path.join(args[4], args[5] + ".xlsx") # Create workbook with the one defaut sheet and rename it wb = Workbook() ws1 = wb.active ws1.title = "ACL Report" # Add the headers, set font, colour and column width (from header dictionary) for col, head in zip(range(1,len(header) + 1), header.items()): ws1['{}1'.format(get_column_letter(col))] = head[0] # get_column_letter converts number to letter ws1['{}1'.format(get_column_letter(col))].fill = PatternFill(bgColor=colors.Color("00DCDCDC")) ws1['{}1'.format(get_column_letter(col))].font = Font(bold=True, size=14) ws1.column_dimensions[get_column_letter(col)].width = head[1] # Add the ACE entries. The columns holding numbers are changed to integrars for ace in final_acl: ace[1] = int(ace[1]) ace[8] = int(ace[8]) ws1.append(ace) # Add a key at start with info on the colourised rows for ACEs with frequent hitcnts ws1.insert_rows(1) ws1.insert_rows(2) keys = {'A1': 'Key:', 'B1':'Hit in last 1 day', 'E1':'Hit in last 7 days', 'G1':'Hit in last 30 days', 'I1':'Inactive'} colour = {'B1':'E6B0AA', 'E1':'A9CCE3', 'G1':'F5CBA7', 'I1':'D4EFDF'} for cell, val in keys.items(): ws1[cell] = val ws1['A1'].font = Font(bold=True) for cell, col in colour.items(): ws1[cell].fill = PatternFill(start_color=col, end_color=col, fill_type='solid') ws1.freeze_panes = ws1['A4'] # Freezes the top row (A1) so remains when scrolling ws1.auto_filter.ref = 'A3:L4' # Adds dropdown to headers to the headers # Colours used for columns dependant on the last hit data (J column). Formula is a standard XL formula style_grn = DifferentialStyle(fill=PatternFill(bgColor=colors.Color("00D4EFDF"))) rule_inactive = Rule(type="expression",formula=['=$L1="inactive"'], dxf=style_grn) style_red = DifferentialStyle(fill=PatternFill(bgColor=colors.Color("00E6B0AA"))) rule_1day = Rule(type="expression",formula=["=AND(TODAY()-$J1>=0,TODAY()-$J1<=1)"], dxf=style_red) style_blu = DifferentialStyle(fill=PatternFill(bgColor=colors.Color("00A9CCE3"))) rule_7day = Rule(type="expression", formula=["=AND(TODAY()-$J1>=0,TODAY()-$J1<=7)"], dxf=style_blu) style_org = DifferentialStyle(fill=PatternFill(bgColor=colors.Color("00F5CBA7"))) rule_30day = Rule(type="expression", formula=["=AND(TODAY()-$J1>=0,TODAY()-$J1<=30)"], dxf=style_org) # Apply the rules to workbook and save it for rule in [rule_inactive, rule_1day, rule_7day, rule_30day]: ws1.conditional_formatting.add(ws1.dimensions, rule) wb.save(filename) print('File {} has been created'.format(filename))
def add_conditional_formatting(colour, summary_result, sheetname): ''' Add conditional formatting in the Summary sheet in Excel, for each check performed. This will fill cells containing certain values. Red for failed tests, blue for tests that need to be checked, green for passed tests. ## Arguments ## colour -- A string specifying hex colour code (RRGGBB) to use for filling. summary_result -- A string with the summary result, either 'Fail', 'Check', or 'Pass' sheetname -- A string specifying the target sheet for the formatting, i.e. 'Summary' ## Example ## add_conditional_formatting(colour='87CEFA', summary_result='Check', sheetname='Summary) ''' fill_col = PatternFill(bgColor=colour) # specify colour style_to_apply = DifferentialStyle( fill=fill_col) # specifyl style (fill) r = Rule(type="expression", dxf=style_to_apply, stopIfTrue=True) # specify rule r.formula = [f'$B2="{summary_result}"' ] # only search in Column B, starting on second row wb[sheetname].conditional_formatting.add( f'A2:C{wb[sheetname].max_row}', r) # apply formatting
def _save_styles(self, wb): """Formatting for non color scale conditional formatting uses the dxf style list in styles.xml. This scans the cf_rules for dxf styles which have not been added - and saves them to the workbook. When adding a conditional formatting rule that uses a font, border or fill, this must be called at least once before saving the workbook. :param wb: the workbook """ for rules in self.cf_rules.values(): for rule in rules: if 'dxf' in rule: dxf = DifferentialStyle() if 'font' in rule['dxf'] and isinstance( rule['dxf']['font'], Font): # DXF font is limited to color, bold, italic, underline and strikethrough dxf.font = rule['dxf']['font'] if 'border' in rule['dxf'] and isinstance( rule['dxf']['border'], Border): dxf.border = rule['dxf']['border'] if 'fill' in rule['dxf'] and isinstance( rule['dxf']['fill'], PatternFill): dxf.fill = rule['dxf']['fill'] wb._differential_styles.append(dxf) rule.pop('dxf') rule['dxfId'] = len(wb._differential_styles) - 1
def conditionalColoring(ws, color, formula, rg): # differential style, conditional color formatting. dxf = DifferentialStyle(fill=color) r = Rule(type="expression", dxf=dxf, stopIfTrue=True) r.formula = formula ws.conditional_formatting.add(rg, r) return (ws)
def addrulesBW(ws): # rule pour les dates inférieures diff = DifferentialStyle(font=Font(bold=True)) rule5 = Rule(type="expression", dxf=diff, formula=['AND(NOT(ISBLANK($D2)),($D2<TODAY()))']) ws.conditional_formatting.add("D2:D466", rule5)
def conditionalColoring(ws): # differential style, conditional color formatting. dxf = DifferentialStyle(fill=pinkFill) r = Rule(type="expression", dxf=dxf, stopIfTrue=True) r.formula = ['$Y3 = "重症"'] ws.conditional_formatting.add(f"A3:AA{ws.max_row}", r) return(ws)
def CellIsRule(operator=None, formula=None, stopIfTrue=None, font=None, border=None, fill=None): """ Conditional formatting rule based on cell contents. """ # Excel doesn't use >, >=, etc, but allow for ease of python development expand = { ">": "greaterThan", ">=": "greaterThanOrEqual", "<": "lessThan", "<=": "lessThanOrEqual", "=": "equal", "==": "equal", "!=": "notEqual" } operator = expand.get(operator, operator) rule = Rule(type='cellIs', operator=operator, formula=formula, stopIfTrue=stopIfTrue) rule.dxf = DifferentialStyle(font=font, border=border, fill=fill) return rule
def calc_profit(): # loading excel file to read and write to wb = openpyxl.load_workbook(filename="product_list.xlsx") sheet = wb.active # Creating and styling Profit Column Heading sheet.cell(row=1, column=8).value = "Profit" sheet.cell(row=1, column=8).font = Font(bold=True, name="Helvetica", size=15) sheet.cell(row=1, column=8).fill = PatternFill(patternType="solid", fgColor="00e5ee") sheet.cell(row=1, column=8).alignment = Alignment(horizontal="center") # Calculating Profit row_index=2 for cell in sheet["H"][1:]: cell.value = f"=G{row_index}-F{row_index}" row_index = row_index + 1 cell.alignment = Alignment(horizontal="center") # Adding Conditional Formatting on entire row yellow_background = PatternFill(bgColor="e9ee9e") diff_style = DifferentialStyle(fill=yellow_background) rule = Rule(type="expression", dxf=diff_style) rule.formula = ["$H1<0"] sheet.conditional_formatting.add(f"A1:H{sheet.max_column}", rule) # Same formula as but for a cell in a column # sheet.conditional_formatting.add(f'H2:H{sheet.max_column}', CellIsRule(operator='lessThan', formula=['0'], fill=yellow_background)) wb.save("product_list.xlsx")
def conditionalColoring(ws): # differential style, conditional color formatting. dxf = DifferentialStyle(fill=darkGreyFill) r = Rule(type="expression", dxf=dxf, stopIfTrue=True) r.formula = ['$J4<>""'] ws.conditional_formatting.add(f"E4:K{ws.max_row}", r) return (ws)
def apply_formatting(output_file: str, sheetname: str) -> None: wb = openpyxl.load_workbook(output_file) ws = wb[sheetname] # Change size of the column with company names and wrap the text ws.column_dimensions["A"].width = 25 for cell in ws["A"]: cell.alignment = Alignment(wrap_text=True) # Add column filters ws.auto_filter.ref = ws.dimensions # Change format of predictions from string to percentage percentage_columns = ["E", "F", "G", "H", "I"] for col in percentage_columns: for cell in ws[col]: cell.number_format = FORMAT_PERCENTAGE_00 # Add conditional formatting for predictions percentages green_background = PatternFill(bgColor="C6EFCE") diff_style = DifferentialStyle(fill=green_background) rule = Rule(type="cellIs", operator="greaterThan", dxf=diff_style, formula=["30.00%"]) ws.conditional_formatting.add(f"F2:G{ws.max_row}", rule) # Hide the column with 5 year predictions ws.column_dimensions["I"].hidden = True wb.save(output_file)
def write_formatting(self): df = DifferentialStyle() wb = self.ws.parent for cf in self.ws.conditional_formatting: for rule in cf.rules: if rule.dxf and rule.dxf != df: rule.dxfId = wb._differential_styles.add(rule.dxf) self.xf.send(cf.to_tree())
def test_formula_rule(): from ..rule import FormulaRule from openpyxl.styles.differential import DifferentialStyle cf = FormulaRule(formula=['ISBLANK(C1)'], stopIfTrue=True) assert dict(cf) == {'priority': '0', 'stopIfTrue': '1', 'type': 'expression'} assert cf.formula == ['ISBLANK(C1)'] assert cf.dxf == DifferentialStyle()
def format_ws(ws): header = DifferentialStyle(font=Font(bold=True)) r0 = Rule(type="expression", dxf=header) last_row = ws.max_row last_column = get_column_letter(ws.max_column) ws.conditional_formatting.add(f"A1:{last_column}1", r0) create_table(ws, ws.title, f"A1:{last_column}{last_row}") alignment_wrapper(ws)
def FormulaRule(formula=None, stopIfTrue=None, font=None, border=None, fill=None): """ Conditional formatting with custom differential style """ rule = Rule(type="expression", formula=formula, stopIfTrue=stopIfTrue) rule.dxf = DifferentialStyle(font=font, border=border, fill=fill) return rule
def write_conditional_formatting(worksheet): """Write conditional formatting to xml.""" wb = worksheet.parent for cf in worksheet.conditional_formatting: for rule in cf.rules: if rule.dxf and rule.dxf != DifferentialStyle(): rule.dxfId = wb._differential_styles.add(rule.dxf) yield cf.to_tree()
def test_formatting(self, PrimedWorksheetReader): reader = PrimedWorksheetReader reader.bind_cells() ws = reader.ws reader.bind_formatting() assert ws.conditional_formatting['T1:T10'][ -1].dxf == DifferentialStyle()
def build_annex_overview(): workbook = Workbook() workbook.create_sheet('Bilagsoversigt', 0) sheet = workbook.active sheet['A1'] = 'Bilag' sheet['B1'] = 'Rapport' sheet['C1'] = 'Sideantal' sheet.column_dimensions['A'].width = 10 sheet.column_dimensions['B'].width = 80 sheet.column_dimensions['C'].width = 9 # Header layout header = NamedStyle(name="header") header.font = Font(bold=True) header_row = sheet[1] for cell in header_row: cell.style = header # Insert data from annex_list for row in range(0, len(annex_list)): sheet.cell(column=1, row=row + 2, value=annex_list[row].annex_number) sheet.cell(column=2, row=row + 2, value=annex_list[row].base_filename.replace( '.pdf', '').replace('{' + annex_list[row].annex_number + '} - ', '')) sheet.cell(column=3, row=row + 2, value=annex_list[row].num_pages) # Conditional statement - Show doublets in "bilagsnumre" red_fill = PatternFill(bgColor="FFC7CE") dxf = DifferentialStyle(fill=red_fill) duplicate_rule = Rule(type="duplicateValues", dxf=dxf, stopIfTrue=None) sheet.conditional_formatting.add(f'A1:A{len(annex_list) + 1}', duplicate_rule) # Printsettings sheet.page_setup.orientation = sheet.ORIENTATION_PORTRAIT sheet.page_setup.paperSize = sheet.PAPERSIZE_A4 sheet.sheet_properties.pageSetUpPr.fitToPage = True sheet.page_setup.fitToWidth = True sheet.page_setup.fitToHeight = False sheet.oddHeader.center.text = operation_titel sheet.oddFooter.center.text = "Side &[Page] af &N" sheet.print_area = 'A:C' # Sorter bilagskolonnen # sheet.auto_filter.add_sort_condition(f'A1:A{len(annex_list) + 1}') # sheet.auto_filter.add_sort_condition('A:A') global destination_folder annex_overview_filename = destination_folder + f'/Bilagsoversigt {datetime.now().date()}.xlsx' workbook.save(filename=annex_overview_filename)
def _get_critical_failed_style(self): ( ctc_font, ctc_border, ctc_fill, ) = self._style.get_rpm_xslx_table_critical_failed() return DifferentialStyle( font=ctc_font, border=ctc_border, fill=ctc_fill, )
def _get_important_failed_style(self): ( ipt_font, ipt_border, ipt_fill, ) = self._style.get_rpm_xslx_table_important_failed() return DifferentialStyle( font=ipt_font, border=ipt_border, fill=ipt_fill, )
def cdg_narrative_dashboard( master: Master, # milestones: MilestoneData, wb_path: Workbook ) -> Workbook: wb = load_workbook(wb_path) ws = wb.active for row_num in range(2, ws.max_row + 1): project_name = ws.cell(row=row_num, column=3).value if project_name in master.current_projects: # Group ws.cell(row=row_num, column=2).value = master.project_information[project_name]["Directorate"] # Abbreviation ws.cell(row=row_num, column=4).value = master.project_information[project_name]["Abbreviations"] # Stage bc_stage = master.master_data[0]["data"][project_name][DATA_KEY_DICT["IPDC approval point"]] ws.cell(row=row_num, column=5).value = convert_bc_stage_text(bc_stage) costs = master.master_data[0]["data"][project_name][DATA_KEY_DICT["Total Forecast"]] ws.cell(row=row_num, column=6).value = dandelion_number_text(costs) overall_dca = convert_rag_text( master.master_data[0]["data"][project_name][DATA_KEY_DICT["Departmental DCA"]] ) ws.cell(row=row_num, column=7).value = overall_dca if overall_dca == "None": ws.cell(row=row_num, column=7).value = "" sro_n = master.master_data[0]["data"][project_name]["SRO Narrative"] ws.cell(row=row_num, column=8).value = sro_n """list of columns with conditional formatting""" list_columns = ["g"] """same loop but the text is black. In addition these two loops go through the list_columns list above""" for column in list_columns: for i, dca in enumerate(rag_txt_list): text = black_text fill = fill_colour_list[i] dxf = DifferentialStyle(font=text, fill=fill) rule = Rule( type="containsText", operator="containsText", text=dca, dxf=dxf ) for_rule_formula = 'NOT(ISERROR(SEARCH("' + dca + '",' + column + "5)))" rule.formula = [for_rule_formula] ws.conditional_formatting.add(column + "5:" + column + "60", rule) for row_num in range(2, ws.max_row + 1): for col_num in range(5, ws.max_column + 1): if ws.cell(row=row_num, column=col_num).value == 0: ws.cell(row=row_num, column=col_num).value = "-" return wb
def cf_mismatches(ws, first_cell, last_cell): # Conditional formatting to highlight mismatches redFill = PatternFill(start_color='EE6666', end_color='EE6666', fill_type='solid') dxf = DifferentialStyle(fill=redFill) rule = Rule(type="expression", formula=["ISNUMBER(SEARCH(\"|\", {0}))".format(first_cell)], dxf=dxf, stopIfTrue=True) ws.conditional_formatting.add('{0}:{1}'.format(first_cell, last_cell), rule)
def cf_blanks(ws, first_cell, last_cell): # Conditional formatting to highlight blanks as light gray blankFill = PatternFill(start_color='EEEEEE', end_color='EEEEEE', fill_type='solid') dxf = DifferentialStyle(fill=blankFill) blank = Rule(type="expression", formula=["ISBLANK({0})".format(first_cell)], dxf=dxf, stopIfTrue=True) ws.conditional_formatting.add('{0}:{1}'.format(first_cell, last_cell), blank)
def _get_header_style(self): ( header_font, header_border, header_fill, ) = self._style.get_rpm_xslx_table_header() return DifferentialStyle( font=header_font, border=header_border, fill=header_fill, )
def addrulesCouleur(ws, longueur): #rule pour la liste terminé gbackground = PatternFill(start_color='00CC00', end_color='00CC00', fill_type='solid') diff = DifferentialStyle(fill=gbackground) rule4 = Rule(type="expression", dxf=diff) rule4.formula = ['$A2="Terminé !"'] ws.conditional_formatting.add("A2:F" + str(longueur + 1), rule4) #rule pour les dates inférieures redbackground = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid') diff = DifferentialStyle(fill=redbackground) #['AND(NOT(ISEMPTY($D1));($D1<TODAY()))'] rule5 = Rule(type="expression", dxf=diff, formula=['AND(NOT(ISBLANK($D2)),($D2<TODAY()))']) ws.conditional_formatting.add("D2:D466", rule5) #rule pour la liste en cours ybackground = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') diff = DifferentialStyle(fill=ybackground) rule1 = Rule(type="expression", dxf=diff, formula=['$A2="En cours"']) ws.conditional_formatting.add("A2:F" + str(longueur + 1), rule1) #rule pour la liste bolqué obackground = PatternFill(start_color='FF9933', end_color='FF9933', fill_type='solid') diff = DifferentialStyle(fill=obackground) rule2 = Rule(type="expression", dxf=diff, formula=['$A2="Bloqué"']) ws.conditional_formatting.add("A2:F" + str(longueur + 1), rule2) #rule pour la liste presque fini lgbackground = PatternFill(start_color='99FF33', end_color='99FF33', fill_type='solid') diff = DifferentialStyle(fill=lgbackground) rule3 = Rule(type="expression", dxf=diff, formula=['$A2="Presque fini"']) ws.conditional_formatting.add("A2:F" + str(longueur + 1), rule3)
def grey_conditional_formatting(ws): ''' function applies grey conditional formatting for 'Not Reporting'. :param worksheet: ws :return: cf of sheet ''' grey_text = Font(color="f0f0f0") grey_fill = PatternFill(bgColor="f0f0f0") dxf = DifferentialStyle(font=grey_text, fill=grey_fill) rule = Rule(type="containsText", operator="containsText", text="Not reporting", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("Not reporting",A1)))'] ws.conditional_formatting.add('A1:X80', rule) grey_text = Font(color="cfcfea") grey_fill = PatternFill(bgColor="cfcfea") dxf = DifferentialStyle(font=grey_text, fill=grey_fill) rule = Rule(type="containsText", operator="containsText", text="Data not collected", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("Data not collected",A1)))'] ws.conditional_formatting.add('A1:X80', rule) return ws
def add_cond_text_format_exact(ws, text, color, start, end): ''' Takes: - ws - worksheet object - text - as string - color - hex color - start cell+col string - end cell+col string ''' fill = PatternFill(bgColor=color) dxf = DifferentialStyle(fill=fill) rule = Rule(type="cellIs", operator="equal", dxf=dxf) rule.formula = ['"{}"'.format(text)] ws.conditional_formatting.add(start + ":" + end, rule)
def write_conditional_formatting(worksheet): """Write conditional formatting to xml.""" wb = worksheet.parent for range_string, rules in iteritems(worksheet.conditional_formatting.cf_rules): cf = Element('conditionalFormatting', {'sqref': range_string}) for rule in rules: if rule.dxf is not None: if rule.dxf != DifferentialStyle(): rule.dxfId = len(wb._differential_styles) wb._differential_styles.append(rule.dxf) cf.append(rule.to_tree()) yield cf
def conditional_formatting(ws, list_columns, list_conditional_text, list_text_colours, list_background_colours, row_start, row_end): for column in list_columns: for i, txt in enumerate(list_conditional_text): text = list_text_colours[i] fill = list_background_colours[i] dxf = DifferentialStyle(font=text, fill=fill) rule = Rule(type="containsText", operator="containsText", text=txt, dxf=dxf) for_rule_formula = 'NOT(ISERROR(SEARCH("' + txt + '",' + column + '1)))' rule.formula = [for_rule_formula] ws.conditional_formatting.add(column + row_start + ':' + column + row_end, rule) return ws
def cf_highlight_good_row(ws, first_cell, last_cell, start_row, end_row): # Conditional formatting to highlight rows with no mismatches greenFill = PatternFill(start_color='66EE66', end_color='66EE66', fill_type='solid') dxf = DifferentialStyle(fill=greenFill) rule = Rule(type="expression", formula=[ "COUNT(SEARCH(\"|\",${0}:${1}))<1".format( start_row, end_row) ], dxf=dxf, stopIfTrue=True) ws.conditional_formatting.add('{0}:{1}'.format(first_cell, last_cell), rule)