def contoh_gauge_chart(self, ws):
data = [
["Donut", "Pie"],
[25, 75],
[50, 1],
[25, 124],
[100],
]
# based on http://www.excel-easy.com/examples/gauge-chart.html
for row in data:
ws.append(row)
# First chart is a doughnut chart
c1 = DoughnutChart(firstSliceAng=270, holeSize=50)
c1.title = "Code coverage"
c1.legend = None
ref = Reference(ws, min_col=1, min_row=2, max_row=5)
s1 = Series(ref, title_from_data=False)
slices = [DataPoint(idx=i) for i in range(4)]
slices[0].graphicalProperties.solidFill = "FF3300" # red
slices[1].graphicalProperties.solidFill = "FCF305" # yellow
slices[2].graphicalProperties.solidFill = "1FB714" # green
slices[3].graphicalProperties.noFill = True # invisible
s1.data_points = slices
c1.series = [s1]
# Second chart is a pie chart
c2 = PieChart(firstSliceAng=270)
c2.legend = None
ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4)
s2 = Series(ref, title_from_data=False)
slices = [DataPoint(idx=i) for i in range(3)]
slices[0].graphicalProperties.noFill = True # invisible
slices[1].graphicalProperties.solidFill = "000000" # black needle
slices[2].graphicalProperties.noFill = True # invisible
s2.data_points = slices
c2.series = [s2]
c1 += c2 # combine charts
ws.add_chart(c1, "D1")
# Second chart is a pie chart
c2 = PieChart(firstSliceAng=270)
# c2.legend = None
ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4)
s2 = Series(ref, title_from_data=False)
# slices = [DataPoint(idx=i) for i in range(3)]
# slices[0].graphicalProperties.noFill = True # invisible
# slices[1].graphicalProperties.solidFill = "000000" # black needle
# slices[2].graphicalProperties.noFill = True # invisible
# s2.data_points = slices
c2.series = [s2]
ws.add_chart(c2, "D20")
def create_data_points(): #←データ要素の作成と書式設定
book = DataPoint(0)
book.graphicalProperties.solidFill = '9370DB' #←MediumPurple
stationery = DataPoint(1)
stationery.graphicalProperties.solidFill = 'FFFFE0' #←LightYellow
misc = DataPoint(2)
misc.graphicalProperties.solidFill = 'D2691E' #←Chocolate
misc.explosion = 10 #←「要素の切り出し」を10%に設定
return [book, stationery, misc]
def _draw_pie_charts(self):
"""
画两个饼图
:return: None
"""
ws = self._wb['analysis']
# 设置单元格值，饼图引用
ws['G3'] = '失败'
ws['G4'] = '通过'
ws['H3'] = self._api_failure
ws['H4'] = self._api_num - self._api_failure
ws['N3'] = '失败'
ws['N4'] = '通过'
ws['O3'] = self._case_failure
ws['O4'] = self._case_num - self._case_failure
# 画接口饼图
pie = PieChart()
labels = Reference(ws, min_col=7, min_row=3, max_row=4)
data = Reference(ws, min_col=8, min_row=2, max_row=4)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "接口执行情况"
slice_ = DataPoint(idx=0, explosion=10)
pie.series[0].data_points = [slice_]
ws.add_chart(pie, "F1")
pie.height = 9.5
pie.width = 13
self._log.info('已生成接口执行情况饼图.')
# 画用例饼图
pie2 = PieChart()
labels2 = Reference(ws, min_col=14, min_row=3, max_row=4)
data2 = Reference(ws, min_col=15, min_row=2, max_row=4)
pie2.add_data(data2, titles_from_data=True)
pie2.set_categories(labels2)
pie2.title = "用例执行情况"
slice2_ = DataPoint(idx=0, explosion=10)
pie2.series[0].data_points = [slice2_]
ws.add_chart(pie2, "M1")
pie2.height = 9.5
pie2.width = 13
self._log.info('已生成用例执行情况饼图.')
def make_pie_chart(self):
sheetnames = self.__wb.get_sheet_names()
# ws = self.__wb.get_sheet_by_name(sheetnames[0])
ws_new = self.__wb.create_sheet("pie chart")
# 最后一行
max_r = self.__ws.max_row
pie = PieChart()
labels = Reference(self.__ws, min_col=1, min_row=max_r - 2, max_row=max_r - 1)
data = Reference(self.__ws, min_col=2, min_row=max_r - 3, max_row=max_r - 1)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Test Result Pie Chart"
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
ws_new.add_chart(pie, "A1")
self.__wb.save(self.__filename)
def pie_types(
self,
ws: Worksheet,
types_counters: dict = None,
cell_start_table: str = "A1",
cell_start_chart: str = "D1",
):
"""Calculates metadata types repartition and add a Pie chart to the wanted sheet of Workbook.
:param Worksheet ws: sheet of a Workbook to write analisis
:param dict types_counters: dictionary of types/count. If not specified, the class attribute will be used instaed
:param str cell_start_table: cell of the sheet where to start writing table
:param str cell_start_chart: cell of the sheet where to start writing the chart
"""
if types_counters is None:
types_counters = self.md_types_repartition
# get starting cells
min_cell_start_table = ws[cell_start_table]
# write headers
ws.cell(
row=min_cell_start_table.row,
column=min_cell_start_table.column,
value=self.tr.get("type"),
)
ws.cell(
row=min_cell_start_table.row,
column=min_cell_start_table.column + 1,
value=self.tr.get("occurrences"),
)
# write data into worksheet
row = min_cell_start_table.row
for md_type, count in self.md_types_repartition.items():
row += 1
ws.cell(row=row,
column=min_cell_start_table.column,
value=self.tr.get(md_type))
ws.cell(row=row,
column=min_cell_start_table.column + 1,
value=count)
# Pie chart
pie = PieChart()
labels = Reference(
worksheet=ws,
min_col=min_cell_start_table.column,
min_row=min_cell_start_table.row + 1,
max_row=row,
)
data = Reference(
worksheet=ws,
min_col=min_cell_start_table.column + 1,
min_row=min_cell_start_table.row + 1,
max_row=row,
)
pie.add_data(data)
pie.set_categories(labels)
pie.title = self.tr.get("type") + "s"
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
ws.add_chart(pie, cell_start_chart)
def pie_formats(
self,
ws: Worksheet,
li_formats: list = None,
cell_start_table: str = "A20",
cell_start_chart: str = "D20",
):
"""Calculates metadata formats repartition and add a Pie chart to the wanted sheet of Workbook.
:param Worksheet ws: sheet of a Workbook to write analisis
:param list li_formats: list of all formats labels. If not specified, the class attribute will be used instaed
:param str cell_start_table: cell of the sheet where to start writing table
:param str cell_start_chart: cell of the sheet where to start writing the chart
"""
if li_formats is None:
li_formats = self.li_data_formats
# build the data for pie chart
data = Counter(li_formats)
# get starting cells
min_cell_start_table = ws[cell_start_table]
# write headers
ws.cell(
row=min_cell_start_table.row,
column=min_cell_start_table.column,
value=self.tr.get("format"),
)
ws.cell(
row=min_cell_start_table.row,
column=min_cell_start_table.column + 1,
value=self.tr.get("occurrences"),
)
# write data into worksheet
row = min_cell_start_table.row
for frmt, count in data.items():
row += 1
ws.cell(row=row,
column=min_cell_start_table.column,
value=frmt.title())
ws.cell(row=row,
column=min_cell_start_table.column + 1,
value=count)
# Pie chart
pie = PieChart()
labels = Reference(
worksheet=ws,
min_col=min_cell_start_table.column,
min_row=min_cell_start_table.row + 1,
max_row=row,
)
data = Reference(
worksheet=ws,
min_col=min_cell_start_table.column + 1,
min_row=min_cell_start_table.row + 1,
max_row=row,
)
pie.add_data(data)
pie.set_categories(labels)
pie.title = self.tr.get("format") + "s"
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
ws.add_chart(pie, cell_start_chart)
# We are setting a new tab for this information
ws = wb.create_sheet(title="Ministry Paid")
for row in data:
ws.append(row)
pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Volunteers vs. Paid"
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
ws.add_chart(pie, "D1")
### Line Chart
from datetime import date
from openpyxl import Workbook
from openpyxl.chart import (
LineChart,
Reference,
)
from openpyxl.chart.axis import DateAxis
def create_spreadsheet(base_channel_report):
# Setup Excel file
filename = "server_os_report.xlsx"
workbook = Workbook()
ws1 = workbook.create_sheet("Sheet_A")
ws1.title = "Overview Linux OS"
ws2 = workbook.create_sheet("Sheet_B")
ws2.title = "Data"
ws3 = workbook.create_sheet("Sheet_C")
ws3.title = "Approver Breakdown"
sheet = workbook["Data"]
for row in base_channel_report:
sheet.append(row)
darkyellow_background = PatternFill(bgColor=colors.DARKYELLOW)
yellow_background = PatternFill(bgColor=colors.YELLOW)
blue_background = PatternFill(bgColor=colors.BLUE)
green_background = PatternFill(bgColor=colors.GREEN)
diff_style7 = DifferentialStyle(fill=darkyellow_background)
rule7 = Rule(type="expression", dxf=diff_style7)
rule7.formula = ["$C1=7"]
sheet.conditional_formatting.add("A1:E600", rule7)
diff_style8 = DifferentialStyle(fill=blue_background)
rule8 = Rule(type="expression", dxf=diff_style8)
rule8.formula = ["$C1=7"]
sheet.conditional_formatting.add("A1:E600", rule8)
diff_style6 = DifferentialStyle(fill=yellow_background)
rule6 = Rule(type="expression", dxf=diff_style6)
rule6.formula = ["$C1=6"]
sheet.conditional_formatting.add("A1:E600", rule6)
diff_style5 = DifferentialStyle(fill=green_background)
rule5 = Rule(type="expression", dxf=diff_style5)
rule5.formula = ["$C1=5"]
sheet.conditional_formatting.add("A1:E600", rule5)
sheet = workbook["Overview Linux OS"]
data = [
['Centos5', '=COUNTIFS(Data!$C$2:$C$600,5, Data!$B$2:$B$600,"Centos")'],
['Centos6', '=COUNTIFS(Data!$C$2:$C$600,6, Data!$B$2:$B$600,"Centos")'],
['Centos7', '=COUNTIFS(Data!$C$2:$C$600,7, Data!$B$2:$B$600,"Centos")'],
['Centos8', '=COUNTIFS(Data!$C$2:$C$600,8, Data!$B$2:$B$600,"Centos")'],
['RedHat5', '=COUNTIFS(Data!$C$2:$C$600,5, Data!$B$2:$B$600,"Redhat")'],
['RedHat6', '=COUNTIFS(Data!$C$2:$C$600,6, Data!$B$2:$B$600,"Redhat")'],
['RedHat7', '=COUNTIFS(Data!$C$2:$C$600,7, Data!$B$2:$B$600,"Redhat")'],
['RedHat8', '=COUNTIFS(Data!$C$2:$C$600,8, Data!$B$2:$B$600,"Redhat")'],
['Unknown', '=COUNTIFS(Data!$C$2:$C$600,0)']
]
for row in data:
sheet.append(row)
pie = PieChart()
labels = Reference(sheet, min_col=1, min_row=2, max_row=9)
data = Reference(sheet, min_col=2, min_row=1, max_row=9)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "OS Breakdown"
pie.height = 20
pie.width = 40
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
sheet.add_chart(pie, "A1")
std=workbook.get_sheet_by_name('Sheet')
workbook.remove_sheet(std)
unique_cost_center = set(x for l in base_channel_report for x in l)
ws3 = workbook.create_sheet("Sheet_C")
ws3.title = "Cost Center Breakdown"
sheet = workbook["Cost Centre Breakdown"]
data =[]
for x in unique_cost_center:
countifs = "=COUNTIFS(Data!$H$2:$H$600,%s)" % x
data.append([x,countifs])
for row in data:
sheet.append(row)
pie = PieChart()
labels = Reference(sheet, min_col=1, min_row=2, max_row=len(data))
data = Reference(sheet, min_col=2, min_row=1, max_row=len(data))
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Cost Center Breakdown"
pie.height = 20
pie.width = 40
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
sheet.add_chart(pie, "A1")
# save file
workbook.save(filename)
def printTable(self, peoplesName, examsName, summaryName):
file_1 = Workbook()
file_2 = Workbook()
file_3 = Workbook()
peoples = file_1.active
exams = file_2.active
summary = file_3.active
imc_data = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
hyp_data = [0, 0, 0, 0, 0, 0]
peoples.append(
('ID', 'Nome', 'Sobrenome', 'RG', 'Sexo', 'Data de nascimento',
'Idade', 'Número de exames', 'Data de entrada no seguro'))
exams.append(('ID', 'Paciente ID', 'Auditor', 'Cargo do auditor',
'Peso (KG)', 'Altura (M)', 'IMC', 'Estado nutricional',
'Estado de Hipertensão', 'Hemoglobina',
'Albúmina sérica', 'Fósforo'))
for i, u in enumerate(self.patients):
peoples.append(
(i, u.first_name, u.last_name, u.register, u.genre,
u.date_birth, u.getAge(), u.exams, u.date_insurance))
for i, e in enumerate(self.exams):
imc = e.getIMCstr()
hyp = e.getHyp()
patient = self.searchPatient(e.patientRG)
imc_data[e.getIMCid()] += 1
hyp_data[e.getHypID()] += 1
exams.append((i, self.searchPatientID(e.patientRG), e.auditorName,
e.auditorStr, e.weight, e.height, e.getIMC(), imc,
hyp, e.hemoglobin, e.albumin, e.phosphor))
c = exams.cell(column=8, row=i + 2)
if imc is not 'Normal':
c.font = Font(bold=True, name='Arial', color='FFF00000')
c.fill = PatternFill(fill_type='solid',
start_color='ffcccc',
end_color='ffcccc')
else:
c.font = Font(bold=True, name='Arial', color='006600')
c.fill = PatternFill(fill_type='solid',
start_color='ccffcc',
end_color='ccffcc')
c = exams.cell(column=9, row=i + 2)
if 'Normal (ótimo)' in hyp:
c.font = Font(bold=True, name='Arial', color='006600')
c.fill = PatternFill(fill_type='solid',
start_color='ccffcc',
end_color='ccffcc')
elif 'Normal (em risco)' in hyp:
c.font = Font(name='Arial', color='ff7500')
c.fill = PatternFill(fill_type='solid',
start_color='fff3df',
end_color='fff3df')
elif 'Normal' in hyp:
c.font = Font(name='Arial', color='006600')
c.fill = PatternFill(fill_type='solid',
start_color='ccffcc',
end_color='ccffcc')
elif 'Hipertensão leve' in hyp:
c.font = Font(bold=True, name='Arial', color='FFF00000')
c.fill = PatternFill(fill_type='solid',
start_color='ffcccc',
end_color='ffcccc')
elif 'Hipertensão moderada' in hyp:
c.font = Font(bold=True, name='Arial', color='FFF00000')
c.fill = PatternFill(fill_type='solid',
start_color='ffcccc',
end_color='ffcccc')
elif 'Hipertensão grave' in hyp:
c.font = Font(bold=True, name='Arial', color='FFFFFFFF')
c.fill = PatternFill(fill_type='solid',
start_color='cc0000',
end_color='cc0000')
c = exams.cell(column=10, row=i + 2)
if patient.genre is 'M':
if e.hemoglobin < 14:
c.font = Font(bold=True, name='Arial', color='FFF00000')
c.fill = PatternFill(fill_type='solid',
start_color='ffcccc',
end_color='ffcccc')
elif 14 <= e.hemoglobin <= 18:
c.font = Font(bold=True, name='Arial', color='006600')
c.fill = PatternFill(fill_type='solid',
start_color='ccffcc',
end_color='ccffcc')
else:
c.font = Font(bold=True, name='Arial', color='FFFFFFFF')
c.fill = PatternFill(fill_type='solid',
start_color='cc0000',
end_color='cc0000')
else:
if e.hemoglobin < 12:
c.font = Font(bold=True, name='Arial', color='FFF00000')
c.fill = PatternFill(fill_type='solid',
start_color='ffcccc',
end_color='ffcccc')
elif 12 <= e.hemoglobin <= 16:
c.font = Font(bold=True, name='Arial', color='006600')
c.fill = PatternFill(fill_type='solid',
start_color='ccffcc',
end_color='ccffcc')
else:
c.font = Font(bold=True, name='Arial', color='FFFFFFFF')
c.fill = PatternFill(fill_type='solid',
start_color='cc0000',
end_color='cc0000')
c = exams.cell(column=11, row=i + 2)
if 3.5 <= e.albumin <= 5:
c.font = Font(bold=True, name='Arial', color='FFF00000')
c.fill = PatternFill(fill_type='solid',
start_color='ffcccc',
end_color='ffcccc')
else:
c.font = Font(bold=True, name='Arial', color='006600')
c.fill = PatternFill(fill_type='solid',
start_color='ccffcc',
end_color='ccffcc')
c = exams.cell(column=12, row=i + 2)
if 2.5 <= e.phosphor <= 4.5:
c.font = Font(bold=True, name='Arial', color='FFF00000')
c.fill = PatternFill(fill_type='solid',
start_color='ffcccc',
end_color='ffcccc')
else:
c.font = Font(bold=True, name='Arial', color='006600')
c.fill = PatternFill(fill_type='solid',
start_color='ccffcc',
end_color='ccffcc')
exams.append(('', 'MÉDIA', '', '', '=MEDIAN(E1:E43)',
'=MEDIAN(F1:F43)', '=MEDIAN(G1:G43)', '', '',
'=MEDIAN(J1:J43)', '=MEDIAN(K1:K43)', '=MEDIAN(L1:L43)'))
exams.append(('', 'DESVIO PADRÃO', '', '', '=STDEV(E1:E43)',
'=STDEV(F1:F43)', '=STDEV(G1:G43)', '', '',
'=STDEV(J1:J43)', '=STDEV(K1:K43)', '=STDEV(L1:L43)'))
for cell in peoples[1]:
cell.fill = PatternFill(start_color="aabedd",
end_color="aabedd",
fill_type="solid")
cell.font = Font(bold=True, name='Arial', color='FFFFFFFF')
for cell in exams[1]:
cell.fill = PatternFill(start_color="aabedd",
end_color="aabedd",
fill_type="solid")
cell.font = Font(bold=True, name='Arial', color='FFFFFFFF')
for cell in exams[44]:
cell.fill = PatternFill(start_color="aabedd",
end_color="aabedd",
fill_type="solid")
cell.font = Font(bold=True, name='Arial', color='FFFFFFFF')
for cell in exams[45]:
cell.fill = PatternFill(start_color="aabedd",
end_color="aabedd",
fill_type="solid")
cell.font = Font(bold=True, name='Arial', color='FFFFFFFF')
file_1.save(peoplesName)
file_2.save(examsName)
_str_ = [
'Desnutrição grau V', 'Desnutrição grau IV',
'Desnutrição grau III', 'Desnutrição grau II',
'Desnutrição grau I', 'Normal', 'Pré-obesidade',
'Obesidade grau I', 'Obesidade grau II', 'Obesidade grau III'
]
summary.append(('Estado nutricional', 'Número de pacientes'))
max, id = 0, 0
for i, x in enumerate(_str_):
summary.append((x, imc_data[i]))
if imc_data[i] > max:
id = i
max = imc_data[i]
pie = PieChart()
labels = Reference(summary, min_col=1, min_row=2, max_row=11)
data = Reference(summary, min_col=2, min_row=1, max_row=11)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Distribuição nutricional"
slice = DataPoint(idx=id, explosion=10)
pie.series[0].data_points = [slice]
pie.height = 6
summary.add_chart(pie, "C1")
summary.append([('')])
summary.append([('')])
summary.append(('Hipertensão', 'Número de pacientes'))
_str_ = [
'Normal (ótimo)', 'Normal', 'Normal (em risco)',
'Hipertensão leve', 'Hipertensão moderada', 'Hipertensão grave'
]
max, id = 0, 0
for i, x in enumerate(_str_):
summary.append((x, hyp_data[i]))
if hyp_data[i] > max:
id = i
max = hyp_data[i]
pie2 = PieChart()
labels = Reference(summary, min_col=1, min_row=15, max_row=20)
data = Reference(summary, min_col=2, min_row=14, max_row=20)
pie2.add_data(data, titles_from_data=True)
pie2.set_categories(labels)
pie2.title = "Distribuição de hipertensão"
slice = DataPoint(idx=id, explosion=10)
pie2.series[0].data_points = [slice]
pie2.height = 6
summary.add_chart(pie2, "C14")
for cell in summary[1]:
cell.fill = PatternFill(start_color="aabedd",
end_color="aabedd",
fill_type="solid")
cell.font = Font(bold=True, name='Arial', color='FFFFFFFF')
for cell in summary[14]:
cell.fill = PatternFill(start_color="aabedd",
end_color="aabedd",
fill_type="solid")
cell.font = Font(bold=True, name='Arial', color='FFFFFFFF')
file_3.save(summaryName)
['Chocolate', 40],
]
wb = Workbook()
ws = wb.active
for row in data:
ws.append(row)
pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Pies sold by category"
# Cut the first slice out of the pie and apply a gradient to it
slice = DataPoint(
idx=0,
explosion=20,
spPr=GraphicalProperties(gradFill=GradientFillProperties(
gsLst=(GradientStop(pos=0, prstClr='blue'),
GradientStop(pos=100000,
schemeClr=SchemeColor(
val='accent1', lumMod=30000, lumOff=70000))))))
pie.series[0].data_points = [slice]
ws.add_chart(pie, "D1")
wb.save("pie.xlsx")
# based on http://www.excel-easy.com/examples/gauge-chart.html
wb = Workbook()
ws = wb.active
for row in data:
ws.append(row)
# First chart is a doughnut chart
c1 = DoughnutChart(firstSliceAng=270, holeSize=50)
c1.title = "Code coverage"
c1.legend = None
ref = Reference(ws, min_col=1, min_row=2, max_row=5)
s1 = Series(ref, title_from_data=False)
slices = [DataPoint(idx=i) for i in range(4)]
slices[0].graphicalProperties.solidFill = "FF3300" # red
slices[1].graphicalProperties.solidFill = "FCF305" # yellow
slices[2].graphicalProperties.solidFill = "1FB714" # green
slices[3].graphicalProperties.noFill = True # invisible
s1.data_points = slices
c1.series = [s1]
# Second chart is a pie chart
c2 = PieChart(firstSliceAng=270)
c2.legend = None
ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4)
s2 = Series(ref, title_from_data=False)
def excel_func(self):
P = self.P
R = self.R
n = self.n
#year variable is considered for advanced calculation
year = 12
#roi is taken for advanced calculation
roi = R / 100
#temp_1 & temp_2 are considered for calculations
temp_1 = (R / 12 / 100)
temp_2 = ((1 + temp_1)**n)
#Processing Fee
Proc_temp = (0.02 * P)
Proc = round(Proc_temp, 2)
#EMI Amount
EMI = ((P * temp_1 * temp_2) / (temp_2 - 1))
#Following values are complementry functions which can be neglected but are important for advance calculations
total_amount = EMI * n
interest = total_amount - P
#Advance Calculation begins here
#This calculation is done only for 1st installment
Int1 = (P * roi) / year
Pri1 = EMI - Int1
Pen1 = P - Pri1
#Rounding of the values of 1st installment to 2 decimal places
Inter = round(Int1, 2)
Princ = round(Pri1, 2)
Pendi = round(Pen1, 2)
matrix_1 = [Inter, Princ, Pendi]
#print("\nInstallment Number: 1")
#print (matrix_1)
#this temporary variable is considered since interest requires pending of previous loop
temp_7 = Pen1
#to initialise excel sheet
wb = Workbook()
ws = wb.get_sheet_by_name(name='Sheet')
ws.column_dimensions["A"].width = 19
ws.column_dimensions["B"].width = 19
ws.column_dimensions["C"].width = 19
ws.column_dimensions["D"].width = 19
ws.column_dimensions["E"].width = 19
#To set various styles
font1 = Font(name='Trebuchet MS',
size=10,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
font2 = Font(name='Trebuchet MS',
size=10,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
fill1 = PatternFill(fill_type='solid',
start_color='FF0000',
end_color='FF0000')
border1 = Border(left=Side(border_style='thin', color='00000000'),
right=Side(border_style='thin', color='00000000'),
top=Side(border_style='thin', color='00000000'),
bottom=Side(border_style='thin', color='00000000'),
diagonal=Side(border_style=None, color='FF000000'),
diagonal_direction=0,
outline=Side(border_style=None, color='FF000000'),
vertical=Side(border_style=None, color='FF000000'),
horizontal=Side(border_style=None, color='FF000000'))
alignment1 = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
alignment2 = Alignment(horizontal='left',
vertical='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
alignment3 = Alignment(horizontal='right',
vertical='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
protection1 = Protection(locked=True, hidden=False)
currency_format = "#,##0.00;[Red]-#,##0.00"
#this is to write header file in the top of excel
ws.cell(row=1, column=1).value = "Principal Amount(Rs):"
ws.cell(row=2, column=1).value = "Rate of Interest (%):"
ws.cell(row=3, column=1).value = "Tenure (Months):"
ws.cell(row=5, column=1).value = "Processing Fee"
ws.cell(
row=7, column=1
).value = "Total Amount Paid:(Including Principle Amount & Interest)"
ws.cell(row=8, column=1).value = "Total Interest paid: "
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=3)
ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=3)
ws.merge_cells(start_row=3, start_column=1, end_row=3, end_column=3)
ws.merge_cells(start_row=4, start_column=1, end_row=4, end_column=3)
ws.merge_cells(start_row=5, start_column=1, end_row=5, end_column=3)
ws.merge_cells(start_row=6, start_column=1, end_row=6, end_column=3)
ws.merge_cells(start_row=7, start_column=1, end_row=7, end_column=3)
ws.merge_cells(start_row=8, start_column=1, end_row=8, end_column=3)
ws.cell(row=1, column=4).value = ("Rs. {0:.2f}".format(P))
ws.cell(row=2, column=4).value = ("{0:.2f} %".format(R))
ws.cell(row=3, column=4).value = ("{0:d} Months".format(n))
ws.cell(row=5, column=4).value = ("Rs. {0:.2f}".format(Proc))
ws.cell(row=7, column=4).value = ("Rs. {0:.2f}".format(total_amount))
ws.cell(row=8, column=4).value = ("Rs. {0:.2f}".format(interest))
#this is to write header content of table
ws.cell(row=15, column=1).value = "Installment Number"
ws.cell(row=15, column=2).value = "Principal"
ws.cell(row=15, column=3).value = "Interest"
ws.cell(row=15, column=4).value = "EMI Amount"
ws.cell(row=15, column=5).value = "Pending Amount"
#this is to write details of 1st installment since formula is bit different
inst_no = 1
xyz = 16
ws.cell(row=xyz, column=1).value = inst_no
ws.cell(row=xyz, column=2).value = Princ
ws.cell(row=xyz, column=3).value = Inter
ws.cell(row=xyz, column=4).value = (Princ + Inter)
ws.cell(row=xyz, column=5).value = Pendi
xyz += 1
#xyz is considered for looping purpose.
inst_no = 2
while inst_no <= n:
Inte = (temp_7 * roi) / year
Prin = EMI - Inte
Pend = temp_7 - Prin
#All the float values are rounded of to 2 decimal places
Intere = round(Inte, 2)
Princi = round(Prin, 2)
Pendin = round(Pend, 2)
#Data (which was rounded of to 2 Decimal places) is written in specific cells.
ws.cell(row=xyz, column=1).value = inst_no
ws.cell(row=xyz, column=2).value = Princi
ws.cell(row=xyz, column=3).value = Intere
ws.cell(row=xyz, column=4).value = (Princi + Intere)
ws.cell(row=xyz, column=5).value = Pendin
temp_7 = Pend
xyz += 1
inst_no += 1
#For final totaling of Principal Amount and Interest
ws.cell(row=xyz, column=1).value = "Total"
ws.cell(row=xyz, column=2).value = P
ws.cell(row=xyz, column=3).value = round(interest, 2)
ws.cell(row=xyz, column=4).value = "---"
ws.cell(row=xyz, column=5).value = Pendin
row_temp = 1
while (row_temp <= 15):
column_temp = 1
while (column_temp <= 5):
ws.cell(row=row_temp, column=column_temp).font = font1
ws.cell(row=row_temp,
column=column_temp).alignment = alignment2
column_temp += 1
row_temp += 1
row_temp = 15
while (row_temp <= xyz):
column_temp = 1
while (column_temp <= 5):
ws.cell(row=row_temp,
column=column_temp).alignment = alignment1
ws.cell(row=row_temp, column=column_temp).border = border1
column_temp += 1
row_temp += 1
row_temp = 16
while (row_temp <= xyz):
column_temp = 1
while (column_temp <= 5):
ws.cell(row=row_temp, column=column_temp).font = font2
column_temp += 1
row_temp += 1
column_temp = 1
while (column_temp <= 5):
ws.cell(row=(xyz), column=column_temp).font = font1
column_temp += 1
row_temp = 1
while (row_temp <= 8):
ws.cell(row=row_temp, column=4).alignment = alignment3
row_temp += 1
row_temp = 16
while (row_temp <= xyz):
column_temp = 2
while (column_temp <= 5):
ws.cell(row=row_temp,
column=column_temp).number_format = currency_format
column_temp += 1
row_temp += 1
#ws.cell(row=1, column=2).number_format = currency_format
#ws.cell(row=5, column=2).number_format = currency_format
#ws.cell(row=7, column=2).number_format = currency_format
#ws.cell(row=8, column=2).number_format = currency_format
data = Reference(ws,
min_col=2,
min_row=15,
max_col=3,
max_row=(xyz - 1))
titles = Reference(ws,
min_col=1,
max_col=1,
min_row=16,
max_row=(xyz - 1))
chart = BarChart()
chart.title = "Detailed Monthly Report"
chart.x_axis.title = 'Installment Number'
chart.y_axis.title = 'Monthly EMI'
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
chart.height = 15.88
chart.width = 31.3
chart.grouping = "stacked"
chart.overlap = 100
ws.add_chart(chart, "G17")
pie = PieChart()
labels = Reference(ws, min_col=2, max_col=3, min_row=15, max_row=15)
data = Reference(ws, min_col=2, max_col=3, min_row=40, max_row=40)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 7.41
pie.width = 11.85
pie.title = "Total Principal to Interest Ratio"
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
ws.add_chart(pie, "G1")
wb.save("EMI_{0:.2f}_{1:d}_{2:.2f}.xlsx".format(P, n, R))
print("Excel file is generated.")
self.root.destroy()
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 excelReportIP(self, fname='ip_report.xlsx'):
"""create an excel with all packets and top IPs with graphs"""
def set_headers(hrs, sheet, r=1):
header_font = Font(color=colors.BLUE, bold=True)
for h in hrs:
cell = sheet.cell(row=r, column=hrs.index(h) + 1, value=h)
cell.font = header_font
return sheet
if self.packets:
wb = Workbook()
ws = wb.active
ws.title = 'packets'
# headers
headers = ['DateTime', 'Protocol', 'Source IP', 'Source Port', 'Destination IP', 'Destination Port']
ws = set_headers(headers, ws)
# values
row = 2
for pkt in self.packets:
if IP in pkt:
ws.cell(row=row, column=headers.index('DateTime') + 1, value=datetime.fromtimestamp(pkt.time))
ws.cell(row=row, column=headers.index('Protocol') + 1, value=pkt[IP].proto)
ws.cell(row=row, column=headers.index('Source IP') + 1, value=pkt[IP].src)
ws.cell(row=row, column=headers.index('Destination IP') + 1, value=pkt[IP].dst)
if TCP in pkt[IP]:
layer = TCP
elif UDP in pkt[IP]:
layer = UDP
else:
layer = None
if layer:
ws.cell(row=row, column=headers.index('Source Port') + 1, value=pkt[IP][layer].sport)
ws.cell(row=row, column=headers.index('Destination Port') + 1, value=pkt[IP][layer].dport)
row += 1
# charts
wb.create_sheet('Charts')
ws = wb['Charts']
# top 10 src ip
headers = ['Count', 'IP']
ws.cell(row=1, column=1, value='Top 10 Source IP')
ws = set_headers(headers, ws, r=2)
row = 3
for count, ip in self.topSrcIP():
ws.cell(row=row, column=1, value=count)
ws.cell(row=row, column=2, value=ip)
row += 1
# pie chart
pie_chart = PieChart()
labels = Reference(ws, min_col=2, min_row=3, max_row=row - 1)
chart_data = Reference(ws, min_col=1, min_row=3, max_row=row - 1)
pie_chart.add_data(chart_data, titles_from_data=True)
pie_chart.set_categories(labels)
pie_chart.title = 'Top 10 Source IP'
# Cut the first slice out of the pie
pie_slice = DataPoint(idx=0, explosion=20)
pie_chart.series[0].data_points = [pie_slice]
ws.add_chart(pie_chart, "F2")
# top 10 dst ip
headers = ['Count', 'IP']
ws.cell(row=19, column=1, value='Top 10 Destination IP')
ws = set_headers(headers, ws, r=20)
row = 21
for count, ip in self.topSrcIP():
ws.cell(row=row, column=1, value=count)
ws.cell(row=row, column=2, value=ip)
row += 1
# pie chart
bar_chart = BarChart()
bar_chart.type = "col"
bar_chart.style = 10
bar_chart.title = 'Top 10 Destination IP'
bar_chart.y_axis.title = 'count'
chart_data = Reference(ws, min_col=1, min_row=21, max_row=row - 1)
cats = Reference(ws, min_col=2, min_row=21, max_row=row - 1)
bar_chart.add_data(chart_data, titles_from_data=True)
bar_chart.set_categories(cats)
bar_chart.shape = 4
ws.add_chart(bar_chart, "F20")
wb.save(fname)
else:
print('no packets, no report')
]
wb.create_sheet('Pie Chart')
ws9 = wb['Pie Chart']
for row in data:
ws9.append(row)
pie = PieChart()
labels = Reference(ws9, min_col=1, min_row=2, max_row=5)
data = Reference(ws9, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Pies sold by category"
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
ws9.add_chart(pie, "D1")
ws10 = wb.create_sheet(title="Projection")
data = [
['Page', 'Views'],
['Search', 95],
['Products', 4],
['Offers', 0.5],
['Sales', 0.5],
]
bar_chart.add_data(series2, titles_from_data=True)
bar_chart.title = "Bar Chart"
bar_chart.style = 11
bar_chart.x_axis.title = 'Size'
bar_chart.y_axis.title = 'Percentage'
ws1.add_chart(bar_chart, "A16")
# Add a Scatter chart
scatter_chart = ScatterChart()
scatter_chart.title = "Scatter Chart"
scatter_chart.style = 14
scatter_chart.x_axis.title = 'Size'
scatter_chart.y_axis.title = 'Percentage'
series = Series(series1, series2, title_from_data=True)
scatter_chart.series.append(series)
ws1.add_chart(scatter_chart, "G1")
# Add a Pie chart
pie_chart = PieChart()
labels = Reference(ws1, min_col=1, min_row=1, max_col=4, max_row=1)
pie_chart.add_data(series5, titles_from_data=True)
pie_chart.set_categories(labels)
pie_chart.title = "Pie Chart"
# Cut the first slice out of the pie
pie_slice = DataPoint(idx=0, explosion=40)
pie_chart.series[0].data_points = [pie_slice]
ws1.add_chart(pie_chart, "K16")
wb.save(filename)