TUTORIAL EXPORTAR A EXCEL DATAGRIDVIEW DESDE UN MENU CONTEXTUAL EN VB.NET

TUTORIAL DE LA APLICACIÓN

IF  EXISTS (SELECT name FROM sys.databases 
            WHERE name = 'BD_Tutoriales')
   
DROP DATABASE BD_Tutoriales
GO
CREATE DATABASE BD_Tutoriales
GO
USE BD_Tutoriales
GO
SET LANGUAGE SPANISH
GO
SET DATEFORMAT DMY
GO
SET NOCOUNT ON
GO


CREATE TABLE Cliente (
       Cliente_ID           char(5) NOT NULL,
       Cli_Nombre           varchar(30) NULL,
       Cli_Apellidos        varchar(30) NULL,
       Cli_FechaNac         datetime NULL,
       Cli_Sexo             char(1) NULL,
       Cli_DNI              char(8) NULL,
       Cli_Direccion        varchar(30) NULL,
       Distrito_ID          char(5) NOT NULL,
       Cli_Telefono         varchar(10) NULL,
    Cli_EstadoCivil      varchar(30) NULL,
       Cli_Email            varchar(50) NULL,
       Cli_Password         char(6) NULL,
       Cli_NroCuenta        char(10) NULL,
       Cli_Estado           char(1) NULL
)

GO

ALTER TABLE Cliente
       ADD PRIMARY KEY (Cliente_ID ASC)

GO

-----------------------------CLIENTES------------------------------

INSERT INTO Cliente VALUES ('C0001','Juan ','Quispe Vallejos','21/06/1974','M','46791346','AV Javier Prado 233','DIS02','654-6689','CASADO','juanqui@hotmail.com','666666','1911250709','1')
INSERT INTO Cliente VALUES ('C0002','Lucho','Mamani Luque','12/05/1975','M','48591526','Av.las Alomos 560','DIS15','798-4598','CASADO','kj_@hotmail.com','794613','1921853100','1')
INSERT INTO Cliente VALUES ('C0003','Luis','Arias Castro','18/09/1974','M','45926132','AV la molina 269','DIS41','411-4562','SOLTERO','ahg_@hotmail.com','134679','1931382201','1')
INSERT INTO Cliente VALUES ('C0004','Alberto','Moreno Manco','16/06/1973','M','47948592','Jr. chalaca 598','DIS51','452-9654','CASADO','poi@hotmail.com','895623','1921305400','1')
INSERT INTO Cliente VALUES ('C0005','Mario','Hidalgo Condori','15/06/1975','M','45138841','av.arequipa 458','DIS31','456-8974','SOLTERO','jhg_45@hotmail.com','235689','1911939502','1')
INSERT INTO Cliente VALUES ('C0006','Manuel','Villalta Talaverano','01/04/1974','M','42968574','AV Inca Garcilazo','DIS45','564-9874','CASADO','fd14@hotmail.com','784512','1961651800','1')
INSERT INTO Cliente VALUES ('C0007','Daniel','Tarazona Panda','05/02/1974','M','48748565','AV Javier Prado 145','DIS16','855-6598','CASADO','dsa_154@hotmail.com','124578','1911891208','1')
INSERT INTO Cliente VALUES ('C0008','Monica','Yangua Pajuelo','12/06/1974','F','47437602','Jr.las cumbres 121','DIS15','798-4879','SOLTERO','auy_45@hotmail.com','741852','1951112209','1')
INSERT INTO Cliente VALUES ('C0009','Mario','Aguila Ramos','10/01/1974','M','44020877','AV Las palmeras 244','DIS12','456-9874','CASADO','aa_14@hotmail.com','963852','2004444508','1')
INSERT INTO Cliente VALUES ('C0010','Luisa','Quesada Blas','21/02/1975','F','43215487','AV Arequipa 452','DIS15','123-6542','CASADO','rosa_45@hotmail.com','666666','1982222308','1')

GO

SOLO configurar las propiedades indicadas en cada uno de los controles del formulario(Ver Cuadro de Controles), lo que no se indica dejarlo tal como está.

Cuadro de Controles
Botones	ContextMenuStrip	DataGridView
Para el boton "Cargar Datos": Name: btnCargarDatos FlatStyle: Flat Text: Cargar Datos	Para el ContextMenuStrip: Name: cmsMenu Items(Coleccion): Agregar MenuItem Para el MenuItem: Name: tsmiXls Text: Exportar a Excel	Para el DataGridView: Name: dgvExportar AllowUserToAddRows: False AllowUserToDeleteRows: False ReadOnly: True ContextMenuStrip: cmsMenu SelectionMode: FullRowSelect AlternatingRowsDefaultCellStyle >> Backcolor: GradientInactiveCaption

