def pie(wb): ws = wb.create_sheet(4, "Pie") for i in range(1, 5): ws.append([i]) chart = PieChart() values = Reference(ws, (0, 0), (3, 0)) series = Series(values, labels=values) chart.append(series) ws.add_chart(chart)
def plot_pie_chart(): wb = xl.load_workbook('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx') # load xl workbook sheet = wb['dATA'] # get the worksheet for i in range(2, 10): cell = sheet['f'+str(i)] sheet['a'+str(i)] = cell.value values = Reference( sheet, min_row=2, max_row=10, min_col=1, max_col=1 ) series_object = xl.chart.Series(values, title="Yearly Revenue") chart = PieChart() chart.append(series_object) sheet.add_chart(chart, 'j2') wb.save('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx')
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')
#Create new worksheet object for the pie chart worksheet_object2 = workbook_object.create_sheet('Pie Chart') for row in data: worksheet_object2.append(row) #Create reference object reference_object = Reference(worksheet_object2, min_col=2, min_row=2, max_col=2, max_row=5) #Create series object labels = Reference(worksheet_object2, min_col=1, min_row=2, max_col=1, max_row=5) series_object = Series(reference_object) #Create chart object chart_object = PieChart() #Append series object to chart object chart_object.append(series_object) chart_object.set_categories(labels) chart_object.title = worksheet_object2['A1'].value #Append chart object to worksheet worksheet_object2.add_chart(chart_object) workbook_object.save('crashData.xlsx')