none
Ejemplo de acceso a Datos con Excel en VB.NET RRS feed

  • Pregunta

  •  Hola buenas noches, como especifico en en titulo, tendrán por allí ejemplo para conectarme a una hoja de calculo de excel, como si fuera una tabla de una base de datos, y poder hacer insert, update, delete y claro select en cada uno de los campos de esa hoja de calculo, espero me puedan ayudar gracias! ;-)
    martes, 11 de abril de 2017 2:37

Respuestas

  • Hola:
    En un Form como el de la imagen, copia y pega el siguiente código

    Option Explicit On
    Option Strict Off
    Imports System.Data.OleDb
    Public Class Form1
        '¡¡¡ OJO !!! TIENES QUE CREAR UN FICHERO DE EXCEL (Libro) llamado Libro1.xlsx
        Private msCadConexion As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Libro1.xlsx;Extended Properties = 'Excel 12.0;Xml'"
        Private Sub btnCrear_Click(sender As Object, e As EventArgs) Handles btnCrear.Click
            Try
                ' Se crea el libro llamado EmployeeData. La tabla tiene 3 campos: ID (char 255), Name (char 255) and Birthdate (date). 
                Dim lsQuery As String = "CREATE TABLE EmployeeData (Id char(255), Name char(255), BirthDate date)"
                Using loConexion As New OleDbConnection(msCadConexion)
                    loConexion.Open()
                    Using loComando As New OleDbCommand(lsQuery, loConexion)
                        loComando.ExecuteNonQuery()
                    End Using
                End Using
                MessageBox.Show("Hoja Creada")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
        Private Sub btnInsertar_Click(sender As Object, e As EventArgs) Handles btnInsertar.Click
            Try
                'Se añade 1 registro
                Dim lsQuery As String = "INSERT INTO EmployeeData (Id, Name, BirthDate) values ('AAA', 'Andrew', '12/4/1955')"
                Using loConexion As New OleDbConnection(msCadConexion)
                    loConexion.Open()
                    Using loComando As New OleDbCommand(lsQuery, loConexion)
                        loComando.ExecuteNonQuery()
                    End Using
                End Using
                MessageBox.Show("Registro Insertado")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
        Private Sub btnModificar_Click(sender As Object, e As EventArgs) Handles btnModificar.Click
            Try
                Using loConexion As New OleDbConnection(msCadConexion)
                    loConexion.Open()
                    Dim lsQuery As String = "UPDATE [EmployeeData$] SET NAME = 'Aaron', BirthDate = '5/4/1975' WHERE ID = 'AAA'"
                    Using loComando As New OleDbCommand(lsQuery, loConexion)
                        loComando.ExecuteNonQuery()
                    End Using
                End Using
                MessageBox.Show("Registro Modificado")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
        Private Sub btnBorrar_Click(sender As Object, e As EventArgs) Handles btnBorrar.Click
            MessageBox.Show("No he encontrado opcion de borrado")
        End Sub

        Private Sub btnConsultar_Click(sender As Object, e As EventArgs) Handles btnConsultar.Click
            Try
                Dim lsQuery As String = "Select * From [EmployeeData$]"
                Using loConexion As New OleDbConnection(msCadConexion)
                    Using loDataAdapter As New OleDbDataAdapter(lsQuery, loConexion)
                        Dim loDataTable As DataTable = New DataTable()
                        loDataAdapter.Fill(loDataTable)
                        Me.DataGridView1.DataSource = loDataTable
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
    End Class

    Un saludo desde Bilbo
    Carlos
    martes, 11 de abril de 2017 6:34

Todas las respuestas

  • Hola Vladimir Angarita

    revisa estos links: 

    1. How to Create an Excel File Using Visual Basic.Net

    2. VB.Net - Excel Sheet

    Saludos.!


    Si ayudé a resolver tu consulta, no olvides marcar como respuesta y/o votar como útil.

    martes, 11 de abril de 2017 4:06
  • Hola:
    En un Form como el de la imagen, copia y pega el siguiente código

    Option Explicit On
    Option Strict Off
    Imports System.Data.OleDb
    Public Class Form1
        '¡¡¡ OJO !!! TIENES QUE CREAR UN FICHERO DE EXCEL (Libro) llamado Libro1.xlsx
        Private msCadConexion As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Libro1.xlsx;Extended Properties = 'Excel 12.0;Xml'"
        Private Sub btnCrear_Click(sender As Object, e As EventArgs) Handles btnCrear.Click
            Try
                ' Se crea el libro llamado EmployeeData. La tabla tiene 3 campos: ID (char 255), Name (char 255) and Birthdate (date). 
                Dim lsQuery As String = "CREATE TABLE EmployeeData (Id char(255), Name char(255), BirthDate date)"
                Using loConexion As New OleDbConnection(msCadConexion)
                    loConexion.Open()
                    Using loComando As New OleDbCommand(lsQuery, loConexion)
                        loComando.ExecuteNonQuery()
                    End Using
                End Using
                MessageBox.Show("Hoja Creada")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
        Private Sub btnInsertar_Click(sender As Object, e As EventArgs) Handles btnInsertar.Click
            Try
                'Se añade 1 registro
                Dim lsQuery As String = "INSERT INTO EmployeeData (Id, Name, BirthDate) values ('AAA', 'Andrew', '12/4/1955')"
                Using loConexion As New OleDbConnection(msCadConexion)
                    loConexion.Open()
                    Using loComando As New OleDbCommand(lsQuery, loConexion)
                        loComando.ExecuteNonQuery()
                    End Using
                End Using
                MessageBox.Show("Registro Insertado")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
        Private Sub btnModificar_Click(sender As Object, e As EventArgs) Handles btnModificar.Click
            Try
                Using loConexion As New OleDbConnection(msCadConexion)
                    loConexion.Open()
                    Dim lsQuery As String = "UPDATE [EmployeeData$] SET NAME = 'Aaron', BirthDate = '5/4/1975' WHERE ID = 'AAA'"
                    Using loComando As New OleDbCommand(lsQuery, loConexion)
                        loComando.ExecuteNonQuery()
                    End Using
                End Using
                MessageBox.Show("Registro Modificado")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
        Private Sub btnBorrar_Click(sender As Object, e As EventArgs) Handles btnBorrar.Click
            MessageBox.Show("No he encontrado opcion de borrado")
        End Sub

        Private Sub btnConsultar_Click(sender As Object, e As EventArgs) Handles btnConsultar.Click
            Try
                Dim lsQuery As String = "Select * From [EmployeeData$]"
                Using loConexion As New OleDbConnection(msCadConexion)
                    Using loDataAdapter As New OleDbDataAdapter(lsQuery, loConexion)
                        Dim loDataTable As DataTable = New DataTable()
                        loDataAdapter.Fill(loDataTable)
                        Me.DataGridView1.DataSource = loDataTable
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
    End Class

    Un saludo desde Bilbo
    Carlos
    martes, 11 de abril de 2017 6:34
  • Gracias mi pana! los revisare!!
    martes, 11 de abril de 2017 15:27
  • Gracias por este completo ejemplo! J. Carlos saludos desde Maracay
    martes, 11 de abril de 2017 15:28