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('~','.')