Ingresar el siguiente código dentro del Formulario

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

    Dim dt As New DataTable

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Llamamos a la subrutina que crea el datatable
        CrearTabla()
    End Sub

    'Subrutina para Crear el DataTable
    Private Sub CrearTabla()
        If dgvExportar.Columns.Count = 0 Then
            With dt.Columns
                .Add("Cliente_Id")
                .Add("Cli_Nombres")
                .Add("Cli_FechaNac")
                .Add("Cli_Sexo")
                .Add("Cli_Dni")
            End With
            dgvExportar.DataSource = dt

            With dgvExportar
                .Columns(0).HeaderText = "Id"
                .Columns(0).Width = 70

                .Columns(1).HeaderText = "Nombres"
                .Columns(1).Width = 180

                .Columns(2).HeaderText = "Fecha Nacimiento"
                .Columns(2).Width = 75

                .Columns(3).HeaderText = "Sexo"
                .Columns(3).Width = 40

                .Columns(4).HeaderText = "Dni"
                .Columns(4).Width = 70
            End With

        End If
    End Sub

    Private Sub btnCargarDatos_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCargarDatos.Click
        'Cadena de conexion a la BD
        'Nota: Configurar password
        Dim cadconex As String = "server=.;database=Bd_Tutoriales;user id=sa; password=xxxx"
        Dim cn As New SqlConnection(cadconex)
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter

        Try
            With cmd
                .Connection = cn
                'Consultamos en la BD los campos necesarios que seran llenados en el DataTable 
                .CommandText = "select cliente_id,cli_nombre+' '+cli_apellidos as cli_nombres,cli_fechanac,cli_sexo,cli_dni from cliente"
                .CommandType = CommandType.Text
            End With
            da.SelectCommand = cmd
            'Llenamos el datatable
            da.Fill(dt)

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub

    'Funcion para Exportar a Excel
    Private Function Exportando_a_Excel(ByVal vDt As DataTable, ByVal vGrid As DataGridView) As Boolean
        Dim xls As New Excel.Application
        With xls
            .Visible = True
            .Workbooks.Add()
            Dim I, J, K As Integer

            For J = 0 To vGrid.Columns.Count - 1

                If vGrid.Columns(J).Visible = True Then            
                    Dim vHeader As String = vGrid.Columns.Item(J).HeaderText.ToString.Trim

                    If K = 0 Then
                        If vHeader <> "" Then
                            .Cells(1, J + 1).Value = vHeader
                        End If
                    Else
                        If vHeader <> "" Then
                            .Cells(1, K + 1).Value = vHeader
                            K = K + 1
                        End If
                    End If
                Else
                    If K = 0 Then

                        K = J
                    End If

                End If
            Next

            K = 0

            For I = 0 To vDt.Rows.Count - 1
                For J = 0 To vGrid.Columns.Count - 1

                    If vGrid.Columns(J).Visible = True Then

                        If K = 0 Then

                            If vGrid.Columns.Item(J).HeaderText.ToString.Trim <> "" Then
                                If IIf(vGrid.Columns(J).Name.ToString.Trim <> "", vGrid.Columns(J).Name.Substring(4, 1).ToUpper, "") = "C" Then
                                    .Cells(I + 2, J + 1).numberformat = "@"
                                End If
                                .Cells(I + 2, J + 1).Value = vGrid.Rows(I).Cells(J).Value.ToString.Trim  'Consulta.Rows(I)(J)
                            End If

                        Else


                            If vGrid.Columns.Item(J).HeaderText.ToString.Trim <> "" Then
                                If IIf(vGrid.Columns(J).Name.ToString.Trim <> "", vGrid.Columns(J).Name.Substring(4, 1).ToUpper, "") = "C" Then
                                    .Cells(I + 2, K + 1).numberformat = "@"
                                End If
                                .Cells(I + 2, K + 1).Value = vGrid.Rows(I).Cells(J).Value.ToString.Trim
                            End If

                            K = K + 1

                        End If

                    Else
                        If K = 0 Then
                            K = J
                        End If

                    End If
                Next
            Next


            .Rows("1:1").Select()
            .Selection.Font.Bold = True
            .Cells.Select()
            .Selection.ColumnWidth = 0
            .Cells.EntireColumn.AutoFit()
            .Range("A1").Select()

            .Sheets("Hoja1").name = "Data Exportada"
            .Sheets("Hoja2").delete()
            .Sheets("Hoja3").delete()

        End With
        Return True
    End Function

    'Evento Click del Menu Contextual "Exportar a Excel"
    Private Sub tsmiXls_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsmiXls.Click
        Exportando_a_Excel(dt, dgvExportar)
    End Sub
End Class