Automatización con Excel y Visual Basic .NET

Este es un ejemplo de automatización –  controlar un programa desde otro – usando Visual Basic .NET y Excel. Desde Visual Basic .NET vamos a crear y llenar una hoja de Excel.

Proyecto

Crearemos un proyecto Visual Basic de tipo Windows Application, y lo llamaremos: WinAppExcelAutomation.

Formulario

Aparece un formulario, Form1, que renombré a frmExcelAutomation. También le cambié el nombre del archivo, de Form1.vb a frmExcelAutomation.vb. Le puse la propiedad Text: Excel Automation.

Le adicioné un DataGrid, que nombré dgDatos, y un botón, con nombre: btnAlmacenarExcel, y texto: Almacenar Excel. (También recomendaría especificar las propiedades Anchor apropiadas, para que al cambiar el tamaño del formulario, los controles se ajusten).

Acceso a Datos

DataAdapter

Para llenar la hoja de Excel, vamos a leer datos de la tabla Customers en la base de datos Northwind que viene de demostración en SQL Server. Usaremos un DataAdapter para accesar la base de datos y llenar un DataSet, que es un área en memoria en donde almacenaremos temporalmente los datos.

En el Toolbox, bajo el encabezado de Data, arrastraremos un control SqlDataAdapter y lo dejamos caer sobre el formulario. Aparece un Wizard de Data Adapter Configuration:

Pasamos a la siguiente pantalla con Next >; y escogemos una conexión a Northwind.
En caso de que no exista una conexión a Northwind, la creamos oprimiendo el botón New Connection.

 
Luego, escogeremos accesar los datos con enunciados SQL (versus con Procedimientos Almacenados):

Y establecemos la sentencia SQL Server
(puede utilizar el botón Query Builder para crear la sentencia SQL en forma gráfica)

Dar Next >; y luego Finish para terminar.

Renombré el Data Adapter SqlDataAdapter1 a daCustomers, y la conexión SqlConnection1 a cnnNorthwind.
DataSet

El DataSet es un área en memoria en donde almacenamos los datos con que vamos a trabajar.

Sobre el control daCustomers le damos botón derecho y pedimos laopción Generate Dataset…
Le decimos que queremos un nuevo Dataset oprimiendo sobre: New y le damos el nombre: dsCustomers
Revisamos que la caja Add this dataset to the designer esté marcada:

y una vez creado, lo renombré a: dsCustomers:

Código Acceso a Datos

Escribiremos código para que al momento de cargar el formulario también cargue los datos.
Damos doble click sobre alguna parte del formulario, lo que nos lleva a ver el código de la página.
Escribimos lo siguiente, para que el DataAdapter daCustomers llene el DataSet dsCustomers con los datos de los clientes:

Public Class frmExcelAutomation
    Inherits System.Windows.Forms.Form

(Windows Form Designer generated code)

    Private Sub frmExcelAutomation_Load( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) _
        Handles MyBase.Load

        Me.daCustomers.Fill(Me.DsCustomers.Customers)

    End Sub

End Class

 
Binding del DataGrid al DataSet

El DataGrid mostrará los datos que hemos almacenado en el DataSet.
Establecemos las propiedades del DataGrid:

 DataSource dsCustomers
 DataMember Customers

Probarlo…

Vamos a revisar que el sistema cargue los datos en el Grid

Dar Debug – Start u oprimir el botón de Start (una flecha azul hacia la derecha).
Si tiene errores, corregirlo.

En mi caso, dio el siguiente error debido a que renombré el formulario de Form1 a frmExcelAutomation: ‘Sub Main’ was not found in ‘WinAppExcelAutomation.Form1’. Para corregir este error, dé doble click sobre el mensaje de error y cuando aparezca una ventana Startup Object escoja el formulario de inicio correcto – en nuestro caso: frmExcelAutomation.

El formulario debe aparecer con los datos de la tabla Customers (Clientes):

Referencia a Excel

Para poder accesar el modelo de objetos de Excel, tenemos que adicionar una a Excel.

En la ventana Solution Explorer, posicionémonos sobre References y con click derecho pedimos la opción Add Reference… Aparece la caja de diálogo: Add Reference

Resulta que Excel todavía es tecnología COM (la tecnología previa a .NET), por lo que nos vamos al tabulado COM. Buscamos Microsoft Excel 11.0 Object Library (yo tengo instalado Office 2003, cuyo Excel es la versión 11). Oprimimos Select para escogerlo y luego OK para aceptarlo.

 
Código que Instancia Excel y lo manipula

    Private Sub btnAlmacenarExcel_Click( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) _
        Handles btnAlmacenarExcel.Click

        Dim oExcel As Excel.ApplicationClass
        Dim oBooks As Excel.Workbooks
        Dim oBook As Excel.WorkbookClass
        Dim oSheet As Excel.Worksheet

        ' Inicia Excel y abre el workbook
        oExcel = CreateObject("Excel.Application")
        oExcel.Visible = True
        oBooks = oExcel.Workbooks
        oBook = oExcel.Workbooks.Add
        oSheet = oBook.Sheets(1)

        'oBook = oBooks.Open( _
        '    "C:\DevCare\DevCareExcelAutomation\Data.xls")

        Const ROW_FIRST = 3
        Dim iRow As Int64 = 1

        ' Encabezado
        oSheet.Cells(ROW_FIRST, 1) = "ID"
        oSheet.Cells(ROW_FIRST, 2) = "Compañía"
        oSheet.Cells(ROW_FIRST, 3) = "Contacto"
        oSheet.Cells(ROW_FIRST, 4) = "País"

        oSheet.Cells(ROW_FIRST, 1).font.bold = True
        oSheet.Cells(ROW_FIRST, 2).font.bold = True
        oSheet.Cells(ROW_FIRST, 3).font.bold = True
        oSheet.Cells(ROW_FIRST, 4).font.bold = True

        oSheet.Columns(1).ColumnWidth = 10
        oSheet.Columns(2).ColumnWidth = 40
        oSheet.Columns(3).ColumnWidth = 30
        oSheet.Columns(4).ColumnWidth = 15

        ' Loop que almacena los datos
        Dim rowCustomer As dsCustomers.CustomersRow
        For Each rowCustomer In Me.DsCustomers.Customers
            Dim iCurrRow As Int64 = ROW_FIRST + iRow
            oSheet.Cells(iCurrRow, 1) = rowCustomer.CustomerID
            oSheet.Cells(iCurrRow, 2) = rowCustomer.CompanyName
            oSheet.Cells(iCurrRow, 3) = rowCustomer.ContactName
            oSheet.Cells(iCurrRow, 4) = rowCustomer.Country

            iRow += 1
        Next

        ' Fórmula
        oSheet.Cells(ROW_FIRST + iRow + 1, 1) = _
            "=counta(R" & (ROW_FIRST + 1) & "C1:R" & _
            (ROW_FIRST + iRow - 1).ToString & "C1)"

        '' Cierra todo
        'oBook.Close(True)
        'System.Runtime.InteropServices.Marshal. _
        '    ReleaseComObject(oBook)
        'oBook = Nothing

        'System.Runtime.InteropServices.Marshal. _
        '    ReleaseComObject(oBooks)
        'oBooks = Nothing

        'oExcel.Quit()
        'System.Runtime.InteropServices.Marshal. _
        '    ReleaseComObject(oExcel)
        'oExcel = Nothing

    End Sub