Consultar documentos Excel
Aunque no lo crean, realizar una consulta de datos contenidos en documentos Excel, es muy similar a consultar datos contenidos en otro tipo de bases de datos (por ejemplo, Access). Comencemos analizando el código que nos permitirá acceder a los datos.
'Asigna a la variable Path, la ruta del archivo *.xls
Path=Server.MapPath("Ejercicio1.xls")
'Establece una conexión entre el servidor asp y una base de datos
Set ConexionBD = Server.CreateObject("ADODB.Connection")
'Abrimos el objeto con el driver específico para Microsoft Excel
ConexionBD.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & Path
'Crea un objeto de tipo recordset para retornar la consulta sql
Set rsVac = Server.CreateObject("ADODB.Recordset")
'Se abre el recordset, señalando como tabla el rango de celdas Excel llamado Feriado.
rsVac.Open "Select * From Feriado order by Oficina, Nombre", ConexionBD,3,3
Como pueden observar, básicamente el código es el mismo que utilizamos para conectarnos con Access. Las principales diferencias son dos:
1. Para abrir la conexión con la base de datos utilizamos el Driver específico para Microsoft Excel.
2. Al abrir el recordset, en la sentencia SQL hacemos referencia al nombre de un rango de celdas de Excel, en vez de referirnos a una tabla específica de Access. Para asignar un nombre a un rango de celdas, en MSExcel debemos seleccionar el menú Insertar, la opción Nombre y la subopción Definir. Luego, asignamos el nombre Feriado al rango y por último, en la casilla "Se refiere a:" seleccionamos el rango =FERIADO!$A$1:$G$6. Ahora, si este procedimiento no nos parece muy claro, podemos reemplazar el nombre Feriado de la consulta SQL por el rango a utilizar. En nuestro ejemplo, nos quedaría así:
'Se abre el recordset, señalando como tabla el rango de celdas Excel llamado Feriado.
rsVac.Open "Select * From A1:G6 order by Oficina, Nombre", ConexionBD,3,3
Una vez que hemos creado el objeto recordset, no nos queda más que comenzar a utilizarlo exactamente igual que si se tratara de datos Access. Por ejemplo, para mostrar en una tabla el contenido de algunos campos del recordset, bastaría que utilizáramos el siguiente código:
'Nos posicionamos al principio del recordset, por cualquier cosa
rsVac.MoveFirst
'Y por fin comenzamos a escribir la tabla de resultados
'Primero los nombres de las columnas (el encabezado de la tabla)
Response.Write "<th>" & rsVac.Fields.Item(0).Name & "</th>" & vbCrLf
Response.Write "<th>" & rsVac.Fields.Item(1).Name & "</th>" & vbCrLf
Response.Write "<th>" & rsVac.Fields.Item(6).Name & "</th>" & vbCrLf
'Y por último, el cuerpo de los datos
Do While Not rsVac.EOF
Response.Write "<tr>" & vbCrLf
Response.Write "<td>" & rsVac(0) & "</td>"
Response.Write "<td>" & rsVac(1) & "</td>"
Response.Write "<td>" & rsVac(6) & "</td>"
Response.Write "</tr>" & vbCrLf
rsVac.MoveNext
Loop
Response.Write "</table>"
'Se cierra y se destruye el objeto recordset
rsVac.Close
Set rsVac = Nothing
'Se cierra y se destruye el objeto connection
ConexionBD.Close
Set ConexionBD = Nothing
Crear documentos Excel en el Servidor con OWC
En determinadas ocasiones necesitaremos crear documentos Excel en el servidor, ya sea para guardar datos entregados por el cliente o para almacenar información generada por distintos procesos automatizados. Una de las formas más sencillas para crear este tipo de archivos es utilizar los componentes OWC (Office Web Components), que forman parte de la suite Office 2000. Por tanto, para que el código utilizado en este sección funcione correctamente es necesario que en el servidor se encuentre instalado el Officce 2000.
Pero bueno, manos a la obra... En primer lugar, deberemos crear el objeto OWC para generar una hoja de trabajo o Spreadsheet. Para ello utilizaremos el siguiente código:
'Se crea un objeto con Office Web Components
Dim oExcel
Set oExcel = Server.CreateObject("OWC.Spreadsheet")
Luego, deberemos acceder directamente a las celdas de la hoja de cálculo creada, escribiendo los datos que deseemos en ella.
'Inicializamos las variables para recorrer las filas y columnas de la hoja
Fila = 1
Columna = 1
' Se escriben valores de la variable cont en la hoja Excel, accediendo directamente a Filas y Columnas
For Fila=1 to 10
For Columna=1 to 10
cont = cont+1
oExcel.Cells(Fila,Columna).Value = cont
Next
Next
Posteriormente, podemos dar algo de formato a la hoja de cálculo. En este caso, fijaremos el ancho de las columnas al tamaño de los datos ingresados. Además, los datos contenidos en la primera fila serán mostrados en negrita, cursiva y con una fuente de tamaño 20.
'Ajusta las columnas al ancho de su contenido
For iCol = 1 to Columna
oExcel.Columns(iCol).AutoFitColumns
Next
' Damos algo de formato a la primera línea de la hoja
Cont = 1
Do While Cont < 11
oExcel.Cells(1, Cont).Font.Bold = True
oExcel.Cells(1, Cont).Font.Italic = True
oExcel.Cells(1, Cont).Font.Size = 20
Cont = Cont + 1
Loop
Por último, no nos queda más que exportar la hoja con la que hemos trabajado, hacia un documento Excel que se guarde en el servidor. Para ello utilizaremos el siguiente código, en el cual se incluye el path y el nombre del archivo que generaremos:
'Se exporta la hoja Excel cargada en el objeto oExcel a un archivo .XLS
Path=Server.MapPath("Ejercicio3.xls")
oExcel.ActiveSheet.Export Path, 0
Si quieres bajarte los archivos de ejemplo pulsa aquí. El ZIP incluye el código en el archivo GenerarXLS_conOWC.asp y un ejemplo de la planilla que deberá generarse en el servidor en Ejercicio2.xls.
Crear documentos Excel en el Servidor desde consultas a bases de datos Access
Así como bajo ciertas circunstancias necesitamos generar archivos Excel con datos entregados por el usuario, es muy común que de vez en cuando queramos crear un documento XLS con los datos obtenidos desde una consulta efectuada a una base de datos Access. Para ello, efectuaremos una consulta normal a la base de datos Access, almacenando sus resultados en un recordset. Luego, recorreremos el recordset y mediante un objeto File System Object iremos creando un archivo de texto delimitado por tabuladores que guardaremos con extensión .xls para poder asociar a Excel.
' Se crean las variables a utilizar (PathXXX corresponde a la ruta del archivo de tipo XXX que utilizaremos).
PathMDB = Server.MapPath("Ejercicio3.mdb")
PathXLS = Server.MapPath("Ejercicio3.xls")
FinLinea = ""
' Creamos los objetos que utilizaremos, serán de los tipos FSO, TextStream y Connection
Set fso = Server.CreateObject("Scripting.FileSystemObject")
Set Arch_Excel = fso.CreateTextFile(PathXLS, True)
Set oConn = Server.CreateObject("ADODB.Connection")
' Abrimos la conexión, ejecuto la consulta y guardo los resultados en el recordset creado
oConn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & PathMDB
Set rs = oConn.Execute("SELECT Oficina, Nombre, Total FROM Feriado")
' Recorremos todo el recordset recuperando sus valores y escribiéndolos en el archivo Excel
Do while Not rs.EOF
FinLinea = ""
For each x in rs.fields
FinLinea = FinLinea & x.value & chr(9)
Next
Arch_Excel.writeline FinLinea
rs.MoveNext
Loop
Con esto ya hemos creado el archivo Excel en el servidor. Ahora, sólo debemos cerrar y destruir los objetos utilizados con el fin de liberar la memoria del servidor.
' Cierro y destruyo todos los objetos utilizados para liberar memoria del servidor
Arch_Excel.Close
Set Arch_Excel = Nothing
Set fso = Nothing
rs.Close
Set rs = Nothing
oConn.Close
Set oConn = Nothing
Por último, podemos insertar en el documento ASP un vínculo hacia el archivo XLS recientemente creado, de forma que el usuario pueda guardarlo en su propia máquina haciendo click en el botón derecho del mouse y seleccionando la opción "Guardar destino como...". Para ello bastará agregar el siguiente código:
<a href="<% =PathXLS %>">Abrir Excel</a>
Si quieres bajarte los archivos de ejemplo pulsa aquí. El ZIP incluye el código en el archivo GenerarXLS_desdeMDB.asp, un ejemplo de la planilla que deberá generarse en el servidor en Ejercicio3.xls, y la base de datos Access utilizada en la consulta en el archivo Ejercicio3.mdb.
Crear documentos Excel en el Cliente
En el apartado anterior, se incluyó una línea de código que permitía que el usuario baje un archivo Excel hacia su máquina seleccionando la opción "Guardar destino como...". Sin embargo, algunas veces necesitaremos que el archivo generado inicie automáticamente el proceso de download. Para ello, sólo basta utilizar un par de líneas al inicio del código y luego generar una tabla HTML que será interpretada como una hoja Excel.
Estas dos líneas especiales son las siguientes:
<%@ Language=VBScript %>
<% Response.ContentType="application/vnd.ms-excel" %>
Con la primera línea señalamos que el lenguaje que utilizaremos es VBScript y que queremos que el código se ejecute del lado del cliente. Luego, con la siguiente definimos el encabezado MIME, declarando que generaremos un documento Excel. Por defecto, el encabezado MIME es del tipo text/html, con lo que se generan los documentos HTML visibles en el browser.
Un ejemplo sencillo de esto es el siguiente:
<%@ Language=VBScript %>
<% Response.ContentType="application/vnd.ms-excel" %>
<Table>
<TR>
<TH>Col1</TH>
<TH>Col2</TH>
<TH>Total</TH>
</TR>
<TR>
<TH>10</TH>
<TH>20</TH>
<TH>=sum(a2:b2)</TH>
</TR>
<TR>
<TH>30</TH>
<TH>40</TH>
<TH>=sum(a3:b3)</TH>
</TR>
</TABLE>
