VB .NET - Exportar para EXCEL

Imports System.Data

Imports System.Data.SqlClient

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private StrConnection As String = "Data Source=CARLOSHENRIQUE;Initial Catalog=Northwind;Integrated Security=True"

    Private sqlDa As New SqlDataAdapter

    Private dtb As DataTable

    Private sqlCon As New SqlConnection(StrConnection)

    Private sqlCmd As New SqlCommand

    Private VSql As String

    Private VerificaVsql As String

    'Uma consulta SQL que obtem

    Const SQLExpression As String = "SELECT CustomerID AS Id, CompanyName AS Empresa," & _

                                    "City As Cidade, Region As Regiao, Country As pais FROM Customers ORDER BY CompanyName;"

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        PCarregarDados()

        dgvCustomer.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

    End Sub

    'Carrega os dados iniciais

    Private Sub PCarregarDados()

        Try

            With sqlCmd

                .CommandType = CommandType.Text

                .CommandText = SQLExpression

                .Connection = sqlCon

            End With

            With sqlDa

                .SelectCommand = sqlCmd

                dtb = New DataTable

                .Fill(dtb)

                dgvCustomer.DataSource = dtb

            End With

        Catch ex As Exception

            MsgBox(ex.Message)

        Finally

            sqlCon.Close()

        End Try

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Me.Cursor = Cursors.WaitCursor

        If Me.txtFiltro.Text = "" Then

            VerificaVsql = SQLExpression

        Else

            VerificaVsql = VSql

        End If

        'Variáveis usadas para a conexão co o banco de dados e o retorno dos dados

        Dim cn As New SqlConnection(StrConnection)

        Dim cmd As New SqlCommand(VerificaVsql, cn)

        cmd.CommandType = CommandType.Text

        cn.Open()

        'Define um Data Reader.

        Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        'A fim de dimensionar o array para os nomes dos campos

        'o número de colunas precisa ser obtido

        Dim contaColuna As Int32 = dr.FieldCount - 1

        Dim camposArr(0, contaColuna) As String

        Dim contaLinha As Int32 = 0

        'O DataReader é um conjunto de dados somente para frente e somente leitura

        'Por isso não podemos saber o número de registros até que ele os retorne

        'a solução possível é estimar o numero de registro e usar

        Dim maxRegistros As Int32 = 10000

        Dim DataArr(maxRegistros, contaColuna) As Object

        'Preenche o array dos nomes dos campos usando o método GetName do DataReader

        For NameCounter As Int32 = 0 To contaColuna

            camposArr(0, NameCounter) = dr.GetName(NameCounter)

        Next

        'Preenche o array de registros lendo todos os registros no DataReader

        While dr.Read

            For RecordCounter As Int32 = 0 To contaColuna

                DataArr(contaLinha, RecordCounter) = dr.Item(RecordCounter)

            Next

            contaLinha = contaLinha + 1

        End While

        'Fecha a conexão e o DataReader.

        cn.Close()

        dr.Close()

        'Definição das variáveis para tratar com o Excel.

        Dim xlApp As New Excel.Application

        Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Add( _

                                        Excel.XlWBATemplate.xlWBATWorksheet)

        Dim xlWSheet As Excel.Worksheet = CType(xlWBook.Worksheets(1), Excel.Worksheet)

        Dim xlCalc As Excel.XlCalculation

        'Salva a configuração atual para o modo de calculo do Excel e a desliga

        With xlApp

            xlCalc = .Calculation

            .Calculation = Excel.XlCalculation.xlCalculationManual

        End With

        'Escreve o nome dos campos e os dados para a planilha destino

        With xlWSheet

            .Range(.Cells(1, 1), .Cells(1, contaColuna + 1)).Value = camposArr

            .Range(.Cells(2, 1), .Cells(contaLinha + 2, contaColuna + 1)).Value = DataArr

            .UsedRange.Columns.AutoFit()

        End With

        'Torna o Excel disponível para o CLiente

        With xlApp

            .Visible = True

            .UserControl = True

            'Restaura o modo de calculo

            .Calculation = xlCalc

        End With

        'Libera os objetos da memória

        cmd.Dispose()

        cn.Dispose()

        dr = Nothing

        cmd = Nothing

        cn = Nothing

        xlWSheet = Nothing

        xlWBook = Nothing

        xlApp = Nothing

        GC.Collect()

        Me.Cursor = Cursors.Default

    End Sub

    'Carrega os dados iniciais

    Private Sub PBuscaRegistro()

        Try

            VSql = "SELECT CustomerID AS Id, CompanyName AS Empresa," & _

                                  "City As Cidade, Region As Regiao, Country As pais " & _

                                  "FROM Customers where CompanyName Like '%" & txtFiltro.Text & "%'" & " ORDER BY CompanyName;"

            With sqlCmd

                .CommandType = CommandType.Text

                .CommandText = VSql

                .Connection = sqlCon

            End With

            With sqlDa

                .SelectCommand = sqlCmd

                dtb = New DataTable

                .Fill(dtb)

                dgvCustomer.DataSource = dtb

            End With

        Catch ex As Exception

            MsgBox(ex.Message)

        Finally

            sqlCon.Close()

        End Try

    End Sub

    Private Sub txtFiltro_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtFiltro.TextChanged

        PBuscaRegistro()

    End Sub

End Class

 Espero ter ajudado... Até mais. Abraços!

Pegue o projeto neste link: http://www.mediafire.com/?ajm1wac15eczmaf