ASP VS EXCEL

Lorion
09 de Diciembre del 2005
Hola a todos, se me ha planteado el siguiente problema y necesitara saber si se puede hacer:
La cuestin en un principio es sencilla, se trata de que tengo unas hojas de excel y necesito poder trabajar con ellas a travs de internet. Las hojas estn en un servidor web y tengo que poder , insertar, modificar y borrar datos. El problema? que no se si es posible. Alguien me puede guiar un poco? muchas gracias de antemano.

server142
09 de Diciembre del 2005
SI ES POSIBLE LO QUE DESEAS ME HE HECHO UN COPY AND PASTE DEL SITIO DE ASPFACIL.COM Y LE ANEXADO UN PAR DE COSAS ESPERO TE SEA UTIL.
http://server142.blogspot.com
<hr>
Con seguridad, Excel y Word deben ser los programas de oficina ms extendidos en el mundo informtico. Por ello, no es de extraar que en numerosas ocasiones aparezcan consultas referentes a Excel en los foros relacionados con programacin de pginas ASP. Este artculo tiene por objetivo responder algunas de esas preguntas, y en especial aquellas referentes a la forma de consultar y crear documentos del tipo XLS.

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 cdigo que nos permitir acceder a los datos.

\'Asigna a la variable Path, la ruta del archivo *.xls
Path=Server.MapPath("Ejercicio1.xls")

\'Establece una conexin entre el servidor asp y una base de datos
Set ConexionBD = Server.CreateObject("ADODB.Connection")


\'Abrimos el objeto con el driver especfico 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, sealando como tabla el rango de celdas Excel llamado Feriado.
rsVac.Open "Select * From Feriado order by Oficina, Nombre", ConexionBD,3,3

Como pueden observar, bsicamente el cdigo es el mismo que utilizamos para conectarnos con Access. Las principales diferencias son dos:

Para abrir la conexin con la base de datos utilizamos el Driver especfico para Microsoft Excel.


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 especfica de Access. Para asignar un nombre a un rango de celdas, en MSExcel debemos seleccionar el men Insertar, la opcin Nombre y la subopcin 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 quedara as:

\'Se abre el recordset, sealando 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 ms 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, bastara que utilizramos el siguiente cdigo:

\'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

El resultado de una consulta de este tipo, se vera ms o menos, de la siguiente manera:

Oficina Nombre Total
1 Martn Salas Oyarzo 50
1 Rodrigo Rohland Mayorga 37
12 Carlos de la Orden Dijs 15
12 Claudio Gonzlez Lpez 26
12 Mario Contreras Espinoza 18

Si quieres bajarte los archivos de este ejemplo pulsa aqu. El ZIP incluye el cdigo en el archivo ConsultarXLS.asp y la planilla utilizada como base de datos en Ejercicio1.xls.



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 informacin generada por distintos procesos automatizados. Una de las formas ms 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 cdigo utilizado en este seccin 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 cdigo:

\'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 clculo 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 clculo. En este caso, fijaremos el ancho de las columnas al tamao de los datos ingresados. Adems, los datos contenidos en la primera fila sern mostrados en negrita, cursiva y con una fuente de tamao 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 lnea 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 ms que exportar la hoja con la que hemos trabajado, hacia un documento Excel que se guarde en el servidor. Para ello utilizaremos el siguiente cdigo, 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 cdigo 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 comn 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 extensin .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, sern 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 conexin, 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 escribindolos 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, slo 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 vnculo hacia el archivo XLS recientemente creado, de forma que el usuario pueda guardarlo en su propia mquina haciendo click en el botn derecho del mouse y seleccionando la opcin "Guardar destino como...". Para ello bastar agregar el siguiente cdigo:

<a href="<% =PathXLS %>">Abrir Excel</a>

Si quieres bajarte los archivos de ejemplo pulsa aqu. El ZIP incluye el cdigo 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 lnea de cdigo que permita que el usuario baje un archivo Excel hacia su mquina seleccionando la opcin "Guardar destino como...". Sin embargo, algunas veces necesitaremos que el archivo generado inicie automticamente el proceso de download. Para ello, slo basta utilizar un par de lneas al inicio del cdigo y luego generar una tabla HTML que ser interpretada como una hoja Excel.

Estas dos lneas especiales son las siguientes:

<%@ Language=VBScript %>
<% Response.ContentType="application/vnd.ms-excel" %>

Con la primera lnea sealamos que el lenguaje que utilizaremos es VBScript y que queremos que el cdigo 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>

Este cdigo deber generar un archivo Excel que contenga los siguientes datos:



Col 1 Col 2 Total
10 20 30
30 40 70


Si quieres bajarte los archivos de ejemplo pulsa aqu. El ZIP incluye el cdigo en el archivo GenerarXLS_ClientSide.asp y un ejemplo de la planilla que deber generarse en el cliente en Ejercicio4.xls. Al ejecutar el archivo ASP, el documento Excel que se genere tendr el mismo nombre del archivo ASP, cambiando la extensin por .XLS.

En resumen...
En este artculo, se ha tratado de entregar un mtodo fcil y rpido de acceso a datos contenidos en documentos Excel. Todo lo expuesto en este artculo puede ser utilizado en cualquier sitio Web, pero sin embargo, debemos estar conscientes que la naturaleza de Excel no nos asegura un correcto funcionamiento, sobre todo en sitios que mantengan altos nmeros de usuarios conectados. Por tanto, mi experiencia me seala que lo ms conveniente es utilizar estos mtodos slo cuando se trate de sitios con bajo volmen de visitas concurrentes, tal como puede ser el caso de una Intranet.