def pie_chart(): data = [ ['Pie', 'Sold'], ['Apple', 50], ['Cherry', 30], ['Pumpkin', 10], ['Chocolate', 40], ] wb = Workbook() ws = wb.active for row in data: ws.append(row) pie = PieChart() labels = Reference(ws, min_col=1, min_row=2, max_row=5) data = Reference(ws, min_col=2, min_row=1, max_row=5) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = "Pies sold by category" # Cut the first slice out of the pie slice = DataPoint(idx=0, explosion=20) pie.series[0].data_points = [slice] ws.add_chart(pie, "D1") wb.save("chart.xlsx")
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 create_chart_file(name_file, ws_result): actual_path = os.path.abspath(os.path.dirname(__file__)) result_file_path = os.path.join(actual_path, f"../files/result_files/{name_file}.xlsx") try: wb = openpyxl.load_workbook(result_file_path) chart_worksheet = wb.create_sheet(title="Gráfico") chart_worksheet['A1'] = "Total Sucess" chart_worksheet['A2'] = "Total Failed" chart_worksheet['B1'] = f'=COUNTIF(${ws_result}.E:E;"Sucess")' chart_worksheet['B2'] = f'=COUNTIF(${ws_result}.E:E;"Failed")' pie_chart = PieChart() values = Reference(chart_worksheet, min_col=1, min_row=1, max_col=2, max_row=2) labels = Reference(chart_worksheet, min_col=1, min_row=1, max_col=2, max_row=2) pie_chart.add_data(values, titles_from_data=True) pie_chart.set_categories(labels) chart_worksheet.add_chart(pie_chart, "A1") wb.save(result_file_path) except Exception as e: print(e)
def pie_chart(wp): table = wp.get_sheet_by_name(wp.get_sheet_names()[0]) #生成饼图对象 pie = PieChart() #图的标题 pie.title = "API接口测试统计" '''行数和列数都是从1开始的,和遍历用例是一样都是从1开始''' #获取标签(取范围第一列的最小行数和最大行数) labels = Reference(table, min_col=4, min_row=6, max_col=4, max_row=7) #获取数据(取范围第二列的最小行数-1和最大行数) data = Reference(table, min_col=5, min_row=5, max_col=5, max_row=7) #添加数据和标签到饼图中 pie.add_data(data, titles_from_data=True) pie.set_categories(labels) #放在excel表中 table.add_chart(pie, "A10") #保存excel # wb.save("test1.xlsx") # wb = load_workbook("D:\\PycharmProjects\\test.xlsx") # pie_chart(wb)
def __init__(self, db_name, save_atexit_only=True): self.db_name = db_name if path.exists(self.db_name): self.wb = openpyxl.load_workbook(self.db_name) self.sheet = self.wb.active else: self.wb = openpyxl.Workbook() self.sheet = self.wb.active data = Reference(self.sheet, min_col=4, min_row=1, max_col=6, max_row=2) chart = PieChart() chart.title = 'Статистика' chart.add_data(data, titles_from_data=True) self.sheet.add_chart(chart, 'D4') self.sheet['D1'] = 'Негативных' self.sheet['D2'] = '=COUNTIF(B3:B20000,"Негативный")' self.sheet['E1'] = 'Нейтральных' self.sheet['E2'] = '=COUNTIF(B3:B20000,"Нейтральный")' self.sheet['F1'] = 'Позитивных' self.sheet['F2'] = '=COUNTIF(B3:B20000,"Позитивный")' if save_atexit_only: atexit.register(self.save)
def add_pie_chart(writer, df): # 初始化excel并确立图表类型 sheet = writer.sheets['全球最新疫情分布'] chart = PieChart() # 设置插入序列 max_row = len(df) labels = Reference(sheet, min_col=2, max_col=2, min_row=2, max_row=max_row + 1) chart_data = Reference(sheet, min_col=3, max_col=3, min_row=1, max_row=max_row + 1) chart.add_data(chart_data, titles_from_data=True) chart.set_categories(labels) # 设置图表格式 chart.title = "除湖北外全球各地区确诊人数占比" chart.height, chart.width = 14, 21 chart.style = 5 chart.dataLabels = DataLabelList() chart.dataLabels.showCatName = True chart.dataLabels.showPercent = True # 插入图表 sheet.add_chart(chart, 'F1') writer.save()
def create_pie_chart(sheet, numOfStocks, sectors): dictSector = Counter(sectors) pie = PieChart() labels = Reference(sheet, min_col=1, min_row=2, max_row=numOfStocks + 1) data = Reference(sheet, min_col=5, min_row=1, max_row=numOfStocks + 1) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = "Percentage of Shares in Portfolio" pie.height = 12 pie.width = 20 sheet.add_chart(pie, "K1") pie2 = PieChart() labels = Reference(sheet, min_col=7, min_row=27, max_row=26 + len(dictSector)) data = Reference(sheet, min_col=8, min_row=26, max_row=26 + len(dictSector)) pie2.add_data(data, titles_from_data=True) pie2.set_categories(labels) pie2.title = "Percentage of Sectors in Portfolio" pie2.height = 12 pie2.width = 20 sheet.add_chart(pie2, "K26")
def excel_automation(filename): # for experiment, ignore it # wb = xl.load_workbook("transactions.xlsx") wb = xl.load_workbook(f"{filename}") sheet = wb['Sheet1'] for row in range(2, sheet.max_row + 1): cell = sheet.cell(row, 3) # add corrected formula here corrected_price = cell.value * 0.7 corrected_price_cell = sheet.cell(row, sheet.max_column + 1) corrected_price_cell.value = corrected_price graphValues = Reference(sheet, min_row=2, max_row=sheet.max_row, min_col=4, max_col=4) chart = PieChart() chart.add_data(graphValues) sheet.add_chart(chart, 'f2') wb.save(f'{filename}')
def make_pie(sheet, left_col, top_row, bot_row, 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) pie = PieChart() 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) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = title pie.height = height pie.width = width sheet.add_chart(pie, print_cell) return None
def pieChart(): from openpyxl import Workbook from openpyxl.chart import PieChart, Reference data = [ ['水果', '销量'], ['苹果', 50], ['樱桃', 30], ['橘子', 10], ['香蕉', 40], ] wb = Workbook() ws = wb.active for row in data: ws.append(row) pie = PieChart() labels = Reference(ws, min_col=1, min_row=2, max_row=5) data = Reference(ws, min_col=2, min_row=1, max_row=5) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = "水果销量占比" ws.add_chart(pie, "D1") wb.save('piechart.xlsx') print('打开文件 piechart.xlsx 查看结果')
def summarize_amount(file: str, sheet_name: str): wb = openpyxl.load_workbook(file) sheet = wb[sheet_name] last_row = sheet.max_row last_col = sheet.max_column for i in range(2, last_row + 1): for j in range(6, last_col): sheet.cell(row=i, column=j).number_format = '#,##,0.00' sheet.cell(row=i, column=last_col).number_format = '0.00%' summaries = [ {'location': (last_row + 2, 1), 'letter': 'A', 'labels': ['招商银行', '恒生银行', '银河', '华盛*', '富途*', '蛋卷*', '同花顺'], 'category': 'platform', 'anchor': 'K1'}, {'location': (last_row + 2, 4), 'letter': 'B', 'labels': ['rmb', 'hkd', 'usd'], 'category': 'currency', 'anchor': 'K16'}, {'location': (last_row + 2, 7), 'letter': 'E', 'labels': [0, 1, 2, 3], 'category': 'risk', 'anchor': 'K31'} ] for summary in summaries: row, col = summary['location'] le = [get_column_letter(j) for j in range(col, col + 3)] for i in range(len(summary['labels'])): sheet.cell(row=row+i, column=col).value = summary['labels'][i] c = sheet.cell(row=row+i, column=col+1) c.number_format = "#,##,0.00" c.value = '=SUMIF(${0}$2:${0}${1},{2}{3},$H$2:$H${1})'.format(summary['letter'], last_row, le[0], row + i) c = sheet.cell(row=row+i, column=col+2) c.number_format = "#,##,0.00" c.value = '=SUMIF(${0}$2:${0}${1},{2}{3},$I$2:$I${1})'.format(summary['letter'], last_row, le[0], row + i) sheet.cell(row=row+i+1, column=col).value = 'sum' c = sheet.cell(row=row+i+1, column=col+1) c.number_format = "#,##,0.00" c.value = '=SUM({0}{1}:{0}{2})'.format(le[1], row, row + i) c = sheet.cell(row=row+i+1, column=col+2) c.number_format = "#,##,0.00" c.value = '=SUM({0}{1}:{0}{2})'.format(le[2], row, row + i) pie = PieChart() labels = Reference(sheet, min_col=col, min_row=row, max_row=row+i) data = Reference(sheet, min_col=col+1, min_row=row-1, max_row=row+i) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = summary['category'] sheet.add_chart(pie, summary['anchor']) wb.save(file)
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 plot_sentiment(tickers): df = scored_news(tickers) #getrange of headlines dates and daily scores dates = date_range(df) daily = daily_sentiment(tickers) df = tally_scores(df).T blank = [] columns = list(df) path = "C:\\Users\\matth\\Desktop\\stock-sentiment-analyzer\\pie.xlsx" sf = StyleFrame(daily.T) col_list = list(daily.columns) writer = pd.ExcelWriter(path, engine='xlsxwriter') daily.T.to_excel(excel_writer=writer, sheet_name='Daily Sentiment Summary') worksheet = writer.sheets['Daily Sentiment Summary'] worksheet.set_column('B:B', 15) writer.save() wb = load_workbook("pie.xlsx") for i in range(0, len(df.columns)): ws = wb.create_sheet(tickers[i]) score = 1 for index, row in df.iterrows(): vals = [] if index == 'tickers': continue vals.append(df.loc[index, i]) vals.insert(0, df.index[score]) score = score + 1 ws.append(vals) pie = PieChart() labels = Reference(ws, min_col=1, max_col=1, min_row=1, max_row=3) data = Reference(ws, min_col=2, max_col=2, min_row=1, max_row=3) pie.add_data(data) pie.set_categories(labels) pie.title = tickers[i] + ' Sentiment' pie.layout = Layout(manualLayout=ManualLayout( h=0.7, w=0.7, )) ws.add_chart(pie, "E3") wb.save("pie.xlsx")
def drawFinalPieChart(sheetName): wb = loadExcel() ws = wb.get_sheet_by_name(sheetName) pie = PieChart() labels = Reference(ws, min_col=1, min_row=2, max_row=3) data = Reference(ws, min_col=2, min_row=1, max_row=3) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = "AWSB FINAL TEST REPORT" ws.add_chart(pie, "A6") wb.save(excelSheetName)
def make_chart_carrier(result_file="MainTestResults/MainResult.xlsx", summary_file="CarrierTestResults" "/CarrierTestResult.json"): try: wb = load_workbook(os.path.join(current_path, result_file)) except: wb = Workbook() if "Carrier Summary" in wb.sheetnames: del wb["Carrier Summary"] wb.create_sheet("Carrier Summary") ws = wb["Carrier Summary"] _ = ws.cell(row=1, column=1, value='Carrier Summary') _.font = Font(bold=True) # summary_file = "CarrierTestResults/CarrierTestResult.json" with open(os.path.join(current_path, summary_file), 'r') as f: res = json.load(f) ips = [key for key in res.keys() if key != 'summary'] row = 1 pie_row_start = 4 for ip in ips: data_to_add = [["made ip", ip]] for key in res[ip].keys(): data_to_add.append([key, res[ip][key]]) for line in data_to_add: ws.append(line) pie_row_end = pie_row_start + 3 pie = PieChart() print("pie row start:", pie_row_start) print("pie row end:", pie_row_end) labels = Reference(ws, min_col=1, min_row=pie_row_start, max_row=pie_row_end) data = Reference(ws, min_col=2, min_row=pie_row_start - 1, max_row=pie_row_end) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = ip + " fail rate" pie.dataLabels = DataLabelList() pie.dataLabels.showPercent = True ws.add_chart(pie, "D" + str(row)) pie_row_start += len(data_to_add) row += 18 wb.save(os.path.join(current_path, result_file))
def create_pie_chart(): wb, ws, max_row = load_book(Main) chart_worksheet = wb[Category_Data] cs = wb.create_chartsheet("Category Chart") r = load_chart_data(ws, "Category", chart_worksheet) category_expenses = PieChart() labels = Reference(chart_worksheet, min_col=2, min_row=3, max_row=r - 1) data = Reference(chart_worksheet, min_col=3, min_row=2, max_row=r - 1) category_expenses.add_data(data, titles_from_data=True) category_expenses.set_categories(labels) category_expenses.title = "Expenses by Category" cs.add_chart(category_expenses) wb.save(file_path)
def add_pie_chart(workbook, worksheet_level): pie = PieChart() cats = Reference(worksheet_level, min_col=1, min_row=2, max_row=worksheet_level.max_row) data = Reference(worksheet_level, min_col=2, min_row=1, max_row=worksheet_level.max_row) pie.add_data(data, titles_from_data=True) pie.set_categories(cats) pie.title = '问题评级' data_points = DataPoint(idx=0, explosion=20) pie.series[0].data_points = [data_points] worksheet_level.add_chart(pie, "C9") workbook.save(PATH + '/total.xlsx')
def B(): sheetB = wb.create_sheet('주요국가별 출원동향', 1) B그래프data = Data.주요국출원동향() for r in dataframe_to_rows(B그래프data, index=False, header=True): sheetB.append(r) sheetB.insert_cols(2) for row, cellobj in enumerate(list(sheetB.columns)[1]): n = '=right(A%d,2)' % (row + 1) cellobj.value = n sheetB['B22'] = '합계' sheetB['C22'] = '=SUM(C2:C21)' sheetB['D22'] = '=SUM(D2:D21)' sheetB['E22'] = '=SUM(E2:E21)' sheetB['F22'] = '=SUM(F2:F21)' chartB1 = LineChart() dataB1 = Reference(sheetB, min_col=3, min_row=1, max_row=21, max_col=6) catsB1 = Reference(sheetB, min_col=2, min_row=2, max_row=21) chartB1.add_data(dataB1, titles_from_data=True) chartB1.set_categories(catsB1) chartB1.y_axis.majorGridlines = None chartB1.width = 15 chartB1.height = 10 chartB1.legend.position = 't' chartB1.graphical_properties = GraphicalProperties(ln=LineProperties( noFill=True)) sheetB.add_chart(chartB1, 'H2') chartB2 = PieChart() dataB2 = Reference(sheetB, min_col=3, min_row=22, max_col=6) labelsB2 = Reference(sheetB, min_col=3, min_row=1, max_col=6) chartB2.add_data(dataB2, from_rows=22, titles_from_data=False) chartB2.set_categories(labelsB2) chartB2.width = 5 chartB2.height = 5 chartB2.legend = None chartB2.graphical_properties = GraphicalProperties( ln=LineProperties(noFill=True, solidFill=None, gradFill=None)) chartB2.dLbls = DataLabelList() chartB2.dLbls.showPercent = True sheetB.add_chart(chartB2, 'M1')
def monthChartBreakdown(): '''Makes pie chart for each month''' curMonth = ['', 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'] for i in range(1, mNum+1): pie = PieChart() length_m = len(categories) monthData = Reference(Sum, min_col = i+9, min_row = 2, max_row = length_m-1) labels = Reference(Sum, min_col=8, min_row=2, max_row = length_m-1) pie.add_data(monthData) pie.set_categories(labels) pie.title = curMonth[i]+ ' Expenses by Category' pie.width = 18.0 pie.height = 12.0 pie.legend.layout = Layout(manualLayout=ManualLayout(x=0.25, y=0.25, h=0.99, w=0.25)) wb.get_sheet_by_name(TM[i]).add_chart(pie, 'G3')
def export_pie_chart(self, data, title): wb = Workbook() ws = wb.active for row in [('', '')] + data: ws.append(row) pie = PieChart() labels = Reference(ws, min_col=1, min_row=2, max_row=len(data) + 1) items = Reference(ws, min_col=2, min_row=1, max_row=len(data) + 1) pie.add_data(items, titles_from_data=True) pie.set_categories(labels) pie.title = title ws.add_chart(pie, 'A{0}'.format(len(data) + 3)) output = StringIO.StringIO() wb.save(filename=output) output.flush() return output.getvalue()
def export_pie_chart(self, data, title): wb = Workbook() ws = wb.active for row in [('', '')] + data: ws.append(row) pie = PieChart() labels = Reference(ws, min_col=1, min_row=2, max_row=len(data)+1) items = Reference(ws, min_col=2, min_row=1, max_row=len(data)+1) pie.add_data(items, titles_from_data=True) pie.set_categories(labels) pie.title = title ws.add_chart(pie, 'A{0}'.format(len(data)+3)) output = StringIO.StringIO() wb.save(filename=output) output.flush() return output.getvalue()
def _draw_pie_charts(self): """ 画两个饼图 :return: None """ ws = self._wb['analysis'] # 设置单元格值,饼图引用 ws['G3'] = '失败' ws['G4'] = '通过' ws['H3'] = self._api_failure ws['H4'] = self._api_num - self._api_failure ws['N3'] = '失败' ws['N4'] = '通过' ws['O3'] = self._case_failure ws['O4'] = self._case_num - self._case_failure # 画接口饼图 pie = PieChart() labels = Reference(ws, min_col=7, min_row=3, max_row=4) data = Reference(ws, min_col=8, min_row=2, max_row=4) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = "接口执行情况" slice_ = DataPoint(idx=0, explosion=10) pie.series[0].data_points = [slice_] ws.add_chart(pie, "F1") pie.height = 9.5 pie.width = 13 self._log.info('已生成接口执行情况饼图.') # 画用例饼图 pie2 = PieChart() labels2 = Reference(ws, min_col=14, min_row=3, max_row=4) data2 = Reference(ws, min_col=15, min_row=2, max_row=4) pie2.add_data(data2, titles_from_data=True) pie2.set_categories(labels2) pie2.title = "用例执行情况" slice2_ = DataPoint(idx=0, explosion=10) pie2.series[0].data_points = [slice2_] ws.add_chart(pie2, "M1") pie2.height = 9.5 pie2.width = 13 self._log.info('已生成用例执行情况饼图.')
def make_pie_chart(self): sheetnames = self.__wb.get_sheet_names() # ws = self.__wb.get_sheet_by_name(sheetnames[0]) ws_new = self.__wb.create_sheet("pie chart") # 最后一行 max_r = self.__ws.max_row pie = PieChart() labels = Reference(self.__ws, min_col=1, min_row=max_r - 2, max_row=max_r - 1) data = Reference(self.__ws, min_col=2, min_row=max_r - 3, max_row=max_r - 1) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = "Test Result Pie Chart" # Cut the first slice out of the pie slice = DataPoint(idx=0, explosion=20) pie.series[0].data_points = [slice] ws_new.add_chart(pie, "A1") self.__wb.save(self.__filename)
def process_workbook(filename): wb = xl.load_workbook(filename) sheet = wb['Sheet1'] 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 print(type(corrected_price)) value = Reference(sheet, min_row=2, max_row=sheet.max_row, min_col=4, max_col=4) chart = PieChart() chart.add_data(value) sheet.add_chart(chart, 'a2') wb.save(filename) wb.close()
def generate_picks_chart(workbook): print("Generating picks chart") chart = PieChart() chart.title = "Picks" datasheet = workbook['Summary'] data = Reference(datasheet, min_col=4, max_col=4, min_row=13, max_row=17) titles = Reference(datasheet, min_col=1, min_row=14, max_row=17) chart.add_data(data=data, titles_from_data=True) chart.set_categories(titles) chart.dataLabels = DataLabelList() chart.dataLabels.showPercent = True chart.dataLabels.showVal = True chart.dataLabels.showCatName = False chart.dataLabels.showSerName = False chart.dataLabels.showLegendKey = False return chart
def results_to_excel(position, techs, posts_seen, total_found): wb = Workbook() sheet = wb.active date = datetime.datetime.now() # initial format of the sheet sheet.cell(1, 1).value = 'Total posts viewed:' sheet.cell(1, 2).value = posts_seen sheet.cell(1, 4).value = 'Date:' sheet.cell(1, 5).value = date sheet.cell(3, 1).value = 'Tech/Lang' sheet.cell(3, 2).value = 'Number of appearances' sheet.cell(3, 3).value = 'Ocurrence percentage (*)' sheet['F19'] = "(*) respect to the total number of posts viewed" sheet.cell(1, 1).font = Font(name='Arial', bold=True, size=13) sheet.cell(1, 4).font = Font(name='Arial', bold=True, size=13) sheet.cell(3, 1).font = Font(name='Arial', bold=True, size=13) sheet.cell(3, 2).font = Font(name='Arial', bold=True, size=13) sheet.cell(3, 3).font = Font(name='Arial', bold=True, size=13) sheet['F19'].font = Font(name='Arial', size=13) # dump the techs dictionary the sheet row = 4 for key, value in techs.items(): sheet.cell(row, 1).value = key sheet.cell(row, 2).value = value sheet.cell(row, 3).value = f'{"{:.2f}".format(value*100/posts_seen)}%' sheet.cell(row, 3).alignment = Alignment(horizontal='right') row += 1 # generates the chart of the results pie = PieChart() labels = Reference(sheet, min_col=1, min_row=4, max_row=row - 1) data = Reference(sheet, min_col=2, min_row=4, max_row=row - 1) pie.add_data(data) pie.set_categories(labels) pie.title = f'Number of times a technology/language was found from {total_found} techs found' sheet.add_chart(pie, 'F3') wb.save(f'{position} {date}.xlsx')
def write_text_xlsx_file(used_list): file1 = open("WordAnalysis.txt", 'w') word_string = list() value_string = list() excel_file = Workbook() sheet = excel_file.active end_word = "A" + str(length) end_data = "B" + str(length) cell_range_word = sheet["A2": end_word] cell_range_data = sheet["B2": end_data] cell_range = sheet["A1": end_data] # 엑셀 범위 지정 sheet["A1"].value = "단어" sheet["B1"].value = "횟수" sheet['C1'].value = "많이 쓰인 단어" sheet['D1'].value = "횟수" marker = 2 # 셀번호 for word in used_list: word_string.append(str(word)) sheet["A" + str(marker)] = str(word) value_string.append(my_list.count(word)) sheet["B"+str(marker)] = my_list.count(word) marker += 1 file1.write(str(word) + " : " + str(my_list.count(word)) + "\n") # 엑셀, 텍스트파일에 자료 이동 row_num = 0 for row in cell_range: if row[1].value > (length / 200): row_num += 1 sheet.cell(row = row_num, column = 4, value = row[1].value) sheet.cell(row = row_num, column = 3, value = row[0].value) # 파이 차트에서 쓸 값 정리 pie = PieChart() labels = Reference(sheet, min_col = 3, min_row = 2, max_row = marker - 1) data = Reference(sheet, min_col = 4, min_row = 1, max_row = marker - 1) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = "Significant words in article" sheet.add_chart(pie, "M1") excel_file.save("Word_AnalyticsFinal.xlsx")
def pie_chart(wb): table = wb.get_sheet_by_name(wb.get_sheet_names()[0]) # 生成饼图对象 pie = PieChart() # 饼图的标题 pie.title = "接口测试统计" # 获取标签 获得的是范围 labels = Reference(table, min_col=7, min_row=11, max_col=7, max_row=13) # 获取数据,需要注意的是excel中的data上面需要留个空行 data = Reference(table, min_col=8, min_row=10, max_col=8, max_row=13) # 添加数据和标签到图表里 pie.add_data(data, titles_from_data=True) pie.set_categories(labels) # 添加图表到sheet里 table.add_chart(pie, "C21") #保存excel wb.save("test1.xlsx")
def sales_func(filename): # Load the file and the sheet tab wb = xl.load_workbook(filename) sheet = wb['Sheet1'] # Create a new price column based on the original price 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 # Bar chart values = Reference(sheet, min_row=1, max_row=sheet.max_row, min_col=3, max_col=4) transaction_id = Reference(sheet, min_row=2, max_row=sheet.max_row, min_col=1, max_col=1) chart = BarChart3D() chart.title = '3D Bar Chart' chart.x_axis.title = "Transaction ID" chart.y_axis.title = "Adjusted Price ($)" chart.add_data(values, titles_from_data=True) chart.set_categories(transaction_id) sheet.add_chart(chart, 'E2') # Pie chart quantities = Reference(sheet, min_row=1, max_row=5, min_col=13, max_col=13) labels = Reference(sheet, min_row=2, max_row=5, min_col=12, max_col=12) chart2 = PieChart() chart2.title = 'Pie Chart' chart2.add_data(quantities, titles_from_data=True) chart2.set_categories(labels) sheet.add_chart(chart2, 'N2') wb.save(filename)
def make_chart_main(result_file="MainTestResults/MainResult.xlsx", result_json='MainTestResults/main_result.json'): try: wb = load_workbook(os.path.join(current_path, result_file)) if "Main Summary" in wb.sheetnames: del wb['Main Summary'] wb.create_sheet('Main Summary') ws = wb['Main Summary'] except: wb = Workbook() ws = wb.active ws.title = 'Main Summary' with open(os.path.join(current_path, result_json), 'r') as f: res = json.load(f) for key in res.keys(): ws.append([key, res[key]]) pie_row_start = 2 pie_row_end = pie_row_start + len(res) - 2 pie = PieChart() print("pie row start:", pie_row_start) print("pie row end:", pie_row_end) labels = Reference(ws, min_col=1, min_row=pie_row_start, max_row=pie_row_end) data = Reference(ws, min_col=2, min_row=pie_row_start - 1, max_row=pie_row_end) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = "main cycle fail rate" pie.dataLabels = DataLabelList() pie.dataLabels.showPercent = True ws.add_chart(pie, "D2") wb.save(os.path.join(current_path, result_file))
def pie(self): try: self.mylogger.info("准备打开用例文件,准备生成图表") wb = openpyxl.load_workbook(self.caseFile) self.mylogger.info("成功用例数:{}个,失败用例数:{}个,不执行用例数:{}个 ".format( self.successNum, self.failedNum, self.noRunNum)) rows = [ ['结果', '数量'], ['成功', self.successNum], ['失败', self.failedNum], ['不执行', self.noRunNum], ] ws = wb.create_sheet('casePie') # 把值添加在excel中 for row in rows: ws.append(row) # 创建图表实例 chart = PieChart() # 设置类别的取值,min_row和max_row有bug,需要加1 labels = Reference(ws, min_col=1, min_row=2, max_row=5) # 设置数据的取值,min_col在哪一行取值 data = Reference(ws, min_col=2, min_row=1, max_row=4) # titles_from_data:鼠标移动上去图表上显示数据 chart.add_data(data, titles_from_data=True) # 设置标题 chart.title = "用例运行结果图表" # 设置类别 chart.set_categories(labels=labels) # 将图表添加在哪个位置 ws.add_chart(chart, 'D1') wb.save(self.caseFile) wb.close() self.mylogger.info("生成图表完成,保存在{}文件中".format(self.caseFile)) except Exception as e: self.mylogger.debug("出现异常,异常日志:{}".format(e))
def createCharts(sheet, recordsLen, rozmowcyLen, operatorzyLen): ''' Creates openpyxl Chart for xls file. sheet: openpyxl sheet recordsLen: int rozmowcyLen: int operatorzyLen: int returns: chartMB, chartSMS, chartMIN, chartPPL''' chartMB = LineChart() chartSMS = LineChart() chartMIN = LineChart() chartPPL = PieChart() chartMB.legend = None chartSMS.legend = None chartMIN.legend = None chartMB.title = "MB" chartSMS.title = "SMS" chartMIN.title = "MIN" chartPPL.title = "Ludzie" data = Reference(sheet, min_row=7, max_row=recordsLen + 7, min_col=2) chartMB.add_data(data, titles_from_data=False) data = Reference(sheet, min_row=7, max_row=recordsLen + 7, min_col=3) chartSMS.add_data(data, titles_from_data=False) data = Reference(sheet, min_row=7, max_row=recordsLen + 7, min_col=4) chartMIN.add_data(data, titles_from_data=False) dates = Reference(sheet, min_row=7, max_row=recordsLen + 7, min_col=1) chartMB.set_categories(dates) chartSMS.set_categories(dates) chartMIN.set_categories(dates) data = Reference(sheet, min_col=6, min_row=5, max_row=5 + rozmowcyLen) labels = Reference(sheet, min_col=8, min_row=5, max_row=5 + rozmowcyLen) chartPPL.add_data(data) chartPPL.set_categories(labels) return chartMB, chartSMS, chartMIN, chartPPL
['Category', 'Number'], ['Paid', paidStaff], ['Volunteer', volunteerStaff], ] wb = Workbook() ws = wb.active ws = wb.create_sheet(title="Ministry Paid") for row in data: ws.append(row) pie = PieChart() labels = Reference(ws, min_col=1, min_row=2, max_row=5) data = Reference(ws, min_col=2, min_row=1, max_row=5) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = "Volunteers vs. Paid" # Cut the first slice out of the pie slice = DataPoint(idx=0, explosion=20) pie.series[0].data_points = [slice] ws.add_chart(pie, "D1") wb.save("pie.xlsx")
def drawSummary(self): self.ws.SetColWid(2,10) self.ws.SetColWid(3,60) self.ws.SetColWid(4,20) self.ws.SetColWid(5,20) self.ws.SetColWid(6,10) self.ws.DrawRegion(2,2,66,6,'thick','Blue 1') self.ws.ws.merge_cells('C3:E3') fmt1 = {'hAlign':'C','vAlign':'C','font':{'emph':'B','size':14}} fmt2 = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Blue 2','font':{'emph':'B','size':11}} fmt2a = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Blue 2','font':{'emph':'B','size':11}} fmt2b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Blue 2','numFmt':'0.0','font':{'emph':'B'}} fmt3 = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Blue 3'} fmt3a = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Blue 3'} fmt3b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Blue 3','numFmt':'0.0','font':{'emph':'B'}} fmt4 = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Green 1'} fmt4b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Green 1','numFmt':'0.0','font':{'emph':'B'}} fmt5 = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Orange 1'} fmt5b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Orange 1','numFmt':'0.0','font':{'emph':'B'}} fmt6 = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Red 1'} fmt6b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Red 1','numFmt':'0.0','font':{'emph':'B'}} fmt7 = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Yellow 1'} fmt7b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Yellow 1','numFmt':'0.0','font':{'emph':'B'}} self.ws.SetCell( 3, 3,'Monthly Summary Statement: Year To Date (YTD)',fmt1) self.ws.SetCell( 6, 3,'Hours',fmt2) self.ws.SetCell( 6, 4,'Hours',fmt2a) self.ws.SetCell( 6, 5,'As a % of Contracted',fmt2a) self.ws.SetCell( 7, 3,'Contracted number of hours',fmt3) self.ws.SetCell( 7, 4,'=SUM(GS243:HE243)',fmt3b) self.ws.SetCell( 7, 5,None,fmt3b) self.ws.SetCell( 8, 3,'Total Hours booked',fmt3) self.ws.SetCell( 8, 4,'=SUM(GS223:HE223)',fmt3b) self.ws.SetCell( 8, 5,None,fmt3b) self.ws.SetCell( 9, 3,'Additional hours worked over contracted',fmt3) self.ws.SetCell( 9, 4,'=SUM(GS242:HE242)',fmt3b) self.ws.SetCell( 9, 5,None,fmt3b) self.ws.SetCell(10, 3,'Number of Heads',fmt3) self.ws.ws.merge_cells('D10:E10') self.ws.SetCell(10, 4,16,fmt3b) self.ws.SetCell(10, 5,None,fmt3b) self.ws.ws.merge_cells('D12:E12') self.ws.ws.merge_cells('D13:E13') self.ws.ws.merge_cells('D14:E14') self.ws.ws.merge_cells('D15:E15') self.ws.ws.merge_cells('D16:E16') self.ws.SetCell(12, 3,'Activity (Summary) AVERAGE ACCROSS YEAR',fmt2) self.ws.SetCell(12, 4,'Percentage %',fmt2a) self.ws.SetCell(13, 3,'Utilisation (Customer Funded works)',fmt4) self.ws.SetCell(13, 4,'=HF224',fmt4b) self.ws.SetCell(13, 5,None,fmt4b) self.ws.SetCell(14, 3,'Utilisation (Pre Sales work)',fmt5) self.ws.SetCell(14, 4,'=HF229',fmt5b) self.ws.SetCell(14, 5,None,fmt5b) self.ws.SetCell(15, 3,'Utilisation (Downtime,Exc Leave and Sickness)',fmt6) self.ws.SetCell(15, 4,'=HF234',fmt6b) self.ws.SetCell(15, 5,None,fmt6b) self.ws.SetCell(16, 3,'Utilisation (Leave and Sickness)',fmt7) self.ws.SetCell(16, 4,'=HF239',fmt7b) self.ws.SetCell(16, 5,None,fmt7b) self.ws.SetCell(19, 3,'Activity (Detailed)',fmt2) self.ws.SetCell(19, 4,'Hours',fmt2a) self.ws.SetCell(19, 5,'As a % of Total',fmt2a) self.ws.SetCell(20, 3,'Support agreement (Software)',fmt4) self.ws.SetCell(20, 4,'=SUM(GS201:HE201)',fmt4b) self.ws.SetCell(20, 5,'=D20/SUM(D20:D27)*100',fmt4b) self.ws.SetCell(21, 3,'Hardware agreement (Hardware)',fmt4) self.ws.SetCell(21, 4,'=SUM(GS202:HE202)',fmt4b) self.ws.SetCell(21, 5,'=D21/SUM(D20:D27)*100',fmt4b) self.ws.SetCell(22, 3,'Post Sales support (SW-Customer Funded)',fmt4) self.ws.SetCell(22, 4,'=SUM(GS205:HE205)',fmt4b) self.ws.SetCell(22, 5,'=D22/SUM(D20:D27)*100',fmt4b) self.ws.SetCell(23, 3,'Post Sales support (HW-Customer Funded',fmt4) self.ws.SetCell(23, 4,'=SUM(GS206:HE206)',fmt4b) self.ws.SetCell(23, 5,'=D23/SUM(D20:D27)*100',fmt4b) self.ws.SetCell(24, 3,'NRE (Customer funded)',fmt4) self.ws.SetCell(24, 4,'=SUM(GS207:HE207)',fmt4b) self.ws.SetCell(24, 5,'=D24/SUM(D20:D27)*100',fmt4b) self.ws.SetCell(25, 3,'Training - Providing - Non Customer Specific',fmt4) self.ws.SetCell(25, 4,'=SUM(GS208:HE208)',fmt4b) self.ws.SetCell(25, 5,'=D25/SUM(D20:D27)*100',fmt4b) self.ws.SetCell(26, 3,'Training - Providing - Customer Specific',fmt4) self.ws.SetCell(26, 4,'=SUM(GS209:HE209)',fmt4b) self.ws.SetCell(26, 5,'=D26/SUM(D20:D27)*100',fmt4b) self.ws.SetCell(27, 3,'Post Sales Support (Warranty Period)',fmt4) self.ws.SetCell(27, 4,'=SUM(GS214:HE214)',fmt4b) self.ws.SetCell(27, 5,'=D27/SUM(D20:D27)*100',fmt4b) self.ws.SetCell(28, 3,'Pre Sales Support',fmt5) self.ws.SetCell(28, 4,'=SUM(GS203:HE203)',fmt5b) self.ws.SetCell(28, 5,'=D28/SUM(D28:D33)*100',fmt5b) self.ws.SetCell(29, 3,'Post Sales Support (Non contract)',fmt5) self.ws.SetCell(29, 4,'=SUM(GS204:HE204)',fmt5b) self.ws.SetCell(29, 5,'=D29/SUM(D28:D33)*100',fmt5b) self.ws.SetCell(30, 3,'Training - Receiving - Non Customer Specific',fmt5) self.ws.SetCell(30, 4,'=SUM(GS210:HE210)',fmt5b) self.ws.SetCell(30, 5,'=D30/SUM(D28:D33)*100',fmt5b) self.ws.SetCell(31, 3,'Training - Receiving - Customer Specific',fmt5) self.ws.SetCell(31, 4,'=SUM(GS211:HE211)',fmt5b) self.ws.SetCell(31, 5,'=D31/SUM(D28:D33)*100',fmt5b) self.ws.SetCell(32, 3,'Internal Business Meeting',fmt5) self.ws.SetCell(32, 4,'=SUM(GS212:HE212)',fmt5b) self.ws.SetCell(32, 5,'=D32/SUM(D28:D33)*100',fmt5b) self.ws.SetCell(33, 3,'Professional Services',fmt5) self.ws.SetCell(33, 4,'=SUM(GS213:HE213)',fmt5b) self.ws.SetCell(33, 5,'=D33/SUM(D28:D33)*100',fmt5b) self.ws.SetCell(34, 3,'Downtime - Excluding Leave & Sickness',fmt6) self.ws.SetCell(34, 4,'=SUM(GS232:HE232)',fmt6b) self.ws.SetCell(34, 5,'=D34/SUM(D34:D34)*100',fmt6b) self.ws.SetCell(35, 3,'Leave and Sickness',fmt7) self.ws.SetCell(35, 4,'=SUM(GS237:HE237)',fmt7b) self.ws.SetCell(35, 5,'=D35/SUM(D35:D35)*100',fmt7b) self.ws.SetCell(37, 3,'Customer split',fmt2) self.ws.SetCell(37, 4,'Hours',fmt2a) self.ws.SetCell(37, 5,'As a % of Total',fmt2a) self.ws.SetCell(38, 3,'Ericsson',fmt3) self.ws.SetCell(38, 4,'=SUM(GS247:HE247)',fmt3b) self.ws.SetCell(38, 5,'=D38/SUM(D38:D44)*100',fmt3b) self.ws.SetCell(39, 3,'Nokia',fmt3) self.ws.SetCell(39, 4,'=SUM(GS248:HE248)',fmt3b) self.ws.SetCell(39, 5,'=D39/SUM(D38:D44)*100',fmt3b) self.ws.SetCell(40, 3,'Alcatel-Lucent',fmt3) self.ws.SetCell(40, 4,'=SUM(GS249:HE249)',fmt3b) self.ws.SetCell(40, 5,'=D40/SUM(D38:D44)*100',fmt3b) self.ws.SetCell(41, 3,'Sum of all other customers',fmt3) self.ws.SetCell(41, 4,'=SUM(GS250:HE250)',fmt3b) self.ws.SetCell(41, 5,'=D41/SUM(D38:D44)*100',fmt3b) self.ws.SetCell(42, 3,'Cobham',fmt3) self.ws.SetCell(42, 4,'=SUM(GS251:HE251)',fmt3b) self.ws.SetCell(42, 5,'=D42/SUM(D38:D44)*100',fmt3b) self.ws.SetCell(43, 3,'Technical Training - All Types',fmt3) self.ws.SetCell(43, 4,'=SUM(GS252:HE251)',fmt3b) self.ws.SetCell(43, 5,'=D43/SUM(D38:D44)*100',fmt3b) self.ws.SetCell(44, 3,'Customer \'Other\'',fmt3) self.ws.SetCell(44, 4,'=SUM(GS253:HE251)',fmt3b) self.ws.SetCell(44, 5,'=D44/SUM(D38:D44)*100',fmt3b) self.ws.SetCell(46, 3,'Labour and Travel',fmt2) self.ws.SetCell(46, 4,'Hours',fmt2a) self.ws.SetCell(46, 5,'As a % of Total',fmt2a) self.ws.SetCell(47, 3,'Number of Labour Hours',fmt3) self.ws.SetCell(47, 4,'=SUM(GS217:HE217)',fmt3b) self.ws.SetCell(47, 5,'=D47/SUM(D47:D49)*100',fmt3b) self.ws.SetCell(48, 3,'Number of Labour Hours',fmt3) self.ws.SetCell(48, 4,'=SUM(GS218:HE218)',fmt3b) self.ws.SetCell(48, 5,'=D48/SUM(D47:D49)*100',fmt3b) self.ws.SetCell(49, 3,'Number of Labour Hours',fmt3) self.ws.SetCell(49, 4,'=SUM(GS219:HE219)',fmt3b) self.ws.SetCell(49, 5,'=D49/SUM(D47:D49)*100',fmt3b) self.ws.DrawBorder( 6, 3, 10, 5,'medium') self.ws.DrawBorder( 6, 3, 6, 5,'medium') self.ws.DrawBorder(12, 3, 16, 5,'medium') self.ws.DrawBorder(12, 3, 12, 5,'medium') self.ws.DrawBorder(19, 3, 35, 5,'medium') self.ws.DrawBorder(19, 3, 19, 5,'medium') self.ws.DrawBorder(37, 3, 44, 5,'medium') self.ws.DrawBorder(37, 3, 37, 5,'medium') self.ws.DrawBorder(46, 3, 49, 5,'medium') self.ws.DrawBorder(46, 3, 46, 5,'medium') ltsChart = PieChart() data = Reference(self.ws.ws,min_col=5,min_row=47,max_row=49) labels = Reference(self.ws.ws,min_col=3,min_row=47,max_row=49) ltsChart.add_data(data) ltsChart.set_categories(labels) ltsChart.title = 'Labour Vs Travel' self.ws.ws.add_chart(ltsChart,'H46')