Visual Basic 2010 importar archivo excel y modificar, insertar, guardar

[Calonso]

Hola que tal amigos requiero de si ayuda ..... soy nuevo en el foro .... y estoy por empezar un proyecto de desarrollo de un sistema de Planeacion de produccion para una empresa y lo estoy empezando con Visual 2010 mi pregunta es la siguiente>

mi base de datos esta hecha en access y ya la conecte a visual .. empece con esto y agregue un editor de menu en el cual la primera opcion es ...

Menu ---- Importar formato 28B

donde al hacer click en la opcion de importar formato debe de importar un archivo de excel y mostrarlo en pantalla para su posterior modificacion ( editar copiar pegar guardar .. etc)

y en ese click yo meti este codigo pero me avienta error al momento de importar la hoja de excel ..... no omito mencionarte que esa hoja de excel contiene al menos 4000 registros

Código: [Seleccionar]

mports System.Data.OleDb
Public Class Form1

    Private Sub MenuPrincipalToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuPrincipalToolStripMenuItem.Click

    End Sub

    Private Sub CapturarFormato28BToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CapturarFormato28BToolStripMenuItem.Click
        Dim stRuta As String = ""
        Dim openFD As New OpenFileDialog()
        With openFD

            .Title = "Seleccionar archivos"
            .Filter = "Archivos Excel(*.xls;*.xlsx)|*.xls;*xlsx|Todos los archivos(*.*)|*.*"
            .Multiselect = False
            .InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop

            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                stRuta = .FileName

            End If
        End With

        Try
            Dim stConexion As String = ("Provider=Microsoft.ACE.OLEDB.12.0­;" & ("Data Source=" & (stRuta & ";Extended Properties=""Excel 12.0;Xml;HDR=YES;IMEX=2"";&quo­t;")))
            Dim cnConex As New OleDbConnection(stConexion)
            Dim Cmd As New OleDbCommand("Select * From [Hoja1$]")
            Dim Ds As New DataSet
            Dim Da As New OleDbDataAdapter
            Dim Dt As New DataTable
            cnConex.Open()
            Cmd.Connection = cnConex
            Da.SelectCommand = Cmd
            Da.Fill(Ds)
            Dt = Ds.Tables(0)
            Me.DataGridView1.Columns.Clear()
            Me.DataGridView1.DataSource = Dt
        Catch ex As Exception

        End Try
        MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")

    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub

-----------------------------------------------------------------
    Private Function ex() As Object
        Throw New NotImplementedException
        MsgBox("No need to implement this for our exercise")
        End
-----------------------------------------------------------------
    End Function
End Class
La parte que esta encerrada en area punteada es donde me marca el error ....

Espero que me puedan ayudar a seguir con el proyectito y que no sea la unica ocasion que me pudieran apoyar.

Gracias ..... saludos

[César Krall]

Hola Calonso, si tienes 4000 registros el problema puede deberse a que es un número demasiado grande como para importarlo y mostrarlo por pantalla todo de golpe, te recomendaría que limites la muestra de datos a un valor razonable (pongamos 10x10 datos, unos 100 ó 200 datos por pantalla).

En este link se comenta el trabajo con Excel y Visual Basic y quizás te sirva de ayuda: https://www.aprenderaprogramar.com/foros/index.php?topic=1322.0

También sería útil que subas un archivo excel de ejemplo (no el que tiene todos los datos, sólo una copia con unos cuantos datos), así podría probarlo en mi pc para ver si me sale el mismo error que a tí y ver cómo solucionarlo.


Saludos

[Calonso]

Listo .... gracias por indicarme como era para adjuntar el archivo Cesar ..... al momento de cargar el archivo de excel lo puedo mandar a otro FORM por que todavia hay que modificar datos y hacer explosionado y concatenado y pivotear los datos ..... Si eres tan amable de verificar el excel ... mandar llamar la hoja y verificar el error que me sale  Cesar por fa y otra Fijate que ya me fije bien cuentas celdas son las que tengo y son aproximadamente 11000 ..... y de esos datos tengo que eliminar algunas columnas y dejar otras ..... para despues hacer otro proceso .... de hecho me gustaria preguntarte si al momento de cargar la hoja de excel se puede hacer en automatico el eliminar unas columnas ?

Espero haberme dado a entender

[César Krall]

Hola, para poder revisarlo haría falta que adjuntes el archivo excel (no hace falta el de miles de registros, con que tenga cien o doscientos basta) y si hay código adicional, el código adicional que haga falta. Las instrucciones para adjuntar archivos o links están en https://www.aprenderaprogramar.com/foros/index.php?topic=1460.0

