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
Gracias asalazar@ec-red.com por el truco