VB .NET - Exportando dados de um GridView para o Excel (revisão)

VB .NET - Exportando dados de um GridView para o Excel (EPPlus)

Neste tutorial vamos rever como podemos exportar dados a partir de um controle GridView para uma planilha Excel gerando um genuíno arquivo .xlsx usando a linguagem VB .NET em uma aplicação ASP .NET.

Se você procurar na internet vai encontrar incontáveis artigos que mostram como exportar o conteúdo de um GridView para o Excel.

Para alcançar esse objetivo vamos usar uma livraria .NET gratuita chamada EPPlus que você pode baixar neste link: http://epplus.codeplex.com/

Esta ferramenta lê e escreve arquivos .xlxs usando o formato Open XML. É possível também usar o SDK Open XML diretamente mas a biblioteca EPPlus é mais simples de usar.

Os recursos usados no projeto deste artigo foram:

Visual Studio 2015 Community
Microsoft Excel versão 2007;
Conhecimentos básicos sobre planilhas e células;
Epplus

Criando o projeto

Abra o VS 2015 Community e crie um novo projeto (File-> New Project);

Selecione a linguagem Visual Basic -> Web e o template Web Forms ;

Informe o nome Exportar_GridView_Excel e clique no botão OK;

Escolha a opção Empty e marque Web Forms de forma a criar uma aplicação ASP .NET Web Forms vazia.

No menu Project clique em Add New Item e selecione o template Web Form informando o nome Default.aspx para criar uma página web no projeto.

Incluindo a biblioteca EPPlus via Nuget

Para incluir a biblioteca EPPlus no projeto vamos usar o Nuget.

Clique no menu Tools e a seguir em Nuget Package Manager -> Manage Nuget Packages for solution;

Selecione a biblioteca EPPlus e clique no botão Install;

Criando a interface com o usuário na página Default.aspx

Agora na página Default.aspx inclua um controle GridView(gdvDados) e um controle Button(Exportar_Excel) a partir da ToolBox;

O código gerado é visto a seguir:

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" 
Inherits="Exportar_GridView_Excel._Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <h3>Macoratti - Net </h3>
    <hr />
    <form id="form1" runat="server">
    <div>
     <asp:GridView ID="gdvDados" runat="server" />
     <asp:Button ID="ExportarExcel" runat="server" Text="Exportar para o Excel" OnClick="ExportarExcel_Click" />
    </div>
    </form>
</body>
</html>

Criando o Entity Data Model do banco de dados Northwind

Para preencher o controle GridView vamos usar o banco de dados Northwind.mdf e criar um Entity Data Model na pasta Models do projeto.

Clique com o botão direito do mouse sobre a pasta Models e a seguir em Add -> New Item;

Selecione a guia Data e clique em ADO .NET Entity Data Model, informe o nome NorthwindModel e clique no botão Add;

A seguir selecione a opção EF Designer from database e clique em Next>:

Para selecionar o banco de dados Northwind.mdf que criamos clique em New Connection;

Selecione o servidor SQL Server e o banco de dados e clique no botão OK;

Confirme a conexão criada e salve a string de conexão no arquivo web.config clicando em Next>:

Selecione a tabela Products e marque as opções conforme a figura abaixo clicando em Finish:

Ao final teremos o nosso modelo de entidades mapeado para a tabela gerada conforme a figura a seguir:

Após isso defina o seguinte namespace no arquivo code-behind página Default.aspx.vb:

Imports System.IO
Imports OfficeOpenXml

A primeira tarefa será preencher o controle DataGridView com dados e vamos fazer isso usando o método Products do contexto NorthwinEntities gerado pelo Entity Framework.

No evento Load do formulário inclua o código abaixo:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        gdvDados.DataSource = GetProdutos.ToList()
        gdvDados.DataBind()
End Sub

A seguir vamos definir o método GetProdutos() que usa o método Products do contexto que retorna uma lista dos produtos cadastrados:

Public Function GetProdutos() As List(Of Product)
        Using context = New NorthwindEntities()
            Return context.Products.ToList()
        End Using
End Function

No evento Click do botão - Exportar para o Excel >> - temos o código onde percorremos cada linha e célula do DataGridView e exportando para o Excel:

 Protected Sub ExportarExcel_Click(sender As Object, e As EventArgs) Handles ExportarExcel.Click
        Dim products = GetProdutos()
        gdvDados.DataSource = products
        gdvDados.DataBind()
        Dim excel As New ExcelPackage()

        Dim workSheet = excel.Workbook.Worksheets.Add("Products")
        Dim totalCols = gdvDados.Rows(0).Cells.Count
        Dim totalRows = gdvDados.Rows.Count
        Dim headerRow = gdvDados.HeaderRow
        Try
            For i = 1 To totalCols
                workSheet.Cells(1, i).Value = headerRow.Cells(i - 1).Text
            Next
            For j = 1 To totalRows
                For i = 1 To totalCols
                    Dim product = products.ElementAt(j - 1)
                    workSheet.Cells(j + 1, i).Value = product.GetType().GetProperty(headerRow.Cells(i - 1).Text).GetValue(product, Nothing)
                Next
            Next
            Using memoryStream = New MemoryStream()
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                Response.AddHeader("content-disposition", "attachment;  filename=products.xlsx")
                excel.SaveAs(memoryStream)
                memoryStream.WriteTo(Response.OutputStream)
                Response.Flush()
                Response.End()
            End Using
            lblmsg.Text = " A planilha Excel foi gerada com sucesso !!"
        Catch ex As Exception
            lblmsg.Text = " erro ao gerar planilha Excel : " + ex.Message
        End Try
    End Sub

O método GetProdutos retorna uma lista de produtos usando uma consulta LINQ. Este código chama este método e vincula os dados no GridView que não será renderizado para HTML. Esta vinculação é necessária porque o GridView fornece uma forma simples de obter os nomes das colunas para a planilha via propriedade HeadersRow.

Primeiro criamos um ExcelPackage o qual representa um woorkbook. Uma planilha é adicionada e então fazemos a iteração sobre HeadersRow para fornecer os valores para a primeira linha da célula na planilha. O índice de linhas e colunas no EPPlus possui índice baseado em 1.

A seguir a lista de produtos é iterada para obter os valores para as células usando Reflection para obter os valores das propriedades que estiverem vinculadas ao GridView.

Finalmente o Contentype do Response é definido e o pacote é salvo como um stream o qual esta escrito para a propriedade OutuputStream do objeto Response.

Você obterá um download da planilha.

Executando o projeto iremos obter o seguinte resultado:

Clicando no botão - Exportar para o Excel - teremos o diálogo para download da planilha products.xlsx

Simples, prático e útil...

Pegue projeto completo aqui : Exportar_GridView_Excel.zip (sem as referências)

(Disse Jesus aos fariseus) Hipócritas, bem profetizou Isaías a vosso respeito, dizendo:
Este povo se aproxima de mim com a sua boca e me honra com os seus lábios, mas o seu coração está longe de mim.
Mas, em vão me adoram, ensinando doutrinas que são preceitos dos homens.
Mateus 15:7-9