VB.NET - Exportando dados para o Excel (Selecionando dados entre duas datas no MS Access)

Para começar vou listar os artigos nos quais eu já tratei da dupla VB.NET-Excel, sugiro que dê uma olhada se o assunto for novo para você.

Como o assunto é vasto e requereria não um artigo mas um livro, o tratamento é feito via conta gotas: um artigo hoje , outro na semana seguinte, e assim vamos...

Em tempos de VB.NET este artigo procura mostrar como podemos  acessar os dados de um banco de dados e exportá-los para um planilha Excel. (Creio que você já viu este filme...)

Criando o projeto no Visual Basic .NET

Inicie um novo projeto no Visual Studio  com o nome excelVB2005 , ou outro que o valha.

Agora vamos criar uma interface para o usuário. Para fazer isto eu preciso o que que quero fazer.

O que eu quero fazer é exibir os dados da tabela Orders do banco de dados Northwind.mdb em um DataGridView usando um intervalo de datas. O usuário primeiro seleciona a data inicial em seguida a data final e clica em um botão para que os dados sejam extraídos da tabela e exibidos no grid.

Após exibir os dados o usuário poderá exportar os mesmos dados que estão no grid para uma planilha Excel que será criada em tempo de execução. Nem preciso dizer que você tem que ter o Excel instalado na sua máquina. (Eu usei o Excel do office 2003, não testei com versões anteriores mas creio que a única mudança será a referência a biblioteca que você deverá usar.)

Como já temos uma visão geral do serviço a ser feito, creio que uma interface bem simples teria os seguintes componentes:

Componente Nome usado no projeto Propriedade
DataTimePicker dtpInicio Format = Short
  dtpFim Format = Short
DataGridView dgvExcel  
Button btnExibir Image=Grid.ico
  btnExportar Image=Excel.ico
Label lblmensagem  

Obs: Configure as propriedades MinDate e MaxDate para um intervalo de datas razoável. Apliquei dois ícones aos botões de comando usando as propriedade Image.

A tabela Orders possui a seguinte estrutura:

Ao montar a instrução SQL para selecionar os dados da tabela eu escolhi exibir apenas as seguintes  colunas:
  1. OrderID
  2. ShipAddress
  3. ShipCountry
  4. OrderDate

Para poder selecionar os dados no intervalo entre a data inicial e a data final eu vou usar a cláusula BETWEEN ,  desta forma a instrução SELECT ficará assim:

SELECT OrderId, ShipAddress,ShipCountry, OrderDate FROM Orders
WHERE OrderDate Between "#" + dtpInicio.Value.ToShortDateString() + "# And #" + dtpFim.Value.ToShortDateString() + "#"
ORDER BY ShipCountry, OrderDate

Perceba que temos :

