Export to Excel data of a table in SQL

0

Hi, I'm doing a platform in asp.net with vb language and I need to export data from a sql table giving it a parameter in the application I'm doing, the only one way I could export this data was using a handler (generic driver), but only doing a normal select, now I need to do it but giving a parameter from the web application.

I leave the code of HANDLER

%@ WebHandler Language="VB" Class="Handler" Debug="true" %>

Imports System
Imports System.Web
Imports System.Data
Imports System.Data.SqlClient

Public Class Handler : Implements IHttpHandler


Public ReadOnly Property IsReusable As Boolean Implements IHttpHandler.IsReusable
    Get
        Throw New NotImplementedException()
    End Get
End Property

Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest

    Dim connectionString As String = "Data Source=(Local); Initial Catalog= SPC; Integrated Security= true"
    Using cnn As New SqlConnection(connectionString)


        Dim selectCommand As String = "select tbl_asignacion_criterios.cod_asigcriterio,tbl_asignacion_criterios.cod_edicion,tbl_edicion.nombre_edicion ,tbl_criterios.nombre_criterio,tbl_detalle_asignacion.cod_criterio from tbl_asignacion_criterios  inner join tbl_edicion on tbl_asignacion_criterios.cod_edicion=tbl_edicion.cod_edicion  inner join tbl_detalle_asignacion on tbl_asignacion_criterios.cod_asigcriterio=tbl_detalle_asignacion.cod_criterio inner join tbl_criterios on tbl_detalle_asignacion.cod_criterio=tbl_criterios.cod_criterio  "
        Dim da As New SqlDataAdapter(selectCommand, cnn)
        Dim ds As New DataSet
        da.Fill(ds)
        ExportDataTableToExcel(ds.Tables.Item(0))

    End Using

End Sub


Private Sub DoSomething()

End Sub

Public Overloads Shared Sub ExportDataTableToExcel(ByVal table As DataTable)
    ExportDataTableToExcel(table, String.Empty, Nothing)
End Sub
Public Overloads Shared Sub ExportDataTableToExcel(ByVal table As DataTable, ByVal captions As Dictionary(Of String, String))
    ExportDataTableToExcel(table, String.Empty, captions)
End Sub
Public Overloads Shared Sub ExportDataTableToExcel(ByVal table As DataTable, ByVal name As String)
    ExportDataTableToExcel(table, name, Nothing)
End Sub
Public Overloads Shared Sub ExportDataTableToExcel(ByVal table As DataTable, ByVal name As String, ByVal captions As Dictionary(Of String, String))
    Dim content As New Text.StringBuilder()
    Dim columnName As String = String.Empty
    For Each column As DataColumn In table.Columns
        If Not captions Is Nothing Then
            If Not captions.TryGetValue(column.ColumnName, columnName) Then
                columnName = column.ColumnName
            End If
        Else
            columnName = column.ColumnName
        End If
        content.Append(columnName & ";")
    Next
    content.Append(Environment.NewLine)
    Dim value As String
    For Each row As DataRow In table.Rows
        For i As Integer = 0 To table.Columns.Count - 1
            value = String.Empty
            If Not row.IsNull(i) Then
                value = row(i).ToString().Replace(";", String.Empty)
            End If
            content.Append(value & ";")
        Next
        content.Append(Environment.NewLine)
    Next
    content.Length = content.Length - 1
    Dim context As HttpContext = HttpContext.Current
    With context.Response
        .Clear()
        .ContentType = "text/csv"
        If String.IsNullOrEmpty(name) Then
            name = DateTime.Now.ToString("ddMMyyyyHHmmss")
        End If
        .AppendHeader("Content-Disposition", String.Format("attachment; filename={0}.csv", name))
        .Charset = Encoding.Unicode.WebName
        .ContentEncoding = Encoding.Unicode
        .Write(content.ToString())
        .End()
    End With
End Sub
    
asked by Jorge Luque 18.01.2018 в 20:44
source

1 answer

0

It is not necessary that you inherit from IHttpHandler. It is enough for you to do a class to recover the data and then from the asp to do the export. Something like that would be enough for you:

Public Class Datos

    Public Shared Sub ObtenerDatos(ByVal parametro As String)

        Dim connectionString As String = "Data Source=(Local); Initial Catalog= SPC; Integrated Security= true"
        Using cnn As New SqlConnection(connectionString)


            Dim selectCommand As String = "select tbl_asignacion_criterios.cod_asigcriterio,tbl_asignacion_criterios.cod_edicion,tbl_edicion.nombre_edicion ,tbl_criterios.nombre_criterio,tbl_detalle_asignacion.cod_criterio from tbl_asignacion_criterios  inner join tbl_edicion on tbl_asignacion_criterios.cod_edicion=tbl_edicion.cod_edicion  inner join tbl_detalle_asignacion on tbl_asignacion_criterios.cod_asigcriterio=tbl_detalle_asignacion.cod_criterio inner join tbl_criterios on tbl_detalle_asignacion.cod_criterio=tbl_criterios.cod_criterio  " +
                " where parametro = " + parametro

            Dim da As New SqlDataAdapter(selectCommand, cnn)
            Dim ds As New DataSet
            da.Fill(ds)
            ExportDataTableToExcel(ds.Tables.Item(0), "Name", Nothing)

        End Using


    End Sub


    Public Overloads Shared Sub ExportDataTableToExcel(ByVal table As DataTable, ByVal name As String, ByVal captions As Dictionary(Of String, String))

        Dim content As New Text.StringBuilder()
        Dim columnName As String = String.Empty
        For Each column As DataColumn In table.Columns
            If Not captions Is Nothing Then
                If Not captions.TryGetValue(column.ColumnName, columnName) Then
                    columnName = column.ColumnName
                End If
            Else
                columnName = column.ColumnName
            End If
            content.Append(columnName & ";")
        Next
        content.Append(Environment.NewLine)
        Dim value As String
        For Each row As DataRow In table.Rows
            For i As Integer = 0 To table.Columns.Count - 1
                value = String.Empty
                If Not row.IsNull(i) Then
                    value = row(i).ToString().Replace(";", String.Empty)
                End If
                content.Append(value & ";")
            Next
            content.Append(Environment.NewLine)
        Next


        content.Length = content.Length - 1
        Dim context As HttpContext = HttpContext.Current
        With context.Response
            .Clear()
            .ContentType = "text/csv"
            If String.IsNullOrEmpty(name) Then
                name = DateTime.Now.ToString("ddMMyyyyHHmmss")
            End If
            .AppendHeader("Content-Disposition", String.Format("attachment; filename={0}.csv", name))
            .Charset = Encoding.Unicode.WebName
            .ContentEncoding = Encoding.Unicode
            .Write(content.ToString())
            .End()
        End With

    End Sub


End Class
    
answered by 22.01.2018 / 14:47
source