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 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 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 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 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 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 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 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_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 _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(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 plot_stack_mult(self, sheetname, column, value, unit): df = self._df[[value, column]].pivot_table(index = "DateTime", values = value, columns = column, fill_value = 0) df = df.resample(unit).sum() with pd.ExcelWriter(self._file) as writer: # excelファイルがあるなら追加書きする if os.path.isfile(self._file): writer.book = load_workbook(self._file) # write the df on excel at once df.to_excel(writer, sheet_name = sheetname) sheet = writer.book.active # 対象シートを設定 # step列数ずつ複数のグラフを作成 step = 3 # 一つの図に描画する列数 max_col = sheet.max_column # 最大列数 chart_num = int(max_col / step) # 図の数 for i in range(chart_num): start = i * step + 2 end = start + (step - 1) pos = i * 18 + 1 title = "積み上げ棒グラフ" + str(i + 1) # 図ごとにタイトルを変える chart = BarChart() chart.type = "col" chart.grouping = "stacked" chart.overlap = 100 self._plot(sheet, chart, title = title, xlabel = "月", ylabel = "回数", ymax = 100, start_col = start, end_col = end, position = pos)
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 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 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 _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 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 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 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 add_chart(wb, ts, ws): chart1 = BarChart() chart1.type = "col" chart1.style = 10 chart1.title = "反馈人" chart1.y_axis.title = '次数' chart1.x_axis.title = '姓名' data = Reference(ts, min_col=2, min_row=1, max_row=ts.max_row, max_col=2) cats = Reference(ts, min_col=1, min_row=2, max_col=1, max_row=ts.max_row) chart1.add_data(data, titles_from_data=True) chart1.set_categories(cats) str = 'B{0}'.format(ws.max_row + 10) ws.add_chart(chart1, str) wb.save(PATH + '/total.xlsx')
def createChart(ws): chart1 = BarChart() chart1.type = "col" chart1.style = 10 chart1.title = "Violation occurrence per area" chart1.y_axis.title = 'Violation occurrence' chart1.x_axis.title = 'Violation and Location' cats = Reference(ws, min_col=1, max_col=2, min_row=2, max_row=ws.max_row) data = Reference(ws, min_col=5, max_col=5, min_row=1, max_row=ws.max_row) chart1.add_data(data, titles_from_data=True) chart1.legend = None chart1.set_categories(cats) chart1.width = 30 chart1.height = 15 ws.add_chart(chart1, "K2")
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 open_close_delta(): """Create a sheet for Open/Close delta.""" ws2 = Workbook.wb.create_sheet('Open Close Delta') mysel, connection = DB_Connection.connect_db() cursor = connection.execute('SELECT * FROM {}'.format(stock)) names = list(map(lambda x: x[0], cursor.description)) idx = 1 for i in range(6): if i == 0 or i == 1 or i == 4: ws2.cell(row=1, column=idx).value = names[i] idx += 1 counter = 1 """Write relevant data to Excel.""" for i, row in enumerate(mysel): post = row[0], row[1], row[4] counter += 1 ws2.append(post) Workbook.wb.save(Workbook.wbook_name) """Use pandas to create a new field, Open/Close Delta.""" ws2['D1'] = 'Delta' df = pd.read_excel(Workbook.wbook_name, 'Open Close Delta') df['Delta'] = df['OpenPrice'] - df['ClosePrice'] for dl in range(2, counter+1): ws2['D{}'.format(dl)] = float(df['Delta'][dl-2]) """Plot delta as bar chart.""" ws2.sheet_view.zoomScale = 85 bc_three_d = BarChart() bc_three_d.type = 'col' bc_three_d .style = 10 bc_three_d .title = 'Open/Close Delta for Past 30 Trading Days' bc_three_d.y_axis.title = 'Price' bc_three_d.x_axis.title = 'Date' data = Reference(ws2, min_col=4, min_row=counter-29, max_row=counter, max_col=4) cats = Reference(ws2, min_col=1, min_row=counter-28, max_row=counter) bc_three_d.add_data(data, titles_from_data=True) bc_three_d.set_categories(cats) bc_three_d.height = 20 bc_three_d.width = 30 bc_three_d.legend = None ws2.add_chart(bc_three_d, 'F2') Workbook.wb.save(Workbook.wbook_name)
def genChart(self): ''' Generates a bar graph to visually represent the data. ''' chart = BarChart() chart.type = "col" chart.style = 1 chart.title = None chart.y_axis.title = 'Force' chart.x_axis.title = 'Operation' chartData = Reference(self.workSheet, min_col=1, \ min_row=2, max_row=len(self.forceDataFrame)+1, max_col=1) chart.add_data(chartData, titles_from_data=None) chart.legend = None chart.height = 15 chart.width = 30 self.workSheet.add_chart(chart, "G5")
def chart_generate(types, style, title, y_title, x_title): chart1 = BarChart() chart1.type = types chart1.style = style chart1.title = title chart1.y_axis.title = y_title chart1.x_axis.title = x_title data = Reference(sheet, min_col=2, min_row=1, max_row=max_row, max_col=max_column) cats = Reference(sheet, min_col=1, min_row=2, max_row=max_row) chart1.add_data(data, titles_from_data=True) chart1.set_categories(cats) sheet.add_chart(chart1, "A10") work_sheet.save("C://Users//baozipu//Desktop//sum.xlsx")
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")
('Month', 'Joined', 'Left'), (2, 50, 10), (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)
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")
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 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