TUTORIAL DE LA APLICACIÓN
1. Base de Datos
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
2. VISUAL STUDIO 2005/2008/2010
2.1 DISEÑO DEL FORMULARIO
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":
|
Para el ContextMenuStrip:
|
Para el DataGridView:
|
2.2 Agregar Referencia Excel
Pasos:
- Descargar Dll Aqui
- Click derecho en "Referencias" y elegir la opcion "Agregar Referencia"
- En la pestaña "Examinar" ubicar el Dll descargado previamente
- Verificar que la referencia se haya agregado correctamente tal como se muestra en la siguiente imagen:
2.3 Codificación
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
Video Demostrativo