Leer una hoja de Excel desde Visual Basic y VB.Net

Vamos a ver lo que hay que hacer para leer un archivo Excel desde código de Visual Basic.Net y Visual Basic 6.0

Desde vb.net

Antes de tocar código vamos al administrador de paquetes de Nuget, para ellos desde el Explorador de soluciones pulsamos con en botón derecho del ratón y elegimos Administrar paquetes Nuget.

Hecho esto, elegimos ExcelDataReader y lo agregamos a nuestro proyecto.

Una vez tenemos agregada esta referencia a nuestro programa podemos leer una hoja de Excel desde nuestro código de Visual Basic .Net del siguiente modo:

Dim ExcelApp As New Excel.Application

Dim worksheet As Excel.Worksheet

Dim workbook As Excel.Workbook

Dim oRange As Excel.Range

Dim oRange_Leer As Excel.Range

Private Sub Lee_Excel()

workbook = ExcelApp.Workbooks.Open("C:\Ruta\Archivo_Excel.xlsx")         worksheet = workbook.Worksheets("Hoja1

oRange = worksheet.Range("A1")  

Dim str1 As String

       str1 = oRange.Value

       Text1.Text = str1

       ExcelApp.DisplayAlerts = False                

ExcelApp.Quit()

 End Sub

Para leer un rango de la Hoja de Excel

Private Sub Lee_Excel()

        Dim i As Integer

        workbook = ExcelApp.Workbooks.Open(strOrigenOriginator) ' Abre el archivo de Excel

        worksheet = workbook.Worksheets("Hoja1")

        oRange = worksheet.Range(strRangoOriginator)

        Dim str1 As String

        For i = 1 To Maximo_Valor_Rango

            str1 = DirectCast(oRange.Value, Object(,))(i, 1)

            If (str1) = cboComboBox.Text Then

                oRange_Leer = worksheet.Range(strRangoLeido & i) ' Lee el rango de celdas y lo almacena en un Combo               cbo_ComboBox.Items.Add(oRange_Leer.Value)

            End If

        Next i

        ExcelApp.DisplayAlerts = False ' Deshabilita las Notificaciones del tipo ¿Desea Guardar el archivo?”

        ExcelApp.Quit() 

    End Sub

Desde Visual Basic 6.0

En este caso se abrirá un libro Excel para leer su contenido y luego se cerrará.

Lo primero que hay que hacer es añadir a las referencias del proyecto la dll de Excel. Para ello sobre nuestro proyecto de Visual Basic  hacemos.  Proyecto -> referencias.

  

Y elegimos Microsoft Excel 15.0 Object Library que es el correspondiente a Excel 2013, para otras versiones puede cambiar el número.

Hecho esto vamos a nuestro proyecto y creamos este procedimiento. En la definición de objetos de Excel hay quien pone esto:

Dim xlApp As Excel.Application

Dim xlLibro As Excel.Workbook

Dim xlHoja As Excel.Worksheet

Y luego abre Excel así:

Set xlApp = New Excel.Application

Esto puede dejar procesos de Excel abiertos después de ejecutar nuestra aplicación (en el administrador de tareas)  por lo que es más correcto definirlo así:

Dim xlApp As Object

Dim xlLibro As Object

Dim xlHoja As Object

Y luego abrir Excel del siguiente modo:

Set xlApp = CreateObject("Excel.Application")

Set xlLibro = xlApp.Workbooks.Add

Set xlHoja = xlLibro.Worksheets(1)

A la hora de abrir el Excel es conveniente haber insertado previamente un cuadro de diálogo de controles comunes en nuestro formulario, esto se hace en  proyecto –> componentes-> Microsoft Common Dialog Control 6.0

De este modo podemos insertar en nuestro formulario un botón que al pulsarlo nos abra un cuadro de diálogo estándard que nos permita elegir el archivo Excel para abrir.

Las líneas de código para abrir un archivo desde el explorador de Windows serían de este estilo:

CommonDialog1.InitDir = "C:\"

CommonDialog1.ShowOpen

Una vez elegido el archivo Excel la forma de tratarlo sería la siguiente:

If CommonDialog1.FileName <> "" Then

    Set xlLibro = xlApp.Workbooks.Open(CommonDialog1.FileName, True, True, , "")

    Set xlHoja = xlApp.Worksheets("Hoja1")

    '1. Si se conoce el rango a leer. OJO es muy importante poner xlHoja delante pues si no se pone funciona 

     'igual pero deja abierto excel incluso aunque se haga xlApp.Quit

    'varMatriz = xlHoja.Range("A1:B65536").value  'lee las columnas A y B

     '2. Si no se conoce el rango. OJO es muy importante poner xlHoja delante 

    lngUltimaFila = xlHoja.Columns("A:A").Range("A65536").End(xlUp).Row  

   

    varMatriz = xlHoja.Range(xlHoja.Cells(1, 1), xlHoja.Cells(lngUltimaFila, NumColumnas))   

Y tratamos los datos leídos

If CommonDialog1.FileName <> "" Then

    

    Set xlLibro = xlApp.Workbooks.Open(CommonDialog1.FileName, True, True, , "")

    Set xlHoja = xlApp.Worksheets("Hoja1")

    '1. Si se conoce el rango a leer. OJO es muy importante poner xlHoja delante pues si no se pone funciona 

     'igual pero deja abierto excel incluso aunque se haga xlApp.Quit

    'varMatriz = xlHoja.Range("A1:B65536").value  'lee las columnas A y B

     '2. Si no conoces el rango. OJO es muy importante poner xlHoja delante 

    lngUltimaFila = xlHoja.Columns("A:A").Range("A65536").End(xlUp).Row  

    

    'Aqui carga en un array multidimensional el rango leído

    varMatriz = xlHoja.Range(xlHoja.Cells(1, 1), xlHoja.Cells(lngUltimaFila, NumColumnas))   

   

Aquí cargamos los datos desde el array a nuestro formulario (puede ser un spread, controles de texto, etc)

    For i = 1 To lngUltimaFila

    'cargamos los datos

         spreadRCF.Text = varMatriz(i, 1)

    Next i

    'cerramos el archivo Excel

    xlLibro.Close SaveChanges:=False

    xlApp.Quit

end if

Para cerrar el archivo Excel hemos utilizado:

xlLibro.Close SaveChanges:=False

xlApp.Quit

Pero para liberarlo totalmente es necesario poner los objetos a nothing.

Set xlHoja = Nothing

Set xlLibro = Nothing

Set xlApp = Nothing

Finalmente si intentamos ejecutar este programa en un equipo sin Excel instalado podemos controlar el error del siguiente modo:

ERROR_EXCEL:

If Err.Number = 429 Then

    MsgBox "Para tener acceso a esta funcionalidad debe instalar Microsoft Excel en el equipo cliente", vbInformation

Else

    MsgBox "Se ha producido un error al intentar abrir el archivo excel", vbExclamation

End If

Aquí un ejemplo de código completo que carga los datos de un Excel de 2 columnas y n filas en una grid (Spread) del formulario.

Este código además de Excel llama a una subrutina que permite leer en formato CSV. En este caso el tratamiento es como un fichero plano de texto.

'LEE UN ARCHIVO EXCEL O CSV Y LO IMPORTA EN UN FORMULARIO (SPREAD) DE VISUAL BASIC.

Private Sub SSCmdImportarExcel_Click()

Dim xlApp As Object

Dim xlLibro As Object

Dim xlHoja As Object

Dim varMatriz As Variant

Dim lngUltimaFila As Long

Dim i, j, k, l As Integer

On Error GoTo ERROR_EXCEL

'abrir programa Excel

Set xlApp = CreateObject("Excel.Application")

Set xlLibro = xlApp.Workbooks.Add

Set xlHoja = xlLibro.Worksheets(1)

'abrir el archivo Excel desde windows

CommonDialog1.InitDir = "C:\"

CommonDialog1.ShowOpen

If CommonDialog1.FileName <> "" Then

    If (InStr(1, UCase(CommonDialog1.FileName), "CSV")) Then ' si la extesión es CSV utiliza otro procedimiento

        CargarCSV (CommonDialog1.FileName)

        Exit Sub

    End If

    Set xlLibro = xlApp.Workbooks.Open(CommonDialog1.FileName, True, True, , "")

    Set xlHoja = xlApp.Worksheets("Hoja1")

    '1. Si se conoce el rango a leer

    'varMatriz = xlHoja.Range("A1:B65536").value  'lee las columnas A y B

     '2. Si no se conoce el rango

lngUltimaFila = xlHoja.Columns("A:A").Range("A65536").End(xlUp).Row   'OJO es muy importante poner xlHoja delante pues si no se pone funciona igual pero deja abierto excel incluso aunque se haga xlApp.Quit

    

    'Detecta hasta donde está relleno el spread

    j = 1

    spreadRCF.Row = j

    While spreadRCF.Text <> ""

        j = j + 1

        spreadRCF.Row = j

    Wend

varMatriz = xlHoja.Range(xlHoja.Cells(1, 1), xlHoja.Cells(lngUltimaFila, 2)) 'toma las columnas 1 y 2 hasta el final

    

    i = 1

    For i = 1 To lngUltimaFila

    'cargamos el spread

        spreadRCF.Col = 1

        spreadRCF.Row = j

        'En este caso concreto fusiona el contenido de las columnas 1 y 2

        spreadRCF.Text = varMatriz(i, 1) & varMatriz(i, 2)

               j = j + 1  'contador para la fila del spread

    Next i

    'cerramos el archivo Excel

    xlLibro.Close SaveChanges:=False

    xlApp.Quit

End If 'Fin de CommonDialog1.FileName

'reset variables de los objetos

Set xlHoja = Nothing

Set xlLibro = Nothing

Set xlApp = Nothing

Exit Sub

ERROR_EXCEL:

If Err.Number = 429 Then

    MsgBox "Para tener acceso a esta funcionalidad debe instalar Microsoft Excel en el equipo cliente", vbInformation

Else

    MsgBox "Se ha producido un error al intentar abrir el archivo excel", vbExclamation

End If

End Sub

'PARA LEER UN CSV  

Private Sub CargarCSV(FicheroCSV As String, Optional Separador As String = ";")

  ' Carga en un spread un fichero en formato CSV

  Dim Fichero As Integer, Registro As String, Campos() As String, Fila As Single, Columna As Single

  Dim i, j As Integer

  

   'Detecta hasta donde está relleno el spread

    j = 1

    spreadRCF.Row = j

    While spreadRCF.Text <> ""

        j = j + 1

        spreadRCF.Row = j

    Wend

  ' Abrimos el fichero de Texto

  Fichero = FreeFile

  Open FicheroCSV For Input As #Fichero

  ' Lo procesamos hasta el final

   i = 1

  While Not EOF(Fichero)

    spreadRCF.Col = 1

    spreadRCF.Row = j

    ' Leemos un Registro y lo separamos en Campos individuales

    Line Input #Fichero, Registro

    Campos = Split(Registro, Separador)

    ' Si es la primera Lectura (Fila=0) dimensionamos adecuadamente el Grid

    Registro = Replace(Registro, ";", "")

    spreadRCF.Text = Registro

    Fila = Fila + 1

  Wend

  Close #Fichero

End Sub