def create_style(self):
side = Side(border_style=self.border_type, color=utils.colors.black)
border = Border(left=side, right=side, top=side, bottom=side)
return NamedStyle(font=Font(name=self.font,
size=self.font_size,
color=Color(self.font_color),
bold=self.bold,
underline=self.underline),
fill=PatternFill(patternType=self.fill_pattern_type,
fgColor=self.bg_color),
alignment=Alignment(
horizontal=self.horizontal_alignment,
vertical=self.vertical_alignment,
wrap_text=self.wrap_text,
shrink_to_fit=self.shrink_to_fit,
indent=self.indent),
border=border,
number_format=self.number_format,
protection=Protection(locked=self.protection))
def test_protection(self):
cell = self.worksheet.cell('A1')
cell.protection = Protection(locked=True, hidden=True)
_ = cell.style_id
w = StyleWriter(self.workbook)
w._write_cell_styles()
xml = tostring(w._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<cellXfs count="2">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
<xf applyProtection="1" borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0">
<protection hidden="1" locked="1"/>
</xf>
</cellXfs>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def write_only():
wb = Workbook()
ws = wb.create_sheet()
ws.title = "首页列表"
c = ws['A1']
c.style = Style(font=Font(name='Courrier', size=36)
, fill=PatternFill(fill_type=None, start_color='FFFFFFFF',
end_color='FF000000')
, protection=Protection(locked='inherit', hidden='inherit')
, alignment=Alignment(horizontal='general', vertical='bottom',
shrink_to_fit=True)
, border=Border(left=Side(border_style=None, color='FF000000')))
c.value = '姓名'
# cell = WriteOnlyCell(ws, value="hello world")
# cell.style = Style(font=Font(name='Courrier', size=36))
# cell.comment = Comment(text="A comment", author="Author's Name")
# ws.header_footer.center_header.text = 'My Excel Page'
# ws.header_footer.center_header.font_size = 14
# ws.header_footer.center_header.font_name = "Tahoma,Bold"
# ws.header_footer.center_header.font_color = "CC3366"
wb.save(filename='empty_book.xlsx')
def get_cell(self,
value,
wrap=False,
shrink_to_fit=False,
bold=False,
unlock=False) -> WriteOnlyCell:
cell = WriteOnlyCell(self.work_sheet, value=value)
if bold:
cell.font = self.font_regular_bold
else:
cell.font = self.font_regular
if wrap:
cell.alignment = self.alignment_wrap_text
elif shrink_to_fit:
cell.alignment = self.alignment_shrink_to_fit
if unlock:
cell.protection = Protection(locked=False)
return cell
def _apply_xlsm_formats(self, sheet, cell):
"""Apply column-specific, macro-enabled spreadsheet formats."""
# Create styles
normal = Font(name='Arial')
bold = copy(normal)
bold.bold = True
# Acquire indices and headers
header = cell.value
col_id = cell.column
col = sheet.column_dimensions[col_id]
col.font = normal
col.width = self._get_column_width(header)
# Lock only the headers
col.protection = Protection(locked=False)
cell.font = normal # Required for locking (openpyxl bug?)
# Format the required columns
if header in REQUIRED:
cell.font = bold
# Format the columns with limited options
try:
options = '"{}"'.format(','.join(LIMITED[header]))
valid = DataValidation(type="list", formula1=options)
valid.error = "Invalid {}.".format(header)
sheet.add_data_validation(valid)
valid.add(self._get_column_body(col_id))
col.width = self._get_column_width(LIMITED[header])
except KeyError:
pass
# Format the date column
if header == 'SEQ_DATE':
valid_date = DataValidation(type="date")
valid_date.error = "Invalid date."
sheet.add_data_validation(valid_date)
valid_date.add(self._get_column_body(col_id))
def _build_global_sheet_(wb: openpyxl.Workbook,
ia_config: ia.iaconfiguration._IaConfiguration):
"""
_build_global_sheet(wb, ia_fonfig) -> Worksheet
Constructs the worksheet into which global ratings will be entered
:param wb: The workbook to which to append the worksheet
:param ia_config: the caastools.parsing.ia.iaconfiguartion._IaConfiguration object specifying the globals to be scored
:return: openpyxl.workbook.workbook.Worksheet
"""
sheet = wb.create_sheet(title="Global Ratings", index=1)
sheet.append(("Global", "Rating"))
min_width = 18.0
sheet.protection.enable()
sheet.protection.password = "******"
# insert a row for each global that needs to be scored
for gp in ia_config.global_properties: # type: ia.iaconfiguration._GlobalProperty
sheet.append((gp.display_name, ))
# apply some styling to the spreadhseet
col = sheet.column_dimensions[get_column_letter(1)]
old_align = Alignment(horizontal=col.alignment.horizontal,
vertical=col.alignment.vertical)
col.alignment = Alignment(horizontal="general",
vertical="bottom",
shrinkToFit=True)
if col.width < min_width:
col.width = min_width
col.alignment = old_align
# Apply some formatting, and unlock data entry cells for modification
for i, row in enumerate(sheet.rows, 1):
for cell in row:
cell.border = BORDER
sheet[f"B{i}"].protection = Protection(locked=False, hidden=False)
return sheet
def get_style():
font = Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
fill = PatternFill(fill_type=None,
start_color='FFFFFF',
end_color='FF000000')
border = Border(left=Side(border_style='thin', color='000000'),
right=Side(border_style='thin', color='000000'),
top=Side(border_style='thin', color='000000'),
bottom=Side(border_style='thin', color='000000'),
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'))
alignment = Alignment(horizontal='center',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
number_format = 'General'
protection = Protection(locked=True, hidden=False)
return {
'font': font,
'fill': fill,
'border': border,
'alignment': alignment,
'number_format': number_format,
'protection': protection,
}
def create_file(query):
wb = Workbook(write_only=True)
stream = None
ws = wb.create_sheet()
# HEADER
ws.append(create_header(query))
row = ws.row_dimensions[1]
p = Protection(locked=True, hidden=False)
c = Color(indexed=22)
f = PatternFill(fill_type='solid', start_color=c, end_color=c)
row.fill = f
row.protection = p
# BODY
body = get_data_from_query_as_lists(query)
for data in body:
ws.append(data)
with NamedTemporaryFile() as tmp:
wb.save(tmp.name)
tmp.seek(0)
stream = tmp.read()
return stream
def downloadScoresheet_xls(request):
courselist = {}
if request.method == 'GET':
if request.session.has_key('courselist'):
courselist = request.session['courselist']
filename = courselist[0][
'MYCOURSEID'] + courselist[0]['MYASESSIONID'].replace(
"/", "_") + courselist[0]['MYSEMESTERID'] + '.xls'
response = HttpResponse(content_type='application/ms-excel')
response[
'Content-Disposition'] = "attachment; filename=" + filename
workbook = openpyxl.Workbook()
worksheet = workbook.active
worksheet.protection.sheet = True
worksheet.protection.enable()
bold_font = Font(bold=True)
big_red_text = Font(bold=True, color="ff0000", size=14)
center_aligned_text = Alignment(horizontal="center")
workbook.security.set_workbook_password(
settings.WORKBOOKHASHED_PASSWORD, already_hashed=False)
worksheet.protection.password = settings.CIPHER_PASS
if request.session.has_key('params'):
params = request.session['params']
else:
print('params was NOT passed in session')
worksheet.title = 'SCORESSHEET'
col_start = 0
row_start = 7
SECRETKEY = {
"CCODE": settings.CIPHER_PASS,
"TOTAL": str(len(courselist)),
"EMAIL": request.user.email,
"CID": params['longerreporttype'],
"AsessionId": courselist[0]['MYASESSIONID'],
"SemesterId": courselist[0]['MYSEMESTERID'],
"LevelToDo": courselist[0]['MYLEVELTODO'],
"CourseState": courselist[0]['MYCOURSESTATE'],
"CourseUnit": courselist[0]['MYCOURSEUNIT'],
"CourseNature": courselist[0]['MYCOURSENATURE'],
"AsetId": courselist[0]['MYASETID'],
"CourseId": courselist[0]['MYCOURSEID'],
}
SECRETKEY_STR = json.dumps(SECRETKEY)
SECRETKEY_STR = SECRETKEY_STR.rjust(320, 'X')
cipher = AES.new(
settings.CIPHER_PASS.rjust(32, 'X'),
AES.MODE_ECB) # never use ECB in strong systems obviously
SECRETKEYciphertext = base64.b64encode(
cipher.encrypt(SECRETKEY_STR))
print(SECRETKEYciphertext)
decoded = cipher.decrypt(base64.b64decode(SECRETKEYciphertext))
worksheet.cell(1, 1).value = SECRETKEYciphertext
worksheet.cell(1, 1).font = Font(color="ffffff", size=2)
worksheet.column_dimensions['B'].width = 20
worksheet.column_dimensions['C'].width = 20
worksheet.column_dimensions['D'].width = 20
worksheet.column_dimensions['E'].width = 20
now = time.strftime("%x")
worksheet.cell(1, 3).value = now
worksheet.cell(1, 3).font = Font(color="ffffff", size=2)
worksheet.cell(3, 2).value = 'THE POLYTECHNIC IBADAN'
worksheet.merge_cells('B3:E3')
worksheet["B3"].font = big_red_text
worksheet["B3"].alignment = center_aligned_text
worksheet.cell(4, 2).value = 'INTERNAL RESULT DOCUMENT'
worksheet.merge_cells('B4:E4')
worksheet["B4"].font = big_red_text
worksheet["B4"].alignment = center_aligned_text
worksheet.cell(5, 2).value = 'COURSE CODE : ' + courselist[0][
'MYCOURSEID'] + ' SESSION : ' + courselist[0][
'MYASESSIONID'] + ' SEMESTER : ' + courselist[0][
'MYSEMESTERID']
worksheet.merge_cells('B5:E5')
worksheet["B5"].font = big_red_text
worksheet["B5"].alignment = center_aligned_text
for index, row in enumerate(courselist):
worksheet.cell(index + row_start,
col_start + 1).value = index + 1
worksheet.cell(index + row_start,
col_start + 1).alignment = center_aligned_text
worksheet.cell(index + row_start,
col_start + 2).font = bold_font
worksheet.cell(index + row_start,
col_start + 2).value = row['MYSTUDENTID']
worksheet.cell(index + row_start,
col_start + 3).value = row['MYSURNAME']
worksheet.cell(index + row_start,
col_start + 4).value = row['MYMIDDLENAME']
worksheet.cell(index + row_start,
col_start + 5).value = row['MYFIRSTNAME']
worksheet.cell(index + row_start,
col_start + 8).value = row['MYCOURSEID']
worksheet.cell(index + row_start,
col_start + 8).font = Font(color="ffffff",
size=2)
worksheet.cell(index + row_start, col_start +
9).value = row['MYSCORESHEETCLASSID']
worksheet.cell(index + row_start,
col_start + 9).font = Font(color="ffffff",
size=2)
worksheet.cell(index + row_start,
col_start + 10).value = row['MYCOURSESTATE']
worksheet.cell(index + row_start,
col_start + 10).font = Font(color="ffffff",
size=2)
worksheet.cell(index + row_start,
col_start + 11).value = row['MYCOURSENATURE']
worksheet.cell(index + row_start,
col_start + 11).font = Font(color="ffffff",
size=2)
modi = 'False'
if row['MYMODIFIED']:
modi = 'True'
worksheet.cell(index + row_start, col_start + 12).value = modi
worksheet.cell(index + row_start,
col_start + 12).font = Font(color="ffffff",
size=2)
worksheet.cell(index + row_start,
col_start + 13).value = row['MYREADONLY']
worksheet.cell(index + row_start,
col_start + 13).font = Font(color="ffffff",
size=2)
worksheet.cell(index + row_start,
col_start + 6).value = row['MYSCORE']
worksheet.cell(index + row_start,
col_start + 6).font = bold_font
worksheet.cell(index + row_start,
col_start + 6).alignment = center_aligned_text
worksheet.cell(index + row_start, col_start +
6).protection = Protection(locked=False)
worksheet.cell(len(courselist) + row_start, 1).value = 'END'
worksheet.cell(len(courselist) + row_start,
1).font = Font(color="ffffff", size=2)
worksheet.cell(len(courselist) + row_start,
1).alignment = center_aligned_text
workbook.save(response)
return response
else:
print('Nothing was passed in session')
else:
print('This is a POST message')
return render(request, 'GradeManager/displayCourse_view.html',
{'courselist': courselist})
def parse_cell_xfs(self):
"""Read styles from the shared style table"""
cell_xfs = self.root.find('{%s}cellXfs' % SHEET_MAIN_NS)
styles_list = self.style_prop['list']
if cell_xfs is None: # can happen on bad OOXML writers (e.g. Gnumeric)
return
builtin_formats = NumberFormat._BUILTIN_FORMATS
cell_xfs_nodes = safe_iterator(cell_xfs, '{%s}xf' % SHEET_MAIN_NS)
for index, cell_xfs_node in enumerate(cell_xfs_nodes):
_style = {}
number_format_id = int(cell_xfs_node.get('numFmtId'))
if number_format_id < 164:
format_code = builtin_formats.get(number_format_id, 'General')
else:
fmt_code = self.custom_num_formats.get(number_format_id)
if fmt_code is not None:
format_code = fmt_code
else:
raise MissingNumberFormat('%s' % number_format_id)
_style['number_format'] = NumberFormat(format_code=format_code)
if bool(cell_xfs_node.get('applyAlignment')):
alignment = {}
al = cell_xfs_node.find('{%s}alignment' % SHEET_MAIN_NS)
if al is not None:
for key in ('horizontal', 'vertical', 'indent'):
_value = al.get(key)
if _value is not None:
alignment[key] = _value
alignment['wrap_text'] = bool(al.get('wrapText'))
alignment['shrink_to_fit'] = bool(al.get('shrinkToFit'))
text_rotation = al.get('textRotation')
if text_rotation is not None:
alignment['text_rotation'] = int(text_rotation)
# ignore justifyLastLine option when horizontal = distributed
_style['alignment'] = Alignment(**alignment)
if bool(cell_xfs_node.get('applyFont')):
_style['font'] = self.font_list[int(
cell_xfs_node.get('fontId'))].copy()
if bool(cell_xfs_node.get('applyFill')):
_style['fill'] = self.fill_list[int(
cell_xfs_node.get('fillId'))].copy()
if bool(cell_xfs_node.get('applyBorder')):
_style['border'] = self.border_list[int(
cell_xfs_node.get('borderId'))].copy()
if bool(cell_xfs_node.get('applyProtection')):
protection = {}
prot = cell_xfs_node.find('{%s}protection' % SHEET_MAIN_NS)
# Ignore if there are no protection sub-nodes
if prot is not None:
protection['locked'] = bool(prot.get('locked'))
protection['hidden'] = bool(prot.get('hidden'))
_style['protection'] = Protection(**protection)
self.style_prop['table'][index] = styles_list.add(Style(**_style))
def discipline_scores_to_excel(course_id):
# определяем стили
font_main, font_bold, font_bold_s, font_calibri, font_arial, fill, border, align_center, align_center2, align_left, align_right, header_font = get_styles()
number_format = 'General'
protection = Protection(locked=True,
hidden=False)
# объект
wb = Workbook()
# активный лист
ws = wb.active
# название страницы
# ws = wb.create_sheet('первая страница', 0)
ws.title = 'первая страница'
# текущее время
p = datetime.datetime.now()
# данные для строк
course = Course.objects.select_related('group','discipline_detail','lecturer').get(pk=course_id)
semester = course.discipline_detail.semester
group = course.group
checkpoints = course.coursemaxpoints_set.all()
if not course or not semester or not group or not checkpoints:
return None
edu_period = get_object_or_404(EduPeriod, active=True)
_row = 13
_column = 4
k = 1
z = 0
for gl in group.grouplist_set.select_related('student', 'group').filter(active=True).order_by('student__FIO'):
ws.cell(row=_row, column=1).value = str(k)
ws.cell(row=_row, column=2).value = gl.student.FIO
ws.cell(row=_row, column=3).value = gl.student.student_id
totalpoints = 0
_column = 4
for points in gl.student.brspoints_set.filter(course__id=course_id):
ws.cell(row=_row, column=_column).value = str(points.points).replace('.', ',')
totalpoints += points.points
_column += 1
_row += 1
k += 1
z += 1
zk = z + 11
zp = z + 14
if course.lecturer is None:
fio = 'отсутствует'
else:
fio = course.lecturer.FIO
ws.cell(row=1, column=1).value = 'ФГАОУ ВО «Северо-Восточный федеральный университет им.М.К.Аммосова'
ws.cell(row=2, column=1).value = 'Институт математики и информатики'
ws.cell(row=4, column=1).value = 'Контрольный лист текущей и промежуточной аттестации'
ws.cell(row=5, column=1).value = str(edu_period.begin_year) + '-' + str(edu_period.end_year) + ' учебный год'
ws.cell(row=6, column=2).value = 'Семестр'
ws.cell(row=6, column=3).value = semester.name
ws.cell(row=7, column=2).value = 'Курс'
ws.cell(row=7, column=3).value = group.year
ws.cell(row=7, column=5).value = 'Группа'
ws.cell(row=7, column=6).value = group.name
ws.cell(row=8, column=2).value = 'Дисциплина:'
ws.cell(row=8, column=3).value = course.discipline_detail.discipline.name
ws.cell(row=9, column=2).value = 'Преподаватель:'
ws.cell(row=9, column=3).value = fio
ws.cell(row=11, column=1).value = '№'
ws.cell(row=11, column=2).value = 'Фамилия, имя, отчество'
ws.cell(row=11, column=3).value = '№ зачетной книжки'
ws.cell(row=11, column=4).value = 'Сумма баллов за текущую работу'
ws.cell(row=11, column=6).value = 'Рубежный срез'
ws.cell(row=11, column=7).value = 'Баллы за экзамен(бонусные баллы)'
ws.cell(row=11, column=8).value = 'Всего баллов'
ws.cell(row=11, column=9).value = 'Оценка прописью'
ws.cell(row=11, column=10).value = 'Буквенный эквивалент'
ws.cell(row=11, column=11).value = 'Подпись преподавателя'
ws.cell(row=12, column=4).value = '1 контр. срез (max=' + str(int(checkpoints.get(checkpoint__id=4).max_point)) + ')'
ws.cell(row=12, column=5).value = '2 контр. срез (max=' + str(int(checkpoints.get(checkpoint__id=5).max_point)) + ')'
# объединение ячеек
ws.merge_cells('A1:K1')
ws.merge_cells('A2:K2')
ws.merge_cells('A4:K4')
ws.merge_cells('A5:K5')
ws.merge_cells('A11:A12')
ws.merge_cells('B11:B12')
ws.merge_cells('C11:C12')
ws.merge_cells('D11:E11')
ws.merge_cells('F11:F12')
ws.merge_cells('G11:G12')
ws.merge_cells('H11:H12')
ws.merge_cells('I11:I12')
ws.merge_cells('J11:J12')
ws.merge_cells('K11:K12')
# шрифты
font_9 = Font(name='Times New Roman',
size=9,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
ws['A11'].font = font_9
ws['B11'].font = font_9
ws['C11'].font = font_9
ws['D11'].font = font_9
ws['D12'].font = font_9
ws['E12'].font = font_9
ws['F11'].font = font_9
ws['G11'].font = font_9
ws['H11'].font = font_9
ws['I11'].font = font_9
ws['J11'].font = font_9
ws['K11'].font = font_9
ws['A4'].font = font_bold
ws['A5'].font = font_bold
# увеличиваем все строки по высоте
max_row = ws.max_row
i = 1
while i <= max_row:
rd = ws.row_dimensions[i]
rd.height = 16
i += 1
# вручную устанавливаем высоту первой строки
rd = ws.row_dimensions[11]
rd.height = 28
rd = ws.row_dimensions[12]
rd.height = 36
# сетка
for cellObj in ws['A11:K' + str(zk+1)]:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].border = border
# выравнивание
ws['B6'].alignment = align_right
ws['B7'].alignment = align_right
ws['B8'].alignment = align_right
ws['B9'].alignment = align_right
ws['E7'].alignment = align_right
ws['C6'].alignment = align_center
ws['C7'].alignment = align_center
for cellObj in ws['A13:A' + str(zk+1)]:
for cell in cellObj:
cell.alignment = align_center
for cellObj in ws['C13:K' + str(zk+1)]:
for cell in cellObj:
cell.alignment = align_center
for cellObj in ws['A1:K5']:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].alignment = align_center
for cellObj in ws['A11:K12']:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].alignment = align_center2
# перетягивание ячеек
dims = {}
for cellObj in ws['A11:A' + str(zk)]:
for cell in cellObj:
if cell.value:
dims[cell.column] = max((dims.get(cell.column, 0), len(cell.value)))
for col, value in dims.items():
# value * коэфициент
ws.column_dimensions[col].width = value * 3
dims = {}
for cellObj in ws['B11:B' + str(zk)]:
for cell in cellObj:
if cell.value:
dims[cell.column] = max((dims.get(cell.column, 0), len(cell.value)))
for col, value in dims.items():
# value * коэфициент
ws.column_dimensions[col].width = value * 1.2
return wb
def lock_markfile_2(self, next_exam):
lock_range = {
'chi': {
'ut1': [15, 1],
'daily1': [15, 9],
'exam1': [16, 4],
'ut2': [15, 1],
'daily2': [15, 9],
'exam2': [16, 4],
},
'eng': {
'ut1': [8, 6],
'daily1': [0, 0],
'exam1': [9, 12],
'ut2': [8, 6],
'daily2': [0, 0],
'exam2': [9, 12],
},
'oth': {
'ut1': [11, 1],
'daily1': [0, 0],
'exam1': [10, 2],
'ut2': [11, 1],
'daily2': [0, 0],
'exam2': [10, 2],
},
}
markfile_save_folder = os.path.join(self.assessment_root_folder,
'markfile_src', next_exam,
'rename')
# from exam_type (= exam_folder)
# also load drop from master run
# unlock websams and update subject for S5
# set path 2021exam\ut1\(subject)
# load master exam file
current_directory = pathlib.Path(markfile_save_folder)
for subject_dir in current_directory.iterdir():
# only loop into sub-folder of merge
if subject_dir.is_dir():
# produce a list of files to loop using os.scandir
with os.scandir(str(subject_dir)) as listOfEntries:
# loop all files in each subject folder
for entry in listOfEntries:
# check if files vs directory
if entry.is_file():
# only open xlsx file
if entry.name[-4:] == 'xlsx':
n = len(self.exam_year + next_exam)
exam_type = entry.name[0:n]
classlevel = entry.name[n:(n + 2)]
base_markfile = entry.name[n:]
base_markfile2 = entry.name[n:-5]
print(base_markfile, base_markfile2)
# subject = (base_markfile[4:-5] if base_markfile[2] == 'x' else base_markfile[3:-5])
if base_markfile[2] in ['x', 'g']:
subject = base_markfile[4:-5]
else:
subject = base_markfile[3:-5]
print(markfile_save_folder)
markfile_wb = openpyxl.load_workbook(
filename=entry.path)
# check drop students for nss elective
# if base_markfile2 in drop_file_dict.keys():
# for row in range(5, 49):
# regno = markfile_wb['websams'].cell(row=row, column=1).value
# if regno == drop_file_dict[base_markfile2]['regno']:
# print(drop_file_dict[base_markfile2]['chname'])
# markfile_wb['websams'].cell(row=row, column=45).value = ''
# need to unlock and set active ws
# need to lock past exam ws
# loop all sheets in workbook
# to lock cells
# unlock files for current exam
locked_sheet_state = {
'ut1': True,
'daily1': False,
'exam1': False,
'ut2': False,
'daily2': False,
'exam2': False,
}
for ws in markfile_wb.sheetnames:
if ws in locked_sheet_state.keys():
# set lock_range cells first
if subject.lower() in ['chi', 'eng']:
col_start = lock_range[
subject.lower()][ws][0]
repeat = lock_range[
subject.lower()][ws][1]
else:
col_start = lock_range['oth'][ws][
0]
repeat = lock_range['oth'][ws][1]
# loop the lock_range range
# print(sheet, colStart, repeat)
sheet_state = locked_sheet_state[ws]
for r2 in range(5, 49):
for c2 in range(
col_start,
col_start + repeat):
markfile_wb[ws].cell(
column=c2, row=r2
).protection = Protection(
locked=sheet_state,
hidden=False)
# else:
# print('undefined sheet!')
markfile_wb[ws].protection.password = '******'
markfile_wb[ws].protection.sheet = True
markfile_wb[ws].protection.enable()
markfile_wb.save(entry.path)
markfile_wb.close()
for i_crse, crse in df_prg_courses.iterrows():
df_prg_crse_clos = df_clo.loc[(df_clo['course_id'] == crse['course_id'])]
for i, clo in df_prg_crse_clos.iterrows():
ws_align.cell(row=j_align, column=1).value = plo['plo_nbr']
ws_align.cell(row=j_align, column=2).value = clo['clo_nbr']
ws_align.cell(row=j_align, column=3).value = plo['program_code']
ws_align.cell(row=j_align, column=4).value = crse['course_code']
ws_align.cell(row=j_align, column=5).alignment = Alignment(wrapText=True)
ws_align.cell(row=j_align, column=5).value = plo['plo_text']
ws_align.cell(row=j_align, column=6).alignment = Alignment(wrapText=True)
ws_align.cell(row=j_align, column=6).value = clo['clo_text']
ws_align.cell(row=j_align, column=7).value = plo['Clear and usable']
ws_align.cell(row=j_align, column=8).value = clo['Clear and usable']
ws_align.cell(row=j_align, column=9).protection = Protection(locked=False)
dv_match.add(ws_align.cell(row=j_align, column=9))
j_align += 1
print('Align Done: {}'.format(j_align))
intTime1 = datetime.now()
print('Time Taken:\t{}'.format(intTime1 - startTime))
intTime2 = intTime1
# Adjust row heights and column widths
ws = wb["Program Summary"]
ws.column_dimensions['B'].width = 60
for i_row, row in enumerate(ws):
if i_row + 1 > 3 and not i_row + 1 >= 16:
# adjust height of row
def main(filename):
init()
rt = openxml(filename)
_, codelists = list(rt)
versions = {}
final_conflict_array = []
if not os.path.isdir('excel_raktai_konceptai'):
os.mkdir('excel_raktai_konceptai')
for codelist in codelists: # kiekvienai rakto versijai sukuriamas atskiras codelistas
# visus codelistus su tuo pačiu id sugrupuoja į vieną masyvą
id = codelist.attrib['id']
if id in versions:
versions[id].append(codelist)
else:
versions[id] = [codelist]
for id in versions:
new_codelist, conflicts, name_conflicts = parse_xml_codelist(
versions[id], id)
# atsikrato visų versijų, išskyrus pirmą sąraše
for version in versions[id][1:]:
codelists.remove(version)
# nustato pirmos versijos atributus (kad likusi versija tikrai būtų pirmoji)
versions[id][0].attrib['version'] = '1.0'
versions[id][0].attrib['urn'] = remove_version_str(
versions[id][0].attrib['urn']) # nustato versiją į 1.0
versions[id][0].attrib['isFinal'] = 'true'
# iš likusios versijos codelisto išmeta VISUS kodus
for code in reversed(versions[id][0]):
versions[id][0].remove(code)
# prie jau tuščios versijos prideda naujus ir sutvarkytus kodus
for code in new_codelist:
versions[id][0].append(remove_version_et(code))
# sukuriamas parent, child masyvas ir pridedamas prie final_conflict_array masyvo
parent = versions[id][0]
if len(conflicts) > 0:
final_conflict_array.append((parent, conflicts, name_conflicts))
for parent, conflicts, name_conflicts in final_conflict_array:
wb = Workbook()
wb.create_sheet('Pavadinimai')
wb.create_sheet('XML')
wb.remove(wb['Sheet'])
ws2 = wb['Pavadinimai']
ws_xml = wb['XML']
id = ''
try:
id = parent.attrib['id']
except:
id = parent
for conflict_id in conflicts:
ws_xml.append([conflict_id])
key_values = []
key_lt = []
key_en = []
for elem in reversed(
conflicts[conflict_id]
): # elem = KS_APREPTIS_UVR(1.0).F. reversed tam, kad pirmiausia dėtų naujausią elementą
text = print_xml(elem)
elem_id = elem.get('urn').split('(')[-1]
elem_id = elem_id.split(')')[0]
for description in elem:
att = description.attrib
record = description.text
for lang in att: # att dydis visada yra 1
language = att[lang]
if language.lower() == 'en':
key_en.append(elem_id)
key_en.append(record)
text = text.replace(record, '###en###')
elif language.lower() == 'lt':
key_lt.append(elem_id)
key_lt.append(record)
text = text.replace(record, '###lt###')
if len(key_values) != 0:
if len(key_values[0]) < len(text):
key_values = []
key_values.append(text)
else:
key_values.append(text)
ws2.append([conflict_id.split('.')[-1]])
ws2.append(key_lt)
ws2.append(key_en)
ws_xml.append(key_values)
# sutvarkyti pavadinimų konfliktams
if name_conflicts['lt'] and name_conflicts['en']:
ws2.append(['pavadinimas'])
ws_xml.append([id + '.pavadinimas'])
record_en = name_conflicts['en'][0][0].text
record_lt = name_conflicts['lt'][0][0].text
text = print_xml(
name_conflicts['en'][0][0]) + '######' + print_xml(
name_conflicts['lt'][0][0])
text = text.replace(record_en, '###en###')
text = text.replace(record_lt, '###lt###')
ws_xml.append([text])
key_lt = []
for version, key in name_conflicts['lt']:
key_lt.append(key)
key_lt.append(version.text)
key_en = []
for version, key in name_conflicts['en']:
key_en.append(key)
key_en.append(version.text)
ws2.append(key_lt)
ws2.append(key_en)
elif name_conflicts['lt'] and not name_conflicts['en']:
ws2.append(['pavadinimas'])
ws_xml.append([id + '.pavadinimas'])
record_lt = name_conflicts['lt'][0][0].text
text = print_xml(name_conflicts['lt'][0][0])
text = text.replace(record_lt, '###lt###')
ws_xml.append([text])
key_lt = []
for version, key in name_conflicts['lt']:
key_lt.append(key)
key_lt.append(version.text)
ws2.append(key_lt)
ws2.append([])
elif not name_conflicts['lt'] and name_conflicts['en']:
ws2.append(['pavadinimas'])
ws_xml.append([id + '.pavadinimas'])
record_en = name_conflicts['en'][0][0].text
text = print_xml(name_conflicts['en'][0][0])
text = text.replace(record_en, '###en###')
ws_xml.append([text])
key_en = []
for version, key in name_conflicts['en']:
key_en.append(key)
key_en.append(version.text)
ws2.append([])
ws2.append(key_en)
for i, row in enumerate(ws_xml.iter_rows()):
for cell in row:
cell.alignment = Alignment(
wrap_text=True
) # nustatoma, kad viename Excel langelyje automatiškai perkeltų tekstą į kitą eilutę, taip nusistato ir stulpelio aukštis
for col in ws_xml.iter_cols():
ws_xml.column_dimensions[
col[0].column_letter].width = 110 # nustatomas langelio plotis
for col in ws2.iter_cols():
if col[0].column % 2 == 0:
ws2.column_dimensions[col[
0].column_letter].width = 70 # nustatomas langelio plotis
for row in ws2.iter_rows():
keys_lt = []
keys_en = []
ws2.protection.sheet = True
ws2.protection.enable()
if row[0].row % 3 == 1:
ws2.merge_cells(
'%s:%s' %
(row[0].coordinate, row[ws2.max_column - 1].coordinate))
for cell in row:
if cell.column % 2 == 0:
cell.protection = Protection(locked=False)
if cell.row % 3 == 1:
cell.fill = PatternFill(fgColor='c2ffd1',
fill_type='solid')
cell.font = Font(size=25, bold=True)
ws2.row_dimensions[cell.row].height = 30
cell.alignment = Alignment(horizontal='left')
elif cell.row % 3 == 2:
if cell.value not in keys_lt and cell.column % 2 == 0:
if keys_lt != []:
cell.font = Font(color='ff0000')
keys_lt.append(cell.value)
else:
if cell.value not in keys_en and cell.column % 2 == 0:
if keys_en != []:
cell.font = Font(color='ff0000')
keys_en.append(cell.value)
ws_xml.protection.sheet = True
ws_xml.protection.enable()
wb.save(os.path.join('excel_raktai_konceptai', ('%s.xlsx' % id)))
final_string = et.tostring(rt)
with open('new_' + filename, 'wb') as f:
f.write(
final_string
) # išsaugomas xml failas, tačiau vietoje konfliktų prisegama pirma versija (vėliau parenkama, kokia norima)
print(Back.GREEN + 'Programa baigė darbą sėkmingai' + Back.BLACK)
_ = input()
def generate_dataentry_for(dps, save_to=None):
is_all_dps = dps == Entity.get_root()
# colors
black = 'FF000000'
dark_gray = 'FFA6A6A6'
light_gray = 'FFDEDEDE'
yellow = 'F9FF00'
# styles
header_font = Font(name='Calibri',
size=12,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=black)
std_font = Font(name='Calibri',
size=12,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=black)
header_fill = PatternFill(fill_type=FILL_SOLID, start_color=dark_gray)
yellow_fill = PatternFill(fill_type=FILL_SOLID, start_color=yellow)
black_fill = PatternFill(fill_type=FILL_SOLID, start_color=black)
odd_fill = PatternFill(fill_type=FILL_SOLID, start_color=light_gray)
thin_black_side = Side(style='thin', color='FF000000')
thick_black_side = Side(style='thick', color='FF000000')
std_border = Border(
left=thin_black_side,
right=thin_black_side,
top=thin_black_side,
bottom=thin_black_side,
)
thick_left_border = Border(
left=thick_black_side,
right=thin_black_side,
top=thin_black_side,
bottom=thin_black_side,
)
thick_right_border = Border(
right=thick_black_side,
left=thin_black_side,
top=thin_black_side,
bottom=thin_black_side,
)
centered_alignment = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
left_alignment = Alignment(horizontal='left', vertical='center')
vertical_alignment = Alignment(horizontal='left',
vertical='bottom',
text_rotation=90,
wrap_text=True,
shrink_to_fit=False,
indent=0)
number_format = '# ### ### ##0'
protected = Protection(locked=True, hidden=False)
unprotected = Protection(locked=False, hidden=False)
header_style = {
'font': header_font,
'fill': header_fill,
'border': std_border,
'alignment': centered_alignment,
'protection': protected
}
vheader_style = {
'font': std_font,
'alignment': vertical_alignment,
'protection': protected
}
vheader_left_style = copy.copy(vheader_style)
vheader_left_style.update({'border': thick_left_border})
vheader_right_style = copy.copy(vheader_style)
vheader_right_style.update({'border': thick_right_border})
std_style = {
'font': std_font,
'border': std_border,
'alignment': centered_alignment,
}
names_style = {
'font': std_font,
'border': std_border,
'alignment': left_alignment,
}
def apply_style(target, style):
for key, value in style.items():
setattr(target, key, value)
# data validations
yv = DataValidation(type="list",
formula1='"{}"'.format(",".join(
[str(y) for y in range(2014, 2025)])),
allow_blank=True)
mv = DataValidation(type="list",
formula1='"{}"'.format(",".join(
[str(y) for y in range(1, 13)])),
allow_blank=True)
dv = DataValidation(type="whole",
operator="greaterThanOrEqual",
formula1='0')
wb = Workbook()
ws = wb.active
ws.title = "Données"
# sticky columns (DPS, ZS, YEAR, MONTH)
ws.freeze_panes = ws['E5']
ws.add_data_validation(yv)
ws.add_data_validation(mv)
ws.add_data_validation(dv)
# resize row height for 0, 1
xl_set_row_height(ws, 1, 2.2)
xl_set_row_height(ws, 2, 2.2)
# resize col A, B
xl_set_col_width(ws, 1, 5.5)
xl_set_col_width(ws, 2, 4.5)
# write partial metadata headers
ws.merge_cells("A3:A4")
ws.cell("A3").value = "DPS"
ws.merge_cells("B3:B4")
ws.cell("B3").value = "ZS"
ws.cell("C3").value = "ANNÉE"
ws.cell("D3").value = "MOIS"
indicator_column = 5
dps_row = 5
# zs_row = dps_row + 1
# header style
for sr in openpyxl.utils.cells_from_range("A3:D4"):
for coord in sr:
apply_style(ws.cell(coord), header_style)
for coord in ["C4", "D4"]:
ws.cell(coord).fill = yellow_fill
ws.cell(coord).protection = unprotected
# ZS of the selected DPS
children = [
child for child in dps.get_children()
if child.etype == Entity.ZONE or is_all_dps
]
def std_write(row, column, value, style=std_style):
cell = ws.cell(row=row, column=column)
cell.value = value
apply_style(cell, style)
def auto_calc_for(indicator, column, row):
calculation = ""
data = {
'num': "${l}{r}".format(l=column_to_letter(column - 2), r=row),
'denom': "${l}{r}".format(l=column_to_letter(column - 1), r=row),
'coef': indicator.TYPES_COEFFICIENT.get(indicator.itype),
'suffix': indicator.value_format.replace('{value}', '')
}
if indicator.itype == indicator.PROPORTION:
calculation += "{num}/{denom}"
else:
try:
calculation += "({num}*{coef})/{denom}"
except ZeroDivisionError:
raise
formula = '=IF({num}<>"",IF({denom}<>"",' \
'CONCATENATE(ROUND(' + calculation + ',2),"{suffix}")' \
',"?"),"?")'
return formula.format(**data)
# write indicator headers
column = indicator_column
for indicator in Indicator.get_all_sorted():
# write top header with indic name
row = 1
ws.merge_cells(start_row=row,
end_row=row + 1,
start_column=column,
end_column=column + 2)
std_write(row, column, indicator.name, vheader_style)
# write header with indic number
row = 3
num_str = "{n} - {t}".format(n=indicator.number,
t=indicator.verbose_collection_type)
ws.merge_cells(start_row=row,
end_row=row,
start_column=column,
end_column=column + 2)
std_write(row, column, num_str, header_style)
apply_style(ws.cell(row=row, column=column + 1), header_style)
# write sub header with NUM/DENOM
row = 4
if indicator.itype == Indicator.NUMBER:
ws.merge_cells(start_row=row,
end_row=row,
start_column=column,
end_column=column + 2)
std_write(row, column, "NOMBRE", std_style)
for r in range(row, row + len(children) + 2): # DPS + children
ws.merge_cells(start_row=r,
end_row=r,
start_column=column,
end_column=column + 2)
else:
std_write(row, column, "NUMERAT", std_style)
std_write(row, column + 1, "DÉNOM", std_style)
std_write(row, column + 2, "CALC", std_style)
row = dps_row + len(children)
nb_rows = row if is_all_dps else row + 1
# whether a row displays a ZS or not
row_is_zs = lambda row: False if is_all_dps else row > dps_row
# row-specific styles
for r in range(1, nb_rows):
left = ws.cell(row=r, column=column)
right = ws.cell(row=r, column=column + 1)
calc = ws.cell(row=r, column=column + 2)
# apply default style
if r >= dps_row:
apply_style(left, std_style)
apply_style(right, std_style)
apply_style(calc, std_style)
left.number_format = number_format
right.number_format = number_format
calc.number_format = number_format
# write formula for auto third calc
calc.set_explicit_value(value=auto_calc_for(
indicator=indicator, column=column + 2, row=r),
data_type=calc.TYPE_FORMULA)
# apply even/odd style
if r % 2 == 0:
if column == indicator_column:
for c in range(1, indicator_column):
ws.cell(row=r, column=c).fill = odd_fill
ws.cell(row=r, column=column).fill = odd_fill
ws.cell(row=r, column=column + 1).fill = odd_fill
ws.cell(row=r, column=column + 2).fill = odd_fill
# disable cell if data not expected at ZS
if row_is_zs(r) and indicator.collection_level != Entity.ZONE:
left.fill = black_fill
left.protection = protected
right.fill = black_fill
right.protection = protected
calc.fill = black_fill
elif not row_is_zs(r) \
and indicator.collection_type == indicator.ROUTINE:
left.fill = black_fill
left.protection = protected
right.fill = black_fill
right.protection = protected
calc.fill = black_fill
else:
left.protection = unprotected
right.protection = unprotected
# calc cell is always protected
calc.protection = protected
# apply thick borders
left.border = thick_left_border
# right.border = thick_right_border
calc.border = thick_right_border
# iterate over indicator
column += 3
last_row = dps_row + len(children)
# apply data validation for periods
yv.ranges.append('C4:C{}'.format(last_row))
mv.ranges.append('D4:D{}'.format(last_row))
# apply positive integer validation to all cells
last_column = indicator_column + len(Indicator.get_all_manual())
last_letter = column_to_letter(last_column)
dv.ranges.append('E4:{c}{r}'.format(c=last_letter, r=last_row))
row = dps_row
initial_row = [] if is_all_dps else [None]
# write names & periods
for child in initial_row + children:
if is_all_dps:
dps_name = child.std_name
zs_name = "-"
else:
dps_name = dps.std_name
zs_name = child.std_name if child else "-"
std_write(row, 1, dps_name, names_style)
std_write(row, 2, zs_name, names_style)
# set default value for period
year = ws.cell(row=row, column=3)
year.set_explicit_value(value="=$C$4", data_type=year.TYPE_FORMULA)
apply_style(year, std_style)
year.protection = unprotected
month = ws.cell(row=row, column=4)
month.set_explicit_value(value="=$D$4", data_type=month.TYPE_FORMULA)
apply_style(month, std_style)
month.protection = unprotected
row += 1
ws.protection.set_password("PNLP")
ws.protection.enable()
if save_to:
logger.info("saving to {}".format(save_to))
wb.save(save_to)
return
stream = StringIO.StringIO()
wb.save(stream)
return stream
def post(self, request, *args, **kwargs):
if (request.POST.get('save')):
studid = request.POST.getlist('studid')
points = []
points.append(request.POST.getlist('points1'))
points.append(request.POST.getlist('points2'))
points.append(request.POST.getlist('points3'))
points.append(request.POST.getlist('points4'))
points.append(request.POST.getlist('points6'))
semester = request.POST.getlist('semester')
arr_size = len(studid)
checkpoint = CheckPoint.objects.all()
discipline = Discipline.objects.get(id=self.kwargs['pk'])
exam = Exam.objects.get(discipline__id=self.kwargs['pk'],
semester__id=semester[0])
for i in range(0, arr_size):
st = Student.objects.get(id=studid[i])
k = 0
exammarks = ExamMarks.objects.filter(
exam__discipline__id=discipline.id,
exam__semester__id=semester[i]).get(student=st)
exammarks.examPoints = float(points[4][i].replace(',', '.'))
exammarks.inPoints = float(points[3][i].replace(',', '.'))
totalPoints = exammarks.examPoints + exammarks.inPoints
for ch in checkpoint:
brspoints = BRSpoints.objects.filter(
brs__discipline__id=discipline.id,
brs__semester__id=semester[i]).filter(
CheckPoint=ch).get(student=st)
if (k != 4):
brspoints.points = float(points[k][i].replace(
',', '.'))
k = k + 1
else:
brspoints.points = totalPoints
k = 0
brspoints.save()
tempMarkSymbol = get_markSymbol(exam.controlType.name,
totalPoints)
tempMark = get_mark(exam.controlType.name, totalPoints)
# if (tempMarkSymbol is None):
# newMarkSymbol = None
# else:
# newMarkSymbol = MarkSymbol.objects.filter(name=tempMarkSymbol).first()
# if (newMarkSymbol is None):
# newMarkSymbol = MarkSymbol.objects.create(name=tempMarkSymbol)
newMark = 2
# exammarks.markSymbol = newMarkSymbol
exammarks.mark_symbol = tempMarkSymbol if tempMarkSymbol else ''
exammarks.mark = newMark
exammarks.save()
return redirect('brs_studentlist', pk=self.kwargs['pk'])
elif request.POST.get('vedomost'):
# определяем стили
font_main = Font(name='Times New Roman',
size=12,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
font_bold = Font(name='Times New Roman',
size=12,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
font_bold_s = Font(name='Times New Roman',
size=10,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
font_calibri = Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
font_arial = Font(name='Arial Cyr',
size=12,
bold=False,
italic=True,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
fill = PatternFill(fill_type='solid',
start_color='c1c1c1',
end_color='c2c2c2')
border = Border(left=Side(border_style='thin', color='FF000000'),
right=Side(border_style='thin', color='FF000000'),
top=Side(border_style='thin', color='FF000000'),
bottom=Side(border_style='thin', color='FF000000'),
diagonal=Side(border_style='thin',
color='FF000000'),
diagonal_direction=0,
outline=Side(border_style='thin',
color='FF000000'),
vertical=Side(border_style='thin',
color='FF000000'),
horizontal=Side(border_style='thin',
color='FF000000'))
align_center = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
align_center2 = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
align_left = Alignment(horizontal='left',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
number_format = 'General'
protection = Protection(locked=True, hidden=False)
# объект
wb = Workbook()
# активный лист
ws = wb.active
# название страницы
# ws = wb.create_sheet('первая страница', 0)
ws.title = 'первая страница'
# текущее время
p = datetime.now()
# данные для строк
group_name = str(request.POST.get('selected_group'))
disc_id = self.kwargs['pk']
studid = request.POST.getlist('studid')
group = Group.objects.get(Name=group_name)
inpoints = request.POST.getlist('points4')
exampoints = request.POST.getlist('points6')
semester = request.POST.getlist('semester')
exam = Exam.objects.get(discipline__id=disc_id,
semester_id=semester[0])
arr_size = len(studid)
_row = 12
_column = 4
k = 1
z = 0
zachteno = 0
ne_zachteno = 0
ne_attest = 0
otl = 0
horosho = 0
udovl = 0
neudovl = 0
ne_yavka = 0
for i in range(0, arr_size):
gl = Student.objects.get(id=studid[i])
ws.cell(row=_row, column=1).value = str(k)
k += 1
ws.cell(row=_row, column=2).value = gl.FIO
ws.cell(row=_row, column=3).value = gl.StudentID
ws.cell(row=_row, column=_column).value = str(
float(inpoints[i].replace(',', '.'))).replace('.', ',')
ws.cell(row=_row, column=_column + 1).value = str(
float(exampoints[i].replace(',', '.'))).replace('.', ',')
totalpoints = float(inpoints[i].replace(',', '.')) + float(
exampoints[i].replace(',', '.'))
ws.cell(row=_row,
column=_column + 2).value = str(totalpoints).replace(
'.', ',')
ws.cell(row=_row,
column=_column + 3).value = get_mark_vedomost(
exam.controlType.name,
float(inpoints[i].replace(',', '.')),
float(exampoints[i].replace(',', '.')))
ws.cell(row=_row, column=_column + 4).value = get_markSymbol(
exam.controlType.name, totalpoints)
_row += 1
z += 1
zk = z + 11
zp = z + 14
zp2 = zp + 7
if group.year is None:
year = 'отсутствует'
else:
year = group.year
if exam.discipline.lecturer is None:
fio = 'отсутствует'
else:
fio = exam.discipline.lecturer.FIO
ws.cell(
row=1, column=1
).value = 'ФГАОУ ВО «Северо-Восточный федеральный университет им.М.К.Аммосова'
ws.cell(row=2,
column=1).value = 'Институт математики и информатики'
ws.cell(row=3, column=1
).value = 'Ведомость текущей и промежуточной аттестации'
ws.cell(row=5, column=1).value = 'Семестр: ' + str(
exam.semester.name
) + ', ' + exam.eduperiod.beginyear + '-' + exam.eduperiod.endyear + ' уч.г.'
ws.cell(row=6, column=1).value = 'Форма контроля:'
ws.cell(row=6, column=3).value = exam.controlType.name
ws.cell(row=6, column=5).value = 'курс ' + str(year)
ws.cell(row=6, column=6).value = 'группа:'
ws.cell(row=6, column=7).value = group_name
ws.cell(row=7, column=1).value = 'Дисциплина:'
ws.cell(row=7, column=3).value = exam.discipline.Name
ws.cell(row=8,
column=1).value = 'Фамилия, имя, отчество преподавателя:'
ws.cell(row=8, column=4).value = fio
ws.cell(row=9, column=1).value = 'Дата проведения зачета/экзамена:'
ws.cell(row=9, column=3).value = exam.examDate
ws.cell(row=11, column=1).value = '№'
ws.cell(row=11, column=2).value = 'Фамилия, имя, отчество'
ws.cell(row=11, column=3).value = '№ зачетной книжки'
ws.cell(
row=11,
column=4).value = 'Сумма баллов за текущую работу-рубеж.срез'
ws.cell(
row=11, column=5
).value = 'Баллы ' + exam.controlType.name + ' (бонусные баллы)'
ws.cell(row=11, column=6).value = 'Всего баллов'
ws.cell(row=11, column=7).value = 'Оценка прописью'
ws.cell(row=11, column=8).value = 'Буквенный эквивалент'
ws.cell(row=11, column=9).value = 'Подпись преподавателя'
ws.cell(row=zp, column=2).value = 'зачтено'
ws.cell(row=zp + 1, column=2).value = 'не зачтено'
ws.cell(row=zp + 2, column=2).value = 'не аттест'
ws.cell(row=zp + 3, column=2).value = '5(отлично)'
ws.cell(row=zp + 4, column=2).value = '4(хорошо)'
ws.cell(row=zp + 5, column=2).value = '3(удовл)'
ws.cell(row=zp + 6, column=2).value = '2(неудовл)'
ws.cell(row=zp2, column=2).value = 'не явка'
ws.cell(row=zp, column=5).value = 'Сумма баллов'
ws.cell(row=zp + 1, column=5).value = '95-100'
ws.cell(row=zp + 2, column=5).value = '85-94,9'
ws.cell(row=zp + 3, column=5).value = '75-84,9'
ws.cell(row=zp + 4, column=5).value = '65-74,9'
ws.cell(row=zp + 5, column=5).value = '55-64,9'
ws.cell(row=zp + 6, column=5).value = '25-54,9'
ws.cell(row=zp2, column=5).value = '0-24,9'
ws.cell(row=zp, column=7).value = 'Буквенный эквивалент оценки'
ws.cell(row=zp + 1, column=7).value = 'A'
ws.cell(row=zp + 2, column=7).value = 'B'
ws.cell(row=zp + 3, column=7).value = 'C'
ws.cell(row=zp + 4, column=7).value = 'D'
ws.cell(row=zp + 5, column=7).value = 'E'
ws.cell(row=zp + 6, column=7).value = 'FX'
ws.cell(row=zp2, column=7).value = 'F'
ws.cell(row=zp + 10,
column=2).value = 'Директор ИМИ СВФУ____________________'
ws.cell(row=zp + 10, column=4).value = 'В.И.Афанасьева'
# объединение ячеек
ws.merge_cells('A1:I1')
ws.merge_cells('A2:I2')
ws.merge_cells('A3:I3')
ws.merge_cells('A5:B5')
ws.merge_cells('A6:B6')
ws.merge_cells('C6:D6')
ws.merge_cells('A7:B7')
ws.merge_cells('C7:G7')
ws.merge_cells('A8:C8')
ws.merge_cells('D8:G8')
ws.merge_cells('A9:B9')
ws.merge_cells('C9:D9')
ws.merge_cells('E' + str(zp) + ':F' + str(zp))
ws.merge_cells('E' + str(zp + 1) + ':F' + str(zp + 1))
ws.merge_cells('E' + str(zp + 2) + ':F' + str(zp + 2))
ws.merge_cells('E' + str(zp + 3) + ':F' + str(zp + 3))
ws.merge_cells('E' + str(zp + 4) + ':F' + str(zp + 4))
ws.merge_cells('E' + str(zp + 5) + ':F' + str(zp + 5))
ws.merge_cells('E' + str(zp + 6) + ':F' + str(zp + 6))
ws.merge_cells('E' + str(zp + 7) + ':F' + str(zp + 7))
ws.merge_cells('G' + str(zp) + ':H' + str(zp))
ws.merge_cells('G' + str(zp + 1) + ':H' + str(zp + 1))
ws.merge_cells('G' + str(zp + 2) + ':H' + str(zp + 2))
ws.merge_cells('G' + str(zp + 3) + ':H' + str(zp + 3))
ws.merge_cells('G' + str(zp + 4) + ':H' + str(zp + 4))
ws.merge_cells('G' + str(zp + 5) + ':H' + str(zp + 5))
ws.merge_cells('G' + str(zp + 6) + ':H' + str(zp + 6))
ws.merge_cells('G' + str(zp + 7) + ':H' + str(zp + 7))
ws.merge_cells('B' + str(zp + 10) + ':C' + str(zp + 10))
ws.merge_cells('D' + str(zp + 10) + ':E' + str(zp + 10))
for cellObj in ws['G12:G' + str(zk)]:
for cell in cellObj:
if ws[cell.coordinate].value == 'Зачтено':
zachteno = zachteno + 1
elif ws[cell.coordinate].value == 'Не зачтено':
ne_zachteno = ne_zachteno + 1
elif ws[cell.coordinate].value == 'Не допущен':
ne_attest = ne_attest + 1
elif ws[cell.coordinate].value == 'Отлично':
otl = otl + 1
elif ws[cell.coordinate].value == 'Хорошо':
horosho = horosho + 1
elif ws[cell.coordinate].value == 'Удовлетворительно':
udovl = udovl + 1
elif ws[cell.coordinate].value == 'Неудовлетворительно':
neudovl = neudovl + 1
elif ws[cell.coordinate].value == 'Не явка':
ne_yavka = ne_yavka + 1
ws.cell(row=zp, column=3).value = str(zachteno)
ws.cell(row=zp + 1, column=3).value = str(ne_zachteno)
ws.cell(row=zp + 2, column=3).value = str(ne_attest)
ws.cell(row=zp + 3, column=3).value = str(otl)
ws.cell(row=zp + 4, column=3).value = str(horosho)
ws.cell(row=zp + 5, column=3).value = str(udovl)
ws.cell(row=zp + 6, column=3).value = str(neudovl)
ws.cell(row=zp2, column=3).value = str(ne_yavka)
# шрифты
for cellObj in ws['A1:I' + str(zk)]:
for cell in cellObj:
ws[cell.coordinate].font = font_main
for cellObj in ws['G12:G' + str(zk)]:
for cell in cellObj:
ws[cell.coordinate].font = font_bold_s
for cellObj in ws['B12:B' + str(zk)]:
for cell in cellObj:
ws[cell.coordinate].font = font_calibri
for cellObj in ws['H12:H' + str(zk)]:
for cell in cellObj:
ws[cell.coordinate].font = font_calibri
for cellObj in ws['E12:E' + str(zk)]:
for cell in cellObj:
ws[cell.coordinate].font = font_bold
for cellObj in ws['E11:I11']:
for cell in cellObj:
ws[cell.coordinate].font = Font(name='Times New Roman',
size=9,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
ws['A3'].font = font_bold
ws['C7'].font = font_bold
ws['D8'].font = font_bold
ws['F6'].font = font_bold
ws['C7'].font = font_arial
ws['D8'].font = font_arial
ws['G6'].font = Font(name='Arial Cyr',
size=12,
bold=False,
italic=True,
vertAlign=None,
underline='single',
strike=False,
color='FF000000')
ws['C9'].font = Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='single',
strike=False,
color='FF000000')
ws['A11'].font = Font(name='Times New Roman',
size=10,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
ws['B11'].font = Font(name='Times New Roman',
size=10,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
ws['C11'].font = Font(name='Times New Roman',
size=9,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
ws['D11'].font = Font(name='Times New Roman',
size=8,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
ws['C6'].font = Font(name='Times New Roman',
size=14,
bold=False,
italic=True,
vertAlign=None,
underline='single',
strike=False,
color='FF000000')
# увеличиваем все строки по высоте
max_row = ws.max_row
i = 1
while i <= max_row:
rd = ws.row_dimensions[i]
rd.height = 16
i += 1
# вручную устанавливаем высоту первой строки
rd = ws.row_dimensions[11]
rd.height = 48
# сетка
for cellObj in ws['A11:I' + str(zk)]:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].border = border
for cellObj in ws['B' + str(zp) + ':C' + str(zp2)]:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].border = border
for cellObj in ws['E' + str(zp) + ':H' + str(zp2)]:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].border = border
# выравнивание
for cellObj in ws['A1:I3' + str(zk)]:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].alignment = align_center
for cellObj in ws['A11:I11']:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].alignment = align_center2
for cellObj in ws['A5:I9']:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].alignment = align_left
# перетягивание ячеек
dims = {}
for cellObj in ws['G11:G' + str(zk)]:
for cell in cellObj:
if cell.value:
dims[cell.column] = max((dims.get(cell.column,
0), len(cell.value)))
for col, value in dims.items():
# value * коэфициент
ws.column_dimensions[col].width = value * 1.5
dims = {}
for cellObj in ws['A11:A' + str(zk)]:
for cell in cellObj:
if cell.value:
dims[cell.column] = max((dims.get(cell.column,
0), len(cell.value)))
for col, value in dims.items():
# value * коэфициент
ws.column_dimensions[col].width = value * 3
dims = {}
for cellObj in ws['B11:B' + str(zk)]:
for cell in cellObj:
if cell.value:
dims[cell.column] = max((dims.get(cell.column,
0), len(cell.value)))
for col, value in dims.items():
# value * коэфициент
ws.column_dimensions[col].width = value * 1.5
dims = {}
for cellObj in ws['D11:D' + str(zk)]:
for cell in cellObj:
if cell.value:
dims[cell.column] = max((dims.get(cell.column,
0), len(cell.value)))
for col, value in dims.items():
# value * коэфициент
ws.column_dimensions[col].width = value * 0.25
# сохранение файла в выбранную директорию
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
)
response[
'Content-Disposition'] = 'attachment; filename=vedomost.xlsx'
wb.save(response)
return response
def fillDimension(self):
from openpyxl import load_workbook
from openpyxl.styles import Protection
from openpyxl.workbook.protection import WorkbookProtection
workbook = load_workbook('resources/output/process/Summaries.xlsx')
for pop in self.__popRes:
for _ in pop:
for index in pop[_]:
try:
for k, v in index.items():
init = 3
workbook.active = workbook[str(k)]
while workbook.active['E{}'.format(
str(init))].value != None:
init += 1
workbook.active.protection.sheet = True
workbook.active.protection.password = '******'
for x in range(3, 100):
workbook.active[
'H' + str(x)].protection = Protection(
locked=False)
workbook.active[
'I' + str(x)].protection = Protection(
locked=False)
workbook.active[
'J' + str(x)].protection = Protection(
locked=False)
workbook.active[
'K' + str(x)].protection = Protection(
locked=False)
workbook.active[
'L' + str(x)].protection = Protection(
locked=False)
workbook.active[
'M' + str(x)].protection = Protection(
locked=False)
workbook.active[
'N' + str(x)].protection = Protection(
locked=False)
workbook.active[
'O' + str(x)].protection = Protection(
locked=False)
workbook.active['E{}'.format(
str(init))].value = v[8][0]
workbook.active['F{}'.format(
str(init))].value = v[8][1]
workbook.active['G{}'.format(
str(init))].value = v[8][2]
workbook.active['H{}'.format(
str(init))].value = v[0]
workbook.active['I{}'.format(
str(init))].value = v[1]
workbook.active['J{}'.format(
str(init))].value = v[2]
workbook.active['K{}'.format(
str(init))].value = v[3]
workbook.active['L{}'.format(
str(init))].value = v[4]
workbook.active['M{}'.format(
str(init))].value = v[5]
workbook.active['N{}'.format(
str(init))].value = v[6]
workbook.active['O{}'.format(
str(init))].value = v[7]
except Exception as e:
print(e)
workbook.security = WorkbookProtection(workbookPassword='******',
lockWindows=True,
lockStructure=True)
workbook.save("resources/output/process/Summaries.xlsx")
workbook.close()
def _format_sheet(self, Sheet):
Sheet.protection.sheet = True
Sheet.protection.set_password('pabi2')
first_row = 8
last_row = first_row + 100
Reason = DataValidation(
type="list",
formula1='"New,Replacement,Extra"'
)
SHEET_FORMULAS.update({'reason': Reason})
Sheet.add_data_validation(Reason)
ResProgramGroup = SHEET_FORMULAS.get('formula_program_group', False)
Sheet.add_data_validation(ResProgramGroup)
ResInvestAsset = SHEET_FORMULAS.get('formula_asset_category', False)
Sheet.add_data_validation(ResInvestAsset)
ResSection = SHEET_FORMULAS.get('formula_section', False)
Sheet.add_data_validation(ResSection)
ResDivision = SHEET_FORMULAS.get('formula_division', False)
Sheet.add_data_validation(ResDivision)
Employee = SHEET_FORMULAS.get('formula_employee', False)
Sheet.add_data_validation(Employee)
protection = Protection(locked=False)
for row in range(first_row, last_row):
for col in range(1, 30):
if col == 2:
ResProgramGroup.add(Sheet.cell(row=row, column=col))
if col == 3:
ResInvestAsset.add(Sheet.cell(row=row, column=col))
Sheet.cell(row=row, column=col).protection = protection
if col == 6:
Employee.add(Sheet.cell(row=row, column=col))
if col == 7:
ResSection.add(Sheet.cell(row=row, column=col))
if col == 8:
ResDivision.add(Sheet.cell(row=row, column=col))
if col == 15:
Reason.add(Sheet.cell(row=row, column=col))
if col == 12:
# Price subtotal
value = "=J%s*$K$%s" % (row, row)
Sheet.cell(row=row, column=col).value = value
elif col == 14:
# Price Total
value = "=L%s+$M$%s" % (row, row)
Sheet.cell(row=row, column=col).value = value
elif col == 22:
# Total Commitment
value = "=W%s+$X$%s+$Y$%s" % (row, row, row)
Sheet.cell(row=row, column=col).value = value
elif col == 27:
# Total Commitment + Actual
value = "=V%s+$Z$%s" % (row, row)
Sheet.cell(row=row, column=col).value = value
elif col == 29:
# Carried Forward (Total Commitment + Residual Budget)
value = "=V%s+$AB$%s" % (row, row)
Sheet.cell(row=row, column=col).value = value
else:
Sheet.cell(row=row, column=col).protection = protection
def __init__(self, df='', jsonObject='', header='', font='', fill='', border='', alignment='',
number_format='General', protection=''):
cache.clear()
if type(jsonObject) == str and jsonObject != '' and type(df) != str:
raise Exception("Multiple object send. You must send either dataframe of a JSON object")
elif type(df) != str:
if isinstance(df, pd.DataFrame) == True:
self.df = df
else:
raise Exception("Type mismatch. Requires queryset must be of type of 'DataFrame'")
elif type(jsonObject) == str and jsonObject != '':
try:
jsonObject = json.loads(jsonObject)
self.df = pd.io.json.json_normalize(jsonObject)
except:
raise Exception(
"Type mismatch. Requires queryset must be of type of either 'DataFrame' or 'Json Object'")
if type(header) != str:
if (type(header) == list):
self.header = header
else:
raise Exception("Type mismatch. header must be a list.")
if type(font) == str and font == '':
self.font = Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
elif type(font) == dict:
self.font = Font(
name=font.get('name') if 'name' in font else 'Calibri',
size=font.get('size') if 'size' in font else 11,
bold=font.get('bold') if 'bold' in font else False,
italic=font.get('italic') if 'italic' in font else False,
vertAlign=font.get('vertAlign') if 'vertAlign' in font else None,
underline=font.get('underline') if 'underline' in font else 'none',
strike=font.get('strike') if 'strike' in font else False,
color=font.get('color') if 'color' in font else 'FF000000'
)
else:
raise Exception("Type mismatch. Font must be a type of dictionary")
if type(fill) == str and fill == '':
self.fill = PatternFill(fill_type=None,
start_color='FFFFFFFF',
end_color='FF000000')
elif type(fill) == dict:
self.fill = PatternFill(
fill_type=fill.get('fill_type') if 'fill_type' in fill else None,
start_color=fill.get('start_color') if 'start_color' in fill else 11,
end_color=fill.get('end_color') if 'end_color' in fill else False,
)
else:
raise Exception("Type mismatch. Fill must be a type of dictionary")
if type(alignment) == str and alignment == '':
self.alignment = Alignment(horizontal='general',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
elif type(alignment) == dict:
self.alignment = Alignment(horizontal=alignment.get('horizontal') if 'horizontal' in alignment else 'general',
vertical=alignment.get('vertical') if 'vertical' in alignment else 'bottom',
text_rotation=alignment.get(
'text_rotation') if 'text_rotation' in alignment else 0,
wrap_text=alignment.get('wrap_text') if 'wrap_text' in alignment else False,
shrink_to_fit=alignment.get(
'shrink_to_fit') if 'shrink_to_fit' in alignment else False,
indent=alignment.get('indent') if 'indent' in alignment else 0)
else:
raise Exception("Type mismatch. alignment must be a type of dictionary")
if type(protection) == str and protection == '':
self.protection = Protection(locked=True,
hidden=False)
elif type(protection) == dict:
self.protection = Protection(locked=protection.get('locked') if 'locked' in protection else True,
hidden=protection.get('hidden') if 'hidden' in protection else False
)
else:
raise Exception("Type mismatch. protection must be a type of dictionary")
def formato(request, id):
# -- Consultar registro
register = get_register_byId(id)
register = register.to_records()
# -- Leer archivo tarificador (input)
path = os.path.join(
os.path.dirname(os.path.dirname(__file__)),
'../static/pricing/tarificadores_inputs/' + register[0][3])
xlsxFile = pd.ExcelFile(path)
Input = pd.DataFrame(pd.read_excel(xlsxFile, sheet_name='Input'))
Input = Input.iloc[1:, :].reset_index(drop=True)
InputsTarificador = pd.DataFrame(
pd.read_excel(xlsxFile, sheet_name='Tarificador'))
# -- Consultar base de datos de coberturas
baseCoberturas = get_all_coberturas()
# -- Consultar productos
productos = InputsTarificador.loc[InputsTarificador['comision'] ==
'Código Producto']
productos = productos.iloc[:, 1:].reset_index(drop=True).T.reset_index(
drop=True)
productos = productos.dropna()
# -- Agrupar productos por 'Código Producto'
productos[1] = productos[0]
for i, row in productos.iterrows():
productos[1][i] = row[0][:-1]
productosAgrupados = productos.groupby([1]).count().reset_index()
productosAgrupados[0] = productosAgrupados[1]
for i, row in productosAgrupados.iterrows():
productosAgrupados[0][i] = row[0] + '1'
# -- Crear dataframe para configuración de productos -- #
DataProductos = InputsTarificador.iloc[:, 0:].set_index(
'comision').T.reset_index(drop=True)
# -- Inicio filtros para extraer No. indices para la tabla --#
# -- Valor Asegurado -- #
indexMin, indexMax = getIndicesTable(InputsTarificador, 'VLR_ASE')
# -- TIPO COBERTURA -- #
indexMinTIP_COB, indexMaxTIP_COB = getIndicesTable(InputsTarificador,
'TIP_COB')
# -- RAMO COD -- #
indexMinRAM_COD, indexMaxRAM_COD = getIndicesTable(InputsTarificador,
'RAM_COD')
# -- Reglas de negocio -- #
indexMinREG_NEG, indexMaxREG_NEG = getIndicesTable(InputsTarificador,
'REG_NEG')
# -- CUOTAS -- #
indexMinCUO, indexMaxCUO = getIndicesTable(InputsTarificador, 'CUO')
# -- Valor a indemnizar; Forma de pago del siniestro -- #
indexMinVIN_FPS, indexMaxVIN_FPS = getIndicesTable(InputsTarificador,
'VIN_FPS')
# -- Distribución Prima Neta antes de IVA -- #
indexMinDIS_IVA, indexMaxDIS_IVA = getIndicesTable(InputsTarificador,
'DIS_IVA')
# -- BENEFICIARIOS -- #
indexMinBEN, indexMaxBEN = getIndicesTable(InputsTarificador, 'BEN')
# -- Periodo de Carencia -- #
indexMinPER_CAR, indexMaxPER_CAR = getIndicesTable(InputsTarificador,
'PER_CAR')
# -- Numero de limites de eventos por vigencia -- #
indexMinLIM_EVE, indexMaxLIM_EVE = getIndicesTable(InputsTarificador,
'LIM_EVE')
# --------- INICIO Extraer información de todas las coberturas por producto --------- #
totalCoberturas = pd.DataFrame(columns=[
'Producto', 'CodProductoTEMP', 'CodCobertura', 'Cobertura',
'FullCobertura', 'TIP_COB', 'RAM_COD', 'REG_NEG', 'TiempoLimite',
'UnidadDeTiempo', 'DiasContLaboral', 'CUO', 'DiasMinimosDeIncapacidad',
'DiasMaximoDeIncapacidad', 'VIN', 'FPS', 'DIS_IVA', 'BEN', 'PER_CAR',
'LIM_EVE', 'i', 'VLR_ASE'
])
contadorCoberturas = 0
ListadoCodigosProductos = []
for i, row in productos.iterrows():
ListadoCodigosProductos.append(str(row[0][:-1]))
coberturasFiltro1 = DataProductos.loc[DataProductos['Código Producto']
== str(row[0])]
coberturasFiltro1 = coberturasFiltro1.reset_index(
drop=True).T.reset_index()
coberturasFiltro2 = coberturasFiltro1.iloc[
indexMin:indexMax].reset_index()
coberturasFiltro2['IndiceCobertura'] = coberturasFiltro2.index
coberturasFiltro3 = coberturasFiltro2.loc[coberturasFiltro2[0] > 0]
# -- Valor Asegurado
VLR_ASE = DataProductos.loc[DataProductos['Código Producto'] == str(
row[0])].reset_index(drop=True).T.reset_index(
drop=True).iloc[indexMin:indexMax].reset_index(drop=True)
# -- Consultar tipo de cobertura
TipoCobertura = DataProductos.loc[
DataProductos['Código Producto'] ==
str(row[0])].reset_index(drop=True).T.reset_index(
drop=True).iloc[indexMinTIP_COB:indexMaxTIP_COB].reset_index(
drop=True)
# -- Consultar Cod ramo
CodRamoCobertura = DataProductos.loc[
DataProductos['Código Producto'] ==
str(row[0])].reset_index(drop=True).T.reset_index(
drop=True).iloc[indexMinRAM_COD:indexMaxRAM_COD].reset_index(
drop=True)
# -- Consultar Reglas de negocio
ReglasDeNegocio = DataProductos.loc[
DataProductos['Código Producto'] ==
str(row[0])].reset_index(drop=True).T.reset_index(
drop=True).iloc[indexMinREG_NEG:indexMaxREG_NEG].reset_index(
drop=True)
# -- Consultar Cuotas
Cuotas = DataProductos.loc[DataProductos['Código Producto'] == str(
row[0])].reset_index(drop=True).T.reset_index(
drop=True).iloc[indexMinCUO:indexMaxCUO].reset_index(drop=True)
# -- Consultar Valor a indemnizar; Forma de pago del siniestro
VIN_FPS = DataProductos.loc[DataProductos['Código Producto'] == str(
row[0])].reset_index(drop=True).T.reset_index(
drop=True).iloc[indexMinVIN_FPS:indexMaxVIN_FPS].reset_index(
drop=True)
# -- Consultar valor de NET %
DIS_IVA = DataProductos.loc[DataProductos['Código Producto'] == str(
row[0])].reset_index(drop=True).T.reset_index(
drop=True).iloc[indexMinDIS_IVA:indexMaxDIS_IVA].reset_index(
drop=True)
# -- Consular valores de BENEFICIARIOS
BEN = DataProductos.loc[DataProductos['Código Producto'] == str(
row[0])].reset_index(drop=True).T.reset_index(
drop=True).iloc[indexMinBEN:indexMaxBEN].reset_index(drop=True)
# -- Consultar valores de Periodo de Carencia
PER_CAR = DataProductos.loc[DataProductos['Código Producto'] == str(
row[0])].reset_index(drop=True).T.reset_index(
drop=True).iloc[indexMinPER_CAR:indexMaxPER_CAR].reset_index(
drop=True)
# -- Consultar Numero de limites de eventos por vigencia
LIM_EVE = DataProductos.loc[DataProductos['Código Producto'] == str(
row[0])].reset_index(drop=True).T.reset_index(
drop=True).iloc[indexMinLIM_EVE:indexMaxLIM_EVE].reset_index(
drop=True)
for j, rowW in coberturasFiltro3.iterrows():
totalCoberturas.loc[contadorCoberturas] = ''
totalCoberturas['i'][contadorCoberturas] = i
totalCoberturas['Producto'][contadorCoberturas] = row[0]
totalCoberturas['CodProductoTEMP'][
contadorCoberturas] = 'Temporal_' + str(i + 1)
totalCoberturas['Cobertura'][contadorCoberturas] = rowW['comision']
# -- Consultar ID de cobertura
CodCobertura = baseCoberturas.loc[
baseCoberturas['COBERTURA'] == str(
rowW['comision'])].reset_index(drop=True)
totalCoberturas['CodCobertura'][contadorCoberturas] = CodCobertura[
'ID'][0]
totalCoberturas['FullCobertura'][
contadorCoberturas] = CodCobertura['FULLCOBERTURA'][0]
# -- TIP_COB
totalCoberturas['TIP_COB'][contadorCoberturas] = TipoCobertura[0][
rowW['IndiceCobertura']]
# -- Consultar Cod ramo
NaneCodRamoCobertura = CodRamoCobertura[0][rowW['IndiceCobertura']]
CODNaneCodRamoCobertura = NaneCodRamoCobertura.split('.')
totalCoberturas['RAM_COD'][
contadorCoberturas] = CODNaneCodRamoCobertura[0]
# -- Consultar Reglas de negocio
totalCoberturas['REG_NEG'][contadorCoberturas] = ReglasDeNegocio[
0][rowW['IndiceCobertura']]
# -- Consultar 'Tiempo límite de Transacción, Bloqueo, límite de Hurto o Daño' de cobertura
totalCoberturas['TiempoLimite'][contadorCoberturas] = CodCobertura[
'TIEMPO_LIMITE'][0]
# -- Consultar 'Unidad de Tiempo Item Columna O' de cobertura
totalCoberturas['UnidadDeTiempo'][
contadorCoberturas] = CodCobertura['UNIDAD_TIEMPO'][0]
# -- Dias de continuidad laboral (Solo se agrega valor fijo 180, siempre y cuando la cobertura sea desempleo)
totalCoberturas['DiasContLaboral'][contadorCoberturas] = 0
if str(rowW['comision']) == 'Desempleo':
totalCoberturas['DiasContLaboral'][contadorCoberturas] = 180
# -- Consultar Cuotas
totalCoberturas['CUO'][contadorCoberturas] = Cuotas[0][
rowW['IndiceCobertura']]
# -- Dias Minimos de Incapacidad (Solo aplica para '%Incapacidad Total Temporal%', se coloca 15)
DiasMinimosDeIncapacidad = 0
if ("Incapacidad Total Temporal" in str(rowW['comision'])):
DiasMinimosDeIncapacidad = 15
totalCoberturas['DiasMinimosDeIncapacidad'][
contadorCoberturas] = DiasMinimosDeIncapacidad
# -- Dias Maximo de Incapacidad (Solo aplica para itt, se coloca 1000)
DiasMaximoDeIncapacidad = 0
if ("Incapacidad Total Temporal" in str(rowW['comision'])):
DiasMaximoDeIncapacidad = 1000
totalCoberturas['DiasMaximoDeIncapacidad'][
contadorCoberturas] = DiasMaximoDeIncapacidad
# -- Consultar Valor a indemnizar; Forma de pago del siniestro
BuscarVIN_FPS = VIN_FPS[0][rowW['IndiceCobertura']].split(';')
totalCoberturas['VIN'][contadorCoberturas] = BuscarVIN_FPS[0]
totalCoberturas['FPS'][contadorCoberturas] = BuscarVIN_FPS[1]
# -- Consultar valor de NET %
totalCoberturas['DIS_IVA'][contadorCoberturas] = DIS_IVA[0][
rowW['IndiceCobertura']]
# -- Consular valores de BENEFICIARIOS
totalCoberturas['BEN'][contadorCoberturas] = BEN[0][
rowW['IndiceCobertura']]
# -- Consultar valores de Periodo de Carencia
totalCoberturas['PER_CAR'][contadorCoberturas] = PER_CAR[0][
rowW['IndiceCobertura']]
# -- Consultar Numero de limites de eventos por vigencia
totalCoberturas['LIM_EVE'][contadorCoberturas] = LIM_EVE[0][
rowW['IndiceCobertura']]
# -- Valor Asegurado
totalCoberturas['VLR_ASE'][contadorCoberturas] = VLR_ASE[0][
rowW['IndiceCobertura']]
# -- Contador ++
contadorCoberturas = contadorCoberturas + 1
# -- Ordenar primero las coberturas Principales
totalCoberturas = totalCoberturas.sort_values(
by=['CodProductoTEMP', 'TIP_COB'], ascending=[True, False])
# -- Agrupar Coberturas por 'Producto'
totalCoberturas['ProductoCobNombre'] = totalCoberturas['Producto']
totalCoberturas['ProductoNombre'] = totalCoberturas['Producto']
totalCoberturas['Plan'] = totalCoberturas['Producto']
for i, row in totalCoberturas.iterrows():
totalCoberturas['ProductoCobNombre'][i] = str(
row['CodCobertura']) + row['Producto'][:-1]
totalCoberturas['ProductoNombre'][i] = row['Producto'][:-1]
totalCoberturas['Plan'][i] = row['Producto'][-1:]
totalCoberturasAgrupadas = totalCoberturas
totalCoberturasAgrupadas = totalCoberturasAgrupadas.drop_duplicates(
subset='ProductoCobNombre')
totalCoberturasAgrupadas = totalCoberturasAgrupadas.reset_index(drop=True)
# -- Re organizar el id2 (id padre)
totalCoberturas["i2"] = (
totalCoberturas.ProductoNombre !=
totalCoberturas.ProductoNombre.shift(1)).fillna(0).cumsum()
totalCoberturas["i2"] = totalCoberturas["i2"] - 1
totalCoberturas = totalCoberturas.reset_index(drop=True)
# -- Re organizar el id2 (id padre)
totalCoberturasAgrupadas["i2"] = (
totalCoberturasAgrupadas.Producto !=
totalCoberturasAgrupadas.Producto.shift(1)).fillna(0).cumsum()
totalCoberturasAgrupadas["i2"] = totalCoberturasAgrupadas["i2"] - 1
# --------- FIN EExtraer información de todas las coberturas por producto --------- #
# ---------------------------------------------------------------------------------------------------------------------------------- #
# -------------------------------------------------------- Configuración ----------------------------------------------------------- #
# ---------------------------------------------------------------------------------------------------------------------------------- #
titlesConfiguracion = [
'CODIGO REGISTRO', 'TIPO DE IMPLEMENTACION', 'CODIGO PRODUCTO',
'VERSIÓN', 'SOCIO', 'COD SOCIO', 'SIGLA SOCIO', 'MONEDA',
'DIAS DE CANCELACION POR MORA', 'FECHA INICIO PRODUCTO',
'GRUPO CONFIGURACION', 'VIGENCIA', 'PERIODICIDAD',
'PRODUCTO FINANCIERO/ LINEA INCENTIVOS', 'CANAL DE VENTA',
'NOMBRE PRODUCTO', 'NOMBRE PRODUCTO PIMS', 'NOMBRE PRODUCTO SOCIO',
'TIPO PRODUCTO SEGURO', 'Product Type', 'ProductCodeMetierType',
'Product Activity Label Type', 'Product Family',
'NO. POLIZA GRUPO CARDIF/ALFA', 'RANGO DE TOLERANCIA',
'ENVIO KIT BIENVENIDA', 'INCENTIVOS', 'ARMADO CERTIFICADO CARDIF',
'ARMADO CERTIFICADO SOCIO', 'PERIODICAS EN PIMS', 'RENOVACION EN PIMS',
'APLICA EXCLUYENTES', 'PRODUCTOS EXCLUYENTES',
'FORMA DE INCREMENTO IPC', 'GRUPO DE FACTURACION',
'TIPO/FORMA DE COBRANZA', 'FRANQUICIA MEDIO DE PAGO',
'COD DE CONVENIO', 'COD DE COMPENSACION', 'COD TERMINAL',
'MODELO DE NEGOCIO', '% Distribucion primas', 'Siniestros IPDV',
'BASE CALCULO PRIMA', 'ALFA', 'Cant. Planes', 'Cant. Coberturas',
'Cant. Terceros Comisión', 'Layout Emisión', 'Layout Novedad',
'Código Interno PIMS', 'Código PIMS', 'ID Job Periódicas',
'ID Job Renovación', 'ID Job Cierre Edad', 'ID Job Cierre Vigencia',
'GRUPO DE CARGA PRODUCCION COBRA', 'PERIODICAS EN COBRA'
]
configuracion = pd.DataFrame(columns=titlesConfiguracion)
for i, row in productosAgrupados.iterrows():
configuracion.loc[i] = ''
# -- INICIO Columnas fijas
configuracion['CODIGO REGISTRO'][i] = '=VLOOKUP(E' + str(
i + 3) + ',socio.conf,10,0) & 2019071'
configuracion['COD SOCIO'][i] = '=VLOOKUP(E' + str(
i + 3) + ',socio.conf,3,0)'
configuracion['SIGLA SOCIO'][i] = '=IFERROR(VLOOKUP(E' + str(
i + 3) + ',socio.conf,4,0),"AC")'
configuracion['MONEDA'][i] = '=VLOOKUP(E' + str(i +
3) + ',socio.conf,6,0)'
configuracion['DIAS DE CANCELACION POR MORA'][i] = '=VLOOKUP(E' + str(
i + 3) + ',socio.conf,7,0)'
# configuracion['VIGENCIA'][i] = '=HLOOKUP(K' + str(i + 3) + ',grupo.conf,3,0)'
# configuracion['PERIODICIDAD'][i] = '=HLOOKUP(K' + str(i + 3) + ',grupo.conf,4,0)'
configuracion['NOMBRE PRODUCTO'][i] = '=C' + str(
i + 3) + ' & "_" & SUBSTITUTE(VLOOKUP(E' + str(
i + 3
) + ',socio.conf,2,0)," ","_") & "_" & SUBSTITUTE(T' + str(
i + 3) + '," ","_") & "_" & HLOOKUP(K' + str(
i + 3
) + ',grupo.conf,4,0) & "_" & VLOOKUP(S' + str(
i + 3
) + ',productoSeguro.info,2,0) & "_" & SUBSTITUTE(O' + str(
i + 3) + '," ","_")'
configuracion['NOMBRE PRODUCTO PIMS'][i] = '=C' + str(
i + 3) + ' & "_" & SUBSTITUTE(VLOOKUP(E' + str(
i + 3) + ',socio.conf,13,0)," ","_") & "_" & VLOOKUP(T' + str(
i + 3) + ',productType.conf,5,0) & "_" & HLOOKUP(K' + str(
i + 3
) + ',grupo.conf,4,0) & "_" & VLOOKUP(S' + str(
i + 3
) + ',productoSeguro.info,2,0) & "_" & SUBSTITUTE(O' + str(
i + 3) + '," ","_")'
configuracion['ProductCodeMetierType'][i] = '=VLOOKUP(T' + str(
i + 3) + ',productType.conf,2,0)'
configuracion['Product Activity Label Type'][i] = '=VLOOKUP(T' + str(
i + 3) + ',productType.conf,3,0)'
configuracion['Product Family'][i] = '=VLOOKUP(T' + str(
i + 3) + ',productType.conf,4,0)'
configuracion['COD DE COMPENSACION'][i] = '=VLOOKUP(E' + str(
i + 3) + ',socio.conf,8,0)'
configuracion['PERIODICAS EN COBRA'][i] = '=IF(AND(HLOOKUP(K' + str(
i + 3) + ',grupo.conf,2,0)="Individual",AR' + str(
i + 3
) + '<>"Valor enviado por el socio (Prima quemada)"),"Si","No")'
# -- FIN Columnas fijas
# configuracion['CODIGO PRODUCTO'][i] = 'Temporal_' + str(i + 1)
# configuracion['CODIGO PRODUCTO'][i] = row[1]
# -- SOCIO
Socio = Input.loc[Input['Variable'] == 'Socio']['Valor'].reset_index(
drop=True)[0]
configuracion['SOCIO'][i] = Socio
# -- VIGENCIA
Vigencia = DataProductos.loc[DataProductos['Código Producto'] == str(
row[0])]['Vigencia'].reset_index(drop=True)[0]
configuracion['VIGENCIA'][i] = Vigencia
# -- PERIODICIDAD
TipoDePagoDePrima = DataProductos.loc[
DataProductos['Código Producto'] == str(
row[0])]['Periodicidad pago prima'].reset_index(drop=True)[0]
configuracion['PERIODICIDAD'][i] = TipoDePagoDePrima
# -- PRODUCTO FINANCIERO/ LINEA INCENTIVOS
Linea = DataProductos.loc[DataProductos['Código Producto'] == str(
row[0])]['Linea'].reset_index(drop=True)[0]
configuracion['PRODUCTO FINANCIERO/ LINEA INCENTIVOS'][i] = Linea
# -- CANAL DE VENTA
CanalDeVenta = DataProductos.loc[
DataProductos['Código Producto'] == str(
row[0])]['Canal de Venta'].reset_index(drop=True)[0]
configuracion['CANAL DE VENTA'][i] = CanalDeVenta
# -- TIPO PRODUCTO SEGURO
TIPOPRODUCTOSEGURO = DataProductos.loc[
DataProductos['Código Producto'] == str(
row[0])]['Tipo producto de seguro'].reset_index(drop=True)[0]
configuracion['TIPO PRODUCTO SEGURO'][i] = TIPOPRODUCTOSEGURO
# -- Product Type
ProductType = DataProductos.loc[
DataProductos['Código Producto'] == str(
row[0])]['Product Type'].reset_index(drop=True)[0]
configuracion['Product Type'][i] = ProductType
# -- INCENTIVOS
"""
PDV = Input.loc[Input['Variable'] == 'PDV']['Valor'].reset_index(drop=True)[0]
if PDV > 0:
configuracion['INCENTIVOS'][i] = 'Si'
else:
configuracion['INCENTIVOS'][i] = 'No'
"""
configuracion['INCENTIVOS'][i] = DataProductos.loc[
DataProductos['Código Producto'] == str(
row[0])]['INCENTIVOS'].reset_index(drop=True)[0]
# -- MODELO DE NEGOCIO
ModeloDeNegocio = Input.loc[Input['Variable'] ==
'MODELO DE NEGOCIO']['Valor'].reset_index(
drop=True)[0]
configuracion['MODELO DE NEGOCIO'][i] = ModeloDeNegocio
# -- % Distribucion primas
ModeloDeNegocio = Input.loc[
Input['Variable'] == '% Distribucion primas']['Valor'].reset_index(
drop=True)[0]
configuracion['% Distribucion primas'][i] = ModeloDeNegocio
# -- BASE CALCULO PRIMA
BaseCalculoPrima = DataProductos.loc[
DataProductos['Código Producto'] == str(
row[0])]['Base Cálculo Prima'].reset_index(drop=True)[0]
configuracion['BASE CALCULO PRIMA'][i] = BaseCalculoPrima
# -- Cant. Planes
CodigoProducto = DataProductos.loc[
DataProductos['Código Producto'] == str(
row[0])]['Código Producto'].reset_index(drop=True)[0]
CodigoProducto = CodigoProducto[:-1]
configuracion['Cant. Planes'][i] = ListadoCodigosProductos.count(
CodigoProducto)
# -- Cant. Coberturas
Coberturas = DataProductos.loc[DataProductos['Código Producto'] == str(
row[0])].reset_index(drop=True).T.reset_index(
drop=True).iloc[indexMin:indexMax]
numeroCoberturas = Coberturas.loc[Coberturas[0] > 0].count()
configuracion['Cant. Coberturas'][i] = numeroCoberturas[0]
# -- Cant. Terceros Comisión
TercerosConComision = DataProductos.loc[
DataProductos['Código Producto'] == str(
row[0])]['Terceros con comision'].reset_index(drop=True)[0]
configuracion['Cant. Terceros Comisión'][i] = TercerosConComision
# ---------------------------------------------------------------------------------------------------------------------------------- #
# ---------------------------------------------------------- Cobertura ------------------------------------------------------------- #
# ---------------------------------------------------------------------------------------------------------------------------------- #
titlesCobertura = [
'COD REGISTRO',
'Version',
'PRODUCTO',
'COD COBERTURA',
'COBERTURAS',
'COBERTURA SUBSCRIBER COD',
'COBERTURA SUBSCRIBER DESC',
'PÓLIZA ALFA',
'GRUPO GIRO ALFA DESC',
'GRUPO GIRO ALFA COD',
'Evento PIM\'s',
'TIPO COBERTURA',
'RAMO COD',
'RAMO DESC',
'RAMO SUBSCRIBER COD',
'RAMO SUBSCRIBER DESC',
'COBERTURAS SUNSYSTEM',
'Reglas de negocio',
'Grupo relgas de negocio',
'ASEGURADO',
'BENEFICIARIOS',
'Tiempo límite de Transacción, Bloqueo, límite de Hurto o Daño',
'Unidad de Tiempo Item Columna O',
'Periodo de Carencia',
'Dias de continuidad laboral',
'Numero de limites de eventos por vigencia',
'CUOTAS',
'PRESCRIPCION DIAS',
'PRESCRIPCION',
'Edad minima de ingreso',
'Edad maxima de ingreso',
'Edad maxima de permanencia',
'Dias Minimos de Incapacidad',
'Dias Maximo de Incapacidad',
'Permitir pago de siniestros con concurrencia de eventos',
'Cobertura para Preexistecias',
'Como establecer valor a indemnizar',
'Forma de pago del siniestro',
'Forma de pago del remanente',
'Número de cuotas a pagar por siniestro',
'Entidad Bancaria a la cual se paga los siniestros al Banco/Socio',
'Tipo de Cuenta',
'Numero de Cuenta',
'Cierre automático del siniestro después del ultimo pago?',
]
cobertura = pd.DataFrame(columns=titlesCobertura)
for i, row in totalCoberturasAgrupadas.iterrows():
cobertura.loc[i] = ''
# -- INICIO Columnas fijas
# cobertura['PRODUCTO'][i] = '=+Configuracion!C' + str(row['i'] + 3)
cobertura['COD REGISTRO'][i] = '=+Configuracion!A' + str(row['i2'] + 3)
# cobertura['PRODUCTO'][i] = row['ProductoNombre']
cobertura['COD COBERTURA'][i] = '=VLOOKUP(E' + str(
i + 3) + ',cober.info,3,0)'
cobertura['COBERTURA SUBSCRIBER COD'][
i] = '=IFERROR(IF("REASEGURO CESIÓN PARCIAL"<>"REASEGURO TPA","", VLOOKUP(D' + str(
i + 3) + ',cober.alfa.info,2,0)),"")'
cobertura['COBERTURA SUBSCRIBER DESC'][
i] = '=IFERROR(IF("REASEGURO CESIÓN PARCIAL"<>"REASEGURO TPA","", VLOOKUP(D' + str(
i + 3) + ',cober.alfa.info,3,0)),"")'
cobertura['GRUPO GIRO ALFA COD'][i] = '=IFERROR(VLOOKUP(I' + str(
i + 3) + ',grupoGiroAlfa.info,2,0),"No Aplica")'
cobertura['Evento PIM\'s'][i] = '=VLOOKUP(E' + str(
i + 3) + ',cober.info,5,0)'
cobertura['RAMO DESC'][i] = '=VLOOKUP(M' + str(i +
3) + ',Ramo.Info,4,0)'
# cobertura['RAMO SUBSCRIBER COD'][i] = '=IFERROR(IF("REASEGURO CESIÓN PARCIAL"<>"REASEGURO TPA","", VLOOKUP(M' + str(i + 3) + ',Ramo.Info,6,0)),"")'
# cobertura['RAMO SUBSCRIBER DESC'][i] = '=IFERROR(IF("REASEGURO CESIÓN PARCIAL"<>"REASEGURO TPA","", VLOOKUP(M' + str(i + 3) + ',Ramo.Info,7,0)),"")'
cobertura['RAMO SUBSCRIBER COD'][i] = '=IF(VLOOKUP(VLOOKUP(C' + str(
i + 3
) + ',Configuracion!C:E,3,0),tercero.info,14,0)="Reaseguro Perú",VLOOKUP(M' + str(
i + 3
) + ',Ramo.Info,7,0),IF(VLOOKUP(VLOOKUP(C' + str(
i + 3
) + ',Configuracion!C:E,3,0),tercero.info,14,0)="Reaseguro AVAL",VLOOKUP(M' + str(
i + 3) + ',Ramo.Info,5,0),"No Aplica"))'
cobertura['RAMO SUBSCRIBER DESC'][i] = '=IF(VLOOKUP(VLOOKUP(C' + str(
i + 3
) + ',Configuracion!C:E,3,0),tercero.info,14,0)="Reaseguro Perú",VLOOKUP(M' + str(
i + 3
) + ',Ramo.Info,8,0),IF(VLOOKUP(VLOOKUP(C' + str(
i + 3
) + ',Configuracion!C:E,3,0),tercero.info,14,0)="Reaseguro AVAL",VLOOKUP(M' + str(
i + 3) + ',Ramo.Info,6,0),"No Aplica"))'
cobertura['COBERTURAS SUNSYSTEM'][i] = '=VLOOKUP(E' + str(
i + 3) + ',cober.info,4,0)'
cobertura['Grupo relgas de negocio'][i] = '=CONCATENATE("FAM",D' + str(
i + 3) + ',"-",R' + str(i + 3) + ')'
cobertura['PRESCRIPCION DIAS'][i] = '=IF(Z' + str(
i + 3) + '="EXTRAORDINARIA",1825,IF(Z' + str(
i + 3) + '="ORDINARIA",730,0))'
cobertura['PRESCRIPCION'][i] = '=VLOOKUP(E' + str(
i + 3) + ',cober.info,6,0)'
# -- FIN Columnas fijas
# cobertura['PRODUCTO'][i] = row['CodProductoTEMP']
cobertura['COBERTURAS'][i] = row['Cobertura']
# cobertura['COD COBERTURA'][i] = row['CodCobertura']
cobertura['TIPO COBERTURA'][i] = row['TIP_COB']
cobertura['RAMO COD'][i] = int(row['RAM_COD'])
cobertura['Reglas de negocio'][i] = row['REG_NEG']
cobertura['ASEGURADO'][i] = 'Persona Titular del producto financiero'
cobertura['BENEFICIARIOS'][i] = row['BEN']
cobertura[
'Tiempo límite de Transacción, Bloqueo, límite de Hurto o Daño'][
i] = row['TiempoLimite']
cobertura['Unidad de Tiempo Item Columna O'][i] = row['UnidadDeTiempo']
cobertura['Periodo de Carencia'][i] = row['PER_CAR']
cobertura['Dias de continuidad laboral'][i] = row['DiasContLaboral']
cobertura['Numero de limites de eventos por vigencia'][i] = row[
'LIM_EVE']
cobertura['CUOTAS'][i] = row['CUO']
# -- Edad minima y maxima de ingreso
EdadMinimaSuscripcion = DataProductos.loc[
DataProductos['Código Producto'] == str(
row['Producto'])]['Edad mínima suscripción'].reset_index(
drop=True)[0]
EdadMaximaSuscripcion = DataProductos.loc[
DataProductos['Código Producto'] == str(
row['Producto'])]['Edad máxima suscripción'].reset_index(
drop=True)[0]
EdadMinimaSuscripcion = re.findall('[1-9][1-9] años',
EdadMinimaSuscripcion, re.DOTALL)
EdadMaximaSuscripcion = re.findall('[1-9][1-9] años',
EdadMaximaSuscripcion, re.DOTALL)
cobertura['Edad minima de ingreso'][i] = int(
EdadMinimaSuscripcion[0].replace(' años', ''))
cobertura['Edad maxima de ingreso'][i] = int(
EdadMaximaSuscripcion[0].replace(' años', ''))
# -- Edad maxima de permanencia
EdadPermanencia = DataProductos.loc[
DataProductos['Código Producto'] == str(
row['Producto'])]['Edad permanencia'].reset_index(drop=True)[0]
cobertura['Edad maxima de permanencia'][i] = int(
EdadPermanencia.replace(' años + 364 días', ''))
# -- Dias Minimos de Incapacidad (Solo aplica para '%Incapacidad Total Temporal%', se coloca 15)
cobertura['Dias Minimos de Incapacidad'][i] = row[
'DiasMinimosDeIncapacidad']
# -- Dias Maximo de Incapacidad (Solo aplica para itt, se coloca 1000)
cobertura['Dias Maximo de Incapacidad'][i] = row[
'DiasMaximoDeIncapacidad']
cobertura['Cobertura para Preexistecias'][i] = 'NO'
cobertura['Como establecer valor a indemnizar'][i] = row['VIN']
cobertura['Forma de pago del siniestro'][i] = row['FPS']
cobertura['Forma de pago del remanente'][i] = 'No Aplica'
cobertura['Número de cuotas a pagar por siniestro'][i] = row['CUO']
cobertura[
'Entidad Bancaria a la cual se paga los siniestros al Banco/Socio'][
i] = row['BEN']
# ---------------------------------------------------------------------------------------------------------------------------------- #
# ----------------------------------------------------------- Tarifa --------------------------------------------------------------- #
# ---------------------------------------------------------------------------------------------------------------------------------- #
titlesTarifa = [
'COD REGISTRO', 'Version', 'Producto', 'Plan', 'Rango Edad Ini',
'Rango Edad Fin', 'Cobetura Codigo', 'Cobetura Descripción', 'Ramo',
'Fecha', 'Valor Min', 'Valor Max', 'Gross %', 'Taxed %', 'Net %',
'Tax %', 'Gross Cob', 'Net Cob', 'Tax Cob', 'Gross Total', 'Net Total',
'Tax Total', 'PRIMA / TASA - Gross %', 'PRIMA / TASA - Net %',
'% IVA Ponderado', 'Ramo 09 Sustracción', 'Ramo 24 Desempleo',
'Ramo 31 Accidentes Personales', 'Ramo 34 Vida Grupo',
'Ramo 03 Automóviles', 'Ramo 25 Hogar',
'Valores Asegurados Producto - Valor Min',
'Valores Asegurados Producto - Valor Max'
]
Tarifa = pd.DataFrame(columns=titlesTarifa)
for i, row in totalCoberturas.iterrows():
Tarifa.loc[i] = ''
# -- INICIO Columnas fijas
# Tarifa['Producto'][i] = '=+Configuracion!C' + str(row['i'] + 3)
Tarifa['COD REGISTRO'][i] = '=+Configuracion!A' + str(row['i2'] + 3)
# Tarifa['Producto'][i] = row['ProductoNombre']
Tarifa['Plan'][i] = row['Plan']
Tarifa['Gross %'][i] = '=ROUND(N' + str(
i + 3) + '/SUMIFS(N:N,C:C,C' + str(i +
3) + ',D:D,D' + str(i +
3) + '),8)'
Tarifa['Taxed %'][i] = '=O' + str(i + 3) + '*(1+P' + str(i + 3) + ')'
Tarifa['Tax %'][i] = '=VLOOKUP(I' + str(i + 3) + ',Ramo.Info,5,0)'
#Tarifa['Gross Cob'][i] = '=ROUND(R' + str(i + 3) + '+S' + str(i + 3) + ',8)'
Tarifa['Gross Cob'][i] = '=IFERROR(ROUND(T' + str(i + 3) + '*M' + str(
i + 3) + ',8),0)'
#Tarifa['Net Cob'][i] = '=ROUND(U$' + str(i + 3) + '*O' + str(i + 3) + ',8)'
Tarifa['Net Cob'][i] = '=ROUND(Q' + str(i +
3) + '/(1+P' + str(i +
3) + '),8)'
#Tarifa['Tax Cob'][i] = '=ROUND(U$' + str(i + 3) + ' * P' + str(i + 3) + ' * O' + str(i + 3) + ',8)'
Tarifa['Tax Cob'][i] = '=ROUND(Q' + str(i + 3) + '-R' + str(i +
3) + ',8)'
#Tarifa['Net Total'][i] = '=IF(T' + str(i + 3) + '>0,ROUND(T' + str(i + 3) + '/SUM(N' + str(i + 3) + ':N' + str(i + 3) + '),8),0)'
Tarifa['Net Total'][i] = '=ROUND(SUM(R' + str(i + 3) + ':R' + str(
i + 3) + '),8)'
#Tarifa['Tax Total'][i] = '=ROUND(T' + str(i + 3) + '-U' + str(i + 3) + ',8)'
Tarifa['Tax Total'][i] = '=ROUND(SUM(S' + str(i + 3) + ':S' + str(
i + 3) + '),8)'
Tarifa['PRIMA / TASA - Gross %'][i] = '=SUM(M' + str(
i + 3) + ':M' + str(i + 3) + ')'
Tarifa['PRIMA / TASA - Net %'][i] = '=SUM(O' + str(i + 3) + ':O' + str(
i + 3) + ')'
Tarifa['% IVA Ponderado'][i] = '=SUMPRODUCT(O' + str(
i + 3) + ':O' + str(i + 3) + ',P' + str(i +
3) + ':P' + str(i +
3) + ')'
Tarifa['Ramo 09 Sustracción'][i] = '=SUMIF($I' + str(
i + 3) + ':$I' + str(i + 3) + ',"9",$O' + str(i + 3) + ':$O' + str(
i + 3) + ')'
Tarifa['Ramo 24 Desempleo'][i] = '=SUMIF($I' + str(
i + 3) + ':$I' + str(i + 3) + ',"24",$O' + str(
i + 3) + ':$O' + str(i + 3) + ')'
Tarifa['Ramo 31 Accidentes Personales'][i] = '=SUMIF($I' + str(
i + 3) + ':$I' + str(i + 3) + ',"31",$O' + str(
i + 3) + ':$O' + str(i + 3) + ')'
Tarifa['Ramo 34 Vida Grupo'][i] = '=SUMIF($I' + str(
i + 3) + ':$I' + str(i + 3) + ',"34",$O' + str(
i + 3) + ':$O' + str(i + 3) + ')'
Tarifa['Ramo 03 Automóviles'][i] = '=SUMIF($I' + str(
i + 3) + ':$I' + str(i + 3) + ',"3",$O' + str(i + 3) + ':$O' + str(
i + 3) + ')'
Tarifa['Ramo 25 Hogar'][i] = '=SUMIF($I' + str(i + 3) + ':$I' + str(
i + 3) + ',"25",$O' + str(i + 3) + ':$O' + str(i + 3) + ')'
Tarifa['Valores Asegurados Producto - Valor Min'][i] = '=MIN(K' + str(
i + 3) + ':K' + str(i + 3) + ')'
Tarifa['Valores Asegurados Producto - Valor Max'][i] = '=SUM(L' + str(
i + 3) + ':L' + str(i + 3) + ')'
# -- FIN Columnas fijas
# Tarifa['Producto'][i] = row['CodProductoTEMP']
Tarifa['Cobetura Codigo'][i] = row['CodCobertura']
Tarifa['Cobetura Descripción'][i] = row['Cobertura']
# -- Edad minima y maxima de ingreso
EdadMinimaSuscripcion = DataProductos.loc[
DataProductos['Código Producto'] == str(
row['Producto'])]['Edad mínima suscripción'].reset_index(
drop=True)[0]
EdadMaximaSuscripcion = DataProductos.loc[
DataProductos['Código Producto'] == str(
row['Producto'])]['Edad máxima suscripción'].reset_index(
drop=True)[0]
EdadMinimaSuscripcion = re.findall('[1-9][1-9] años',
EdadMinimaSuscripcion, re.DOTALL)
EdadMaximaSuscripcion = re.findall('[1-9][1-9] años',
EdadMaximaSuscripcion, re.DOTALL)
Tarifa['Rango Edad Ini'][i] = int(EdadMinimaSuscripcion[0].replace(
' años', ''))
Tarifa['Rango Edad Fin'][i] = int(EdadMaximaSuscripcion[0].replace(
' años', ''))
Tarifa['Ramo'][i] = int(row['RAM_COD'])
# -- Calcular cuota
if '+' in str(row['CUO']):
CUOta = row['CUO'].split('+')
CUOmin = float(CUOta[0])
CUOmax = float(CUOta[1]) + float(CUOta[0])
Valor_Min = CUOmin * row['VLR_ASE']
Valor_Max = CUOmax * row['VLR_ASE']
else:
Valor_Min = row['VLR_ASE']
Valor_Max = row['VLR_ASE']
Tarifa['Valor Min'][i] = Valor_Min
Tarifa['Valor Max'][i] = Valor_Max
Tarifa['Net %'][i] = row['DIS_IVA']
PrimaCliente = DataProductos.loc[
DataProductos['Código Producto'] == str(
row[0])]['Prima Cliente'].reset_index(drop=True)
Tarifa['Gross Total'][i] = PrimaCliente[0]
# ---------------------------------------------------------------------------------------------------------------------------------- #
# ---------------------------------------------------------- Comisión -------------------------------------------------------------- #
# ---------------------------------------------------------------------------------------------------------------------------------- #
titlesComision = [
'COD REGISTRO', 'Version', 'Producto', 'Tipo Tercero',
'Nombre Tercero', 'NIT', 'RAZÓN SOCIAL', 'Tipo Movimiento',
'Fecha Inicio', 'Fecha Fin', 'BASE CALCULO COMISIONES',
'Comision antes de IVA', 'IVA', 'Comision despues de IVA'
]
Comision = pd.DataFrame(columns=titlesComision)
contador = 0
for i, row in productosAgrupados.iterrows():
# -- Calcular números de terceros
# -- Cant. Terceros Comisión
TercerosConComision = DataProductos.loc[
DataProductos['Código Producto'] == str(
row[0])]['Terceros con comision'].reset_index(drop=True)[0]
for numTer in range(TercerosConComision):
contador = contador + numTer
Comision.loc[i + contador] = ''
# -- INICIO Columnas fijas
Comision['Producto'][i +
contador] = '=+Configuracion!C' + str(i + 3)
Comision['NIT'][i + contador] = '=VLOOKUP(E' + str(
i + contador + 3) + ', tercero.info,11,0)'
Comision['RAZÓN SOCIAL'][i + contador] = '=VLOOKUP(E' + str(
i + contador + 3) + ', tercero.info,12,0)'
Comision['IVA'][i + contador] = np.where(
"Alfa" in Input.loc[Input['Variable'] == 'Socio']
['Valor'].reset_index(drop=True)[0], 0, 1.19)
# -- FIN Columnas fijas
#Comision['COD REGISTRO'][i] = '=+Configuracion!A' + str(row['i2'] + 3)
if numTer == 0: # -- DATOS DEL PRIMER TERCERO
Comision['Tipo Tercero'][i + contador] = 'Socio'
Comision['Nombre Tercero'][i + contador] = Input.loc[
Input['Variable'] == 'Socio']['Valor'].reset_index(
drop=True)[0]
# -- Comision antes de IVA
comision = DataProductos.loc[
DataProductos['Código Producto'] == str(
row[0])]['Comisión Socio'].reset_index(drop=True)[0]
Comision['Comision antes de IVA'][i + contador] = comision
Comision['Comision despues de IVA'][
i + contador] = Comision['Comision antes de IVA'][
i + contador] * (Comision['IVA'][i + contador])
elif numTer == 1: # -- DATOS DEL SEGUNDO TERCERO
Comision['Tipo Tercero'][i + contador] = Input.loc[
Input['Variable'] ==
'Tipo de Tercero 1']['Valor'].reset_index(drop=True)[0]
Comision['Nombre Tercero'][i + contador] = Input.loc[
Input['Variable'] == 'Intermediario']['Valor'].reset_index(
drop=True)[0]
# -- Comision antes de IVA
comision = DataProductos.loc[
DataProductos['Código Producto'] == str(
row[0])]['Comisión Intermediario'].reset_index(
drop=True)[0]
Comision['Comision antes de IVA'][i +
contador] = comision / 1.19
Comision['Comision despues de IVA'][i + contador] = comision
elif numTer == 2: # -- DATOS DEL TERCER TERCERO
Comision['Tipo Tercero'][i + contador] = Input.loc[
Input['Variable'] ==
'Tipo de Tercero 2']['Valor'].reset_index(drop=True)[0]
Comision['Nombre Tercero'][i + contador] = Input.loc[
Input['Variable'] == 'Facilitador']['Valor'].reset_index(
drop=True)[0]
# -- Comision antes de IVA
comision = DataProductos.loc[
DataProductos['Código Producto'] == str(
row[0])]['Comisión Facilitador'].reset_index(
drop=True)[0]
Comision['Comision antes de IVA'][i +
contador] = comision / 1.19
Comision['Comision despues de IVA'][i + contador] = comision
Comision['BASE CALCULO COMISIONES'][i + contador] = 'Prima Neta'
# -- IVA
Comision['IVA'][i + contador] = np.where(
"Alfa" in Input.loc[Input['Variable'] == 'Socio']
['Valor'].reset_index(drop=True)[0], 0, 0.19)
# ( Siempre es 19% excepto en alfa )
# ------------------------------ #
# ------- Imprimir excel ------- #
# ------------------------------ #
# -- Leer archivo base (Plantilla implementación)
pathBook = os.path.join(
os.path.dirname(os.path.dirname(__file__)),
'../static/pricing/business_plan_templates/Matriz_de_configuracion3.xlsx'
)
book = load_workbook(pathBook)
# file_output = 'Formato_ID_' + str(id) + '_' + time.strftime("%Y_%m_%d_%H_%M_%S") + '.xlsx'
file_output = 'Matriz_de_configuración_ID_' + str(id) + '.xlsx'
path2 = os.path.join(
os.path.dirname(os.path.dirname(__file__)),
'../static/pricing/formato_implementacion/' + file_output)
writer = pd.ExcelWriter(path2, engine='openpyxl')
writer.book.strings_to_formulas = True
# -- Agregar hojas de archivo base
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
# -- Imprimir datos calculados
configuracion.to_excel(writer,
sheet_name='Configuracion',
index=None,
float_format='%.15f',
startrow=2,
header=False)
cobertura.to_excel(writer,
sheet_name='Cobertura',
index=None,
float_format='%.15f',
startrow=2,
header=False)
Tarifa.to_excel(writer,
sheet_name='Tarifa',
index=None,
float_format='%.15f',
startrow=2,
header=False)
Comision.to_excel(writer,
sheet_name='Comision',
index=None,
float_format='%.15f',
startrow=2,
header=False)
protectionStyle = Protection(locked=False)
# ---------------------------------------------------------------------- #
# ------- Desbloquear celdas para implementación | Configuración ------- #
# ---------------------------------------------------------------------- #
ws = writer.sheets['Configuracion']
ColumnasDesbloqueadas = {
'A', 'B', 'C', 'D', 'F', 'G', 'H', 'I', 'J', 'K', 'P', 'Q', 'R', 'U',
'V', 'W', 'X', 'Y', 'Z', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH',
'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AQ', 'AS', 'AW', 'AX', 'AY', 'AZ',
'BA', 'BB', 'BC', 'BD', 'BE', 'BF'
}
for i in ColumnasDesbloqueadas:
for cell in ws[i]:
cell.protection = protectionStyle
# ------------------------------------------------------------------ #
# ------- Desbloquear celdas para implementación | Cobertura ------- #
# ------------------------------------------------------------------ #
ws = writer.sheets['Cobertura']
ColumnasDesbloqueadas = {
'A', 'B', 'C', 'D', 'F', 'G', 'H', 'I', 'J', 'K', 'N', 'O', 'P', 'Q',
'S', 'AB', 'AC', 'AI', 'AK', 'AL', 'AM', 'AP', 'AQ', 'AR'
}
for i in ColumnasDesbloqueadas:
for cell in ws[i]:
cell.protection = protectionStyle
# --------------------------------------------------------------- #
# ------- Desbloquear celdas para implementación | Tarifa ------- #
# --------------------------------------------------------------- #
ws = writer.sheets['Tarifa']
ColumnasDesbloqueadas = {
'A', 'B', 'C', 'D', 'J', 'M', 'N', 'P', 'Q', 'R', 'S', 'U', 'V', 'W',
'X', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG'
}
for i in ColumnasDesbloqueadas:
for cell in ws[i]:
cell.protection = protectionStyle
# ----------------------------------------------------------------- #
# ------- Desbloquear celdas para implementación | Comisión ------- #
# ----------------------------------------------------------------- #
ws = writer.sheets['Comision']
ColumnasDesbloqueadas = {'A', 'B', 'C', 'F', 'G', 'H', 'I', 'J'}
for i in ColumnasDesbloqueadas:
for cell in ws[i]:
cell.protection = protectionStyle
# -- Formato para valores en hoja tarifa
ws = writer.sheets['Tarifa']
ColumnasDesbloqueadas = {
'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X',
'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG'
}
for i in ColumnasDesbloqueadas:
for cell in ws[i]:
cell.number_format = '0.00000000'
writer.save()
# -- Imprimir formato
with open(path2, 'rb') as fh:
response = HttpResponse(fh.read(),
content_type="application/vnd.ms-excel")
response[
'Content-Disposition'] = 'inline; filename=' + os.path.basename(
path2)
return response
def unlock_forecast_cells(ws, row, start, end):
for c in range(start, end):
ws.cell(column=c, row=row).protection = Protection(locked=False)
def __init__(self, cell_css):
info = {}
for key in cell_css:
value = cell_css[key].replace(', ', ',')
lst = value.split(' ')
ignore = [
'width', 'word-break', 'border-collapse', 'border-spacing',
'border-top-style', 'padding', 'margin'
]
if key in ignore:
continue
elif key == 'background-color':
info['start_color'] = self.rgb2hex(value)
elif key == 'text-align':
info['horizontal'] = value
elif key == 'vertical-align':
if value == 'middle':
value = 'center'
info['vertical'] = value
elif key == 'font':
info['fname'] = lst[4][1:-1]
info['fsize'] = int(lst[3][:-2])
if lst[2] == 'bold':
info['fbold'] = True
elif key == 'border-left':
lst[1] = self.convert_boder_style(lst[1], lst[0])
info['blbs'] = lst[1]
info['blc'] = self.rgb2hex(lst[2])
elif key == 'border-right':
lst[1] = self.convert_boder_style(lst[1], lst[0])
info['brbs'] = lst[1]
info['brc'] = self.rgb2hex(lst[2])
elif key == 'border-top':
lst[1] = self.convert_boder_style(lst[1], lst[0])
info['btbs'] = lst[1]
info['btc'] = self.rgb2hex(lst[2])
elif key == 'border-bottom':
lst[1] = self.convert_boder_style(lst[1], lst[0])
info['bbbs'] = lst[1]
info['bbc'] = self.rgb2hex(lst[2])
elif key == 'color':
info['fc'] = self.rgb2hex(value)
else:
logging.debug("Unknown css attribute '%s'", key)
self.font = Font(name=info.get('fname', 'Courier'),
size=info.get('fsize', 10),
bold=info.get('fbold', False),
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=info.get('fc', 'FF000000'))
self.fill = PatternFill(fill_type=FILL_SOLID,
start_color=info.get('start_color',
'FFFFFFFF'),
end_color='FF000000')
self.border = Border(left=Side(border_style=info.get('blbs', None),
color=info.get('blc', 'FF000000')),
right=Side(border_style=info.get('brbs', None),
color=info.get('brc', 'FF000000')),
top=Side(border_style=info.get('btbs', None),
color=info.get('btc', 'FF000000')),
bottom=Side(border_style=info.get('bbbs', None),
color=info.get('bbc', 'FF000000')),
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'))
self.alignment = Alignment(horizontal=info.get('horizontal',
'general'),
vertical=info.get('vertical', 'bottom'),
text_rotation=0,
wrap_text=True,
shrink_to_fit=True,
indent=0)
self.number_format = 'General'
self.protection = Protection(locked=False, hidden=False)
outline=Side(
border_style="double", #外框
color='000000'),
vertical=Side(
border_style="thin", #内部垂直框？
color='000000'),
horizontal=Side(
border_style="thin", #内部水平框？
color='000000'))
alignment = Alignment(
horizontal='general', #水平对齐方式
vertical='bottom', #垂直对齐方式
text_rotation=0, #旋转文本
wrap_text=False, #自动换行
shrink_to_fit=False, #收缩到合适尺寸
indent=0) #缩进
number_format = 'General' #数字格式
protection = Protection(
locked=True, #保护？锁定
hidden=False) #保护？隐藏
from openpyxl import Workbook, load_workbook
filename = "filtered.xlsx"
wb = load_workbook(filename)
ws = wb.active
ws["c5"].border = border #:B15
ws["E5"].border = border #:G8
left, right, top, bottom = [Side(style='double', color='000000')] * 4
ws['g8'].border = Border(left=left, right=right, top=top, bottom=bottom)
wb.save(filename)
class ExcelFontStyle:
"""
excel字体风格，背景
"""
__default_style__ = NamedStyle('default')
__default_style__.border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
__default_style__.font = Font(size=12)
__default_style__.alignment = Alignment(
horizontal='center',
vertical='center',
)
# header_style
__header_style__ = NamedStyle("header_style")
__header_style__.font = Font(bold=True, size=18)
__header_style__.fill = PatternFill(
fill_type='solid',
start_color='00C0C0C0',
end_color='00C0C0C0',
)
__header_style__.border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
__header_style__.alignment = Alignment(
horizontal='center',
vertical='center',
)
__header_style__.number_format = 'General'
#openpyxl默认样式
__openpyxl_default_style__ = NamedStyle("openpyxl_style")
__openpyxl_default_style__.font = Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
__openpyxl_default_style__.fill = PatternFill(fill_type=None,
start_color='FFFFFFFF',
end_color='FF000000')
__openpyxl_default_style__.border = Border(left=Side(border_style=None,
color='FF000000'),
right=Side(border_style=None,
color='FF000000'),
top=Side(border_style=None,
color='FF000000'),
bottom=Side(border_style=None,
color='FF000000'),
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'))
__openpyxl_default_style__.alignment = Alignment(horizontal='general',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
__openpyxl_default_style__.number_format = 'General'
__openpyxl_default_style__.protection = Protection(locked=True,
hidden=False)
@classmethod
def get_default_style(self):
"""
设置单元格格式，其中字体及背景为默认
#TODO 表格样式和筛选
:return:
"""
return self.__default_style__
@classmethod
def get_openpyxl_default_style(self):
return self.__openpyxl_default_style__
@classmethod
def get_header_style(self):
return self.__header_style__
def xls_style_to_xlsx(self, xf_ndx):
"""Convert an xls xf_ndx into a 6-tuple of styles for xlsx"""
font = Font()
fill = PatternFill()
border = Border()
alignment = Alignment()
number_format = 'General'
protection = Protection(locked=False, hidden=False)
if xf_ndx < len(self.book.xf_list):
xf = self.book.xf_list[xf_ndx]
xls_font = self.book.font_list[xf.font_index] # Font object
font.b = xls_font.bold
font.i = xls_font.italic
if xls_font.character_set:
font.charset = xls_font.character_set
font.color = self.xls_color_to_xlsx(xls_font.colour_index)
escapement = xls_font.escapement # 1=Superscript, 2=Subscript
family = xls_font.family # FIXME: 0=Any, 1=Roman, 2=Sans, 3=monospace, 4=Script, 5=Old English/Franktur
font.name = xls_font.name
font.sz = self.xls_height_to_xlsx(xls_font.height) # A twip = 1/20 of a pt
if xls_font.struck_out:
font.strike = xls_font.struck_out
if xls_font.underline_type:
font.u = ('single', 'double')[(xls_font.underline_type&3)-1]
xls_format = self.book.format_map[xf.format_key] # Format object
number_format = xls_format.format_str
if False: # xlrd says all cells are locked even if the sheet isn't protected!
protection.locked = xf.protection.cell_locked
protection.hidden = xf.protection.formula_hidden
fill_patterns = {0x00:'none', 0x01:'solid', 0x02:'mediumGray', 0x03:'darkGray', 0x04:'lightGray',
0x05:'darkHorizontal', 0x06:'darkVertical', 0x07:'darkDown', 0x08:'darkUp', 0x09:'darkGrid',
0x0A:'darkTrellis', 0x0B:'lightHorizontal', 0x0C:'lightVertical', 0x0D:'lightDown', 0x0E:'lightUp',
0x0F:'lightGrid', 0x10:'lightTrellis', 0x11:'gray125', 0x12:'gray0625'
}
fill_pattern = xf.background.fill_pattern
fill_background_color = self.xls_color_to_xlsx(xf.background.background_colour_index)
fill_pattern_color = self.xls_color_to_xlsx(xf.background.pattern_colour_index)
fill.patternType = fill_patterns.get(fill_pattern, 'none')
fill.bgColor = fill_background_color
fill.fgColor = fill_pattern_color
horizontal = {0:'general', 1:'left', 2:'center', 3:'right', 4:'fill', 5:'justify', 6:'centerContinuous', 7:'distributed'}
vertical = {0:'top', 1:'center', 2:'bottom', 3:'justify', 4:'distributed'}
hor_align = horizontal.get(xf.alignment.hor_align, None)
if hor_align:
alignment.horizontal = hor_align
vert_align = vertical.get(xf.alignment.vert_align, None)
if vert_align:
alignment.vertical = vert_align
alignment.textRotation = xf.alignment.rotation
alignment.wrap_text = xf.alignment.text_wrapped
alignment.indent = xf.alignment.indent_level
alignment.shrink_to_fit = xf.alignment.shrink_to_fit
border_styles = {0: None, 1:'thin', 2:'medium', 3:'dashed', 4:'dotted',
5:'thick', 6:'double', 7:'hair', 8:'mediumDashed', 9:'dashDot',
10:'mediumDashDot', 11:'dashDotDot', 12:'mediumDashDotDot',
13:'slantDashDot',}
xls_border = xf.border
top = Side(style=border_styles.get(xls_border.top_line_style), color=self.xls_color_to_xlsx(xls_border.top_colour_index))
bottom = Side(style=border_styles.get(xls_border.bottom_line_style), color=self.xls_color_to_xlsx(xls_border.bottom_colour_index))
left = Side(style=border_styles.get(xls_border.left_line_style), color=self.xls_color_to_xlsx(xls_border.left_colour_index))
right = Side(style=border_styles.get(xls_border.right_line_style), color=self.xls_color_to_xlsx(xls_border.right_colour_index))
diag = Side(style=border_styles.get(xls_border.diag_line_style), color=self.xls_color_to_xlsx(xls_border.diag_colour_index))
border.top = top
border.bottom = bottom
border.left = left
border.right = right
border.diagonal = diag
border.diagonalDown = xls_border.diag_down
border.diagonalUp = xls_border.diag_up
return (font, fill, border, alignment, number_format, protection)
def create(self):
# design a weight checking later
markfile_col_list = [
'regno', 'enname', 'chname', 'sex', 'classlevel', 'classcode',
'classno'
]
markfile_col = {key: i + 1 for i, key in enumerate(markfile_col_list)}
lock_range = {
'chi': {
'ut1': [15, 1],
'daily1': [15, 9],
'exam1': [16, 4],
'ut2': [15, 1],
'daily2': [15, 9],
'exam2': [16, 4],
},
'eng': {
'ut1': [8, 6],
'daily1': [0, 0],
'exam1': [9, 12],
'ut2': [8, 6],
'daily2': [0, 0],
'exam2': [9, 12],
},
'oth': {
'ut1': [11, 1],
'daily1': [0, 0],
'exam1': [10, 2],
'ut2': [11, 1],
'daily2': [0, 0],
'exam2': [10, 2],
},
}
print('create files for', self.exam_year)
print('load student info and exam master file')
# load student subjects as dataframe
self.load_stuinfo()
# print(self.stuinfo_df)
self.load_run()
# print(len(self.exam_class_list))
# build folders first
markfile_src_root_folder = os.path.join(self.assessment_root_folder,
'markfile_src')
os.makedirs(markfile_src_root_folder, exist_ok=True)
for folder in ['ut1', 'exam1', 'ut2', 'exam2', 'mock']:
folder_to_create = os.path.join(markfile_src_root_folder, folder)
os.makedirs(folder_to_create, exist_ok=True)
num_of_markfiles = 0
for key, exam_class in self.exam_class_list.items():
class_type = exam_class.class_type
class_level = exam_class.classlevel # s6
group_code = exam_class.groupcode # s6x / x1 / x2 / x3
subject = exam_class.subject
exam_type = exam_class.create
classcode = exam_class.classcode.upper()
teacher = exam_class.tch
# print(exam_class)
# print(exam_class.class_type)
# a classlevel filter for restrictive test run
if exam_class.select == 'O':
if exam_class.classlevel.lower() in [
's1', 's2', 's3', 's4', 's5', 's6'
]:
if exam_class.class_type == 'class':
# filter dte/hec students for s2 and s3
if exam_class.subject.lower() in [
'dte', 'hec'
] and exam_class.classlevel.lower() in ['s2', 's3']:
class_df = self.stuinfo_df[
(self.stuinfo_df.classcode.isin([classcode]))
& (self.stuinfo_df['DH'] == exam_class.subject
)][markfile_col_list]
class_dict = class_df.to_dict('records')
elif exam_class.subject.lower() in ['pedm', 'pedf']:
sex_code = exam_class.subject.lower()[-1]
class_df = self.stuinfo_df[
(self.stuinfo_df.classcode.isin([classcode]))
& (self.stuinfo_df['sex'] == sex_code.upper()
)][markfile_col_list]
class_dict = class_df.to_dict('records')
else:
class_df = self.stuinfo_df[
self.stuinfo_df.classcode.isin(
[classcode])][markfile_col_list]
class_dict = class_df.to_dict('records')
elif exam_class.class_type == 'group':
# print(exam_class.groupcode, exam_class.subject.lower())
if exam_class.groupcode[0] == 'x':
class_df = self.stuinfo_df[
(self.stuinfo_df['classlevel'] ==
exam_class.classlevel.upper())
& (self.stuinfo_df[exam_class.groupcode] ==
exam_class.subject.lower()
)][markfile_col_list]
class_dict = class_df.to_dict('records')
# print(class_dict)
elif exam_class.groupcode[0] == 'g':
# did not decide for the old s6 mth/m1/m2 split classes
# good for new m1 as x1 elective
# print(subject)
# distinguish m1, m2, mth when 'g'
# so that ok for non-x-subject setting of m1/m2
if exam_class.subject == 'mth':
class_df = self.stuinfo_df[
(self.stuinfo_df['classlevel'] ==
exam_class.classlevel.upper())
& (self.stuinfo_df[exam_class.subject.
lower()] == exam_class.
groupcode)][markfile_col_list]
class_dict = class_df.to_dict('records')
elif exam_class.subject in ['m1', 'm2']:
class_df = self.stuinfo_df[
(self.stuinfo_df['classlevel'] ==
exam_class.classlevel.upper())
& (self.stuinfo_df['m'] == exam_class.
subject.lower())][markfile_col_list]
class_dict = class_df.to_dict('records')
else:
class_df = []
else:
print('Error: not class nor group!')
# print(class_dict)
# load mark file template
markfile_template_folder = os.path.join(
self.websams_root_folder, 'setup',
str(self.exam_year) + 'files')
# print(markfile_template_folder, exam_class.subject, exam_class.classlevel.lower(), exam_type)
markfile_template = os.path.join(
markfile_template_folder, exam_class.subject,
exam_class.classlevel.lower() + 'x.xlsx')
# websams\1920\1920exam\markfile_src\{ exam }
# print(self.home_folder, self.full_exam, 'markfile_src', exam_type, markfile_template)
markfile_src_folder = os.path.join(
markfile_src_root_folder, exam_type)
# print(markfile_src_folder)
markfile_wb = openpyxl.load_workbook(
filename=markfile_template)
websams_ws = markfile_wb['websams']
setup_ws = markfile_wb['setup']
# set the file key on setup sheet
setup_ws.cell(column=1, row=2).value = key
setup_ws.cell(column=4,
row=1).value = exam_class.subject.lower()
if exam_class.groupcode in ['x1', 'x2', 'x3', 'g1', 'g2']:
setup_ws.cell(column=3,
row=1).value = exam_class.groupcode
# write student data on websams sheet by loop through class list dict
# ** need to ensure no strange white spaces in names
currow = 5
col_taken = 45
for stu in class_dict:
# print(stu)
for item in markfile_col:
# print(item)
websams_ws.cell(column=markfile_col[item],
row=currow).value = stu[item]
websams_ws.cell(column=col_taken,
row=currow).value = 'O'
currow += 1
# loop all sheets in workbook
# to lock cells
for sheet in markfile_wb.sheetnames:
if sheet in [
'ut1', 'daily1', 'exam1', 'ut2', 'daily2',
'exam2'
]:
# set unlocked cells first
if exam_class.subject.lower() in ['chi', 'eng']:
col_start = lock_range[
exam_class.subject.lower()][sheet][0]
repeat = lock_range[
exam_class.subject.lower()][sheet][1]
else:
col_start = lock_range['oth'][sheet][0]
repeat = lock_range['oth'][sheet][1]
# loop the lock_range range
# print(sheet, colStart, repeat)
for r2 in range(5, 49):
for c2 in range(col_start, col_start + repeat):
markfile_wb[sheet].cell(
column=c2,
row=r2).protection = Protection(
locked=False, hidden=False)
markfile_wb[sheet].protection.password = '******'
markfile_wb[sheet].protection.sheet = True
markfile_wb[sheet].protection.enable()
# build teacher and subject folder first
# for check and create if necessary
teacher_folder = os.path.join(markfile_src_folder,
'teacher')
subject_folder = os.path.join(markfile_src_folder,
'subject')
# os.makedirs(teacher_folder, exist_ok=True)
os.makedirs(subject_folder, exist_ok=True)
# check folder -> teacher or subject
# set the file save folder and filename
# override exam_type for s6 mock
if exam_class.classlevel.lower(
) == 's6' and exam_type == 'mock':
exam_type = 'mock'
mark_filename = self.markfile_name(class_type, exam_type,
class_level, group_code,
subject, teacher)
file_save_folder = os.path.join(subject_folder,
exam_class.subject)
os.makedirs(file_save_folder, exist_ok=True)
file_save = os.path.join(file_save_folder, mark_filename)
# from https://groups.google.com/forum/#!topic/openpyxl-users/Y9_iSeTi3bM
#
# FWIW this is what I used to work around something similar in a file with
# chart sheets and invisible worksheets with data:
#
# wb.views[0].firstSheet = 1
#
# I'd love to be able to explain this but I can't as the specification says
# nothing about grouping worksheets. :-/ If anyone can come up with
# something suitable I'd love to add it to the documentation!
#
# it just does not work.
# k = markfile_wb.views[0].activeTab
# markfile_wb.views[0].firstSheet = k
# print(markfile_wb.views[0].firstSheet, markfile_wb.views[0].activeTab)
num_of_markfiles += 1
print('\t#{} created: {}'.format(str(num_of_markfiles),
file_save))
markfile_wb.save(filename=file_save)
print('total no of markfiles: {}'.format(num_of_markfiles))
def modify_cell_style1(self):
"""
修改单元格样式
openpyxl提供的格式控制方法可以实现对单元格属性所有基本操作
:return:
"""
# 字体格式
# 指定字体类型、大小、是否加粗、颜色等
font0 = Font(name='Calibri',
size=20,
bold=False,
italic=False,
vertAlign=None, # Maybe：'baseline', 'superscript', 'subscript'
underline='none', # Maybe：'single','double','singleAccounting','doubleAccounting'
strike=False,
color='FF00FF00')
# 单元格填充
fill0 = PatternFill(fill_type=None,
# Maybe：'lightUp', 'darkVertical', 'darkGrid', 'solid', 'darkHorizontal', 'darkUp', 'lightVertical', 'lightGray', 'darkTrellis', 'lightDown', 'gray125', 'gray0625', 'mediumGray', 'lightTrellis', 'darkGray', 'darkDown', 'lightHorizontal', 'lightGrid'
start_color='FFFFFFFF',
end_color='FF000000')
# 边框
border0 = Border(left=Side(border_style=None, color='FF000000'),
# style Maybe：'mediumDashDotDot', 'dotted', 'thick', 'medium', 'dashDotDot', 'double', 'dashed', 'mediumDashed', 'dashDot', 'mediumDashDot', 'hair', 'slantDashDot', 'thin'
right=Side(border_style=None, color='FF000000'),
top=Side(border_style=None, color='FF000000'),
bottom=Side(border_style=None, color='FF000000'),
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')
)
# 单元格对齐方式
alignment0 = Alignment(horizontal='center',
# Maybe:'centerContinuous', 'fill', 'right', 'distributed', 'justify', 'general', 'center', 'left'
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
# 表格保护
protection0 = Protection(locked=True,
hidden=False)
# 创建格式
style0 = NamedStyle(name='style_example')
# 格式赋值
style0.font = font0
style0.alignment = alignment0
style0.border = border0
style0.fill = fill0
style0.Protection = protection0
wb = openpyxl.Workbook()
sheet = wb['Sheet']
write_value_to_cell_with_num(sheet, 1, 1, 'xag')
# 设置属性样式
sheet['A1'].font = font0
sheet['A1'].alignment = alignment0
sheet.cell(row=1, column=1).border = border0
# 按名称调用
# sheet['A1'].style = style0
# sheet['A1'].style = 'style_example'
# sheet.cell(row=1, column=1).style = style0
wb.template = False
wb.save('new.xlsx')
for i_crse, crse in df_prg_courses.iterrows():
df_prg_crse_clos = df_clo.loc[(
df_clo['course_id'] == crse['course_id'])]
for i, clo in df_prg_crse_clos.iterrows():
ws_align.cell(row=j_align, column=1).value = plo['plo_nbr']
ws_align.cell(row=j_align, column=2).value = clo['clo_nbr']
ws_align.cell(row=j_align,
column=3).value = crse['course_code']
ws_align.cell(row=j_align,
column=4).alignment = Alignment(wrapText=True)
ws_align.cell(row=j_align, column=4).value = plo['plo_text']
ws_align.cell(row=j_align,
column=5).alignment = Alignment(wrapText=True)
ws_align.cell(row=j_align, column=5).value = clo['clo_text']
ws_align.cell(row=j_align,
column=6).protection = Protection(locked=False)
dv.add(ws_align.cell(row=j_align, column=6))
j_align += 1
# Adjust row heights and column widths
ws = wb["Program Summary"]
ws.column_dimensions['B'].width = 60
for i_row, row in enumerate(ws):
if i_row + 1 > 3 and not i_row + 1 >= 16:
# adjust height of row
ws.row_dimensions[i_row + 1].height = 65
for j_col, cell in enumerate(row):
# centre text
cell.alignment = Alignment(horizontal='center',
vertical='center')
# Wrap column B
right=Side(border_style=None, color='FF000000'),
top=Side(border_style=None, color='FF000000'),
bottom=Side(border_style=None, color='FF000000'),
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'))
alignment = Alignment(horizontal='general',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
number_format = 'General'
protection = Protection(locked=True, hidden=False)
print("hello world")
wb = xl.load_workbook("example.xlsx")
print(type(wb))
print(wb.get_sheet_names())
sheet = wb.get_sheet_by_name("Sheet1")
print(sheet["A1"].value)
i = 1
while sheet.cell(row=i, column=1).value != None:
print(sheet.cell(row=i, column=1).value, sheet.cell(row=i, column=2).value)
sheet.cell(row=i, column=3).value = "=5*" + str(i)
def rename(self, past_exam, next_exam, lock_state=True):
lock_range = {
'chi': {
'ut1': [15, 1],
'daily1': [15, 12],
'exam1': [16, 12],
'ut2': [15, 1],
'daily2': [15, 12],
'exam2': [16, 12]
},
'eng': {
'ut1': [8, 6],
'daily1': [0, 0],
'exam1': [9, 14],
'ut2': [8, 6],
'daily2': [0, 0],
'exam2': [9, 14]
},
'oth': {
'ut1': [11, 1],
'daily1': [0, 0],
'exam1': [10, 2],
'ut2': [11, 1],
'daily2': [0, 0],
'exam2': [10, 2]
},
}
markfile_src_folder = os.path.join(self.assessment_root_folder,
'markfile_src', next_exam)
# from exam_type (= exam_folder)
# also load drop from master run
# unlock websams and update subject for S5
# set path 2021exam\ut1\(subject)
past_exam_folder = os.path.join(self.assessment_root_folder, past_exam,
'merge')
print('past exam:', past_exam_folder)
# load master exam file
exam_run_df = pd.read_excel(self.exam_run_file, sheet_name='run')
exam_run_df = exam_run_df[(exam_run_df[past_exam] == 'O')
& (exam_run_df[next_exam] == 'O')]
file_rename_list = exam_run_df['basename'].to_list()
print(file_rename_list)
# excel_file_tch_dict = dict(zip(exam_run_df.file, exam_run_df.TchV))
# form drop student dict
# need more work on dict
# drop_header = ['term', 'regno', 'chname', 'classlevel', 'classcode', 'classno', 'subject', 'basefile']
# drop_df = pd.read_excel(self.exam_master_file, sheet_name='drop')
# drop_df = drop_df[drop_header]
# drop_file_dict = dict(zip(drop_df.basefile, drop_df.to_dict(orient='records')))
# print(drop_file_dict)
# loop current merge folder
# and save to next exam
current_directory = pathlib.Path(past_exam_folder)
for subject_dir in current_directory.iterdir():
# only loop into sub-folder of merge
if subject_dir.is_dir():
# produce a list of files to loop using os.scandir
with os.scandir(str(subject_dir)) as listOfEntries:
# loop all files in each subject folder
for entry in listOfEntries:
# check if files vs directory
if entry.is_file():
# only open xlsx file
if entry.name[-4:] == 'xlsx':
n = len(self.exam_year + past_exam)
exam_type = entry.name[0:n]
classlevel = entry.name[n:(n + 2)]
base_markfile = entry.name[n:]
base_markfile2 = entry.name[n:-5]
print(base_markfile, base_markfile2)
# subject = (base_markfile[4:-5] if base_markfile[2] == 'x' else base_markfile[3:-5])
if base_markfile[2] in ['x', 'g']:
subject = base_markfile[4:-5]
else:
subject = base_markfile[3:-5]
if base_markfile in file_rename_list:
print(entry.name, subject, classlevel,
past_exam)
# tch = excel_file_tch_dict[base_markfile]
# markfile_save_folder = self.exam_folder + next_exam + sep \
# + 'markfile_src' + sep + 'rename' + sep + tch + sep
markfile_save_folder = os.path.join(
self.exam_home, 'markfile_src',
next_exam, 'rename', subject)
print(markfile_save_folder)
if not os.path.isdir(markfile_save_folder):
os.makedirs(markfile_save_folder)
markfile_save = os.path.join(
markfile_save_folder, self.exam_year +
next_exam + base_markfile)
print(exam_type, base_markfile, next_exam,
markfile_save)
markfile_wb = openpyxl.load_workbook(
filename=entry.path)
# check drop students for nss elective
# if base_markfile2 in drop_file_dict.keys():
# for row in range(5, 49):
# regno = markfile_wb['websams'].cell(row=row, column=1).value
# if regno == drop_file_dict[base_markfile2]['regno']:
# print(drop_file_dict[base_markfile2]['chname'])
# markfile_wb['websams'].cell(row=row, column=45).value = ''
# filter out all previous exams
# rewrite lock state for the cells in those ws
# do not lock future exam
locked_sheet_state = {
'ut1': True,
'daily1': True,
'exam1': True,
'ut2': True,
'daily2': False,
'exam2': False,
}
for ws in [
x
for x in locked_sheet_state.keys()
if locked_sheet_state[x]
]:
# set lock_range cells first
if subject.lower() in ['chi', 'eng']:
col_start = lock_range[
subject.lower()][ws][0]
repeat = lock_range[
subject.lower()][ws][1]
else:
col_start = lock_range['oth'][ws][
0]
repeat = lock_range['oth'][ws][1]
# loop the lock_range range
# print(sheet, colStart, repeat)
# sheet_state = locked_sheet_state[ws]
for r2 in range(5, 49):
for c2 in range(
col_start,
col_start + repeat):
markfile_wb[ws].cell(
column=c2, row=r2
).protection = Protection(
locked=True, hidden=False)
# loop all ws to update lock state of each ws with password
for ws in markfile_wb.sheetnames:
markfile_wb[
ws].protection.password = '******'
markfile_wb[
ws].protection.sheet = False
if lock_state:
markfile_wb[ws].protection.enable()
else:
markfile_wb[ws].protection.disable(
)
markfile_wb.save(markfile_save)
markfile_wb.close()
