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 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 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=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 setProcessedConditionalFormatting(wsProc):
for key,list in clusters.items():
for clusterMotif in list:
dxf = DifferentialStyle(fill=conditionalFill[key])
rule = Rule(type="containsText", operator="containsText", text=clusterMotif, dxf=dxf) #blue
rule.formula = ['NOT(ISERROR(SEARCH("' + clusterMotif + '",D1)))']
wsProc.conditional_formatting.add('A:A', rule)
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 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 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 add_cond_text_format_contains(ws, text, color, start, end):
'''
Takes:
- ws - worksheet object
- text - as string
- color - hex color
- start cell+col string
- end cell+col string
'''
print("using non-exact cond formatting")
fill = PatternFill(bgColor=color)
dxf = DifferentialStyle(fill=fill)
rule = Rule(type="containsText",
operator="containsText",
text=text,
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("{}",A2)))'.format(text)]
ws.conditional_formatting.add(start + ":" + end, rule)
def conditional_formatting_old(ws):
'''
function applies conditional formatting for RAG colors... in development.
:param ws: worksheet
:return: worksheet with conditional formatting
'''
for column in ws.max_column:
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)
return ws
def loadTxtFile(self):
workBook = Workbook()
workSheet = workBook.active
Months = [
"", "January", "February", "March", "April", "May", "June", "July",
"August", "September", "October", "November", "December"
]
workSheet.title = Months[int(self.MonthString.get())]
workSheet.append(
["Date", "Cash", "EBT", "Credit", "Taxable", "Tax", "Total", "MP"])
fileTo = self.PathOutString.get() + "Data" + self.YearString.get(
) + self.MonthString.get() + ".xlsx"
# Open each file that matches the month from days 1-31 and push to Excel
# range is 33 because Excel arrays starts at 1 and not 0
for i in range(1, 33):
day = ""
if (i < 10):
day = "0" + str(i)
else:
day = str(i)
filePath = self.PathInString.get() + "Data" + self.YearString.get(
) + self.MonthString.get() + day + ".txt"
#print(filePath)
if (os.path.isfile(filePath)):
f = open(filePath)
fList = f.read().split("{", 1)[1].split("}", 1)[0].split(",")
f.close()
arrayList = []
dateIndex = True
for x in fList:
#print( (x.strip("\n").split(":"))[1] )
value = (x.strip("\n").split(":"))[1]
#If date becomes a float Excel will "Power" it automatically
#values won't =sum() if they are strings
if (dateIndex):
dateIndex = False
value = "{}/{}/{}".format(value[0:4], value[4:6],
value[6:8])
else:
value = float(value)
arrayList.append(value)
workSheet.append(arrayList)
else:
print("File Not Found:" + filePath)
workSheet.append(["", "", "", "", "", "", "",
""]) #push in empty slot of missing dates
workSheet.append([
"Totals:", "=Sum(B2:B31)", "=Sum(C2:C31)", "=Sum(D2:D31)",
"=Sum(E2:E31)", "=Sum(F2:F31)", "=Sum(G2:G31)", "=Sum(H2:H31)"
])
red_fill = PatternFill(bgColor="FFC7CE")
dxf = DifferentialStyle(fill=red_fill)
r = Rule(type="expression", dxf=dxf, stopIfTrue=True)
r.formula = ['NOT(ISERROR(SEARCH("highlight",A1)))']
workSheet.conditional_formatting.add("A1:C10", r)
try:
workBook.save(fileTo)
except:
messagebox.showerror("Task Failed",
"The Excel file must be closed:\n" + fileTo)
else:
messagebox.showinfo("Task Completed",
"New Excel File Created:\n" + fileTo)
def cdg_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)
# try:
# bc_stage_lst_qrt = master.master_data[1].data[project_name][
# "IPDC approval point"
# ]
# if bc_stage != bc_stage_lst_qrt:
# # ws.cell(row=row_num, column=4).font = Font(
# # name="Arial", size=10, color="00fc2525"
# # )
# ws.cell(row=row_num, column=3).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
# except KeyError:
# pass
# Total Costs
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, none_handle="none")
# try:
# wlc_lst_quarter = master.master_data[1].data[project_name][
# "Total Forecast"
# ]
# diff_lst_qrt = wlc_now - wlc_lst_quarter
# if float(diff_lst_qrt) > 0.49 or float(diff_lst_qrt) < -0.49:
# # ws.cell(row=row_num, column=7).value = diff_lst_qrt
# ws.cell(row=row_num, column=6).value = diff_lst_qrt
# else:
# # ws.cell(row=row_num, column=7).value = "-"
# ws.cell(row=row_num, column=6).value = "-"
#
# try:
# percentage_change = ((wlc_now - wlc_lst_quarter) / wlc_now) * 100
# if percentage_change > 5 or percentage_change < -5:
# # ws.cell(row=row_num, column=7).font = Font(
# # name="Arial", size=10, color="00fc2525"
# # )
# ws.cell(row=row_num, column=6).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
# except ZeroDivisionError:
# pass
# except KeyError:
# ws.cell(row=row_num, column=6).value = "-"
# Total Income
income = master.master_data[0]["data"][project_name]["Total Income"]
ws.cell(row=row_num, column=7).value = dandelion_number_text(income, none_handle="none")
# Total Benefits
benefits = master.master_data[0]["data"][project_name]["Total Benefits"]
ws.cell(row=row_num, column=8).value = dandelion_number_text(benefits, none_handle="none")
# VfM Category
vfm = master.master_data[0]["data"][project_name]["VfM Category"]
ws.cell(row=row_num, column=9).value = vfm
# """WLC variance against baseline quarter"""
# bl = master.bl_index["ipdc_costs"][project_name][2]
# wlc_baseline = master.master_data[bl].data[project_name]["Total Forecast"]
# try:
# diff_bl = wlc_now - wlc_baseline
# if float(diff_bl) > 0.49 or float(diff_bl) < -0.49:
# # ws.cell(row=row_num, column=8).value = diff_bl
# ws.cell(row=row_num, column=7).value = diff_bl
# else:
# # ws.cell(row=row_num, column=8).value = "-"
# ws.cell(row=row_num, column=7).value = "-"
# except TypeError: # exception is here as some projects e.g. Hs2 phase 2b have (real) written into historical totals
# pass
# try:
# percentage_change = ((wlc_now - wlc_baseline) / wlc_now) * 100
# if percentage_change > 5 or percentage_change < -5:
# # ws.cell(row=row_num, column=8).font = Font(
# # name="Arial", size=10, color="00fc2525"
# # )
# ws.cell(row=row_num, column=7).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
#
# except (
# ZeroDivisionError,
# TypeError,
# ): # zerodivision error obvious, type error handling as above
# pass
# """vfm category now"""
# if (
# master.master_data[0].data[project_name]["VfM Category single entry"]
# is None
# ):
# vfm_cat = (
# str(
# master.master_data[0].data[project_name][
# "VfM Category lower range"
# ]
# )
# + " - "
# + str(
# master.master_data[0].data[project_name][
# "VfM Category upper range"
# ]
# )
# )
# # ws.cell(row=row_num, column=10).value = vfm_cat
# ws.cell(row=row_num, column=8).value = vfm_cat
#
# else:
# vfm_cat = master.master_data[0].data[project_name][
# "VfM Category single entry"
# ]
# # ws.cell(row=row_num, column=10).value = vfm_cat
# ws.cell(row=row_num, column=8).value = vfm_cat
#
# """vfm category baseline"""
# bl_i = master.bl_index["ipdc_benefits"][project_name][2]
# try:
# if (
# master.master_data[bl_i].data[project_name][
# "VfM Category single entry"
# ]
# is None
# ):
# vfm_cat_baseline = (
# str(
# master.master_data[bl_i].data[project_name][
# "VfM Category lower range"
# ]
# )
# + " - "
# + str(
# master.master_data[bl_i].data[project_name][
# "VfM Category upper range"
# ]
# )
# )
# # ws.cell(row=row_num, column=11).value = vfm_cat_baseline
# else:
# vfm_cat_baseline = master.master_data[bl_i].data[project_name][
# "VfM Category single entry"
# ]
# # ws.cell(row=row_num, column=11).value = vfm_cat_baseline
#
# except KeyError:
# try:
# vfm_cat_baseline = master.master_data[bl_i].data[project_name][
# "VfM Category single entry"
# ]
# # ws.cell(row=row_num, column=11).value = vfm_cat_baseline
# except KeyError:
# vfm_cat_baseline = master.master_data[bl_i].data[project_name][
# "VfM Category"
# ]
# # ws.cell(row=row_num, column=11).value = vfm_cat_baseline
#
# if vfm_cat != vfm_cat_baseline:
# if vfm_cat_baseline is None:
# pass
# else:
# ws.cell(row=row_num, column=8).font = Font(
# name="Arial", size=8, color="00fc2525"
# )
#
# abb = master.abbreviations[project_name]["abb"]
# current = get_milestone_date(
# abb, milestones.milestone_dict, "current", " Full Operations"
# )
# last_quarter = get_milestone_date(
# abb, milestones.milestone_dict, "last", " Full Operations"
# )
# bl = get_milestone_date(
# abb, milestones.milestone_dict, "bl_one", " Full Operations"
# )
# ws.cell(row=row_num, column=9).value = current
# if current is not None and current < IPDC_DATE:
# ws.cell(row=row_num, column=9).value = "Completed"
# try:
# last_change = (current - last_quarter).days
# ws.cell(row=row_num, column=10).value = plus_minus_days(last_change)
# if last_change is not None and last_change > 46:
# ws.cell(row=row_num, column=10).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
# except TypeError:
# pass
# try:
# bl_change = (current - bl).days
# ws.cell(row=row_num, column=11).value = plus_minus_days(bl_change)
# if bl_change is not None and bl_change > 85:
# ws.cell(row=row_num, column=11).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
# except TypeError:
# pass
# last at/next at ipdc information removed
# try:
# ws.cell(row=row_num, column=12).value = concatenate_dates(
# master.master_data[0].data[project_name]["Last time at BICC"],
# IPDC_DATE,
# )
# ws.cell(row=row_num, column=13).value = concatenate_dates(
# master.master_data[0].data[project_name]["Next at BICC"],
# IPDC_DATE,
# )
# except (KeyError, TypeError):
# print(
# project_name
# + " last at / next at ipdc data could not be calculated. Check data."
# )
# DCA ratings
overall_dca = convert_rag_text(
master.master_data[0]["data"][project_name][DATA_KEY_DICT["Departmental DCA"]]
)
ws.cell(row=row_num, column=10).value = overall_dca
if overall_dca == "None":
ws.cell(row=row_num, column=10).value = ""
conf_list = ["Costs Confidence", "Schedule Confidence", "Benefits Confidence"]
for i, key in enumerate(conf_list):
dca = convert_rag_text(
master.master_data[0]["data"][project_name][key]
)
ws.cell(row=row_num, column=11+i).value = dca
risk_list = [
"Benefits",
"Capability",
"Cost",
"Objectives",
"Purpose",
"Schedule",
"Sponsorship",
"Stakeholders",
]
for i, key in enumerate(risk_list):
risk = master.master_data[0]["data"][project_name][key]
if risk == "YES":
ws.cell(row=row_num, column=14+i).value = risk
# """DCA rating - this quarter"""
# ws.cell(row=row_num, column=17).value = convert_rag_text(
# master.master_data[0].data[project_name]["Departmental DCA"]
# )
# """DCA rating - last qrt"""
# try:
# ws.cell(row=row_num, column=19).value = convert_rag_text(
# master.master_data[1].data[project_name]["Departmental DCA"]
# )
# except KeyError:
# ws.cell(row=row_num, column=19).value = ""
# """DCA rating - 2 qrts ago"""
# try:
# ws.cell(row=row_num, column=20).value = convert_rag_text(
# master.master_data[2].data[project_name]["Departmental DCA"]
# )
# except (KeyError, IndexError):
# ws.cell(row=row_num, column=20).value = ""
# """DCA rating - 3 qrts ago"""
# try:
# ws.cell(row=row_num, column=21).value = convert_rag_text(
# master.master_data[3].data[project_name]["Departmental DCA"]
# )
# except (KeyError, IndexError):
# ws.cell(row=row_num, column=21).value = ""
# """DCA rating - baseline"""
# bl_i = master.bl_index["ipdc_costs"][project_name][2]
# ws.cell(row=row_num, column=23).value = convert_rag_text(
# master.master_data[bl_i].data[project_name]["Departmental DCA"]
# )
def sro_narrative():
sro_n = master.master_data[0].data[project_name]["SRO Narrative"]
ws.cell(row=row_num, column=22).value = sro_n
# sro_narrative()
"""list of columns with conditional formatting"""
list_columns = ["j", "k", "l", "m"]
"""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 cdg_overall_dashboard(master: Master, wb: Workbook) -> Workbook:
wb = load_workbook(wb)
ws = wb.worksheets[0]
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=2).value
if project_name in master.current_projects:
"""BC Stage"""
bc_stage = master.master_data[0].data[project_name]["CDG approval point"]
# ws.cell(row=row_num, column=4).value = convert_bc_stage_text(bc_stage)
ws.cell(row=row_num, column=3).value = convert_bc_stage_text(bc_stage)
try:
bc_stage_lst_qrt = master.master_data[1].data[project_name][
"CDG approval point"
]
if bc_stage != bc_stage_lst_qrt:
# ws.cell(row=row_num, column=4).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
ws.cell(row=row_num, column=3).font = Font(
name="Arial", size=10, color="00fc2525"
)
except (KeyError, IndexError):
pass
"""planning stage"""
plan_stage = master.master_data[0].data[project_name]["Project stage"]
# ws.cell(row=row_num, column=5).value = plan_stage
ws.cell(row=row_num, column=4).value = plan_stage
try:
plan_stage_lst_qrt = master.master_data[1].data[project_name][
"Project stage"
]
if plan_stage != plan_stage_lst_qrt:
# ws.cell(row=row_num, column=5).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
ws.cell(row=row_num, column=4).font = Font(
name="Arial", size=10, color="00fc2525"
)
except (KeyError, IndexError):
pass
"""Total WLC"""
wlc_now = master.master_data[0].data[project_name]["Total Forecast"]
# ws.cell(row=row_num, column=6).value = wlc_now
ws.cell(row=row_num, column=5).value = wlc_now
"""WLC variance against lst quarter"""
try:
wlc_lst_quarter = master.master_data[1].data[project_name][
"Total Forecast"
]
diff_lst_qrt = wlc_now - wlc_lst_quarter
if float(diff_lst_qrt) > 0.49 or float(diff_lst_qrt) < -0.49:
# ws.cell(row=row_num, column=7).value = diff_lst_qrt
ws.cell(row=row_num, column=6).value = diff_lst_qrt
else:
# ws.cell(row=row_num, column=7).value = "-"
ws.cell(row=row_num, column=6).value = "-"
try:
percentage_change = ((wlc_now - wlc_lst_quarter) / wlc_now) * 100
if percentage_change > 5 or percentage_change < -5:
# ws.cell(row=row_num, column=7).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
ws.cell(row=row_num, column=6).font = Font(
name="Arial", size=10, color="00fc2525"
)
except ZeroDivisionError:
pass
except (KeyError, IndexError):
ws.cell(row=row_num, column=6).value = "-"
"""WLC variance against baseline quarter"""
bl = master.bl_index["quarter"][project_name][2]
wlc_baseline = master.master_data[bl].data[project_name]["Total Forecast"]
try:
diff_bl = wlc_now - wlc_baseline
if float(diff_bl) > 0.49 or float(diff_bl) < -0.49:
# ws.cell(row=row_num, column=8).value = diff_bl
ws.cell(row=row_num, column=7).value = diff_bl
else:
# ws.cell(row=row_num, column=8).value = "-"
ws.cell(row=row_num, column=7).value = "-"
except TypeError: # exception is here as some projects e.g. Hs2 phase 2b have (real) written into historical totals
pass
try:
percentage_change = ((wlc_now - wlc_baseline) / wlc_now) * 100
if percentage_change > 5 or percentage_change < -5:
# ws.cell(row=row_num, column=8).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
ws.cell(row=row_num, column=7).font = Font(
name="Arial", size=10, color="00fc2525"
)
except (
ZeroDivisionError,
TypeError,
): # zerodivision error obvious, type error handling as above
pass
"""vfm category now"""
vfm_cat = master.master_data[0].data[project_name][
"VfM Category single entry"
]
# if (
# master.master_data[0].data[project_name]["VfM Category single entry"]
# is None
# ):
# vfm_cat = (
# str(
# master.master_data[0].data[project_name][
# "VfM Category lower range"
# ]
# )
# + " - "
# + str(
# master.master_data[0].data[project_name][
# "VfM Category upper range"
# ]
# )
# )
# # ws.cell(row=row_num, column=10).value = vfm_cat
# ws.cell(row=row_num, column=8).value = vfm_cat
#
# else:
# vfm_cat = master.master_data[0].data[project_name][
# "VfM Category single entry"
# ]
# # ws.cell(row=row_num, column=10).value = vfm_cat
ws.cell(row=row_num, column=8).value = vfm_cat
"""vfm category baseline"""
bl_i = master.bl_index["quarter"][project_name][2]
vfm_cat_baseline = master.master_data[bl_i].data[project_name][
"VfM Category single entry"
]
# try:
# if (
# master.master_data[bl_i].data[project_name][
# "VfM Category single entry"
# ]
# is None
# ):
# vfm_cat_baseline = (
# str(
# master.master_data[bl_i].data[project_name][
# "VfM Category lower range"
# ]
# )
# + " - "
# + str(
# master.master_data[bl_i].data[project_name][
# "VfM Category upper range"
# ]
# )
# )
# # ws.cell(row=row_num, column=11).value = vfm_cat_baseline
# else:
# vfm_cat_baseline = master.master_data[bl_i].data[project_name][
# "VfM Category single entry"
# ]
# # ws.cell(row=row_num, column=11).value = vfm_cat_baseline
# except KeyError:
# try:
# vfm_cat_baseline = master.master_data[bl_i].data[project_name][
# "VfM Category single entry"
# ]
# # ws.cell(row=row_num, column=11).value = vfm_cat_baseline
# except KeyError:
# vfm_cat_baseline = master.master_data[bl_i].data[project_name][
# "VfM Category"
# ]
# # ws.cell(row=row_num, column=11).value = vfm_cat_baseline
if vfm_cat != vfm_cat_baseline:
if vfm_cat_baseline is None:
pass
else:
ws.cell(row=row_num, column=8).font = Font(
name="Arial", size=8, color="00fc2525"
)
current = master.master_data[0].data[project_name]["Project End Date"]
try:
last_quarter = master.master_data[1].data[project_name][
"Full Operations"
]
except IndexError:
pass
bl = master.master_data[bl_i].data[project_name]["Project End Date"]
#
# abb = master.abbreviations[project_name]["abb"]
# current = get_milestone_date(
# abb, milestones.milestone_dict, "current", " Full Operations"
# )
# last_quarter = get_milestone_date(
# abb, milestones.milestone_dict, "last", " Full Operations"
# )
# bl = get_milestone_date(
# abb, milestones.milestone_dict, "bl_one", " Full Operations"
# )
ws.cell(row=row_num, column=9).value = current
if current is not None and current < DCG_DATE:
ws.cell(row=row_num, column=9).value = "Completed"
try:
last_change = (current - last_quarter).days
if last_change == 0:
ws.cell(row=row_num, column=10).value = "-"
else:
ws.cell(row=row_num, column=10).value = plus_minus_days(last_change)
if last_change is not None and last_change > 46:
ws.cell(row=row_num, column=10).font = Font(
name="Arial", size=10, color="00fc2525"
)
except (TypeError, UnboundLocalError):
pass
try:
bl_change = (current - bl).days
if bl_change == 0:
ws.cell(row=row_num, column=11).value = "-"
else:
ws.cell(row=row_num, column=11).value = plus_minus_days(bl_change)
if bl_change is not None and bl_change > 85:
ws.cell(row=row_num, column=11).font = Font(
name="Arial", size=10, color="00fc2525"
)
except TypeError:
pass
# last at/next at cdg information removed
try:
ws.cell(row=row_num, column=12).value = concatenate_dates(
master.master_data[0].data[project_name]["Last date at CDG"],
DCG_DATE,
)
ws.cell(row=row_num, column=13).value = concatenate_dates(
master.master_data[0].data[project_name]["Next date at CDG"],
DCG_DATE,
)
except (KeyError, TypeError):
print(
project_name
+ " last at / next at ipdc data could not be calculated. Check data."
)
# """IPA DCA rating"""
# ipa_dca = convert_rag_text(
# master.master_data[0].data[project_name]["GMPP - IPA DCA"]
# )
# ws.cell(row=row_num, column=15).value = ipa_dca
# if ipa_dca == "None":
# ws.cell(row=row_num, column=15).value = ""
"""DCA rating - this quarter"""
ws.cell(row=row_num, column=17).value = convert_rag_text(
master.master_data[0].data[project_name]["Departmental DCA"]
)
"""DCA rating - last qrt"""
try:
ws.cell(row=row_num, column=19).value = convert_rag_text(
master.master_data[1].data[project_name]["Departmental DCA"]
)
except (KeyError, IndexError):
ws.cell(row=row_num, column=19).value = ""
"""DCA rating - 2 qrts ago"""
try:
ws.cell(row=row_num, column=20).value = convert_rag_text(
master.master_data[2].data[project_name]["Departmental DCA"]
)
except (KeyError, IndexError):
ws.cell(row=row_num, column=20).value = ""
"""DCA rating - 3 qrts ago"""
try:
ws.cell(row=row_num, column=21).value = convert_rag_text(
master.master_data[3].data[project_name]["Departmental DCA"]
)
except (KeyError, IndexError):
ws.cell(row=row_num, column=21).value = ""
"""DCA rating - baseline"""
bl_i = master.bl_index["quarter"][project_name][2]
ws.cell(row=row_num, column=23).value = convert_rag_text(
master.master_data[bl_i].data[project_name]["Departmental DCA"]
)
"""list of columns with conditional formatting"""
list_columns = ["o", "q", "s", "t", "u", "w"]
"""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
for x in range(2,ws.max_row+1):
if y !=1:
ws.cell(row=x,column=y).alignment = Alignment(horizontal='right')
#apply conditional formatting to rpn cells
last_cell = f'C{ws.max_row}'
redFill = PatternFill(bgColor = 'ffcccb')
redText = Font(color = '650000')
dxfredFill = DifferentialStyle(fill=redFill, font = redText)
greenText = Font(color="006100")
greenFill = PatternFill(bgColor="C6EFCE")
dxfgreen = DifferentialStyle(font = greenText, fill = greenFill)
yellowFill = PatternFill(bgColor='FFFF99')
yellowText = Font(color = '666600')
dxfyellowFill = DifferentialStyle(fill=yellowFill, font = yellowText)
rRed = Rule(type="expression", dxf=dxfredFill, stopIfTrue=True)
rRed.formula = [f'B2>={upper_threshold}']
ws.conditional_formatting.add(f"B2:{last_cell}", rRed)
rYellow = Rule(type="expression", dxf=dxfyellowFill, stopIfTrue=True)
rYellow.formula = [f'B2>={middle_threshold}']
ws.conditional_formatting.add(f"B2:{last_cell}", rYellow)
rGreen = Rule(type="expression", dxf=dxfgreen, stopIfTrue=True)
rGreen.formula = ['B2>=0']
ws.conditional_formatting.add(f"B2:{last_cell}", rGreen)
#create output report
report.save(filename = 'report.xlsx')
def formatQueryProfilePlan(ws1):
dxf = DifferentialStyle(fill=yellowFill)
rule = Rule(type="containsText",
operator="containsText",
text="> JOIN",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("> JOIN",A19)))']
ws1.conditional_formatting.add('A19:ZZ1499', rule)
dxf = DifferentialStyle(fill=lightblueFill)
rule = Rule(type="containsText",
operator="containsText",
text="Filter",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Filter",A19)))']
ws1.conditional_formatting.add('A19:ZZ1499', rule)
dxf = DifferentialStyle(fill=orangeFill)
rule = Rule(type="containsText",
operator="containsText",
text="Join Cond",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Join Cond",A19)))']
ws1.conditional_formatting.add('A19:ZZ1499', rule)
dxf = DifferentialStyle(fill=pinkFill)
rule = Rule(type="containsText",
operator="containsText",
text="Projection:",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Projection:",A19)))']
ws1.conditional_formatting.add('A19:ZZ1499', rule)
dxf = DifferentialStyle(fill=lightgreenFill)
rule = Rule(type="containsText",
operator="containsText",
text="SELECT",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("SELECT",A19)))']
ws1.conditional_formatting.add('A19:ZZ1499', rule)
dxf = DifferentialStyle(fill=darkredFill)
rule = Rule(type="containsText",
operator="containsText",
text="SORT [",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("SORT [",A19)))']
ws1.conditional_formatting.add('A19:ZZ1499', rule)
dxf = DifferentialStyle(fill=redFill)
rule = Rule(type="containsText",
operator="containsText",
text="> GROUPBY",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("> GROUPBY",A19)))']
ws1.conditional_formatting.add('A19:ZZ1499', rule)
dxf = DifferentialStyle(fill=blueFill)
rule = Rule(type="containsText",
operator="containsText",
text="Outer -> STORAGE",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Outer -> STORAGE",A19)))']
ws1.conditional_formatting.add('A19:ZZ1499', rule)
dxf = DifferentialStyle(fill=greenFill)
rule = Rule(type="containsText",
operator="containsText",
text="Inner -> STORAGE",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Inner -> STORAGE",A19)))']
ws1.conditional_formatting.add('A19:ZZ1499', rule)
# end
return ws1
)
# Add a conditional formatting based on a cell comparison
# addCellIs(range_string, operator, formula, stopIfTrue, wb, font, border, fill)
# Format if cell is less than 'formula'
ws.conditional_formatting.add('C2:C10',
CellIsRule(operator='lessThan', formula=['C$1'], stopIfTrue=True, fill=redFill))
# Format if cell is between 'formula'
ws.conditional_formatting.add('D2:D10',
CellIsRule(operator='between', formula=['1','5'], stopIfTrue=True, fill=redFill))
# Format using a formula
ws.conditional_formatting.add('E1:E10',
FormulaRule(formula=['ISBLANK(E1)'], stopIfTrue=True, fill=redFill))
# Aside from the 2-color and 3-color scales, format rules take fonts, borders and fills for styling:
myFont = Font()
myBorder = Border()
ws.conditional_formatting.add('E1:E10',
FormulaRule(formula=['E1=0'], font=myFont, border=myBorder, fill=redFill))
# Highlight cells that contain particular text by using a special formula
red_text = Font(color="9C0006")
red_fill = PatternFill(bgColor="FFC7CE")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText", operator="containsText", text="highlight", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("highlight",A1)))']
ws.conditional_formatting.add('A1:F40', rule)
wb.save("test.xlsx")
def build_workflow_task_sheet(_wb, _modules, user_data=None):
""" Builds the workflows cover sheet with a table containing available tasks
based on the project manifest loaded from schema.yml.
:param _wb: (object) An openpyxl workbook object
:param _modules: (dict) Dictionary describing the project modules loaded from schema.yml
:param user_data: (dict) In the case that there is existing data to preserve we can provide the existing table data
:returns: an updated openpyxl workbook object """
_manifest = package_tools.load_install_manifest()
from dna_workflows import wf_engine
wf_doc = wf_engine.get_module_definition()
_ws = _wb.create_sheet("workflows", 0)
_ws.sheet_properties.tabColor = "0080FF"
for _package_name, _package_meta in _manifest['manifest'].items():
_module_list = []
if _package_meta['type'] == 'module':
_module_list.append(_package_name)
_schema_suffix = _package_name
elif _package_meta['type'] == 'bundle':
_schema_suffix = 'bundle.{}'.format(_package_name)
for _bundle_module in _package_meta['provides']:
_module = '{}.{}'.format(_package_name, _bundle_module)
_module_list.append(_module)
# Build a list of modules and tasks based on the manifest
methods = []
for _module in _module_list:
module_doc = package_tools.get_module_doc(_module)
for m in module_doc['module']['methods']:
methods.append(m)
_table_name = 'workflow.{}'.format(_schema_suffix)
if user_data is not None:
user_methods = get_data_from_schema(_table_name, user_data)
for _m in methods:
for row in user_methods:
if _m['module'] == row['module'] and _m['task'] == row[
'task']:
_m['status'] = row['status']
wf_schema = wf_doc['module']['schemas']['workflow']
sdtables.add_schema_table_to_worksheet(_ws,
_table_name,
wf_schema,
data=methods,
table_style='TableStyleLight14')
# Add conditional formatting to workflow worksheet
for table in _ws.tables.values():
if _table_name == table.name:
_tdef = table.ref
red_fill = PatternFill(bgColor="9da19e")
dxf = DifferentialStyle(fill=red_fill)
r = Rule(type="expression", dxf=dxf, stopIfTrue=True)
_formula = '${}="disabled"'.format(_tdef.split(':')[0])
r.formula = [_formula]
_ws.conditional_formatting.add(_tdef, r)
return _wb
from openpyxl.formatting.rule import ColorScaleRule
print(sheet.max_row)
print(sheet.max_column)
for row in sheet["L2:N101"]:
for cell in row:
cell.number_format = "#,##0"
# work_book.save("sales_basic_conditional.xlsx")
yellow_background = PatternFill(bgColor="00FFFF00")
diff_style = DifferentialStyle(fill=yellow_background)
rule = Rule(type="expression", dxf=diff_style)
rule.formula = ["$M1<70000"]
# rule1 = Rule(type="expression", dxf=diff_style)
# rule.formula = ["$M1<70000"]
print(sheet.calculate_dimension())
sheet.conditional_formatting.add(sheet.calculate_dimension(), rule)
# work_book.save("sales_basic_conditional.xlsx")
# color_scale_rule = ColorScaleRule(start_type="min", start_color="00FFFF00", end_type="max", end_color="00ff0000")
work_book = openpyxl.load_workbook("sales_record.xlsx")
sheet = work_book.active
for row in sheet["K2:N101"]:
def add_template(xl_ws):
# Text and formulas
xl_ws['A1'] = 'Result'
xl_ws['B1'] = 'Team'
xl_ws['C1'] = 'K/A/D'
xl_ws['D1'] = 'K/R'
xl_ws['E1'] = 'K/D'
xl_ws['F1'] = 'HS rate'
xl_ws['G1'] = 'Score'
xl_ws['H1'] = 'Map'
xl_ws['I1'] = 'Date'
xl_ws['J1'] = 'ELO diff.'
xl_ws['K1'] = 'ELO aft. match'
xl_ws['N2'] = 'Total games'
xl_ws['N3'] = '=COUNTA(A:A)-1'
xl_ws['O2'] = 'Win rate'
xl_ws['O3'] = '=COUNTIF(A:A,"Win")/N3'
xl_ws['P2'] = 'Average K/D'
xl_ws['P3'] = '=AVERAGE(E:E)'
xl_ws['Q2'] = 'Av. HS rate'
xl_ws['Q3'] = '=AVERAGE(F:F)'
xl_ws['N5'] = 'Map'
xl_ws['N6'] = 'Map %'
xl_ws['N7'] = 'Win rate %'
xl_ws['O5'] = 'de_mirage'
xl_ws['P5'] = 'de_inferno'
xl_ws['Q5'] = 'de_dust2'
xl_ws['R5'] = 'de_overpass'
xl_ws['S5'] = 'de_nuke'
xl_ws['T5'] = 'de_vertigo'
xl_ws['U5'] = 'de_train'
for i in range(15, 22):
col_letter = gcl(i)
xl_ws.cell(row=6, column=i).value = f'=COUNTIF(H:H,{col_letter}5)/N3'
xl_ws.cell(row=7, column=i).value = f'=IF(COUNTIF(H:H,{col_letter}5)=0,0,' \
f'COUNTIFS(H:H,"="&{col_letter}5,A:A,"=Win")/COUNTIF(H:H,{col_letter}5))'
# Conditional formatting
win_rule = Rule(
type="containsText",
operator="containsText",
text="Win",
dxf=DifferentialStyle(
fill=PatternFill(patternType='solid', bgColor=win)))
win_rule.formula = ['NOT(ISERROR(SEARCH("Win",A1)))']
xl_ws.conditional_formatting.add('A1:A1048576', win_rule)
lose_rule = Rule(
type="containsText",
operator="containsText",
text="Lose",
dxf=DifferentialStyle(
fill=PatternFill(patternType='solid', bgColor=lose)))
lose_rule.formula = ['NOT(ISERROR(SEARCH("Lose",A1)))']
xl_ws.conditional_formatting.add('A1:A1048576', lose_rule)
colour_scale_rule = ColorScaleRule(start_type='min',
start_color=red,
mid_type='percentile',
mid_color=yellow,
end_type='max',
end_color=green)
xl_ws.conditional_formatting.add('E1:E1048576', colour_scale_rule)
xl_ws.conditional_formatting.add('F1:F1048576', colour_scale_rule)