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')
