Export Bank Table to XLSX without Interop

1

In the code below a Query was assigned to the sQuery variable.

I need to somehow export this Query to Excel using some feature available for C # WebForm other than Interop strong>.

 Dim da = New OleDbDataAdapter(sQuery, ConnectionString)
    Dim dt = New DataTable()
    da.Fill(dt)
    Dim GridView1 As New GridView()
    Dim iTipoExport As Byte


        Dim sstr As String

        Session("Arquivo") = sNomeArquivo

        CaminhoDiretorio = Server.MapPath("~/Download/" & sNomeArquivo & ".txt")

        FileOpen(1, CaminhoDiretorio, OpenMode.Binary)
        sstr = ""
        For k As Integer = 0 To dt.Columns.Count - 1
            sstr = sstr & dt.Columns(k).ColumnName + "^"
        Next
        sstr = Mid(sstr, 1, Len(sstr) - 1)
        sstr = sstr & vbCrLf
        FilePut(1, sstr)

        For i As Integer = 0 To dt.Rows.Count - 1
            sstr = ""
            For k As Integer = 0 To dt.Columns.Count - 1
                sstr = sstr & dt.Rows(i)(k).ToString() + "^"
            Next
            sstr = Mid(sstr, 1, Len(sstr) - 1)
            sstr = sstr & vbCrLf
            FilePut(1, sstr)
        Next

        FileClose(1)
        AppExcelExport = CreateObject("Excel.Application")            
        AppExcelExport.Workbooks.OpenText(CaminhoDiretorio, StartRow:=1, DataType:=Excel.XlTextParsingType.xlDelimited, TextQualifier:=Excel.XlTextQualifier.xlTextQualifierNone, Other:=True, OtherChar:="^")
        WboExcelExport = AppExcelExport.Workbooks(1)
        WshExcelExport = WboExcelExport.Sheets(1)
        WshExcelExport.Select()
        RngExcelExport = WshExcelExport.Rows(1)
    
asked by anonymous 06.06.2018 / 18:16

2 answers

0

Good morning, you can use the free .NET library called EPPlus. Below is a simple how to use tutorial, I did not put the code here because it is large.

link

    
12.06.2018 / 16:04
0

I use a component that creates the XLSX directly through OpenXML not needing Excel installed on the machine or server.

Here's an example:

link

    
12.06.2018 / 16:11