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 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 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 generate_availability_chart(workbook): print("Generating availability chart") chart = BarChart() chart.type = "bar" chart.grouping = "stacked" chart.overlap = 100 chart.style = 12 chart.title = "Availability Details" chart.y_axis.title = "Hours" datasheet = workbook['Summary'] data = Reference(datasheet, min_col=2, max_col=3, 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) return chart
def plot_stack(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() # self._df.to_csv("a.csv", index = True, header = True, encoding='utf_8_sig') 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) # plot Area chart chart = BarChart() chart.type = "col" chart.grouping = "stacked" chart.overlap = 100 self._plot(writer.book[sheetname], chart, title = "積み上げ棒グラフ", xlabel = "月", ylabel = "回数")
def insertCPIOMOIchartToCPMISheet(sheet_obj, symbol, finalOptionExpDate, num_of_exp, final_OTMOI_chart_insert_pos, iotmio_data_col_loc, getNumberColsData, iotmio_row_loc): if (Commonapi.debug == 1): print("insertCPIOMOIchartToCPMISheet started") for i in range(0, num_of_exp): min_rows = (int)(iotmio_row_loc[1]) min_cols = (int)(iotmio_data_col_loc[i]) max_cols = min_cols + 3 max_rows = min_rows + getNumberColsData chart = BarChart() chart.type = "col" chart.grouping = "stacked" chart.overlap = 100 chart.title = symbol + '_' + finalOptionExpDate[i] chart.y_axis.title = final_OTM_chart_y_axis[0] chart.x_axis.title = 'Dates' data = Reference(sheet_obj, min_col=min_cols, min_row=min_rows, max_row=max_rows, max_col=max_cols) cats = Reference(sheet_obj, min_col=1, min_row=min_rows + 1, max_row=max_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_OTMOI_chart_insert_pos[i]) title = symbol + '_' + finalOptionExpDate[i] print("Bar chart :", title) if (Commonapi.debug == 1): print("insertCPIOMOIchartToCPMISheet ended")
def format_output_file(chart_records): read_wb = load_workbook(filename=TEST_RESULTS) logging.info("Compile graphs and reports=> %s", read_wb) total_sheets = len(read_wb.sheetnames) # Css Style thin = Side(border_style="thin", color="000000") fontsize = Font(size=EXCELSHEET_FONT_SIZE) for sheet in range(total_sheets): read_wb.active = sheet ws = read_wb.active sheetRange = ws.calculate_dimension() # set sheet active with index for col in ws.iter_cols(min_row=1, max_col=ws.max_column, max_row=ws.max_row): for cell in col: # Apply excel styles for formating output # Apply border on excel cells cell.alignment = Alignment(horizontal="left", vertical="center", shrinkToFit=True, wrapText=True) cell.border = Border(top=thin, left=thin, right=thin, bottom=thin) # Apply font size on all rows of excel sheet cell.font = fontsize # apply Color on cells w.r.t pass or failed cases if cell.value == PASS_CELL_VALUE: cell.fill = PatternFill(start_color=PASSED_CELL_COLOR, fill_type="solid") cell.font = Font(bold=True) elif cell.value == FAILED_CELL_VALUE: cell.fill = PatternFill(start_color=FAILED_CELL_COLOR, fill_type="solid") cell.font = Font(bold=True) # Dashboard sheet styling sheets = read_wb.sheetnames if 'Sheet' in sheets: read_wb.active = sheets.index(sheets[-1]) ws = read_wb.active # Dashboard heading style ws.merge_cells('A1:Q2') cell = ws['A1'] cell.value = "API Automation Test Results" cell.fill = PatternFill(start_color='35C4F1', fill_type="solid") cell.font = Font(bold=True, size=16) cell.alignment = Alignment(horizontal="left", vertical="center") # print API analysis records in table # add column headings #ws.append(["APINames","TotalTestCases","Executed","Skipped","Passed","Failed"]) # output example [['BalanceInquiry-POST', 3, 3, 0, 3, 0], ['TransactionInquiry-POST', 2, 2, 0, 2, 0]] ws.cell(row=API_TABLE_STARTS_FROM_ROW, column=1).value = "APINames" ws.cell(row=API_TABLE_STARTS_FROM_ROW, column=2).value = "TotalTestCases" ws.cell(row=API_TABLE_STARTS_FROM_ROW, column=3).value = "Executed" ws.cell(row=API_TABLE_STARTS_FROM_ROW, column=4).value = "Skipped" ws.cell(row=API_TABLE_STARTS_FROM_ROW, column=5).value = "Passed" ws.cell(row=API_TABLE_STARTS_FROM_ROW, column=6).value = "Failed" ws.column_dimensions['A'].width = 40 ws.column_dimensions['B'].width = 15 ws.column_dimensions['C'].width = 15 ws.column_dimensions['D'].width = 15 ws.column_dimensions['E'].width = 15 ws.column_dimensions['F'].width = 15 # Adding 1 beause API_TABLE_STARTS_FROM_ROW takes headings row,hence data will inserted from next row rows = API_TABLE_STARTS_FROM_ROW + 1 for api_record in chart_records: columns = 1 for val in api_record: ws.cell(row=rows, column=columns).value = val columns = columns + 1 rows = rows + 1 total_record = API_TABLE_STARTS_FROM_ROW + total_sheets refs = 'A' + str(API_TABLE_STARTS_FROM_ROW) + ":F" + str(total_record) tab = Table(displayName="Table1", ref=refs) # Add a default style with striped rows and banded columns style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) tab.tableStyleInfo = style ws.add_table(tab) #Add First chart/graph chart1 = BarChart() chart1.type = "col" chart1.style = 10 chart1.height = 8 # default is 7.5 chart1.width = 30 # default is 15 chart1.title = "Bar Chart" chart1.y_axis.title = 'Total TestCases' chart1.x_axis.title = 'API Names' #add Category as API name apis = Reference(ws, min_col=1, min_row=int(API_TABLE_STARTS_FROM_ROW + 1), max_row=total_record) data = Reference(ws, min_col=3, min_row=int(API_TABLE_STARTS_FROM_ROW), max_row=total_record, max_col=4) chart1.add_data(data, titles_from_data=True) chart1.set_categories(apis) chart1.shape = 4 chart1.title = 'API TestCase Stats' ws.add_chart(chart1, "A4") #Second chart chart2 = BarChart() chart2.type = "col" chart2.style = 13 chart2.height = 8 # default is 7.5 chart2.width = 30 # default is 15 chart2.grouping = "percentStacked" chart2.title = "API Success Stats" chart2.y_axis.title = 'Success Rate' chart2.overlap = 100 #pick date from cell locations v2 = Reference(ws, min_col=5, min_row=int(API_TABLE_STARTS_FROM_ROW), max_col=6, max_row=total_record) chart2.add_data(v2, titles_from_data=True) chart2.set_categories(apis) # Style the lines s1 = chart2.series[0] s1.graphicalProperties.solidFill = "90EE90" # green s2 = chart2.series[1] s2.graphicalProperties.solidFill = "FF3300" # red #Add Chart at locton ws.add_chart(chart2, "A20") # save formated file read_wb.save(TEST_RESULTS)
min_col=wsheet.min_column, min_row=wsheet.min_row + 1, max_row=wsheet.max_row) chart3.add_data(data, titles_from_data=True) chart3.set_categories(cats) wsheet.add_chart(chart3, "C2") wsheet = wbook["By區和時段"] chart4 = BarChart() chart4.styles = range(0, len(idx3) + 1) chart4.height = 14 chart4.width = 22 chart4.title = "計數-區和時段" chart4.type = "col" chart4.grouping = "percentStacked" #百分比堆疊圖 chart4.overlap = 100 data = Reference(wsheet, min_col=wsheet.min_column + 1, max_col=wsheet.max_column, min_row=wsheet.min_row, max_row=wsheet.max_row) cats = Reference(wsheet, min_col=wsheet.min_column, min_row=wsheet.min_row + 1, max_row=wsheet.max_row) chart4.add_data(data, titles_from_data=True) chart4.set_categories(cats) wsheet.add_chart(chart4, "B14") wbook.save(fileName) path = os.getcwd() + "\\" + fileName print("已產出檔案:", path, ",結束")
if (rfu > max_rfu_c[col_num2] * (second_screen / 100)): ws8.cell(row=v_offset + col_num2, column=cx8 + 1).value = round( (100 * rfu) / minus_2_rfu_c[col_num2], 1) cx8 = cx8 + 1 # calculate placement of data - link to chart ws8.column_dimensions['A'].width = 50 chart1 = BarChart() chart1.type = "col" chart1.style = 2 chart1.width = 0.7 * sites chart1.height = 15 chart1.gapWidth = 10 chart1.overlap = 100 chart1.grouping = "percentStacked" chart1.legend = None # chart1.title = 'Percent Stacked Chart' # chart1.y_axis.title = 'Percentage' # chart1.x_axis.title = 'Sample Sites' # chart1.varyColors = True # chart1.shape = 4 site_list = Reference(ws8, min_col=1, min_row=35, max_row=35 + sites) data = Reference(ws8, min_col=1, min_row=34, max_row=34 + sites, max_col=cx8) chart1.add_data(data, titles_from_data=True) chart1.set_categories(site_list) #chart1.add_data(data)
def sauveTout(nom:str, recap:list): try: log_fret = openpyxl.load_workbook(p.CHEMIN_ECRITURE_RESULTATS+nom) except FileNotFoundError: fonctionsMatrices.print_log_erreur("Le document résultat n'est pas trouvé à l'emplacement "+p.CHEMIN_ECRITURE_RESULTATS+nom, inspect.stack()[0][3]) sys.exit("Le document résultat n'est pas trouvé à l'emplacement "+ p.CHEMIN_ECRITURE_RESULTATS+nom) feuille = log_fret.get_sheet_by_name(log_fret.get_sheet_names()[0]) styleFond, styleTeteTab, styleVal, styleTableau, styleEntree, styleTitre = p.STYLES_EXCEL alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" tableau = [[u[0], u[1][1], u[2][0], u[2][1], u[2][2], u[2][3]]for u in p.MATRICE_RESULTAT] for li in range(1,len(tableau[1:])+1): for col in range(len(tableau[li])): if col>0: feuille.cell(row = li+10, column = col+2).value=tableau[li][col] else: feuille.cell(row = li+10, column = col+2).value=tableau[li][col]+" - "+tableau[li][col+1] if col<=1: feuille.cell(row=li+10, column=col+2).style = styleEntree else: feuille.cell(row=li+10, column=col+2).style = styleVal if col in [2,4]: feuille.cell(row = li+10, column = col+2).number_format = '0.0' tab1 = Table(displayName="Tableau_general", ref="A2:F"+str(len(p.MATRICE_RESULTAT))) style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) tab1.tableStyleInfo = style # rule = DataBarRule(start_type='num', start_value=0, end_type='percentile', end_value='100',color="FF638EC6", showValue="None", minLength=None, maxLength=None) # feuille.conditional_formatting.add("C2:C"+str(len(p.MATRICE_RESULTAT)), rule) feuille.column_dimensions['A'].width = 10 feuille.column_dimensions['B'].width = 17 feuille.column_dimensions['C'].width = 0 feuille.column_dimensions['D'].width = 25 feuille.column_dimensions['E'].width = 6 feuille.column_dimensions['F'].width = 25 feuille.column_dimensions['G'].width = 10 #Affichage du premier graphe général (avec tous les postes) if True: graphique = BarChart() graphique.type = "bar" graphique.style = 12 graphique.title = "Bilan carbone total par poste et par site" graphique.grouping = "standard" graphique.overlap = 00 position_row = 9 position_col = 1 postes = Reference(feuille, min_col = position_col+1, min_row = position_row+2, max_row = position_row+len(p.MATRICE_RESULTAT)-1) for i in [3]: valeurs = Reference(feuille, min_col=position_col+i, min_row=position_row+1, max_row=position_row+len(p.MATRICE_RESULTAT)-1) graphique.add_data(valeurs, titles_from_data=True) graphique.set_categories(postes) graphique.height = 103 graphique.width = 20 secaxe = BarChart() valsecond = Reference(feuille, min_col=position_col+i+2, min_row=position_row+1, max_row=position_row+len(p.MATRICE_RESULTAT)-1) secaxe.add_data(valsecond, titles_from_data=True) secaxe.y_axis.axId = 200 graphique.x_axis.scaling.orientation = "maxMin" secaxe.type = "bar" secaxe.y_axis.title = "Ramenées à la production (kgeCO2/m3)" cp = CharacterProperties(sz=800) #Taille du label entre 100 et 400 000 graphique.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)]) graphique.legend.legendPos = 't' graphique.legend.tagname= 'Légende' graphique.y_axis.title = 'Emissions Carbones (teCO2)' graphique.y_axis.crosses = "min" serie = secaxe.series[0] serie.graphicalProperties.line.solidFill = "FF0000" serie.graphicalProperties.noFill = True graphique += secaxe feuille.add_chart(graphique, "I10") matRes_inv_tot,matRes_inv_m3 = renversermatres(p.MATRICE_RESULTAT) nb_postes = len(matRes_inv_tot)-1 nb_sites = len(matRes_inv_tot[0])-1 # Changement de feuille du classeur feuille = log_fret.create_sheet("Resultats detailles",1) for lin in range(1,200): for col in [x for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"]: case = col+str(lin) feuille[case].style=styleFond for col in [x+y for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ" for y in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"]: case = col+str(lin) feuille[case].style=styleFond
emphase_colonne_n(feuilleFret, 15) emphase_colonne_n(feuilleFret, 16) emphase_colonne_n(feuilleFret, 19) emphase_colonne_n(feuilleFret, 20) #Affichage de graphe if True: #On crée un graphique en barre graphique = BarChart() #Style horizontal graphique.type = "bar" graphique.style = 12 #whatever graphique.title = "FE des matières premières, fabrication + fret moyen "+str(p.ANNEE)+" (tCO2e/t)" graphique.grouping = "stacked" #100% de recouvrement pour avoir la seconde catégorie (axe2) par dessus graphique.overlap = 100 #On prend en abscisses la première colonne mp = Reference(feuilleFret, min_col = 1, min_row = 2, max_row = len(fret_FE)) #Les ordonnées sont en colonnes 10 11 13 for i in [11,12,14,18,19]: #On ajoute une à une les séries valeurs = Reference(feuilleFret, min_col=i, min_row=1, max_row=len(fret_FE)) graphique.add_data(valeurs, titles_from_data=True) graphique.set_categories(mp) #Dimensions du graphique graphique.height = 103 graphique.width = 20 graphique.x_axis.scaling.orientation = "maxMin" cp = CharacterProperties(sz=800) #Taille du label entre 100 et 400 000 graphique.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)])
def sheet_partecipazione(filename, start_time, participants, workbook): worksheet = workbook.create_sheet('Partecipazione') titles = ['Partecipante'] max_interruptions = 0 for pp in participants: if len(pp['periods']) > max_interruptions: max_interruptions = len(pp['periods']) for i in range(max_interruptions): titles.append(f'Inizio {i+1}') titles.append(f'Fine {i+1}') worksheet.append(titles) for cell in worksheet["1:1"]: cell.font = Font(bold=True) i = 1 for pp in participants: i = i + 1 cur_start = start_time row = [pp['name']] for j in range(len(pp['periods'])): p = pp['periods'][j] row.append(p[0] - cur_start) row.append(p[1] - p[0]) cur_start = p[1] worksheet.append(row) for ii in range(1, len(row) + 1): cell = worksheet.cell(i, ii) cell.number_format = 'hh "ore e" mm "minuti"' column_widths = [30] for _i in range(max_interruptions * 2): column_widths.append(20) for ii, column_width in enumerate(column_widths): worksheet.column_dimensions[get_column_letter(ii + 1)].width = column_width max_col = get_column_letter((max_interruptions * 2) + 1) mediumStyle = TableStyleInfo(name='TableStyleMedium2', showRowStripes=True) worksheet.add_table( Table(ref=f'A1:{max_col}{i}', displayName='RegistroPartecipazione', tableStyleInfo=mediumStyle)) worksheet.sheet_view.showGridLines = False report_id = os.path.basename(filename).replace('.json', '') worksheet.append([f'']) worksheet.append([f'Report generato per il meeting con ID: {report_id}']) data = Reference(worksheet, min_col=2, min_row=1, max_row=i, max_col=(max_interruptions * 2) + 1) cats = Reference(worksheet, min_col=1, min_row=2, max_row=i) chart = BarChart() chart.add_data(data, titles_from_data=True) chart.set_categories(cats) chart.type = "bar" chart.grouping = "stacked" chart.overlap = 100 chart.legend = None chart.height = 20 chart.width = 40 chart.x_axis.scaling.orientation = "maxMin" for ii, s in enumerate(chart.series): s.graphicalProperties.line.nofill = True s.graphicalProperties.line.shadow = None if ii % 2 == 0: s.graphicalProperties.noFill = True else: s.graphicalProperties.solidFill = "008F00" worksheet.add_chart(chart, "A43")
def generate_charts(filename): wb = openpyxl.load_workbook(filename) mysheet = wb.get_active_sheet() # 找到最大行 for i in range(3, 30): if mysheet['B' + str(i)].value == None: max_row = i - 1 break # ft = Font(bold=True) mysheet['B' + str(max_row + 1)].value = 'total' mysheet['B' + str(max_row + 1)].font = ft k = 54 t = 61 for x in 'CDEFGHIJKLMN': x_max = x + str(max_row) x_cell = x + str(max_row + 1) mysheet[x_cell].value = '=SUM(' + x + str(4) + ':' + x_max + ')' if x > 'D' and x < 'J': mysheet['C' + str(k)].value = mysheet[x_cell].value k += 1 if x >= 'J' and x <= 'N': mysheet['C' + str(t)].value = mysheet[x_cell].value t += 1 mysheet[x_cell].font = ft chart = BarChart() chart.style = 11 chart.type = "bar" chart.title = "问题燃尽情况" # 系列重叠 chart.overlap = 100 # 添加数据标签 chart.dataLabels = DataLabelList() chart.dataLabels.showVal = True data = Reference(mysheet, min_col=3, min_row=3, max_row=max_row, max_col=4) cats = Reference(mysheet, min_col=2, min_row=4, max_row=max_row) chart.add_data(data, titles_from_data=True) chart.set_categories(cats) chart.shape = 4 mysheet.add_chart(chart, 'B' + str(max_row + 3)) pie1 = PieChart() labels = Reference(mysheet, min_col=2, min_row=54, max_row=58) data = Reference(mysheet, min_col=3, min_row=53, max_row=58) pie1.add_data(data, titles_from_data=True) pie1.set_categories(labels) pie1.title = "问题分类占比" pie1.dataLabels = DataLabelList() pie1.dataLabels.showVal = True # Cut the first slice out of the pie slice = DataPoint(idx=0, explosion=20) pie1.series[0].data_points = [slice] mysheet.add_chart(pie1, 'I' + str(max_row + 3)) pie2 = PieChart() labels = Reference(mysheet, min_col=2, min_row=61, max_row=65) data = Reference(mysheet, min_col=3, min_row=60, max_row=65) pie2.add_data(data, titles_from_data=True) pie2.set_categories(labels) pie2.title = "严重级别占比" pie2.dataLabels = DataLabelList() pie2.dataLabels.showVal = True # Cut the first slice out of the pie slice = DataPoint(idx=0, explosion=20) pie2.series[0].data_points = [slice] mysheet.add_chart(pie2, 'B' + str(max_row + 19)) wb.save(filename)
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 = 'stacked' #←積み上げ棒グラフを表すstackedを指定 chart.overlap = 100 #←積み上げ棒グラフのときは100固定 chart.title = '支店別売り上げ (積み上げ)' 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_stacked.xlsx')
def writeOutput2(outfile,records): #################################### wb = Workbook() summaryColumn = 3 for record in records: sheetname = str(label[record]) wb.create_sheet(sheetname) ws = wb[str(sheetname)] wsummary = wb.worksheets[0] # write labels ws.cell(row=1, column=1).value = "code" ws.cell(row=1, column=2).value = "name" columnVar = 1 rowVar = 2 for x in sorted(major): ws.cell(row=rowVar, column=columnVar).value = x ws.cell(row=rowVar, column=columnVar+1).value = label[x] rowVar += 1 columnVar = 3 rowVar = 2 for r in records[record]: # r is column ws.cell(row=1, column=columnVar).value = label[r] for x in xrange(0,len(major)): code = str(ws.cell(row=rowVar+x, column=1).value) if code in records[record][r]: ws.cell(row=rowVar+x, column=columnVar).value = records[record][r][code] if isinstance(wsummary.cell(row=rowVar+x, column=summaryColumn).value,float): wsummary.cell(row=rowVar+x, column=summaryColumn).value += float(records[record][r][code]) else: wsummary.cell(row=rowVar+x, column=summaryColumn).value = float(records[record][r][code]) columnVar += 1 for col in ws.columns: max_length = 0 column = col[0].column # Get the column name for cell in col: try: # Necessary to avoid error on empty cells if len(str(cell.value)) > max_length: max_length = len(cell.value) except: pass adjusted_width = (max_length) * 1 ws.column_dimensions[column].width = adjusted_width summaryColumn+=1 data = Reference(ws, min_col=3, min_row=1, max_col=columnVar-1, max_row=6) titles = Reference(ws, min_col=2, min_row=2, max_row=6) chart = BarChart() chart.title = label[record] chart.add_data(data=data, titles_from_data=True) chart.set_categories(titles) chart.x_axis.delete = False chart.y_axis.delete = False ws.add_chart(chart, "A10") #################################### # write values ws = wb.worksheets[0] ws.title="summary" columnVar = 1 rowVar = 2 # write labels ws.cell(row=1, column=1).value = "code" ws.cell(row=1, column=2).value = "name" for x in sorted(major): ws.cell(row=rowVar, column=columnVar).value = x ws.cell(row=rowVar, column=columnVar+1).value = label[x] rowVar +=1 # make multiple outputs per project and by per employee columnVar = 1 rowVar = 1 for record in records: ws.cell(row=rowVar, column=columnVar+2).value = label[record] columnVar+=1 for col in ws.columns: max_length = 0 column = col[0].column # Get the column name for cell in col: try: # Necessary to avoid error on empty cells if len(str(cell.value)) > max_length: max_length = len(cell.value) except: pass adjusted_width = (max_length) * 1 ws.column_dimensions[column].width = adjusted_width print outfile, columnVar data = Reference(ws, min_col=3, min_row=1, max_col=columnVar+1, max_row=6) titles = Reference(ws, min_col=2, min_row=2, max_row=6) chart = BarChart3D() chart.title = "3D Bar Chart" chart.add_data(data=data, titles_from_data=True) chart.set_categories(titles) ws.add_chart(chart, "A10") chart3 = BarChart() chart3 = deepcopy(chart) chart3.type = "col" chart3.style = 10 chart3.grouping = "stacked" chart3.overlap = 100 chart3.title = 'Stacked Chart' chart3.set_categories(titles) ws.add_chart(chart3, "A30") chart.title.delete = False chart.x_axis.delete = False chart.y_axis.delete = False chart3.title.delete = False chart3.x_axis.delete = False chart3.y_axis.delete = False wb.save(year+"_"+month+"_"+outfile)
def enregistre_resultat_resume(recap, ligne, colonne, feuille, nb_postes): styleFond, styleTeteTab, styleVal, styleTableau, styleEntree, styleTitre = p.STYLES_EXCEL feuille.cell(row=ligne, column=1+colonne).value ="Regroupement de résultats, les postes principaux" feuille.merge_cells('B'+str(ligne)+':L'+str(ligne)) feuille.cell(row=ligne, column=1+colonne).style = styleEntree feuille.cell(row=ligne, column=1+colonne).font = Font(bold=True) ligne += 2 for li in range(len(recap)): for co in range(len(recap[0])): feuille.cell(row=ligne, column=1+colonne+co).value = recap[li][co] if li == 0: feuille.cell(row=ligne, column=1+colonne+co).style = styleTeteTab elif co<1: feuille.cell(row=ligne, column=1+colonne+co).style = styleEntree else: feuille.cell(row=ligne, column=1+colonne+co).style = styleVal feuille.cell(row = ligne, column = 1+colonne+co).number_format = '0.0' ligne+=1 feuille.cell(row=ligne, column=2).value = "EGES tot dues à la tourbe" for c in range(3, 11): feuille.cell(row=ligne, column=c).style = styleVal feuille.cell(row=ligne, column=8).number_format = '#.0' feuille.cell(row=ligne, column=2).style = styleEntree feuille.cell(row=ligne, column=3).value = p.COMPARAISON_TOURBE[0] feuille.cell(row=ligne, column=3).number_format = '#.0' feuille.cell(row=ligne, column=6).value = p.COMPARAISON_TOURBE[1] feuille.cell(row=ligne, column=6).number_format = '#.0' feuille.cell(row=ligne, column=8).value = p.COMPARAISON_TOURBE[2] feuille.cell(row=ligne, column=8).number_format = '#.0' feuille.cell(row=ligne, column=10).value = "=SUM(C"+str(ligne)+":I"+str(ligne)+")" feuille.cell(row=ligne, column=10).number_format = '#.0' ligne +=1 for li in range(1,len(recap)-1): for co in range(len(recap[0])): if co<1: feuille.cell(row=ligne, column=1+colonne+co).value = recap[li][co]+ " (%)" feuille.cell(row=ligne, column=1+colonne+co).style = styleEntree elif co <=len(recap[0])-1 and li ==1: feuille.cell(row=ligne, column=1+colonne+co).value = recap[li][co]/recap[1][-1] feuille.cell(row=ligne, column=1+colonne+co).style = styleVal feuille.cell(row=ligne, column=1+colonne+co).number_format = '0.0%' elif co <=len(recap[0])-1 and li ==2: feuille.cell(row=ligne, column=1+colonne+co).value = recap[li][co]/recap[2][-1] feuille.cell(row=ligne, column=1+colonne+co).style = styleVal feuille.cell(row=ligne, column=1+colonne+co).number_format = '0.0%' ligne+=1 offset3 = 20+(2*nb_postes) sites = Reference(feuille, min_row = offset3, min_col = 2+colonne, max_col = len(recap[0])+colonne-1) graphique3 = BarChart() graphique3.title = "Emissions totales des sites par poste" graphique3.type = "col";graphique3.style= 10;graphique3.grouping='standard';graphique3.overlap = 25 graphique3.height = 12;graphique3.width = 25 graphique3.x_axis.scaling.orientation = "maxMin" cp = CharacterProperties(sz=1000) #Taille du label entre 100 et 400 000 graphique3.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)]) graphique3.varyColors=True valeurs3 = Reference(feuille, min_row=1+offset3, min_col=1+colonne, max_col=len(recap[0])+colonne-1, max_row =1+offset3 ) graphique3.y_axis.crosses = "max" graphique3.add_data(valeurs3, titles_from_data=True, from_rows=True) graphique3.set_categories(sites) graphique3.y_axis.title = 'Emissions Carbones globales (tCO2e)' # graphique3.layout=Layout(manualLayout=ManualLayout(x=0.1, y=0.050,h=0.80, w=0.90,)) graphique3.legend.layout = Layout(manualLayout=ManualLayout(yMode='edge',xMode='edge',x=0, y=0.9,h=0.1, w=0.5)) feuille.add_chart(graphique3, "B"+str(offset3+7)) tarte = PieChart() labels = Reference(feuille, min_row = offset3, min_col = 2+colonne, max_col = len(recap[0])+colonne-1) for i in [0]: data = Reference(feuille, min_row=offset3+1+i, min_col=1+colonne, max_col=len(recap[0])+colonne-1) tarte.add_data(data, titles_from_data=True, from_rows=True) tarte.set_categories(labels) tarte.dataLabels = DataLabelList() tarte.dataLabels.position ='bestFit' tarte.dataLabels.showCat = True tarte.dataLabels.showPercent = True tarte.dataLabels.separator = ":" tarte.height = 8.15;tarte.width = 9 tarte.title = "Part des postes dans les émissions totales" set_chart_title_size(tarte, size=1200) feuille.add_chart(tarte, "M"+str(offset3+16)) return ligne
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')
def __init__(self, filename, sheet_name=[], chart_title=[], xaxis_title=[], yaxis_title=[], stack=[], chart_position="F10", whiten_all_area=True, font_name='Calibri', axis_title_font_size=10, output_filename=None): wb = load_workbook(filename) counts = len(sheet_name) wsDict = {} for count in range(counts): wsDict['ws' + str(count)] = wb[sheet_name[count]] chart = BarChart() chart.style = 10 chart.type = "col" if stack[count] == True: chart.grouping = "stacked" chart.overlap = 100 chart.title = chart_title[count] chart.y_axis.title = xaxis_title[count] chart.x_axis.title = yaxis_title[count] data = Reference(wsDict['ws' + str(count)], min_col=wsDict['ws' + str(count)].min_column + 1, min_row=wsDict['ws' + str(count)].min_row, max_row=wsDict['ws' + str(count)].max_row, max_col=wsDict['ws' + str(count)].max_column) cats = Reference(wsDict['ws' + str(count)], min_col=wsDict['ws' + str(count)].min_column, min_row=wsDict['ws' + str(count)].min_row + 1, max_row=wsDict['ws' + str(count)].max_row, max_col=wsDict['ws' + str(count)].min_column) chart.add_data(data, titles_from_data=True) chart.set_categories(cats) ## set axis title property?? font_test = draw_Font(typeface=font_name) cp = CharacterProperties(latin=font_test, sz=axis_title_font_size * 100) chart.x_axis.txPr = RichText(p=[ Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp) ]) chart.y_axis.txPr = RichText(p=[ Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp) ]) xPara = [ Paragraph(pPr=ParagraphProperties(defRPr=cp), r=RegularTextRun(t=s)) for s in xaxis_title[count].split("\n") ] yPara = [ Paragraph(pPr=ParagraphProperties(defRPr=cp), r=RegularTextRun(t=s)) for s in yaxis_title[count].split("\n") ] chart.x_axis.title.tx.rich.paragraphs = xPara chart.y_axis.title.tx.rich.paragraphs = yPara ## hide legend if not stack[count]: chart.legend = None wsDict['ws' + str(count)].add_chart(chart, chart_position) ## whiten data if whiten_all_area: table = wsDict['ws' + str(count)]["A1":"AZ200"] color = 'FFFFFFFF' font = Font(bold=False, italic=False, vertAlign=None, underline='none', strike=False, color=color) fill = PatternFill(start_color=color, end_color=color, fill_type="solid") for tr in table: for td in tr: td.font = font td.border = None td.fill = fill ### data label chart.dataLabels = DataLabelList() chart.dataLabels.showVal = True if output_filename is None: wb.save(filename=filename) else: wb.save(filename=output_filename) # # barchart_creater(filename = 'temp0.xlsx',sheet_name=["Issue Count by Due Date","Issue Count By Owner","MTM Issue Comparison"], # chart_title=["Number of Issues By Due Date","Number of outstanding Issues by Owner","Number of Issues comparison month to month by Source"], # xaxis_title = ["Number of Issues",'Issue Owner','Month'], # yaxis_title=['Number of Days till Due','Number of Issues','Number Of Issues'],stack = [False,False,True])