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
