User Tools

Site Tools


windows:descargarcomoexcel

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
windows:descargarcomoexcel [2010/08/26 14:27]
rlunaro
windows:descargarcomoexcel [2014/12/24 10:42] (current)
Line 14: Line 14:
  
 Ahora, si reproducimos **exactamente** el formato, hará lo mismo, digo yo, no???  Ahora, si reproducimos **exactamente** el formato, hará lo mismo, digo yo, no??? 
 +
 +===== Segundo paso: hacer el desarrollo ====
 +
 +<​code>​
 +<%@ Language=VBScript %>
 +<%
 +
 +' OJO!!!! ESTO NO FUNCIONA EN MODO DEPURACION
 +' OJO!!!! ESTO NO FUNCIONA EN MODO DEPURACION
 +' OJO!!!! ESTO NO FUNCIONA EN MODO DEPURACION
 +' OJO!!!! ESTO NO FUNCIONA EN MODO DEPURACION
 +
 +Response.Expires = 0
 +server.scriptTimeOut = 3600 ' una hora 
 +
 +dim oTest 
 +dim rs
 +
 +set oTest = server.CreateObject("​componente"​) ​
 +
 +set rs = oTest.hacerunaconsultaquedevuelvaunrecordset( ""​ ) 
 +
 +
 +dim oUtil 
 +dim sTable ​
 +
 +set oUtil = server.CreateObject("​adWrap.util"​ ) 
 +
 +sTable = oUtil.exportQuery( rs, 0 )
 +
 +sContentType = "​application/​octet-stream"​
 +sFileName = "​miconsulta.xls"​
 +
 +Response.AddHeader "​Content-Length",​ len(sTable)
 +Response.ContentType = sContentType
 +Response.AddHeader "​Last-Modified",​ now
 +Response.AddHeader "​Accept-Ranges",​ "​bytes"​
 +Response.AddHeader "​Date",​ now 
 +' cabecera para que el dialogo que aparezca en el navegador del cliente es 
 +' "​abrir"​ o "​guardar como", en lugar de aparecer encastrado en una ventana del 
 +' navegador.
 +Response.AddHeader "​Content-Disposition",​ "​attachment;​filename="""​ & sFileName & """"​
 +
 +Response.write sTable
 +
 +Response.End
 +
 +</​code>​
 +
 +Y el código fuente de la funcion visual basic que convierte un recordset a tabla: ​
 +
 +<​code>​
 +' nFormat = 1 - to HTML <​table><​tr>​....</​tr></​table>​
 +' nFormat = 0 - to CVS data;​data2;​data3
 +Public Function exportQuery(ByVal rsIn As Object, _
 +                           ​Optional ByVal nFormat As Long = 1) As String
 +
 +Dim rsParam As adodb.Recordset
 +Dim oUtil As Object
 +Dim sql As String
 +Dim functionName As String
 +
 +' pon aquí el nombre de la funcion, para registro de errores
 +functionName = "​exportQuery"​
 +
 +On Error GoTo catch
 +
 +Set oUtil = doNew("​util"​)
 +
 +Dim sTableTemplate As String
 +Dim sRowTemplate As String
 +Dim sColumnTemplate As String
 +Dim nColumn As Long
 +Dim sTr As String
 +Dim sTd As String
 +Dim sTable As String
 +Dim sFilteredSemicolon As String
 +Dim sSeparator As String
 +
 +Select Case nFormat
 +    Case 0
 +        ' para sacar un contenido cvs
 +        sTableTemplate = "​$content$"​
 +        sRowTemplate = "​$content$"​ & vbCrLf
 +        sColumnTemplate = """​$content_filtered$"""​
 +        sSeparator = ";"​
 +    Case 1
 +        ' para sacar una tabla html
 +        sTableTemplate = "<​table>"​ & vbCrLf
 +        sTableTemplate = sTableTemplate & "​$content$"​ & vbCrLf
 +        sTableTemplate = sTableTemplate & "</​table>"​ & vbCrLf
 +        sRowTemplate = "<​tr>"​ & vbCrLf
 +        sRowTemplate = sRowTemplate & "​$content$"​ & vbCrLf
 +        sRowTemplate = sRowTemplate & "</​tr>"​ & vbCrLf
 +        sColumnTemplate = "<​td>​$content$</​td>"​ & vbCrLf
 +        sSeparator = ""​
 +    Case Else
 +        ' para sacar un contenido cvs
 +        sTableTemplate = "​$content$"​
 +        sRowTemplate = "​$content$"​ & vbCrLf
 +        sColumnTemplate = """​$content_filtered$"""​
 +        sSeparator = ";"​
 +End Select
 +
 +
 +
 +
 +
 +sTable = ""​
 +If Not rs_empty(rsIn) Then
 +
 +    sTr = ""​
 +
 +    ' for every record
 +    nColumn = 0
 +    sTd = ""​
 +    Do While nColumn < rsIn.Fields.Count
 +    ​
 +        If nFormat = 1 Then
 +            sTd = concat(sTd, Replace(sColumnTemplate,​ "​$content$",​ rsIn.Fields(nColumn).Name))
 +        Else
 +            sFilteredSemicolon = Replace(rsIn.Fields(nColumn).Name,​ sSeparator, ""​)
 +            sTd = concat(sTd, Replace(sColumnTemplate,​ "​$content_filtered$",​ sFilteredSemicolon),​ sSeparator)
 +        End If
 +        nColumn = nColumn + 1
 +    Loop
 +    sTr = sTr & Replace(sRowTemplate,​ "​$content$",​ sTd)
 +    ​
 +    rsIn.MoveFirst
 +    Do While Not rsIn.EOF
 +    ​
 +        ' for every record
 +        nColumn = 0
 +        sTd = ""​
 +        Do While nColumn < rsIn.Fields.Count
 +        ​
 +            If nFormat = 1 Then
 +                sTd = concat(sTd, Replace(sColumnTemplate,​ "​$content$",​ rs_field(rsIn,​ nColumn, ""​)))
 +            Else
 +                sFilteredSemicolon = Replace(rs_field(rsIn,​ nColumn, ""​),​ ";",​ ""​)
 +                sTd = concat(sTd, Replace(sColumnTemplate,​ "​$content_filtered$",​ sFilteredSemicolon),​ sSeparator)
 +            End If ' nFormat = 1
 +            ​
 +            nColumn = nColumn + 1
 +        Loop
 +        sTr = concat(sTr, Replace(sRowTemplate,​ "​$content$",​ sTd))
 +        ​
 +    ​
 +        rsIn.MoveNext
 +    Loop
 +    sTable = concat(sTable,​ Replace(sTableTemplate,​ "​$content$",​ sTr))
 +
 +End If ' not rs_empty(rsIn)
 +
 +
 +
 +
 +final:
 +
 +doDel oUtil
 +
 +exportQuery = sTable
 +
 +Exit Function
 +
 +catch:
 +    ​
 +    Dim txtError As String
 +
 +    txtError = functionName & ": " & Err.Description
 +
 +    oUtil.EscribirLog txtError
 +
 +
 +    '​Devolvemos un resultado de error
 +    exportQuery = ""​
 +
 +    '​Destruimos los objetos
 +    doDel oUtil
 +    ​
 +    Err.Raise Err.Number, App.EXEName,​ txtError
 +
 +End Function
 +
 +' performs the operation cBase = cBase & cIn
 +' adding a separator (cSep) if needed
 +Private Function concat(ByVal cBase As String, ByVal cIn As String, Optional ByVal cSep As String = ""​) As String
 +
 +    If Len(cBase) = 0 Then
 +        concat = cIn
 +    Else
 +        concat = cBase & cSep & cIn
 +    End If ' len(cBase)
 +
 +End Function
 +
 +</​code>​
  
  
windows/descargarcomoexcel.txt · Last modified: 2014/12/24 10:42 (external edit)