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 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 format_data(writer, current_date):
workbook = writer.book
sheet = workbook[current_date]
#sheet = workbook.active
#Fill ("NEW") rule
blue_bg = PatternFill(bgColor=Color(indexed=27))
diff_style = DifferentialStyle(fill=blue_bg)
rule = Rule(type="expression", dxf=diff_style)
rule.formula = ['$D2 = "NEW"']
sheet.conditional_formatting.add("D2:E201", rule)
#Arrow rule
rule_arrow = IconSetRule('3Arrows', 'num', [-200, 0, 1])
sheet.conditional_formatting.add("D2:E201", rule_arrow)
write_further_info(sheet)
workbook.save(FILENAME)
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 formatting_xls():
wb = Workbook()
ws = wb.active
# Create fill
redFill = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
# Add a two-color scale
# Takes colors in excel 'RRGGBB' style.
ws.conditional_formatting.add('A1:A10', ColorScaleRule(start_type='min', start_color='AA0000', end_type='max', end_color='00AA00'))
# Add a three-color scale
ws.conditional_formatting.add('B1:B10', ColorScaleRule(start_type='percentile', start_value=10, start_color='AA0000', mid_type='percentile', mid_value=50, mid_color='0000AA', end_type='percentile', end_value=90, end_color='00AA00'))
# 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")
logging.info('A kiírandó file: ' + kiirando_fajl)
arfolyam_file = "arfolyamok.csv"
logging.info('A használt árfolyamok file: ' + arfolyam_file)
arfolyam = {}
egyenleg2 = ""
# xml fájl betöltése
mydoc = minidom.parse(olvasando_fajl)
# Munkalap stílusának beálítása forintra
still = NamedStyle(name="Pénzecske") # Ilyen nevű stílus hozzáadása
still.number_format = '#,##0 "HUF";-#,##0 "HUF"' # A számformátum beállítása forintra
diff_style = DifferentialStyle(fill=PatternFill(
bgColor='C6EFCE', fgColor='006100')) # A feltételes formázás beállítása
rule = Rule(type="expression", dxf=diff_style) # Feltételes kifejezés megadása
rule.formula = ["$B2>0"] # Formula a feltételes formázáshoz
# Árfolyam file meglétének ellenőrzése és frissítése ha két óránál régebbi
fileido = datetime.fromtimestamp(os.stat(
'./arfolyamok.csv').st_ctime) # a meglévő file idejének lekérdezése
now = datetime.now()
max_delay = timedelta(hours=2)
try:
if now - fileido > max_delay:
shutil.copyfile('arfolyamok.csv', 'arfolyamok.old')
logging.debug(
"Régi a fájl ezért lekérdezem az árfolyamokat: {} ".format(
fileido))
c = CurrencyRates() # Aktuális árfolyam lekérdezése
arfolyam = c.get_rates('HUF') # átszámítás forint vs. valutákra
w = csv.writer(open("arfolyamok.csv", "w"))
critical_text = PatternFill(bgColor=severity[4]['color'])
high_text = PatternFill(bgColor=severity[3]['color'])
medium_text = PatternFill(bgColor=severity[2]['color'])
low_text = PatternFill(bgColor=severity[1]['color'])
alignment_text = Alignment(vertical='top', horizontal='left')
critical_diff_style = DifferentialStyle(fill=critical_text,
alignment=alignment_text)
high_diff_style = DifferentialStyle(fill=high_text, alignment=alignment_text)
medium_diff_style = DifferentialStyle(fill=medium_text,
alignment=alignment_text)
low_diff_style = DifferentialStyle(fill=low_text, alignment=alignment_text)
rule = Rule(type="expression", dxf=critical_diff_style)
rule.formula = ['NOT(ISERROR(SEARCH("Critical",$A1)))']
sheet1.conditional_formatting.add("A1:A1000", rule)
rule = Rule(type="expression", dxf=high_diff_style)
rule.formula = ['NOT(ISERROR(SEARCH("High",$A1)))']
sheet1.conditional_formatting.add("A1:A1000", rule)
rule = Rule(type="expression", dxf=medium_diff_style)
rule.formula = ['NOT(ISERROR(SEARCH("Medium",$A1)))']
sheet1.conditional_formatting.add("A1:A1000", rule)
rule = Rule(type="expression", dxf=low_diff_style)
rule.formula = ['NOT(ISERROR(SEARCH("Low",$A1)))']
sheet1.conditional_formatting.add("A1:A1000", rule)
# Below cell auto-wrap
def financial_info(wb):
ws = wb.worksheets[0]
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=3).value
if project_name in latest_quarter_project_names:
'''BC Stage'''
bc_stage = financial_analysis_masters_list[0].data[project_name][
'BICC approval point']
ws.cell(row=row_num,
column=4).value = convert_bc_stage_text(bc_stage)
try:
bc_stage_lst_qrt = financial_analysis_masters_list[1].data[
project_name]['BICC approval point']
if bc_stage != bc_stage_lst_qrt:
ws.cell(row=row_num,
column=4).font = Font(name='Arial',
size=10,
color='00fc2525')
except KeyError:
pass
'''planning stage'''
plan_stage = financial_analysis_masters_list[0].data[project_name][
'Project stage']
ws.cell(row=row_num, column=5).value = plan_stage
try:
plan_stage_lst_qrt = financial_analysis_masters_list[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')
except KeyError:
pass
'''Total WLC'''
wlc_now = financial_analysis_masters_list[0].data[project_name][
'Total Forecast']
ws.cell(row=row_num, column=6).value = wlc_now
'''WLC variance against lst quarter'''
try:
wlc_lst_quarter = financial_analysis_masters_list[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
else:
ws.cell(row=row_num, column=7).value = '-'
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')
except KeyError:
ws.cell(row=row_num, column=7).value = '-'
'''WLC variance against baseline quarter'''
wlc_baseline = financial_analysis_masters_list[fin_bc_index[
project_name][2]].data[project_name]['Total Forecast']
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
else:
ws.cell(row=row_num, column=8).value = '-'
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')
'''Aggregate Spent'''
'''Committed spend'''
'''remaining'''
'''P-Value'''
'''Contigency'''
ws.cell(row=row_num, column=13).value = \
financial_analysis_masters_list[0].data[project_name]['Overall contingency (£m)']
'''OB'''
ws.cell(row=row_num, column=14).value = \
financial_analysis_masters_list[0].data[project_name]['Overall figure for Optimism Bias (£m)']
'''financial DCA rating - this quarter'''
ws.cell(row=row_num, column=15).value = convert_rag_text(
financial_analysis_masters_list[0].data[project_name]
['SRO Finance confidence'])
'''financial DCA rating - last qrt'''
try:
ws.cell(row=row_num, column=16).value = convert_rag_text(
financial_analysis_masters_list[1].data[project_name]
['SRO Finance confidence'])
except KeyError:
ws.cell(row=row_num, column=16).value = ''
'''financial DCA rating - 2 qrts ago'''
try:
ws.cell(row=row_num, column=17).value = convert_rag_text(
financial_analysis_masters_list[2].data[project_name]
['SRO Finance confidence'])
except KeyError:
ws.cell(row=row_num, column=17).value = ''
'''financial DCA rating - 3 qrts ago'''
try:
ws.cell(row=row_num, column=18).value = convert_rag_text(
financial_analysis_masters_list[3].data[project_name]
['SRO Finance confidence'])
except KeyError:
ws.cell(row=row_num, column=18).value = ''
'''financial DCA rating - baseline'''
ws.cell(row=row_num, column=19).value = \
convert_rag_text(financial_analysis_masters_list[fin_bc_index[project_name][2]].data[project_name]
['SRO Finance confidence'])
'''list of columns with conditional formatting'''
list_columns = ['o', 'p', 'q', 'r', 's']
'''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 benefits_info(wb):
ws = wb.worksheets[2]
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=3).value
if project_name in latest_quarter_project_names:
'''BICC approval point'''
bc_stage = list_of_masters_all[0].data[project_name][
'BICC approval point']
ws.cell(row=row_num,
column=4).value = convert_bc_stage_text(bc_stage)
try:
bc_stage_lst_qrt = list_of_masters_all[1].data[project_name][
'BICC approval point']
if bc_stage != bc_stage_lst_qrt:
ws.cell(row=row_num,
column=4).font = Font(name='Arial',
size=10,
color='00fc2525')
except:
pass
'''Next stage'''
proj_stage = list_of_masters_all[0].data[project_name][
'Project stage']
ws.cell(row=row_num, column=5).value = proj_stage
try:
proj_stage_lst_qrt = list_of_masters_all[1].data[project_name][
'Project stage']
if proj_stage != proj_stage_lst_qrt:
ws.cell(row=row_num,
column=5).font = Font(name='Arial',
size=10,
color='00fc2525')
except:
pass
'''initial bcr'''
initial_bcr = list_of_masters_all[0].data[project_name][
'Initial Benefits Cost Ratio (BCR)']
ws.cell(row=row_num, column=6).value = initial_bcr
'''initial bcr baseline'''
try:
baseline_initial_bcr = \
list_of_masters_all[bc_index[project_name][2]].data[project_name]['Initial Benefits Cost Ratio (BCR)']
ws.cell(row=row_num, column=7).value = baseline_initial_bcr
if initial_bcr != baseline_initial_bcr:
if baseline_initial_bcr is None:
pass
else:
ws.cell(row=row_num,
column=6).font = Font(name='Arial',
size=10,
color='00fc2525')
ws.cell(row=row_num,
column=7).font = Font(name='Arial',
size=10,
color='00fc2525')
except TypeError:
ws.cell(row=row_num, column=7).value = ''
'''adjusted bcr'''
adjusted_bcr = list_of_masters_all[0].data[project_name][
'Adjusted Benefits Cost Ratio (BCR)']
ws.cell(row=row_num, column=8).value = adjusted_bcr
'''adjusted bcr baseline'''
try:
baseline_adjusted_bcr = \
list_of_masters_all[bc_index[project_name][2]].data[project_name]['Adjusted Benefits Cost Ratio (BCR)']
ws.cell(row=row_num, column=9).value = baseline_adjusted_bcr
if adjusted_bcr != baseline_adjusted_bcr:
if baseline_adjusted_bcr is None:
pass
else:
ws.cell(row=row_num,
column=8).font = Font(name='Arial',
size=10,
color='00fc2525')
ws.cell(row=row_num,
column=9).font = Font(name='Arial',
size=10,
color='00fc2525')
except TypeError:
ws.cell(row=row_num, column=9).value = ''
'''vfm category now'''
if list_of_masters_all[0].data[project_name][
'VfM Category lower range'] is None:
vfm_cat = list_of_masters_all[0].data[project_name][
'VfM Category single entry']
ws.cell(row=row_num, column=10).value = vfm_cat
else:
vfm_cat = str(list_of_masters_all[0].data[project_name]['VfM Category lower range']) + ' - ' + \
str(list_of_masters_all[0].data[project_name]['VfM Category upper range'])
ws.cell(row=row_num, column=10).value = vfm_cat
'''vfm category baseline'''
try:
if list_of_masters_all[bc_index[project_name][2]].data[
project_name]['VfM Category lower range'] is None:
vfm_cat_baseline = list_of_masters_all[bc_index[
project_name[2]]].data[project_name][
'VfM Category single entry']
ws.cell(row=row_num, column=11).value = vfm_cat_baseline
else:
vfm_cat_baseline = str(list_of_masters_all[bc_index[project_name][2]].data[project_name]['VfM Category lower range']) + ' - ' + \
str(list_of_masters_all[bc_index[project_name][2]].data[project_name]['VfM Category upper range'])
ws.cell(row=row_num, column=11).value = vfm_cat_baseline
except KeyError:
try:
vfm_cat_baseline = list_of_masters_all[
bc_index[project_name]
[2]].data[project_name]['VfM Category single entry']
ws.cell(row=row_num, column=11).value = vfm_cat_baseline
except KeyError:
vfm_cat_baseline = list_of_masters_all[bc_index[
project_name][2]].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=10).font = Font(name='Arial',
size=10,
color='00fc2525')
ws.cell(row=row_num,
column=11).font = Font(name='Arial',
size=10,
color='00fc2525')
'''total monetised benefits'''
tmb = list_of_masters_all[0].data[project_name][
'Total BEN Forecast - Total Monetised Benefits']
ws.cell(row=row_num, column=12).value = tmb
'''tmb variance'''
baseline_tmb = list_of_masters_all[bc_index[project_name][2]].data[
project_name]['Total BEN Forecast - Total Monetised Benefits']
ws.cell(row=row_num, column=13).value = tmb - baseline_tmb
try:
percentage_change = ((tmb - baseline_tmb) / tmb) * 100
if percentage_change > 5 or percentage_change < -5:
ws.cell(row=row_num,
column=13).font = Font(name='Arial',
size=10,
color='00fc2525')
except ZeroDivisionError:
pass
'''in year benefits'''
'''benefits DCA rating - this quarter'''
ws.cell(row=row_num, column=16).value = convert_rag_text(
list_of_masters_all[0].data[project_name]['SRO Benefits RAG'])
'''benefits DCA rating - last qrt'''
try:
ws.cell(row=row_num, column=17).value = convert_rag_text(
list_of_masters_all[1].data[project_name]
['SRO Benefits RAG'])
except KeyError:
ws.cell(row=row_num, column=17).value = ''
'''benefits DCA rating - 2 qrts ago'''
try:
ws.cell(row=row_num, column=18).value = convert_rag_text(
list_of_masters_all[2].data[project_name]
['SRO Benefits RAG'])
except KeyError:
ws.cell(row=row_num, column=18).value = ''
'''benefits DCA rating - 3 qrts ago'''
try:
ws.cell(row=row_num, column=19).value = convert_rag_text(
list_of_masters_all[3].data[project_name]
['SRO Benefits RAG'])
except KeyError:
ws.cell(row=row_num, column=19).value = ''
'''benefits DCA rating - baseline'''
try:
ws.cell(row=row_num, column=20).value = \
convert_rag_text(list_of_masters_all[bc_index[project_name][2]].data[project_name]
['SRO Benefits RAG'])
except:
ws.cell(row=row_num, column=20).value = ''
'''list of columns with conditional formatting'''
list_columns = ['p', 'q', 'r', 's', 't']
'''loops below place conditional formatting (cf) rules into the wb. There are two as the dashboard currently has
two distinct sections/headings, which do not require cf. Therefore, cf starts and ends at the stated rows. this
is hard code that will need to be changed should the position of information in the dashboard change. It is an
easy change however'''
'''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
header = NamedStyle(name="header")
header.font = Font(bold=True)
header.border = Border(bottom=Side(border_style="thin"))
header.alignment = Alignment(horizontal="center", vertical="center")
# Now let's apply this to all first row (header) cells
header_row = sheet[1]
for cell in header_row:
cell.style = header
workbook.save(filename="sample_styles.xlsx")
red_background = PatternFill(bgColor=colors.RED)
diff_style = DifferentialStyle(fill=red_background)
rule = Rule(type="expression", dxf=diff_style)
rule.formula = ["$H1<3"]
sheet.conditional_formatting.add("A1:O100", rule)
workbook.save("sample_conditional_formatting.xlsx")
color_scale_rule = ColorScaleRule(start_type="min",start_color=colors.RED,end_type="max",end_color=colors.GREEN)
# Again, let's add this gradient to the star ratings, column "H"
sheet.conditional_formatting.add("H2:H100", color_scale_rule)
workbook.save(filename="sample_conditional_formatting_color_scale.xlsx")
color_scale_rule = ColorScaleRule(start_type="num",start_value=1,start_color=colors.RED,
mid_type="num",mid_value=3,mid_color=colors.YELLOW,end_type="num",
end_value=5,end_color=colors.GREEN)
# Again, let's add this gradient to the star ratings, column "H"
sheet.conditional_formatting.add("H2:H100", color_scale_rule)
def schedule_info(wb):
ws = wb.worksheets[1]
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=3).value
if project_name in latest_quarter_project_names:
'''BICC approval point'''
bc_stage = list_of_masters_all[0].data[project_name][
'BICC approval point']
ws.cell(row=row_num,
column=4).value = convert_bc_stage_text(bc_stage)
try:
bc_stage_lst_qrt = list_of_masters_all[1].data[project_name][
'BICC approval point']
if bc_stage != bc_stage_lst_qrt:
ws.cell(row=row_num,
column=4).font = Font(name='Arial',
size=10,
color='00fc2525')
except KeyError:
pass
'''Next stage'''
plan_stage = list_of_masters_all[0].data[project_name][
'Project stage']
ws.cell(row=row_num, column=5).value = plan_stage
try:
plan_stage_lst_qrt = list_of_masters_all[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')
except KeyError:
pass
'''Next milestone name and variance'''
local_milestone_dates = []
for x, key in enumerate(
current_milestones_ap_p[project_name].keys()):
date = tuple(current_milestones_ap_p[project_name][key])[0]
if date is None:
pass
elif date > bicc_date:
local_milestone_dates.append((date, x))
try:
next_milestone_name = list(
current_milestones_ap_p[project_name].keys())[
local_milestone_dates[0][1]]
ws.cell(row=row_num, column=6).value = next_milestone_name
next_milestone_date = local_milestone_dates[0][0]
ws.cell(row=row_num, column=7).value = next_milestone_date
except (TypeError, KeyError):
ws.cell(row=row_num, column=6).value = 'None Scheduled'
try:
lst_qrt_diff = first_diff_data[project_name][
next_milestone_name]
ws.cell(row=row_num, column=8).value = lst_qrt_diff
if lst_qrt_diff > 25:
ws.cell(row=row_num,
column=8).font = Font(name='Arial',
size=10,
color='00fc2525')
except (TypeError, KeyError):
ws.cell(row=row_num, column=8).value = ''
try:
bl_qrt_diff = second_diff_data[project_name][
next_milestone_name]
ws.cell(row=row_num, column=9).value = bl_qrt_diff
if bl_qrt_diff > 46:
ws.cell(row=row_num,
column=9).font = Font(name='Arial',
size=10,
color='00fc2525')
except (TypeError, KeyError):
ws.cell(row=row_num, column=9).value = ''
'''start of construction (soc) current date'''
try:
current_soc = tuple(current_milestones_all[project_name]
['Start of Construction/build'])[0]
ws.cell(row=row_num, column=10).value = current_soc
if current_soc < bicc_date:
ws.cell(row=row_num, column=10).value = 'Completed'
except (KeyError, TypeError):
ws.cell(row=row_num, column=10).value = ''
'''soc variance against lst quarter'''
try:
soc_lst_qrt_diff = first_diff_data[project_name][
'Start of Construction/build']
ws.cell(row=row_num, column=11).value = soc_lst_qrt_diff
if soc_lst_qrt_diff > 46:
ws.cell(row=row_num,
column=11).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=11).value = ''
'''soc variance against baseline'''
try:
soc_bl_diff = second_diff_data[project_name][
'Start of Construction/build']
ws.cell(row=row_num, column=12).value = soc_bl_diff
if soc_bl_diff > 85:
ws.cell(row=row_num,
column=12).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=12).value = ''
'''start of operation (sop) current date'''
try:
current_sop = tuple(current_milestones_all[project_name]
['Start of Operation'])[0]
ws.cell(row=row_num, column=13).value = current_sop
if current_sop < bicc_date:
ws.cell(row=row_num, column=13).value = 'Completed'
except (KeyError, TypeError):
ws.cell(row=row_num, column=13).value = ''
'''sop variance against lst quarter'''
try:
sop_lst_qrt_diff = first_diff_data[project_name][
'Start of Operation']
ws.cell(row=row_num, column=14).value = sop_lst_qrt_diff
if sop_lst_qrt_diff > 46:
ws.cell(row=row_num,
column=14).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=14).value = ''
'''sop variance against baseline'''
try:
sop_bl_diff = second_diff_data[project_name][
'Start of Operation']
ws.cell(row=row_num, column=15).value = sop_bl_diff
if sop_bl_diff > 86:
ws.cell(row=row_num,
column=15).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=15).value = ''
'''full operation current date'''
try:
foc = tuple(
current_milestones_all[project_name]['Full Operations'])[0]
ws.cell(row=row_num, column=16).value = foc
if foc < bicc_date:
ws.cell(row=row_num, column=16).value = 'Completed'
else:
ws.cell(row=row_num, column=16).value = foc
except (KeyError, TypeError):
ws.cell(row=row_num, column=16).value = ''
'''fop against lst quarter'''
try:
foc_lst_qrt_diff = first_diff_data[project_name][
'Full Operations']
ws.cell(row=row_num, column=17).value = foc_lst_qrt_diff
if foc_lst_qrt_diff > 46:
ws.cell(row=row_num,
column=17).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=17).value = ''
'''fop against baseline'''
try:
foc_bl_diff = second_diff_data[project_name]['Full Operations']
ws.cell(row=row_num, column=18).value = foc_bl_diff
if foc_bl_diff > 86:
ws.cell(row=row_num,
column=18).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=18).value = ''
'''project end date'''
try:
ped = tuple(current_milestones_all[project_name]
['Project End Date'])[0]
ws.cell(row=row_num, column=19).value = ped
except (KeyError, TypeError):
ws.cell(row=row_num, column=19).value = ''
'''ped variance against lst quarter'''
try:
ped_lst_qrt_diff = first_diff_data[project_name][
'Project End Date']
ws.cell(row=row_num, column=20).value = ped_lst_qrt_diff
if ped_lst_qrt_diff > 46:
ws.cell(row=row_num,
column=20).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=20).value = ''
'''ped variance against baseline'''
try:
ped_bl_diff = second_diff_data[project_name][
'Project End Date']
ws.cell(row=row_num, column=21).value = ped_bl_diff
if ped_bl_diff > 86:
ws.cell(row=row_num,
column=21).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=21).value = ''
'''schedule DCA rating - this quarter'''
ws.cell(row=row_num, column=22).value = convert_rag_text(
list_of_masters_all[0].data[project_name]
['SRO Schedule Confidence'])
'''schedule DCA rating - last qrt'''
try:
ws.cell(row=row_num, column=23).value = convert_rag_text(
list_of_masters_all[1].data[project_name]
['SRO Schedule Confidence'])
except KeyError:
ws.cell(row=row_num, column=23).value = ''
'''schedule DCA rating - 2 qrts ago'''
try:
ws.cell(row=row_num, column=24).value = convert_rag_text(
list_of_masters_all[2].data[project_name]
['SRO Schedule Confidence'])
except KeyError:
ws.cell(row=row_num, column=24).value = ''
'''schedule DCA rating - 3 qrts ago'''
try:
ws.cell(row=row_num, column=25).value = convert_rag_text(
list_of_masters_all[3].data[project_name]
['SRO Schedule Confidence'])
except KeyError:
ws.cell(row=row_num, column=25).value = ''
'''schedule DCA rating - baseline'''
try:
ws.cell(row=row_num, column=26).value = \
convert_rag_text(list_of_masters_all[bc_index[project_name][2]].data[project_name]
['SRO Schedule Confidence'])
except:
ws.cell(row=row_num, column=26).value = ''
'''list of columns with conditional formatting'''
list_columns = ['v', 'w', 'x', 'y', 'z']
'''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 placing_excel(master_data_one, master_data_two):
'''
function that places all information into the summary dashboard sheet
:param master_data_one: python dictionary of latest ar data.
:param master_data_two: python dictionary of last ar data.
:return: populated Excel dashboard.
'''
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=2).value
print(project_name)
if project_name in master_data_one.projects:
dca_one = master_data_one.data[project_name]['DCA']
try:
dca_two = master_data_two.data[project_name]['DCA']
change = up_or_down(dca_one, dca_two)
ws.cell(row=row_num, column=4).value = change
except KeyError:
ws.cell(row=row_num, column=4).value = 'NEW'
ws.cell(row=row_num,
column=5).value = master_data_one.data[project_name]['DCA']
start_date_one = master_data_one.data[project_name]['Start Date']
ws.cell(row=row_num, column=6).value = start_date_one
try:
start_date_two = master_data_two.data[project_name][
'Start Date']
s_date_diff = cal_date_difference(start_date_one,
start_date_two)
ws.cell(row=row_num, column=7).value = s_date_diff
except KeyError:
ws.cell(row=row_num, column=7).value = 0
end_date_one = master_data_one.data[project_name]['End Date']
ws.cell(row=row_num, column=8).value = end_date_one
try:
end_date_two = master_data_two.data[project_name]['End Date']
e_date_diff = cal_date_difference(end_date_one, end_date_two)
ws.cell(row=row_num, column=9).value = e_date_diff
except KeyError:
ws.cell(row=row_num, column=9).value = 0
ws.cell(
row=row_num, column=10
).value = master_data_one.data[project_name]['in year baseline']
ws.cell(
row=row_num, column=11
).value = master_data_one.data[project_name]['in year forecast']
ws.cell(
row=row_num, column=12
).value = master_data_one.data[project_name]['in year variance']
wlc_one = master_data_one.data[project_name]['WLC baseline']
ws.cell(row=row_num, column=13).value = wlc_one
try:
wlc_two = master_data_two.data[project_name]['WLC baseline']
wlc_diff = wlc_one - wlc_two
ws.cell(row=row_num, column=14).value = wlc_diff
except KeyError:
ws.cell(row=row_num, column=14).value = 0
except TypeError:
ws.cell(row=row_num, column=14).value = 'Check wlc value/data'
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_data_two.data:
ws.cell(row=row_num,
column=3).value = master_data_two[project_name]['DCA']
# Highlight cells that contain RAG text, with background and text the same colour. column E.
ag_text = Font(color="00a5b700")
ag_fill = PatternFill(bgColor="00a5b700")
dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber/Green",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber/Green",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
ar_text = Font(color="00f97b31")
ar_fill = PatternFill(bgColor="00f97b31")
dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber/Red",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber/Red",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
red_text = Font(color="00fc2525")
red_fill = PatternFill(bgColor="00fc2525")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Red",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Red",E1)))']
ws.conditional_formatting.add('E1:E100', rule)
green_text = Font(color="0017960c")
green_fill = PatternFill(bgColor="0017960c")
dxf = DifferentialStyle(font=green_text, fill=green_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Green",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Green",e1)))']
ws.conditional_formatting.add('E1:E100', rule)
amber_text = Font(color="00fce553")
amber_fill = PatternFill(bgColor="00fce553")
dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
# Highlight cells that contain RAG text, with background and black text columns G to L.
ag_text = Font(color="000000")
ag_fill = PatternFill(bgColor="00a5b700")
dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber/Green",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber/Green",G1)))']
ws.conditional_formatting.add('G1:L100', rule)
ar_text = Font(color="000000")
ar_fill = PatternFill(bgColor="00f97b31")
dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber/Red",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber/Red",G1)))']
ws.conditional_formatting.add('G1:L100', rule)
red_text = Font(color="000000")
red_fill = PatternFill(bgColor="00fc2525")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Red",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Red",G1)))']
ws.conditional_formatting.add('G1:L100', rule)
green_text = Font(color="000000")
green_fill = PatternFill(bgColor="0017960c")
dxf = DifferentialStyle(font=green_text, fill=green_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Green",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Green",G1)))']
ws.conditional_formatting.add('G1:L100', rule)
amber_text = Font(color="000000")
amber_fill = PatternFill(bgColor="00fce553")
dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber",G1)))']
ws.conditional_formatting.add('G1:L100', rule)
# highlighting new projects
red_text = Font(color="00fc2525")
white_fill = PatternFill(bgColor="000000")
dxf = DifferentialStyle(font=red_text, fill=white_fill)
rule = Rule(type="containsText",
operator="containsText",
text="NEW",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("NEW",F1)))']
ws.conditional_formatting.add('D1:D100', rule)
# assign the icon set to a rule
first = FormatObject(type='num', val=-1)
second = FormatObject(type='num', val=0)
third = FormatObject(type='num', val=1)
iconset = IconSet(iconSet='3Arrows',
cfvo=[first, second, third],
percent=None,
reverse=None)
rule = Rule(type='iconSet', iconSet=iconset)
ws.conditional_formatting.add('D1:D100', rule)
return wb
def _xlsx_create_rpm_details(self, wb, rpm_table):
df = self._rename_rpm_detail_columns(rpm_table)
ws_rd = wb.create_sheet("RPMs details")
(
normal_font,
normal_border,
normal_fill,
) = self._style.get_rpm_xslx_table_normal()
normal_align = Alignment(horizontal="left",
vertical="top",
wrap_text=True)
center_align = Alignment(horizontal="center",
vertical="center",
wrap_text=True)
(
imt_font,
imt_border,
imt_fill,
) = self._style.get_rpm_xslx_table_important_failed()
(
ctc_font,
ctc_border,
ctc_fill,
) = self._style.get_rpm_xslx_table_critical_failed()
ctc_style = self._get_critical_failed_style()
imt_style = self._get_important_failed_style()
(
header_font,
header_border,
header_fill,
) = self._style.get_rpm_xslx_table_header()
cols = df.columns
xlsx_cols = list(string.ascii_lowercase[0:len(cols)])
for i in range(len(xlsx_cols)):
cell_no = xlsx_cols[i] + "1"
ws_rd[cell_no] = cols[i]
ws_rd[cell_no].font = header_font
ws_rd[cell_no].fill = header_fill
ws_rd[cell_no].border = header_border
ws_rd.column_dimensions["J"].hidden = True
curr_row_no = 2
supported_col_no = 0
for i in range(len(cols)):
if cols[i] == "Driver Flag: Supported":
supported_col_no = i
break
vld_lic = self._style.get_valid_licenses()
for i, row in df.iterrows():
rpm_license = row["License"]
for col_idx in range(len(cols)):
cell_no = xlsx_cols[col_idx] + str(curr_row_no)
val = row[cols[col_idx]]
ws_rd[cell_no].font = normal_font
ws_rd[cell_no].fill = normal_fill
ws_rd[cell_no].border = normal_border
ws_rd[cell_no].alignment = normal_align
if cols[col_idx] == "Symbols Check":
val = self._get_sym_check_failed(val)
if val == "":
val = "All passed!"
ws_rd[cell_no] = val
ws_rd[cell_no].alignment = center_align
else:
ws_rd[cell_no] = val
elif cols[col_idx] == "Driver Flag: Supported":
val = "All passed!"
ws_rd[cell_no] = val
ws_rd[cell_no].alignment = center_align
elif cols[col_idx] == "License":
lcs_chk = self._fmt_driver_license_check(
rpm_license, row["Driver Licenses"], vld_lic)
if lcs_chk == "":
if rpm_license == "":
ws_rd[cell_no] = "No License"
ws_rd[cell_no].font = imt_font
ws_rd[cell_no].fill = imt_fill
ws_rd[cell_no].border = imt_border
else:
ws_rd[cell_no] = rpm_license
else:
ws_rd[cell_no] = lcs_chk
ws_rd[cell_no].font = imt_font
ws_rd[cell_no].fill = imt_fill
ws_rd[cell_no].border = imt_border
else:
ws_rd[cell_no] = str(val)
failed_drivers = self._get_supported_driver_failed(
row["Driver Flag: Supported"])
driver_count = len(failed_drivers)
if driver_count > 0: # format supported information
for sp_idx in range(driver_count):
cell_no = xlsx_cols[supported_col_no] + str(curr_row_no +
sp_idx)
ws_rd[cell_no] = failed_drivers[sp_idx]
ws_rd[cell_no].font = ctc_font
ws_rd[cell_no].fill = ctc_fill
ws_rd[cell_no].border = ctc_border
ws_rd[cell_no].alignment = normal_align
if driver_count > 1: # need merge cell
for col_idx in range(len(cols)):
if col_idx != supported_col_no:
start_cell_no = xlsx_cols[col_idx] + str(curr_row_no)
end_cell_no = xlsx_cols[col_idx] + str(curr_row_no +
driver_count -
1)
merge_range = start_cell_no + ":" + end_cell_no
ws_rd.merge_cells(merge_range)
if driver_count == 0:
curr_row_no += 1
else:
curr_row_no += driver_count
records = curr_row_no
empty_vendor = Rule(type="expression", dxf=imt_style)
empty_vendor.formula = ['$C2 = ""']
ws_rd.conditional_formatting.add(f"C2:C{records}", empty_vendor)
sf_rule = Rule(type="expression", dxf=ctc_style)
sf_rule.formula = [
'=OR($H2="All passed!", AND(ISNUMBER(FIND(":", $H2)), ISNUMBER(FIND("external", $H2))))'
]
ws_rd.conditional_formatting.add(f"H2:H{records}", sf_rule)
sig_rule = Rule(type="expression", dxf=ctc_style)
sig_rule.formula = ['=AND($D2 <> "", $D2 <> "(none)")']
ws_rd.conditional_formatting.add(f"D2:D{records}", sig_rule)
sym_rule = Rule(type="expression", dxf=ctc_style)
sym_rule.formula = ['=ISNUMBER(FIND(".ko", $I2))']
ws_rd.conditional_formatting.add(f"I2:I{records}", sym_rule)
def overall_info(wb):
ws = wb.worksheets[3]
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=2).value
if project_name in latest_quarter_project_names:
'''BC Stage'''
bc_stage = financial_analysis_masters_list[0].data[project_name][
'BICC approval point']
ws.cell(row=row_num,
column=3).value = convert_bc_stage_text(bc_stage)
try:
bc_stage_lst_qrt = financial_analysis_masters_list[1].data[
project_name]['BICC approval point']
if bc_stage != bc_stage_lst_qrt:
ws.cell(row=row_num,
column=3).font = Font(name='Arial',
size=10,
color='00fc2525')
except KeyError:
pass
'''planning stage'''
plan_stage = financial_analysis_masters_list[0].data[project_name][
'Project stage']
ws.cell(row=row_num, column=4).value = plan_stage
try:
plan_stage_lst_qrt = financial_analysis_masters_list[1].data[
project_name]['Project stage']
if plan_stage != plan_stage_lst_qrt:
ws.cell(row=row_num,
column=4).font = Font(name='Arial',
size=10,
color='00fc2525')
except KeyError:
pass
'''Total WLC'''
wlc_now = financial_analysis_masters_list[0].data[project_name][
'Total Forecast']
ws.cell(row=row_num, column=5).value = wlc_now
'''WLC variance against lst quarter'''
try:
wlc_lst_quarter = financial_analysis_masters_list[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=6).value = diff_lst_qrt
else:
ws.cell(row=row_num, column=6).value = '-'
percentage_change = (
(wlc_now - wlc_lst_quarter) / wlc_now) * 100
if percentage_change > 5 or percentage_change < -5:
ws.cell(row=row_num,
column=6).font = Font(name='Arial',
size=10,
color='00fc2525')
except KeyError:
ws.cell(row=row_num, column=6).value = '-'
'''WLC variance against baseline quarter'''
wlc_baseline = financial_analysis_masters_list[fin_bc_index[
project_name][2]].data[project_name]['Total Forecast']
diff_bl = wlc_now - wlc_baseline
if float(diff_bl) > 0.49 or float(diff_bl) < -0.49:
ws.cell(row=row_num, column=7).value = diff_bl
else:
ws.cell(row=row_num, column=7).value = '-'
print(project_name)
percentage_change = ((wlc_now - wlc_baseline) / 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')
'''vfm category now'''
if list_of_masters_all[0].data[project_name][
'VfM Category lower range'] is None:
vfm_cat = list_of_masters_all[0].data[project_name][
'VfM Category single entry']
ws.cell(row=row_num, column=8).value = vfm_cat
else:
vfm_cat = str(list_of_masters_all[0].data[project_name]['VfM Category lower range']) + ' - ' + \
str(list_of_masters_all[0].data[project_name]['VfM Category upper range'])
ws.cell(row=row_num, column=8).value = vfm_cat
'''vfm category baseline'''
try:
if list_of_masters_all[bc_index[project_name][2]].data[
project_name]['VfM Category lower range'] is None:
vfm_cat_baseline = list_of_masters_all[1].data[
project_name]['VfM Category single entry']
else:
vfm_cat_baseline = str(list_of_masters_all[bc_index[project_name][2]].data[project_name][
'VfM Category lower range']) + ' - ' + \
str(list_of_masters_all[bc_index[project_name][2]].data[project_name][
'VfM Category upper range'])
except KeyError:
try:
vfm_cat_baseline = list_of_masters_all[
bc_index[project_name]
[2]].data[project_name]['VfM Category single entry']
except KeyError:
try:
vfm_cat_baseline = list_of_masters_all[
bc_index[project_name]
[2]].data[project_name]['VfM Category']
except:
vfm_cat_baseline = None
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=10,
color='00fc2525')
'''full operation current date'''
try:
foc = tuple(current_milestones_all[project_name]
['Project End Date'])[0]
ws.cell(row=row_num, column=9).value = foc
if foc < bicc_date:
ws.cell(row=row_num, column=9).value = 'Completed'
else:
ws.cell(row=row_num, column=9).value = foc
except (KeyError, TypeError):
ws.cell(row=row_num, column=9).value = ''
'''fop against lst quarter'''
try:
foc_lst_qrt_diff = first_diff_data[project_name][
'Project End Date']
ws.cell(row=row_num, column=10).value = foc_lst_qrt_diff
if foc_lst_qrt_diff > 46:
ws.cell(row=row_num,
column=10).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=10).value = ''
'''fop against baseline'''
try:
foc_bl_diff = second_diff_data[project_name][
'Project End Date']
ws.cell(row=row_num, column=11).value = foc_bl_diff
if foc_bl_diff > 86:
ws.cell(row=row_num,
column=11).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=11).value = ''
try:
ws.cell(row=row_num, column=12).value = highlight_close_dates_ipdc\
(concatenate_dates(list_of_masters_all[0].data[project_name]['Last time at BICC']))
ws.cell(row=row_num, column=13).value = highlight_close_dates_ipdc\
(concatenate_dates(list_of_masters_all[0].data[project_name]['Next at BICC']))
except TypeError:
print(project_name +
' caused error in last at / next at ipdc calculation')
'''IPA DCA rating'''
ipa_dca = convert_rag_text(
list_of_masters_all[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(
list_of_masters_all[0].data[project_name]['Departmental DCA'])
'''DCA rating - last qrt'''
try:
ws.cell(row=row_num, column=19).value = convert_rag_text(
list_of_masters_all[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(
list_of_masters_all[2].data[project_name]
['Departmental DCA'])
except KeyError:
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(
list_of_masters_all[3].data[project_name]
['Departmental DCA'])
except KeyError:
ws.cell(row=row_num, column=21).value = ''
'''DCA rating - baseline'''
try:
ws.cell(row=row_num, column=23).value = \
convert_rag_text(list_of_masters_all[bc_index[project_name][2]].data[project_name]
['Departmental DCA'])
except:
ws.cell(row=row_num, column=23).value = ''
'''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
def write(ws, orders, created_on, update, settings):
row_offset = 0
max_row = 0
locations = sorted(orders.keys())
# set column widths
ws.column_dimensions["A"].width = 14.00 + MYSTERY_WIDTH_OFFSET
ws.column_dimensions["B"].width = 10.00 + MYSTERY_WIDTH_OFFSET
ws.column_dimensions["C"].width = 06.71 + MYSTERY_WIDTH_OFFSET
ws.column_dimensions["D"].width = 07.00 + MYSTERY_WIDTH_OFFSET
ws.column_dimensions["E"].width = 08.00 + MYSTERY_WIDTH_OFFSET
ws.column_dimensions["F"].width = 11.00 + MYSTERY_WIDTH_OFFSET
ws.column_dimensions["J"].width = 01.57 + MYSTERY_WIDTH_OFFSET
ws.column_dimensions["L"].width = 13.14 + MYSTERY_WIDTH_OFFSET
#format 'created / updated' and 'search' cells
ws.merge_cells("G1:I1")
ws["G1"].alignment = Alignment(horizontal="right",
vertical="top",
wrap_text=True)
ws["K2"] = "Search:"
ws["K2"].alignment = Alignment(horizontal="right")
ws["L2"].fill = style['recent']['fill']
#the big loop
for location in locations:
sz = len(orders[location])
if (sz > 0):
row = 1 + row_offset
ws.row_dimensions[row].height = 30
ws['A%s' % row].font = style['fonts']['header']
ws['A%s' % row] = location
# insert 'created on' date
if row == 1:
date_string = "Created On: {}".format(
created_on.strftime('%m-%d-%Y'))
if update:
date_string += "\nUpdated On: {}".format(
datetime.date.today().strftime('%m-%d-%Y'))
ws["G1"] = date_string
row_offset += 1
for excelRow in range(1, sz + 1):
#begin writing the business data to sheet
for excelCol in range(1, len(COLUMN_NAMES) + 1):
col_name = COLUMN_NAMES[excelCol - 1]
cell = ws.cell(row=excelRow + row_offset, column=excelCol)
value = orders[location][excelRow - 1]['info'][col_name]
status = orders[location][excelRow - 1]['status']
# right justify date
if col_name == "Need-By Date":
cell.alignment = Alignment(horizontal="right")
# generate 'balance due' formula
if col_name == "Balance Due":
value = "=if(C{row}-D{row}<0, 0, C{row}-D{row})".format(
row=excelRow + row_offset)
if col_name in ["Quantity Ordered", "Quantity Received"]:
try:
value = int(value)
except:
pass
# add horizontal gridlines
cell.border = style['borders']['underline']
cell.font = style[status]['font']
cell.fill = style[status]['fill']
cell.value = value
# add gridlines to the right of the page for handwritten notes on print out
ws.cell(row=excelRow + row_offset,
column=7).border = style['borders']['underline']
ws.cell(row=excelRow + row_offset,
column=8).border = style['borders']['mid']
ws.cell(row=excelRow + row_offset,
column=9).border = style['borders']['underline']
max_row = excelRow + row_offset
row_offset += sz + 1
ws.print_area = f'A1:I{max_row}'
#create conditional formatting for search results
dxf = DifferentialStyle(fill=style['found']['fill'])
r1 = Rule(type="expression", stopIfTrue=True)
r1.formula = ['isblank($L$2)']
ws.conditional_formatting.add(f"A1:F{max_row}".format(), r1)
r2 = Rule(type="expression", dxf=dxf, stopIfTrue=False)
r2.formula = ['isnumber(search($L$2, $A1))']
ws.conditional_formatting.add(f"A1:F{max_row}".format(), r2)
# print( " 1 4 ".replace(' ','').isdigit() )
# read from each row:
# skip the first row, it the titles
# only read from the first col
ws["C1"] = "male"
ws["D1"] = "female"
ws["E1"] = "ratio"
print(len(ws['A']))
for row in ws.iter_rows(min_row=2):
ratio = row[0].value
if ratio is None:
ratio = ""
valid, male, female = fix_ratio(ratio)
if not valid:
print("not valid: " + ratio)
row[2].value = male if valid else 0
row[3].value = female if valid else 0
row[4].value = male / female if valid else -1
row[4].value = float("{0:.4f}".format(row[4].value))
# highlight the invalid data
red_background = PatternFill(bgColor=colors.RED)
diff_style = DifferentialStyle(fill=red_background)
rule = Rule(type="expression", dxf=diff_style)
rule.formula = ["$E1<0"]
ws.conditional_formatting.add("A1:E{0}".format(len(ws['A'])), rule)
wb.save(filename=out_file)
def placing_excel(dict_one, dict_two):
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=3).value
print(project_name)
if project_name in dict_one:
ws.cell(row=row_num,
column=4).value = dict_one[project_name]['Total Forecast']
ws.cell(row=row_num,
column=6).value = dict_one[project_name]['Change']
ws.cell(row=row_num, column=7
).value = dict_one[project_name]['SRO Finance confidence']
narrative = combine_narrtives(project_name, dict_one,
gmpp_narrative_keys)
print(narrative)
if narrative == 'NoneNoneNone':
ws.cell(row=row_num, column=8).value = combine_narrtives(
project_name, dict_one, bicc_narrative_keys)
else:
ws.cell(row=row_num, column=8).value = combine_narrtives(
project_name, dict_one, gmpp_narrative_keys)
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=3).value
if project_name in dict_two:
try:
ws.cell(
row=row_num, column=5
).value = dict_two[project_name]['SRO Finance confidence']
except KeyError:
pass
# Highlight cells that contain RAG text, with background and text the same colour. column E.
ag_text = Font(color="00a5b700")
ag_fill = PatternFill(bgColor="00a5b700")
dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber/Green",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber/Green",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
ar_text = Font(color="00f97b31")
ar_fill = PatternFill(bgColor="00f97b31")
dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber/Red",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber/Red",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
red_text = Font(color="00fc2525")
red_fill = PatternFill(bgColor="00fc2525")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Red",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Red",E1)))']
ws.conditional_formatting.add('E1:E100', rule)
green_text = Font(color="0017960c")
green_fill = PatternFill(bgColor="0017960c")
dxf = DifferentialStyle(font=green_text, fill=green_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Green",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Green",e1)))']
ws.conditional_formatting.add('E1:E100', rule)
amber_text = Font(color="00fce553")
amber_fill = PatternFill(bgColor="00fce553")
dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
# Highlight cells that contain RAG text, with background and black text columns G to L.
ag_text = Font(color="000000")
ag_fill = PatternFill(bgColor="00a5b700")
dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber/Green",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber/Green",G1)))']
ws.conditional_formatting.add('G1:G100', rule)
ar_text = Font(color="000000")
ar_fill = PatternFill(bgColor="00f97b31")
dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber/Red",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber/Red",G1)))']
ws.conditional_formatting.add('G1:G100', rule)
red_text = Font(color="000000")
red_fill = PatternFill(bgColor="00fc2525")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Red",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Red",G1)))']
ws.conditional_formatting.add('G1:G100', rule)
green_text = Font(color="000000")
green_fill = PatternFill(bgColor="0017960c")
dxf = DifferentialStyle(font=green_text, fill=green_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Green",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Green",G1)))']
ws.conditional_formatting.add('G1:G100', rule)
amber_text = Font(color="000000")
amber_fill = PatternFill(bgColor="00fce553")
dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber",G1)))']
ws.conditional_formatting.add('G1:G100', rule)
# highlighting new projects
red_text = Font(color="00fc2525")
white_fill = PatternFill(bgColor="000000")
dxf = DifferentialStyle(font=red_text, fill=white_fill)
rule = Rule(type="containsText",
operator="containsText",
text="NEW",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("NEW",F1)))']
ws.conditional_formatting.add('F1:F100', rule)
# assign the icon set to a rule
first = FormatObject(type='num', val=-1)
second = FormatObject(type='num', val=0)
third = FormatObject(type='num', val=1)
iconset = IconSet(iconSet='3Arrows',
cfvo=[first, second, third],
percent=None,
reverse=None)
rule = Rule(type='iconSet', iconSet=iconset)
ws.conditional_formatting.add('F1:F100', rule)
# # change text in last at next at BICC column
# for row_num in range(2, ws.max_row + 1):
# if ws.cell(row=row_num, column=13).value == '-2 weeks':
# ws.cell(row=row_num, column=13).value = 'Last BICC'
# if ws.cell(row=row_num, column=13).value == '2 weeks':
# ws.cell(row=row_num, column=13).value = 'Next BICC'
# if ws.cell(row=row_num, column=13).value == 'Today':
# ws.cell(row=row_num, column=13).value = 'This BICC'
# if ws.cell(row=row_num, column=14).value == '-2 weeks':
# ws.cell(row=row_num, column=14).value = 'Last BICC'
# if ws.cell(row=row_num, column=14).value == '2 weeks':
# ws.cell(row=row_num, column=14).value = 'Next BICC'
# if ws.cell(row=row_num, column=14).value == 'Today':
# ws.cell(row=row_num, column=14).value = 'This BICC'
#
# # highlight text in bold
# ft = Font(bold=True)
# for row_num in range(2, ws.max_row + 1):
# lis = ['This week', 'Next week', 'Last week', 'Two weeks',
# 'Two weeks ago', 'This mth', 'Last mth', 'Next mth',
# '2 mths', '3 mths', '-2 mths', '-3 mths', '-2 weeks',
# 'Today', 'Last BICC', 'Next BICC', 'This BICC',
# 'Later this mth']
# if ws.cell(row=row_num, column=10).value in lis:
# ws.cell(row=row_num, column=10).font = ft
# if ws.cell(row=row_num, column=11).value in lis:
# ws.cell(row=row_num, column=11).font = ft
# if ws.cell(row=row_num, column=13).value in lis:
# ws.cell(row=row_num, column=13).font = ft
# if ws.cell(row=row_num, column=14).value in lis:
# ws.cell(row=row_num, column=14).font = ft
return wb
end_type='max', end_color='00AA00'))
ws.conditional_formatting.add('B1:B10',ColorScaleRule(start_type='percentile', start_value=10, start_color='AA0000',
mid_type='percentile', mid_value=50, mid_color='0000AA',
end_type='percentile', end_value=90, end_color='00AA00'))
ws.conditional_formatting.add('C2:C10',CellIsRule(operator='lessThan', formula=['C$1'], stopIfTrue=True, fill=redFill))
ws.conditional_formatting.add('D2:D10',CellIsRule(operator='between', formula=['1','5'], stopIfTrue=True, fill=redFill))
ws.conditional_formatting.add('E1:E10',FormulaRule(formula=['ISBLANK(E1)'], stopIfTrue=True, fill=redFill))
myFont = Font()
myBorder = Border()
ws.conditional_formatting.add('E1:E10',FormulaRule(formula=['E1=0'], font=myFont, border=myBorder, fill=redFill))
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('test2.xlsx')
def placing_excel(dict_one, dict_two):
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=3).value
print(project_name)
if project_name in dict_one:
ws.cell(row=row_num, column=4).value = dict_one[project_name]['Total Forecast']
ws.cell(row=row_num, column=6).value = dict_one[project_name]['Change']
ws.cell(row=row_num, column=7).value = convert_rag_text(dict_one[project_name]['Departmental DCA'])
ws.cell(row=row_num, column=8).value = convert_rag_text(dict_one[project_name]['GMPP - IPA DCA last quarter'])
ws.cell(row=row_num, column=9).value = convert_bc_stage_text(dict_one[project_name]['BICC approval point'])
ws.cell(row=row_num, column=10).value = dict_one[project_name]['Start of Operation']
ws.cell(row=row_num, column=11).value = dict_one[project_name]['Project End Date']
ws.cell(row=row_num, column=12).value = convert_rag_text(dict_one[project_name]['SRO Finance confidence'])
ws.cell(row=row_num, column=13).value = dict_one[project_name]['Last time at BICC']
ws.cell(row=row_num, column=14).value = dict_one[project_name]['Next at BICC']
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=3).value
if project_name in dict_two:
ws.cell(row=row_num, column=5).value = convert_rag_text(dict_two[project_name]['Departmental DCA'])
# Highlight cells that contain RAG text, with background and text the same colour. column E.
ag_text = Font(color="00a5b700")
ag_fill = PatternFill(bgColor="00a5b700")
dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
rule = Rule(type="containsText", operator="containsText", text="A/G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/G",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
ar_text = Font(color="00f97b31")
ar_fill = PatternFill(bgColor="00f97b31")
dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
rule = Rule(type="containsText", operator="containsText", text="A/R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/R",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
red_text = Font(color="00fc2525")
red_fill = PatternFill(bgColor="00fc2525")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText", operator="containsText", text="R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("R",E1)))']
ws.conditional_formatting.add('E1:E100', rule)
green_text = Font(color="0017960c")
green_fill = PatternFill(bgColor="0017960c")
dxf = DifferentialStyle(font=green_text, fill=green_fill)
rule = Rule(type="containsText", operator="containsText", text="G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("G",e1)))']
ws.conditional_formatting.add('E1:E100', rule)
amber_text = Font(color="00fce553")
amber_fill = PatternFill(bgColor="00fce553")
dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
rule = Rule(type="containsText", operator="containsText", text="A", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
# highlight cells in column g
ag_text = Font(color="000000")
ag_fill = PatternFill(bgColor="00a5b700")
dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
rule = Rule(type="containsText", operator="containsText", text="A/G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/G",g1)))']
ws.conditional_formatting.add('g1:g100', rule)
ar_text = Font(color="000000")
ar_fill = PatternFill(bgColor="00f97b31")
dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
rule = Rule(type="containsText", operator="containsText", text="A/R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/R",g1)))']
ws.conditional_formatting.add('g1:g100', rule)
red_text = Font(color="000000")
red_fill = PatternFill(bgColor="00fc2525")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText", operator="containsText", text="R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("R",g1)))']
ws.conditional_formatting.add('g1:g100', rule)
green_text = Font(color="000000")
green_fill = PatternFill(bgColor="0017960c")
dxf = DifferentialStyle(font=green_text, fill=green_fill)
rule = Rule(type="containsText", operator="containsText", text="G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("G",g1)))']
ws.conditional_formatting.add('g1:g100', rule)
amber_text = Font(color="000000")
amber_fill = PatternFill(bgColor="00fce553")
dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
rule = Rule(type="containsText", operator="containsText", text="A", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A",g1)))']
ws.conditional_formatting.add('g1:g100', rule)
# highlight cells in column H
ag_text = Font(color="000000")
ag_fill = PatternFill(bgColor="00a5b700")
dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
rule = Rule(type="containsText", operator="containsText", text="A/G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/G",h1)))']
ws.conditional_formatting.add('h1:h100', rule)
ar_text = Font(color="000000")
ar_fill = PatternFill(bgColor="00f97b31")
dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
rule = Rule(type="containsText", operator="containsText", text="A/R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/R",h1)))']
ws.conditional_formatting.add('h1:h100', rule)
red_text = Font(color="000000")
red_fill = PatternFill(bgColor="00fc2525")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText", operator="containsText", text="R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("R",h1)))']
ws.conditional_formatting.add('h1:h100', rule)
green_text = Font(color="000000")
green_fill = PatternFill(bgColor="0017960c")
dxf = DifferentialStyle(font=green_text, fill=green_fill)
rule = Rule(type="containsText", operator="containsText", text="G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("G",h1)))']
ws.conditional_formatting.add('h1:h100', rule)
amber_text = Font(color="000000")
amber_fill = PatternFill(bgColor="00fce553")
dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
rule = Rule(type="containsText", operator="containsText", text="A", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A",h1)))']
ws.conditional_formatting.add('h1:h100', rule)
# highlight cells in column H
ag_text = Font(color="000000")
ag_fill = PatternFill(bgColor="00a5b700")
dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
rule = Rule(type="containsText", operator="containsText", text="A/G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/G",l1)))']
ws.conditional_formatting.add('l1:l100', rule)
ar_text = Font(color="000000")
ar_fill = PatternFill(bgColor="00f97b31")
dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
rule = Rule(type="containsText", operator="containsText", text="A/R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/R",l1)))']
ws.conditional_formatting.add('l1:l100', rule)
red_text = Font(color="000000")
red_fill = PatternFill(bgColor="00fc2525")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText", operator="containsText", text="R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("R",l1)))']
ws.conditional_formatting.add('l1:l100', rule)
green_text = Font(color="000000")
green_fill = PatternFill(bgColor="0017960c")
dxf = DifferentialStyle(font=green_text, fill=green_fill)
rule = Rule(type="containsText", operator="containsText", text="G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("G",l1)))']
ws.conditional_formatting.add('l1:l100', rule)
amber_text = Font(color="000000")
amber_fill = PatternFill(bgColor="00fce553")
dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
rule = Rule(type="containsText", operator="containsText", text="A", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A",l1)))']
ws.conditional_formatting.add('l1:l100', rule)
# Highlight cells that contain RAG text, with background and black text columns G to L.
# ag_text = Font(color="000000")
# ag_fill = PatternFill(bgColor="00a5b700")
# dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
# rule = Rule(type="uniqueValues", operator="equal", text="A/G", dxf=dxf)
# rule.formula = ['NOT(ISERROR(SEARCH("A/G",G1)))']
# ws.conditional_formatting.add('G1:L100', rule)
#
# ar_text = Font(color="000000")
# ar_fill = PatternFill(bgColor="00f97b31")
# dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
# rule = Rule(type="uniqueValues", operator="equal", text="A/R", dxf=dxf)
# rule.formula = ['NOT(ISERROR(SEARCH("A/R",G1)))']
# ws.conditional_formatting.add('G1:L100', rule)
#
# red_text = Font(color="000000")
# red_fill = PatternFill(bgColor="00fc2525")
# dxf = DifferentialStyle(font=red_text, fill=red_fill)
# rule = Rule(type="uniqueValues", operator="equal", text="R", dxf=dxf)
# rule.formula = ['NOT(ISERROR(SEARCH("R",G1)))']
# ws.conditional_formatting.add('G1:L100', rule)
#
# green_text = Font(color="000000")
# green_fill = PatternFill(bgColor="0017960c")
# dxf = DifferentialStyle(font=green_text, fill=green_fill)
# rule = Rule(type="uniqueValues", operator="equal", text="G", dxf=dxf)
# rule.formula = ['NOT(ISERROR(SEARCH("Green",G1)))']
# ws.conditional_formatting.add('G1:L100', rule)
#
# amber_text = Font(color="000000")
# amber_fill = PatternFill(bgColor="00fce553")
# dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
# rule = Rule(type="uniqueValues", operator="equal", text="A", dxf=dxf)
# rule.formula = ['NOT(ISERROR(SEARCH("A",G1)))']
# ws.conditional_formatting.add('G1:L100', rule)
# highlighting new projects
red_text = Font(color="00fc2525")
white_fill = PatternFill(bgColor="000000")
dxf = DifferentialStyle(font=red_text, fill=white_fill)
rule = Rule(type="uniqueValues", operator="equal", text="NEW", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("NEW",F1)))']
ws.conditional_formatting.add('F1:F100', rule)
# assign the icon set to a rule
first = FormatObject(type='num', val=-1)
second = FormatObject(type='num', val=0)
third = FormatObject(type='num', val=1)
iconset = IconSet(iconSet='3Arrows', cfvo=[first, second, third], percent=None, reverse=None)
rule = Rule(type='iconSet', iconSet=iconset)
ws.conditional_formatting.add('F1:F100', rule)
# change text in last at next at BICC column
for row_num in range(2, ws.max_row + 1):
if ws.cell(row=row_num, column=13).value == '-2 weeks':
ws.cell(row=row_num, column=13).value = 'Last BICC'
if ws.cell(row=row_num, column=13).value == '2 weeks':
ws.cell(row=row_num, column=13).value = 'Next BICC'
if ws.cell(row=row_num, column=13).value == 'Today':
ws.cell(row=row_num, column=13).value = 'This BICC'
if ws.cell(row=row_num, column=14).value == '-2 weeks':
ws.cell(row=row_num, column=14).value = 'Last BICC'
if ws.cell(row=row_num, column=14).value == '2 weeks':
ws.cell(row=row_num, column=14).value = 'Next BICC'
if ws.cell(row=row_num, column=14).value == 'Today':
ws.cell(row=row_num, column=14).value = 'This BICC'
# highlight text in bold
ft = Font(bold=True)
for row_num in range(2, ws.max_row + 1):
lis = ['This week', 'Next week', 'Last week', 'Two weeks',
'Two weeks ago', 'This mth', 'Last mth', 'Next mth',
'2 mths', '3 mths', '-2 mths', '-3 mths', '-2 weeks',
'Today', 'Last BICC', 'Next BICC', 'This BICC',
'Later this mth']
if ws.cell(row=row_num, column=10).value in lis:
ws.cell(row=row_num, column=10).font = ft
if ws.cell(row=row_num, column=11).value in lis:
ws.cell(row=row_num, column=11).font = ft
if ws.cell(row=row_num, column=13).value in lis:
ws.cell(row=row_num, column=13).font = ft
if ws.cell(row=row_num, column=14).value in lis:
ws.cell(row=row_num, column=14).font = ft
return wb
from openpyxl.styles import numbers
for row in sheet['B2:C13']:
for cell in row:
cell.number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1
from openpyxl.styles import PatternFill, colors
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule
yellow_background = PatternFill(bgColor=colors.COLOR_INDEX[5])
diff_style = DifferentialStyle(fill=yellow_background)
rule = Rule(type="expression", dxf=diff_style)
rule.formula = ["$B1<1000"]
sheet.conditional_formatting.add(sheet.calculate_dimension(), rule)
work_book.save('AirLineBumps_16_17_formatted.xlsx')
# Analyzing the potential causes of attrition among the Research & Development department
f2 = (dataset_subset.Department == 'Research & Development')
rd_dataset = dataset_subset[f2]
# Building the dashboard of potential causes of attritions
f, axes = plt.subplots(2, 3, figsize=(15, 15))
def plotStackedHistoSubplot(ax, data, xaxis, title):
def _xlsx_create_vendor_summary(self, wb, rpm_table):
ws_vs = wb.create_sheet("vendor summary")
sm_table = self._get_summary_table(rpm_table)
for row in dataframe_to_rows(sm_table, index=False, header=True):
ws_vs.append(row)
(
header_font,
header_border,
header_fill,
) = self._style.get_rpm_xslx_table_header()
for cell in ws_vs[1]:
cell.font = header_font
cell.border = header_border
cell.fill = header_fill
for row in ws_vs[f"A1:G{len(sm_table.index)+1}"]:
for cell in row:
cell.border = header_border
last_record_row_no = len(sm_table.index) + 1
(
great_font,
great_border,
great_fill,
) = self._style.get_rpm_xslx_table_great_row()
great_row_style = DifferentialStyle(
font=great_font,
border=great_border,
fill=great_fill,
)
great_row = Rule(type="expression", dxf=great_row_style)
great_row.formula = [
'AND($A2 <> "", VALUE(LEFT($C2, FIND(" ",$C2)-1))=$B2, VALUE(LEFT($D2, FIND(" ", $D2) - 1)) = $B2, VALUE(LEFT($E2, FIND(" ", $E2) - 1)) = $B2, VALUE(LEFT($F2, FIND(" ", $F2)-1))=$B2, VALUE(LEFT($G2, FIND(" ", $G2) - 1))=0)'
]
ws_vs.conditional_formatting.add(f"A2:G{last_record_row_no}",
great_row)
ipt_style = self._get_important_failed_style()
ctc_style = self._get_critical_failed_style()
empty_vendor = Rule(type="expression", dxf=ipt_style)
empty_vendor.formula = ['$A2 = ""']
ws_vs.conditional_formatting.add(f"A2:A{last_record_row_no}",
empty_vendor)
supported_failed = Rule(type="expression", dxf=ctc_style)
supported_failed.formula = [
'VALUE(LEFT($C2, FIND(" ", $C2) - 1)) <> $B2'
]
ws_vs.conditional_formatting.add(f"C2:C{last_record_row_no}",
supported_failed)
license_check = Rule(type="expression", dxf=ipt_style)
license_check.formula = ['VALUE(LEFT($D2, FIND(" ", $D2) - 1)) <> $B2']
ws_vs.conditional_formatting.add(f"D2:D{last_record_row_no}",
license_check)
sig_check = Rule(type="expression", dxf=ipt_style)
sig_check.formula = ['VALUE(LEFT($E2, FIND(" ", $E2) - 1)) <> $B2']
ws_vs.conditional_formatting.add(f"E2:E{last_record_row_no}",
sig_check)
wm_check = Rule(type="expression", dxf=ipt_style)
wm_check.formula = ['VALUE(LEFT($F2, FIND(" ", $F2) - 1)) <> $B2']
ws_vs.conditional_formatting.add(f"F2:F{last_record_row_no}", wm_check)
sym_failed = Rule(type="expression", dxf=ctc_style)
sym_failed.formula = ['VALUE(LEFT($G2, FIND(" ", $G2) - 1)) <> 0']
ws_vs.conditional_formatting.add(f"G2:G{last_record_row_no}",
sym_failed)
def post_formatting():
column_widths = []
for row in ws.iter_rows(3, 55):
for i, cell in enumerate(row):
try:
column_widths[i] = max(column_widths[i], len(str(cell.value)))
except IndexError:
try:
column_widths.append(len(cell.value))
except TypeError:
print("Type Error")
for i, column_width in enumerate(column_widths):
ws.column_dimensions[get_column_letter(i + 1)].width = column_width + 1
ws.conditional_formatting.add(
'D4:D54',
ColorScaleRule(start_type='percentile',
start_value=10,
start_color='ea7d7d',
mid_type='percentile',
mid_value=50,
mid_color='C0C0C0',
end_type='percentile',
end_value=90,
end_color='9de7b1'))
ws.conditional_formatting.add(
'F4:F54',
ColorScaleRule(start_type='percentile',
start_value=10,
start_color='ea7d7d',
mid_type='percentile',
mid_value=50,
mid_color='C0C0C0',
end_type='percentile',
end_value=90,
end_color='9de7b1'))
ws.conditional_formatting.add(
'H4:H54',
ColorScaleRule(start_type='percentile',
start_value=10,
start_color='AA0000',
mid_type='percentile',
mid_value=50,
mid_color='C0C0C0',
end_type='percentile',
end_value=90,
end_color='00AA00'))
ws.conditional_formatting.add(
'J4:J54',
ColorScaleRule(start_type='percentile',
start_value=10,
start_color='ffffff',
mid_type='percentile',
mid_value=50,
mid_color='ffe3a3',
end_type='percentile',
end_value=90,
end_color='ffc73b'))
red_text = Font(color="ffffff")
red_fill = PatternFill(bgColor="131313")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="highlight",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("TSM",B1)))']
ws.conditional_formatting.add('B1:F61', rule)
red_text = Font(color="ffffff")
red_fill = PatternFill(bgColor="149fda")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="highlight",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("C9",B1)))']
ws.conditional_formatting.add('B1:F61', rule)
red_text = Font(color="131313")
red_fill = PatternFill(bgColor="FFFF66")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="highlight",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("DIG",B1)))']
ws.conditional_formatting.add('B1:F61', rule)
red_text = Font(color="ff1d1d")
red_fill = PatternFill(bgColor="131313")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="highlight",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("100T",B1)))']
ws.conditional_formatting.add('B1:F61', rule)
red_text = Font(color="ffffff")
red_fill = PatternFill(bgColor="092f7e")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="highlight",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("TL",B1)))']
ws.conditional_formatting.add('B1:F61', rule)
red_text = Font(color="3399ff")
red_fill = PatternFill(bgColor="131313")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="highlight",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("CLG",B1)))']
ws.conditional_formatting.add('B1:F61', rule)
red_text = Font(color="ffffff")
red_fill = PatternFill(bgColor="001a33")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="highlight",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("EG",B1)))']
ws.conditional_formatting.add('B1:F61', rule)
red_text = Font(color="004d00")
red_fill = PatternFill(bgColor="e6b800")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="highlight",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("FLY",B1)))']
ws.conditional_formatting.add('B1:F61', rule)
red_text = Font(color="33cccc")
red_fill = PatternFill(bgColor="0a2929")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="highlight",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("IMT",B1)))']
ws.conditional_formatting.add('B1:F61', rule)
red_text = Font(color="ffcc33")
red_fill = PatternFill(bgColor="131313")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="highlight",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("GG",B1)))']
ws.conditional_formatting.add('B1:F61', rule)
for rows in ws.iter_rows(min_row=1, max_row=54, min_col=11, max_col=11):
for cell in rows:
cell.fill = PatternFill(bgColor="FFC7CE", fill_type="solid")
ws.column_dimensions['K'].width = .5
def create_spreadsheet(base_channel_report):
# Setup Excel file
filename = "server_os_report.xlsx"
workbook = Workbook()
ws1 = workbook.create_sheet("Sheet_A")
ws1.title = "Overview Linux OS"
ws2 = workbook.create_sheet("Sheet_B")
ws2.title = "Data"
ws3 = workbook.create_sheet("Sheet_C")
ws3.title = "Approver Breakdown"
sheet = workbook["Data"]
for row in base_channel_report:
sheet.append(row)
darkyellow_background = PatternFill(bgColor=colors.DARKYELLOW)
yellow_background = PatternFill(bgColor=colors.YELLOW)
blue_background = PatternFill(bgColor=colors.BLUE)
green_background = PatternFill(bgColor=colors.GREEN)
diff_style7 = DifferentialStyle(fill=darkyellow_background)
rule7 = Rule(type="expression", dxf=diff_style7)
rule7.formula = ["$C1=7"]
sheet.conditional_formatting.add("A1:E600", rule7)
diff_style8 = DifferentialStyle(fill=blue_background)
rule8 = Rule(type="expression", dxf=diff_style8)
rule8.formula = ["$C1=7"]
sheet.conditional_formatting.add("A1:E600", rule8)
diff_style6 = DifferentialStyle(fill=yellow_background)
rule6 = Rule(type="expression", dxf=diff_style6)
rule6.formula = ["$C1=6"]
sheet.conditional_formatting.add("A1:E600", rule6)
diff_style5 = DifferentialStyle(fill=green_background)
rule5 = Rule(type="expression", dxf=diff_style5)
rule5.formula = ["$C1=5"]
sheet.conditional_formatting.add("A1:E600", rule5)
sheet = workbook["Overview Linux OS"]
data = [
['Centos5', '=COUNTIFS(Data!$C$2:$C$600,5, Data!$B$2:$B$600,"Centos")'],
['Centos6', '=COUNTIFS(Data!$C$2:$C$600,6, Data!$B$2:$B$600,"Centos")'],
['Centos7', '=COUNTIFS(Data!$C$2:$C$600,7, Data!$B$2:$B$600,"Centos")'],
['Centos8', '=COUNTIFS(Data!$C$2:$C$600,8, Data!$B$2:$B$600,"Centos")'],
['RedHat5', '=COUNTIFS(Data!$C$2:$C$600,5, Data!$B$2:$B$600,"Redhat")'],
['RedHat6', '=COUNTIFS(Data!$C$2:$C$600,6, Data!$B$2:$B$600,"Redhat")'],
['RedHat7', '=COUNTIFS(Data!$C$2:$C$600,7, Data!$B$2:$B$600,"Redhat")'],
['RedHat8', '=COUNTIFS(Data!$C$2:$C$600,8, Data!$B$2:$B$600,"Redhat")'],
['Unknown', '=COUNTIFS(Data!$C$2:$C$600,0)']
]
for row in data:
sheet.append(row)
pie = PieChart()
labels = Reference(sheet, min_col=1, min_row=2, max_row=9)
data = Reference(sheet, min_col=2, min_row=1, max_row=9)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "OS Breakdown"
pie.height = 20
pie.width = 40
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
sheet.add_chart(pie, "A1")
std=workbook.get_sheet_by_name('Sheet')
workbook.remove_sheet(std)
unique_cost_center = set(x for l in base_channel_report for x in l)
ws3 = workbook.create_sheet("Sheet_C")
ws3.title = "Cost Center Breakdown"
sheet = workbook["Cost Centre Breakdown"]
data =[]
for x in unique_cost_center:
countifs = "=COUNTIFS(Data!$H$2:$H$600,%s)" % x
data.append([x,countifs])
for row in data:
sheet.append(row)
pie = PieChart()
labels = Reference(sheet, min_col=1, min_row=2, max_row=len(data))
data = Reference(sheet, min_col=2, min_row=1, max_row=len(data))
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Cost Center Breakdown"
pie.height = 20
pie.width = 40
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
sheet.add_chart(pie, "A1")
# save file
workbook.save(filename)
def set_scene_rule(sheet):
red_fill = PatternFill(bgColor="FFC7CE")
dxf = DifferentialStyle(fill=red_fill)
r = Rule(type="expression", dxf=dxf)
r.formula = ['=AND(LEFT($C2, 6)<>"SOUND:", $C2<>"")']
sheet.conditional_formatting.add("A2:Y1000", r)
import re
from fuzzywuzzy import fuzz
from operator import itemgetter, add
from openpyxl import worksheet
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, colors
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule
import collections
full_path = os.path.abspath(os.getcwd())
# file_name = r"C:\Users\willlee\Desktop\Manufacturing Issue\2020\Test Station Part Number.xlsx"
red_background = PatternFill(bgColor=colors.BLUE)
diff_style = DifferentialStyle(fill=red_background)
rule = Rule(type="expression", dxf=diff_style)
rule.formula = ["istext($AA2)"] # <---need to change to dynamic
def text_splitter(text, regex_expression, take_which_group=1):
matches = re.finditer(regex_expression, text, re.MULTILINE)
for matchNum, match in enumerate(matches, start=1):
print("Match {matchNum} was found at {start}-{end}: {match}".format(
matchNum=matchNum,
start=match.start(),
end=match.end(),
match=match.group()))
for groupNum in range(0, len(match.groups())):
groupNum = groupNum + 1
def input_monthly_ytd_summary(input_csv_fix=input_csv_fix):
#runs input_csv_fix to fix a csv, then takes the fixed file name to open it and work with the fixed data to start summarizing
fixed_file_name = input_csv_fix()
#uses the fixed file name to open the fixed file
print("Fixing the date...")
summary_data = pd.read_excel(default_base_file_path + fixed_file_name,
header=3)
start = process_time()
#takes the month and year from the data and strings them together into "YR Month" format
summary_data['Date'] = pd.to_datetime(summary_data['Date'])
summary_data['Month_YR'] = summary_data['Date'].dt.year.astype(
str) + " " + summary_data['Date'].dt.month.astype(str)
#makes a list of the month_YR column's unique values to use later in renaming meshed columns
months = summary_data['Month_YR'].unique().tolist()
#changes the list months to datetime so we can get the wanted formatting later when renaming meshed columns
for i in range(len(months)):
months[i] = datetime.strptime(months[i], '%Y %m')
end = process_time()
fix_date_as_datetime_time = "Time to fix date column and store as datetime: " + str(
end - start)
start = process_time()
#groups the data by company, salesperson, and month_yr
print("Grouping data by Company, Salesperson, and Date...")
data_by_month_and_comp = summary_data.groupby(
['Company', 'Salesperson', 'Month_YR']).agg({
'Sales': sum, # Sum Sales per group
'Pounds Shipped': sum, # Sum Pounds Shipped per group
}).reset_index()
end = process_time()
group_by_table_time = "Time to group by the table: " + str(end - start)
#uncomment the below line to get a file (group_by_test.csv) to test the above grouped table
#data_by_month_and_comp.to_csv(default_base_file_path + "group_by_test.csv")
print("Creating multiindex table...")
#found this function online that supports pivoting multiindex, multilevel tables (where duplicated indices are not a problem)
def multiindex_pivot(df, index=None, columns=None, values=None):
if index is None:
names = list(df.index.names)
df = df.reset_index()
else:
names = index
list_index = df[names].values
tuples_index = [tuple(i) for i in list_index] # hashable
df = df.assign(tuples_index=tuples_index)
df = df.pivot(index="tuples_index", columns=columns, values=values)
tuples_index = df.index # reduced
index = pd.MultiIndex.from_tuples(tuples_index, names=names)
df.index = index
return df
start = process_time()
#uses the above function to make a multiindex, multilevel table with the company and salesperson as indexes, the month-yr as one level of columns, the sales and pounds shipped as another level of columns
data_by_month_and_comp_pivot = multiindex_pivot(
data_by_month_and_comp,
index=['Company', 'Salesperson'],
columns='Month_YR',
values=['Sales', 'Pounds Shipped']).reset_index()
end = process_time()
multiindex_table_time = "Time to make the multiindex table: " + str(end -
start)
print("Sorting columns by date (month and year)")
start = process_time()
#fixes number months to abbreviated months in column labels using the sorted months list and converting it to the datetime format we want while still keeping the columns in the right order
data_by_month_and_comp_pivot.columns.set_levels(
[x.strftime('%b %Y') for x in sorted(months)],
1,
inplace=True,
verify_integrity=False)
end = process_time()
fix_months_column_headers_time = "Time to fix the months in column headers: " + str(
end - start)
start = process_time()
print("Adding total sales, pounds, and avg price per pound columns...")
#adds columns for the total sales and total lbs for that company/salesperson during the timeperiod the data runs across
data_by_month_and_comp_pivot.insert(
2, 'Total Sales', data_by_month_and_comp_pivot['Sales'].sum(axis=1))
data_by_month_and_comp_pivot.insert(
3, 'Total Lbs',
data_by_month_and_comp_pivot["Pounds Shipped"].sum(axis=1))
data_by_month_and_comp_pivot.insert(
4, 'Avg Price Per Lb', data_by_month_and_comp_pivot['Total Sales'] /
data_by_month_and_comp_pivot['Total Lbs'])
end = process_time()
make_total_sales_and_lbs_time = "Time to make the total sales and lbs columns: " + str(
end - start)
print("Merging levels in table...")
start = process_time()
#fixes the stacked columns into one column name per column (meshes the column level names)
data_by_month_and_comp_pivot.columns = list(
map(" ".join, data_by_month_and_comp_pivot.columns))
data_by_month_and_comp_pivot.reset_index()
end = process_time()
merge_levels_time = "Time to merge levels on columns: " + str(end - start)
start = process_time()
#saves the data to the fixed csv filename
data_by_month_and_comp_pivot.to_excel(default_base_file_path +
fixed_file_name,
index=None,
header=True)
end = process_time()
ot_export_fixed_time = "Time to export as fixed file: " + str(end - start)
#excel manipulation
print("Messing with Excel formatting...")
start = process_time()
workbook = load_workbook(filename=default_base_file_path + fixed_file_name)
sheet = workbook.active
#inserts 3 rows before header for other stuff
sheet.insert_rows(idx=1, amount=3)
#freezes header row
sheet.freeze_panes = "A5"
#saves the header row to work with formatting
header_row = sheet[4]
#saves a header style
header = NamedStyle(name="header")
header.font = Font(bold=True)
header.border = Border(bottom=Side(border_style="thick"),
left=Side(border_style="thick"),
right=Side(border_style="thick"),
top=Side(border_style="thick"))
header.alignment = Alignment(horizontal="center",
vertical="top",
wrap_text=True)
print("\n")
#applies header style to header row
for cell in header_row:
cell.style = header
#adds totals above months
maxrow = sheet.max_row
#saves the border settings thin and all around
side = Side(border_style="thin", color="000000")
border = Border(left=side, right=side, top=side, bottom=side)
for i in range(6, sheet.max_column + 1):
col = get_column_letter(i)
cell = get_column_letter(i) + "3"
sheet[cell] = f"=SUBTOTAL(9,{col}5:{col}{maxrow})"
sheet[cell].fill = PatternFill(start_color="9BFF9C",
end_color="9BFF9C",
fill_type="solid")
sheet[cell].border = border
sheet[cell].number_format = numbers.BUILTIN_FORMATS[3]
#formats the cell before the totals cells
sheet["E3"] = "Totals:"
sheet["E3"].font = Font(bold=True)
sheet["E3"].fill = PatternFill(start_color="9BFF9C",
end_color="9BFF9C",
fill_type="solid")
sheet["E3"].border = border
print("\n")
sheet["A1"] = "Monthly Sales & Lbs"
sheet["A1"].font = Font(bold=True)
#Adds red background to avg prices that have avg price/lb > $6
red_background = PatternFill(start_color="FC6E5E", end_color="FC6E5E")
diff_style = DifferentialStyle(fill=red_background)
rule = Rule(type="expression", dxf=diff_style)
rule.formula = ["$E5>6"]
sheet.conditional_formatting.add(f"E5:E{maxrow}", rule)
#fixes the number formats
print("Fixing the number formats...")
for i in range(3, sheet.max_column + 1):
col = get_column_letter(i)
for i in range(5, maxrow + 1):
if col != "E":
sheet[f"{col}{i}"].number_format = numbers.BUILTIN_FORMATS[3]
else:
sheet[f"{col}{i}"].number_format = numbers.BUILTIN_FORMATS[4]
#fixes the borders
print("Fixing borders...")
for i in range(1, sheet.max_column + 1):
col = get_column_letter(i)
for i in range(5, maxrow + 1):
sheet[f"{col}{i}"].border = border
#adds filter buttons to columns
sheet.auto_filter.ref = f"A4:{get_column_letter(sheet.max_column)}{maxrow}"
print("Saving the workbook...\n")
#saves the workbook
workbook.save(default_base_file_path + fixed_file_name)
end = process_time()
excel_manip_time = "Time to manipulate excel: " + str(end - start) + "\n"
#prints all the times to do certain sections of code. Commented out unless needed.
print(fix_date_as_datetime_time)
print(group_by_table_time)
print(multiindex_table_time)
print(fix_months_column_headers_time)
print(make_total_sales_and_lbs_time)
print(merge_levels_time)
print(ot_export_fixed_time)
print(excel_manip_time)