Nota: Lembre-se que quando usamos um banco de dados Access os valores do tipo data devem vir envolvidos pela cerquilha(#)

A nossa interface deverá ter um leiaute parecido com o da figura abaixo: (É claro que você pode ser mais criativo do que eu...)

Uma coisa importante que você não deve esquecer e fazer uma referência a biblioteca do Excel no seu projeto. Faça assim:

  1. Clique com o botão direito do mouse sobre o nome do projeto na janela Solution Explorer
  2. Selecione a opção Add Reference e na janela Add Reference marque a aba COM
  3. A seguir procure pela biblioteca do Excel que deve estar exibida como abaixo: (Pode estar diferente na sua máquina, dependendo da sua versão do Excel)

   

Outro ponto importante é como iremos fazer a conexão com o banco de dados. Neste ponto você deve pensar assim: é uma base de dados Access ? Sim é. Sem dúvida alguma vamos usar o provedor OleDb da plataforma .NET. (O Namespace usado será o System.Data.OleDb)

A string de conexão para o banco de dados Northwind.mdb que esta na minha máquina é a seguinte:(Cuidado ! Na sua máquina com certeza será diferente...)

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:/teste/Northwind.mdb;Persist Security Info=False

Que tal se você guardasse essa string no arquivo de configuração da aplicação usando o novo recurso My.Settings ?

Faça Assim:

  1. Clique com o botão direito sobre My Project na janela Solution Explorer e clique em Open
  2. A seguir na selecione a opção Settings e informe o nome para identificar a string o Scope como sendo Application e guarde a string no campo Value conforme a figura abaixo:

   

Para recuperar a string no código você apenas digita : My.Settings.conOleDb

Criando o código do projeto

Agora vamos criar uma procedure chamada obtemDadosBD() que será responsável pela conexão com o banco de dados , extração dos dados , criação do dataset e exibição dos mesmos no datagridview. 

Private Sub ObtemDadosBD()

        lblmensagem.Text = ""

        If (dtpInicio.Value <= dtpFim.Value) Then
            'Declara variáveis
            Dim ds As New DataSet("Orders")
            Dim conexaoBD As New OleDb.OleDbConnection(My.Settings.conOledb)
            Dim da As New OleDb.OleDbDataAdapter
            Dim cmd As New OleDb.OleDbCommand( _
                         ("SELECT OrderId, ShipAddress,ShipCountry, OrderDate  FROM Orders  _
                          WHERE OrderDate Between #" + dtpInicio.Value.ToShortDateString() + "# And #" + dtpFim.Value.ToShortDateString() + "# _
                          ORDER BY ShipCountry, OrderDate"), conexaoBD)
            'Abre a conexão
            Try
                conexaoBD.Open()
            Catch ex As Exception
                lblmensagem.Text = ex.Message
                conexaoBD.Close()
            End Try
            'Vincula o dataAdapter ao objeto Command
            cmd.CommandType = CommandType.Text
            da.SelectCommand = cmd
            da.SelectCommand.Connection = conexaoBD
            'Vincula o da ao dataset e, a seguir vincula ao datagridview
            Try
                da.Fill(ds)
                dgvExcel.DataSource = ds.Tables(0)
            Catch ex As Exception
                lblmensagem.Text = ex.Message
            End Try
            'Fecha a conexao
            conexaoBD.Close()
        Else
            lblmensagem.Text = "A data de início precisar ser anterior a data final..."
        End If
    End Sub

 

Agora vamos criar a rotina mais importante. A rotina exportaDadosExcel que irá exportar os dados para o Excel.

Esta rotina terá que efetuar a conexão com a base de dados novamente usando os parâmetros do intervalo de datas para obter os dados, criar instâncias do Excel, formatar o cabeçalho e exibir os dados na planilha.

 Private Sub exportaDadosExcel()

        lblmensagem.Text = ""
 
        If (dtpInicio.Value <= dtpFim.Value) Then
            'Inicia o Excel e cria um novo workbook/worksheet
            Dim excelApp As New Excel.Application
            Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
            Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
            'Torna o Excel invisível ao usuário até que a planilha seja preenchida
            excelApp.Visible = False
            With excelWorksheet
 
                'Cria uma conexão para obter os dados do  DataSet
                Dim ds As New DataSet("Orders")
                Dim dr As DataRow
                Dim conexaoBD As New OleDb.OleDbConnection(My.Settings.conOledb)
                Dim da As New OleDb.OleDbDataAdapter
 
                Dim cmd As New OleDb.OleDbCommand( _
                           ("SELECT OrderId, ShipAddress,ShipCountry, OrderDate  FROM Orders _
                             WHERE OrderDate Between #" + beginDate.Value.ToShortDateString() + "# And #" + endDate.Value.ToShortDateString() _ 
                            + "#  ORDER BY ShipCountry, OrderDate"), conexaoBD)
 
                'define o indice da célula da qual iremos extrair os dados
                Dim i As Integer = 2
                'Vincula o dataadapter(da) ao objeto Command (cmd)
                Try
                    cmd.CommandType = CommandType.Text
                    da.SelectCommand = cmd
                    da.SelectCommand.Connection = conexaoBD
                    da.Fill(ds)
                Catch ex As Exception
                    lblmensagem.Text = ex.Message
                End Try
 
                'Formata os cabeçalhos das células
                .Range("A1").Value = "Código"
                .Range("A1").Font.Bold = True
                .Range("A1").ColumnWidth = 10
                .Range("B1").Value = "Endereço"
                .Range("B1").Font.Bold = True
                .Range("B1").ColumnWidth = 35
                .Range("C1").Value = "País"
                .Range("C1").Font.Bold = True
                .Range("C1").ColumnWidth = 15
                .Range("D1").Value = "Data"
                .Range("D1").Font.Bold = True
                .Range("D1").ColumnWidth = 10
 
                'Preenche a planilha Excel
                Try
                    For Each dr In ds.Tables(0).Rows
                        .Range("A" & i.ToString).Value = dr("OrderID")
                        .Range("B" & i.ToString).Value = dr("ShipAddress")
                        .Range("C" & i.ToString).Value = dr("ShipCountry")
                        .Range("D" & i.ToString).Value = dr("OrderDate")
                        i += 1
                    Next
                Catch ex As Exception
                    lblmensagem.Text = ex.Message
                End Try
                'Torna o Excel visível
                excelApp.Visible = True
            End With
        Else
            lblmensagem.Text = "A data de início precisar ser anterior a data final..."
        End If
    End Sub

 

Preste atenção que neste código eu estou usando o objeto Application. Sempre que você for trabalhar com instâncias do Excel, Word, etc.. você usa este objeto.

Agora repare que eu crio uma instância da aplicação Excel : Dim excelApp As New Excel.Application

Depois eu crio uma instância do WorkBook :   Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add

E, finalmente da planilha com a qual vou trabalhar:

  Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)

A formatação dos cabeçalhos das células irá trabalhar com a propriedade Range de A1 a D1 pois irei exibir 4 colunas.

          'Formata os cabeçalhos das células
                .Range("A1").Value = "Código"
                .Range("A1").Font.Bold = True
                .Range("A1").ColumnWidth = 10
                .Range("B1").Value = "Endereço"
                .Range("B1").Font.Bold = True
                .Range("B1").ColumnWidth = 35
                .Range("C1").Value = "País"
                .Range("C1").Font.Bold = True
                .Range("C1").ColumnWidth = 15
                .Range("D1").Value = "Data"
                .Range("D1").Font.Bold = True
                .Range("D1").ColumnWidth = 10

Finalmente o preenchimento das células percorre cada DataRow(dr) no DataSet e obtém os dados exibindo-os nas células de A a D.

          'Preenche a planilha Excel
                Try
                    For Each dr In ds.Tables(0).Rows
                        .Range("A" & i.ToString).Value = dr("OrderID")
                        .Range("B" & i.ToString).Value = dr("ShipAddress")
                        .Range("C" & i.ToString).Value = dr("ShipCountry")
                        .Range("D" & i.ToString).Value = dr("OrderDate")
                        i += 1
                    Next

O resultado da aplicação executada para um intervalo de datas entre 15/01/1998 a 20/01/1998 é dado nas figuras abaixo:

Os dados da tabela Orders sendo exibidos para o intervalo informado
Os mesmos dados exportados para o Excel

Ah, ia me esquecendo: as rotinas são chamadas quando o usuário clica nos botões de comando. O código é o seguinte:

Private Sub btnExibir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExibir.Click

    ObtemDadosBD()

End Sub

Private Sub btnExportar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportar.Click

     exportaDadosExcel()

End Sub

Quer mais simplicidade e facilidade do que isto ???

Bom estudo e até o próximo artigo VB.NET...

Veja os Destaques e novidades do SUPER DVD Visual Basic (sempre atualizado) : clique e confira !

Quer migrar para o VB .NET ?

Quer aprender C# ??

Quer aprender os conceitos da Programação Orientada a objetos ?

Quer aprender o gerar relatórios com o ReportViewer no VS 2013 ?

  Gostou ?   Compartilhe no Facebook   Compartilhe no Twitter

Referências:

José Carlos Macoratti