def area_max(df, wb, sheet_name):
df_columns = df.columns.tolist()
ws = wb[sheet_name]
chart = AreaChart()
chart.height = 15
chart.width = 30
chart.x_axis.title = df_columns[1]
chart.y_axis.title = None
chart.grouping = "percentStacked"
chart.title = sheet_name
episodes = Reference(ws, min_col=df_columns.index('episode') + 1, min_row=2, max_row=len(df) + 1)
data = Reference(ws, min_col=df_columns.index('happy') + 1, min_row=1, max_col=len(df_columns), max_row=len(df) + 1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(episodes)
ws.add_chart(chart, f"A{len(df) + 3}")
def lesfimi_excel_entire_country_stats():
ref_values = {
1: (20, 55, 75),
2: (40, 85, 100),
3: (55, 100, 120),
4: (80, 120, 145),
5: (90, 140, 160),
6: (105, 155, 175),
7: (120, 165, 190),
8: (130, 180, 210),
9: (140, 180, 210),
10: (145, 180, 210),
}
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response[
'Content-Disposition'] = 'attachment; filename=Lesfimi - Allt Landið.xlsx'
wb = openpyxl.Workbook()
ws = wb.get_active_sheet()
wb.remove_sheet(ws)
tests = (
('b{}_LF_mai17', 'Maí 2017'),
('b{}_LF_jan17', 'Janúar 2017'),
('{}b_LF_sept', 'September 2016'),
)
for test in tests:
identifier = test[0]
title = test[1]
ws = wb.create_sheet(title=title)
ws['A1'] = 'Árgangur'
ws['B1'] = 'Fjöldi nemenda'
ws['C1'] = 'Fjöldi sem þreytti próf'
ws['D1'] = 'Hlutfall sem þreytti próf'
ws['E1'] = 'Hlutfall sem nær 90% viðmiðum'
ws['F1'] = 'Hlutfall sem nær 50% viðmiðum'
ws['G1'] = 'Hlutfall sem nær 25% viðmiðum'
index = 2
errors = []
for year in range(1, 11):
ws['A' + str(index)] = year
survey = Survey.objects.filter(
identifier=identifier.format(year)).first()
studentgroups = StudentGroup.objects.filter(
student_year=year).all()
this_year_result = {
'students': 0,
'students_who_took_test': 0,
'students_over_25pct': 0,
'students_over_50pct': 0,
'students_over_90pct': 0,
}
for studentgroup in studentgroups:
this_year_result['students'] += studentgroup.students.all(
).count()
groupsurveys = GroupSurvey.objects.filter(
studentgroup=studentgroup, survey=survey)
if groupsurveys.all().count() > 1:
errors.append(
'sama próf skráð {} sinnum fyrir {} í {}'.format(
groupsurveys.all().count(), studentgroup.name,
studentgroup.school.name))
for groupsurvey in groupsurveys.all():
for student in studentgroup.students.all():
surveyresults = SurveyResult.objects.filter(
survey=groupsurvey, student=student)
if surveyresults.all().count() > 1:
errors.append(
'{} niðurstöður í sama prófi skráðar fyrir nemanda {} í bekk {} í {}'
.format(
surveyresults.all().count(),
student.ssn,
studentgroup.name,
studentgroup.school.name,
))
for surveyresult in surveyresults.all():
try:
survey_student_result = surveyresult.calculated_results(
)
if not survey_student_result[0] == '':
this_year_result[
'students_who_took_test'] += 1
if int(survey_student_result[0]
) >= ref_values[year][2]:
this_year_result[
'students_over_25pct'] += 1
if int(survey_student_result[0]
) >= ref_values[year][1]:
this_year_result[
'students_over_50pct'] += 1
if int(survey_student_result[0]
) >= ref_values[year][0]:
this_year_result[
'students_over_90pct'] += 1
except:
pass
ws['B' + str(index)] = this_year_result['students']
ws['C' + str(index)] = this_year_result['students_who_took_test']
if this_year_result['students'] > 0 and this_year_result[
'students_who_took_test'] > 0:
ws['D' +
str(index)] = (this_year_result['students_who_took_test'] /
this_year_result['students']) * 100
pct_over_90pct = (
this_year_result['students_over_90pct'] /
this_year_result['students_who_took_test']) * 100
ws['E' + str(index)] = pct_over_90pct
pct_over_50pct = (
this_year_result['students_over_50pct'] /
this_year_result['students_who_took_test']) * 100
ws['F' + str(index)] = pct_over_50pct
pct_over_25pct = (
this_year_result['students_over_25pct'] /
this_year_result['students_who_took_test']) * 100
ws['G' + str(index)] = pct_over_25pct
else:
ws['D' + str(index)] = 0
ws['E' + str(index)] = 0
ws['F' + str(index)] = 0
ws['G' + str(index)] = 0
index += 1
dims = {}
for row in ws.rows:
for cell in row:
if cell.value:
dims[cell.column] = max(
(dims.get(cell.column, 0), len(str(cell.value))))
for col, value in dims.items():
ws.column_dimensions[col].width = int(value) + 2
chart = AreaChart()
chart.title = "Lesfimi í {} - Allt landið".format(title)
chart.style = 10
chart.width = 40
chart.height = 20
chart.layout = Layout(ManualLayout(
xMode="edge",
yMode="edge",
))
chart.x_axis.title = 'Árgangur'
chart.y_axis.title = 'Prósent'
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 100
cats = Reference(ws, min_col=1, min_row=2, max_row=index - 1)
data = Reference(ws, min_col=5, min_row=1, max_col=7, max_row=index)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
bchart = BarChart()
bchart.title = "Hlutfall nemenda sem þreyttu próf"
bchart.style = 10
bchart.width = 20
bchart.height = 10
bchart.x_axis.title = 'Árgangur'
bchart.y_axis.title = 'Prósent'
bchart.y_axis.scaling.min = 0
bchart.y_axis.scaling.max = 100
bdata = Reference(ws, min_col=4, max_col=4, min_row=2, max_row=index)
bchart.add_data(bdata)
bchart.legend = None
bchart.set_categories(cats)
ws.add_chart(bchart, "I1")
if index > 20:
ws.add_chart(chart, "A" + str(index + 2))
else:
ws.add_chart(chart, "A22")
if errors:
ws = wb.create_sheet(title='Villur')
index = 1
for error in errors:
ws['A' + str(index)] = "ATH: " + error
ws['A' + str(index)].fill = PatternFill(start_color='ff0000',
end_color='ff0000',
fill_type='solid')
ws.merge_cells('A' + str(index) + ':F' + str(index))
index += 1
wb.save(filename='/tmp/test.xlsx')
# wb.save(response)
return response
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)