Creación de Excel mediante ADO

Aunque la forma más fácil de crear un nuevo libro de trabajo de Excel es a través del propio programa Microsoft Excel, esto no es siempre posible, ya que el usuario final de la aplicación puede no disponer de dicho programa instalado en su sistema, por lo que no se podrá incluir en la aplicación ninguna referencia a la biblioteca Microsoft Excel x.x Object Library.

Para crear nuevos libros y hojas de cálculo de Excel, con independencia de que el usuario final disponga o no de dicho programa, es necesario hacer uso de MDAC (Microsoft Data Access Components) y del ISAM (Indexed Sequential Access Method) de Excel del motor Microsoft Jet. Por lo tanto, lo único que se necesita es que el cliente final tenga instalado una versión de MDAC y los componentes de Microsoft Jet, incluida la biblioteca del ISAM de Excel, que serán los archivos msexcl40.dll (para la versión del motor Jet 4.0), o msexcl35.dll (para la versión 3.51 de dicho motor). Desde la página Web de descargas de Microsoft se puede obtener tanto Microsoft Jet como MDAC.

Mediante SQL (Structured Query Language) del motor de base de datos Microsoft Jet, es posible crear nuevos libros de trabajo de Excel, utilizando la instrucción CREATE TABLE. Simplemente será necesario definir la cadena de texto con la sentencia SQL para crear la tabla, la cuál incluirá el nombre de la tabla (hoja de cálculo) y el nombre y tipo de los campos (columnas) que formarán la misma. Una vez definida, hay que ejecutar el método Execute del objeto Connection, siempre y cuando dicho objeto se encuentre abierto.

En el siguiente ejemplo en Visual Basic 6.0 se crea un nuevo libro de trabajo de Excel (Libro1.xls) y una nueva hoja de cálculo "Clientes" con las columnas "IdCliente", "Nombre Cliente", "Domicilio", "Fecha de Alta":

Dim oConnection As ADODB.Connection
Dim cSQL As String

Set oConnection = New ADODB.Connection
With oConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "C:\tmp\Libro1.xls"
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With

cSQL = "CREATE TABLE Clientes (IdCliente LONG, [Nombre Cliente] TEXT," & _
"Domicilio TEXT, [Fecha Alta] DATETIME)"

oConnection.Execute cSQL

oConnection.Close

Set oConnection = Nothing

Para poder instanciar el objeto Connection será necesario agregar la referencia a la biblioteca Microsoft ActiveX Data Objects x.x Library en el proyecto Visual Basic.

A continuación, se podrían insertar datos en la hoja de cálculo utilizando la instrucción INSERT de SQL, de manera similar a lo realizado con CREATE TABLE, es decir, cadena de texto con sentencia SQL y ejecución del método Execute:

cSQL = "INSERT INTO [Hoja1$] (IdSocio, Nombre, [Fecha Alta]) " & _
"VALUES (1234, 'Maestre González, David', #05/21/2006#)"

oConnection.Execute SQL

Otra forma de crear un nuevo libro de trabajo y una nueva hoja de cálculo (con datos) es mediante la instrucción SELECT de SQL, utilizando también la cláusula INTO. En el ejemplo siguiente se exporta una tabla de una base de datos Microsoft Access (Bd1.mdb) a un nuevo libro de trabajo (Libro1.xls) y a una nueva hoja de cálculo:

Dim cNombreFicheroExcel As String
Dim cNombreLibro As String
Dim cNombreTabla As String
Dim oConnection As ADODB.Connection
Dim cSQL As String

cNombreFicheroExcel = "C:\tmp\Libro1.xls"
cNombreLibro = "Libro1"
cNombreTabla = "Socios"

Set oConnection = New ADODB.Connection
oConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\tmp\Bd1.mdb;"

cSQL = "SELECT * INTO [Excel 8.0;DATABASE=" & cNombreFicheroExcel & "].[" & _
cNombreLibro & "] FROM " & "[" & cNombreTabla & "]"

oConnection.Execute cSQL

oConnection.Close

Set oConnection = Nothing

Al utilizar ésta forma de crear una hoja de cálculo, todas las celdas tendrán el formato General, a excepción de los campos que contengan un tipo de dato Fecha, en los que Excel reconocerá dicho formato de celda.

Este código Visual Basic sería reutilizable en páginas ASP (Active Server Pages) realizando algunas modificaciones:

<%@ language="VBScript"%>

<%
Dim oConnection
Dim cSQL

Set oConnection = Server.CreateObject("ADODB.Connection")
With oConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "C:\tmp\Libro1.xls"
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With

cSQL = "CREATE TABLE Clientes (IdCliente LONG, [Nombre Cliente] TEXT," & _
"Domicilio TEXT, [Fecha Alta] DATETIME)"

oConnection.Execute cSQL

oConnection.Close

Set oConnection = Nothing
%>

<%@ language="VBScript"%>

<%
Dim cNombreFicheroExcel
Dim cNombreLibro
Dim cNombreTabla
Dim oConnection
Dim cSQL

cNombreFicheroExcel = "C:\tmp\Libro1.xls"
cNombreLibro = "Libro1"
cNombreTabla = "Socios"

Set oConnection = Server.CreateObject("ADODB.Connection")
oConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\tmp\Bd1.mdb;"

cSQL = "SELECT * INTO [Excel 8.0;DATABASE=" & cNombreFicheroExcel & "].[" & _
cNombreLibro & "] FROM " & "[" & cNombreTabla & "]"

oConnection.Execute cSQL

oConnection.Close

Set oConnection = Nothing
%>

Fuente: Trabajar con ADO, DAO y Excel.