def build_chart_single(ws, project_name, approval_point, td_data_dict):
chart = ScatterChart()
chart.title = str(project_name) + ' last approved business case: ' + str(
approval_point)
chart.style = 18
chart.x_axis.title = 'Time delta for each business case (year intervals)'
#chart.y_axis.title = 'Milestones'
chart.auto_axis = False
'''this code is necessary to calculate min chart value if its greater than zero'''
x_axis_min = min_value(project_name, td_data_dict)
if x_axis_min >= 0:
chart.x_axis.scaling.min = 0
elif x_axis_min < 0:
anchor = x_axis_min % 365
chart.x_axis.scaling.min = x_axis_min - anchor
chart.x_axis.scaling.max = max_value(
project_name, td_data_dict
) # max number (of days) in the x axis. calculated by max_value function
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 7 # hard coded for now - although minor issue as number of bc time deltas static
chart.height = 9 # default is 7.5
chart.width = 21 # default is 15
'''changes units on x and y axis'''
chart.x_axis.majorUnit = 365 # hard coded for now - minor issue as td will normally be in year intervals
# chart.y_axis.majorUnit = 1.0 testing to see if required
'''reverses y axis'''
#chart.x_axis.scaling.orientation = "minMax"
#chart.y_axis.scaling.orientation = "maxMin"
'''makes the x axis cross at the max y value'''
#chart.x_axis.crosses = 'max'
'''removes lable on y axis'''
chart.y_axis.delete = True
#TOD: sort styling
'''styling chart'''
'''formating for titles'''
#font = Font(typeface='Calibri')
#size = 1200 # 12 point size
#cp = CharacterProperties(latin=font, sz=size, b=True) # Bold
#pp = ParagraphProperties(defRPr=cp)
#rtp = RichText(p=[Paragraph(pPr=pp, endParaRPr=cp)])
#chart.x_axis.title.tx.rich.p[0].pPr = pp # x_axis title
#size_2 = 1400
#cp_2 = CharacterProperties(latin=font, sz=size_2, b=True)
#pp_2 = ParagraphProperties(defRPr=cp_2)
#rtp_2 = RichText(p=[Paragraph(pPr=pp_2, endParaRPr=cp_2)])
#chart.title.tx.rich.p[0].pPr = pp_2 # chart title
'''the below assigns series information to the data that has been placed in the chart.
old values are placed first show that they show behind the current values'''
for i in range(0, 18, 3):
xvalues = Reference(ws, min_col=7, min_row=i + 1, max_row=i + 1)
yvalues = Reference(ws, min_col=8, min_row=i + 1, max_row=i + 1)
series = Series(values=yvalues,
xvalues=xvalues,
title="Latest quarter")
chart.series.append(series)
s1 = chart.series[i]
s1.marker.symbol = "diamond"
s1.marker.size = 10
s1.marker.graphicalProperties.solidFill = "c9e243" # Marker filling greenish
s1.marker.graphicalProperties.line.solidFill = "c9e243" # Marker outline greenish
s1.graphicalProperties.line.noFill = True
xvalues = Reference(ws, min_col=7, min_row=i + 2, max_row=i + 2)
yvalues = Reference(ws, min_col=8, min_row=i + 2, max_row=i + 2)
series = Series(values=yvalues, xvalues=xvalues, title="Last quarter")
chart.series.append(series)
s1 = chart.series[i + 1]
s1.marker.symbol = "diamond"
s1.marker.size = 10
s1.marker.graphicalProperties.solidFill = "ced0ff" # Marker filling grey/blue
s1.marker.graphicalProperties.line.solidFill = "ced0ff" # Marker outline grey/blue
s1.graphicalProperties.line.noFill = True
xvalues = Reference(ws, min_col=7, min_row=i + 3, max_row=i + 3)
yvalues = Reference(ws, min_col=8, min_row=i + 3, max_row=i + 3)
series = Series(values=yvalues, xvalues=xvalues, title="Baseline")
chart.series.append(series)
s1 = chart.series[i + 2]
s1.marker.symbol = "diamond"
s1.marker.size = 10
s1.marker.graphicalProperties.solidFill = "8187ff" # Marker filling blue
s1.marker.graphicalProperties.line.solidFill = "8187ff" # Marker outline blue
s1.graphicalProperties.line.noFill = True
ws.add_chart(chart, "K2")
return ws
def build_chart_master(wb):
ws = wb.active
chart = ScatterChart()
chart.title = ' Time deltas overall ' # consider what other titles might be used.
chart.style = 18
chart.x_axis.title = 'Time delta for each business case (year intervals)'
chart.auto_axis = False
'''this code is necessary to calculate min chart value if its greater than zero'''
chart.x_axis.scaling.min = 0 # hard coded
chart.x_axis.scaling.max = 4000 # hard coded
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 21 # hard coded
chart.height = 9 # default is 7.5
chart.width = 21 # default is 15
'''changes units on x and y axis'''
chart.x_axis.majorUnit = 365 # hard coded for now - minor issue as td will normally be in year intervals
# chart.y_axis.majorUnit = 1.0 testing to see if required
'''reverses y axis'''
# chart.x_axis.scaling.orientation = "minMax"
# chart.y_axis.scaling.orientation = "maxMin"
'''makes the x axis cross at the max y value'''
# chart.x_axis.crosses = 'max'
'''removes lable on y axis'''
chart.y_axis.delete = True
# TOD: sort styling
'''styling chart'''
'''formating for titles'''
# font = Font(typeface='Calibri')
# size = 1200 # 12 point size
# cp = CharacterProperties(latin=font, sz=size, b=True) # Bold
# pp = ParagraphProperties(defRPr=cp)
# rtp = RichText(p=[Paragraph(pPr=pp, endParaRPr=cp)])
# chart.x_axis.title.tx.rich.p[0].pPr = pp # x_axis title
# size_2 = 1400
# cp_2 = CharacterProperties(latin=font, sz=size_2, b=True)
# pp_2 = ParagraphProperties(defRPr=cp_2)
# rtp_2 = RichText(p=[Paragraph(pPr=pp_2, endParaRPr=cp_2)])
# chart.title.tx.rich.p[0].pPr = pp_2 # chart title
'''the below assigns series information to the data that has been placed in the chart.
old values are placed first show that they show behind the current values'''
row_start = 1
for i in range(ws.max_row + 1):
for x in range(2):
ws.cell(row=row_start, column=5).value = i + 1
row_start += 1
for i in range(0, ws.max_row + 1, 2):
xvalues = Reference(ws, min_col=4, min_row=i + 1, max_row=i + 1)
yvalues = Reference(ws, min_col=5, min_row=i + 1, max_row=i + 1)
series = Series(values=yvalues,
xvalues=xvalues,
title="Current/Actual")
chart.series.append(series)
s1 = chart.series[i]
s1.marker.symbol = "diamond"
s1.marker.size = 10
s1.marker.graphicalProperties.solidFill = "c9e243" # Marker filling greenish
s1.marker.graphicalProperties.line.solidFill = "c9e243" # Marker outline greenish
s1.graphicalProperties.line.noFill = True
xvalues = Reference(ws, min_col=4, min_row=i + 2, max_row=i + 2)
yvalues = Reference(ws, min_col=5, min_row=i + 2, max_row=i + 2)
series = Series(values=yvalues, xvalues=xvalues, title="Baseline")
chart.series.append(series)
s1 = chart.series[i + 1]
s1.marker.symbol = "diamond"
s1.marker.size = 10
s1.marker.graphicalProperties.solidFill = "8187ff" # Marker filling blue
s1.marker.graphicalProperties.line.solidFill = "8187ff" # Marker outline blue
s1.graphicalProperties.line.noFill = True
ws.add_chart(chart, "K2")
return wb
