RE:Exportar Grid a Excel
Publicado por
pipax (1 intervención) el 05/11/2009 18:04:52
se_filtro = ""
F=""
F2=""
strQuery = " SELECT "
strQuery = strQuery +" I.OFI_CODANTERIOR, "
strQuery = strQuery +" E.UNO_NOMBRE, "
strQuery = strQuery +" I.ING_CODANTERIOR, "
strQuery = strQuery +" ISNULL(I.ING_REFER,'') AS ING_REFER, "
strQuery = strQuery +" ISNULL(I.ING_LLEGA,'') AS ING_LLEGA, "
strQuery = strQuery +" E2.UNO_CODANTERIOR AS UNO_CODANTERIOREXTERNA, "
strQuery = strQuery +" E2.UNO_NOMBRE AS UNO_NOMBREEXTERNA, "
strQuery = strQuery +" I.ING_INSTIT , "
strQuery = strQuery +" TIPO.DOC_NOMBRE, "
strQuery = strQuery +" CONVERT(VARCHAR,I.ING_FECHA,103) AS ING_FECHA, "
strQuery = strQuery +" I.ING_ASUNTO, "
strQuery = strQuery +" I.OFI_CODIGO, "
strQuery = strQuery +" OFICINA.OFI_NOMBRE, "
strQuery = strQuery +" OFICINA.OFI_SIGLAS "
strQuery = strQuery +" FROM INGRESOS I "
strQuery = strQuery +" INNER JOIN TIPO ON TIPO.DOC_CODIGO = I.DOC_CODIGO "
strQuery = strQuery +" INNER JOIN ECGUNOP AS E ON E.UNO_CODANTERIOR = I.OFI_CODANTERIOR "
strQuery = strQuery +" INNER JOIN OFICINA ON OFICINA.UNO_CODANTERIOR=I.UNO_CODOFI AND OFICINA.OFI_CODIGO=I.OFI_CODIGO "
strQuery = strQuery +" LEFT JOIN ECGUNOP AS E2 ON I.UNO_CODANTERIOR=E2.UNO_CODANTERIOR AND E2.UNO_CONDIC=1"
if ((len(alltrim(SUBSTR(fecha,1,2)))=2) and (len(alltrim(SUBSTR(fecha,4,2)))=2) and (len(trim(SUBSTR(fecha,7,4) ) )=4)) then
F=trim(SUBSTR(fecha,7,4) )+"/"+ alltrim(SUBSTR(fecha,4,2)) +"/"+ alltrim(SUBSTR(fecha,1,2))
ENDIF
if ((len(alltrim(SUBSTR(fecha2,1,2)))=2) and (len(alltrim(SUBSTR(fecha2,4,2)))=2) and (len(trim(SUBSTR(fecha2,7,4) ) )=4)) then
F2=trim(SUBSTR(fecha2,7,4) )+"/"+ alltrim(SUBSTR(fecha2,4,2)) +"/"+ alltrim(SUBSTR(fecha2,1,2))
endif
IF !EMPTY(oficodigo)
se_filtro = se_filtro + " AND I.ofi_codanterior = '"+oficodigo+"' "
ENDIF
IF !EMPTY(unocodigo)
se_filtro = se_filtro + " AND I.Uno_codanterior = '"+unocodigo+"' "
ENDIF
IF !EMPTY(ALLTRIM(F) )
se_filtro=se_filtro + " AND cast( (substring(I.Ing_fecha,7,4)+'/'+substring(I.Ing_fecha,4,2)+'/'+substring(I.Ing_fecha,1,2))as datetime) >= '"+F+"' "
ENDIF
IF !EMPTY(ALLTRIM(F2) )
se_filtro=se_filtro + " AND cast( (substring(I.Ing_fecha,7,4)+'/'+substring(I.Ing_fecha,4,2)+'/'+substring(I.Ing_fecha,1,2))as datetime) <= '"+F2+"' "
ENDIF
IF !EMPTY(expinic)
se_filtro=se_filtro + " AND I.ING_CODANTERIOR >= '"+expinic+"' "
ENDIF
IF !EMPTY(expfin)
se_filtro=se_filtro + " AND I.ING_CODANTERIOR <= '"+expfin+"' "
ENDIF
se_filtro=ALLTRIM(se_filtro)
IF !EMPTY(ALLTRIM(se_filtro))
IF(SUBSTR(se_filtro,1,3)=="AND") THEN
se_filtro=SUBSTR(se_filtro,4,(LEN(se_filtro)-3))
ENDIF
strQuery = strQuery +" WHERE I.ing_anio="+TRANSFORM(anio)+" AND "+ se_filtro+ " ORDER BY I.ing_codanterior asc"
else
messagebox("Debe ingresar al menos un filtro...!")
return
ENDIF
*---------------------------
set escape off
Store Sqlstringconnect(vConexion) TO CN
A=SQLSETPROP(CN,"IdleTimeout", 0)
IF CN < 0
= MESSAGEBOX('No se puede conectar', 16, 'Error de conexión SQL')
ELSE
=SQLEXEC(CN,strQuery,'CINGRESOS')
= SQLDISCONNECT(CN)
IF( RECC() >0)
if thisform.Optiongroup1.optvistapreliminar.value=1
KEYBOARD '{CTRL+F10}'
report form "informes\infrepingresos" TO PRINTER PROMPT PREVIEW
KEYBOARD '{CTRL+F10}'
endif
IF ThisForm.Optiongroup1.optprint.value = 1
report form "informes\infrepingresos" NOCONSOLE NOEJECT TO PRINTER PROMPT
ENDIF
IF ThisForm.Optiongroup1.optexcel.value = 1
gcDelimName = ALIAS( ) + '.xls'
gcDelimFile = putfile("Archivos de Excel",alias()+".xls","MS Excel (*.xls)")
IF EMPTY(gcDelimFile)
CANCEL
ENDIF
COPY TO (gcDelimFile) TYPE XL5
ENDIF
ELSE
MESSAGEBOX("No hay registros selecccionados...")
ENDIF
ENDIF