How to export content generated by VB.NET to Excel

1

I have a page that loads a table, and I wanted to export it to Excel, how can I do that?

    Public Class RelRegepaCap
        Inherits IcatuHartford.Web.UI.Pagina


        Protected Sub Pag

e_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            PopularControles()
        End If
    End Sub

    Private Sub PopularControles()
        txtCPF.Attributes.Add("onkeypress", "MascInteiro();")
        txtTitulo.Attributes.Add("onkeypress", "MascInteiro();")

        Dim oRelFacade As New RelRegepaCapFacade
        Dim Dt As New DataTable

        With Me.cboProdutos
            .DataSource = oRelFacade.GetProduto(Me.CacheApp.Item(VariaveisContexto.Veiculo.ToString()))
            .DataBind()
        End With
    End Sub
    Public Sub FiltraProduto(ByVal sender As Object, ByVal e As System.EventArgs) Handles CheckBoxPU.CheckedChanged


    End Sub

    Protected Sub cmdContinuar_Click(sender As Object, e As EventArgs) Handles cmdContinuar.Click
        Dim RelRegepaCapFac As New RelRegepaCapFacade
        Dim dt As DataTable
        Dim cod_veiculo As String
        Dim cpfcnjp As String
        Dim titulo As String
        Dim useruniqueID As Guid
        Dim CodProduto As String
        Dim TpProduto As String


        useruniqueID = New Guid(Me.Usuario.Identity.Id)
        cod_veiculo = Me.CacheApp.Item(VariaveisContexto.Veiculo.ToString())
        cpfcnjp = txtCPF.Text
        titulo = txtTitulo.Text
        CodProduto = cboProdutos.SelectedValue

        If CheckBoxPM.Checked Then
            TpProduto = "M"
        ElseIf CheckBoxPU.Checked Then
            TpProduto = "U"
        Else
            TpProduto = String.Empty
        End If

        Try
            With Me.rptRegepa
                .DataSource = RelRegepaCapFac.GetRelRegepa(cod_veiculo, cpfcnjp, titulo, useruniqueID, CodProduto, TpProduto)
                .DataBind()
            End With

        Finally
            RelRegepaCapFac = Nothing
        End Try
    End Sub

    Protected Sub cmdExportarExcel_Click(sender As Object, e As EventArgs) Handles cmdExportarExcel.Click
        GeraRelatorioExcel()

    End Sub
    Private Sub GeraRelatorioExcel()
        Dim strScript As String
        Dim params As String() = New String(13) {}

        params(0) = ConfigurationSettings.AppSettings("IHInternetService_Relatorios") & "RelRegepaCapPopUpExcel.aspx"
        params(3) = Me.Usuario.Identity.Veiculo

        strScript = String.Format("<script>window.open(CodVeiculo={3}" & _
                "','_blank','width=780,height=460');</script>", params)

        Me.RegisterClientScriptBlock("POPUP", strScript)

    End Sub


End Class




    <%@ Page Language="vb" AutoEventWireup="false" CodeBehind="RelRegepaCap.aspx.vb"
    Inherits="IHInternetService_Relatorios.RelRegepaCap" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script>
        function Esconde() {
            document.all("cboStatus").style.display = "none";
        }


    </script>
