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 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 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 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 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 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 make_chart_jesd(result_file="MainTestResults/MainResult.xlsx", summary_file="JesdLogAndResults/jesd.json"): try: wb = load_workbook(os.path.join(current_path, result_file)) except: wb = Workbook() if "JESD Summary" in wb.sheetnames: del wb["JESD Summary"] wb.create_sheet("JESD Summary") ws = wb["JESD Summary"] _ = ws.cell(row=1, column=1, value='JESD Summary') _.font = Font(bold=True) ws.cell(row=2, column=1, value="Cycle times") ws.cell(row=3, column=1, value="Pass times") ws.cell(row=4, column=1, value="Fail times") ws.cell(row=5, column=1, value="Read counts") with open(os.path.join(current_path, summary_file), 'r') as f: res = json.load(f) ws.cell(row=2, column=2, value=res['summary']["Cycle times"]) ws.cell(row=3, column=2, value=res['summary']["Pass times"]) ws.cell(row=4, column=2, value=res['summary']["Fail times"]) ws.cell(row=5, column=2, value=res['summary']["read counts"]) row = 7 pie_row_start = 7 bar_row_start = 9 ips = [key for key in res.keys() if key != 'summary'] for ip in ips: # wrong_value_file = f"JesdLogAndResults/wrong_value_record_{ip.split('.')[-1]}.json" # with open(os.path.join(current_path, wrong_value_file), 'r') as f: # res = json.load(f) data_to_add = [["made ip", ip]] for key in res[ip].keys(): reg = re.search(r'/sbin/devmem .*$', key) if reg: data_to_add.append([reg.group().split()[-1], res[ip][key]]) else: data_to_add.append([key, res[ip][key]]) bar_rows = len(data_to_add) - 3 pie_row_end = pie_row_start + 1 bar_row_end = bar_row_start + bar_rows - 1 for line in data_to_add: ws.append(line) 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)) bar = BarChart() bar.type = "col" bar.style = 10 bar.title = ip + " failed register" bar.y_axis.title = 'fail times' bar.x_axis.title = 'fail case' data = Reference(ws, min_col=2, min_row=bar_row_start - 1, max_row=bar_row_end) cats = Reference(ws, min_col=1, min_row=bar_row_start, max_row=bar_row_end) bar.add_data(data, titles_from_data=True) bar.set_categories(cats) bar.shape = 4 ws.add_chart(bar, "N" + str(row)) row += 18 print(len(data)) bar_row_start += len(data_to_add) pie_row_start += len(data_to_add) wb.save(os.path.join(current_path, result_file))
def transcribe_client_data_to_workbooks(client): report_path = r'C:\Program Files\Notepad++\reports' + '\\' + string.replace( client, '/', '-') + '_report.csv' wb = openpyxl.load_workbook( 'C:\Program Files\Notepad++\Bucket-Asset Allocation Model.xlsm', read_only=False, keep_vba=True) wb.active = 5 ws = wb.active with open(report_path, 'rU') as f: reader = csv.reader(f) for row_index, row in enumerate(reader): for column_index, cell in enumerate(row): column_letter = get_column_letter((column_index + 1)) if column_letter == 'A': if row_index > 0: ws[column_letter + str(row_index + 1)] = long( string.replace(string.replace(cell, 'Z', '9'), 'X', '0')) else: ws[column_letter + str(row_index + 1)] = cell #Proposed allocation pie chart creation #Close the file f.close() #Proposed Allocation pie chart creation wb.active = 1 ws = wb.active pie1 = PieChart() labels1 = Reference(ws, min_col=14, min_row=19, max_row=21) data1 = Reference(ws, min_col=15, min_row=18, max_row=21) pie1.add_data(data1, titles_from_data=True) pie1.set_categories(labels1) pie1.title = 'Proposed Allocation' pie1.height = 14 pie1.width = 18 ws.add_chart(pie1, "F20") pie1.dataLabels = DataLabelList() pie1.dataLabels.showPercent = True #Current allocation pie chart creation pie2 = PieChart() labels2 = Reference(ws, min_col=14, min_row=29, max_row=31) data2 = Reference(ws, min_col=15, min_row=28, max_row=31) pie2.add_data(data2, titles_from_data=True) pie2.set_categories(labels2) pie2.title = 'Current Allocation' pie2.height = 14 pie2.width = 18 ws.add_chart(pie2, 'B20') pie2.dataLabels = DataLabelList() pie2.dataLabels.showPercent = True #Bar chart creation #Change to allocations worksheet wb.active = 3 ws = wb.active #Short term bucket bar graph creation chart1 = BarChart() chart1.type = "col" chart1.style = 12 chart1.title = "Bucket I \n Short - Term" data = Reference(ws, min_col=14, min_row=61, max_row=62, max_col=15) cats = Reference(ws, min_col=13, min_row=62) chart1.add_data(data, titles_from_data=True) chart1.set_categories(cats) chart1.shape = 4 ws.add_chart(chart1, "B25") chart1.dataLabels = DataLabelList() chart1.dataLabels.showVal = True #Intermediate term bucket bar graph creation chart2 = BarChart() chart2.type = "col" chart2.style = 10 chart2.title = "Bucket II \n Intermediate - Term" data2 = Reference(ws, min_col=14, min_row=63, max_row=64, max_col=15) cats2 = Reference(ws, min_col=13, min_row=64) chart2.add_data(data2, titles_from_data=True) chart2.set_categories(cats2) chart2.shape = 4 ws.add_chart(chart2, "I25") chart2.dataLabels = DataLabelList() chart2.dataLabels.showVal = True #Long term bucket bar graph creation chart3 = BarChart() chart3.type = "col" chart3.style = 13 chart3.title = "Bucket III \n Long - Term" data3 = Reference(ws, min_col=14, min_row=65, max_row=66, max_col=15) cats3 = Reference(ws, min_col=13, min_row=66) chart3.add_data(data3, titles_from_data=True) chart3.set_categories(cats3) chart3.shape = 4 ws.add_chart(chart3, "P25") chart3.dataLabels = DataLabelList() chart3.dataLabels.showVal = True #0 portfolio bar graph creation chart4 = BarChart() chart4.type = "col" chart4.style = 10 chart4.height = 10 chart4.width = 20 chart4.title = "0 Portfolio" data4 = Reference(ws, min_col=14, min_row=57, max_row=58, max_col=15) cats4 = Reference(ws, min_col=14, min_row=57, max_col=15) chart4.add_data(data4, titles_from_data=True) chart4.set_categories(cats4) chart4.shape = 4 ws.add_chart(chart4, "B3") chart4.dataLabels = DataLabelList() chart4.dataLabels.showVal = True #Allocation Comparison Bar graph creation chart5 = BarChart() chart5.type = "col" chart5.style = 10 chart5.title = "Allocation Comparison" chart5.height = 10 chart5.width = 20 data5 = Reference(ws, min_col=10, min_row=62, max_row=64, max_col=12) cats5 = Reference(ws, min_col=9, min_row=63, max_row=64) chart5.add_data(data5, titles_from_data=True) chart5.set_categories(cats5) chart5.shape = 4 ws.add_chart(chart5, "M3") chart5.dataLabels = DataLabelList() chart5.dataLabels.showVal = True print(len(set(cdict.values()))) #Attempt to save, move on if the file is open for reading. try: wb.save('C:\\Program Files\\Notepad++\\workbooks\\' + string.replace(client, '/', '-') + '_workbook.xlsm') print(string.replace(client, '/', '-') + ' workbook completed') except IOError: print("Workbook already open, cannot overwrite, moving on.")
Assignment 08 Donor Code Adapted from https://openpyxl.readthedocs.io/en/stable/charts/pie.html @author: nicomp ''' from openpyxl import load_workbook from openpyxl.chart import (PieChart, ProjectedPieChart, Reference) from openpyxl.chart.series import DataPoint from openpyxl.chart.label import DataLabelList wb = load_workbook(filename='Top5TransactionsByLoyaltyNumber.xlsx') ws = wb['Sheet1'] pie = PieChart() labels = Reference(ws, min_col=4, min_row=2, max_row=6) data = Reference(ws, min_col=2, min_row=2, max_row=6) pie.add_data(data, titles_from_data=False) pie.set_categories(labels) pie.title = "Top 5 Total Transactions by Loyalty Number" pie.dataLabels = DataLabelList() pie.dataLabels.showVal = True # Cut the first slice out of the pie pieSlice = DataPoint(idx=0, explosion=20) pie.series[0].data_points = [pieSlice] ws.add_chart(pie, "A7") wb.save('Top5TransactionsByLoyaltyNumberWithPieChart.xlsx' ) # .xlsx file cannot be open when we do this print("Done")
def writeresult(result, pathsave): result = sortresult(result) statuscount = { "ok": 0, "wildcard": 0, "expire": 0, "expiresoon": 0, "expiresoon": 0, "validitytoolong": 0, "notmatch": 0, "timeout": 0, "errresolution": 0, "error": 0 } wb = Workbook() wssommaire = wb.active wssommaire.title = u'Sommaire' wssommaire.column_dimensions['A'].width = len("VALIDITE TROP LONGUE") * 1.3 wssommaire["A1"] = u"EXPIRÉ" wssommaire["A1"].fill = PatternFill(fill_type="solid", start_color=getstatuscolor("expire"), end_color=getstatuscolor("expire")) wssommaire["A1"].font = Font(color='FFFFFF') wssommaire["A2"] = u"EXPIRE BIENTÔT" wssommaire["A2"].fill = PatternFill( fill_type="solid", start_color=getstatuscolor("expiresoon"), end_color=getstatuscolor("expiresoon")) wssommaire["A2"].font = Font(color='FFFFFF') wssommaire["A3"] = "VALIDITE TROP LONGUE" wssommaire["A3"].fill = PatternFill( fill_type="solid", start_color=getstatuscolor("validitytoolong"), end_color=getstatuscolor("validitytoolong")) wssommaire["A3"].font = Font(color='000000') wssommaire["A4"] = u"NOM D'HÔTE INVALIDE" wssommaire["A4"].fill = PatternFill(fill_type="solid", start_color=getstatuscolor("notmatch"), end_color=getstatuscolor("notmatch")) wssommaire["A4"].font = Font(color='000000') wssommaire["A5"] = "WILDCARD" wssommaire["A5"].fill = PatternFill(fill_type="solid", start_color=getstatuscolor("wildcard"), end_color=getstatuscolor("wildcard")) wssommaire["A5"].font = Font(color='FFFFFF') wssommaire["A6"] = "VALIDES" wssommaire["A6"].fill = PatternFill(fill_type="solid", start_color=getstatuscolor("ok"), end_color=getstatuscolor("ok")) wssommaire["A6"].font = Font(color='FFFFFF') ws = wb.create_sheet(u'Détails') listwidthcolumn = [] ws['A1'] = "Status certificat" ws['A1'].alignment = Alignment(horizontal='center') ws['A1'].font = Font(bold=True) ws.column_dimensions['A'].width = len("Status certificat") * 1.3 ws['B1'] = "Domaines" ws['B1'].alignment = Alignment(horizontal='center') ws['B1'].font = Font(bold=True) ws['C1'] = "Port" ws.column_dimensions['C'].width = len("Port") * 1.3 ws['C1'].alignment = Alignment(horizontal='center') ws['C1'].font = Font(bold=True) ws['D1'] = "Date de délivrance" ws.column_dimensions['D'].width = len("Date de délivrance") * 1.3 ws['D1'].alignment = Alignment(horizontal='center') ws['D1'].font = Font(bold=True) ws['E1'] = "Date d'expiration" ws.column_dimensions['E'].width = len("Date d'expiration") * 1.3 ws['E1'].alignment = Alignment(horizontal='center') ws['E1'].font = Font(bold=True) ws['F1'] = "Jours restants" ws.column_dimensions['F'].width = len("Jours restants") * 1.3 ws['F1'].alignment = Alignment(horizontal='center') ws['F1'].font = Font(bold=True) ws['G1'] = "Validité (nombre de jours)" ws.column_dimensions['G'].width = len("Validité (nombre de jours)") * 1.3 ws['G1'].alignment = Alignment(horizontal='center') ws['G1'].font = Font(bold=True) ws['H1'] = "Vérifié par" ws.column_dimensions['H'].width = len("Emis par") * 1.3 ws['H1'].alignment = Alignment(horizontal='center') ws['H1'].font = Font(bold=True) ws['I1'] = "Emis pour" ws.column_dimensions['I'].width = len("Emis pour") * 1.3 ws['I1'].alignment = Alignment(horizontal='center') ws['I1'].font = Font(bold=True) ws['J1'] = "Numéro de série" ws.column_dimensions['J'].width = len("Numéro de série") * 1.3 ws['J1'].alignment = Alignment(horizontal='center') ws['J1'].font = Font(bold=True) currentline = 2 wsnotmatch = wb.create_sheet(u'Noms d\'hôte invalides') wsnotmatch['A1'] = "Domaines" wsnotmatch['B1'] = "Port" wserrresolution = wb.create_sheet(u'Erreur de résolution') wserrresolution['A1'] = "Domaines" wserrresolution['B1'] = "Port" wstimeout = wb.create_sheet(u'Timeout') wstimeout['A1'] = "Domaines" wstimeout['B1'] = "Port" wsinvalidecert = wb.create_sheet(u'Certificats invalides') wsinvalidecert['A1'] = "Domaines" wsinvalidecert['B1'] = "Port" wserror = wb.create_sheet(u'Autres erreurs') wserror['A1'] = "Domaines" wserror['B1'] = "Port" countersheet = { 'principal': 1, 'notmatch': 1, 'errresolution': 1, 'timeout': 1, 'error': 1 } currentlinenotmatch = 1 currentsheetinfo = {'object': None, 'line': 0} for current in result: if current["status"] == "ok": statuscount["ok"] += 1 countersheet["principal"] += 1 ws['A' + str(countersheet["principal"])] = "VALIDE" ws['A' + str(countersheet["principal"])].fill = PatternFill( fill_type="solid", start_color=getstatuscolor("ok"), end_color=getstatuscolor("ok")) currentsheetinfo['object'] = ws currentsheetinfo['line'] = countersheet["principal"] elif current["status"] == "wildcard": statuscount["wildcard"] += 1 countersheet["principal"] += 1 ws['A' + str(countersheet["principal"])] = "WILDCARD" ws['A' + str(countersheet["principal"])].fill = PatternFill( fill_type="solid", start_color=getstatuscolor("wildcard"), end_color=getstatuscolor("wildcard")) currentsheetinfo['object'] = ws currentsheetinfo['line'] = countersheet["principal"] elif current["status"] == "expire": statuscount["expire"] += 1 countersheet["principal"] += 1 ws['A' + str(countersheet["principal"])] = "EXPIRÉ" ws['A' + str(countersheet["principal"])].fill = PatternFill( fill_type="solid", start_color=getstatuscolor("expire"), end_color=getstatuscolor("expire")) currentsheetinfo['object'] = ws currentsheetinfo['line'] = countersheet["principal"] elif current["status"] == "expiresoon": statuscount["expiresoon"] += 1 countersheet["principal"] += 1 ws['A' + str(countersheet["principal"])] = "EXPIRE BIENTOT" ws['A' + str(countersheet["principal"])].fill = PatternFill( fill_type="solid", start_color=getstatuscolor("expiresoon"), end_color=getstatuscolor("expiresoon")) currentsheetinfo['object'] = ws currentsheetinfo['line'] = countersheet["principal"] elif current["status"] == "validitytoolong": statuscount["validitytoolong"] += 1 countersheet["principal"] += 1 if ws.column_dimensions['A'].width < len( "VALIDITE TROP LONGUE") * 1.3: ws.column_dimensions['A'].width = len( "VALIDITE TROP LONGUE") * 1.3 ws['A' + str(countersheet["principal"])] = "VALIDITÉ TROP LONGUE" ws['A' + str(countersheet["principal"])].fill = PatternFill( fill_type="solid", start_color=getstatuscolor("validitytoolong"), end_color=getstatuscolor("validitytoolong")) currentsheetinfo['object'] = ws currentsheetinfo['line'] = countersheet["principal"] elif current["status"] == "notmatch": statuscount["notmatch"] += 1 countersheet["notmatch"] += 1 if wsnotmatch.column_dimensions['A'].width < len( "HOSTNAME INVALIDE") * 1.3: wsnotmatch.column_dimensions['A'].width = len( "HOSTNAME INVALIDE") * 1.3 currentsheetinfo['object'] = wsnotmatch currentsheetinfo['line'] = countersheet["notmatch"] else: if current["status"] == "errresolution": countersheet["errresolution"] += 1 currentsheetinfo['object'] = wserrresolution currentsheetinfo['line'] = countersheet["errresolution"] statuscount["errresolution"] += 1 elif current["status"] == "timeout": countersheet["timeout"] += 1 statuscount["timeout"] += 1 currentsheetinfo['line'] = countersheet["timeout"] currentsheetinfo['object'] = wstimeout else: countersheet["error"] += 1 statuscount["error"] += 1 currentsheetinfo['line'] = countersheet["error"] currentsheetinfo['object'] = wserror if currentsheetinfo['object'] == ws: ws['A' + str(countersheet["principal"])] = getstatustext( current["status"]) ws['A' + str(countersheet["principal"])].fill = PatternFill( fill_type="solid", start_color=getstatuscolor(current["status"]), end_color=getstatuscolor(current["status"])) ws['A' + str(countersheet["principal"])].font = Font( color=getstatustextcolor(current["status"])) ws['A' + str(countersheet["principal"])].alignment = Alignment( horizontal='center') ws['B' + str(countersheet["principal"])] = current["domain"] if ws.column_dimensions['B'].width < len( current["domain"] ) * 1.3 and ws.column_dimensions['B'].width != 255: if len(current["domain"]) * 1.3 >= 255: ws.column_dimensions['B'].width = 255 else: ws.column_dimensions['B'].width = len( current["domain"]) * 1.3 if current["status"] == "errresolution": ws['C' + str(countersheet["principal"])] = '*' else: ws['C' + str(countersheet["principal"])] = current["port"] if ws.column_dimensions['C'].width < len(current["port"]) * 1.3: ws.column_dimensions['C'].width = len(current["port"]) * 1.3 if (str(current["notBefore"]) != "error"): ws['D' + str(countersheet["principal"])] = str( current["notBefore"].year) + '-' + "{:02d}".format( current["notBefore"].month) + '-' + "{:02d}".format( current["notBefore"].day) else: ws['D' + str(countersheet["principal"])] = "ERROR" if (str(current["notAfter"]) != "error"): ws['E' + str(countersheet["principal"])] = str( current["notAfter"].year) + '-' + "{:02d}".format( current["notAfter"].month) + '-' + "{:02d}".format( current["notAfter"].day) else: ws['E' + str(countersheet["principal"])] = "ERROR" if (str(current["serialNumber"]) != "error"): if ws.column_dimensions['J'].width < len( str(current["serialNumber"])) * 1.3: ws.column_dimensions['J'].width = len( str(current["serialNumber"])) * 1.3 ws['J' + str(countersheet["principal"])] = str( current["serialNumber"]) else: ws['J' + str(countersheet["principal"])] = "ERROR" ws['F' + str(countersheet["principal"])] = str( current["deltaToday"]) ws['G' + str(countersheet["principal"])] = str( current["periodevalidity"]) ws['H' + str(countersheet["principal"])] = current["deliver"] if ws.column_dimensions['H'].width < len(current["deliver"]) * 1.3: ws.column_dimensions['H'].width = len(current["deliver"]) * 1.3 ws['I' + str(countersheet["principal"])] = current["deliverfor"] if ws.column_dimensions['I'].width < len( current["deliverfor"]) * 1.3: ws.column_dimensions['I'].width = len( current["deliverfor"]) * 1.3 else: currentsheetinfo['object'][ 'A' + str(currentsheetinfo['line'])] = current["domain"] currentsheetinfo['object'][ 'B' + str(currentsheetinfo['line'])] = current["port"] wssommaire["B1"] = statuscount["expire"] wssommaire["B2"] = statuscount["expiresoon"] wssommaire["B3"] = statuscount["validitytoolong"] wssommaire["B4"] = statuscount["notmatch"] wssommaire["B5"] = statuscount["wildcard"] wssommaire["B6"] = statuscount["ok"] pie = PieChart() labels = Reference(wssommaire, min_col=1, min_row=1, max_col=1, max_row=6) data = Reference(wssommaire, min_col=2, min_row=1, max_row=6, max_col=2) series = Series(data, title="Vue sommaire des certificats SSL") pie.append(series) pie.height = 11 pie.width = 14 pie.set_categories(labels) pie.dataLabels = label.DataLabelList() pie.dataLabels.showPercent = True pie.title = "Vue sommaire des certificats SSL" wssommaire.add_chart(pie, "D4") wb.save(pathsave + 'StatusCertificates.xlsx')
def auto_chart(in_path, out_path): """ 自动生成报告 汇总某个文件夹内所有同类Excel文件,输出报表 以时间维度,粒度为月,汇总统计单店经营和全国经营 输出报表: 1. 各门店经营:销售额、成本、毛利 2. 全国经营:销售额,成本,毛利 3. 门店总排行榜:销售额、毛利润 TODO: 可以增加产品维度的统计作为练习。 Version: 0.0.1 Author : yichu.cheng """ wb = Workbook() ws_all = wb.active ws_all.title = '全国汇总' # 一、先为每个门店生成一张单店经营表 # 月份, 总营收, 总折扣, 总成本, 总毛利 shop_list = list(in_path.glob('*.xlsx')) shop_names = [shop.stem for shop in shop_list] for shop in shop_list: # shop = shop_list[0] print(f'正在处理 {shop.stem} 表......') wb_shop = load_workbook(shop) # 打开城市门店数据 ws_shop = wb_shop.active shop_name = shop.stem ws = wb.create_sheet(shop_name) # 生成对应城市门店表 # 表头 header_list = ['月份', '销售额', '折扣额', '成本', '毛利润'] ws.append(header_list) # 从1到12月生成数据汇总表 for m in range(1, 13): start_date = datetime.datetime(2020, m, 1) _, days_in_month = calendar.monthrange( start_date.year, start_date.month) end_date = start_date + datetime.timedelta(days=days_in_month) # print(m, start_date, end_date) # 按月提取数据 month_data_list = [ ([ws_shop.cell(row=i, column=j).value for j in range(6, 10)]) for i in range(2, ws_shop.max_row + 1) if start_date <= ws_shop.cell(row=i, column=1).value < end_date ] # 累加汇总 # zip(*list)可以把list中的tuple重新按序打包 month_data_T = list(zip(*month_data_list)) month_sum = [ f'{m}月', sum(month_data_T[1]), # sum of sale # sum of sale*(1-discount) 折扣总额 sum([a*(1-b) for a, b in zip(month_data_T[1], month_data_T[0])]), sum(month_data_T[2]), # sum of cost sum(month_data_T[3]) # sum of profit ] ws.append(month_sum) year_sum = [ '全年总计', '=SUM(B2:B13)', '=SUM(C2:C13)', '=SUM(D2:D13)', '=SUM(E2:E13)' ] ws.append(year_sum) # 画个月度统计图表 # 列状图 bar_chart = BarChart() bar_chart.type = 'col' # 列状图 bar_chart.style = 10 bar_chart.title = '门店经营统计图' bar_chart.y_axis.title = '金额' bar_chart.x_axis.title = '月份' d_ref = Reference(ws, min_col=2, min_row=1, max_row=13, max_col=5) cat = Reference(ws, min_col=1, min_row=2, max_row=13) bar_chart.add_data(d_ref, titles_from_data=True) bar_chart.set_categories(cat) ws.add_chart(bar_chart, 'A17') # 开始处理全国总表 print('开始处理全国总表......') ws_all.append(header_list) for m in range(1, 13): # (销售额, 折扣额, 成本, 毛利润) month_all_shop = [ ([wb[shop].cell(row=m+1, column=j).value for j in range(2, 6)]) for shop in shop_names ] month_all_shop_T = list(zip(*month_all_shop)) month_all_sum = [ f'{m}月', sum(month_all_shop_T[0]), # sale sum(month_all_shop_T[1]), # discount sum(month_all_shop_T[2]), # cost sum(month_all_shop_T[3]), # profit ] ws_all.append(month_all_sum) ws_all.append(year_sum) # 生成全国月度统计报表 bar_chart = BarChart() bar_chart.type = 'col' # 列状图 bar_chart.style = 10 bar_chart.title = '全国经营统计图' bar_chart.y_axis.title = '金额' bar_chart.x_axis.title = '月份' d_ref = Reference(ws_all, min_col=2, min_row=1, max_row=13, max_col=5) cat = Reference(ws_all, min_col=1, min_row=2, max_row=13) bar_chart.add_data(d_ref, titles_from_data=True) bar_chart.set_categories(cat) ws_all.add_chart(bar_chart, 'J1') # 生成城市门店排名数据 header_list = ['城市门店', '年度销售额', '年度毛利润'] for j in range(3): ws_all.cell(row=19, column=j+1, value=header_list[j]) for i, shop in enumerate(shop_names, start=2): ws_shop = wb[shop] year_sale = sum([ws_shop.cell(row=r, column=2).value for r in range(2, 14)]) year_profit = sum([ws_shop.cell(row=r, column=5).value for r in range(2, 14)]) ws_all.append([ shop, year_sale, year_profit ]) # 排序 ws_all.auto_filter.ref = 'A19:C27' ws_all.auto_filter.add_sort_condition('B20:B27') # 生成排名图表 bar_chart = BarChart() bar_chart.type = 'col' bar_chart.style = 10 bar_chart.title = '城市门店年度经营' bar_chart.y_axis.title = '年度销售额' bar_chart.x_axis.title = '城市门店' d_ref = Reference(ws_all, min_col=2, min_row=19, max_row=27, max_col=3) cat = Reference(ws_all, min_col=1, min_row=20, max_row=27) bar_chart.add_data(d_ref, titles_from_data=True) bar_chart.set_categories(cat) ws_all.add_chart(bar_chart, 'J19') # 门店销售额比例饼图 pie_chart = PieChart() pie_chart.dataLabels = DataLabelList() pie_chart.dataLabels.showPercent = True pie_chart.style = 10 pie_chart.title = '门店销量占比' d_ref = Reference(ws_all, min_col=2, min_row=19, max_row=27) cat = Reference(ws_all, min_col=1, min_row=20, max_row=27) pie_chart.add_data(d_ref, titles_from_data=True) pie_chart.set_categories(cat) ws_all.add_chart(pie_chart, 'S1') # 门店利润比例饼图 pie_chart = PieChart() pie_chart.dataLabels = DataLabelList() pie_chart.dataLabels.showPercent = True pie_chart.title = '门店利润占比' d_ref = Reference(ws_all, min_col=3, min_row=19, max_row=27) cat = Reference(ws_all, min_col=1, min_row=20, max_row=27) pie_chart.add_data(d_ref, titles_from_data=True) pie_chart.set_categories(cat) ws_all.add_chart(pie_chart, 'S19') wb.save(out_path)
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)
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