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