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 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 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 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 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 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 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 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 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 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 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 excel_processor(filename):
wb = xl.load_workbook(filename)
# Grab excel sheet to work with.
# In order to know the available sheet names,
# use the wb.sheetnames to view sheet arrays
sheet = wb['Sheet1']
sheet['D1'] = "Corrected Price"
# Access sheet cell
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = cell.value * 0.9
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.x_axis.title = "New Price"
chart.y_axis.title = "Y-Axis"
chart.title = "New corrected price chart"
chart.add_data(values)
sheet.add_chart(chart, 'E2')
wb.save(filename)
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 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 process_workbook(file_name):
wb = xl.load_workbook(file_name)
sheet = wb['Sheet1']
# lopping over rows
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = cell.value * 0.9
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
sheet['D1'] = 'discounted_price' # new column title
# create barchart
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'f2')
chart.y_axis.title = 'Discounted price'
chart.x_axis.title = 'Product ID'
chart.title = "Sales - Q1"
series = chart.series[0]
fill = PatternFillProperties(prst="pct5")
fill.foreground = ColorChoice(prstClr="red")
fill.background = ColorChoice(prstClr="blue")
series.graphicalProperties.pattFill = fill
wb.save(file_name)
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 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 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 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 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 bar_chart():
wb = Workbook()
sheet = wb.active
for i in range(1, 11): # create some data in column A
sheet['A' + str(i)] = i
refObj = Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=10)
seriesObj = Series(refObj, title='First series')
chartObj = BarChart()
chartObj.title = 'My Chart'
chartObj.append(seriesObj)
sheet.add_chart(chartObj, 'C5')
wb.save('chart.xlsx')
def add_counter(ws):
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "PCIE Test Summary"
chart1.y_axis.title = 'Number'
chart1.x_axis.title = ''
data1 = Reference(ws, min_col=1, min_row=1, max_row=2, max_col=1)
chart1.add_data(data1, titles_from_data=True)
data2 = Reference(ws, min_col=2, min_row=1, max_row=2, max_col=2)
chart1.add_data(data2, titles_from_data=True)
ws.add_chart(chart1, CHART_LOCATION)
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 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 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 draw_chart(self, wsheet, series, left=0, title=""):
chart = BarChart()
chart.title = title
chart.drawing.left = 500 + left
chart.drawing.top = 250 + left
chart.drawing.height = 200
chart.drawing.width = 500
i = 0
for serie in series:
if len(serie) == 4:
x1 = int(serie[0])
y1 = int(serie[1])
x2 = int(serie[2])
y2 = int(serie[3])
if x2 > 3:
if i == 0:
legend = Reference(wsheet, (0, 0))
labels = Reference(wsheet, (x1, 0), (x2, 0))
else:
if y1 < 13:
legend = Reference(wsheet, (0, 4))
if y1 >= 13:
legend = Reference(wsheet, (0, 13))
#value = wsheet.cell(row=4,column=0).value
title = wsheet.title
if title.find("Rate") < 0:
isLabel = True
else:
if len(title) > 12:
isLabel = True
else:
isLabel = False
if i == 0 and isLabel: # type(value).__name__ <> 'int':
seri = Serie(
Reference(wsheet, (x1, y1), (x2, y2)), labels=labels, legend=legend)
else:
seri = Serie(
Reference(wsheet, (x1, y1), (x2, y2)), legend=legend)
chart.add_serie(seri)
i += 1
wsheet.add_chart(chart)
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 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')
rows = [
['Aliens', 2, 3, 4, 5, 6, 7],
['Humans', 10, 40, 50, 20, 10, 50],
]
for row in rows:
ws.append(row)
c1 = BarChart()
v1 = Reference(ws, min_col=1, min_row=1, max_col=7)
c1.add_data(v1, titles_from_data=True, from_rows=True)
c1.x_axis.title = 'Days'
c1.y_axis.title = 'Aliens'
c1.y_axis.majorGridlines = None
c1.title = 'Survey results'
# Create a second chart
c2 = LineChart()
v2 = Reference(ws, min_col=1, min_row=2, max_col=7)
c2.add_data(v2, titles_from_data=True, from_rows=True)
c2.y_axis.axId = 200
c2.y_axis.title = "Humans"
# 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, "D4")
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')
(3,),
(2,),
(3,),
(3,),
(1,),
(2,),
]
for r in rows:
ws.append(r)
c = BarChart()
data = Reference(ws, min_col=1, min_row=1, max_row=8)
c.add_data(data, titles_from_data=True)
c.title = "Chart with patterns"
# set a pattern for the whole series
series = c.series[0]
fill = PatternFillProperties(prst="pct5")
fill.foreground = ColorChoice(prstClr="red")
fill.background = ColorChoice(prstClr="blue")
series.graphicalProperties.pattFill = fill
# set a pattern for a 6th data point (0-indexed)
pt = DataPoint(idx=5)
pt.graphicalProperties.pattFill = PatternFillProperties(prst="ltHorz")
series.dPt.append(pt)
ws.add_chart(c, "C1")
(3, 22, 5),
(4, 21, 7),
(5, 45, 1),
(6, 15, 4),
(7, 12, 3),
]
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"
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)
continue
else:
wb = Workbook()
sheet = wb.active
monty = opassage[0:8]
preview = "".join([s for s in monty if s != " "])
sheet.title = "{}".format(preview)
i = 1
for word, occur in ordered_dict:
sheet['A' + str(i)] = word
sheet['B' + str(i)] = occur
i += 1
values = Reference(sheet, min_col=1,min_row=1,max_col=2,max_row=len(ordered_dict))
chart = BarChart()
chart.type = "col"
chart.style = 11
chart.title = "Word Frequency"
chart.y_axis.title = "Frequency"
chart.x_axis.title = "Word"
chart.add_data(values, titles_from_data=True)
sheet.add_chart(chart, "D2")
if not(isdir("C:/WordCounterExports")):
mkdir("C:/WordCounterExports/")
name = "wc{}.xlsx".format(preview)
wb.save("C:/WordCounterExports/{}".format(name))
print("Workbook successfully created at C:/WordCounterExports/ named {}".format(name))
continue
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")
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