</head>
<body>
    <form id="Form1" method="post" runat="server" onkeypress="if(event.which || event.keyCode){if ((event.which == 13) || (event.keyCode == 13)) {document.getElementById('cmdContinuar').click();return false;}} else {return true};">
    <div id="dvValidaRelClientes" class="Erro">
    </div>
    <table class="caixa_home" id="Table4" cellspacing="0" cellpadding="3" width="640px"
        border="0">
        <tr>
            <td>
                <table width="100%" cellspacing="0" cellpadding="4" border="0" class="linha_caixa_home">
                    <tbody>
                        <tr class="fundo_titulo_tabelas_internas">
                            <td>
                                Buscar por:
                            </td>
                        </tr>
                        <tr class="fundo_caixa_home">
                            <td>
                                <table width="100%" cellspacing="1" cellpadding="3" border="0" class="TiraBorda">
                                    <tbody>
                                        <tr class="fundo_caixa_home">
                                            <td class="Texto2">
                                                Tipo de Produtos:
                                            </td>
                                            <td class="fundo_cinza Texto_1">
                                                <asp:CheckBox ID="CheckBoxPM" runat="server" Text="M" OnCheckedChanged="FiltraProduto" />
                                                <asp:CheckBox ID="CheckBoxPU" runat="server" Text="U" OnCheckedChanged="FiltraProduto" />
                                            </td>
                                        </tr>
                                        <tr class="fundo_caixa_home">
                                            <td class="Texto2">
                                                Produtos
                                            </td>
                                            <td class="fundo_cinza Texto_1">
                                                <asp:ListBox ID="cboProdutos" runat="server" CssClass="InputP" DataValueField="PRODUTO"
                                                    DataTextField="NOMNEGOCIACAO" Height="54px" SelectionMode="Single" Width="349px">
                                                </asp:ListBox>
                                            </td>
                                        </tr>
                                        <tr>
                                            <td class="Texto2">
                                                CPF/CNPJ do cliente
                                            </td>
                                            <td class="fundo_cinza Texto_1">
                                                <asp:TextBox ID="txtCPF" CssClass="InputP" Width="140px" runat="server" MaxLength="14"></asp:TextBox>
                                            </td>
                                        </tr>
                                        <tr>
                                            <td class="Texto2">
                                                Título
                                            </td>
                                            <td class="fundo_cinza Texto_1">
                                                <asp:TextBox ID="txtTitulo" CssClass="InputP" Width="140px" runat="server" MaxLength="11"></asp:TextBox>
                                            </td>
                                        </tr>
                                    </tbody>
                                </table>
                                <table width="100%" align="center">
                                    <tr>
                                        <td width="25%">
                                        </td>
                                        <td align="center">
                                            <asp:Button ID="cmdContinuar" CssClass="botao_formulario" runat="server" Text="Consultar">
                                            </asp:Button>
                                        </td>
                                        <td align="center">
                                        </td>
                                        <td width="25%">
                                        </td>
                                    </tr>
                                </table>
                            </td>
                        </tr>
                    </tbody>
                </table>
            </td>
        </tr>
        <tr>
            <td>
                <asp:Panel ID="pnlRelatorio" runat="server" Visible="<%#rptRegepa.Items.Count>0%>">
                    <asp:Repeater ID="rptRegepa" runat="server">
                        <HeaderTemplate>
                            <table id="resultado" cellspacing="0" cellpadding="4" width="100%" border="0">
                                <tr>
                                    <td>
                                        <table class="linha_caixa_home" cellspacing="0" cellpadding="4" width="100%" border="0">
                                            <tr class="fundo_cinza Texto2">
                                                <td>
                                                     Nº do Título
                                                </td>
                                                <td width="20%">
                                                    Série
                                                </td>
                                                <td>
                                                    Proposta
                                                </td>
                                                <td>
                                                   Nome Cliente
                                                </td>
                                                <td>
                                                    Cpf/Cnpj Cliente
                                                </td>
                                                <td>
                                                    Conta Corrente
                                                </td>
                                                <td>
                                                    Cód. Produto
                                                </td>
                                                <td>
                                                    Nome Produto
                                                </td>
                                                <td>
                                                    Status
                                                </td>
                                                <td>
                                                    Vlr. Mensalidade
                                                </td>
                                                <td>
                                                    Última Parcela Paga
                                                </td>
                                                <td>
                                                    Data Último Pagamento
                                                </td>
                                                <td>
                                                    Vlr. Reserva
                                                </td>
                                            </tr>
                                            <tr class="fundo_caixa_home">
                                                <td colspan="13" style="height: 3px;">
                                                </td>
                                            </tr>
                        </HeaderTemplate>
                        <ItemTemplate>
                            <tr id="trClienteSorte" runat="server">
                                <td class="Texto_1">
                                    <%#Container.DataItem("TITULO")%>
                                </td>
                                <td class="Texto_1">
                                    <%#Container.DataItem("SERIE")%>
                                </td>
                                <td class="Texto_1">
                                     <%#Container.DataItem("PROPOSTA")%>
                                </td>
                                <td class="Texto_1">
                                     <%#Container.DataItem("NOME_CLIENTE")%>
                                </td>
                                <td class="Texto_1">
                                     <%#Container.DataItem("CPF_CNPJ_CLIENTE")%>
                                </td>
                                <td class="Texto_1">
                                    <%#Container.DataItem("CONTA_CORRENTE")%>
                                </td>
                                <td class="Texto_1">
                                    <%#Container.DataItem("PRODUTO")%>
                                </td>
                                <td class="Texto_1">
                                    <%#Container.DataItem("NOMNEGOCIACAO")%>
                                </td>
                                 <td class="Texto_1">
                                    <%#Container.DataItem("STATUS_TITULO")%>
                                </td>
                                 <td class="Texto_1">
                                    <%#Databinder.Eval(Container.DataItem,"VALOR_MENSALIDADE","{0:N2}")%>
                                </td>
                                <td class="Texto_1">
                                    <%#Container.DataItem("ULTIMA_PARCELA_PAGA")%>
                                </td>
                                <td class="Texto_1">
                                   <%#Databinder.Eval(Container.DataItem,"DT_ULTIMO_PAGAMENTO","{0:dd/MM/yyyy}")%>
                                </td>
                                 <td class="Texto_1">
                                    <%#DataBinder.Eval(Container.DataItem, "VALOR_RESERVA_CLIENTE", "{0:N2}")%>
                                </td>


                            </tr>
                        </ItemTemplate>
                        <FooterTemplate>
                            </TABLE> </TD> </TR> </TABLE>
                        </FooterTemplate>
                    </asp:Repeater>
                    </br>
                    <table id="exportar" cellspacing="0" cellpadding="0" width="100%" align="center"
                        border="0" runat="server" visible="<%#rptRegepa.Items.Count>0%>">
                        <tr>
                            <td width="30%">
                            </td>
                            <td align="center" width="40%">
                                <asp:Button ID="cmdExportarExcel" CssClass="botao_formulario" runat="server" Text="Exportar Excel">
                                </asp:Button>
                            </td>
                            <td width="30%">
                            </td>
                        </tr>
                    </table>
                </asp:Panel>
            </td>
        </tr>
    </table>
    </form>
</body>
</html>
    
asked by anonymous 29.06.2015 / 22:08

1 answer

0

You can write data from your datatable to a Session and read it when opening a new form (export.aspx for example):

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim dt As DataTable = TryCast(Session("GRID"), DataTable)
    gvLista.DataSource = dt
    gvLista.DataBind()
    Dim sNomeArquivo As String = "Arquivo" & CStr(Format(Now(), "dd-MM-yyyy") & ".xls")
    Dim attachment As String = ("attachment; filename=" & sNomeArquivo)
    Response.ClearContent()
    Response.AddHeader("content-disposition", attachment)
    Response.ContentType = "application/ms-excel"
    Dim sw As New System.IO.StringWriter()
    Dim htw As New HtmlTextWriter(sw)
    gvLista.RenderControl(htw)
    Response.Write(sw.ToString())
    Session("GRID") = Nothing
End Sub

In the above case I used the gridview for the file to stay in the defined style.

    
11.07.2015 / 06:29