Saludos!

[César Krall]

Hola, una cosa que debes tener presente y que no estoy seguro de si has hecho es que para hacer posible operar con ficheros de Excel con la interface que proporciona Visual Basic tienes que habilitar esa opción dentro de Visual Basic, para ello hay que ir al menú Proyecto -- > Agregar referencia -- > Elegir la ficha COM (componente) -- > Seleccionar Microsoft Excel 12.0 Object Library (o un nombre similar) y pulsar aceptar.


He puesto algunos mensajes de aviso en el programa

Código: [Seleccionar]

Imports System.Data.OleDb
Public Class Form1

    Private Sub CapturarFormato28BToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CapturarFormato28BToolStripMenuItem.Click
        Dim stRuta As String = ""
        Dim openFD As New OpenFileDialog()
        With openFD

            .Title = "Seleccionar archivos"
            .Filter = "Archivos Excel(*.xls;*.xlsx)|*.xls;*xlsx|Todos los archivos(*.*)|*.*"
            .Multiselect = False
            .InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop

            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                stRuta = .FileName
            End If
        End With
        MsgBox("Aqui llega 1")
        Try
            MsgBox("Aqui llega 2")
            Dim stConexion As String = ("Provider=Microsoft.ACE.OLEDB.12.0­;" & ("Data Source=" & (stRuta & ";Extended Properties=""Excel 12.0;Xml;HDR=YES;IMEX=2"";"")))

            MsgBox("Aqui llega 3")
            Dim cnConex As New OleDbConnection(stConexion)
            MsgBox("Aqui llega 4")
            Dim Cmd As New OleDbCommand("Select * From [Hoja1$]")
            Dim Ds As New DataSet
            Dim Da As New OleDbDataAdapter
            Dim Dt As New DataTable

            cnConex.Open()
            Cmd.Connection = cnConex
            Da.SelectCommand = Cmd
            Da.Fill(Ds)
            Dt = Ds.Tables(0)
            Me.DataGridView1.Columns.Clear()
            Me.DataGridView1.DataSource = Dt
        Catch ex As Exception
            MsgBox("Se ha producido un error" & ex.Message)
        End Try
        MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")

    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub


    Private Function ex() As Object
        ' Throw New NotImplementedException
        MsgBox("No need to implement this for our exercise")
        End
    End Function


End Class


Se está obteniendo nada más ejecutar el programa:

Excepción del tipo 'System.ArgumentException' en System.Data.dll
Excepción del tipo 'System.NotImplementedException' en WindowsApplication1.exe


Esto parece indicar que no están bien implementadas la recuperación de excepciones.

Si ejecuto este código de prueba obtengo lo siguiente:

- Se muestra hasta Aqui llega 3 pero no Aqui llega 4, el error parece estar en esta línea: Dim cnConex As New OleDbConnection(stConexion)

- Mensaje por pantalla "Se ha producido un error. El formato de la cadena de inicialización no se ajusta a la especificación que comienza en el índice 186"


La línea del error nos remite a su vez a la línea Dim stConexion As String = ("Provider=Microsoft.ACE.OLEDB.12.0­;" & ("Data Source=" & (stRuta & ";Extended Properties=""Excel 12.0;Xml;HDR=YES;IMEX=2"";&quo­t;")))

Todavía no veo solución, habría que seguir investigando...

Saludos

[Calonso]

Muchas gracias por tu Apoyo ... por mi parte aun sigo buscando solucion alguna para este error.
Sin embargo quisiera preguntarte si  cuando cargue el archivo hay alguna forma de cargarlo pero no con todas las columnas

Eres muy amable

[César Krall]

Hola este es un ejemplo de código que te permite seleccionar sólo las columnas A y B, a lo mejor te sirve como idea para cargar las columnas que te interesen.

Antes de operar con ficheros de Excel vete al menú Proyecto -- > Agregar referencia -- > Elige la ficha COM (componente) -- > Selecciona Microsoft Excel 12.0 Object Library (o un nombre similar) y pulsa aceptar.

Crea el fichero ejemplo.xlsx e introduce algunos datos en las columnas A, B y C. Luego en un formulario añade un botón y un label y ejecuta el código.

Código: [Seleccionar]

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Label1.Text = ""
        Label1.AutoSize = False
    End Sub
   
    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim objXLApp As Excel.Application
        Dim intLoopCounter As Integer
        Dim objXLWb As Excel.Workbook
        Dim objXLWs As Excel.Worksheet
        Dim objRange As Excel.Range

        objXLApp = New Excel.Application
        objXLApp.Workbooks.Open("C:\Users\Juan\CursoVB\aprender\ejemplo.xlsx")
        objXLWb = objXLApp.Workbooks(1)
        objXLWs = objXLWb.Worksheets(1)
        MsgBox("Ahora la celda A5 contiene: " & objXLWs.Cells(5, 1).value)
        For intLoopCounter = 1 To CInt(objXLWs.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
            objRange = objXLWs.Range("A" & intLoopCounter)
            Label1.Text = Label1.Text & vbCrLf & objRange.Value
            objRange = objXLWs.Range("B" & intLoopCounter)
            Label1.Text = Label1.Text & " -- > " & objRange.Value
        Next intLoopCounter

        'Escribimos en el fichero
        objXLWs.Cells(5, 1) = "Probando"
        MsgBox("Ahora la celda A5 contiene: " & objXLWs.Cells(5, 1).value)
        objXLApp.Quit()
    End Sub

End Class
Saludos

[Calonso]

Ok Cesar ......... hoy incorporo este codigo a ver como sale igual y con este es mas facil que con el que tenia ya que si me a causado muchas broncas para importar el archivo de excel.

Gracias te agradezco mucho tu ayuda.

en momento te dire como anda

[Calonso]

Hola Cesar como estas .... oye con la novedad de que el código que me mandaste funciona al cien por ciento .... lo único es q no es lo que ando buscando (no pretendo meter datos desde visual hacia una hoja de excel), si no todo lo contrario quiero importar una hoja de excel a visual 2010, para poder manipular los datos, eliminar columnas, concatenar y pivotear hojas, aparte de explosionar los datos.

Pero lo primero seria importar la hoja de excel a visual con ciertas columnas ... no todas....agradezco bastante tu apoyo Cesar y espero sigas apoyándome con este proyecto.

Gracias y salu2

[Calonso]

Estoy verificando el código, solo que ahora me marca diferente error ya que lo modifique cualquier cosa si alguien me pudiera apoyar para verificar los errores que me marca y a q se deben al momento de importar el archivo de excel a VB2010 .... este es el código

Código: [Seleccionar]

Imports System.Data.OleDb
Public Class Form1


    Dim unloadme As Object

    Private Sub MenuPrincipalToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuPrincipalToolStripMenuItem.Click

    End Sub

    Private Sub CapturarFormato28BToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CapturarFormato28BToolStripMenuItem.Click
        Dim stRuta As String = ""
        Dim openFD As New OpenFileDialog()
        With openFD

            .Title = "Seleccionar archivos"
            .Filter = "Archivos Excel(*.xls;*.xlsx)|*.xls;*xlsx|Todos los archivos(*.*)|*.*"
            .Multiselect = False
            .InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop

            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                stRuta = .FileName

            End If
        End With

        Try
            Dim stConexion As String = ("Provider=Microsoft.ACE.OLEDB.12.0­;" & ("Data Source=" & (stRuta & ";Extended Properties=""Excel 12.0;Xml;HDR=YES;IMEX=2"";&quo­t;")))
            Dim stConexion As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" & ("Data Source=" & (stRuta & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")))
            Dim cnConex As New OleDbConnection(stConexion)
            Dim Cmd As New OleDbCommand("C:\28b - Ejemplo.xlsx")
            Dim Ds As New DataSet
            Dim Da As New OleDbDataAdapter
            Dim Dt As New DataTable
            cnConex.Open()
            Cmd.Connection = cnConex
            Da.SelectCommand = Cmd
            Da.Fill(Ds)
            Dt = Ds.Tables(0)
            Me.DataGridView1.Columns.Clear()
            Me.DataGridView1.DataSource = Dt
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        End Try


    End Sub
End Class
Gracias a toda la rasita que ve este msj de ayuda

[javi in the sky]

Hola Alonso tienes esta declaración repetida con lo cual la primera linea es como si no existiera

Código: [Seleccionar]

            Dim stConexion As String = ("Provider=Microsoft.ACE.OLEDB.12.0­;" & ("Data Source=" & (stRuta & ";Extended Properties=""Excel 12.0;Xml;HDR=YES;IMEX=2"";&quo­t;")))
            Dim stConexion As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" & ("Data Source=" & (stRuta & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")))

