def export_data(self):
conn = self.get_conn()
cursor = conn.cursor()
row_id = 10
sql = 'SELECT `year`, `max`, `avg` FROM `score` limit 0, 100'
cursor.execute(sql)
rows = cursor.fetchall()
for (i, row) in enumerate(rows):
self.ws['C{0}'.format(row_id)] = row[0]
self.ws['D{0}'.format(row_id)] = row[1]
self.ws['E{0}'.format(row_id)] = row[2]
row_id += 1
# 显示图表
chart = AreaChart()
chart.title = "统计表"
chart.style = 13
chart.x_axis.title = '年份'
chart.y_axis.title = '分数'
# 横坐标
cats = Reference(self.ws, min_col=3, min_row=10, max_row=row_id)
# 数据区域
data = Reference(self.ws, min_col=4, min_row=9, max_col=5, max_row=row_id)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
self.ws.add_chart(chart, "A{0}".format(row_id+2))
self.wb.save('./static/stats.xlsx')
def areachart(self):
# 2DArea Charts
wb = Workbook()
ws = wb.active
rows = [
['Number具体数', '高地', '山丘'],
[2, 40, 30],
[3, 40, 25],
[4, 50, 30],
[5, 30, 10],
[6, 25, 5],
[7, 150, 10],
]
for row in rows:
ws.append(row)
chart = AreaChart()
chart.title = "我的内容上门"
chart.style = 13
chart.x_axis.title = '标题测试组'
chart.y_axis.title = '比率'
cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "G10")
wb.save("area.xlsx")
def export_data(self):
conn = self.get_conn()
cursor = conn.cursor()
cursor.execute(
'SELECT `year`, `max_score`, `avg_score` FROM `user_score`')
rows = cursor.fetchall()
row_id = 10
for (i, row) in enumerate(rows):
(self.ws['C{0}'.format(row_id)], self.ws['D{0}'.format(row_id)],
self.ws['E{0}'.format(row_id)]) = row
row_id += 1
chart = AreaChart()
chart.title = '统计表'
chart.style = 13
chart.x_axis.title = '年份'
chart.y_axis.title = '分数'
cats = Reference(self.ws, min_col=3, min_row=10, max_row=row_id)
data = Reference(self.ws,
min_col=4,
min_row=9,
max_col=5,
max_row=row_id)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
self.ws.add_chart(chart, "A{0}".format(row_id + 2))
self.wb.save('./static/stats.xlsx')
def export_data(self):
# 获取数据库的连接
conn = self.get_conn()
cursor = conn.cursor()
# 准备查询语句 (如果数据量大，需要借助于分页查询)
sql = 'SELECT `year`, `max`, `avg` FROM `score`'
# 查询数据
cursor.execute(sql)
rows = cursor.fetchall()
# 循环写入到excel
row_id = 10
for (i, row) in enumerate(rows):
print(row)
(self.ws['C{0}'.format(row_id)], self.ws['D{0}'.format(row_id)],
self.ws['E{0}'.format(row_id)]) = row
row_id += 1
# 显示图表
chart = AreaChart()
chart.title = "统计表"
chart.style = 13
chart.x_axis.title = '年份'
chart.y_axis.title = '分数'
# 横坐标
cats = Reference(self.ws, min_col=3, min_row=10, max_row=row_id)
# 数据区域
data = Reference(self.ws,
min_col=4,
min_row=9,
max_col=5,
max_row=row_id)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
self.ws.add_chart(chart, "A{0}".format(row_id + 2))
# 保存excel
self.wb.save('./static/stats.xlsx')
def export_xls(self):
conn = self.get_conn()
cursor = conn.cursor()
sql = 'SELECT `year`, `max`, `avg` FROM `score`'
cursor.execute(sql)
rows = cursor.fetchall()
row_id = 10
for i, row in enumerate(rows):
(self.ws['C{0}'.format(row_id)], self.ws['D{0}'.format(row_id)],
self.ws['E{0}'.format(row_id)]) = row
row_id += 1
chart = AreaChart()
chart.title = "分数统计图"
chart.style = 13
chart.x_axis.title = '年份'
chart.y_axis.title = '分数'
x_header = Reference(self.ws,
min_col=3,
min_row=10,
max_row=row_id - 1)
# 参数说明
# min_col第几列开始
# min_row第几行开始(这里取9是因为, 数据第一行默认为表头)
# max_col第几列结束
# max_row第几行结束
data = Reference(self.ws,
min_col=4,
min_row=10,
max_col=5,
max_row=row_id - 1)
chart.add_data(data)
chart.set_categories(x_header)
self.ws.add_chart(chart, "A{0}".format(row_id + 2))
self.wb.save('./static/stats.xlsx')
def create_chart_test():
from openpyxl import Workbook
from openpyxl.chart import (
AreaChart,
Reference,
Series,
)
wb = Workbook()
ws = wb.active
rows = [
['Number', 'Batch 1', 'Batch 2'],
[2, 40, 30],
[3, 40, 25],
[4, 50, 30],
[5, 30, 10],
[6, 25, 5],
[7, 50, 10],
]
for row in rows:
ws.append(row)
chart = AreaChart()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "A10")
wb.save("./file_location/area.xlsx")
def export_data(self):
# 获取数据库的连接
conn = self.get_conn()
cursor = conn.cursor()
# 准备查询语句
sql = "SELECT year, max, avg FROM user_grade.score;"
# 查询数据
cursor.execute(sql)
rows = cursor.fetchall()
# 循环写 入excel
row_id = 10
for (i, row) in enumerate(rows):
(self.ws['C{0}'.format(row_id)], self.ws['D{0}'.format(row_id)],
self.ws['E{0}'.format(row_id)]) = row
row_id += 1
# 显示图表
chart = AreaChart()
chart.title = "统计表"
chart.style = 13
chart.x_axis.title = '年份'
chart.y_axis.title = '分数'
# 横坐标
cats = Reference(self.ws, min_col=3, min_row=10, max_row=row_id)
# 数据区域
# 数据区域的min_row比横坐标的小1，是为了把第一行当作折线的名称
data = Reference(self.ws,
min_col=4,
min_row=9,
max_col=5,
max_row=row_id)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
self.ws.add_chart(chart, "A{0}".format(row_id + 2))
# 保存excel
self.wb.save('./static/templ.xlsx')
rows = [
['Number', 'Batch 1', 'Batch 2'],
['A', 40, 30],
['B', 40, 25],
['C', 50, 30],
['D', 30, 10],
['E', 25, 5],
['F', 50, 10],
]
for row in rows:
ws.append(row)
chart = AreaChart()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
# yData needs to start on row=1 to include titles
xData = Reference(ws, min_col=1, min_row=2, max_row=7)
yData = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=7)
chart.add_data(yData, titles_from_data=True)
chart.set_categories(xData)
ws.add_chart(chart, "C10")
chart = AreaChart3D()
cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
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
rows = [
['Number', 'Batch 1', 'Batch 2'],
['A', 40, 30],
['B', 40, 25],
['C', 50, 30],
['D', 30, 100],
['E', 25, 5],
['F', 50, 10],
]
for row in rows:
ws.append(row)
chart = AreaChart()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
# yData needs to start on row=1 to include titles
xData = Reference(ws, min_col=1, min_row=2, max_row=7)
yData = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=7)
chart.add_data(yData, titles_from_data=True)
chart.set_categories(xData)
ws.add_chart(chart, "C10")
chart = AreaChart3D()
cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
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)
d = ws.cell(row=ct + 9, column=5).value #繳款金額
ws.cell(row=ct + 9, column=3).value = d - (b * yrate * 0.01 / 12) #本期繳款本金
aplusaone = ws.cell(row=ct + 9, column=2) #本期貸款餘額
aplusaone.value = b - d + (b * yrate * 0.01 / 12)
allrate = (rf3 * lv) * yr * 12
ws['B5'] = allrate
rows = ws['A9':'E248']
for row in ws.iter_rows(min_row=8, min_col=1, max_row=8 + yr * 12, max_col=5):
for cell in row:
cell
chart = AreaChart()
chart.title = "本息平均攤還"
chart.style = 13
chart.x_axis.title = '期數'
chart.y_axis.title = '金額'
cats = Reference(ws, min_col=1, min_row=9, max_row=9 + yr * 12)
data = Reference(ws, min_col=3, min_row=9, max_col=4, max_row=9 + yr * 12)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "I9")
#本金平均攤還
ws1['A1'] = '貸款金額'
ws1['A2'] = '還款方式'
ws1['A3'] = '貸款年數'
ws1['A4'] = '年利息'