def _set_swiss7_chart(self, workbook):
ws = workbook['SWISS7_G']
ws_data = workbook['Data_category']
chart1 = BarChart()
chart1.type = "col"
# chart1.style = 12
chart1.width = 21
chart1.height = 12
chart1.grouping = "stacked"
chart1.overlap = 100
chart1.y_axis.title = 'Volume du trafic en %'
# chart1.x_axis.title = 'Heure'
chart1.gapWidth = 2
data = Reference(ws_data, min_col=2, min_row=4, max_row=28, max_col=8)
# cats = Reference(ws_data, min_col=2, min_row=4, max_row=28)
chart1.add_data(data, titles_from_data=True)
# chart1.set_categories(cats)
chart1.shape = 4
chart1.legend = None
chart1.series[0].graphicalProperties.solidFill = "00a9ff"
chart1.series[1].graphicalProperties.solidFill = "bce273"
chart1.series[2].graphicalProperties.solidFill = "ff708c"
chart1.series[3].graphicalProperties.solidFill = "003366"
chart1.series[4].graphicalProperties.solidFill = "ff00ff"
chart1.series[5].graphicalProperties.solidFill = "ff3399"
chart1.series[6].graphicalProperties.solidFill = "ff99cc"
ws.add_chart(chart1, "A11")
chart1 = BarChart()
chart1.type = "col"
# chart1.style = 12
chart1.width = 21
chart1.height = 12
chart1.grouping = "stacked"
chart1.overlap = 100
chart1.y_axis.title = 'Volume du trafic en %'
# chart1.x_axis.title = 'Heure'
chart1.gapWidth = 2
data = Reference(ws_data, min_col=2, min_row=32, max_row=56, max_col=8)
# cats = Reference(ws_data, min_col=2, min_row=32, max_row=56)
chart1.add_data(data, titles_from_data=True)
# chart1.set_categories(cats)
chart1.shape = 4
chart1.legend = None
chart1.series[0].graphicalProperties.solidFill = "00a9ff"
chart1.series[1].graphicalProperties.solidFill = "bce273"
chart1.series[2].graphicalProperties.solidFill = "ff708c"
chart1.series[3].graphicalProperties.solidFill = "003366"
chart1.series[4].graphicalProperties.solidFill = "ff00ff"
chart1.series[5].graphicalProperties.solidFill = "ff3399"
chart1.series[6].graphicalProperties.solidFill = "ff99cc"
ws.add_chart(chart1, "A46")
def ExportToExcel():
h = open('final.txt','r')
h = h.read()
book = openpyxl.Workbook()
sheet1 = book.active
sheet1.cell(column=1,row=1,value='Server')
sheet1.cell(column=2,row=1,value='Consumption')
sheet1.cell(column=3,row=1,value='Output')
sheet1.cell(column=4,row=1,value='Average')
sername = [sername.split()[0] for sername in h.splitlines()]
consump = [float(consump.split()[1].replace(',','')) for consump in h.splitlines()]
output = [int(output.split()[2]) for output in h.splitlines()]
for row in range(len(sername)):
_ = sheet1.cell(column=1, row=row+2, value="%s" %sername[row])
_ = sheet1.cell(column=2, row=row+2, value=consump[row])
_ = sheet1.cell(column=3, row=row+2, value=output[row])
_ = sheet1.cell(column=4, row=row+2, value="=B%d/C%d" %(row+2,row+2))
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Server vs Consumption"
chart1.y_axis.title = 'Consumption'
chart1.x_axis.title = 'Server Name'
data = Reference(sheet1, min_col=2, min_row=1, max_row=len(sername)+1, max_col=3)
cats = Reference(sheet1, min_col=1, min_row=2, max_row=len(sername)+1)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
sheet1.add_chart(chart1, "I1")
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Server vs Consumption"
chart1.y_axis.title = 'Consumption'
chart1.x_axis.title = 'Server Name'
data = Reference(sheet1, min_col=4, min_row=1, max_row=len(sername)+1, max_col=4)
cats = Reference(sheet1, min_col=1, min_row=2, max_row=len(sername)+1)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
sheet1.add_chart(chart1, "I20")
global name
name = "EnergyConsumption_{}.xlsx".format(datetime.datetime.now().date())
book.save(name)
return
def make_bar(sheet, left_col, top_row, bot_row, title, x_title, y_title,
print_cell, height, width):
left_col = int(left_col)
right_col = left_col + 1
top_row = int(top_row)
bot_row = int(bot_row)
title = str(title)
print_cell = str(print_cell)
height = float(height)
width = float(width)
bar = BarChart()
bar.type = "col"
bar.style = 10
bar.shape = 4
bar.title = title
bar.y_axis.title = y_title
bar.x_axis.title = x_title
labels = Reference(sheet,
min_col=left_col,
max_col=left_col,
min_row=top_row + 1,
max_row=bot_row)
data = Reference(sheet,
min_col=right_col,
max_col=right_col,
min_row=top_row,
max_row=bot_row)
bar.add_data(data, titles_from_data=True)
bar.set_categories(labels)
bar.height = height
bar.width = width
sheet.add_chart(bar, print_cell)
def createGraph(excel_file, df):
sheet_name = "Data"
sheet_name_graphs = "Graphs"
writer = pd.ExcelWriter(excel_file, engine="openpyxl")
book = load_workbook(excel_file)
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
ws_graph = writer.book.create_sheet(sheet_name_graphs)
df.to_excel(writer, sheet_name=sheet_name)
ws = writer.sheets[sheet_name]
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "ETL Results"
chart1.y_axis.title = "Count"
chart1.x_axis.title = "Programm"
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
chart1.height = 12
chart1.width = 20
ws_graph.add_chart(chart1, "A10")
writer.save()
def draw_aging_inflow_graph(self):
chart = BarChart()
chart.type = "col"
chart.title = "IR Aging - Top 5 Apps"
chart.style = 10
chart.x_axis.title = 'Applications'
chart.y_axis.title = 'Aging value'
data = Reference(self.graph_workbook_sheet1,
min_col=2,
min_row=1,
max_row=6,
max_col=5)
cats = Reference(self.graph_workbook_sheet1,
min_col=1,
min_row=2,
max_row=6)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 6
self.graph_workbook_sheet1.add_chart(chart, "G2")
self.graph_workbook.save('final_graph.xlsx')
print("######SUCCESSFUL......GRAPH IS READY######")
print()
print()
print("--- %s seconds for Aging---" % (time.time() - self.start_time))
def graph(purposes, averages, worksheet):
#writes the titles in the spreadsheet
worksheet.cell(row=1, column=1, value="purposes")
worksheet.cell(row=1, column=2, value="avg_rate")
#writes the purposes with the corresponding averages values in the spreadsheet
for index, item in enumerate(purposes):
worksheet.cell(row=(index + 2), column=1, value=item)
worksheet.cell(row=(index + 2), column=2, value=averages[index])
#creates the Excel bar graph
chart1 = BarChart()
chart1.type = "col"
chart1.style = 13
chart1.height = 20
chart1.width = 20
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Average Rates'
chart1.x_axis.title = 'Purpose'
data = Reference(worksheet, min_col=2, max_col=2, min_row=2, max_row=13)
cats = Reference(worksheet, min_col=1, max_col=1, min_row=2, max_row=13)
chart1.add_data(data)
chart1.set_categories(cats)
chart1.shape = 4
worksheet.add_chart(chart1, "E4") #adds the graph to the spreadsheet
def _draw_bar_chart(self, row_):
"""
画垂直条形图
:param row_: 起始行
:return: None
"""
ws = self._wb['analysis']
bar = BarChart()
bar.type = 'bar'
bar.style = 11
bar.title = '失败接口概况图'
bar.y_axis.title = '通过或失败用例个数'
if row_ != 4:
data = Reference(ws,
min_col=2,
min_row=3,
max_row=row_ - 1,
max_col=3)
else:
data = Reference(ws, min_col=2, min_row=3, max_row=row_, max_col=3)
if row_ != 4:
cats = Reference(ws, min_col=1, min_row=4, max_row=row_ - 1)
else:
cats = Reference(ws, min_col=1, min_row=4, max_row=row_)
bar.add_data(data, titles_from_data=True)
bar.set_categories(cats)
bar.shape = 4
ws.add_chart(bar, "F12")
bar.width = 30
bar.height = 0.5 * (row_ + 20) # 根据行数计算自适应条形图高度
self._log.info('已生成失败接口概况条形图.')
def draw_top5(dadaist):
wb = openpyxl.load_workbook("total.xlsx")
sheets = wb.get_sheet_names()
ws = wb.create_sheet(title="top5", index=len(sheets))
rows = dadaist
for row in rows:
ws.append(row)
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Top5反馈评级数"
chart1.y_axis.title = '数量'
chart1.x_axis.title = '人名'
data = Reference(ws,
min_col=2,
min_row=1,
max_row=len(rows),
max_col=len(rows[0]))
cats = Reference(ws, min_col=1, min_row=2, max_row=len(rows))
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")
wb.save("total.xlsx")
def drawBarChart(self, fileName, sheetName, saveFileName = None):
spelertjes = Spelertjes()
if saveFileName is None:
saveFileName = fileName
#read all the data using openpyxl and write data to grafiek tab
wb = load_workbook(fileName)
ws = wb['gegevens']
goals = {"staart":{1:0,2:0,3:0,4:0}, "linkervleugel":{1:0,2:0,3:0,4:0},"rechtervleugel":{1:0,2:0,3:0,4:0},
"piloot":{1:0,2:0,3:0,4:0},"keeper":{1:0,2:0,3:0,4:0}}
positions = ["staart","linkervleuger","rechtervleuger","piloot","keeper"]
iterrows = iter(ws.rows)
next(iterrows)
for row in iterrows:
position = goals[row[1].value]
position[row[3].value] += row[2].value
goals[row[1].value] = position
ws = wb['grafiek']
for i in range(2,6):
cellref = ws.cell(1, i)
cellref.value = i - 1
for i in range(2,7):
cellref = ws.cell(i, 1)
cellref.value = positions[i-2]
row = 2
for i in goals.values():
column = 2
for j in i.values():
cellref = ws.cell(row, column)
cellref.value = j
column += 1
row += 1
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "goals per position per birth cat"
chart1.y_axis.title = 'goals'
chart1.x_axis.title = 'position'
data = Reference(ws, min_col=2, min_row=1, max_row=6, max_col=5)
cats = Reference(ws, min_col=1, min_row=1 , max_row=6)
chart1.add_data(data,titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "C24")
wb.save(saveFileName)
pd.DataFrame(goals).plot(kind='bar')
plt.xlabel("geboortecategorie")
plt.ylabel("aantal gemaakte goals")
plt.grid(True, alpha=0.5)
plt.show()
def bar_chart_demo():
wb = Workbook(write_only=True)
ws = wb.create_sheet()
rows = [
('Number', 'Batch 1', 'Batch 2'),
(2, 10, 30),
(3, 40, 60),
(4, 50, 70),
(5, 20, 10),
(6, 10, 40),
(7, 50, 30),
]
for row in rows:
ws.append(row)
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")
from copy import deepcopy
chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"
ws.add_chart(chart2, "I10")
chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12
chart3.grouping = "stacked"
chart3.overlap = 100
chart3.title = 'Stacked Chart'
ws.add_chart(chart3, "A27")
chart4 = deepcopy(chart1)
chart4.type = "bar"
chart4.style = 13
chart4.grouping = "percentStacked"
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'
ws.add_chart(chart4, "I27")
wb.save("out/bar.xlsx")
def set_charts(self, ws):
chart_peso = BarChart()
chart_peso.type = "col"
chart_peso.style = 10
chart_peso.title = "Peso"
data = Reference(ws, min_col=5, min_row=29, max_col=15)
chart_peso.add_data(data, from_rows=True)
chart_peso.shape = 4
ws.add_chart(chart_peso, "C14")
chart_grasa = BarChart()
chart_grasa.type = "col"
chart_grasa.style = 10
chart_grasa.title = "%GRASA (YC)"
data = Reference(ws, min_col=5, min_row=45, max_col=15)
chart_grasa.add_data(data, from_rows=True)
chart_grasa.shape = 4
ws.add_chart(chart_grasa, "C30")
def sheetThree():
ws = wb.create_sheet('Evaluation Summary')
ruleSummaryRes = client.get_compliance_summary_by_config_rule()
global column_num
column_num = 3
ws.merge_cells('A1:D1')
ws['A1'] = "Evaluation Summary(by config rule)"
#border line
comp = ruleSummaryRes['ComplianceSummary']['CompliantResourceCount'][
'CappedCount']
noncomp = ruleSummaryRes['ComplianceSummary']['NonCompliantResourceCount'][
'CappedCount']
#rows = [
# ('', '준수', '미준수', '전체'),
# ('Rule count',comp,noncomp,comp+noncomp)
#]
excelData = ['AWS Config rule 개수', comp, noncomp, comp + noncomp]
saveContent(excelData, ws)
#for row in rows:
# ws.append(row)
bc = BarChart()
bc.type = "col"
bc.style = 10
bc.title = "Evaluation Summary"
bc.y_axis.title = 'Rule Count'
bc.x_axis.title = 'Compliant Status'
data = Reference(ws, min_col=2, min_row=1, max_row=4, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=4)
bc.add_data(data, titles_from_data=True)
bc.set_categories(cats)
bc.shape = 4
bc.width = 8
bc.legend = None
ws.add_chart(bc, "A6")
rownum = 0
while rownum < 3:
ws.cell(row=rownum + 1, column=1).style = paintstyle
ws.cell(row=rownum + 1, column=2).style = paintstyle
ws.cell(row=rownum + 1, column=3).style = paintstyle
ws.cell(row=rownum + 1, column=4).style = paintstyle
rownum += 1
setTitleCell(ws.cell(row=1, column=1))
excelColName = ['', '준수', '미준수', '전체']
writeColName(excelColName, 2, ws)
ws.row_dimensions[1].height = 25
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 15
print('Evaluation Summary sheet done')
def draw_bar2D(
self,
sheet_name,
data_position=[],
label_position=[],
display_position="A10",
title="Bar Chart",
x_title="display_x",
y_title="display_y",
is_display_legend=False
):
"""
数据必须为列。。。不知道为啥。。
"""
ws = self.wb[sheet_name]
# 数据所在列的坐标范围， 不包含label
DATA_COL_MIN = data_position[0]
DATA_COL_MAX = data_position[1]
DATA_ROW_MIN = data_position[2]
DATA_ROW_MAX = data_position[3]
# label 所在范围
LABEL_COL_MIN = label_position[0]
LABEL_COL_MAX = label_position[1]
LABEL_ROW_MIN = label_position[2]
LABEL_ROW_MAX = label_position[3]
# 创建 chart 对象
chart1 = BarChart()
# 竖直的柱状图"col"
chart1.type = "col"
chart1.style = 10
chart1.shape = 4
chart1.title = title
chart1.y_axis.title = y_title
chart1.x_axis.title = x_title
data = Reference(ws, min_col=DATA_COL_MIN, max_col=DATA_COL_MAX, min_row=DATA_ROW_MIN, max_row=DATA_ROW_MAX)
cats = Reference(ws, min_col=LABEL_COL_MIN, max_col=LABEL_COL_MAX, min_row=LABEL_ROW_MIN, max_row=LABEL_ROW_MAX)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
# label , 柱状图上的数字
chart1.dLbls = DataLabelList()
chart1.dLbls.showVal = True
# 是否显示图例图例
if is_display_legend == False:
chart1.legend = None
# 显示位置
ws.add_chart(chart1, display_position)
def add_barchart_sheet_to_excel(excel_path, name, df):
"""
*add chart sheet using "df" data, to existing / new workbook (excel file)
*all df data will be inserted to sheet, but only first 2 columns will be used for the chart
:str: excel_path: path for the relevant excel
:str: name: name for the sheet and chart
:pandas.Dataframe: df: the data to insert to sheet and base the chart on
"""
# create excel if doesn't exist
remove_default_sheet = False
if not os.path.isfile(excel_path):
wb = openpyxl.Workbook().save(excel_path)
remove_default_sheet = True
# init workbook
workbook = openpyxl.load_workbook(excel_path)
if name in workbook.sheetnames:
raise Exception('"{0}" sheet already exists in "{1}"'.format(
name, excel_path))
writer = pd.ExcelWriter(excel_path, engine='openpyxl')
writer.book = workbook
# create sheet and add df to it
df.to_excel(writer, sheet_name=name)
writer.save()
writer.close()
# set chart attributes
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = name
chart.x_axis.title = df.columns[0]
chart.y_axis.title = df.columns[1]
# create chart
worksheet = workbook[name]
# categories == x-axis, data == y-axis
categories = Reference(worksheet,
min_col=2,
min_row=2,
max_row=len(df) + 2)
data = Reference(worksheet, min_col=3, min_row=1, max_row=len(df) + 2)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4
worksheet.add_chart(chart, "A{0}".format(len(df) + 5))
# remove default sheet if created the excel in current function iteration
if remove_default_sheet:
del workbook["Sheet"]
workbook.save(excel_path)
workbook.close()
def make_bar_chart(ws, bar_title, x_title, y_title):
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = bar_title
chart.y_axis.title = y_title
chart.x_axis.title = x_title
data = Reference(ws, min_col=1, min_row=1, max_row=8, max_col=2)
cats = Reference(ws, min_col=1, min_row=2, max_row=8)
chart.add_data(data, titles_from_data=True)
chart.shape = 4
ws.add_chart(chart, "A10")
return ws
def draw_incident_inflow_graph(self):
chart = BarChart()
chart.type = "col"
chart.title = "IR Inflow - Last 7 Days - Top 5 Apps"
chart.style = 10
chart.x_axis.title = 'Applications'
chart.y_axis.title = 'Inflow_Value'
data = Reference(self.graph_workbook_sheet, min_col=2, min_row=1, max_row= 6, max_col=8)
cats = Reference(self.graph_workbook_sheet, min_col=1, min_row=2, max_row=6)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 6
self.graph_workbook_sheet.add_chart(chart, "K2")
def get_handler():
workbook = load_workbook('total.xlsx')
worksheet = workbook["total"]
# 读取数据部分 存入dict
dict = {}
for cell in list(worksheet.columns)[0]:
if cell.value in dict:
dict[cell.value] += 1
else:
addtemp = {cell.value: 1}
dict.update(addtemp)
# print("==========测试打印===========")
# for eachprint in dict:
# print(eachprint, ":", dict[eachprint])
# print("==========测试打印===========")
# 新建工作表 无则创建 有则删除重新创建
all_sheets = workbook.sheetnames
findsheet = False
for i in range(len(all_sheets)):
if all_sheets[i] == "charthandler":
findsheet = True
if findsheet:
worksheetnew = workbook["charthandler"]
workbook.remove(worksheetnew)
worksheetnew = workbook.create_sheet("charthandler")
# 将数据写入新工作表
worksheetnew.append(["姓名", "处理反馈数"])
for i in dict:
worksheetnew.append([i, dict[i]])
# 创建表格
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "处理人数据统计"
chart1.y_axis.title = '处理反馈数'
chart1.x_axis.title = '问题处理人'
# row是行 col是列
data = Reference(worksheetnew, min_col=2, min_row=1, max_row=worksheetnew.max_row, max_col=worksheetnew.max_column)
cats = Reference(worksheetnew, min_col=1, min_row=2, max_row=worksheetnew.max_row)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
worksheetnew.add_chart(chart1, "A10")
# 最后保存工作簿
workbook.save("total.xlsx")
def test_my_way(ws, bigArr, bigTitle):
try:
row = 1
col = 1
chardata = bigArr[3][:]
chardata.append(bigArr[4][1])
char = [[] for i in range(6)]
char[0] = ["Severity", "SE", "QE"]
for i in range(len(chardata)):
for j in range(len(chardata[0])):
char[j + 1].append(chardata[i][j])
write_arr_in_sheet(ws, 18, 7, "", char)
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Severity Chart"
chart1.y_axis.title = 'Bug Number'
chart1.x_axis.title = 'Severity'
cats = Reference(ws, min_col=7, min_row=19, max_row=23)
data = Reference(ws, min_col=8, max_col=9, min_row=18, max_row=23)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 6
ws.add_chart(chart1, "G18")
for i in range(5):
write_arr_in_sheet(ws, row, col, bigTitle[i], bigArr[i])
row += 4
row = 1
col += len(bigArr[0][1]) + 1
write_arr_in_sheet(ws, row, col, bigTitle[5], bigArr[5])
row = 1
col += len(bigArr[5][0]) + 1
t = col
write_arr_in_sheet(ws, row, col, bigTitle[6], bigArr[6])
row = 15
col = t
write_arr_in_sheet(ws, row, col, bigTitle[7], bigArr[7])
except:
print "Unexpected error:", sys.exc_info()
print "something wrong"
def create_box_chart(rt_ws):
"""Uses openpyxl built in library to creates box charts of
the data for easy reading"""
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Response Time Comparison"
chart1.y_axis.title = 'Seconds'
chart1.width = 12
data = Reference(rt_ws, min_col=2, min_row=1, max_col=3, max_row=3)
titles = Reference(rt_ws, min_col=1, min_row=2, max_row=3)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(titles)
chart1.shape = 4
rt_ws.add_chart(chart1, "A10")
def DrewChart(path, chartlocation): # drew the char in the report excel file
location = chartlocation + 4
location_1 = 'A' + str(location)
wb = load_workbook(path)
ws = wb.active
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Exception Chart"
chart1.y_axis.title = 'Time(times)'
chart1.x_axis.title = 'Name'
data = Reference(ws, min_col=6, min_row=1, max_row=14) #setting X-RAY
cats = Reference(ws, min_col=2, min_row=2, max_row=14) #setting Y-RAY
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, location_1)
wb.save(path)
def excel_mtf_barchart(ws):
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "MTF Chart"
chart1.y_axis.title = 'MTF'
chart1.x_axis.title = 'ROI'
# Select all data include title
data = Reference(ws, min_col=2, min_row=1, max_row=19, max_col=2)
# Select data only
cats = Reference(ws, min_col=1, min_row=2, max_row=18)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
chart1.x_axis.scaling.min = 0
chart1.x_axis.scaling.max = 18
chart1.y_axis.scaling.min = 0
chart1.y_axis.scaling.max = 1
ws.add_chart(chart1, "G1")
def excel_sfr_barchart(ws):
chart1 = BarChart()
chart1.type = "col"
chart1.style = 12
chart1.title = "SFR Chart"
chart1.y_axis.title = 'SFR'
chart1.x_axis.title = 'ROI'
# Select all data include title
data = Reference(ws, min_col=5, min_row=1, max_row=37, max_col=5)
# Select data only
cats = Reference(ws, min_col=4, min_row=2, max_row=37)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
chart1.x_axis.scaling.min = 0
chart1.x_axis.scaling.max = 37
chart1.y_axis.scaling.min = 0
chart1.y_axis.scaling.max = 1
ws.add_chart(chart1, "G21")
def create_bar_chart(sheet, start_row, size):
bar_chart = BarChart()
bar_chart.type = "col"
bar_chart.style = 10
bar_chart.title = "Память квазициклов"
bar_chart.y_axis.title = 'Память'
bar_chart.x_axis.title = 'Квазициклы'
data = Reference(sheet,
min_col=2,
min_row=start_row - 1,
max_row=start_row + size)
indexes = Reference(sheet,
min_col=1,
min_row=start_row,
max_row=start_row + size)
bar_chart.add_data(data, titles_from_data=True)
bar_chart.set_categories(indexes)
bar_chart.shape = 4
bar_chart.legend = None
return bar_chart
def DrewChart(path, chartlocation): # drew the char in the report excel file
location = chartlocation + 4
location_1 = 'A' + str(location)
wb = load_workbook(path)
ws = wb.active
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Infected Confirmed Numbers"
chart1.y_axis.title = 'Confirmed'
chart1.x_axis.title = 'Area'
data = Reference(ws, min_col=4, min_row=1,
max_row=chartlocation) #setting X-RAY
cats = Reference(ws, min_col=2, min_row=1,
max_row=chartlocation) #setting Y-RAY
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, location_1)
wb.save(path)
def create_xlsx(voting_id):
voting = Voting.objects.get(id=voting_id)
wb = Workbook()
ws = wb.active
ws.title = 'Результаты голосования'
person_votes = voting.person_votes.all()
ws.append(('Персонажи', 'Голоса'))
for person_vote in person_votes:
row = (str(person_vote.person.get_full_name()), int(person_vote.votes))
ws.append(row)
ws.column_dimensions[get_column_letter(1)].width = 40
chart = BarChart()
chart.type = 'bar'
chart.style = 10
chart.title = str(voting.title)
chart.x_axis.title = 'Персонажи'
chart.y_axis.title = 'Голоса'
chart.y_axis.scaling.min = 0
if voting.max_votes:
chart.y_axis.scaling.max = int(voting.max_votes)
max_row = len(person_votes) + 1
data = Reference(ws, min_col=2, min_row=1, max_row=max_row)
cats = Reference(ws, min_col=1, min_row=2, max_row=max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4
ws.add_chart(chart, f'A{max_row + 3}')
io = BytesIO(save_virtual_workbook(wb))
voting.xlsx.save(f'{voting.title}.xlsx', File(io))
voting.xlsx_status = 1
voting.save()
def create_chart(sheet, width, height):
chart = BarChart()
chart.type = 'col'
chart.style = 10
chart.y_axis.title = 'Качество, %'
chart.x_axis.title = 'Классы'
data = Reference(sheet,
min_col=2,
max_col=width + 1,
min_row=sheet.max_row - height,
max_row=sheet.max_row)
cats = Reference(sheet,
min_col=1,
min_row=sheet.max_row - height + 1,
max_row=sheet.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4
sheet.add_chart(chart, 'K{}'.format(sheet.max_row - height))
def chart():
wb = load_workbook('/home/votebot/mysite/commands/voting.xlsx')
ws = wb.active
#pie = PieChart()
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Результаты зрительского голосования"
chart1.y_axis.title = ''
chart1.x_axis.title = ''
labels = Reference(ws, min_col=1, min_row=2, max_row=11)
data = Reference(ws, min_col=2, min_row=2, max_row=11)
#pie.add_data(data, titles_from_data=True)
#pie.set_categories(labels)
#pie.title = "Результаты"
#ws.add_chart(pie, "D1")
chart1.add_data(data, titles_from_data=False)
chart1.set_categories(labels)
chart1.shape = 4
ws.add_chart(chart1, "D1")
wb.save('/home/votebot/mysite/commands/voting.xlsx')
return 'chart added', '', None
def turn_filter(bar_dict):
file_path = base_path + file_name
if file_path:
# 用pandas读取Excel,获取指定sheet的里的数据
df = pd.read_excel(file_path, sheet_name=sheet_name)
# 创建时间筛选数据,根据数据生成多个条件,key为tag显示,len()为需要展示的数据
rows = [('轮次', '缺陷数量')]
char_index = 1
for key in bar_dict.keys():
start_date = bar_dict[key]['start_date'] # 开始时间
end_date = bar_dict[key]['end_date'] # 结束时间
# 筛选列表数据
data = df[(df['创建日期'] >= start_date) & (df['创建日期'] <= end_date)]
rows.append((key, len(data)))
# print(key, start_date, end_date, len(data))
char_index = char_index + 1
# 打开文件，创建新的sheet
wb = load_workbook(file_path)
ws = wb.create_sheet('缺陷轮次统计')
# 添加数据
for row in rows:
ws.append(row)
histogram_chart = BarChart()
histogram_chart.type = "col"
histogram_chart.style = 10
histogram_chart.title = "缺陷轮次统计"
histogram_chart.y_axis.title = '数量'
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=2)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
histogram_chart.add_data(data, titles_from_data=True)
histogram_chart.set_categories(cats)
histogram_chart.shape = 4
# 设置图表位置
ws.add_chart(histogram_chart, "A{0}".format(char_index + 6))
wb.save(file_path)
def ChartDraw(ws, ChartName, MinRow, MinColumn, MaxRow, MaxColumn, CatsCol,
CatsMinRow, CatsMaxRow, ChartPos, xTitle, yTitle):
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = ChartName
chart.y_axis.title = yTitle
chart.x_axis.title = xTitle
data = Reference(ws,
min_row=MinRow,
min_col=MinColumn,
max_row=MaxRow,
max_col=MaxColumn)
cats = Reference(ws,
min_row=CatsMinRow,
max_row=CatsMaxRow,
min_col=CatsCol)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4
ws.add_chart(chart, ChartPos)
def to_excel_barchart(df,
filename="untitled.xlsx",
xlabel='samples',
ylabel='value',
title=''):
"""
Takes every column of a dataframe and plots it as an excel barchart series
"""
wb = Workbook()
ws = wb.active
for r in dataframe_to_rows(df, index=True, header=True):
ws.append(r)
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = title
chart1.y_axis.title = ylabel
chart1.x_axis.title = xlabel
data = Reference(ws,
min_col=2,
min_row=3,
max_row=len(df) + 2,
max_col=len(df.columns))
name_ref = Reference(ws,
min_col=1,
min_row=3,
max_col=1,
max_row=len(df) + 2)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(name_ref)
chart1.shape = 4
ws.add_chart(chart1, "A10")
wb.save(filename)
def create_chart(data):
workbook = Workbook()
ws = workbook.get_active_sheet()
for row in data:
if data.index(row) == 0:
iteration, hits, throws, pi = row
ws.append((iteration, pi, "Actual Value of Pi"))
continue
iteration, hits, throws, pi = row
addPi = float(pi)
ws.append((iteration, addPi, math.pi))
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Buffon's Needle"
chart1.y_axis.title = 'Estimate of Pi'
chart1.x_axis.title = 'Iteration'
reference_data = Reference(ws,
min_col=2,
max_col=3,
min_row=1,
max_row=ws.max_row)
categories = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
chart1.add_data(reference_data, titles_from_data=True)
chart1.set_categories(categories)
chart1.shape = 4
ws.add_chart(chart1, "F1")
workbook.save("piEstimate.xlsx")
workbook.close()
def create_bar_chart_1(file_path):
"""
插入柱形图
:param file_path: Excel 文件路径
:return: None
"""
wb = load_workbook(file_path)
st = wb.active
data1 = Reference(st, min_col=2, min_row=1, max_row=7, max_col=3)
cats1 = Reference(st, min_col=1, min_row=2, max_row=7)
chart1 = BarChart()
chart1.type = "col"
chart1.style = 9
chart1.title = "日均值对比"
# chart1.y_axis.title = '数值'
chart1.x_axis.title = st.cell(column=1, row=1).value
chart1.add_data(data1, titles_from_data=True)
chart1.set_categories(cats1)
chart1.shape = 0
st.add_chart(chart1, 'A8')
wb.save(file_path)
import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
wb = Workbook()
ws = wb.active
df = pd.read_csv('store_sales.csv')
ws.append(df.columns.tolist())
for row in df.values:
ws.append(list(row))
row_length = 1 + len(df.values)
data = Reference(ws, min_col=2, max_col=4, min_row=1, max_row=row_length)
categories = Reference(ws, min_col=1, min_row=2, max_row=row_length)
chart = BarChart()
chart.type = 'col'
chart.shape = 4
chart.grouping = 'percentStacked' #←100%積み上げ棒グラフを表すpercentStackedを指定
chart.overlap = 100
chart.title = '支店別売り上げ (100%積み上げ)'
chart.x_axis.title = '支店'
chart.y_axis.title = '売上'
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, 'A9')
wb.save('store_sales_vertical_percent_stacked.xlsx')
from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference
wb = Workbook(write_only=True)
ws = wb.create_sheet()
rows = [
('Number', 'Batch 1', 'Batch 2'),
(2, 10, 30),
(3, 40, 60),
(4, 50, 70),
(5, 20, 10),
(6, 10, 40),
(7, 50, 30),
]
for row in rows:
ws.append(row)
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "Bar Chart"
chart.y_axis.title = 'Test number'
chart.x_axis.title = 'Sample length (mm)'
xData = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
yData = Reference(ws, min_col=1, min_row=2, max_row=7)
chart.add_data(xData, titles_from_data=True)
chart.set_categories(yData)
chart.shape = 4
ws.add_chart(chart, "B10")
wb.save('data/barCharts.xlsx')
def draw_excel_charts(sblog_dict, excel_filename):
"""
This function accepts a defaultdict which contains all the data of tps and rt
an will create an Microsoft Excel spreadsheet with charts.
:param sblog_dict:
:param excel_filename:
:return:
"""
clean_dict = data_cleansing(sblog_dict)
tps_data, rt_data, avg_rt_data, tps_std_data, rt_std_data = [], [], [], [], []
workload_cols_rows = {}
workload_types = set()
col_name_parsed = False
for key in clean_dict.keys():
data_list = clean_dict[key]
col_name, tps, rt, avg_rt, tps_std, rt_std = zip(*data_list)
if not col_name_parsed:
rt_data.append(col_name)
tps_data.append(col_name)
avg_rt_data.append(col_name)
tps_std_data.append(col_name)
rt_std_data.append(col_name)
workload_types = set(x.split('_')[1] for x in col_name[1:])
workload_cols_rows.update({wl_type: {'cols': 0, 'rows': 0} for wl_type in workload_types})
col_name_parsed = True
tps_data.append(tps)
rt_data.append(rt)
avg_rt_data.append(avg_rt)
tps_std_data.append(tps_std)
rt_std_data.append(rt_std)
# print('tps_data: {}'.format(tps_data))
# print('rt_data: {}'.format(rt_data))
# print('avg_rt_data: {}'.format(avg_rt_data))
wb = Workbook(write_only=True)
for wl_type in workload_types:
wb.create_sheet(title=get_sheetname_by_workload(wl_type))
merged_rows = []
for tps, rt, avg_rt, tps_std, rt_std in zip(tps_data, rt_data, avg_rt_data, tps_std_data, rt_std_data):
merged_rows.append(tps + rt + avg_rt + tps_std + rt_std)
# print(merged_rows)
# The tps chart:
# print('merged_rows: {}\n'.format(merged_rows))
for row in merged_rows:
for wl_type in workload_types:
wl_row = [row[i] for i in range(len(row)) if
wl_type in merged_rows[0][i].split('_') or i == 0 or merged_rows[0][i] == 'Thread']
# print('wl_row: {}'.format(wl_row))
wb.get_sheet_by_name(get_sheetname_by_workload(wl_type)).append(wl_row)
workload_cols_rows[wl_type]['cols'] = len(wl_row)
workload_cols_rows[wl_type]['rows'] += 1
for ws in wb:
global_max_row = workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows']
# Chart of TPS
chart_tps = BarChart(gapWidth=500)
chart_tps.type = "col"
chart_tps.style = 10
chart_tps.title = "TPS chart of {}".format(ws.title)
chart_tps.y_axis.title = 'tps'
chart_tps.y_axis.scaling.min = 0
chart_tps.x_axis.title = 'tps'
data_tps = Reference(ws, min_col=2, min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] / 5)
cats_tps = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_tps.add_data(data_tps, titles_from_data=True)
chart_tps.set_categories(cats_tps)
chart_tps.shape = 4
ws.add_chart(chart_tps, "A{}".format(global_max_row + 5))
# Chart of Response Time
chart_rt = BarChart(gapWidth=500)
chart_rt.type = "col"
chart_rt.style = 10
chart_rt.title = "Response Time(95%) chart of {}".format(ws.title)
chart_rt.y_axis.title = 'rt'
chart_rt.y_axis.scaling.min = 0
chart_rt.x_axis.title = 'response time'
data_rt = Reference(ws, min_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] / 5 + 2,
min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 2 / 5)
cats_rt = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_rt.add_data(data_rt, titles_from_data=True)
chart_rt.set_categories(cats_rt)
chart_rt.shape = 4
ws.add_chart(chart_rt, "I{}".format(global_max_row + 5))
# Chart of avg response time
chart_avg_rt = BarChart(gapWidth=500)
chart_avg_rt.type = "col"
chart_avg_rt.style = 10
chart_avg_rt.title = "Average Response Time chart of {}".format(ws.title)
chart_avg_rt.y_axis.title = 'avg rt'
chart_avg_rt.y_axis.scaling.min = 0
chart_avg_rt.x_axis.title = 'avg resp time'
data_avg_rt = Reference(ws, min_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 2 / 5 + 2,
min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 3 / 5)
cats_avg_rt = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_avg_rt.add_data(data_avg_rt, titles_from_data=True)
chart_avg_rt.set_categories(cats_avg_rt)
chart_avg_rt.shape = 4
ws.add_chart(chart_avg_rt, "Q{}".format(global_max_row + 5))
# Chart of tps standard deviation
chart_tps_std = BarChart(gapWidth=500)
chart_tps_std.type = "col"
chart_tps_std.style = 10
chart_tps_std.title = "tps standard deviation chart of {}".format(ws.title)
chart_tps_std.y_axis.title = 'std'
chart_tps_std.y_axis.scaling.min = 0
chart_tps_std.x_axis.title = 'tps std'
data_tps_std = Reference(ws, min_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 3 / 5 + 2,
min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 4 / 5)
cats_tps_std = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_tps_std.add_data(data_tps_std, titles_from_data=True)
chart_tps_std.set_categories(cats_tps_std)
chart_tps_std.shape = 4
ws.add_chart(chart_tps_std, "A{}".format(global_max_row + 20))
# Chart of response time standard deviation
chart_rt_std = BarChart(gapWidth=500)
chart_rt_std.type = "col"
chart_rt_std.style = 10
chart_rt_std.title = "response time standard deviation chart of {}".format(ws.title)
chart_rt_std.y_axis.title = 'std'
chart_rt_std.y_axis.scaling.min = 0
chart_rt_std.x_axis.title = 'rt std'
data_rt_std = Reference(ws, min_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 4 / 5 + 2,
min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 5 / 5)
cats_rt_std = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_rt_std.add_data(data_rt_std, titles_from_data=True)
chart_rt_std.set_categories(cats_rt_std)
chart_rt_std.shape = 4
ws.add_chart(chart_rt_std, "I{}".format(global_max_row + 20))
wb.save(excel_filename)
def pop_excl (sv_dict, ClusName):
wb = openpyxl.Workbook ()
sh = wb.active
count1 = 0
count2 = 2
alph = ['a', 'b', 'c', 'd']
f = sh['a1']
f.font = Font (bold=True)
f = sh['b1']
f.font = Font (bold=True)
sh.title = 'HighLevel'
sh['a1'] = 'StorageView'
sh['b1'] = 'Size(G)'
for i in sv_dict:
sh[alph[count1] + str (count2)] = i
count1 += 1
sh[alph[count1] + str (count2)] = float (sv_dict[i][-1][-1])
count2 += 1
count1 = 0
count2 = 2
for i in sv_dict:
sh = wb.create_sheet (i)
sh = wb.get_sheet_by_name (i)
f = sh['a1']
f.font = Font (bold=True)
f = sh['b1']
f.font = Font (bold=True)
f = sh['c1']
f.font = Font (bold=True)
f = sh['d1']
f.font = Font (bold=True)
sh['a1'] = 'LunID'
sh['b1'] = 'Name'
sh['c1'] = 'VPD'
sh['d1'] = 'Size(G/T)'
for j in range (len (sv_dict[i])):
for k in range (4):
sh[alph[count1] + str (count2)] = sv_dict[i][j][k]
count1 += 1
count2 += 1
count1 = 0
count2 = 2
logging.debug('Start of chart')
l = len(sv_dict)
sh = wb.get_sheet_by_name ('HighLevel')
logging.debug('sheets: %s' % (wb.get_sheet_names ()))
logging.debug('sh: %s' % (sh.title))
chart1 = BarChart()
chart1.type = "col"
chart1.style = 11
chart1.title = "VPlex Capacity Report"
chart1.y_axis.title = 'Size'
chart1.x_axis.title = 'View Name'
logging.debug('len of sv_dict: %d' % (l))
data = Reference(sh, min_col=2, min_row=2, max_row=l + 1, max_col=2)
cats = Reference(sh, min_col=1, min_row=2, max_row=l + 1)
chart1.add_data(data, titles_from_data=False)
chart1.set_categories(cats)
chart1.top = 100
chart1.left = 30
chart1.width = 27
chart1.height = 10
chart1.shape = sh.add_chart(chart1, "D2")
wb.save (ClusName)
return 0
for row in rows:
ws.append(row)
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Membership'
chart1.x_axis.title = 'Month'
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")
from copy import deepcopy
chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"
ws.add_chart(chart2, "G10")
chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12
