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 set_chart(ws):
# 차트의 레이블에 사용할 데이터의 위치를 설정합니다.
# - ws : Excel sheet 가 저장된 변수
# - min_col : Excel sheet 에서 데이터를 읽기 시작하는 위치의 행
# - min_row : Excel sheet 에서 데이터를 읽기 시작하는 위치의 열
# - Title 과 데이터의 레이블을 제외하여, 1+2 = 3 이 됨
# - max_row : Excel sheet 에서 데이터가 있는 마지막 부분의 행
categories = Reference(ws, min_col=1, min_row=3, max_row=8)
# 차트에서 보여줄 데이터의 위치를 설정합니다.
# - ws : Excel sheet 가 저장된 변수
# - min_col : Excel sheet 에서 데이터를 읽기 시작하는 위치의 행
# - min_row : Excel sheet 에서 데이터를 읽기 시작하는 위치의 열
# - Title 을 제외하고, 데이터의 레이블을 포함해서 2 가 됨
# - max_col : Excel sheet 에서 데이터가 있는 마지막 부분의 행
# - max_row : Excel sheet 에서 데이터가 있는 마지막 부분의 열
values = Reference(ws, min_col=1, min_row=2, max_col=5, max_row=8)
# BarChart 를 생성합니다.
chart = BarChart()
# add_data 를 사용해서 앞서 설정한 values 를 넘겨줍니다.
# data 에 제목이 포함되어있으면, titles_from_data 를 True 로 세팅합니다.
chart.add_data(values, titles_from_data=True)
# set_categories 를 사용해서 앞서 설정한 categories 를 설정합니다.
chart.set_categories(categories)
# 차트가 생성될 위치를 지정합니다.
ws.add_chart(chart, 'A10')
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 chartBreakdown():
'''Makes pie chart and bar chart for summary tab'''
pie = PieChart()
z = len(categories)
data = Reference(Sum, min_col=9, min_row=2, max_row= z-1)
labels = Reference(Sum, min_col=8, min_row=2, max_row= z-1)
pie.add_data(data)
pie.set_categories(labels)
pie.title = 'Breakdown of Expenses'
pie.width = 15.0
pie.height = 12.0
pie.legend.layout = Layout(manualLayout=ManualLayout(x=0.25, y=0.25, h=0.99, w=0.25))
Sum.add_chart(pie, 'A1')
pie.dataLabels = DataLabelList()
pie.dataLabels.showPercent = True
bar = BarChart()
barData1 = Reference(Sum, min_col=mNum+9, min_row=1, max_row=z-1)
barData2 = Reference(Sum, min_col=mNum+12, min_row=1, max_row=z-1)
bar.add_data(barData1, titles_from_data=True)
bar.add_data(barData2, titles_from_data=True)
bar.set_categories(labels)
bar.title = 'Goal Comparison'
bar.width = 2.0*z
bar.height = 12.0
bar.legend.layout = Layout(manualLayout=ManualLayout(x=0.25, y=0.25, h=1.99, w=0.25))
Sum.add_chart(bar, 'A28')
def generate_hourly_chart(df_hourly, workbook):
num_rows = df_hourly.shape[0]
num_columns = df_hourly.shape[1] + 1
print("Generating hourly chart for {} rows and {} columns".format(
num_rows, num_columns))
chart = BarChart()
chart.type = "col"
chart.grouping = "stacked"
chart.overlap = 100
chart.style = 12
chart.title = "Hourly"
chart.y_axis.title = "Picks"
chart.x_axis.title = "Hour"
datasheet = workbook['Hourly']
data = Reference(datasheet,
min_col=3,
max_col=num_columns,
min_row=1,
max_row=num_rows)
titles = Reference(datasheet, min_col=2, min_row=2, max_row=num_rows)
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
return chart
def produce(filename, name, date):
wb = xl.load_workbook(filename)
rez = False
for sheet in wb:
if sheet.title == name + date:
ws = wb[name + date]
rez = True
break
if not rez:
ws = wb.create_sheet(title=name + date)
for i in range(1, 13):
ws.cell(row=1, column=i).value = i
ws.cell(row=2, column=i).value = 0
for prod in IterRowDict(wb, "Products"):
for sale in IterRowDict(wb, "Sales"):
if prod["Name"] == name and sale["Date"][6:] == date and prod[
"id"] == prod["id"]:
amount = int(prod["Price"]) * int(sale["Quantity"])
ws.cell(row=2, column=int(sale["Date"][3:5])).value = amount
ws = wb[name + date]
chart = BarChart()
x = Reference(ws, min_col=1, max_col=12, min_row=1)
y = Reference(ws, min_col=1, max_col=12, min_row=2)
chart.append(Series(y))
chart.set_categories(x)
ws.add_chart(chart, "A10")
wb.save(filename)
def insertTotalMoneyCPChart(sheet_obj, num_of_exp, symbol, finalOptionExpDate,
final_chart_col_loc, final_chart_insert_pos,
getNumberColsData):
if (Commonapi.debug == 1):
print("insertTotalMoneyCPChart started")
for i in range(0, (int)(num_of_exp)):
min_cols = (int)(final_chart_col_loc[i])
max_cols = min_cols + 1
m_rows = min_cols + getNumberColsData
print("m_rows min_cols is :", m_rows, min_cols)
chart = BarChart()
chart.type = "col"
chart.grouping = "stacked"
chart.overlap = 100
chart.title = symbol + '_' + finalOptionExpDate[i]
chart.y_axis.title = 'TOTAL_CALL_PUT_TM x K'
chart.x_axis.title = 'Dates'
data = Reference(sheet_obj,
min_col=min_cols,
min_row=1,
max_row=m_rows,
max_col=max_cols)
cats = Reference(sheet_obj, min_col=1, min_row=2, max_row=m_rows)
chart.add_data(data, titles_from_data=True)
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
chart.set_categories(cats)
sheet_obj.add_chart(chart, final_chart_insert_pos[i])
title = symbol + '_' + finalOptionExpDate[i]
print("Bar chart :", title)
if (Commonapi.debug == 1):
print("insertTotalMoneyCPChart ended")
def draw_bar_chart(writer, max_row):
sheet = writer.sheets['死亡率']
date = sheet['A2'].value
# 初始化我们的bar chart
chart = BarChart()
# 指定bar chart 的数据范围
chart_data = Reference(sheet, min_col=6, max_col=6, min_row=2, max_row=max_row-1)
chart_series = Series(chart_data, title='截止{}死亡率'.format(date))
chart_series.graphicalProperties.solidFill = '5DD092' # Silver Tree
# 指定x轴
x_axis_data = Reference(sheet, min_col=3, max_col=3, min_row=2, max_row=max_row-1)
# chart 添加到 sheet里
chart.append(chart_series)
chart.set_categories(x_axis_data)
# 设置chart的样式
chart.height, chart.width = 14, 21
chart.title, chart.y_axis.title = '主要疫情地区死亡率', '死亡率'
chart.y_axis.number_format = '0.0%'
chart.legend.position = 't'
# 画平均死亡率横线line chart
line_chart = LineChart()
line_data = Reference(sheet, min_col=7, max_col=7, min_row=2, max_row=max_row - 1)
line_series = Series(line_data, title='除湖北外死亡率')
line_chart.append(line_series)
# 合并图表
chart += line_chart
# 添加图表并保存
sheet.add_chart(chart, 'I1')
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 _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 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 _do_chi_square_graph(self, ws, title, y_axis, x_axis, labels):
chart = BarChart()
chart.type = 'col'
chart.style = 10
chart.height = 20
chart.width = 30
chart.title = title
chart.y_axis.title = y_axis
chart.x_axis.title = x_axis
# mas uno por la fila de titulos
chart_data = Reference(ws,
min_col=2,
min_row=1,
max_row=len(labels) + 1,
max_col=3)
categories = Reference(ws,
min_col=1,
min_row=2,
max_row=len(labels) + 1)
chart.x_axis.delete = False
chart.y_axis.delete = False
chart.add_data(chart_data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, 'I2')
def chart_bar(self, ws, destination_cell_coordinate,
label_min_row, label_max_row, label_min_col, label_max_col,
data_min_row, data_max_row, data_min_col, data_max_col,
title='',from_rows=False, chart_height = default_chart_height, chart_width = default_chart_width,
is_3d = False, add_chart = True, show_legend=True
, **kw
):
if is_3d:
chart = BarChart3D()
else:
chart = BarChart()
chart.height = chart_height # default is 7.5
chart.width = chart_width # default is 15
labels = Reference(ws, min_row=label_min_row, max_row=label_max_row, min_col=label_min_col, max_col=label_max_col,)
data = Reference(ws, min_row=data_min_row, max_row=data_max_row, min_col=data_min_col, max_col=data_max_col,)
chart.add_data(data, titles_from_data=False, from_rows=from_rows)
chart.set_categories(labels)
chart.title = title
if not show_legend:
chart.legend = None
if add_chart:
ws.add_chart(chart, destination_cell_coordinate)
for seri in chart.series:
seri.label = True
return chart
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 build_graph_in_excel(self, ws, table, start_row_in_excel, start_column_in_excel):
columns_list_from_db = self.get_columns_list_without_id(table)[1:-1].split(', ')
count_rows_in_table_db = self.get_count_rows(table)+1
start_column_number = self.get_number_for_letter(start_column_in_excel) + 1
chart = BarChart()
chart.title = '{}'.format(table)
chart.style = 10
#chart.x_axis.title = 'TEst'
chart.y_axis.title = 'Percentage'
cats = Reference(ws,
min_col=start_column_number,
min_row=start_row_in_excel + 1,
max_row='{}'.format(count_rows_in_table_db)
)
data1 = Reference(ws,
min_col=10,
min_row=1,
max_row='{}'.format(count_rows_in_table_db)
)
data2 = Reference(ws, min_col=14, min_row=1, max_row='{}'.format(count_rows_in_table_db))
chart.add_data(data1, titles_from_data=True)
chart.add_data(data2, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, 'A15')
def sample_output_excel_chart():
# Sample from http://zetcode.com/articles/openpyxl/
book = Workbook()
sheet = book.active
rows = [
("USA", 46),
("China", 38),
("UK", 29),
("Russia", 22),
("South Korea", 13),
("Germany", 11)
]
for row in rows:
sheet.append(row)
data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=6)
categs = Reference(sheet, min_col=1, min_row=1, max_row=6)
chart = BarChart()
chart.add_data(data=data)
chart.set_categories(categs)
chart.legend = None
chart.y_axis.majorGridlines = None
chart.varyColors = True
chart.title = "Olympic Gold medals in London"
sheet.add_chart(chart, "A8")
book.save("bar_chart.xlsx")
def implementChart(listOfDate, Errordate, save_excel_file_today):
wb_obj = openpyxl.load_workbook(save_excel_file_today)
wb_obj.create_sheet('Chart view')
sheet_obj = wb_obj["Chart view"]
Drows = 1
Erows = 1
cell_obj = sheet_obj.cell(row=1, column=1)
cell_obj.value = "listOfDate"
cell_obj = sheet_obj.cell(row=1, column=2)
cell_obj.value = "Error_data"
for i in listOfDate:
cell_obj = sheet_obj.cell(row=Drows + 1, column=1)
cell_obj.value = i
Drows += 1
for i in Errordate:
cell_obj = sheet_obj.cell(row=Erows + 1, column=2)
cell_obj.value = i
Erows += 1
dates = Reference(sheet_obj, min_col=1, min_row=2, max_col=1, max_row=16)
values = Reference(sheet_obj, min_col=2, min_row=2, max_col=2, max_row=16)
chart = BarChart()
chart.add_data(values)
chart.set_categories(dates)
chart.height = 15 # default is 7.5
chart.width = 30
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
chart.title = " 15 Day's Error Count "
chart.x_axis.title = " Dates "
chart.y_axis.title = " Number of Error "
sheet_obj.add_chart(chart, "A1")
wb_obj.save(save_excel_file_today)
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 main():
book = Workbook()
sheet = book.active
rows = [("USA", 46), ("UK", 27), ("China", 26), ("Russia", 19),
("Germany", 17), ("Japan", 12)]
for row in rows:
sheet.append(row)
data = Reference(sheet, min_row=1, min_col=2, max_row=6, max_col=2)
categs = Reference(sheet, min_row=1, min_col=1, max_row=6)
chart = BarChart()
chart.add_data(data=data)
chart.set_categories(categs)
chart.legend = None
chart.y_axis.majorGridlines = None
chart.varyColors = True
chart.title = 'Olympic Gold medals in Rio 2016'
sheet.add_chart(chart, 'A8')
book.save('bar_chart.xlsx')
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 build_advanced_report_for_ticker(self, wb: openpyxl.Workbook,
scenario_result, ticker: str,
scenario):
periods = scenario["total_periods"]
result = scenario_result.get(ticker)
sheet = wb.create_sheet(ticker)
total_revenue_hist = result["total_revenue_hist"][::-1]
total_revenue_growth = result["total_revenue_growth"][::-1]
total_revenue_growth_mean = result["total_revenue_growth_mean"]
hist_data_len = len(total_revenue_hist)
categories = [x for x in range(-hist_data_len + 1, periods + 1)]
sheet.append([" "] + categories)
sheet.append(["total_revenue"] + total_revenue_hist +
result["total_revenue_forward"])
tr_growth_rate_forward = []
revenue = [total_revenue_hist[-1]] + result["total_revenue_forward"]
for i in range(len(revenue) - 1):
tr_growth_rate_forward.append(revenue[i + 1] / revenue[i] - 1)
sheet.append(["total_revenue_growth"] + total_revenue_growth +
tr_growth_rate_forward)
sheet.append([""])
sheet.append(["prices_t"] + result["prices_t"])
chart = LineChart()
chart.y_axis.title = "Total Revenue"
chart.x_axis.title = "Year"
chart.height = 12
chart.width = 24
revenue_data = Reference(sheet,
min_col=2,
min_row=2,
max_row=2,
max_col=hist_data_len + periods + 1)
cat_data = Reference(sheet,
min_col=2,
min_row=1,
max_row=1,
max_col=hist_data_len + periods + 1)
chart.add_data(revenue_data, from_rows=True)
sheet.add_chart(chart, "F8")
chart.set_categories(cat_data)
bar_chart = BarChart()
bar_chart.y_axis.title = "Total Revenue Growth Rate"
bar_chart.x_axis.title = "Year"
bar_chart.height = 12
bar_chart.width = 24
growth_data = Reference(sheet,
min_col=2,
min_row=3,
max_row=3,
max_col=hist_data_len + periods + 1)
bar_chart.add_data(growth_data, from_rows=True)
bar_chart.set_categories(cat_data)
sheet.add_chart(bar_chart, "S8")
for i in range(scenario["total_periods"]):
pass
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 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 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 _set_cv_lv_chart(self, workbook):
ws = workbook['CV_LV']
ws_data = workbook['Data_day']
chart1 = BarChart()
chart1.type = "col"
chart1.width = 19
chart1.height = 8
chart1.style = 10
chart1.y_axis.title = 'Vehicules à moteur en % du TJMO de la section'
chart1.x_axis.title = "Selon l'heure de la journée"
chart1.gapWidth = 0
#chart1.legend = None
data = Reference(ws_data,
min_col=11,
min_row=4,
max_row=28,
max_col=11)
cats = Reference(ws, min_col=1, min_row=5, max_row=28)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
# chart1.shape = 4
s = chart1.series[0]
s.graphicalProperties.line.solidFill = "000000"
s.graphicalProperties.solidFill = "66ffb2"
chart2 = LineChart()
data = Reference(ws_data,
min_col=11,
min_row=34,
max_row=58,
max_col=11)
chart2.add_data(data, titles_from_data=True)
data = Reference(ws_data,
min_col=11,
min_row=65,
max_row=89,
max_col=11)
chart2.add_data(data, titles_from_data=True)
# chart2.style = 12
s = chart2.series[0]
s.graphicalProperties.line.solidFill = "3333ff"
s.smooth = False
s = chart2.series[1]
s.graphicalProperties.line.solidFill = "ff3333"
s.graphicalProperties.line.dashStyle = "sysDash"
s.smooth = False
chart1 += chart2
ws.add_chart(chart1, "A15")
def add_charts(sheet, mark, model, data, cats):
"""draw charts with median and average mileages for each year"""
chart = BarChart()
chart.title = mark + " " + model
chart.type = "col"
chart.y_axis.title = 'Mileage'
chart.x_axis.title = 'Year of prod.'
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
sheet.add_chart(chart, "E3")
def draw_chart(input_wb, max_row):
# 拿到我们需要操作的sheet top10
top_10_sheet = input_wb.worksheets[-1]
top_10_sheet.sheet_view.zoomScale = 200
# 初始化chart
bar_chart = BarChart()
line_chart = LineChart()
pie_chart = PieChart()
# 生成数据
bar_chart_data = Reference(top_10_sheet,
min_col=4,
max_col=7,
min_row=1,
max_row=max_row)
line_chart_data = Reference(top_10_sheet,
min_col=4,
max_col=4,
min_row=1,
max_row=max_row)
pie_chart_data = Reference(top_10_sheet,
min_col=4,
max_col=4,
min_row=1,
max_row=max_row)
# 指定chart的x_axis
x_data = Reference(top_10_sheet,
min_col=2,
max_col=2,
min_row=2,
max_row=max_row)
# 设置chart样式
bar_chart.height, bar_chart.width = 7, 15
line_chart.height, line_chart.width = 7, 15
pie_chart.height, pie_chart.width = 7, 15
bar_chart.title, bar_chart.y_axis.title, bar_chart.x_axis.title = 'top10', '人数', '国家'
# bar_chart.y_axis.scaling.max = 5000000
# 把数据添加进chart
bar_chart.add_data(bar_chart_data, titles_from_data=True)
line_chart.add_data(line_chart_data, titles_from_data=True)
bar_chart.set_categories(x_data)
line_chart.set_categories(x_data)
pie_chart.add_data(pie_chart_data, titles_from_data=True)
pie_chart.set_categories(x_data)
pie_chart.dataLabels = DataLabelList()
pie_chart.dataLabels.showVal = True
pie_chart.dataLabels.showLegendKey = True
# 把chart添加到sheet
# top_10_sheet.add_chart(bar_chart, 'I1')
# top_10_sheet.add_chart(pie_chart, 'I11')
bar_chart += line_chart
top_10_sheet.add_chart(bar_chart, 'I1')
# 保存我们的workbook
input_wb.save('./excel_files/report_chart.xlsx')
def create_chart(self, chart_title, anchor_cell, data_min_col, data_min_row, data_max_col, data_max_row, ref_min_col, ref_min_row, ref_max_row):
ws1 = self.workbook.active
data = Reference(ws1, min_col = data_min_col, min_row = data_min_row, max_col = data_max_col, max_row = data_max_row)
titles = Reference(ws1, min_col = ref_min_col, min_row = ref_min_row, max_row = ref_max_row)
chart = BarChart()
chart.title = chart_title
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
ws1.add_chart(chart, anchor_cell)
self.workbook.save(self.excel_file_name)
def createchart(self, minc = 2, maxc = 3, minr = 3, maxr = 6):
values = Reference(self.ws, min_col=minc, min_row=minr, max_col=maxc, max_row=maxr)
cat = Reference(self.ws, min_col=1, min_row=3, max_row=6)
chart = BarChart()
chart.title = "Loitd example barchart"
chart.style = 10
chart.type = 'col'
chart.x_axis.title = 'X-Axis Title'
chart.y_axis.title = 'Y-Axix Title'
chart.add_data(values, titles_from_data=True)
chart.set_categories(cat)
self.ws.add_chart(chart, "G3")
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 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 chart_openpyxl(worksheet):
global TOTAL_DAYS
print 'generate Chart...'
# create open-high-low-close chart
stock = StockChart()
dates = Reference(worksheet, min_col=1, min_row=31, max_row=TOTAL_DAYS+1)
data = Reference(worksheet, min_col=2, max_col=5, min_row=31, max_row=TOTAL_DAYS+1)
stock.add_data(data, titles_from_data= True)
stock.set_categories(dates)
for s in stock.series:
s.graphicalProperties.line.noFill = True
stock.hiLowLines = ChartLines()
stock.upDownBars = UpDownBars()
# Excel is broken and needs a cache of values in order to display hiLoLines :-/
from openpyxl.chart.data_source import NumData, NumVal
pts = [NumVal(idx=i) for i in range(len(data) - 1)]
cache = NumData(pt=pts)
# add dummy cache
stock.series[-1].val.numRef.numCache = cache
# create 5-30MA chart
bar = BarChart()
data = Reference(worksheet, min_col=9, min_row=31, max_row=TOTAL_DAYS+1)
bar.add_data(data, titles_from_data=False)
bar.set_categories(dates)
# merge K-Line & 5-30MA chart
stock_tmp = deepcopy(bar)
bar_tmp = deepcopy(stock)
stock_tmp.title = "TWSE Mometum Chart"
stock_tmp.height = 15
stock_tmp.width = 25
stock_tmp.y_axis.axId = 20
stock_tmp.z_axis = bar_tmp.y_axis
stock_tmp.y_axis.crosses = "max"
stock_tmp.legend = None # hidden series label name
bar_tmp.y_axis.majorGridlines = None
bar_tmp.y_axis.title = "Price"
stock_tmp += bar_tmp
worksheet.add_chart(stock_tmp, "A{}".format(TOTAL_DAYS+2))
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 insertarGraficoMercadoTC(ws):
maxCol = col2num(getMaxCol(ws, 'B', 46))
c1 = BarChart()
v1 = Reference(ws, min_col=1, min_row=47, max_col=maxCol)
c1.add_data(v1, titles_from_data=True, from_rows=True)
c1.y_axis.scaling.min = 0
c1.y_axis.majorGridlines = None
c2 = LineChart()
v2 = Reference(ws, min_col=1, min_row=48, max_col=maxCol)
c2.add_data(v2, titles_from_data=True, from_rows=True)
c2.y_axis.axId = 200
c1.z_axis = c2.y_axis
categories = Reference(ws, min_col=2, min_row=46, max_col=maxCol)
c1.set_categories(categories)
# Display y-axis of the second chart on the right by setting it to cross the x-axis at its maximum
c1.y_axis.crosses = "max"
c1 += c2
ws.add_chart(c1, "A54")
def save_excel_data():
count = 0
wb=openpyxl.Workbook()
ws1=wb.active
ws1=wb.create_sheet("mysheet2")
ws1.append(['이름','국어','영어'])
for n in myDataList:
count += 1
ws1.append([n['name'],n['korean'],n['english']])
#Chart 1
chart1 = BarChart()
chart1.style=11
chart1.title='Bar chart'
chart1.x_axis.title='이름'
chart1.y_axis.title='점수'
data = Reference(ws1, min_col=1, min_row=1, max_row=count+1, max_col=3)
cat = Reference(ws1, min_col=1, min_row=2, max_row=count+1)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cat)
ws1.add_chart(chart1, 'F1')
wb.save('day2result.xlsx')
print('write...done')
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)
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')
s.graphicalProperties.line.noFill = True c2.hiLowLines = ChartLines() c2.upDownBars = UpDownBars() c2.title = "Open-high-low-close" # add dummy cache c2.series[-1].val.numRef.numCache = cache ws.add_chart(c2, "G10") # Create bar chart for volume bar = BarChart() data = Reference(ws, min_col=2, min_row=1, max_row=6) bar.add_data(data, titles_from_data=True) bar.set_categories(labels) from copy import deepcopy # Volume-high-low-close b1 = deepcopy(bar) c3 = deepcopy(c1) c3.y_axis.majorGridlines = None c3.y_axis.title = "Price" b1.y_axis.axId = 20 b1.z_axis = c3.y_axis b1.y_axis.crosses = "max" b1 += c3 c3.title = "High low close volume"
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"
def ronava_bar_chart(writingSheet, dataSheet, params):
# TODO add dictionary in parameters to avoid overlapping
if params["use"] == "bars":
data = Reference(
dataSheet,
min_col=params["data_min_col"],
min_row=params["data_min_row"],
max_row=params["data_max_row"],
max_col=params["data_max_col"],
)
cats = Reference(
dataSheet,
min_col=params["cats_min_col"],
min_row=params["cats_min_row"],
max_row=params["cats_max_row"],
max_col=params["cats_max_col"],
)
chart = BarChart()
chart.type = params["type"]
chart.style = 12
# chart.grouping = "stacked"
chart.title = params["title"]
chart.y_axis.title = params["y_axis"]
chart.x_axis.title = params["x_axis"]
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.height = params["heigth"]
chart.width = params["width"]
writingSheet.add_chart(chart, "D2")
elif params["use"] == "single":
c1 = BarChart()
v1 = Reference(
dataSheet, min_col=params["data_min_col"], min_row=params["data_min_row"], max_col=params["data_max_col"]
)
cats = Reference(
dataSheet, min_col=params["cats_min_col"], min_row=params["cats_min_row"], max_col=params["cats_max_col"]
)
c1.series = [Series(v1, title_from_data=True)]
c1.style = 12
c1.set_categories(cats)
c1.x_axis.title = params["x_axis"]
c1.y_axis.title = params["y_axis"]
c1.height = params["heigth"]
c1.width = params["width"]
c1.title = params["title"]
writingSheet.add_chart(c1, "D4")
else:
c1 = BarChart()
v1 = Reference(
dataSheet, min_col=params["data_min_col"], min_row=params["data_min_row"], max_col=params["data_max_col"]
)
cats = Reference(
dataSheet, min_col=params["cats_min_col"], min_row=params["cats_min_row"], max_col=params["cats_max_col"]
)
c1.series = [Series(v1, title_from_data=True)]
c1.y_axis.majorGridlines = None
c1.set_categories(cats)
c1.x_axis.title = params["x_axis"]
c1.y_axis.title = params["y_axis"]
c1.height = params["heigth"]
c1.width = params["width"]
c1.title = params["title"]
c1.style = 12
# Create a second chart
c2 = LineChart()
v2 = Reference(
dataSheet,
min_col=params["data_min_col"],
min_row=params["data_min_row"] + 1,
max_col=params["data_max_col"],
)
c2.series = [Series(v2, title_from_data=True)]
c2.y_axis.axId = 20
c2.y_axis.title = "Porcentaje Produccion"
# Assign the y-axis of the second chart to the third axis of the first chart
c1.z_axis = c2.y_axis
c1.y_axis.crosses = "max"
c1 += c2
writingSheet.add_chart(c1, "D4")
