Ejemplo n.º 1
0
    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
Ejemplo n.º 2
0
    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 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)
Ejemplo n.º 4
0
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))
Ejemplo n.º 5
0
    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
Ejemplo n.º 6
0
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)
Ejemplo n.º 7
0
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)
Ejemplo n.º 8
0
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 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)
Ejemplo n.º 10
0
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")
Ejemplo n.º 11
0
 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)
Ejemplo n.º 12
0
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)
Ejemplo n.º 13
0
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)
Ejemplo n.º 14
0
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()
Ejemplo n.º 15
0
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
Ejemplo n.º 16
0
 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())
Ejemplo n.º 17
0
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()
Ejemplo n.º 18
0
    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()
Ejemplo n.º 19
0
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)
Ejemplo n.º 20
0
 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,
     )
Ejemplo n.º 21
0
 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,
     )
Ejemplo n.º 22
0
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
Ejemplo n.º 23
0
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)
Ejemplo n.º 24
0
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)
Ejemplo n.º 25
0
    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,
        )
Ejemplo n.º 26
0
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)
Ejemplo n.º 27
0
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
Ejemplo n.º 28
0
 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)
Ejemplo n.º 29
0
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
Ejemplo n.º 30
0
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
Ejemplo n.º 31
0
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)
Ejemplo n.º 32
0
 def parse_dxfs(self):
     """Read in the dxfs effects - used by conditional formatting."""
     for node in self.root.findall("{%s}dxfs/{%s}dxf" % (SHEET_MAIN_NS, SHEET_MAIN_NS) ):
         self.differential_styles.append(DifferentialStyle.from_tree(node))