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