def writeToExcel(worksheet: xlsx.worksheet,
keys: tuple,
data: list,
row=0,
col=0,
maxRow=inf,
maxCol=inf):
'''
This function write list of dict to Excel file
worksheet: xlsx worksheet to write into
keys: a tuple of keys because keys() would randomize the keys
data: List of dict
row: row to start
col: col to start
maxRow: Maximum row counts
maxCol: Maximum column counts
return
row: The cursor of the row, int type
col: The cursor of the col, int type
'''
# Write the header
for key in keys:
worksheet.write(row, col, key)
col += 1
col = 0
row += 1
for k in data:
# Check if exceed max value of row
if row > maxRow:
break
# now k is a dictionary
for key in keys:
# Check if exceed the max value of col
if col > maxCol:
break
# now key is one of index of k, iterate and fill in xlsx
worksheet.write(row, col, k[key])
# After write, the col plus 1
col += 1
# Then iterate all keys
# After one section done, cols need to rewind
col = 0
# And go to next row
row += 1
return row, col
def writeDOWRows(ws: xlsxwriter.worksheet) -> None:
"""
Write the rows in the DOW worksheet for all courseDOW.
:param ws: Excel Worksheet Object
"""
global coursesDOW
global courseDOWWsRow
col = 0
for dow in coursesDOW:
courseDOWId = dow.getId()
ws.write(courseDOWWsRow, col, courseDOWId)
col += 1
courseId = dow.getCourseId()
ws.write(courseDOWWsRow, col, courseId)
col += 1
ws.write(courseDOWWsRow, col, dow.getDOW())
courseDOWWsRow += 1
col = 0
def addJourneys(apiKey: str, worksheet: xlsxwriter.worksheet, dateformat,
serial: str, startRow: int):
yesterday = datetime.now(BRUSSELS_TZ) - timedelta(days=1)
start = datetime(yesterday.year,
yesterday.month,
yesterday.day,
0,
0,
0,
tzinfo=BRUSSELS_TZ)
end = datetime(yesterday.year,
yesterday.month,
yesterday.day,
23,
59,
59,
tzinfo=BRUSSELS_TZ)
q = {'from': start.isoformat(), 'to': end.isoformat(), 'apiKey': apiKey}
journeyUrl = server + '/rest/api/v2/sigfoxdevices/' + serial + '/journey'
r = requests.get(journeyUrl, params=q)
journey = r.json()
# check if we have something
if not 'journeyParts' in journey:
return
# let's only focus on the trips ...
trips = list(
filter(lambda e: e['type'] == 'ON_THE_MOVE', journey['journeyParts']))
print(f"Trip length {len(trips)}")
row = startRow
for t in trips:
start = dateutil.parser.isoparse(
t['startLocation']['timestamp']).astimezone(tz=BRUSSELS_TZ)
stop = dateutil.parser.isoparse(
t['stopLocation']['timestamp']).astimezone(tz=BRUSSELS_TZ)
worksheet.write(row, 0, serial)
worksheet.write(row, 1, t['startLocation'].get('address'))
worksheet.write_number(row, 2, t['startLocation']['lat'])
worksheet.write_number(row, 3, t['startLocation']['lng'])
worksheet.write_datetime(row, 4, start.replace(tzinfo=None),
dateformat)
worksheet.write(row, 5, t['stopLocation'].get('address'))
worksheet.write_number(row, 6, t['stopLocation']['lat'])
worksheet.write_number(row, 7, t['stopLocation']['lng'])
worksheet.write_datetime(row, 8, stop.replace(tzinfo=None), dateformat)
worksheet.write_number(row, 9, round(t.get('distance') / 1000, 3))
worksheet.write_number(row, 10,
round((stop - start).total_seconds() / 60, 0))
row = row + 1
return row
def save(self, sheet: worksheet, **formats) -> None:
# Formats:
bold = formats["bold"]
merge_format = formats["merge_format"]
# Set spacing:
prob_horiz_spacing = self.__column_width + 2
norm_horiz_spacing = prob_horiz_spacing + 1
test_horizontal_spacing = prob_horiz_spacing + norm_horiz_spacing * 2 + 3
matrix_vertical_spacing = self.__column_width + 5
# Write the header:
sheet.merge_range('A1:X1', self.title, merge_format)
# Write the headers:
if self.__use_k_fold:
sheet.write(2, 0, "Probability Distributions", bold)
sheet.write(2, norm_horiz_spacing,
"Normalized Averaged Distributions", bold)
sheet.write(2, norm_horiz_spacing * 2 + 1,
"Normalized Combined Distributions", bold)
sheet.write(2, test_horizontal_spacing, "Test Results", bold)
else:
sheet.write(2, 0, "Probability Distributions", bold)
sheet.write(2, norm_horiz_spacing, "Normalized Distributions",
bold)
sheet.write(2, norm_horiz_spacing * 2 + 1,
"Normalized Combined Distributions", bold)
sheet.write(2, test_horizontal_spacing, "Test Results", bold)
sort_matrices(matrix_list=self.__normalized_distributions)
sort_matrices(matrix_list=self.__n_c_distributions)
self.__normalized_averaged_matrices = self.__normalized_distributions
self.__normalized_combined_matrices = self.__n_c_distributions
# Write the probability distribution columns:
for index, normalized_distribution in enumerate(
self.__normalized_averaged_matrices):
probability_distribution = normalized_distribution.parent_matrix
# Probability distributions:
for row, value_list in enumerate(
probability_distribution.csv_list):
for col, val in enumerate(value_list):
if row == 0: # Matrix Header
sheet.write(row + 3 + matrix_vertical_spacing * index,
col, val, bold)
else:
sheet.write(row + 3 + matrix_vertical_spacing * index,
col, val)
# Normalized probability distributions:
for row, value_list in enumerate(normalized_distribution.csv_list):
for col, val in enumerate(value_list):
if row == 0: # Matrix Header
sheet.write(row + 3 + matrix_vertical_spacing * index,
col + norm_horiz_spacing, val, bold)
else:
sheet.write(row + 3 + matrix_vertical_spacing * index,
col + norm_horiz_spacing, val)
# Write the normalized combined distribution column:
norm_horiz_spacing *= 2
for index, distribution in enumerate(
self.__normalized_combined_matrices):
for row, value_list in enumerate(distribution.csv_list):
for col, val in enumerate(value_list):
if row == 0: # Matrix Header
sheet.write(row + 3 + matrix_vertical_spacing * index,
col + norm_horiz_spacing + 1, val, bold)
else:
sheet.write(row + 3 + matrix_vertical_spacing * index,
col + norm_horiz_spacing + 1, val)
#JC-01 add code to report 2nd guess accuracy
# Write the test result column:
for index, distribution in enumerate(
self.__normalized_combined_matrices):
result = self.__test_results[distribution]
# Num tests ran:
sheet.write(3 + matrix_vertical_spacing * index,
test_horizontal_spacing, distribution.csv_list[0][0],
bold)
sheet.write(4 + matrix_vertical_spacing * index,
test_horizontal_spacing, "Tests Ran:")
sheet.write(4 + matrix_vertical_spacing * index,
test_horizontal_spacing + 1, result.tests_ran)
# Test Result Headers:
sheet.write(5 + matrix_vertical_spacing * index,
test_horizontal_spacing, "Zones:")
sheet.write(6 + matrix_vertical_spacing * index,
test_horizontal_spacing, "Times Tested:")
sheet.write(7 + matrix_vertical_spacing * index,
test_horizontal_spacing, "Times Correct:")
sheet.write(8 + matrix_vertical_spacing * index,
test_horizontal_spacing, "Zone Percentage Correct:")
sheet.write(9 + matrix_vertical_spacing * index,
test_horizontal_spacing, "Overall Percentage Correct:")
sheet.write(9 + matrix_vertical_spacing * index,
test_horizontal_spacing + 1, result.accuracy)
sheet.write(10 + matrix_vertical_spacing * index,
test_horizontal_spacing, "Times 2nd Correct:")
sheet.write(11 + matrix_vertical_spacing * index,
test_horizontal_spacing,
"Zone Percentage with 2nd Correct:")
# Test Result Data:
with2 = 0
percentage_eq = '=INDIRECT(ADDRESS(ROW()-1, COLUMN()))/INDIRECT(ADDRESS(ROW()-2,COLUMN()))'
for zone, zone_results in result.answer_details.items():
# Zone Numbers:
sheet.write(5 + matrix_vertical_spacing * index,
test_horizontal_spacing + zone, str(zone))
# Times Tested:
sheet.write(6 + matrix_vertical_spacing * index,
test_horizontal_spacing + zone,
zone_results["times_tested"])
# Times Correct:
sheet.write(7 + matrix_vertical_spacing * index,
test_horizontal_spacing + zone,
zone_results["times_correct"])
# Zone correct percentage:
sheet.write(
8 + matrix_vertical_spacing * index,
test_horizontal_spacing + zone,
zone_results["times_correct"] /
zone_results["times_tested"])
# Zone 2nd correct:
sheet.write(10 + matrix_vertical_spacing * index,
test_horizontal_spacing + zone,
zone_results["times_2nd_correct"])
# Zone correct percentage:
sheet.write(11 + matrix_vertical_spacing * index,
test_horizontal_spacing + zone,
(zone_results["times_correct"] +
zone_results["times_2nd_correct"]) /
zone_results["times_tested"])
# No. of success including the 2nd times
with2 = with2 + zone_results["times_correct"] + zone_results[
"times_2nd_correct"]
sheet.write(12 + matrix_vertical_spacing * index,
test_horizontal_spacing,
"Overall Percentage with 2nd Correct:")
sheet.write(12 + matrix_vertical_spacing * index,
test_horizontal_spacing + 1, with2 / result.tests_ran)
def save_key_page(sheet: worksheet, **formats) -> None:
# Formats:
bold = formats["bold"]
merge_format = formats["merge_format"]
title = "KEYS"
example_title = "{ Combinations } - { Dates } - { Error Mode } - { Combination Mode }"
# Write the header:
sheet.merge_range('D4:H4', title, merge_format)
# Write the example page header:
sheet.write("D6", "Examples:", bold)
sheet.write("E6", example_title)
# Write the key:
# -- Left column:
sheet.write("D9", "In title:", bold)
sheet.write("D10", "{ Combinations }")
sheet.write("D11", "{ Dates }")
sheet.write("D12", "{ Error Mode }")
sheet.write("D13", "{ Combination Mode }")
# -- Middle column:
sheet.write("F9", "Example:", bold)
sheet.write("F10", "2 Combinations")
sheet.write("F11", "N19, N20")
sheet.write("F12", "WGT Error Mode")
sheet.write("F13", "AB Combination Mode")
# -- Right column:
sheet.write("H9", "Meaning:", bold)
sheet.write("H10", "Using a combination of 2 matrices.")
sheet.write(
"H11", "Using data from November 19 (N19), and November 20 (N20).")
sheet.write("H12", "Using weighted errors.")
sheet.write("H13", "Using Adaptive Boosting combination method.")
def writeNavigationEventRows(ws: xlsxwriter.worksheet) -> None:
"""
Write the rows in the navigationEvents worksheet for all navigationEvents.
:param ws: Excel Worksheet Object
"""
global navigationEvents
global navigationEventWsRow
col = 0
neUser = navigationEvents[0].getUserId()
for navigationEvent in navigationEvents:
if navigationEvent.getUserId() != neUser:
for x in range(6):
ws.write(navigationEventWsRow, 1, "")
navigationEventWsRow += 1
neUser = navigationEvent.getUserId()
ws.write(navigationEventWsRow, col, navigationEvent.getUserId())
col += 1
ws.write(navigationEventWsRow, col, navigationEvent.getTimestamp())
col += 2
ws.write(navigationEventWsRow, col, navigationEvent.getEvent())
col += 1
ws.write(navigationEventWsRow, col, navigationEvent.getParticipantId())
col += 1
ws.write(navigationEventWsRow, col, navigationEvent.getGroup())
navigationEventWsRow += 1
col = 0
def writeTimeInModalRows(ws: xlsxwriter.worksheet) -> None:
"""
Write the rows in the TimeInModal worksheet for all times in modal.
:param ws: Excel Worksheet Object
"""
global timeInModals
global timeInModalWsRow
col = 0
tmUser = timeInModals[0].getUserId()
for timeInModal in timeInModals:
if timeInModal.getUserId() != tmUser:
for x in range(6):
ws.write(timeInModalWsRow, 1, "")
timeInModalWsRow += 1
tmUser = timeInModal.getUserId()
ws.write(timeInModalWsRow, col, timeInModal.getUserId())
col += 1
ws.write(timeInModalWsRow, col, timeInModal.getTimeEnteredModal())
col += 1
ws.write(timeInModalWsRow, col, timeInModal.getTimeClosedModal())
col += 1
ws.write(timeInModalWsRow, col, timeInModal.getTimeInModal())
col += 1
ws.write(timeInModalWsRow, col, timeInModal.getDisposition())
col += 1
ws.write(timeInModalWsRow, col, timeInModal.getParticipantId())
col += 1
ws.write(timeInModalWsRow, col, timeInModal.getGroup())
timeInModalWsRow += 1
col = 0
def save(self, sheet: worksheet, **formats) -> None:
# Formats:
bold = formats["bold"]
merge_format = formats["merge_format"]
high_light = formats["high_light"]
# Set spacing:
prob_horiz_spacing = self.__column_width + 2 # 8
norm_horiz_spacing = prob_horiz_spacing + 1 # 9
test_horizontal_spacing = norm_horiz_spacing * 2 + 1 # 19
basic_distribution_spacing = norm_horiz_spacing * 3 + 1 # 28
matrix_vertical_spacing = self.__column_width + 5 # 11
d_vertical_starting = 3
# Write the header:
sheet.merge_range('A1:X1', self.title, merge_format)
# Write the headers:
sheet.write(2, 0, "Probability Distributions", bold)
sheet.write(2, norm_horiz_spacing,
"Normalized Probability Distributions", bold)
sheet.write(2, test_horizontal_spacing, "Test Results", bold)
# sheet.write(2, basic_distribution_spacing, "Basic Distributions")
# sheet.write(2, norm_horiz_spacing * 2 + 1, "Normalized Combined Distributions", bold)
# sheet.write(2, test_horizontal_spacing, "Test Results", bold)
for d, normalized_list in self.__normalized_probability_matrices.items(
):
results = self.__test_results[d]
index_d = d - 2
sheet.merge_range(d_vertical_starting, 0, d_vertical_starting,
self.__column_width,
"D={}, Num={}".format(d, len(normalized_list)),
high_light)
sheet.merge_range(d_vertical_starting, norm_horiz_spacing,
d_vertical_starting,
norm_horiz_spacing + self.__column_width + 2,
"D={}, Num={}".format(d, len(normalized_list)),
high_light)
sheet.merge_range(d_vertical_starting, test_horizontal_spacing,
d_vertical_starting,
test_horizontal_spacing + self.__column_width,
"D={}, Num={}".format(d,
len(results)), high_light)
# Write the probability distribution columns:
for index, normalized_distribution in enumerate(normalized_list):
probability_distribution = normalized_distribution.parent_matrix
# Probability distributions:
for row, value_list in enumerate(
probability_distribution.csv_list):
for col, val in enumerate(value_list):
if row == 0: # Matrix Header
sheet.write(
row + d_vertical_starting + 1 +
matrix_vertical_spacing * index, col, val,
bold)
else:
sheet.write(
row + d_vertical_starting + 1 +
matrix_vertical_spacing * index, col, val)
# Normalized probability distributions:
for row, value_list in enumerate(
normalized_distribution.csv_list):
for col, val in enumerate(value_list):
if row == 0: # Matrix Header
sheet.write(
row + d_vertical_starting + 1 +
matrix_vertical_spacing * index,
col + norm_horiz_spacing, val, bold)
else:
sheet.write(
row + d_vertical_starting + 1 +
matrix_vertical_spacing * index,
col + norm_horiz_spacing, val)
for index, result in enumerate(results):
# index = list(self.__test_results.keys()).index(distribution)
distribution = normalized_list[index]
# Num tests ran:
sheet.write(
1 + d_vertical_starting + matrix_vertical_spacing * index,
test_horizontal_spacing, distribution.csv_list[0][0], bold)
sheet.write(
2 + d_vertical_starting + matrix_vertical_spacing * index,
test_horizontal_spacing, "Tests Ran:")
sheet.write(
2 + d_vertical_starting + matrix_vertical_spacing * index,
test_horizontal_spacing + 1, result.tests_ran)
# Test Result Headers:
sheet.write(
3 + d_vertical_starting + matrix_vertical_spacing * index,
test_horizontal_spacing, "Zones:")
sheet.write(
4 + d_vertical_starting + matrix_vertical_spacing * index,
test_horizontal_spacing, "Times Tested:")
sheet.write(
5 + d_vertical_starting + matrix_vertical_spacing * index,
test_horizontal_spacing, "Times Correct:")
sheet.write(
6 + d_vertical_starting + matrix_vertical_spacing * index,
test_horizontal_spacing, "Zone Percentage Correct:")
sheet.write(
7 + d_vertical_starting + matrix_vertical_spacing * index,
test_horizontal_spacing, "Overall Percentage Correct:")
sheet.write(
7 + d_vertical_starting + matrix_vertical_spacing * index,
test_horizontal_spacing + 1, result.accuracy)
sheet.write(
8 + d_vertical_starting + matrix_vertical_spacing * index,
test_horizontal_spacing, "Times 2nd Correct:")
sheet.write(
9 + d_vertical_starting + matrix_vertical_spacing * index,
test_horizontal_spacing,
"Zone Percentage with 2nd Correct:")
# Te st Result Data:
with2 = 0
percentage_eq = '=INDIRECT(ADDRESS(ROW()-1, COLUMN()))/INDIRECT(ADDRESS(ROW()-2,COLUMN()))'
for zone, zone_results in result.answer_details.items():
# Zone Numbers:
sheet.write(
3 + d_vertical_starting +
matrix_vertical_spacing * index,
test_horizontal_spacing + zone.num, str(zone))
# Times Tested:
sheet.write(
4 + d_vertical_starting +
matrix_vertical_spacing * index,
test_horizontal_spacing + zone.num,
zone_results["times_tested"])
# Times Correct:
sheet.write(
5 + d_vertical_starting +
matrix_vertical_spacing * index,
test_horizontal_spacing + zone.num,
zone_results["times_correct"])
# Zone correct percentage:
sheet.write(
6 + d_vertical_starting +
matrix_vertical_spacing * index,
test_horizontal_spacing + zone.num,
zone_results["times_correct"] /
zone_results["times_tested"])
# Zone 2nd correct:
sheet.write(
8 + d_vertical_starting +
matrix_vertical_spacing * index,
test_horizontal_spacing + zone.num,
zone_results["times_2nd_correct"])
# Zone correct percentage:
sheet.write(
9 + d_vertical_starting +
matrix_vertical_spacing * index,
test_horizontal_spacing + zone.num,
(zone_results["times_correct"] +
zone_results["times_2nd_correct"]) /
zone_results["times_tested"])
# No. of success including the 2nd times
with2 = with2 + zone_results[
"times_correct"] + zone_results["times_2nd_correct"]
sheet.write(
10 + d_vertical_starting + matrix_vertical_spacing * index,
test_horizontal_spacing,
"Overall Percentage with 2nd Correct:")
sheet.write(
10 + d_vertical_starting + matrix_vertical_spacing * index,
test_horizontal_spacing + 1, with2 / result.tests_ran)
d_vertical_starting += matrix_vertical_spacing * len(
normalized_list) * index_d
def tuan_dui(wb: xlsxwriter.Workbook, ws: xlsxwriter.worksheet) -> None:
'''
编写分公司简报中内部团队统计表
'''
logging.debug('开始写入内部团队数据统计表')
# 获取单元格样式对象
sy = Style(wb)
# 获取日期对象
idate = IDate(2020)
# 设置行、列计数器
nrow = 0
ncol = 0
# 写入表标题
ws.merge_range(first_row=nrow,
first_col=ncol,
last_row=nrow,
last_col=ncol + 6,
data='2020年内部团队数据统计表',
cell_format=sy.title)
# 设置表标题行高为字体的两倍
ws.set_row(row=nrow, height=24)
nrow += 1
logging.debug('表标题写入完成')
# 写入说明性文字，数据统计的时间范围
ws.merge_range(first_row=nrow,
first_col=ncol,
last_row=nrow,
last_col=ncol + 6,
data=f'数据统计范围：2020-01-01 至 {idate.long_ri_qi()}',
cell_format=sy.explain)
nrow += 1
logging.debug('统计范围说明性文字写入完成')
# 写入表头
biao_ti = ['序号', '机构', '险种', '计划任务', '累计保费', '时间进度\n达成率', '同比\n增长率']
for value in biao_ti:
ws.write(nrow, ncol, value, sy.string_bold_gray)
ncol += 1
nrow += 1
ncol = 0
logging.debug('表头写入完成')
# 设置机构名称列表
ji_gou_list = (('曲靖中支本部', '曲靖一部', '曲靖二部', '曲靖三部'),
('文山中支本部', '文山一部', '文山二部'), ('保山中支本部', '保山一部', '保山二部'),
('大理中支本部', ), ('版纳中支本部', ), ('怒江中支本部', ))
# 设置险种名称列表
risk_list = ('车险', '非车险', '驾意险', '整体')
hui = False # 机构名称改变的计数器
xu_hao = 1 # 序号计数器
# 将数据写入表中
for name_list in ji_gou_list:
if hui is False:
wen_zi = sy.string
shu_zi = sy.number
bai_fen_bi = sy.percent
hui = True
else:
wen_zi = sy.string_gray
shu_zi = sy.number_gray
bai_fen_bi = sy.percent_gray
hui = False
n = len(name_list) * 4 - 1
ws.merge_range(nrow, ncol, nrow + n, ncol, xu_hao, wen_zi)
for name in name_list:
if hui is False:
wen_zi = sy.string_gray
shu_zi = sy.number_gray
bai_fen_bi = sy.percent_gray
else:
wen_zi = sy.string
shu_zi = sy.number
bai_fen_bi = sy.percent
ws.merge_range(nrow, ncol + 1, nrow + 3, ncol + 1, name, wen_zi)
for risk in risk_list:
if risk == '整体':
if hui is False:
wen_zi = sy.string_bold_gray
shu_zi = sy.number_bold_gray
bai_fen_bi = sy.percent_bold_gray
else:
wen_zi = sy.string_bold
shu_zi = sy.number_bold
bai_fen_bi = sy.percent_bold
else:
if hui is False:
wen_zi = sy.string_gray
shu_zi = sy.number_gray
bai_fen_bi = sy.percent_gray
else:
wen_zi = sy.string
shu_zi = sy.number
bai_fen_bi = sy.percent
d = Tong_Ji(name=name, risk=risk)
ws.write(nrow, ncol + 2, d.xian_zhong, wen_zi)
ws.write(nrow, ncol + 3, d.ren_wu(), wen_zi)
ws.write(nrow, ncol + 4, d.nian_bao_fei(), shu_zi)
ws.write(nrow, ncol + 5, d.shi_jian_da_cheng(), bai_fen_bi)
ws.write(nrow, ncol + 6, d.nian_tong_bi(), bai_fen_bi)
nrow += 1
logging.debug(f'{name}机构数据写入完成')
xu_hao += 1
risk_list = ('车险', '非车险', '驾意险')
ws.merge_range(nrow, ncol, nrow + 4, ncol, xu_hao, sy.string)
ws.merge_range(nrow, ncol + 1, nrow + 4, ncol + 1, '分公司本部', sy.string)
for risk in risk_list:
d = Tong_Ji(name='分公司本部', risk=risk)
ws.write(nrow, ncol + 2, d.xian_zhong, sy.string)
ws.write(nrow, ncol + 3, d.ren_wu(), sy.string)
ws.write(nrow, ncol + 4, d.nian_bao_fei(), sy.number)
ws.write(nrow, ncol + 5, d.shi_jian_da_cheng(), sy.percent)
ws.write(nrow, ncol + 6, d.nian_tong_bi(), sy.percent)
nrow += 1
d = Tong_Ji(name='航旅项目', risk='整体')
ws.write(nrow, ncol + 2, '航旅项目', sy.string)
ws.write(nrow, ncol + 3, d.ren_wu(), sy.string)
ws.write(nrow, ncol + 4, d.nian_bao_fei(), sy.number)
ws.write(nrow, ncol + 5, d.shi_jian_da_cheng(), sy.percent)
ws.write(nrow, ncol + 6, d.nian_tong_bi(), sy.percent)
nrow += 1
d = Tong_Ji(name='分公司本部', risk='整体')
h = Tong_Ji(name='航旅项目', risk='整体')
ren_wu = d.ren_wu() + h.ren_wu()
nian_bao_fei = d.nian_bao_fei() + h.nian_bao_fei()
shi_jian_da_cheng = nian_bao_fei / ren_wu / d.shi_jian_jin_du()
wang_nian_bao_fei = d.nian_bao_fei(ny=1) + h.nian_bao_fei(ny=1)
nian_tong_bi = nian_bao_fei / wang_nian_bao_fei - 1
ws.write(nrow, ncol + 2, '整体', sy.string_bold)
ws.write(nrow, ncol + 3, ren_wu, sy.string_bold)
ws.write(nrow, ncol + 4, nian_bao_fei, sy.number_bold)
ws.write(nrow, ncol + 5, shi_jian_da_cheng, sy.percent_bold)
ws.write(nrow, ncol + 6, nian_tong_bi, sy.percent_bold)
logging.debug(f'分公司本部机构数据写入完成')
# 开始设置列宽
ncol = 0
ws.set_column(first_col=ncol, last_col=ncol, width=4)
ws.set_column(first_col=ncol + 1, last_col=ncol + 1, width=15)
ws.set_column(first_col=ncol + 2, last_col=ncol + 4, width=10)
ws.set_column(first_col=ncol + 5, last_col=ncol + 6, width=12)
logging.debug('列宽设置完成')
logging.debug('内部团队数据统计表写入完成')
logging.debug('-' * 60)
def ji_gou(wb: xlsxwriter.Workbook,
ws: xlsxwriter.worksheet) -> None:
'''
编写分公司简报中四级机构统计表
'''
logging.debug('开始写入四级机构数据统计表')
# 获取单元格样式对象
sy = Style(wb)
# 获取日期对象
idate = IDate(2020)
# 设置行、列计数器
nrow = 0
ncol = 0
# 写入表标题
ws.merge_range(first_row=nrow,
first_col=ncol,
last_row=nrow,
last_col=ncol + 6,
data='2020年四级机构数据统计表',
cell_format=sy.title)
# 设置表标题行高为字体的两倍
ws.set_row(row=nrow, height=24)
nrow += 1
logging.debug('表标题写入完成')
# 写入说明性文字，数据统计的时间范围
ws.merge_range(first_row=nrow,
first_col=ncol,
last_row=nrow,
last_col=ncol + 6,
data=f'数据统计范围：2020-01-01 至 {idate.long_ri_qi()}',
cell_format=sy.explain)
nrow += 1
logging.debug('统计范围说明性文字写入完成')
# 写入表头
biao_ti = ['序号', '机构', '险种', '计划任务',
'累计保费', '时间进度\n达成率', '同比\n增长率']
for value in biao_ti:
ws.write(nrow, ncol, value, sy.string_bold_gray)
ncol += 1
nrow += 1
ncol = 0
logging.debug('表头写入完成')
# 设置机构名称列表
name_list = [
'百大国际', '春怡雅苑', '香榭丽园', '春之城', '东川', '宜良', '安宁',
'师宗', '宣威', '陆良', '沾益', '罗平', '会泽',
'丘北', '马关', '广南', '麻栗坡', '富宁',
'砚山', '祥云', '云龙', '宾川', '弥渡', '漾濞', '洱源',
'勐海', '勐腊', '施甸', '腾冲', '兰坪'
]
datas = []
# 对机构的年度整体保费进行统计，并在之后根据年保费进行排序
for name in name_list:
d = Tong_Ji(name=name, risk='整体')
datas.append((d.ming_cheng, d.nian_bao_fei()))
# 根据 年保费按倒序进行排序
datas.sort(key=lambda k: k[1], reverse=True)
# 使用排序后的顺序重新填充机构名称列表
name_list = []
for d in datas:
name_list.append(d[0])
# 在新的机构名称列表之前添加分公司
name_list.insert(0, '分公司整体')
logging.debug('机构排序完成')
# 设置险种名称列表
risk_list = ['车险', '非车险', '驾意险', '整体']
# 将数据写入表中
for name in name_list:
# 根据机构名称设置机构类型
if name == '分公司整体':
xu_hao = '' # 分公司不参与排名
wen_zi_temp = sy.string
shu_zi_temp = sy.number
bai_fen_bi_temp = sy.percent
elif xu_hao == '':
xu_hao = 1
wen_zi_temp = sy.string_gray
shu_zi_temp = sy.number_gray
bai_fen_bi_temp = sy.percent_gray
# 根据序号设置单元格是否增加底色
elif xu_hao % 2 == 0:
xu_hao += 1
wen_zi_temp = sy.string_gray
shu_zi_temp = sy.number_gray
bai_fen_bi_temp = sy.percent_gray
else:
xu_hao += 1
wen_zi_temp = sy.string
shu_zi_temp = sy.number
bai_fen_bi_temp = sy.percent
# 写入序号列，序号占4行
ws.merge_range(first_row=nrow,
first_col=ncol,
last_row=nrow + 3,
last_col=ncol,
data=xu_hao,
cell_format=wen_zi_temp)
# 写入 机构名称列，名称占4行
ws.merge_range(first_row=nrow,
first_col=ncol + 1,
last_row=nrow + 3,
last_col=ncol + 1,
data=name,
cell_format=wen_zi_temp)
# 根据险种名称 设置险种类型
for risk in risk_list:
if risk == '整体':
if xu_hao == '' or xu_hao % 2 == 0:
wen_zi_temp = sy.string_bold
shu_zi_temp = sy.number_bold
bai_fen_bi_temp = sy.percent_bold
else:
wen_zi_temp = sy.string_bold_gray
shu_zi_temp = sy.number_bold_gray
bai_fen_bi_temp = sy.percent_bold_gray
d = Tong_Ji(name=name, risk=risk)
ws.write(nrow, ncol + 2, d.xian_zhong, wen_zi_temp)
ws.write(nrow, ncol + 3, d.ren_wu(), wen_zi_temp)
ws.write(nrow, ncol + 4, d.nian_bao_fei(), shu_zi_temp)
ws.write(nrow, ncol + 5, d.shi_jian_da_cheng(), bai_fen_bi_temp)
ws.write(nrow, ncol + 6, d.nian_tong_bi(), bai_fen_bi_temp)
nrow += 1
logging.debug(f'{name}机构数据写入完成')
# 开始设置列宽
ncol = 0
ws.set_column(first_col=ncol, last_col=ncol, width=4)
ws.set_column(first_col=ncol + 1, last_col=ncol + 1, width=12)
ws.set_column(first_col=ncol + 2, last_col=ncol + 4, width=10)
ws.set_column(first_col=ncol + 5, last_col=ncol + 6, width=12)
logging.debug('列宽设置完成')
logging.debug('三级机构数据统计表写入完成')
logging.debug('-' * 60)
def save_key_page(sheet: worksheet,
data_results: Dict[str, Dict[AccessPoint, Tuple[int, int,
int]]],
**formats) -> None:
# Formats:
bold = formats["bold"]
merge_format = formats["merge_format"]
title = "KEYS"
example_title = "{ Combinations } - { Dates } - { Error Mode } - { Combination Mode }"
# Write the header:
sheet.merge_range('D4:H4', title, merge_format)
# Write the example page header:
sheet.write("D6", "Examples:", bold)
sheet.write("E6", example_title)
# Write the key:
# -- Left column:
sheet.write("D9", "In title:", bold)
sheet.write("D10", "{ Building }")
sheet.write("D11", "{ Floor }")
sheet.write("D12", "{ Data type }")
sheet.write("D13", "{ Sheet Type }")
# -- Middle column:
sheet.write("F9", "Example:", bold)
sheet.write("F10", "Home, SCAET")
sheet.write("F11", "1 5 6")
sheet.write("F12", "AP, Mix, ALL")
sheet.write("F13", "Matrix, Chart, Error, Table")
# -- Right column:
sheet.write("H9", "Meaning:", bold)
sheet.write("H10", "Which building the data belong to")
sheet.write("H11", "Which floor the data belong to")
sheet.write("H12", "the signal type used")
sheet.write("H13", "the information type ")
for floor, data_result in data_results.items():
if floor == "5":
number = 16
else:
number = 26
sheet.merge_range('D{}:H{}'.format(number - 1, number - 1),
"Home - {}".format(floor), merge_format)
sheet.write("D{}".format(str(number)), "Type", bold)
sheet.write("E{}".format(str(number)), "AP", bold)
sheet.write("F{}".format(str(number)), "Min", bold)
sheet.write("G{}".format(str(number)), "Mean", bold)
sheet.write("H{}".format(str(number)), "Max", bold)
for ap, data in data_result.items():
sheet.write("D{}".format(str(ap.num + number)), ap.type)
sheet.write("E{}".format(str(ap.num + number)), ap.id)
sheet.write("F{}".format(str(ap.num + number)), data[0])
sheet.write("G{}".format(str(ap.num + number)), data[1])
sheet.write("H{}".format(str(ap.num + number)), data[2])
def save(self, sheet: worksheet, chart_sheet: worksheet,
special_sheet: worksheet, book: workbook, **formats) -> None:
# Formats:
# bold = formats["bold"]
bold = book.add_format({'bold': True})
merge_format = formats["merge_format"]
# Set spacing:
horizontal_gap = 17
vertical_gap = 11
# # Write the header:
sheet.merge_range('A1:X1', self.title, merge_format)
chart_sheet.merge_range('A1:X1', self.title, merge_format)
special_sheet.merge_range('A1:X1', self.title, merge_format)
for index, table in self.__tables.items():
num_ap = self.__access_points[index]
# set charts
avg_chart = book.add_chart({'type': 'line'})
best_chart = book.add_chart({'type': 'line'})
gd_col = index * horizontal_gap + 2
jc_col = index * horizontal_gap + 5
ig_col = index * horizontal_gap + 8
mm_col = index * horizontal_gap + 11
gd_tables = table[0]
jc_tables = table[1]
mm_tables = table[2]
ig_tables = table[3]
for modes, gd_tuple in gd_tables.items():
table_chart = book.add_chart({'type': 'column'})
mse_chart = book.add_chart({'type': 'line'})
table_num = self.__specific_modes.index(modes)
mode_name = modes[0] + "-" + modes[1]
jc_tuple = jc_tables[modes]
mm_tuple = mm_tables[modes]
ig_tuple = ig_tables[modes]
jc_results = jc_tuple[0]
gd_results = gd_tuple[0]
mm_results = mm_tuple[0]
ig_results = ig_tuple[0]
gd_train_time = gd_tuple[2]
gd_test_time = gd_tuple[1]
jc_train_time = jc_tuple[2]
jc_test_time = jc_tuple[1]
ig_train_time = ig_tuple[2]
ig_test_time = ig_tuple[1]
mm_train_time = mm_tuple[2]
mm_test_time = mm_tuple[1]
# first Row
sheet.write(table_num * vertical_gap + 2,
index * horizontal_gap, "{}".format(mode_name),
bold)
sheet.write(table_num * vertical_gap + 2,
index * horizontal_gap + 1, "GD Approach", bold)
sheet.write(table_num * vertical_gap + 2,
index * horizontal_gap + 2,
round(gd_train_time, 4), bold)
sheet.write(table_num * vertical_gap + 2,
index * horizontal_gap + 3,
round(sum(gd_test_time), 4), bold)
sheet.write(table_num * vertical_gap + 2,
index * horizontal_gap + 4, "JC Method", bold)
sheet.write(table_num * vertical_gap + 2,
index * horizontal_gap + 5,
round(jc_train_time, 4), bold)
sheet.write(table_num * vertical_gap + 2,
index * horizontal_gap + 6,
round(sum(jc_test_time), 4), bold)
sheet.write(table_num * vertical_gap + 2,
index * horizontal_gap + 7, "InfoGain Method",
bold)
sheet.write(table_num * vertical_gap + 2,
index * horizontal_gap + 8,
round(ig_train_time, 4), bold)
sheet.write(table_num * vertical_gap + 2,
index * horizontal_gap + 9,
round(sum(ig_test_time), 4), bold)
sheet.write(table_num * vertical_gap + 2,
index * horizontal_gap + 10, "MaxMean Method",
bold)
sheet.write(table_num * vertical_gap + 2,
index * horizontal_gap + 11,
round(mm_train_time, 4), bold)
sheet.write(table_num * vertical_gap + 2,
index * horizontal_gap + 12,
round(sum(mm_test_time), 4), bold)
# Second Row
sheet.write(table_num * vertical_gap + 3,
index * horizontal_gap, "Num of AP", bold)
sheet.write(table_num * vertical_gap + 3,
index * horizontal_gap + 1, "Ap Set", bold)
sheet.write(table_num * vertical_gap + 3,
index * horizontal_gap + 2, "Accuracy", bold)
sheet.write(table_num * vertical_gap + 3,
index * horizontal_gap + 3, "Mean Error", bold)
sheet.write(table_num * vertical_gap + 3,
index * horizontal_gap + 4, "Ap Set", bold)
sheet.write(table_num * vertical_gap + 3,
index * horizontal_gap + 5, "Accuracy", bold)
sheet.write(table_num * vertical_gap + 3,
index * horizontal_gap + 6, "Mean Error", bold)
sheet.write(table_num * vertical_gap + 3,
index * horizontal_gap + 7, "Ap Set", bold)
sheet.write(table_num * vertical_gap + 3,
index * horizontal_gap + 8, "Accuracy", bold)
sheet.write(table_num * vertical_gap + 3,
index * horizontal_gap + 9, "Mean Error", bold)
sheet.write(table_num * vertical_gap + 3,
index * horizontal_gap + 10, "Ap Set", bold)
sheet.write(table_num * vertical_gap + 3,
index * horizontal_gap + 11, "Accuracy", bold)
sheet.write(table_num * vertical_gap + 3,
index * horizontal_gap + 12, "Mean Error", bold)
# Rest Row
length = num_ap
mm_values = list()
mm_keys = list()
mm_mse = list()
ig_keys = list()
ig_values = list()
ig_mse = list()
gd_keys = list()
gd_values = list()
gd_mse = list()
jc_keys = list()
jc_values = list()
jc_mse = list()
# if modes[0] != "SVM" and modes[0] == modes[1] and index != 2:
# length += 3
row_start = table_num * vertical_gap + 4
gd_row_end = table_num * vertical_gap + 1 + num_ap
jc_row_end = table_num * vertical_gap + 1 + length
for d in range(3, length + 1):
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap, "d={}".format(d), bold)
if d <= num_ap:
jc_result = jc_results[d - 3]
gd_result = gd_results[d - 3]
for key, value in gd_result.items():
gd_keys.append(key)
gd_values.append(value[0])
gd_mse.append(value[1])
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 1,
"{}".format(key), bold)
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 2,
round(value[0] * 100, 4), bold)
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 3,
round(value[1], 4), bold)
for key, value in jc_result.items():
jc_keys.append(key)
jc_values.append(value[0])
jc_mse.append(value[1])
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 4,
"{}".format(key), bold)
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 5,
round(value[0] * 100, 4), bold)
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 6,
round(value[1], 4), bold)
mm_result = mm_results[d - 3]
ig_result = ig_results[d - 3]
for key, value in ig_result.items():
ig_keys.append(key)
ig_values.append(value[0])
ig_mse.append(value[1])
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 7,
"{}".format(key), bold)
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 8,
round(value[0] * 100, 4), bold)
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 9,
round(value[1], 4), bold)
for key, value in mm_result.items():
mm_keys.append(key)
mm_values.append(value[0])
mm_mse.append(value[1])
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 10,
"{}".format(key), bold)
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 11,
round(value[0] * 100, 4), bold)
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 12,
round(value[1], 4), bold)
else:
jc_result = jc_results[d - 3]
for key, value in jc_result.items():
jc_keys.append(key)
jc_values.append(value[0])
jc_mse.append(value[1])
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 4,
"{}".format(key), bold)
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 5,
round(value[0] * 100, 4), bold)
sheet.write(table_num * vertical_gap + 3 + d - 2,
index * horizontal_gap + 6,
round(value[1], 4), bold)
# End Row
sheet.write(table_num * vertical_gap + 2 + length + 1,
index * horizontal_gap, "Best", bold)
sheet.write(table_num * vertical_gap + 2 + length,
index * horizontal_gap, "Average", bold)
best_gd_value = max(gd_values)
best_gd_key = gd_keys[gd_values.index(best_gd_value)]
best_gd_mse = min(gd_mse)
average_gd_value = sum(gd_values) / len(gd_values)
average_gd_mse = sum(gd_mse) / len(gd_mse)
# average_gd_time = sum(gd_test_time) / len(gd_test_time)
best_jc_value = max(jc_values)
best_jc_mse = min(jc_mse)
best_jc_key = jc_keys[jc_values.index(best_jc_value)]
average_jc_value = sum(jc_values) / len(jc_values)
average_jc_mse = sum(jc_mse) / len(jc_mse)
# average_jc_time = sum(jc_test_time) / len(jc_test_time)
best_ig_value = max(ig_values)
best_ig_mse = min(ig_mse)
best_ig_key = ig_keys[ig_values.index(best_ig_value)]
average_ig_value = sum(ig_values) / len(ig_values)
average_ig_mse = sum(ig_mse) / len(ig_mse)
best_mm_value = max(mm_values)
best_mm_mse = min(mm_mse)
best_mm_key = mm_keys[mm_values.index(best_mm_value)]
average_mm_value = sum(mm_values) / len(mm_values)
average_mm_mse = sum(mm_mse) / len(mm_mse)
sheet.write(table_num * vertical_gap + 2 + length + 1,
index * horizontal_gap + 1,
"{}".format(best_gd_key), bold)
sheet.write(table_num * vertical_gap + 2 + length + 1,
index * horizontal_gap + 2,
round(best_gd_value * 100, 4), bold)
sheet.write(table_num * vertical_gap + 2 + length + 1,
index * horizontal_gap + 3, round(best_gd_mse,
4), bold)
sheet.write(table_num * vertical_gap + 2 + length + 1,
index * horizontal_gap + 4,
"{}".format(best_jc_key), bold)
sheet.write(table_num * vertical_gap + 2 + length + 1,
index * horizontal_gap + 5,
round(best_jc_value * 100, 4), bold)
sheet.write(table_num * vertical_gap + 2 + length + 1,
index * horizontal_gap + 6, round(best_jc_mse,
4), bold)
sheet.write(table_num * vertical_gap + 2 + length + 1,
index * horizontal_gap + 7,
"{}".format(best_ig_key), bold)
sheet.write(table_num * vertical_gap + 2 + length + 1,
index * horizontal_gap + 8,
round(best_ig_value * 100, 4), bold)
sheet.write(table_num * vertical_gap + 2 + length + 1,
index * horizontal_gap + 9, round(best_ig_mse,
4), bold)
sheet.write(table_num * vertical_gap + 2 + length + 1,
index * horizontal_gap + 10,
"{}".format(best_mm_key), bold)
sheet.write(table_num * vertical_gap + 2 + length + 1,
index * horizontal_gap + 11,
round(best_mm_value * 100, 4), bold)
sheet.write(table_num * vertical_gap + 2 + length + 1,
index * horizontal_gap + 12, round(best_mm_mse,
4), bold)
# sheet.write(table_num * vertical_gap + 2 + length, index * horizontal_gap + 1,
# "Average Values", bold)
sheet.write(table_num * vertical_gap + 2 + length,
index * horizontal_gap + 2,
round(average_gd_value * 100, 4), bold)
sheet.write(table_num * vertical_gap + 2 + length,
index * horizontal_gap + 3,
round(average_gd_mse, 4), bold)
# sheet.write(table_num * vertical_gap + 2 + length, index * horizontal_gap + 5,
# "Average Values", bold)
sheet.write(table_num * vertical_gap + 2 + length,
index * horizontal_gap + 5,
round(average_jc_value * 100, 4), bold)
sheet.write(table_num * vertical_gap + 2 + length,
index * horizontal_gap + 6,
round(average_jc_mse, 4), bold)
# sheet.write(table_num * vertical_gap + 2 + length, index * horizontal_gap + 9,
# "Average Values", bold)
sheet.write(table_num * vertical_gap + 2 + length,
index * horizontal_gap + 8,
round(average_mm_value * 100, 4), bold)
sheet.write(table_num * vertical_gap + 2 + length,
index * horizontal_gap + 9,
round(average_mm_mse, 4), bold)
# sheet.write(table_num * vertical_gap + 2 + length, index * horizontal_gap + 12,
# "Average Values", bold)
sheet.write(table_num * vertical_gap + 2 + length,
index * horizontal_gap + 11,
round(average_ig_value * 100, 4), bold)
sheet.write(table_num * vertical_gap + 2 + length,
index * horizontal_gap + 12,
round(average_ig_mse, 4), bold)
table_chart.add_series({
'name': [
self.__tab_title, table_num * vertical_gap + 2,
index * horizontal_gap + 4
],
'categories': [
self.__tab_title, row_start, index * horizontal_gap,
jc_row_end, index * horizontal_gap
],
'values':
[self.__tab_title, row_start, jc_col, jc_row_end, jc_col],
})
table_chart.add_series({
'name': [
self.__tab_title, table_num * vertical_gap + 2,
index * horizontal_gap + 1
],
'categories': [
self.__tab_title, row_start, index * horizontal_gap,
gd_row_end, index * horizontal_gap
],
'values':
[self.__tab_title, row_start, gd_col, gd_row_end, gd_col],
})
table_chart.add_series({
'name': [
self.__tab_title, table_num * vertical_gap + 2,
index * horizontal_gap + 7
],
'categories': [
self.__tab_title, row_start, index * horizontal_gap,
gd_row_end, index * horizontal_gap
],
'values':
[self.__tab_title, row_start, ig_col, gd_row_end, ig_col],
})
table_chart.add_series({
'name': [
self.__tab_title, table_num * vertical_gap + 2,
index * horizontal_gap + 10
],
'categories': [
self.__tab_title, row_start, index * horizontal_gap,
gd_row_end, index * horizontal_gap
],
'values':
[self.__tab_title, row_start, mm_col, gd_row_end, mm_col],
})
table_chart.set_x_axis({'name': 'D Value'})
table_chart.set_y_axis({'name': 'Accuracy'})
table_chart.set_y_axis({'max': 100, 'min': 50})
table_chart.set_title({
'name':
'{} comparison with {}'.format(mode_name,
self.__data_type[index])
})
chart_sheet.insert_chart(table_num * 20 + 5, index * 8,
table_chart)
mse_chart.add_series({
'name': [
self.__tab_title, table_num * vertical_gap + 2,
index * horizontal_gap + 4
],
'categories': [
self.__tab_title, row_start, index * horizontal_gap,
jc_row_end, index * horizontal_gap
],
'values': [
self.__tab_title, row_start, jc_col + 1, jc_row_end,
jc_col + 1
],
})
mse_chart.add_series({
'name': [
self.__tab_title, table_num * vertical_gap + 2,
index * horizontal_gap + 1
],
'categories': [
self.__tab_title, row_start, index * horizontal_gap,
gd_row_end, index * horizontal_gap
],
'values': [
self.__tab_title, row_start, gd_col + 1, gd_row_end,
gd_col + 1
],
})
mse_chart.add_series({
'name': [
self.__tab_title, table_num * vertical_gap + 2,
index * horizontal_gap + 7
],
'categories': [
self.__tab_title, row_start, index * horizontal_gap,
gd_row_end, index * horizontal_gap
],
'values': [
self.__tab_title, row_start, ig_col + 1, gd_row_end,
ig_col + 1
],
})
mse_chart.add_series({
'name': [
self.__tab_title, table_num * vertical_gap + 2,
index * horizontal_gap + 10
],
'categories': [
self.__tab_title, row_start, index * horizontal_gap,
gd_row_end, index * horizontal_gap
],
'values': [
self.__tab_title, row_start, mm_col + 1, gd_row_end,
mm_col + 1
],
})
mse_chart.set_x_axis({'name': 'D Value'})
mse_chart.set_y_axis({'name': 'Mean Error(m)'})
mse_chart.set_title({
'name':
'{} comparison with {}'.format(mode_name,
self.__data_type[index])
})
special_sheet.insert_chart(table_num * 20 + 5, index * 8,
mse_chart)
def writeWorksheet(ws: xlsxwriter.worksheet,
knowledgeArea: KnowledgeArea) -> None:
"""
Iterate through the global collection of KnowledgeAreas
and write them to a worksheet in an Excel workbook.
:param ws: Excel Worksheet Object
:param knowledgeArea: KnowledgeArea Object
"""
global courses
global learningObjectives
row = 0
col = 0
ws.write(row, col, 'CourseId')
col += 1
ws.write(row, col, 'CourseTitle - As Extracted')
col += 1
ws.write(row, col, 'learningObjectiveId')
col += 1
ws.write(row, col, 'learningObjective - As Extracted')
col += 1
ws.write(row, col, 'learningObjective - As Revised')
row += 1
col = 0
knowledgeAreaId = knowledgeArea.getId()
for course in courses:
#print("Course {}, {} ".format(course.getTitle(), course.getKnowledgeAreaId()))
if str(course.getKnowledgeAreaId()) == str(knowledgeAreaId):
courseId = course.getId()
if course.getDescription() == "":
ws.write(row, col, courseId)
col += 1
ws.write(row, col, course.getTitle())
row += 1
col = 0
# write another row for the course to allow for 2 learning outcomes
ws.write(row, col, courseId)
col += 1
ws.write(row, col, course.getTitle())
row += 1
col = 0
continue
else:
for learningObjective in learningObjectives:
#print("LearningObjective: {}, {} ".format(learningObjective.getText(), learningObjective.getCourseId()))
if str(learningObjective.getCourseId()) == str(courseId):
ws.write(row, col, courseId)
col += 1
ws.write(row, col, course.getTitle())
col += 1
ws.write(row, col, learningObjective.getId())
col += 1
ws.write(row, col, learningObjective.getText())
row += 1
col = 0
continue
def writeCourseRows(ws: xlsxwriter.worksheet,
knowledgeArea: KnowledgeArea) -> None:
"""
Write the rows in the courses worksheet for all courses under
the knowledgearea.
:param ws: Excel Worksheet Object
:param knowledgeArea: KnowledgeArea
"""
global courses
global coursesWsRow
col = 0
knowledgeAreaId = knowledgeArea.getId()
for course in courses:
if str(course.getKnowledgeAreaId()) == str(knowledgeAreaId):
ws.write(coursesWsRow, col, knowledgeArea.getText())
col += 1
courseId = course.getId()
ws.write(coursesWsRow, col, courseId)
col += 1
ws.write(coursesWsRow, col, course.getTitle())
col += 2
ws.write(coursesWsRow, col, course.getDescription())
col += 2
ws.write(coursesWsRow, col, course.getInstructor())
col += 2
ws.write(coursesWsRow, col, course.getFee())
coursesWsRow += 1
col = 0
def writeSessionRows(ws: xlsxwriter.worksheet) -> None:
"""
Write the rows in the sessions worksheet for all sessions.
:param ws: Excel Worksheet Object
:param session: Session
"""
global sessions
global sessionsWsRow
col = 0
for session in sessions:
sessionId = session.getId()
ws.write(sessionsWsRow, col, sessionId)
col += 1
courseId = session.getCourseId()
ws.write(sessionsWsRow, col, courseId)
col += 1
ws.write(sessionsWsRow, col, session.getDOW())
col += 2
ws.write(sessionsWsRow, col, session.getSessionNumber())
col += 1
ws.write(sessionsWsRow, col, session.getSessionDate())
col += 2
ws.write(sessionsWsRow, col, session.getSessionTimeStart())
col += 2
ws.write(sessionsWsRow, col, session.getSessionTimeEnd())
sessionsWsRow += 1
col = 0