Intenta probar este código

Código: [Seleccionar]

Imports System.Data.SqlClient
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\28b - Ejemplo.xlsx';Extended Properties=Excel 8.0;")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
        MyCommand.TableMappings.Add("Table", "aprenderaprogramar.com")
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        DataGridView1.DataSource = DtSet.Tables(0)
        MyConnection.Close()

    End Sub
End Class
saludos!

[Calonso]

Hola Javi in the Sky ........ primero gracias por apoyarme y dedicar un tiempo libre a este proyectito ... sin embargo lo estuve checando con el codigo que me enviaste y en esta parte

        DtSet = New System.Data.DataSet
        ----MyCommand.Fill(DtSet)----
        DataGridView1.DataSource = DtSet.Tables(0)
        MyConnection.Close()

me manda error y de hecho ni siquiera me da oportunidad de importar el excel.

Seguire hechandole ganas con esto y gracias por la ayuda ..... si puedes seguir apoyandome te lo agradecere mucho Javi.


Saludos

[Mastermind]

Hola! He hecho una prueba a partir del código propuesto por César

Parto de un archivo ejemplo.xlsx que contiene esto:

PARTNUMBER   REQUESTDATE   FORECASTQUANTITY
101-250001   15-AUG-15   200
101-250001   15-NOV-15   250
101-250001   15-OCT-15   250
101-250001   15-SEP-15   240
101-250001   19-APR-15   50
101-250001   21-JUN-15   110
1875204C91   01-JUN-15   500
1875204C91   02-NOV-15   500


