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 plot_area(self, sheetname, column, value):
# 必要な値と項目を残した時系列データを作成
df = self._df[[value, column]].pivot_table(index = "DateTime", values = value, columns = column, fill_value = 0)
with pd.ExcelWriter(self._file) as writer:
# excelファイルがあるなら追加書きする
if os.path.isfile(self._file):
writer.book = load_workbook(self._file)
# write the df on excel at once
df.to_excel(writer, sheet_name = sheetname)
# plot Area chart
chart = AreaChart()
chart.grouping = "stacked"
self._plot(writer.book[sheetname], chart, title = "面グラフ", xlabel = "日付", ylabel = "回数")
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 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 plot_area_chart():
wb = xl.load_workbook('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx') # load xl workbook
sheet = wb['dATA'] # get the worksheet
for i in range(2, 10):
cell = sheet['f'+str(i)]
sheet['a'+str(i)] = cell.value
values = Reference(
sheet,
min_row=2,
max_row=10,
min_col=1,
max_col=1
)
series_object = xl.chart.Series(values, title="Yearly Revenue")
chart = AreaChart()
chart.append(series_object)
sheet.add_chart(chart, 'j2')
wb.save('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx')
def create_area_chart(ws, chart=AreaChart()):
chart.style = 13
cats = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
data = Reference(ws, min_col=2, min_row=1, max_col=ws.max_column, max_row=ws.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
col = dec2tsix(ws.max_column + 2)
chart_cell = '%s%s' % (col, ws.max_row + 2)
ws.add_chart(chart, chart_cell)
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')
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("area.xlsx")
wb = Workbook()
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()
import openpyxl from openpyxl.chart import AreaChart, Reference wb = openpyxl.load_workbook(r"..\data\area_chart.xlsx") sh = wb.active data = Reference(sh, min_col=3, max_col=7, min_row=1, max_row=sh.max_row) labels = Reference(sh, min_col=2, max_col=2, min_row=2, max_row=sh.max_row) chart = AreaChart() chart.grouping = "stacked" #chart.grouping = "percentStacked" chart.title = "分類別売上(サイズ積上げ)" chart.x_axis.title = "分類" chart.y_axis.title = "サイズ" chart.add_data(data, titles_from_data=True) chart.set_categories(labels) sh.add_chart(chart, "I2") wb.save(r"..\data\area_chart.xlsx")
ws = wb.active
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)
from openpyxl import Workbook
from openpyxl.chart import AreaChart, Reference
wb = Workbook()
ws = wb.active
rows = [['Number', 'Batch 1', 'Batch 2'], [12, 50, 40], [13, 20, 15],
[14, 70, 10], [15, 90, 20]]
for row in rows:
ws.append(row)
chart = AreaChart()
chart.title = 'Area Chart'
chart.style = 12
chart.x_axis.title = 'ID'
chart.y_axis.title = 'Percentage'
cats = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, 'A10')
wb.save('2DAreachart.xlsx')
ws = wb.active
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)
ws.cell(row=ct + 9, column=4).value = b * yrate * 0.01 / 12
#利息
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'] = '還款方式'
ws41 = wb4.create_sheet('Data')
ws42 = wb4.create_sheet('Area')
ws43 = wb4.create_sheet('Bar')
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')
#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.add_data(values, titles_from_data = True)
dates = Reference(sheet, min_col = 1, min_row = 2,
max_col = 1, max_row = sheet.max_row)
chart.set_categories(dates)
# 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")
i, j + initial_col, cell))
ws.cell(row=i, column=j + initial_col, value=int(cell))
else:
ws.cell(row=i, column=j + initial_col, value=cell)
logger.info("Adding {} cols".format(len(row)))
i += 1
final_row = i - 1
logger.info("Final Row: {}, Final Col: {}".format(final_row, final_col))
table_range = "{}{}:{}{}".format(
openpyxl.utils.cell.get_column_letter(initial_col), initial_row,
openpyxl.utils.cell.get_column_letter(final_col), final_row)
logger.info("Setting table range to {}".format(table_range))
table.ref = table_range
# Add Routing StackedLine Chart
chart = AreaChart()
data = Reference(ws,
min_col=2,
min_row=1,
max_col=final_col,
max_row=final_row)
cats = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=final_row)
chart.add_data(data, titles_from_data=True)
chart.grouping = "percentStacked"
chart.set_categories(cats)
ws.add_chart(chart, "H1")
wb.save(filename=args.output)
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)
Series,
)
# Creamos los datos
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:
ws2.append(row)
# creamos cuadro
chart = AreaChart() # tipo de grafica
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
# Agregamos la data al cuadro
cats = Reference(ws2, min_col=1, min_row=1, max_row=7)
data = Reference(ws2, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
# La dibujamos
ws2.add_chart(chart, "A10")
###################
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 type1():
from openpyxl.chart import AreaChart
chart = AreaChart()
return chart
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()
#print(ws.max_row)
#print(ws.max_column)
#print(ws.cell(3,2).value)
#for row in ws.rows:
# for cell in row:
# print(cell.value)
for row in ws.rows:
for cell in row:
print(cell.value, end='\t')
print()
ws.append()
# print(row)
#for i in range(1,3):
# for j in range(1,2):
# print(ws.cell(row=i,column=j).value)
chart = AreaChart()
chart.title = "Nginx log"
chart.style = 13
chart.x_axis.title = 'date'
chart.y_axis.title = 'vistor'
cats = Reference(ws, min_col=1, min_row=1, max_row=17)
data = Reference(ws, min_col=1, min_row=1, max_col=3, max_row=17)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "A20")
wb.save("d:/python-execl/log4.xlsx")
chart.title = 'Capitals Dummy Information - PieChart'
ws.add_chart(chart, 'D1')
chart = LineChart()
chart.add_data(data)
chart.set_categories(labels)
chart.title = 'Capitals Dummy Information - LineChart'
ws.add_chart(chart, 'M1')
chart = BarChart()
chart.add_data(data)
chart.set_categories(labels)
chart.title = 'Capitals Dummy Information - BarChart'
ws.add_chart(chart, 'D17')
chart = AreaChart()
chart.add_data(data)
chart.set_categories(labels)
chart.title = 'Capitals Dummy Information - AreaChart'
ws.add_chart(chart, 'M17')
# Tables
# Let's create a table for our previous data
table = Table(displayName='Capitals', ref='A1:B6')
style = TableStyleInfo(name='TableStyleMedium9',
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
table.tableStyleInfo = style
ws.add_table(table)
#Area charts
#2D Area Chars
from openpyxl.chart import (AreaChart, Reference, Series)
wb = Workbook()
ws = wb.active
rows = [['Number', 'Batch 1', 'Batch 2'], #column headings
[2, 40, 30], #Row 2
[3, 40, 25]]
for row in rows:
ws.append(row)
chart = AreaChart() #Create chart. AreaChart3D() for 3D
chart.title = "Area Chart" #title of chart
chart.style = 13 #
chart.x_axis.title = 'Test' #title of x-axis
chart.y_axis.title = 'Percentage' #title of y-axis
chart.legend = None #for the legend
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")
#Bar Charts
from openpyxl.chart import BarChart, Series, Reference