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 _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)
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 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 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 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)
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, 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 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 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 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 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_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 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)
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))