Con este código logro que me muestre los datos en un datagridview, el código no es bueno pero al menos da algunas ideas:

Código: [Seleccionar]

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Dim objXLApp As Excel.Application
    Dim objXLWb As Excel.Workbook
    Dim objXLWs As Excel.Worksheet
    Dim objRange As Excel.Range
    Dim contenido() As String = {"", "", ""}

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Label1.Text = ""
        Label1.AutoSize = False
    End Sub

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

        Dim intLoopCounter As Integer
        objXLApp = New Excel.Application
        objXLApp.Workbooks.Open("C:\ejemplo.xlsx")
        objXLWb = objXLApp.Workbooks(1)
        objXLWs = objXLWb.Worksheets(1)

        DataGridView1.ColumnCount = 3
        DataGridView1.Columns(0).Name = "Uno"
        DataGridView1.Columns(1).Name = "Dos"
        DataGridView1.Columns(2).Name = "Tres"

        For intLoopCounter = 1 To CInt(objXLWs.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
            objRange = objXLWs.Range("A" & intLoopCounter)
            contenido(0) = objRange.Value
            objRange = objXLWs.Range("B" & intLoopCounter)
            contenido(1) = objRange.Value
            objRange = objXLWs.Range("C" & intLoopCounter)
            contenido(2) = objRange.Value
            DataGridView1.Rows.Add(contenido)
        Next intLoopCounter
        objXLApp.Quit()
    End Sub

End Class

Saludos

[Calonso]

Excelente master muchisimas gracias por apoyarme y partir de ese codigo hoy lo mismo lo veo ... ya les contare como fue...

Gracias a todos


Salu2

[Ogramar]

Hola! He estado probando con todo lo que han comentado aquí y con el código que he puesto en el hilo https://www.aprenderaprogramar.com/foros/index.php?topic=2575.0 he logrado importar todos los datos desde el archivo excel hasta el datagridview de visual basic, espero sirva de ayuda

Salu2!

[Calonso]

He seguido el hilo de la conversación y aquí seguimos comparto el código para la gente que este haciendo algo similar le ayude y lo tome. Agradezco mucho el apoyo de todos los que han aportado a este mensaje de ayuda.

Salu2 a todos

Código: [Seleccionar]

Imports System.Data.OleDb
Imports System.IO
Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub
    Private Sub CapturarFormato28BToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CapturarFormato28BToolStripMenuItem.Click
        Dim objConn As OleDbConnection
        Dim oleDA As OleDbDataAdapter
        Dim ds As DataSet
        Dim FileName As String
        FileName = "C:\Users\EDRodriguez\Desktop\28b - PS on past (36 weeks) (3320) 2015-04-10.xlsx"

        Try
            'Create a OLEDB connection for Excel file
            Dim connectionString As String = "Provider=Microsoft.ACE.Oledb.12.0;Data Source=" & FileName & ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"""
            objConn = New OleDbConnection(connectionString)
            oleDA = New OleDbDataAdapter("select * from [_28b___PS_on_past__36_weeks_$]", objConn)
            ds = New DataSet()
            'Fill the Data Set
            oleDA.Fill(ds)
            'Set DataSource of DataGridView
            DataGridView1.DataSource = ds.Tables(0)
            ds.Dispose()
            oleDA.Dispose()
            objConn.Dispose()
        Catch ex As Exception
        End Try
    End Sub
End Class

Seguiré poniendo el código para que los demás puedan compartir