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 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 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')
['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)
# chart.set_categories(cats)
ws.add_chart(chart, "B30")
wb.save('data/charts.xlsx')
def cmd_export(self):
bkg_color = self.app.colors["color_3"].get().replace("#", "")
bkg_fill = PatternFill(patternType='solid', fgColor=Color(rgb=bkg_color))
head_color = self.app.colors["color_1"].get().replace("#", "")
head_fill = PatternFill(patternType='solid', fgColor=Color(rgb=head_color))
head_font_color = self.app.colors["font_color_1"].get().replace("#", "")
head_font = Font(bold=True, color=head_font_color)
body_font_color = self.app.colors["font_color_2"].get().replace("#", "")
body_font = Font(bold=False, color=body_font_color)
align_center = Alignment(horizontal="center", vertical="center")
align_right = Alignment(horizontal="right", vertical="center")
align_left = Alignment(horizontal="left", vertical="center")
row_offset = 1
col_offset = 1
work_book = Workbook()
work_sheet = work_book.active
work_sheet.title = "CS4_output"
for xl_row in range(1, 100):
for xl_col in range(1, 100):
active_cell = work_sheet.cell(column=xl_col, row=xl_row)
active_cell.fill = bkg_fill
for xl_row, res_row in enumerate(self.result_table):
for xl_col, res_col in enumerate(res_row):
active_cell = work_sheet.cell(column=xl_col+col_offset,
row=xl_row+row_offset,
value="{0}".format(res_col))
# Left column header
if not xl_col:
active_cell.fill = head_fill
active_cell.font = head_font
active_cell.alignment = align_right
# Top row header
elif not xl_row:
active_cell.fill = head_fill
active_cell.font = head_font
active_cell.alignment = align_center
# Data row at the bottom
elif xl_row >= len(self.result_table)-3:
active_cell.value = int(res_col)
# Style names
else:
active_cell.font = body_font
active_cell.alignment = align_left
# Create chart
values = Reference(work_sheet,
min_col=col_offset+1,
max_col=len(self.result_table[0])+col_offset-1,
min_row=len(self.result_table)+row_offset-1,
max_row=len(self.result_table)+row_offset-1)
chart = AreaChart()
chart.add_data(values, from_rows=True)
chart.legend = None
chart.title = "Core Sound analysis"
# set a pattern for the whole series
series = chart.series[0]
fill = PatternFillProperties()
fill.foreground = ColorChoice(prstClr="red")
fill.background = ColorChoice(prstClr="blue")
series.graphicalProperties.pattFill = fill
work_sheet.add_chart(chart, "E15")
work_book.save(filename=self.file_name)
self.root.destroy()
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
['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)
# chart.set_categories(cats)
ws.add_chart(chart, "B30")
wb.save('data/charts2.xlsx')
import openpyxl
from openpyxl.chart import AreaChart, Reference
wb = openpyxl.Workbook()
sheet = wb.active
for i in range(10):
sheet.append([i])
values = Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=10)
chart = AreaChart()
chart.add_data(values)
chart.title = "Area Chart"
chart.x_axis.title = " X_Axis"
chart.y_axis.title = " Y_Axis"
sheet.add_chart(chart, 'E2')
wb.save("Areachart.xlsx")
cs = wb.create_chartsheet()
rows = [
["Bricks", 3],
["Tiles", 2],
["Blocks", 4],
["Grass", 8],
["Plates", 8],
["Soil", 1],
]
for row in rows:
ws1.append(row)
# Titles
chart = AreaChart()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Item'
chart.y_axis.title = 'Share of area'
# Add the data to the chart
data = Reference(ws1, min_col=2, min_row=1, max_row=6)
categories = Reference(ws1, min_col=1, min_row=1, max_row=6)
chart.add_data(data, titles_from_data=False)
chart.set_categories(categories)
# Add the chart to the chartsheet
#cs.add_chart(chart)
wb.save(filename)
tc = ws3.max_column
for r in range(1, tr + 1):
for c in range(1, tc + 1):
ws4.cell(row=r, column=c).value = ws3.cell(row=r, column=c).value
#print(ws3.cell(row=r,column=c).value)
# ws3(A1)
# ws3(A)
# ws3(A:C)
# ws3(A1:C10)
# ws3(1:10)
wb.save('dbdump.xlsx')
wb.close()
wb = openpyxl.load_workbook('graph.xlsx')
ws5 = wb.active
ws6 = wb.create_sheet('Bar')
ws7 = wb.create_sheet('Area')
from openpyxl.chart import Reference, AreaChart, BarChart
area = AreaChart()
bar = BarChart()
cat = Reference(ws5, min_row=1, max_row=10, min_col=1)
data = Reference(ws5, min_row=1, max_row=10, min_col=2, max_col=3)
area.set_categories(cat)
bar.set_categories(cat)
area.add_data(data)
bar.add_data(data)
ws6.add_chart(bar, 'A1')
ws7.add_chart(area, 'A1')
wb.save('graph.xlsx')
wb.close()
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'] = '年利息'
ws1['A5'] = '總繳利息'
ws1['B1'] = lv
ws1['B2'] = repay
ws1['B3'] = yr
ws1['B4'] = yrate
for i in range(1, 10):
for j in range(1, 5):
ws41.cell(row=i, column=j).value =i*j
wb4.save('chart.xlsx')
wb4.close()
wb5 = openpyxl.load_workbook('chart.xlsx')
ws51 = wb5['Data']
ws52 = wb5['Area']
ws53 = wb5['Bar']
achart = AreaChart()
category = Reference(ws51, min_row=1, min_col=1, max_row=10)
data = Reference(ws51, min_row=1, min_col=2, max_row=10, max_col=4)
achart.set_categories(category)
achart.add_data(data)
ws52.add_chart(achart, 'A1')
wb5.save('chart.xlsx')
bchart = BarChart()
bchart.set_categories(category)
bchart.add_data(data)
ws53.add_chart(bchart, 'A1')
wb5.save('chart.xlsx')
#ws['A'] => 1 column
#ws['A:C'] => A to C columns
#ws['2'] => 1 row
#ws['2:10'] => 2 to 10 rows
wb = openpyxl.load_workbook(r'C:\Bhushan\Python\log\chart.xlsx')
ws1 = wb['Sheet1']
ws2 = wb.create_sheet('Area')
ws3 = wb.create_sheet('Bar')
from openpyxl.chart import AreaChart, BarChart, Reference
#Chart Obj => Category: x-axis, data: y-axis
#expore other charts by urself
chart1 = AreaChart()
chart2 = BarChart()
cat = Reference(ws1, min_row=1, max_row=10, min_col=1)
data = Reference(ws1, min_row=1, max_row=10, min_col=2, max_col=3)
chart1.set_categories(cat)
chart1.add_data(data)
chart2.set_categories(cat)
chart2.add_data(data)
ws2.add_chart(chart1)
ws3.add_chart(chart2)
wb.save(r'C:\Bhushan\Python\log\chart.xlsx')
# chart.x_axis.tickLblSkip = 24
# chart.width = 25
# chart.height = 12.5
sheet.add_chart(chart, "E16")
wb.save('AirLineBumps_linechart.xlsx')
from openpyxl.chart import AreaChart
area_chart = AreaChart()
area_chart.title = "AirLine Bumps"
area_chart.x_axis.title = "Year"
area_chart.y_axis.title = "Number of Bumps"
area_chart.add_data(values, titles_from_data = True)
area_chart.set_categories(dates)
area_chart.series
area_chart.series[0].graphicalProperties.solidFill = "ff9900"
sheet.add_chart(area_chart, "E31")
wb.save('AirLineBumps_linechart.xlsx')
wb = openpyxl.load_workbook('AirLineBumps_16_17.xlsx')
sheet = wb['timeanalysis']
multi_line_chart = LineChart()