def CreateCharts(ws, fileIndex):
chartObj=ScatterChart()
min_col=fileIndex*columnsPerFile+leftColumnOffset
min_row=topRowOffset+2
max_col=fileIndex*columnsPerFile+leftColumnOffset
max_row=maxRows[fileIndex]-cutOffValues[fileIndex]+topRowOffset+1
xValues=Reference(ws, min_col=min_col+headers.index('Time (s)'), min_row=min_row, max_col=max_col+headers.index('Time (s)'), max_row=max_row)
yValues=Reference(ws, min_col=min_col+headers.index('Total volume (uL)'), min_row=min_row, max_col=max_col+headers.index('Total volume (uL)'), max_row=max_row)
serObj=Series(yValues, xValues, title='Volume vs. time')
chartObj.append(serObj)
chartObj.x_axis.title='Time (s)'
chartObj.y_axis.title='Volume (uL)'
chartObj.legend=None
#<source=DrFunn1 at https://python-forum.io/Thread-How-to-change-font-size-of-chart-title-and-axis-title>
font = Font(typeface='Verdana')
size = 1000
cp = CharacterProperties(latin=font, sz=size, b=False)
pp = ParagraphProperties(defRPr=cp)
rtp = RichText(p=[Paragraph(pPr=pp, endParaRPr=cp)])
chartObj.x_axis.txPr = rtp
chartObj.y_axis.txPr = rtp
chartObj.x_axis.title.tx.rich.p[0].pPr = pp
chartObj.y_axis.title.tx.rich.p[0].pPr = pp
#</source>
ws.add_chart(chartObj, get_column_letter(fileIndex*columnsPerFile+leftColumnOffset)+'1')
def title_maker(text):
title = Title()
paraprops = ParagraphProperties()
paraprops.defRPr = CharacterProperties()
paras = [Paragraph(r=[RegularTextRun(t=s)], pPr=paraprops) for s in text.split("\n")]
title.tx.rich.paragraphs = paras
return title
def __init__(self,
bodyPr=None,
lstStyle=None,
p=None,
):
if bodyPr is None:
bodyPr = RichTextProperties()
self.bodyPr = bodyPr
self.lstStyle = lstStyle
if p is None:
p = [Paragraph()]
self.p = p
def format_chart(chart: ScatterChart, x_axis_title: str, y_axis_title: str, title: str):
chart.height = 15
chart.width = 30
chart.x_axis.tickLblPos = "low"
chart.title = title
chart.x_axis.title = x_axis_title
chart.y_axis.title = y_axis_title
font = drawing.text.Font(typeface='Arial')
cp_axis = CharacterProperties(latin=font, sz=1600, b=True)
cp_axis_title = CharacterProperties(latin=font, sz=1600)
cp_title = CharacterProperties(latin=font, sz=1200)
chart.y_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis), endParaRPr=cp_axis)])
chart.y_axis.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis),
endParaRPr=cp_axis_title)])
chart.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis), endParaRPr=cp_axis)])
chart.x_axis.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis),
endParaRPr=cp_axis_title)])
chart.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_title), endParaRPr=cp_title)])
def setChartFontSize(chart, isDataLabel):
chart.title.tx.rich.p[0].r.rPr = cpTitle
chart.x_axis.title.tx.rich.p[0].r.rPr = cpAxisTitle
chart.y_axis.title.tx.rich.p[0].r.rPr = cpAxisTitle
chart.x_axis.txPr = RichText(p=[
Paragraph(pPr=ParagraphProperties(defRPr=cpAxisText),
endParaRPr=cpAxisText)
])
chart.y_axis.txPr = RichText(p=[
Paragraph(pPr=ParagraphProperties(defRPr=cpAxisText),
endParaRPr=cpAxisText)
])
chart.legend.txPr = RichText(p=[
Paragraph(pPr=ParagraphProperties(defRPr=cpLegend),
endParaRPr=cpLegend)
])
if isDataLabel:
chart.dataLabels = DataLabelList()
chart.dataLabels.txPr = RichText(p=[
Paragraph(pPr=ParagraphProperties(defRPr=cpDataLabel),
endParaRPr=cpDataLabel)
])
chart.dataLabels.showVal = True
def style_chart(text, chart):
# Style chart title
font = openpyxl.drawing.text.Font(typeface='Calibri')
size = 1100 # 14 point size
# chart.title = Title()
# paraprops = ParagraphProperties()
# paraprops.defRPr = CharacterProperties(latin=font, sz=size, b=False)
# paras = Paragraph(pPr=paraprops, endParaRPr=paraprops.defRPr)
# X and Y axes numbers
cp = CharacterProperties(latin=font, sz=size, b=False) # Not bold
pp = ParagraphProperties(defRPr=cp)
rtp = RichText(p=[Paragraph(pPr=pp, endParaRPr=cp)])
chart.x_axis.txPr = rtp # Works!
chart.y_axis.txPr = rtp # Works!
# X and Y axes titles
chart.x_axis.title.tx.rich.p[0].pPr = pp # Works!
chart.y_axis.title.tx.rich.p[0].pPr = pp # Works!
def mk_cpuabsolute_sheet(wb, _options):
"""
Purpose:
To create the Workbook(excel file) with Relative CPU usage graphs using the data gathered from vmstat
Parameters:
wb - The current Workbook
_options - the arguments passed to the script
"""
# --- 'CPU' Sheet
ws = wb.create_sheet('CPU_Absolute')
looprow_marker = 1 # Keeps a track of the next row that a data record will be inserted
loopcol = 40 # increases in 4 columns for each server
graphpos = 2 # Which row to place the First Graph
# "Coloring" the sheet's background, to hide the data.
area = Reference(ws, min_col=1, min_row=1, max_row=500, max_col=40)
for cell in area.cells:
ws[cell].fill = PatternFill(bgColor=BGCOLOR,
fgColor=BGCOLOR,
fill_type="solid")
ws[cell].font = Font(color=BGCOLOR)
for server in _options['server']:
print('Generating "lparstat" reports for {} ...'.format(server))
cpu_type = cpu_mode = ''
vprocs = 0
looprow = looprow_marker
ws.cell(row=1, column=loopcol, value='Date')
ws.cell(row=1, column=loopcol + 3, value='Average Busy')
ws.cell(row=1, column=loopcol + 2, value='Entitled capacity')
ws.cell(row=1, column=loopcol + 1, value='Virtual Procs')
# Querying Server and its data from Database
server_entries = Lparstat().query_by('servername', server)
highest_vprocs = 0
physc_usage_list = []
for entry in server_entries:
date = entry.date
cpu_type = entry.cpu_type
cpu_mode = entry.cpu_mode
vprocs = entry.vprocs
if vprocs > highest_vprocs:
highest_vprocs = vprocs
ent_cap = entry.ent_cap
physc_peak_average = entry.peak_avg_physc
# if the entry's date is newer than range's start and older then range's stop
if _options['startdate'] <= date <= _options['enddate']:
# if we want to skip weekend days
if not _options['dontskip'] and date.weekday() < 5:
looprow = looprow + 1
ws.cell(row=looprow, column=loopcol, value=date)
ws.cell(row=looprow,
column=loopcol + 3,
value=physc_peak_average)
ws.cell(row=looprow, column=loopcol + 2, value=ent_cap)
ws.cell(row=looprow, column=loopcol + 1, value=vprocs)
physc_usage_list.append(physc_peak_average)
# if we want to include weekends
elif _options['dontskip']:
looprow = looprow + 1
ws.cell(row=looprow, column=loopcol, value=date)
ws.cell(row=looprow,
column=loopcol + 3,
value=physc_peak_average)
ws.cell(row=looprow, column=loopcol + 2, value=ent_cap)
ws.cell(row=looprow, column=loopcol + 1, value=vprocs)
try:
# Setting the FORECAST data
for i in range(looprow, 2 + int(looprow * (1 + FORECAST))):
ws.cell(row=i, column=loopcol).value = ws.cell(
row=i - 1, column=loopcol).value + timedelta(days=1)
ws.cell(row=i, column=loopcol + 1).value = ws.cell(
row=i - 1, column=loopcol + 1).value
ws.cell(row=i, column=loopcol + 2).value = ws.cell(
row=i - 1, column=loopcol + 2).value
# --- Setting Chart Properties
chart = AreaChart()
chart.title = '{} - Physical Cores ({} {}/{} Virt. Procs)' \
.format(server, vprocs, cpu_type, cpu_mode)
chart.style = 3
chart.x_axis.number_format = 'dd/mm'
chart.x_axis.majorTimeUnit = "days"
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = highest_vprocs
# --- All this to rotate the 'date' axis in 270 degrees
rot = RichTextProperties(vert='vert270')
axis = CharacterProperties()
chart.x_axis.textProperties = \
RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)], bodyPr=rot)
# ---
chart.height = 10
chart.width = 25
chart.legend.position = "t"
chart.legend.layout = Layout(manualLayout=ManualLayout(
yMode='edge', xMode='edge', h=0.1, w=0.8))
chart.plot_area.layout = Layout(
manualLayout=ManualLayout(yMode='edge', xMode='edge'))
pf = PatternFillProperties(prst='pct20',
fgClr=ColorChoice(srgbClr='8FAF52'))
chart.plot_area.graphicalProperties = GraphicalProperties(
pattFill=pf)
# --- Creating and formatting the Chart's Series
# Virtual Procs Series
values = Reference(ws,
min_col=loopcol + 1,
min_row=1,
max_row=1 + int(looprow * (1 + FORECAST)))
serie = Series(values, title_from_data=True) # creating the Serie
pf = PatternFillProperties(prst='smGrid',
fgClr=ColorChoice(srgbClr='D4ECBA'))
serie.graphicalProperties = GraphicalProperties(pattFill=pf)
serie.graphicalProperties.line.width = 25000
chart.series.append(serie)
# Formatting Entitled Capacity Series
values = Reference(ws,
min_col=loopcol + 2,
min_row=1,
max_row=1 + int(looprow * (1 + FORECAST)))
serie = Series(values, title_from_data=True) # creating the Serie
serie.graphicalProperties = GraphicalProperties(solidFill='D4ECBA')
serie.graphicalProperties.line.width = 25000
chart.series.append(serie)
# Formatting Physc Busy Series
values = Reference(ws,
min_col=loopcol + 3,
min_row=1,
max_row=looprow)
serie = Series(values, title_from_data=True) # creating the Serie
serie.graphicalProperties = GraphicalProperties()
serie.graphicalProperties.line.solidFill = '558ED5'
serie.graphicalProperties.solidFill = MyColorChoice(
srgbClr=MyRGBColor('558ED5', alpha=35000))
serie.graphicalProperties.line.width = 25000
chart.series.append(serie)
# Creating and Formatting Trend Line
chart.series[2].trendline = Trendline(
forward=str(1 + int(looprow * FORECAST)))
chart.series[
2].trendline.graphicalProperties = GraphicalProperties()
chart.series[
2].trendline.graphicalProperties.line.solidFill = ColorChoice(
prstClr='purple')
chart.series[2].trendline.graphicalProperties.line.width = 25000
# Setting the 'date' x-axis, with forecast of 33% of the time range
dates = Reference(ws,
min_col=loopcol,
min_row=2,
max_row=1 + int(looprow * (1 + FORECAST)))
chart.set_categories(dates)
# Set the Starting column for the next server
loopcol = loopcol + 4
# Adding the Chart
ws.add_chart(chart, "A{}".format(graphpos))
# Adding The Comments Session
# Setting Analysis Column size ---
ws.column_dimensions['P'].width = 75
ws.merge_cells('P{}:P{}'.format(graphpos + 1, graphpos + 17))
ws['P{}'.format(graphpos)].font = Font(name=FONTNAME,
size=14,
color=FONTCOLOR)
ws['P{}'.format(graphpos)].value = '{} Analysis:'.format(server)
ws['P{}'.format(graphpos + 1)].alignment = Alignment(
horizontal='left', vertical='top', wrapText=True)
area = Reference(ws,
min_col=16,
min_row=graphpos + 1,
max_row=graphpos + 17,
max_col=16)
for cell in area.cells:
ws[cell].font = Font(name=FONTNAME, size=11, color=FONTCOLOR)
ws[cell].fill = PatternFill(fill_type="solid",
start_color='FFFFFF',
end_color='FFFFFF')
ws[cell].border = BORDER
# Updating the Graphic Positioner
graphpos = graphpos + 19
except (ValueError, TypeError) as err_msg:
print 'Error while processing lparstat data for server {}! Could not create the Chart.\n' \
'Extend error message: \n' \
'{}'.format(server, err_msg)
def mk_cpurelative_sheet(wb, _options):
"""
Purpose:
To create the Workbook(excel file) with Relative CPU usage graphs using the data gathered from vmstat
Parameters:
wb - The current Workbook
_options - the arguments passed to the script
"""
# --- 'CPU' Sheet
ws = wb.create_sheet('CPU_Relative')
looprow_marker = 1 # Keeps a track of the next row that a data record will be inserted
loopcol = 40 # increases in 4 columns for each server
graphpos = 2 # Which row to place the First Graph
# "Coloring" the sheet's background, to hide the data.
area = Reference(ws, min_col=1, min_row=1, max_row=500, max_col=40)
for cell in area.cells:
ws[cell].fill = PatternFill(bgColor=BGCOLOR,
fgColor=BGCOLOR,
fill_type="solid")
ws[cell].font = Font(color=BGCOLOR)
for server in _options['server']:
print('Generating "vmstat" reports for {} ...'.format(server))
looprow = looprow_marker
ws.cell(row=1, column=loopcol, value='Date')
ws.cell(row=1, column=loopcol + 1, value='Average Busy')
ws.cell(row=1,
column=loopcol + 2,
value='% of Samples above {}% Cpu Usage'.format(100 -
TRESHOLDW))
ws.cell(row=1,
column=loopcol + 3,
value='% of Samples above {}% Cpu Usage'.format(100 -
TRESHOLDC))
# Querying Server and its data from Database
server_entries = Vmstat().query_by('servername', server)
# Looping over server's entries
for entry in server_entries:
date = entry.date
average = entry.peak_avg_busy
warning = entry.peak_avg_warning
critical = entry.peak_avg_critical
# if the entry's date is newer than range's start and older then range's stop
if _options['startdate'] <= date <= _options['enddate']:
# if we want to skip weekend days
if not _options['dontskip'] and date.weekday() < 5:
looprow = looprow + 1
ws.cell(row=looprow, column=loopcol, value=date)
ws.cell(row=looprow, column=loopcol + 1, value=average)
ws.cell(row=looprow, column=loopcol + 2, value=warning)
ws.cell(row=looprow, column=loopcol + 3, value=critical)
# if we want to include weekends
elif _options['dontskip']:
looprow = looprow + 1
ws.cell(row=looprow, column=loopcol, value=date)
ws.cell(row=looprow, column=loopcol + 1, value=average)
ws.cell(row=looprow, column=loopcol + 2, value=warning)
ws.cell(row=looprow, column=loopcol + 3, value=critical)
try:
# --- Setting Chart Properties
chart = LineChart()
chart.title = '{} %CPU'.format(server)
chart.style = 3
chart.x_axis.number_format = 'dd/mm'
chart.x_axis.majorTimeUnit = "days"
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 100
# --- All this to rotate the 'date' axis in 270 degrees
rot = RichTextProperties(vert='vert270')
axis = CharacterProperties()
chart.x_axis.textProperties = \
RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)], bodyPr=rot)
# ---
chart.height = 10
chart.width = 25
chart.legend.position = "t"
chart.legend.layout = Layout(manualLayout=ManualLayout(
yMode='edge', xMode='edge', h=0.1, w=0.8))
chart.plot_area.layout = Layout(
manualLayout=ManualLayout(yMode='edge', xMode='edge'))
pf = PatternFillProperties(prst='pct25',
fgClr=ColorChoice(srgbClr='8FAF52'))
chart.plot_area.graphicalProperties = GraphicalProperties(
pattFill=pf)
# --- Looping over Columns to build the Chart
# columns Average, Warning and Critical
for col in range(loopcol + 1, loopcol + 4):
values = Reference(ws, min_col=col, min_row=1, max_row=looprow)
series = Series(values,
title_from_data=True) # creating the Serie
chart.series.append(series)
# --- Setting Plot Area Format
# Formatting Series
chart.series[0].graphicalProperties.line = LineProperties(
solidFill=ColorChoice(prstClr='green'))
chart.series[1].graphicalProperties.line = LineProperties(
solidFill=ColorChoice(prstClr='orange'))
chart.series[2].graphicalProperties.line = LineProperties(
solidFill=ColorChoice(prstClr='red'))
# Creating and Formatting Trend Line
chart.series[0].trendline = Trendline(
forward=str(1 + int(looprow * FORECAST)))
chart.series[
0].trendline.graphicalProperties = GraphicalProperties()
chart.series[
0].trendline.graphicalProperties.line.solidFill = ColorChoice(
prstClr='purple')
chart.series[0].trendline.graphicalProperties.line.width = 25000
# Setting the 'date' x-axis, with forecast of 33% of the time range
i = 0
for i in range(looprow, 2 + int(looprow * (1 + FORECAST))):
ws.cell(row=i, column=loopcol).value = \
ws.cell(row=i - 1, column=loopcol).value + timedelta(days=1)
dates = Reference(ws, min_col=loopcol, min_row=2, max_row=i)
chart.set_categories(dates)
# Set the Starting column for the next server
loopcol = loopcol + 4
# Adding the Chart
ws.add_chart(chart, "A{}".format(graphpos))
# Adding The Comments Session
# Setting Analysis Column size ---
ws.column_dimensions['P'].width = 75
ws.merge_cells('P{}:P{}'.format(graphpos + 1, graphpos + 17))
ws['P{}'.format(graphpos)].font = Font(name=FONTNAME,
size=14,
color=FONTCOLOR)
ws['P{}'.format(graphpos)].value = '{} Analysis:'.format(server)
ws['P{}'.format(graphpos + 1)].alignment = Alignment(
horizontal='left', vertical='top', wrapText=True)
area = Reference(ws,
min_col=16,
min_row=graphpos + 1,
max_row=graphpos + 17,
max_col=16)
for cell in area.cells:
ws[cell].font = Font(name=FONTNAME, size=11, color=FONTCOLOR)
ws[cell].fill = PatternFill(fill_type="solid",
start_color='FFFFFF',
end_color='FFFFFF')
ws[cell].border = BORDER
# Updating the Graphic Positioner
graphpos = graphpos + 19
except ValueError as err_msg:
print 'Error while processing vmstat data for server {}! Could not create the Chart.\n' \
'Extend error message: \n' \
'{}'.format(server, err_msg)
def place_in_excel(name, latest_fin_data, last_fin_data, baseline_fin_data):
'''
function places all data into excel spreadsheet and creates chart.
data is placed into sheet in reverse order (see how data_list is ordered) so that most recent
data is displayed on right hand side of the data table
'''
wb = Workbook()
ws = wb.active
data_list = [baseline_fin_data, last_fin_data, latest_fin_data]
count = 0
'''places in raw/reported data'''
for data in data_list:
for i, key in enumerate(capture_rdel):
try:
ws.cell(row=i + 3, column=2 + count, value=data[name][key])
except KeyError:
ws.cell(row=i + 3, column=2 + count, value=0)
for i, key in enumerate(capture_cdel):
try:
ws.cell(row=i + 3, column=3 + count, value=data[name][key])
except KeyError:
ws.cell(row=i + 3, column=3 + count, value=0)
for i, key in enumerate(capture_ng):
try:
ws.cell(row=i + 3, column=4 + count, value=data[name][key])
except KeyError:
ws.cell(row=i + 3, column=4 + count, value=0)
count += 4
'''places in totals'''
baseline_totals = calculate_totals(name, baseline_fin_data)
last_q_totals = calculate_totals(name, last_fin_data)
latest_q_totals = calculate_totals(name, latest_fin_data)
total_list = [baseline_totals, last_q_totals, latest_q_totals]
c = 0
for l in total_list:
for i, total in enumerate(l):
ws.cell(row=i + 3, column=5 + c, value=total)
c += 4
'''labeling data in table'''
labeling_list_quarter = ['Baseline', 'Last Quarter', 'Latest quarter']
ws.cell(row=1, column=2, value=labeling_list_quarter[0])
ws.cell(row=1, column=6, value=labeling_list_quarter[1])
ws.cell(row=1, column=10, value=labeling_list_quarter[2])
labeling_list_type = ['RDEL', 'CDEL', 'Non-Gov', 'Total']
repeat = 3
c = 0
while repeat > 0:
for i, label in enumerate(labeling_list_type):
ws.cell(row=2, column=2 + i + c, value=label)
c += 4
repeat -= 1
labeling_list_year = [
'Spend', '19/20', '20/21', '21/22', '22/23', '23/24', '24/25', '25/26',
'26/27', '27/28', '28/29', 'Unprofiled'
]
for i, label in enumerate(labeling_list_year):
ws.cell(row=2 + i, column=1, value=label)
'''process for showing total cost profile. starting with data'''
row_start = 16
for x, l in enumerate(total_list):
for i, total in enumerate(l):
ws.cell(row=i + row_start, column=x + 2, value=total)
'''data for graph labeling'''
for i, quarter in enumerate(labeling_list_quarter):
ws.cell(row=15, column=i + 2, value=quarter)
for i, label in enumerate(labeling_list_year):
ws.cell(row=15 + i, column=1, value=label)
chart = LineChart()
chart.title = str(name) + ' Cost Profile'
chart.style = 4
chart.x_axis.title = 'Financial Year'
chart.y_axis.title = 'Cost £m'
'''styling chart'''
# axis 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
chart.y_axis.title.tx.rich.p[0].pPr = pp
# chart.title.tx.rich.p[0].pPr = pp
# 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
'''unprofiled costs not included in the chart'''
data = Reference(ws, min_col=2, min_row=15, max_col=4, max_row=25)
chart.add_data(data, titles_from_data=True)
cats = Reference(ws, min_col=1, min_row=16, max_row=25)
chart.set_categories(cats)
s3 = chart.series[0]
s3.graphicalProperties.line.solidFill = "cfcfea" # light blue
s8 = chart.series[1]
s8.graphicalProperties.line.solidFill = "5097a4" # medium blue
s9 = chart.series[2]
s9.graphicalProperties.line.solidFill = "0e2f44" # dark blue'''
ws.add_chart(chart, "H15")
'''process for creating income chart'''
baseline_total_income = calculate_income_totals(name, baseline_fin_data)
last_q_total_income = calculate_income_totals(name, last_fin_data)
latest_q_total_income = calculate_income_totals(name, latest_fin_data)
total_income_list = [
baseline_total_income, last_q_total_income, latest_q_total_income
]
if sum(latest_q_total_income) is not 0:
for x, l in enumerate(total_income_list):
for i, total in enumerate(l):
ws.cell(row=i + 32, column=x + 2, value=total)
'''data for graph labeling'''
for i, quarter in enumerate(labeling_list_quarter):
ws.cell(row=32, column=i + 2, value=quarter)
for i, label in enumerate(labeling_list_year):
ws.cell(row=32 + i, column=1, value=label)
'''income graph'''
chart = LineChart()
chart.title = str(name) + ' Income Profile'
chart.style = 4
chart.x_axis.title = 'Financial Year'
chart.y_axis.title = 'Cost £m'
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
chart.y_axis.title.tx.rich.p[0].pPr = pp
# chart.title.tx.rich.p[0].pPr = pp
# 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
#unprofiled costs not included in the chart
data = Reference(ws, min_col=2, min_row=32, max_col=4, max_row=42)
chart.add_data(data, titles_from_data=True)
cats = Reference(ws, min_col=1, min_row=33, max_row=42)
chart.set_categories(cats)
'''
keeping as colour coding is useful
s1 = chart.series[0]
s1.graphicalProperties.line.solidFill = "cfcfea" #light blue
s2 = chart.series[1]
s2.graphicalProperties.line.solidFill = "e2f1bb" #light green
s3 = chart.series[2]
s3.graphicalProperties.line.solidFill = "eaba9d" #light red
s4 = chart.series[3]
s4.graphicalProperties.line.solidFil = "5097a4" #medium blue
s5 = chart.series[4]
s5.graphicalProperties.line.solidFill = "a0db8e" #medium green
s6 = chart.series[5]
s6.graphicalProperties.line.solidFill = "b77575" #medium red
s7 = chart.series[6]
s7.graphicalProperties.line.solidFil = "0e2f44" #dark blue
s8 = chart.series[7]
s8.graphicalProperties.line.solidFill = "29ab87" #dark green
s9 = chart.series[8]
s9.graphicalProperties.line.solidFill = "691c1c" #dark red
'''
s3 = chart.series[0]
s3.graphicalProperties.line.solidFill = "e2f1bb" # light green
s8 = chart.series[1]
s8.graphicalProperties.line.solidFill = "a0db8e" # medium green
s9 = chart.series[2]
s9.graphicalProperties.line.solidFill = "29ab87" # dark green
ws.add_chart(chart, "H31")
else:
pass
return wb
def __init__(self,
filename,
sheet_name=[],
chart_title=[],
xaxis_title=[],
yaxis_title=[],
stack=[],
chart_position="F10",
whiten_all_area=True,
font_name='Calibri',
axis_title_font_size=10,
output_filename=None):
wb = load_workbook(filename)
counts = len(sheet_name)
wsDict = {}
for count in range(counts):
wsDict['ws' + str(count)] = wb[sheet_name[count]]
chart = BarChart()
chart.style = 10
chart.type = "col"
if stack[count] == True:
chart.grouping = "stacked"
chart.overlap = 100
chart.title = chart_title[count]
chart.y_axis.title = xaxis_title[count]
chart.x_axis.title = yaxis_title[count]
data = Reference(wsDict['ws' + str(count)],
min_col=wsDict['ws' + str(count)].min_column + 1,
min_row=wsDict['ws' + str(count)].min_row,
max_row=wsDict['ws' + str(count)].max_row,
max_col=wsDict['ws' + str(count)].max_column)
cats = Reference(wsDict['ws' + str(count)],
min_col=wsDict['ws' + str(count)].min_column,
min_row=wsDict['ws' + str(count)].min_row + 1,
max_row=wsDict['ws' + str(count)].max_row,
max_col=wsDict['ws' + str(count)].min_column)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
## set axis title property??
font_test = draw_Font(typeface=font_name)
cp = CharacterProperties(latin=font_test,
sz=axis_title_font_size * 100)
chart.x_axis.txPr = RichText(p=[
Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)
])
chart.y_axis.txPr = RichText(p=[
Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)
])
xPara = [
Paragraph(pPr=ParagraphProperties(defRPr=cp),
r=RegularTextRun(t=s))
for s in xaxis_title[count].split("\n")
]
yPara = [
Paragraph(pPr=ParagraphProperties(defRPr=cp),
r=RegularTextRun(t=s))
for s in yaxis_title[count].split("\n")
]
chart.x_axis.title.tx.rich.paragraphs = xPara
chart.y_axis.title.tx.rich.paragraphs = yPara
## hide legend
if not stack[count]:
chart.legend = None
wsDict['ws' + str(count)].add_chart(chart, chart_position)
## whiten data
if whiten_all_area:
table = wsDict['ws' + str(count)]["A1":"AZ200"]
color = 'FFFFFFFF'
font = Font(bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=color)
fill = PatternFill(start_color=color,
end_color=color,
fill_type="solid")
for tr in table:
for td in tr:
td.font = font
td.border = None
td.fill = fill
### data label
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
if output_filename is None:
wb.save(filename=filename)
else:
wb.save(filename=output_filename)
#
# barchart_creater(filename = 'temp0.xlsx',sheet_name=["Issue Count by Due Date","Issue Count By Owner","MTM Issue Comparison"],
# chart_title=["Number of Issues By Due Date","Number of outstanding Issues by Owner","Number of Issues comparison month to month by Source"],
# xaxis_title = ["Number of Issues",'Issue Owner','Month'],
# yaxis_title=['Number of Days till Due','Number of Issues','Number Of Issues'],stack = [False,False,True])
def createTwoAxisChart(wb,
readType,
rows,
titles,
lineNumber,
place="C10",
max_row=-1,
max_col=-1,
min_row=1,
min_col=1,
isDesc=True,
isMark=True,
isLineWidth=True,
isDataLable=True):
ws = excel_util.creatSheet(wb, False, titles[0])
excel_util.putRowsToSheet(rows, ws)
if max_row == -1:
max_row = len(rows)
if max_col == -1:
max_col = len(rows[0])
c1 = LineChart()
c2 = BarChart()
if readType == "row":
addChartDataBtType(ws, readType, c2, max_row, max_col, lineNumber,
min_col)
addChartDataBtType(ws, readType, c1, lineNumber, max_col, min_row,
min_col)
elif readType == "col":
addChartDataBtType(ws, readType, c2, max_row, max_col, min_row,
lineNumber)
addChartDataBtType(ws, readType, c1, max_row, lineNumber, min_row,
min_col)
c1.y_axis.axId = 200
c1.y_axis.crosses = "max"
c1 += c2
c2.y_axis.title = titles[3]
c2.y_axis.title.tx.rich.p[0].r.rPr = cpAxisTitle
c2.y_axis.txPr = RichText(p=[
Paragraph(pPr=ParagraphProperties(defRPr=cpAxisText),
endParaRPr=cpAxisText)
])
c2.dataLabels = DataLabelList()
c2.dataLabels.txPr = RichText(p=[
Paragraph(pPr=ParagraphProperties(defRPr=cpDataLabel),
endParaRPr=cpDataLabel)
])
c2.dataLabels.showVal = True
setChartTitleAndSize(c1, titles)
setChartByParameter(c1, isDesc, isMark, isLineWidth, isDataLable)
# 逆序
if isDesc:
c1.y_axis.scaling.orientation = "maxMin"
c1.x_axis.crosses = "min"
ws.add_chart(c1, place)
def enregistre_resultat_resume(recap, ligne, colonne, feuille, nb_postes):
styleFond, styleTeteTab, styleVal, styleTableau, styleEntree, styleTitre = p.STYLES_EXCEL
feuille.cell(row=ligne, column=1+colonne).value ="Regroupement de résultats, les postes principaux"
feuille.merge_cells('B'+str(ligne)+':L'+str(ligne))
feuille.cell(row=ligne, column=1+colonne).style = styleEntree
feuille.cell(row=ligne, column=1+colonne).font = Font(bold=True)
ligne += 2
for li in range(len(recap)):
for co in range(len(recap[0])):
feuille.cell(row=ligne, column=1+colonne+co).value = recap[li][co]
if li == 0:
feuille.cell(row=ligne, column=1+colonne+co).style = styleTeteTab
elif co<1:
feuille.cell(row=ligne, column=1+colonne+co).style = styleEntree
else:
feuille.cell(row=ligne, column=1+colonne+co).style = styleVal
feuille.cell(row = ligne, column = 1+colonne+co).number_format = '0.0'
ligne+=1
feuille.cell(row=ligne, column=2).value = "EGES tot dues à la tourbe"
for c in range(3, 11):
feuille.cell(row=ligne, column=c).style = styleVal
feuille.cell(row=ligne, column=8).number_format = '#.0'
feuille.cell(row=ligne, column=2).style = styleEntree
feuille.cell(row=ligne, column=3).value = p.COMPARAISON_TOURBE[0]
feuille.cell(row=ligne, column=3).number_format = '#.0'
feuille.cell(row=ligne, column=6).value = p.COMPARAISON_TOURBE[1]
feuille.cell(row=ligne, column=6).number_format = '#.0'
feuille.cell(row=ligne, column=8).value = p.COMPARAISON_TOURBE[2]
feuille.cell(row=ligne, column=8).number_format = '#.0'
feuille.cell(row=ligne, column=10).value = "=SUM(C"+str(ligne)+":I"+str(ligne)+")"
feuille.cell(row=ligne, column=10).number_format = '#.0'
ligne +=1
for li in range(1,len(recap)-1):
for co in range(len(recap[0])):
if co<1:
feuille.cell(row=ligne, column=1+colonne+co).value = recap[li][co]+ " (%)"
feuille.cell(row=ligne, column=1+colonne+co).style = styleEntree
elif co <=len(recap[0])-1 and li ==1:
feuille.cell(row=ligne, column=1+colonne+co).value = recap[li][co]/recap[1][-1]
feuille.cell(row=ligne, column=1+colonne+co).style = styleVal
feuille.cell(row=ligne, column=1+colonne+co).number_format = '0.0%'
elif co <=len(recap[0])-1 and li ==2:
feuille.cell(row=ligne, column=1+colonne+co).value = recap[li][co]/recap[2][-1]
feuille.cell(row=ligne, column=1+colonne+co).style = styleVal
feuille.cell(row=ligne, column=1+colonne+co).number_format = '0.0%'
ligne+=1
offset3 = 20+(2*nb_postes)
sites = Reference(feuille, min_row = offset3, min_col = 2+colonne, max_col = len(recap[0])+colonne-1)
graphique3 = BarChart()
graphique3.title = "Emissions totales des sites par poste"
graphique3.type = "col";graphique3.style= 10;graphique3.grouping='standard';graphique3.overlap = 25
graphique3.height = 12;graphique3.width = 25
graphique3.x_axis.scaling.orientation = "maxMin"
cp = CharacterProperties(sz=1000) #Taille du label entre 100 et 400 000
graphique3.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)])
graphique3.varyColors=True
valeurs3 = Reference(feuille, min_row=1+offset3, min_col=1+colonne,
max_col=len(recap[0])+colonne-1, max_row =1+offset3 )
graphique3.y_axis.crosses = "max"
graphique3.add_data(valeurs3, titles_from_data=True, from_rows=True)
graphique3.set_categories(sites)
graphique3.y_axis.title = 'Emissions Carbones globales (tCO2e)'
# graphique3.layout=Layout(manualLayout=ManualLayout(x=0.1, y=0.050,h=0.80, w=0.90,))
graphique3.legend.layout = Layout(manualLayout=ManualLayout(yMode='edge',xMode='edge',x=0, y=0.9,h=0.1, w=0.5))
feuille.add_chart(graphique3, "B"+str(offset3+7))
tarte = PieChart()
labels = Reference(feuille, min_row = offset3, min_col = 2+colonne, max_col = len(recap[0])+colonne-1)
for i in [0]:
data = Reference(feuille, min_row=offset3+1+i, min_col=1+colonne, max_col=len(recap[0])+colonne-1)
tarte.add_data(data, titles_from_data=True, from_rows=True)
tarte.set_categories(labels)
tarte.dataLabels = DataLabelList()
tarte.dataLabels.position ='bestFit'
tarte.dataLabels.showCat = True
tarte.dataLabels.showPercent = True
tarte.dataLabels.separator = ":"
tarte.height = 8.15;tarte.width = 9
tarte.title = "Part des postes dans les émissions totales"
set_chart_title_size(tarte, size=1200)
feuille.add_chart(tarte, "M"+str(offset3+16))
return ligne
def sauveTout(nom:str, recap:list):
try:
log_fret = openpyxl.load_workbook(p.CHEMIN_ECRITURE_RESULTATS+nom)
except FileNotFoundError:
fonctionsMatrices.print_log_erreur("Le document résultat n'est pas trouvé à l'emplacement "+p.CHEMIN_ECRITURE_RESULTATS+nom, inspect.stack()[0][3])
sys.exit("Le document résultat n'est pas trouvé à l'emplacement "+ p.CHEMIN_ECRITURE_RESULTATS+nom)
feuille = log_fret.get_sheet_by_name(log_fret.get_sheet_names()[0])
styleFond, styleTeteTab, styleVal, styleTableau, styleEntree, styleTitre = p.STYLES_EXCEL
alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
tableau = [[u[0], u[1][1], u[2][0], u[2][1], u[2][2], u[2][3]]for u in p.MATRICE_RESULTAT]
for li in range(1,len(tableau[1:])+1):
for col in range(len(tableau[li])):
if col>0:
feuille.cell(row = li+10, column = col+2).value=tableau[li][col]
else:
feuille.cell(row = li+10, column = col+2).value=tableau[li][col]+" - "+tableau[li][col+1]
if col<=1:
feuille.cell(row=li+10, column=col+2).style = styleEntree
else:
feuille.cell(row=li+10, column=col+2).style = styleVal
if col in [2,4]:
feuille.cell(row = li+10, column = col+2).number_format = '0.0'
tab1 = Table(displayName="Tableau_general", ref="A2:F"+str(len(p.MATRICE_RESULTAT)))
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab1.tableStyleInfo = style
# rule = DataBarRule(start_type='num', start_value=0, end_type='percentile', end_value='100',color="FF638EC6", showValue="None", minLength=None, maxLength=None)
# feuille.conditional_formatting.add("C2:C"+str(len(p.MATRICE_RESULTAT)), rule)
feuille.column_dimensions['A'].width = 10
feuille.column_dimensions['B'].width = 17
feuille.column_dimensions['C'].width = 0
feuille.column_dimensions['D'].width = 25
feuille.column_dimensions['E'].width = 6
feuille.column_dimensions['F'].width = 25
feuille.column_dimensions['G'].width = 10
#Affichage du premier graphe général (avec tous les postes)
if True:
graphique = BarChart()
graphique.type = "bar"
graphique.style = 12
graphique.title = "Bilan carbone total par poste et par site"
graphique.grouping = "standard"
graphique.overlap = 00
position_row = 9
position_col = 1
postes = Reference(feuille, min_col = position_col+1, min_row = position_row+2, max_row = position_row+len(p.MATRICE_RESULTAT)-1)
for i in [3]:
valeurs = Reference(feuille, min_col=position_col+i, min_row=position_row+1, max_row=position_row+len(p.MATRICE_RESULTAT)-1)
graphique.add_data(valeurs, titles_from_data=True)
graphique.set_categories(postes)
graphique.height = 103
graphique.width = 20
secaxe = BarChart()
valsecond = Reference(feuille, min_col=position_col+i+2, min_row=position_row+1, max_row=position_row+len(p.MATRICE_RESULTAT)-1)
secaxe.add_data(valsecond, titles_from_data=True)
secaxe.y_axis.axId = 200
graphique.x_axis.scaling.orientation = "maxMin"
secaxe.type = "bar"
secaxe.y_axis.title = "Ramenées à la production (kgeCO2/m3)"
cp = CharacterProperties(sz=800) #Taille du label entre 100 et 400 000
graphique.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)])
graphique.legend.legendPos = 't'
graphique.legend.tagname= 'Légende'
graphique.y_axis.title = 'Emissions Carbones (teCO2)'
graphique.y_axis.crosses = "min"
serie = secaxe.series[0]
serie.graphicalProperties.line.solidFill = "FF0000"
serie.graphicalProperties.noFill = True
graphique += secaxe
feuille.add_chart(graphique, "I10")
matRes_inv_tot,matRes_inv_m3 = renversermatres(p.MATRICE_RESULTAT)
nb_postes = len(matRes_inv_tot)-1
nb_sites = len(matRes_inv_tot[0])-1
# Changement de feuille du classeur
feuille = log_fret.create_sheet("Resultats detailles",1)
for lin in range(1,200):
for col in [x for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"]:
case = col+str(lin)
feuille[case].style=styleFond
for col in [x+y for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ" for y in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"]:
case = col+str(lin)
feuille[case].style=styleFond
graphique.overlap = 100
#On prend en abscisses la première colonne
mp = Reference(feuilleFret, min_col = 1, min_row = 2, max_row = len(fret_FE))
#Les ordonnées sont en colonnes 10 11 13
for i in [11,12,14,18,19]:
#On ajoute une à une les séries
valeurs = Reference(feuilleFret, min_col=i, min_row=1, max_row=len(fret_FE))
graphique.add_data(valeurs, titles_from_data=True)
graphique.set_categories(mp)
#Dimensions du graphique
graphique.height = 103
graphique.width = 20
graphique.x_axis.scaling.orientation = "maxMin"
cp = CharacterProperties(sz=800) #Taille du label entre 100 et 400 000
graphique.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)])
#Création du second axe de BC total
axe2 = BarChart() #idem
axe2.type = 'bar'
val2 = Reference(feuilleFret, min_col=21, min_row=1, max_row=len(fret_FE))
axe2.set_categories(mp)
axe2.add_data(val2, titles_from_data=True, from_rows=False)
serie = axe2.series[0]
#On rend cette série transparente avec les bords rouges
serie.graphicalProperties.line.solidFill = "FF0000"
serie.graphicalProperties.noFill = True
axe2.overlap = -50 #{ça fonctionne comme ça}
axe2.y_axis.axId = 200
axe2.y_axis.title = "Bilan Carbone total de la MP"
axe2.y_axis.crosses = "min"
def place_in_excel(proj_list, data_key_list, total_data, q_masters_dict_list,
q_masters_list, period):
wb = Workbook()
ws = wb.active
ws.cell(row=1, column=1).value = 'Project'
for i, proj_name in enumerate(proj_list):
'''lists project names in row one'''
ws.cell(row=1, column=i + 2).value = proj_name
'''iterates through financial dictionary - placing financial data in ws'''
for x, key in enumerate(data_key_list):
try:
if period == 'baseline':
ws.cell(row=x + 2,
column=i + 2).value = q_masters_dict_list[
q_masters_list[proj_name][2]][proj_name][key]
if period == 'last':
ws.cell(row=x + 2, column=i + 2).value = \
q_masters_dict_list[q_masters_list[proj_name][1]][proj_name][key]
if period == 'latest':
ws.cell(row=x + 2, column=i + 2).value = \
q_masters_dict_list[q_masters_list[proj_name][0]][proj_name][key]
except KeyError:
ws.cell(row=x + 2, column=i + 2).value = 0
'''places totals in final column. to note because this is a list and not a dictionary as for fin_data there is
possibility that data could become unaligned. Whether changing the list of cells_to_capture causes them to become
unaligned needs to be tested'''
ws.cell(row=1, column=len(proj_list) + 2).value = 'Total'
for i, values in enumerate(total_data):
ws.cell(row=i + 2, column=len(proj_list) + 2).value = values
'''places keys into the chart in the first column'''
for i, key in enumerate(data_key_list):
ws.cell(row=i + 2, column=1).value = key
'''information on which projects are not included in totals'''
ws.cell(
row=1, column=len(proj_list) +
4).value = 'Projects that have been removed to avoid double counting'
for i, project in enumerate(dont_double_count):
ws.cell(row=i + 2, column=len(proj_list) + 4).value = project
# ws.cell(row=1, column=len(proj_list)+6).value = 'Projects that have been removed to enable like for like' \
# 'comparison of totals'
# for i, project in enumerate(like_for_like_totals):
# ws.cell(row=i + 2, column=len(proj_list)+6).value = project
'''data for overall chart. As above because this data is in a list - possibility of it being unaligned needs
testing. not the best way of managing data flow, but working for now'''
start_row = len(total_data) + 8
for x in range(0, int(len(total_data) / 4)):
ws.cell(row=start_row, column=2, value=total_data[x])
start_row += 1
start_row = len(total_data) + 8
for x in range(int(len(total_data) / 4), (int(len(total_data) / 4) * 2)):
ws.cell(row=start_row, column=3, value=total_data[x])
start_row += 1
start_row = len(total_data) + 8
for x in range((int(len(total_data) / 4) * 2),
(int(len(total_data) / 4) * 3)):
ws.cell(row=start_row, column=4, value=total_data[x])
start_row += 1
start_row = len(total_data) + 8
for x in range((int(len(total_data) / 4) * 3), int(len(total_data))):
ws.cell(row=start_row, column=5, value=total_data[x])
start_row += 1
'''code was essentially a hack'''
start_row = len(total_data) + 8
list_of_numbers = [0, len(capture_rdel), len(capture_rdel) * 2]
total_sum = 0
for i in range(0, len(capture_rdel)):
for x in list_of_numbers:
total_sum = total_sum + total_data[x + i]
ws.cell(row=start_row, column=6, value=total_sum)
start_row += 1
total_sum = 0
a = len(total_data) + 7
ws.cell(row=a, column=2, value='RDEL')
ws.cell(row=a, column=3, value='CDEL')
ws.cell(row=a, column=4, value='Non-Gov')
ws.cell(row=a, column=5, value='Income')
ws.cell(row=a, column=6, value='Total')
# ws.cell(row=a+1, column=1, value='17/18')
#ws.cell(row=a + 1, column=1, value='18/19')
ws.cell(row=a + 1, column=1, value='19/20')
ws.cell(row=a + 2, column=1, value='20/21')
ws.cell(row=a + 3, column=1, value='21/22')
ws.cell(row=a + 4, column=1, value='22/23')
ws.cell(row=a + 5, column=1, value='23/24')
ws.cell(row=a + 6, column=1, value='24/25')
ws.cell(row=a + 7, column=1, value='25/26')
ws.cell(row=a + 8, column=1, value='26/27')
ws.cell(row=a + 9, column=1, value='27/28')
ws.cell(row=a + 10, column=1, value='28/29')
ws.cell(row=a + 11, column=1, value='Unprofiled')
'''this builds a very basic chart'''
# TODO fix chart
chart = LineChart()
chart.title = 'Portfolio cost profile'
chart.style = 4
chart.x_axis.title = 'Financial Year'
chart.y_axis.title = 'Cost (£m)'
chart.height = 15 # default is 7.5
chart.width = 26 # default is 15
'''styling chart'''
# axis 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
chart.y_axis.title.tx.rich.p[0].pPr = pp
# 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
data = Reference(ws, min_col=2, min_row=51, max_col=5, max_row=61)
cats = Reference(ws, min_col=1, min_row=52, max_row=61)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
s3 = chart.series[0]
s3.graphicalProperties.line.solidFill = "36708a" # dark blue
s8 = chart.series[1]
s8.graphicalProperties.line.solidFill = "68db8b" # green
s9 = chart.series[2]
s9.graphicalProperties.line.solidFill = "794747" # dark red
s9 = chart.series[3]
s9.graphicalProperties.line.solidFill = "73527f" # purple
ws.add_chart(chart, "I52")
return wb
def title_maker(text):
title = Title()
paras = [Paragraph(r=RegularTextRun(t=s)) for s in text.split("\n")]
title.tx.rich.paragraphs = paras
return title
def place_in_excel_one_wb():
project_name_list = list(latest_cost_profiles.keys())
wb = Workbook()
cost_list.append(' total')
for i, project_name in enumerate(project_name_list):
'''worksheet is created for each project'''
ws = wb.create_sheet(project_name, i) # creating worksheets
ws.title = project_name # title of worksheet
'''COST PROFILE CHART'''
''''places in raw/reported cost data'''
for i, year in enumerate(year_list):
for x, type in enumerate(cost_list):
ws.cell(row=i + 3,
column=x + 2,
value=baseline_1_cost_profiles[project_name][year +
type])
try:
ws.cell(row=i + 3,
column=x + 6,
value=last_cost_profiles[project_name][year +
type])
except KeyError:
pass
ws.cell(row=i + 3,
column=x + 10,
value=latest_cost_profiles[project_name][year + type])
'''labeling data in table'''
labeling_list_quarter = ['Baseline', 'Last Quarter', 'Latest quarter']
ws.cell(row=1, column=2, value=labeling_list_quarter[0])
ws.cell(row=1, column=6, value=labeling_list_quarter[1])
ws.cell(row=1, column=10, value=labeling_list_quarter[2])
'''top reference to which data being used'''
labeling_list_type = ['RDEL', 'CDEL', 'Non-Gov', 'Total']
repeat = 3
c = 0
while repeat > 0:
for i, label in enumerate(labeling_list_type):
ws.cell(row=2, column=2 + i + c, value=label)
c += 4
repeat -= 1
'''labeling of years down the side'''
for i, label in enumerate(year_list):
ws.cell(row=3 + i, column=1, value=label)
ws.cell(row=2, column=1, value='Year')
'''record of which baseline is being used'''
ws.cell(row=1, column=16).value = 'Baseline quarter'
ws.cell(row=2, column=16).value = baseline_bc_stamp[project_name][0][1]
'''Total cost profile. starting with data placement'''
for i, year in enumerate(year_list):
for x, type in enumerate([' total']):
ws.cell(row=i + 16,
column=x + 2,
value=baseline_1_cost_profiles[project_name][year +
type])
ws.cell(row=i + 16,
column=x + 3,
value=last_cost_profiles[project_name][year + type])
ws.cell(row=i + 16,
column=x + 4,
value=latest_cost_profiles[project_name][year + type])
'''data for graph labeling'''
for i, quarter in enumerate(labeling_list_quarter):
ws.cell(row=15, column=i + 2, value=quarter)
for i, label in enumerate(year_list):
ws.cell(row=16 + i, column=1, value=label)
ws.cell(row=15, column=1, value='Year')
'''Cost chart information'''
chart = LineChart()
chart.title = str(project_name) + ' Cost Profile'
chart.style = 4
chart.x_axis.title = 'Financial Year'
chart.y_axis.title = 'Cost £m'
'''styling chart'''
# axis 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
chart.y_axis.title.tx.rich.p[0].pPr = pp
# chart.title.tx.rich.p[0].pPr = pp
# 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
'''unprofiled costs not included in the chart'''
data = Reference(ws, min_col=2, min_row=15, max_col=4, max_row=25)
chart.add_data(data, titles_from_data=True)
cats = Reference(ws, min_col=1, min_row=16, max_row=25)
chart.set_categories(cats)
s3 = chart.series[0]
s3.graphicalProperties.line.solidFill = "cfcfea" # light blue
s8 = chart.series[1]
s8.graphicalProperties.line.solidFill = "5097a4" # medium blue
s9 = chart.series[2]
s9.graphicalProperties.line.solidFill = "0e2f44" # dark blue'''
ws.add_chart(chart, "H15")
'''INCOME CHART'''
'''Quick calculation to see if project is reporting income. If not then no graph is created'''
total_income = sum(list(latest_income_profiles[project_name].values()))
'''income data is inserted'''
if total_income > 0:
for i, year in enumerate(year_list):
for type in income_list:
ws.cell(
row=i + 32,
column=2,
value=baseline_1_income_profiles[project_name][year +
type])
ws.cell(row=i + 32,
column=3,
value=last_income_profiles[project_name][year +
type])
ws.cell(row=i + 32,
column=4,
value=latest_income_profiles[project_name][year +
type])
'''labeling of the data table'''
for i, quarter in enumerate(labeling_list_quarter):
ws.cell(row=32, column=i + 2, value=quarter)
for i, label in enumerate(year_list):
ws.cell(row=33 + i, column=1, value=label)
ws.cell(row=32, column=1, value='Year')
'''income graph created'''
chart = LineChart()
chart.title = str(project_name) + ' Income Profile'
chart.style = 4
chart.x_axis.title = 'Financial Year'
chart.y_axis.title = 'Cost £m'
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
chart.y_axis.title.tx.rich.p[0].pPr = pp
# chart.title.tx.rich.p[0].pPr = pp
# 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
#unprofiled costs not included in the chart
data = Reference(ws, min_col=2, min_row=32, max_col=4, max_row=42)
chart.add_data(data, titles_from_data=True)
cats = Reference(ws, min_col=1, min_row=33, max_row=42)
chart.set_categories(cats)
s3 = chart.series[0]
s3.graphicalProperties.line.solidFill = "e2f1bb" # light green
s8 = chart.series[1]
s8.graphicalProperties.line.solidFill = "a0db8e" # medium green
s9 = chart.series[2]
s9.graphicalProperties.line.solidFill = "29ab87" # dark green
ws.add_chart(chart, "H31")
else:
pass
return wb