def SetCell(ws,wsRow,wsCol,val,fmt): align = None fill = None numFmt = None border = None c = ws.cell(row=wsRow,column=wsCol) for i in fmt: #logging.debug(i) if (i == 'hAlign'): if (not align): align = Alignment() align.horizontal = alignType[fmt[i]] if (i == 'vAlign'): if (not align): align = Alignment() align.vertical = alignType[fmt[i]] if (i == 'wrap'): if (not align): align = Alignment() if (fmt[i] == '1'): align.wrap_text = 1 else: align.wrap_text = 0 if (i == 'border'): if (not align): align = Alignment() side = Side(style='thin') border = Border(left=side,right=side,top=side,bottom=side) if (i == 'fill'): color = ColorTable[fmt[i]] fill = PatternFill(start_color=color,end_color='FFFFFFFF',fill_type='solid') if (i == 'orient'): pass if (i == 'bg'): fill = PatternFill(start_color='FFEE1111',end_color='FFEE1111',fill_type='solid') if (i == 'numFmt'): numFmt = fmt[i] if (align): c.alignment = align.copy() if (border): c.border = border.copy() if (fill): c.fill = fill.copy() if (numFmt): c.number_format = numFmt c.value = val
def SetCellFmt(self,cell,hAlign,vAlign,fmt,value): if (align == 'C'): align = Alignment(horizontal='center',vertical='center') elif (align == 'L'): align = Alignment(horizontal='left',vertical='center') elif (align == 'R'): align = Alignment(horizontal='right',vertical='center') else: align = Alignment(horizontal='right',vertical='center') side = Side(style='thin') border = Border(left=side,right=side,top=side,bottom=side) if (fmt == 'F'): fmt = '0.00' cell.number_format = fmt cell.alignment = align.copy() cell.border = border.copy() cell.value = value
def Informe_pdf(sheet, dic, lista_alumnos): columnas_filas(sheet, 0, 'A', 4.00) columnas_filas(sheet, 0, 'B', 4.00) columnas_filas(sheet, 0, 'C', 2.00) columnas_filas(sheet, 0, 'D', 10.00) columnas_filas(sheet, 0, 'E', 4.00) columnas_filas(sheet, 0, 'F', 14.00) columnas_filas(sheet, 0, 'G', 14.00) columnas_filas(sheet, 0, 'H', 12.00) columnas_filas(sheet, 0, 'I', 7.00) columnas_filas(sheet, 0, 'J', 5.00) columnas_filas(sheet, 0, 'K', 10.00) columnas_filas(sheet, 0, 'L', 3.00) columnas_filas(sheet, 0, 'M', 4.00) columnas_filas(sheet, 0, 'N', 4.00) columnas_filas(sheet, 0, 'O', 5.00) columnas_filas(sheet, 0, 'P', 5.00) alignment_title = Alignment(horizontal='center', vertical='center') fuente = Font(bold=False, size=5, name='arial') fuente3 = Font(bold=True, size=10, name='arial') fuente2 = Font(bold=True, size=5, name='arial') fila = 3 fila1 = 2 acum = 1 cont = 0 col = 2 col1 = 4 fil = 4 coli = 2 colf = 2 sheet.merge_cells('D2:N2') sheet['D2'].alignment = alignment_title.copy(wrapText=True, horizontal='center', vertical='top') sheet['D2'].font = fuente3 sheet['D2'] = 'Detalle de Recordatorios de Pago' #sheet.merge_cells('L1:M1') sheet['L1'].alignment = alignment_title.copy(wrapText=True, horizontal='right', vertical='top') sheet['L1'].font = fuente2 sheet['L1'] = 'Usuario' usuario_id = str(dic['usuario_id'].encode('utf-8')) sheet.merge_cells('M1:N1') sheet['M1'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['M1'].font = fuente sheet['M1'] = str(dic['usuario_id'].encode('utf-8')) sheet.merge_cells('C1:F1') sheet['C1'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['C1'].font = fuente2 sheet['C1'] = 'Compañia: ' + str(dic['company_id'].encode('utf-8')) #poner_border(sheet,1,1,'medium','medium','none','none') #poner_border(sheet,1,2,'none','medium','none','none') poner_border(sheet, 1, 3, 'medium', 'medium', 'none', 'none') poner_border(sheet, 1, 4, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 5, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 5, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 6, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 7, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 8, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 9, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 10, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 11, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 12, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 13, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 14, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 15, 'none', 'medium', 'medium', 'none') poner_border(sheet, 2, 3, 'medium', 'none', 'none', 'none') poner_border(sheet, 2, 15, 'none', 'none', 'medium', 'none') poner_border(sheet, 3, 3, 'medium', 'none', 'none', 'none') poner_border(sheet, 3, 15, 'none', 'none', 'medium', 'none') poner_border(sheet, 4, 3, 'none', 'medium', 'none', 'none') poner_border(sheet, 4, 4, 'none', 'medium', 'none', 'none') poner_border(sheet, 4, 5, 'none', 'medium', 'none', 'none') poner_border(sheet, 4, 6, 'none', 'medium', 'none', 'none') poner_border(sheet, 4, 7, 'none', 'medium', 'none', 'none') poner_border(sheet, 4, 8, 'none', 'medium', 'none', 'none') poner_border(sheet, 4, 9, 'none', 'medium', 'none', 'none') poner_border(sheet, 4, 10, 'none', 'medium', 'none', 'none') poner_border(sheet, 4, 11, 'none', 'medium', 'none', 'none') poner_border(sheet, 4, 12, 'none', 'medium', 'none', 'none') poner_border(sheet, 4, 13, 'none', 'medium', 'none', 'none') poner_border(sheet, 4, 14, 'none', 'medium', 'none', 'none') poner_border(sheet, 4, 15, 'none', 'medium', 'none', 'none') fecha_ini = dic['fecha_desde'] fecha_fin = dic['fecha_hasta'] fecha = str(" Desde: " + dic['fecha_desde'] + " Hasta: " + dic['fecha_hasta']) sheet.merge_cells('D3:N3') sheet['D3'].alignment = alignment_title.copy(wrapText=True, horizontal='center', vertical='top') sheet['D3'].font = fuente2 sheet['D3'] = str(" Desde: " + dic['fecha_desde'] + " Hasta: " + dic['fecha_hasta']) sheet.merge_cells('A5:F5') sheet['A5'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['A5'].font = fuente2 sheet['A5'] = str('Fecha Emisión: ' + str(dic['fecha_emision'])) sheet.merge_cells('A6:C6') sheet['A6'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['A6'].font = fuente2 jornada = 'Jornada: %s' % (dic['jornada'] if dic['jornada'] else '') sheet['A6'] = jornada sheet.merge_cells('F6:G6') sheet['F6'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['F6'].font = fuente2 seccion = 'Seccion: %s' % (dic['seccion'] if dic['seccion'] else '') sheet['F6'] = seccion sheet.merge_cells('H6:I6') sheet['H6'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['H6'].font = fuente2 curso = 'Curso: %s' % (dic['curso'] if dic['curso'] else '') sheet['H6'] = curso sheet.merge_cells('M6:N6') sheet['M6'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['M6'].font = fuente2 paralelo = 'Paralelo: %s' % (dic['paralelo'] if dic['paralelo'] else '') sheet['M6'] = paralelo sheet.merge_cells('A7:F7') sheet['A7'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['A7'].font = fuente2 representante = 'Representante: %s' % (dic['representante'] if dic['representante'] else '') sheet['A7'] = representante sheet.merge_cells('H7:M7') sheet['H7'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['H7'].font = fuente2 alumno = 'Alumno: %s' % (dic['alumno'] if dic['alumno'] else '') sheet['H7'] = alumno fecha_actual = dic['fecha_emision'] sheet['A8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['A8'].font = fuente2 sheet['A8'] = 'Orden' sheet.merge_cells('B8:C8') sheet['B8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['B8'].font = fuente2 sheet['B8'] = 'Codigo' sheet.merge_cells('D8:E8') sheet['D8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['D8'].font = fuente2 sheet['D8'] = 'Estructura Escolar' sheet['F8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['F8'].font = fuente2 sheet['F8'] = 'Alumno' sheet['G8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['G8'].font = fuente2 sheet['G8'] = 'Representante' sheet['H8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['H8'].font = fuente2 sheet['H8'] = 'Correo' sheet['I8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['I8'].font = fuente2 sheet['I8'] = '# Factura' sheet['J8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['J8'].font = fuente2 sheet['J8'] = 'Fecha Factura' sheet.merge_cells('K8:L8') sheet['K8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['K8'].font = fuente2 sheet['K8'] = 'Concepto' sheet['M8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['M8'].font = fuente2 sheet['M8'] = 'Monto' sheet['N8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['N8'].font = fuente2 sheet['N8'] = 'Saldo' sheet['O8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['O8'].font = fuente2 sheet['O8'] = '# Notificacion' sheet['P8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['P8'].font = fuente2 sheet['P8'] = 'Fecha envio' fila = 9 total_general = 0.0 saldo_general = 0.0 saldo = 0.0 total = 0.0 dic = {} lista_datos = [] for recorrer in lista_alumnos: sheet['A' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['A' + str(fila)].font = fuente sheet['A' + str(fila)] = recorrer['orden'] #poner_border(sheet,fila,1,'none','none','none','medium') sheet.merge_cells('B' + str(fila) + ':C' + str(fila)) sheet['B' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['B' + str(fila)].font = fuente sheet['B' + str(fila)] = recorrer['sequence'] sheet.merge_cells('D' + str(fila) + ':E' + str(fila)) sheet['D' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['D' + str(fila)].font = fuente sheet['D' + str(fila)] = recorrer['descripcion'] sheet['F' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['F' + str(fila)].font = fuente sheet['F' + str(fila)] = recorrer['alumno'] sheet['G' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['G' + str(fila)].font = fuente sheet['G' + str(fila)] = recorrer['representante'] sheet['H' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['H' + str(fila)].font = fuente sheet['H' + str(fila)] = recorrer['correo'] sheet['I' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['I' + str(fila)].font = fuente sheet['I' + str(fila)] = recorrer['numero_factura'] sheet['J' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['J' + str(fila)].font = fuente sheet['J' + str(fila)] = recorrer['fecha_factura'] sheet.merge_cells('K' + str(fila) + ':L' + str(fila)) sheet['K' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='justify', vertical='top') sheet['K' + str(fila)].font = fuente sheet['K' + str(fila)] = recorrer['concepto'] sheet['M' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='right', vertical='top') sheet['M' + str(fila)].font = fuente sheet['M' + str(fila)] = "{:,}".format(float( recorrer['monto'])).replace(',', '~').replace('.', ',').replace('~', '.') sheet['N' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='right', vertical='top') sheet['N' + str(fila)].font = fuente sheet['N' + str(fila)] = "{:,}".format(float( recorrer['saldo'])).replace(',', '~').replace('.', ',').replace('~', '.') sheet['O' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='center', vertical='top') sheet['O' + str(fila)].font = fuente sheet['O' + str(fila)] = recorrer['notificaciones'] sheet['P' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='center', vertical='top') sheet['P' + str(fila)].font = fuente sheet['P' + str(fila)] = recorrer['fecha_envio_correo'] total_general = total_general + float(recorrer['monto']) saldo_general = saldo_general + float(recorrer['saldo']) fila = fila + 1 sheet.merge_cells('K' + str(fila) + ':L' + str(fila)) sheet['K' + str(fila)].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['K' + str(fila)].font = fuente2 sheet['K' + str(fila)] = 'TOTAL' sheet['M' + str(fila)].alignment = alignment_title.copy(wrapText=True, horizontal='right', vertical='top') sheet['M' + str(fila)].font = fuente2 sheet['M' + str(fila)] = "{:,}".format(float(total_general)).replace( ',', '~').replace('.', ',').replace('~', '.') sheet['N' + str(fila)].alignment = alignment_title.copy(wrapText=True, horizontal='right', vertical='top') sheet['N' + str(fila)].font = fuente2 sheet['N' + str(fila)] = "{:,}".format(float(saldo_general)).replace( ',', '~').replace('.', ',').replace('~', '.') columnas_filas(sheet, 1, str(fila + 5), 10.00) sheet.merge_cells('H' + str(fila + 5) + ':I' + str(fila + 5)) sheet['H' + str(fila + 5)].alignment = alignment_title.copy( wrapText=True, horizontal='center', vertical='center') sheet['H' + str(fila + 5)].font = fuente2 sheet['H' + str(fila + 5)] = usuario_id poner_border(sheet, fila + 5, 8, 'none', 'thin', 'none', 'none') poner_border(sheet, fila + 5, 9, 'none', 'thin', 'none', 'none')
def SetFormat(self,row,col,fmt): pyws = self.pyws font = None color = None align = None fill = None numFmt = None border = None c = pyws.cell(row=row,column=col) #------------------------------------------------------------------------- for i in fmt: if (i == 'hAlign'): if (not align): align = Alignment() align.horizontal = alignType[fmt[i]] elif (i == 'vAlign'): if (not align): align = Alignment() align.vertical = alignType[fmt[i]] elif (i == 'tAlign'): if (not align): align = Alignment() align.text_rotation = fmt[i] elif (i == 'wrap'): if (not align): align = Alignment() align.wrap_text = fmt[i] elif (i == 'font'): name = 'Calibri' bold = False size = 11 dict = fmt[i] if ('emph' in dict): if (dict['emph'] == 'B'): bold = True if ('size' in dict): size = dict['size'] if (not font): font = Font(name=name,size=size,bold=bold) elif (i == 'border'): dict = fmt[i] color = None style = None if ('Color' in dict): color = ColorTable[dict['Color']] else: color = ColorTable['Black'] if ('Style' in dict): color = dict['Style'] if (c.border.top.style == None): tSide = Side(color=color) else: tSide = c.border.top.copy() if (c.border.bottom.style == None): bSide = Side(color=color) else: bSide = c.border.bottom.copy() if (c.border.left.style == None): lSide = Side(color=color) else: lSide = c.border.left.copy() if (c.border.right.style == None): rSide = Side(color=color) else: rSide = c.border.right.copy() if ((len(dict) ==1) and ('A' in dict)): tSide.style = dict['A'] bSide.style = dict['A'] lSide.style = dict['A'] rSide.style = dict['A'] else: for j in dict: if (j == 'T'): tSide.style = dict[j] if (j == 'B'): bSide.style = dict[j] if (j == 'L'): lSide.style = dict[j] if (j == 'R'): rSide.style = dict[j] border = Border(left=lSide,right=rSide,top=tSide,bottom=bSide) elif (i == 'fill'): color = ColorTable[fmt[i]] fill = PatternFill(start_color=color,end_color='FFFFFFFF',fill_type='solid') elif (i == 'orient'): pass elif (i == 'numFmt'): numFmt = fmt[i] #------------------------------------------------------------------------- if (font): c.font = font.copy() if (align): c.alignment = align.copy() if (border): c.border = border.copy() if (fill): c.fill = fill.copy() if (numFmt): c.number_format = numFmt
def Informe(sheet, dic,lista_alumnos,cant_alumno): columnas_filas(sheet, 0, 'A', 20.00) columnas_filas(sheet, 0, 'B', 18.00) columnas_filas(sheet, 0, 'C', 40.00) columnas_filas(sheet, 0, 'D', 40.00) columnas_filas(sheet, 0, 'F', 40.00) columnas_filas(sheet, 0, 'E', 50.00) columnas_filas(sheet, 0, 'G', 20.00) columnas_filas(sheet, 0, 'H', 20.00) columnas_filas(sheet, 0, 'I', 15.00) alignment_title = Alignment(horizontal='center', vertical='center') fuente = Font(bold=False, size=11, name='arial') fila = 3 fila1 = 2 acum=1 cont=0 col=2 col1=4 fil=4 coli=2 colf=2 sheet.merge_cells('A2:I2') sheet['A2'].alignment = alignment_title.copy(wrapText=True,horizontal='center', vertical='top') sheet['A2'].font = fuente.copy(bold=True,size=15, name= "arial") sheet['A2']= 'DIRECTORIO DE ALUMNOS' sheet['G1'].alignment = alignment_title.copy(wrapText=True,horizontal='right', vertical='top') sheet['G1'].font = fuente.copy(bold=True,size=12, name= "arial") sheet['G1']= 'Usuario' sheet['H1'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['H1'].font = fuente sheet['H1']= dic['usuario_id'] sheet['A1'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A1'].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A1']= 'Cia' sheet.merge_cells('B1:C1') sheet['B1'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B1'].font = fuente.copy(bold=False,size=12, name= "arial") sheet['B1']= dic['company_id'] sheet['A3'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A3'].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A3']= 'Fecha Emision:' #fecha_actual = datetime.strftime(datetime.now(), '%d-%m-%Y %H:%M:%S') fecha_actual = dic['fecha'] sheet.merge_cells('B3:C3') sheet['B3'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B3'].font = fuente.copy(bold=True,size=12, name= "arial") sheet['B3']= fecha_actual fila=6 for recorrer in lista_alumnos: sheet['A'+str(fila+1)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+1)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A'+str(fila+1)]= 'Seccion:' sheet.merge_cells('B'+str(fila+1)+':C'+str(fila+1)) sheet['B'+str(fila+1)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila+1)].font = fuente.copy(bold=False,size=12, name= "arial") if recorrer['seccion_id']==False: sheet['B'+str(fila+1)]= '' else: sheet['B'+str(fila+1)]= recorrer['seccion_id'] sheet['A'+str(fila+2)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+2)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A'+str(fila+2)]= 'Curso:' sheet.merge_cells('B'+str(fila+2)+':C'+str(fila+2)) sheet['B'+str(fila+2)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila+2)].font = fuente.copy(bold=False,size=12, name= "arial") if recorrer['curso_id']==False: sheet['B'+str(fila+2)]= '' else: sheet['B'+str(fila+2)]= recorrer['curso_id'] sheet['A'+str(fila+3)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+3)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A'+str(fila+3)]= 'Codigo:' sheet.merge_cells('B'+str(fila+3)+':C'+str(fila+3)) sheet['B'+str(fila+3)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila+3)].font = fuente.copy(bold=False,size=12, name= "arial") if recorrer['codigo_curso']==False: sheet['B'+str(fila+3)]= '' else: sheet['B'+str(fila+3)]= recorrer['codigo_curso'] sheet['A'+str(fila+4)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+4)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A'+str(fila+4)]= 'Paralelo:' sheet.merge_cells('B'+str(fila+4)+':C'+str(fila+4)) sheet['B'+str(fila+4)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila+4)].font = fuente.copy(bold=False,size=12, name= "arial") if recorrer['paralelo_id']==False: sheet['B'+str(fila+4)]= '' else: sheet['B'+str(fila+4)]= recorrer['paralelo_id'] sheet['A'+str(fila+5)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+5)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A'+str(fila+5)]= 'Total Alumnos:' sheet['B'+str(fila+5)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila+5)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['B'+str(fila+5)]= len(recorrer['lista_detalle']) fila=fila+7 #poner_border(sheet,13,7,'medium','none','none','none') border_tabla(sheet,1,9,fila,fila,'none','none','none','medium') sheet['A'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A'+str(fila)]= 'Orden' sheet['B'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['B'+str(fila)]= 'Codigo' sheet['C'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['C'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['C'+str(fila)]= 'Alumno' sheet['D'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['D'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['D'+str(fila)]= 'Representante' sheet['E'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['E'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['E'+str(fila)]= 'Correo' sheet['F'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['F'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['F'+str(fila)]= 'Direccion' sheet['G'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['G'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['G'+str(fila)]= 'Telefono' sheet['H'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['H'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['H'+str(fila)]= 'Cedula - RUC' sheet['I'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['I'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['I'+str(fila)]= 'Status' fila=fila+1 orden=1 for detalle in recorrer['lista_detalle']: sheet['A'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") sheet['A'+str(fila)]= orden orden=orden+1 sheet['B'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") sheet['B'+str(fila)]= detalle['codigo'] sheet['C'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='justify', vertical='top') sheet['C'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") sheet['C'+str(fila)]= detalle['alumno'] sheet['D'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='justify', vertical='top') sheet['D'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") sheet['D'+str(fila)]= detalle['representante'] sheet['E'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['E'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") if detalle['correo']==False: sheet['E'+str(fila)]= '' else: sheet['E'+str(fila)]= detalle['correo'] sheet['F'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['F'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") if detalle['direccion']==False: sheet['F'+str(fila)]= '' else: sheet['F'+str(fila)]= detalle['direccion'] sheet['G'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='justify', vertical='top') sheet['G'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") if detalle['telefono']==False: sheet['G'+str(fila)]= '' else: sheet['G'+str(fila)]= detalle['telefono'] sheet['H'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['H'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") sheet['H'+str(fila)]= detalle['cedula'] sheet['I'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['I'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") sheet['I'+str(fila)]= detalle['state'] fila=fila+1 border_tabla(sheet,1,9,fila,fila,'none','none','none','medium') fila=fila+2 fila=fila+2 sheet.merge_cells('A'+str(fila)+':B'+str(fila)) sheet['A'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A'+str(fila)]= 'TOTAL ALUMNOS REPORTE' sheet['C'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['C'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") sheet['C'+str(fila)]= cant_alumno
def Informe_pdf(sheet, dic, lista_alumnos): columnas_filas(sheet, 0, 'A', 5.00) columnas_filas(sheet, 0, 'B', 5.00) columnas_filas(sheet, 0, 'C', 2.00) columnas_filas(sheet, 0, 'D', 7.00) columnas_filas(sheet, 0, 'E', 18.00) columnas_filas(sheet, 0, 'F', 8.00) columnas_filas(sheet, 0, 'G', 5.00) columnas_filas(sheet, 0, 'H', 8.00) columnas_filas(sheet, 0, 'I', 5.00) columnas_filas(sheet, 0, 'J', 5.00) columnas_filas(sheet, 0, 'K', 10.00) columnas_filas(sheet, 0, 'L', 3.00) alignment_title = Alignment(horizontal='center', vertical='center') fuente = Font(bold=False, size=6, name='arial') fuente3 = Font(bold=True, size=8, name='arial') fuente2 = Font(bold=True, size=6, name='arial') fila = 3 fila1 = 2 acum = 1 cont = 0 col = 2 col1 = 4 fil = 4 coli = 2 colf = 2 sheet.merge_cells('A2:I2') sheet['A2'].alignment = alignment_title.copy(wrapText=True, horizontal='center', vertical='top') sheet['A2'].font = fuente3 sheet['A2'] = 'Cobros Automaticos' sheet.merge_cells('G1:H1') sheet['G1'].alignment = alignment_title.copy(wrapText=True, horizontal='right', vertical='top') sheet['G1'].font = fuente2 sheet['G1'] = 'Usuario' usuario_id = str(dic['usuario_id'].encode('utf-8')) sheet.merge_cells('I1:J1') sheet['I1'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['I1'].font = fuente sheet['I1'] = str(dic['usuario_id'].encode('utf-8')) sheet.merge_cells('A1:C1') sheet['A1'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['A1'].font = fuente2 sheet['A1'] = 'Compañia ' + str(dic['company_id'].encode('utf-8')) poner_border(sheet, 1, 1, 'medium', 'medium', 'none', 'none') poner_border(sheet, 1, 2, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 3, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 4, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 5, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 5, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 6, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 7, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 8, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 9, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 10, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 11, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 12, 'none', 'medium', 'medium', 'none') #poner_border(sheet,3,12,'none','none','medium','none') poner_border(sheet, 2, 1, 'medium', 'none', 'none', 'none') poner_border(sheet, 2, 12, 'none', 'none', 'medium', 'none') poner_border(sheet, 3, 1, 'none', 'medium', 'none', 'none') poner_border(sheet, 3, 2, 'none', 'medium', 'none', 'none') poner_border(sheet, 3, 3, 'none', 'medium', 'none', 'none') poner_border(sheet, 3, 4, 'none', 'medium', 'none', 'none') poner_border(sheet, 3, 5, 'none', 'medium', 'none', 'none') poner_border(sheet, 3, 6, 'none', 'medium', 'none', 'none') poner_border(sheet, 3, 7, 'none', 'medium', 'none', 'none') poner_border(sheet, 3, 8, 'none', 'medium', 'none', 'none') poner_border(sheet, 3, 9, 'none', 'medium', 'none', 'none') poner_border(sheet, 3, 10, 'none', 'medium', 'none', 'none') poner_border(sheet, 3, 11, 'none', 'medium', 'none', 'none') poner_border(sheet, 3, 12, 'none', 'medium', 'none', 'none') sheet['A4'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['A4'].font = fuente2 sheet['A4'] = 'Fecha Emisión:' fecha_actual = dic['fecha_emision'] sheet.merge_cells('B4:D4') sheet['B4'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['B4'].font = fuente sheet['B4'] = fecha_actual sheet['F4'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['F4'].font = fuente2 sheet['F4'] = 'Estado:' sheet.merge_cells('G4:J4') sheet['G4'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['G4'].font = fuente sheet['G4'] = dic['estado'] sheet['A5'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['A5'].font = fuente2 sheet['A5'] = 'Codigo:' sheet.merge_cells('B5:E5') sheet['B5'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['B5'].font = fuente sheet['B5'] = dic['codigo'] sheet['F5'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['F5'].font = fuente2 sheet['F5'] = 'Metodo de Pago:' sheet.merge_cells('G5:J5') sheet['G5'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['G5'].font = fuente sheet['G5'] = dic['met_pago'] sheet['A6'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['A6'].font = fuente2 sheet['A6'] = 'Banco' sheet.merge_cells('B6:E6') sheet['B6'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['B6'].font = fuente sheet['B6'] = dic['banco'] sheet['F6'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['F6'].font = fuente2 sheet['F6'] = 'Cuenta Bancaria:' sheet.merge_cells('G6:J6') sheet['G6'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['G6'].font = fuente sheet['G6'] = dic['cuenta_bancaria'] sheet['A7'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['A7'].font = fuente2 sheet['A7'] = 'Caja' sheet.merge_cells('B7:E7') sheet['B7'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['B7'].font = fuente sheet['B7'] = dic['caja'] sheet['F7'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['F7'].font = fuente2 sheet['F7'] = 'Fecha de Cobro:' sheet.merge_cells('G7:J7') sheet['G7'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['G7'].font = fuente sheet['G7'] = dic['fecha_cobro'] sheet['A8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['A8'].font = fuente2 sheet['A8'] = 'Tipo' sheet.merge_cells('B8:C8') sheet['B8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['B8'].font = fuente2 sheet['B8'] = 'No. Factura' sheet['D8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['D8'].font = fuente2 sheet['D8'] = 'Codigo del Alumno' sheet['E8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['E8'].font = fuente2 sheet['E8'] = 'Alumno' sheet['F8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['F8'].font = fuente2 sheet['F8'] = 'Codigo del Pago' sheet['G8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['G8'].font = fuente2 sheet['G8'] = 'Valor' sheet.merge_cells('H8:I8') sheet['H8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['H8'].font = fuente2 sheet['H8'] = 'Curso' sheet['J8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['J8'].font = fuente2 sheet['J8'] = 'Fecha Factura' sheet.merge_cells('K8:L8') sheet['K8'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['K8'].font = fuente2 sheet['K8'] = 'Observacion' fila = 9 total_general = 0.0 saldo_general = 0.0 saldo = 0.0 total = 0.0 dic = {} lista_datos = [] for recorrer in lista_alumnos: sheet['A' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['A' + str(fila)].font = fuente sheet['A' + str(fila)] = recorrer['tipo'] #poner_border(sheet,fila,1,'none','none','none','medium') sheet.merge_cells('B' + str(fila) + ':C' + str(fila)) sheet['B' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['B' + str(fila)].font = fuente sheet['B' + str(fila)] = recorrer['numero_factura'] sheet['D' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['D' + str(fila)].font = fuente sheet['D' + str(fila)] = recorrer['codigo_alumno'] sheet['E' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['E' + str(fila)].font = fuente sheet['E' + str(fila)] = recorrer['alumno'] sheet['F' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['F' + str(fila)].font = fuente sheet['F' + str(fila)] = recorrer['numero_pago'] sheet['G' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='right', vertical='top') sheet['G' + str(fila)].font = fuente sheet['G' + str(fila)] = "{:,}".format(float( recorrer['valor'])).replace(',', '~').replace('.', ',').replace('~', '.') sheet.merge_cells('H' + str(fila) + ':I' + str(fila)) sheet['H' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='right', vertical='top') sheet['H' + str(fila)].font = fuente sheet['H' + str(fila)] = recorrer['jornada'] + '/' + recorrer[ 'curso'] + '/' + recorrer['paralelo'] sheet['J' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='right', vertical='top') sheet['J' + str(fila)].font = fuente sheet['J' + str(fila)] = recorrer['fecha_factura'] sheet.merge_cells('K' + str(fila) + ':L' + str(fila)) sheet['K' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='justify', vertical='top') sheet['K' + str(fila)].font = fuente sheet['K' + str(fila)] = recorrer['observacion'] total_general = total_general + float(recorrer['valor']) fila = fila + 1 sheet['E' + str(fila)].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['E' + str(fila)].font = fuente2 sheet['E' + str(fila)] = 'TOTAL' sheet['G' + str(fila)].alignment = alignment_title.copy(wrapText=True, horizontal='right', vertical='top') sheet['G' + str(fila)].font = fuente2 sheet['G' + str(fila)] = "{:,}".format(float(total_general)).replace( ',', '~').replace('.', ',').replace('~', '.') columnas_filas(sheet, 1, str(fila + 5), 10.00) sheet.merge_cells('D' + str(fila + 5) + ':F' + str(fila + 5)) sheet['D' + str(fila + 5)].alignment = alignment_title.copy( wrapText=True, horizontal='center', vertical='center') sheet['D' + str(fila + 5)].font = fuente2 sheet['D' + str(fila + 5)] = usuario_id poner_border(sheet, fila + 5, 4, 'none', 'thin', 'none', 'none') poner_border(sheet, fila + 5, 5, 'none', 'thin', 'none', 'none') poner_border(sheet, fila + 5, 6, 'none', 'thin', 'none', 'none')
def Informe(sheet, dic, lista_alumnos): columnas_filas(sheet, 0, 'A', 10.00) columnas_filas(sheet, 0, 'B', 5.00) columnas_filas(sheet, 0, 'C', 10.00) columnas_filas(sheet, 0, 'D', 7.00) columnas_filas(sheet, 0, 'E', 12.00) columnas_filas(sheet, 0, 'F', 10.00) columnas_filas(sheet, 0, 'G', 10.00) columnas_filas(sheet, 0, 'H', 7.00) columnas_filas(sheet, 0, 'I', 10.00) alignment_title = Alignment(horizontal='center', vertical='center') fuente = Font(bold=False, size=6, name='arial') fuente3 = Font(bold=True, size=8, name='arial') fuente2 = Font(bold=True, size=6, name='arial') fila = 3 fila1 = 2 acum = 1 cont = 0 col = 2 col1 = 4 fil = 4 coli = 2 colf = 2 sheet.merge_cells('A2:I2') sheet['A2'].alignment = alignment_title.copy(wrapText=True, horizontal='center', vertical='top') sheet['A2'].font = fuente3 sheet['A2'] = 'Listado de Cheques y Ordenes de Pago' sheet['H1'].alignment = alignment_title.copy(wrapText=True, horizontal='right', vertical='top') sheet['H1'].font = fuente2 sheet['H1'] = 'Usuario' usuario_id = str(dic['usuario_id'].encode('utf-8')) sheet.merge_cells('B1:C1') sheet['I1'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['I1'].font = fuente sheet['I1'] = str(dic['usuario_id'].encode('utf-8')) sheet['A1'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['A1'].font = fuente2 sheet['A1'] = 'Compañia' sheet.merge_cells('B1:C1') sheet['B1'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['B1'].font = fuente sheet['B1'] = str(dic['company_id'].encode('utf-8')) sheet['A4'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['A4'].font = fuente2 sheet['A4'] = 'Fecha Emisión:' #fecha_actual = datetime.strftime(datetime.now(), '%d-%m-%Y %H:%M:%S') fecha_actual = dic['fecha_corte'] sheet.merge_cells('B4:C4') sheet['B4'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['B4'].font = fuente sheet['B4'] = fecha_actual sheet['A5'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['A5'].font = fuente2 sheet['A5'] = 'Metodos de Pagos:' sheet.merge_cells('B5:E5') sheet['B5'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['B5'].font = fuente if filtro == 0: sheet['B5'] = "Todos los metodos de pagos" else: sheet['B5'] = filtro sheet['F5'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['F5'].font = fuente2 sheet['F5'] = 'Estados de Pagos:' sheet.merge_cells('G5:G5') sheet['G5'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['G5'].font = fuente if filtro1 == 0: sheet['G5'] = "Todos" else: sheet['G5'] = filtro1 poner_border(sheet, 1, 1, 'medium', 'medium', 'none', 'none') poner_border(sheet, 1, 2, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 3, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 4, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 5, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 5, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 6, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 7, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 8, 'none', 'medium', 'none', 'none') poner_border(sheet, 1, 9, 'none', 'medium', 'medium', 'none') poner_border(sheet, 2, 1, 'medium', 'none', 'none', 'none') poner_border(sheet, 2, 9, 'none', 'none', 'medium', 'none') poner_border(sheet, 3, 1, 'medium', 'none', 'none', 'medium') poner_border(sheet, 3, 2, 'none', 'none', 'none', 'medium') poner_border(sheet, 3, 3, 'none', 'none', 'none', 'medium') poner_border(sheet, 3, 4, 'none', 'none', 'none', 'medium') poner_border(sheet, 3, 5, 'none', 'none', 'none', 'medium') poner_border(sheet, 3, 6, 'none', 'none', 'none', 'medium') poner_border(sheet, 3, 7, 'none', 'none', 'none', 'medium') poner_border(sheet, 3, 8, 'none', 'none', 'none', 'medium') poner_border(sheet, 3, 9, 'none', 'none', 'medium', 'medium') fecha_ini = dic['fecha_desde'] fecha_fin = dic['fecha_hasta'] fecha = str(" Desde: " + dic['fecha_desde'] + " Hasta: " + dic['fecha_hasta']) sheet.merge_cells('D3:F3') sheet['D3'].alignment = alignment_title.copy(wrapText=True, horizontal='center', vertical='top') sheet['D3'].font = fuente2 sheet['D3'] = str(" Desde: " + dic['fecha_desde'] + " Hasta: " + dic['fecha_hasta']) sheet['A6'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['A6'].font = fuente2 sheet['A6'] = 'Fecha Emisión' sheet.merge_cells('B6:C6') sheet['B6'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['B6'].font = fuente2 sheet['B6'] = 'Egreso #' sheet.merge_cells('D6:E6') sheet['D6'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['D6'].font = fuente2 sheet['D6'] = 'Cheque #' sheet['F6'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['F6'].font = fuente2 sheet['F6'] = 'Beneficiario' sheet.merge_cells('G6:H6') sheet['G6'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['G6'].font = fuente2 sheet['G6'] = 'Observación' sheet['I6'].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['I6'].font = fuente2 sheet['I6'] = 'Valor' fila = 7 total_general = 0.0 saldo_general = 0.0 saldo = 0.0 total = 0.0 dic = {} lista_datos = [] for recorrer in lista_alumnos: sheet['A' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['A' + str(fila)].font = fuente sheet['A' + str(fila)] = recorrer['fecha_emision'] #poner_border(sheet,fila,1,'none','none','none','medium') sheet.merge_cells('B' + str(fila) + ':C' + str(fila)) sheet['B' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['B' + str(fila)].font = fuente if recorrer['egreso'] == 0: sheet['B' + str(fila)] = "" else: sheet['B' + str(fila)] = recorrer['egreso'] sheet.merge_cells('D' + str(fila) + ':E' + str(fila)) sheet['D' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['D' + str(fila)].font = fuente if recorrer['cheque'] == 0: sheet['D' + str(fila)] = "" else: sheet['D' + str(fila)] = recorrer['cheque'] sheet['F' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['F' + str(fila)].font = fuente sheet['F' + str(fila)] = recorrer['beneficiario'] sheet.merge_cells('G' + str(fila) + ':H' + str(fila)) sheet['G' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='left', vertical='top') sheet['G' + str(fila)].font = fuente if recorrer['observacion'] == 0: sheet['G' + str(fila)] = "" else: sheet['G' + str(fila)] = recorrer['observacion'] sheet['I' + str(fila)].alignment = alignment_title.copy( wrapText=True, horizontal='right', vertical='top') sheet['I' + str(fila)].font = fuente sheet['I' + str(fila)] = "{:,}".format(float( recorrer['valor'])).replace(',', '~').replace('.', ',').replace('~', '.') total_general = total_general + float(recorrer['valor']) fila = fila + 1 sheet['G' + str(fila)].alignment = alignment_title.copy(wrapText=True, horizontal='left', vertical='top') sheet['G' + str(fila)].font = fuente2 sheet['G' + str(fila)] = 'TOTAL' sheet.merge_cells('H' + str(fila) + ':I' + str(fila)) sheet['H' + str(fila)].alignment = alignment_title.copy(wrapText=True, horizontal='right', vertical='top') sheet['H' + str(fila)].font = fuente2 sheet['H' + str(fila)] = "{:,}".format(float(total_general)).replace( ',', '~').replace('.', ',').replace('~', '.') columnas_filas(sheet, 1, str(fila + 5), 10.00) sheet.merge_cells('D' + str(fila + 5) + ':F' + str(fila + 5)) sheet['D' + str(fila + 5)].alignment = alignment_title.copy( wrapText=True, horizontal='center', vertical='center') sheet['D' + str(fila + 5)].font = fuente2 sheet['D' + str(fila + 5)] = usuario_id poner_border(sheet, fila + 5, 4, 'none', 'thin', 'none', 'none') poner_border(sheet, fila + 5, 5, 'none', 'thin', 'none', 'none') poner_border(sheet, fila + 5, 6, 'none', 'thin', 'none', 'none')
def Informe_pdf(sheet, dic,lista_alumnos,cant_alumno): columnas_filas(sheet, 0, 'A', 8.00) columnas_filas(sheet, 0, 'B', 15.00) columnas_filas(sheet, 0, 'C', 15.00) columnas_filas(sheet, 0, 'D', 10.00) columnas_filas(sheet, 0, 'E', 12.00) columnas_filas(sheet, 0, 'F', 18.00) columnas_filas(sheet, 0, 'G', 1.00) alignment_title = Alignment(horizontal='center', vertical='center') fuente = Font(bold=False, size=7, name='arial') fuente3 = Font(bold=True, size=10, name='arial') fuente2 = Font(bold=True, size=7, name='arial') fila = 3 fila1 = 2 acum=1 cont=0 col=2 col1=4 fil=4 coli=2 colf=2 sheet.merge_cells('A2:F2') sheet['A2'].alignment = alignment_title.copy(wrapText=True,horizontal='center', vertical='top') sheet['A2'].font = fuente3 sheet['A2']= 'ESTADO DE CUENTA' sheet['E1'].alignment = alignment_title.copy(wrapText=True,horizontal='right', vertical='top') sheet['E1'].font = fuente2 sheet['E1']= 'Usuario' sheet['F1'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['F1'].font = fuente sheet['F1']= str(dic['usuario_id'].encode('utf-8')) sheet['A1'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A1'].font = fuente2 sheet['A1']= 'Cia' sheet.merge_cells('B1:C1') sheet['B1'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B1'].font = fuente sheet['B1']= str(dic['company_id'].encode('utf-8')) sheet['A3'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A3'].font = fuente2 sheet['A3']= 'Fecha Emision:' #fecha_actual = datetime.strftime(datetime.now(), '%d-%m-%Y %H:%M:%S') fecha_actual = dic['fecha_corte'] sheet.merge_cells('B3:C3') sheet['B3'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B3'].font = fuente sheet['B3']= fecha_actual poner_border(sheet,1,1,'medium','medium','none','none') poner_border(sheet,1,2,'none','medium','none','none') poner_border(sheet,1,3,'none','medium','none','none') poner_border(sheet,1,4,'none','medium','none','none') poner_border(sheet,1,5,'none','medium','none','none') poner_border(sheet,1,6,'none','medium','medium','none') poner_border(sheet,2,1,'medium','none','none','none') poner_border(sheet,2,6,'none','none','medium','none') poner_border(sheet,3,1,'medium','none','none','medium') poner_border(sheet,3,2,'none','none','none','medium') poner_border(sheet,3,3,'none','none','none','medium') poner_border(sheet,3,4,'none','none','none','medium') poner_border(sheet,3,5,'none','none','none','medium') poner_border(sheet,3,6,'none','none','medium','medium') fila=2 total_general=0.0 saldo_general=0.0 for recorrer in lista_alumnos: columnas_filas(sheet, 1, str(fila+2), 10.00) columnas_filas(sheet, 1, str(fila+3), 15.00) columnas_filas(sheet, 1, str(fila+4), 10.00) columnas_filas(sheet, 1, str(fila+5), 10.00) columnas_filas(sheet, 1, str(fila+6), 10.00) columnas_filas(sheet, 1, str(fila+7), 10.00) columnas_filas(sheet, 1, str(fila+8), 10.00) columnas_filas(sheet, 1, str(fila+9), 10.00) sheet['A'+str(fila+2)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+2)].font = fuente2 sheet['A'+str(fila+2)]= 'Alumno:' sheet.merge_cells('B'+str(fila+2)+':C'+str(fila+2)) sheet['B'+str(fila+2)].alignment = alignment_title.copy(wrapText=True,horizontal='justify', vertical='top') sheet['B'+str(fila+2)].font = fuente if recorrer['alumno']!=False: sheet['B'+str(fila+2)]= str(recorrer['alumno']) sheet['A'+str(fila+3)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+3)].font = fuente2 sheet['A'+str(fila+3)]= 'Direccion' sheet.merge_cells('B'+str(fila+3)+':C'+str(fila+3)) sheet['B'+str(fila+3)].alignment = alignment_title.copy(wrapText=True,horizontal='justify', vertical='justify') sheet['B'+str(fila+3)].font = fuente sheet['B'+str(fila+3)]= str(recorrer['direccion'].encode('utf-8')) sheet['A'+str(fila+4)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+4)].font = fuente2 sheet['A'+str(fila+4)]= 'Telefono' sheet.merge_cells('B'+str(fila+4)+':C'+str(fila+4)) sheet['B'+str(fila+4)].alignment = alignment_title.copy(wrapText=True,horizontal='justify', vertical='top') sheet['B'+str(fila+4)].font = fuente sheet['B'+str(fila+4)]= (recorrer['telefono']) sheet['D'+str(fila+2)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['D'+str(fila+2)].font = fuente2 sheet['D'+str(fila+2)]= 'Representante' sheet.merge_cells('E'+str(fila+2)+':F'+str(fila+2)) sheet['E'+str(fila+2)].alignment = alignment_title.copy(wrapText=True,horizontal='justify', vertical='top') sheet['E'+str(fila+2)].font = fuente if recorrer['representante']!=False: sheet['E'+str(fila+2)]= str(recorrer['representante'].encode('utf-8')) sheet['D'+str(fila+3)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['D'+str(fila+3)].font = fuente2 sheet['D'+str(fila+3)]= 'Cedula o RUC' sheet.merge_cells('E'+str(fila+3)+':F'+str(fila+3)) sheet['E'+str(fila+3)].alignment = alignment_title.copy(wrapText=True,horizontal='justify', vertical='top') sheet['E'+str(fila+3)].font = fuente sheet['E'+str(fila+3)]= (recorrer['cedula']) sheet.merge_cells('D'+str(fila+5)+':E'+str(fila+5)) sheet['D'+str(fila+5)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['D'+str(fila+5)].font = fuente sheet['D'+str(fila+5)]= 'Jornada: '+(recorrer['jornada']) sheet['A'+str(fila+6)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+6)].font = fuente sheet['A'+str(fila+6)]= 'STATUS A' sheet.merge_cells('D'+str(fila+6)+':E'+str(fila+6)) sheet['D'+str(fila+6)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['D'+str(fila+6)].font = fuente sheet['D'+str(fila+6)]= 'Curso: '+(recorrer['curso']) sheet['F'+str(fila+5)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['F'+str(fila+5)].font = fuente sheet['F'+str(fila+5)]= 'Seccion: '+(recorrer['seccion']) sheet['F'+str(fila+6)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['F'+str(fila+6)].font = fuente sheet['F'+str(fila+6)]= 'Paralelo: '+(recorrer['paralelo']) poner_border(sheet,fila+7,6,'none','medium','medium','none') poner_border(sheet,fila+7,1,'medium','medium','none','none') poner_border(sheet,fila+7,2,'none','medium','none','none') poner_border(sheet,fila+7,3,'none','medium','none','none') poner_border(sheet,fila+7,4,'none','medium','none','none') poner_border(sheet,fila+7,5,'none','medium','none','none') sheet.merge_cells('A'+str(fila+7)+':B'+str(fila+7)) sheet['A'+str(fila+7)].alignment = alignment_title.copy(wrapText=True,horizontal='center', vertical='top') sheet['A'+str(fila+7)].font = fuente2 sheet['A'+str(fila+7)]= 'Documento' sheet['A'+str(fila+8)].alignment = alignment_title.copy(wrapText=True,horizontal='center', vertical='top') sheet['A'+str(fila+8)].font = fuente2 sheet['A'+str(fila+8)]= 'Tipo' sheet['B'+str(fila+8)].alignment = alignment_title.copy(wrapText=True,horizontal='center', vertical='top') sheet['B'+str(fila+8)].font = fuente2 sheet['B'+str(fila+8)]= 'Numero' sheet['C'+str(fila+8)].alignment = alignment_title.copy(wrapText=True,horizontal='center', vertical='top') sheet['C'+str(fila+8)].font = fuente2 sheet['C'+str(fila+8)]= 'Emision' sheet['D'+str(fila+8)].alignment = alignment_title.copy(wrapText=True,horizontal='center', vertical='top') sheet['D'+str(fila+8)].font = fuente2 sheet['D'+str(fila+8)]= 'Cargos' sheet['E'+str(fila+8)].alignment = alignment_title.copy(wrapText=True,horizontal='center', vertical='top') sheet['E'+str(fila+8)].font = fuente2 sheet['E'+str(fila+8)]= 'Abonos' sheet['F'+str(fila+8)].alignment = alignment_title.copy(wrapText=True,horizontal='center', vertical='top') sheet['F'+str(fila+8)].font = fuente2 sheet['F'+str(fila+8)]= 'Comentario' poner_border(sheet,fila+8,6,'none','none','medium','medium') poner_border(sheet,fila+8,1,'medium','none','none','medium') poner_border(sheet,fila+8,2,'none','none','none','medium') poner_border(sheet,fila+8,3,'none','none','none','medium') poner_border(sheet,fila+8,4,'none','none','none','medium') poner_border(sheet,fila+8,5,'none','none','none','medium') sheet.merge_cells('A'+str(fila+9)+':B'+str(fila+9)) sheet['A'+str(fila+9)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+9)].font = fuente2 sheet['A'+str(fila+9)]= 'Saldo al '+str(dic['fecha_desde']) sheet['D'+str(fila+9)].alignment = alignment_title.copy(wrapText=True,horizontal='right', vertical='top') sheet['D'+str(fila+9)].font = fuente2 sheet['D'+str(fila+9)]= "{:,}".format(float(recorrer['cargo'])).replace(',','~').replace('.',',').replace('~','.') sheet['E'+str(fila+9)].alignment = alignment_title.copy(wrapText=True,horizontal='right', vertical='top') sheet['E'+str(fila+9)].font = fuente2 sheet['E'+str(fila+9)]= 0.00 fila=fila+10 saldo=0.0 total=0.0 for det in recorrer['detalle']: columnas_filas(sheet, 1, str(fila), 10.00) sheet['A'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila)].font = fuente sheet['A'+str(fila)]= det['tipo'] sheet['B'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila)].font = fuente sheet['B'+str(fila)]= det['numero'] sheet['C'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='center', vertical='top') sheet['C'+str(fila)].font = fuente sheet['C'+str(fila)]= det['emision'] sheet['D'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='right', vertical='top') sheet['D'+str(fila)].font = fuente sheet['D'+str(fila)]= "{:,}".format(float(det['cargos'])).replace(',','~').replace('.',',').replace('~','.') sheet['E'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='right', vertical='top') sheet['E'+str(fila)].font = fuente sheet['E'+str(fila)]= "{:,}".format(float(det['total'])).replace(',','~').replace('.',',').replace('~','.') sheet['F'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='justify', vertical='top') sheet['F'+str(fila)].font = fuente sheet['F'+str(fila)]= det['comentario'] saldo=saldo+float(det['cargos']) total=total+float(det['total']) fila=fila+1 sheet.merge_cells('A'+str(fila)+':B'+str(fila)) sheet['A'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila)].font = fuente2 sheet['A'+str(fila)]= 'Saldo al '+str(dic['fecha_hasta']) sheet['D'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='right', vertical='top') sheet['D'+str(fila)].font = fuente2 sheet['D'+str(fila)]= "{:,}".format(float(saldo)).replace(',','~').replace('.',',').replace('~','.') sheet['E'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='right', vertical='top') sheet['E'+str(fila)].font = fuente2 sheet['E'+str(fila)]= "{:,}".format(float(total)).replace(',','~').replace('.',',').replace('~','.') resta=saldo-total sheet['F'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='right', vertical='top') sheet['F'+str(fila)].font = fuente2 sheet['F'+str(fila)]= "{:,}".format(float(resta)).replace(',','~').replace('.',',').replace('~','.') poner_border(sheet,fila,4,'none','medium','none','none') poner_border(sheet,fila,5,'none','medium','none','none') total_general = total_general + total saldo_general = saldo_general + saldo fila= fila + 2 sheet['C'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='center', vertical='top') sheet['C'+str(fila)].font = fuente2 sheet['C'+str(fila)]= 'Total General' sheet['D'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='right', vertical='top') sheet['D'+str(fila)].font = fuente2 sheet['D'+str(fila)]= "{:,}".format(float(saldo_general)).replace(',','~').replace('.',',').replace('~','.') sheet['E'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='right', vertical='top') sheet['E'+str(fila)].font = fuente2 sheet['E'+str(fila)]= "{:,}".format(float(total_general)).replace(',','~').replace('.',',').replace('~','.')