How to export content generated by VB.NET to Excel

Asked

Viewed 135 times

2

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

    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>
vb.net

1 answer

1


You can save your datatable data 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 used gridview for the file to be in the defined style.

Browser other questions tagged vb.net

You are not signed in. Login or sign up in order to post.