Primero hay que agregar la referencia en COM, Microsoft Excel 14.0 Object Library.
Creamos un hoja de excel con 6 columnas de la siguiente manera:
Nombre | Ape Pat | Ape Mat | Nombre de Conyugue | Ape Pat | Ape Mat
El diseño del Form es el siguiente:
La Base de datos consta de 2 tablas [Empleado y Conyugue] ligadas por el nombre del empleado. El código es el siguiente:
[Se necesita un Form, 2 DataGridView, 1 Boton, 1 OpenFileDialog]
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop
Public Class Form1
Private excel As New Microsoft.Office.Interop.Excel.Application
Private adaptador As SqlDataAdapter
Private almacendatos As DataSet
Dim ruta As String = "Server=GSUS_DLL\SQLEXPRESS1;integrated security = sspi; database = pruebas; uid = sa; pwd =; "
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
OpenFileDialog1.Filter = "Documento de Excel |*.xls"
OpenFileDialog1.FileName = "*.xls"
OpenFileDialog1.ShowDialog()
End Sub
Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
Try
With excel
.Workbooks.Open(OpenFileDialog1.FileName)
For a As Integer = 1 To 1000 Step 1
If .Range("a" & a).Value = "" Then
a = 1000
Else
Dim conexion As New SqlConnection
Dim Sentencia, Sentencia2 As String
Dim Resultado As Integer
conexion.ConnectionString = ruta
Sentencia = "INSERT INTO empleado VALUES ('" & .Range("a" & a).Value & "', '" & .Range("b" & a).Value & _
"', '" & .Range("c" & a).Value & "')"
Sentencia2 = "INSERT INTO conyugue VALUES ('" & .Range("a" & a).Value & "', '" & .Range("d" & a).Value & _
"', '" & .Range("e" & a).Value & "', '" & .Range("f" & a).Value & "')"
Dim Comando As New SqlCommand(Sentencia, conexion)
conexion.Open()
Resultado = Comando.ExecuteNonQuery
conexion.Close()
Comando = New SqlCommand(Sentencia2, conexion)
conexion.Open()
Resultado = Comando.ExecuteNonQuery
conexion.Close()
End If
Next
.Workbooks.Close()
End With
Catch ex As Exception
End Try
consulta()
End Sub
Public Sub consulta()
Dim conexion As New SqlConnection
conexion.ConnectionString = ruta
adaptador = New SqlDataAdapter("select * from empleado", conexion)
Dim constructor As SqlCommandBuilder = New SqlCommandBuilder(adaptador)
almacendatos = New DataSet()
adaptador.Fill(almacendatos, "empleado")
DataGridView1.DataSource = almacendatos
DataGridView1.DataMember = "empleado"
adaptador = New SqlDataAdapter("select * from conyugue", conexion)
constructor = New SqlCommandBuilder(adaptador)
almacendatos = New DataSet()
adaptador.Fill(almacendatos, "conyugue")
DataGridView2.DataSource = almacendatos
DataGridView2.DataMember = "conyugue"
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
consulta()
End Sub
End Class
