Introducción
No es un hecho poco común el que nos encontremos con la necesidad de acceder desde nuestras aplicaciones, ya sean estas de escritorio (Win32) o de Internet (ASP.NET), a datos almacenados en otros formatos que no sean bases de datos. En este articulo les mostraremos como leer una planilla Excel subida por un usuario a un sitio Web utilizando ADO.NET.
Limitaciones Actuales
Uno de los casos con los que podemos encontrarnos es la necesidad de leer los datos de una planilla Excel. De acuerdo al escenario en el que nos movamos, la lectura podría llegar a tener grandes problemas de escalabilidad. Supongamos que estamos desarrollando un sitio Web y le brindamos al usuario la posibilidad de subir al sitio un archivo en formato Excel para, la importación de esos datos en una Base de Datos. Una de las posibilidades de leer un archivo XLS, es usar Excel mismo instanciándolo mediante OLE Automation.
'Creo un Nuevo objeto Excel.
Dim oExcelApp As Excel.Application
oExcelApp = New Excel.Application()
La primera desventaja es que esta instanciación implica Excel corriendo en todo su esplendor, lo que consume recursos en el Servidor. Ni pensar si esta tarea esta siendo realizada simultáneamente por varios usuarios.
Otro inconveniente con el que podríamos llegar a toparnos es la necesidad de una sesión de usuario abierta en el servidor para que pueda correr Excel. Punto de seguridad en contra para el administrador del equipo.
Solución
Para evitar estos inconvenientes de seguridad y por sobre todo de escalabilidad, es que les detallaremos los pasos a seguir para poder acceder a datos almacenados en forma de tablas dentro de una hoja de cálculo de Excel, utilizando los servicios de ADO.NET, lo que nos permitirá manipularlos como si se tratara de tablas normales de Access o SQL Server.
Generando la planilla de ejemplo
Como ejemplo, empezaremos creando una hoja cálculo en Excel, con datos de nuestra colección de CD's:
Uno de los requisitos más importantes en esta tarea es definir con un nombre al área de datos (rango de celdas). Este rango de celdas es el que definirá una Tabla en términos de ADO.
Para ello debemos seleccionar el rango y luego seleccionar la opción "Definir ..." del menú "Insertar \ Nombre", en nuestro caso ingresaremos "miTabla". Más adelante veremos que "miTabla" pasará a ser el identificador utilizado en nuestras instrucciones SQL.
Esta manera de trabajar asignando nombres a los rangos de celdas nos permite tener diferentes tablas dentro de una misma planilla, sólo debemos asignar diferentes nombres a cada uno de esos rangos. Podemos pensar entonces en la planilla Excel como una Base de Datos con un conjunto de tablas no relacionadas definidas por áreas de datos con Nombre
Otro punto a tener en cuenta es que ADO.NET considera que en la primera fila del rango seleccionado están los nombres de los campos de la tabla. Si el rango de celdas no contiene los nombres de los campos (títulos de columnas), debemos seleccionar una fila más por encima de los datos aunque las celdas estén vacías, en cuyo caso ADO.NET tomará nombres por defecto de la siguiente manera: "F1" para la primera columna, "F2" para la segunda, "F3", para la tercera, etc. Si no lo hacemos de esta manera, la primera fila de datos se considerará como nombres de los campos, con lo que habremos perdido el primer "registro" de nuestra tabla.
Una vez finalizada la carga de datos en la tabla de ejemplo, podemos comenzar a trabajar con nuestro proyecto, que en este caso, será una aplicación ASP.NET.
Upload de un archivo en ASP.NET, paso a paso
Esta aplicación nos permitirá hacer un "Upload" (subir un archivo) de un documento Excel a un directorio que nosotros definiremos en el servidor de nuestro sitio Web (por Ej.: C:\Inetpub\wwwroot\ExcelAdo\Upload). Una vez que la planilla se encuentre en el servidor, visualizará una grilla con los datos contenidos en ella. Este directorio deberá ser creado previamente a la ejecución del proyecto y además deberá tener asignado permisos de escritura para el usuario impersonado de ASP.NET.
Para hacer esto crearemos primero el directorio utilizando Windows Explorer y luego le asignaremos los permisos siguiendo estos pasos:
- 1) Hacer click con el botón derecho del mouse sobre el directorio Upload recientemente creado y seleccionar la opción Propiedades (Properties).
- 2) En la ventana que aparece hacemos click en la solapa Seguridad (Security).
- 3) Seleccionamos el usuario impersonado de ASP.NET (aspnet_wp account) o si no aparece en la lista lo agregamos mediante el botón Agregar (Add).
- 4) En la parte inferior de la ventana tildaremos la opción Permitir (Allow) para la acción Escribir (Write).
- 5) Damos click en Aceptar (OK) y cerramos Windows Explorer.
Para crear nuestro proyecto ASP.NET, si contamos con Visual Studio.NET seleccionaremos Proyecto Visual Basic o Visual C# (según nuestra preferencia) y utilizaremos la plantilla "ASP.NET Web Application".
A continuación, utilizaremos el Editor de WebForms para visualizar el código HTML del WebForm creado por defecto por VS.NET haciendo click en la solapa "HTML" del editor y reemplazaremos el tag <form> por el que a continuación se transcribe:
<form id="Form1" method="post" encType="multipart/form-data" runat="server">
Seleccione el Archivo a Enviar: <INPUT id="File1" type="file" name="File1" runat="server">
<asp:button id="cmdEnviar" runat="server" Text="Enviar"></asp:button>
<BR>
<asp:datagrid id="DataGrid1" runat="server" Width="585px" Height="159px" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="3" GridLines="Horizontal" Font-Names="Arial" Font-Size="10pt">
<SelectedItemStyle Font-Bold="True" ForeColor="#F7F7F7" BackColor="#738A9C"></SelectedItemStyle>
<AlternatingItemStyle BackColor="#F7F7F7"></AlternatingItemStyle>
<ItemStyle ForeColor="#4A3C8C" BackColor="#E7E7FF"></ItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="#F7F7F7" BackColor="#4A3C8C"></HeaderStyle>
<FooterStyle ForeColor="#4A3C8C" BackColor="#B5C7DE"></FooterStyle>
<PagerStyle HorizontalAlign="Right" ForeColor="#4A3C8C" BackColor="#E7E7FF" Mode="NumericPages"></PagerStyle>
</asp:datagrid>
<BR>
<asp:label id="Mensaje" runat="server"></asp:label>
</form>
Dado que el objetivo de esta nota no es adentrarnos en el código HTML diremos muy brevemente que estas líneas generarán en el explorador una página básica que permitirá seleccionar un archivo (el documento Excel) y enviarlo al servidor haciendo un post con la codificación multipart/form-data . Además incluirá una grilla con los datos contenidos en la tabla dentro del documento Excel, todo esto utilizando HTML Controls y Web Controls.
A continuación, debemos ingresar el código que dé funcionalidad a nuestro proyecto. Para ello en la ventana Explorador de Soluciones (Solution Explorer) de VS.NET, haremos click con el botón derecho del mouse sobre el ítem "WebForm1.aspx" seleccionando la opción "Ver Código" ("View Code") y escribiremos lo siguiente:
using System.IO;
using System.Data
using System.Data.OleDb;
El namespace System.Data incluye la mayoría de las clases que constituyen ADO.NET, el corazón de nuestro proyecto. Más adelante, en este mismo artículo, hablaremos con un poco más de detalle de ADO.NET.
ADO.NET incluye en forma nativa los proveedores de datos (data providers) SQL Server .NET Data Provider y OLEDB .NET Data Provider, y dado que utilizaremos éste último en nuestro proyecto, incluiremos también el NameSpace System.Data.OleDb.
Posteriormente, utilizando el editor de VS.NET accederemos al evento cmdEnviar_Click e ingresaremos el código que a continuación se detalla. Recordemos que ASP.NET, a través de la implementación de CodeBehind (traducido literalmente como "Código Detrás"), nos permite construir una aplicación de Internet casi como si se tratara de una aplicación de escritorio (Win32), escribiendo código en cada uno de los eventos que se produzcan durante la navegación de un sitio Web.
private void cmdEnviar_Click(object sender, System.EventArgs e)
{
string Destino = Server.MapPath(null) + "\\Upload\\" + Path.GetFileName(File1.PostedFile.FileName);
OleDbConnection oConn = new OleDbConnection();
OleDbCommand oCmd = new OleDbCommand();
OleDbDataAdapter oDa = new OleDbDataAdapter();
DataSet oDs = new DataSet();
File1.PostedFile.SaveAs(Destino);
Mensaje.Text = "Su archivo ha sido cargado en : <b>" + Destino + "</b><br>Tamaño: " + File1.PostedFile.ContentLength + " bytes.";
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Destino + ";Extended Properties=Excel 8.0;";
oConn.Open();
oCmd.CommandText = "SELECT * FROM miTabla";
oCmd.Connection = oConn;
oDa.SelectCommand = oCmd;
oDa.Fill(oDs);
DataGrid1.DataSource = oDs.Tables[0].DefaultView;
DataGrid1.DataBind();
oConn.Close();
}
Explicaremo el objetivo de cada una de las líneas basándonos en el código escrito en C#.
string Destino = Server.MapPath(null) + "\\Upload\\" + Path.GetFileName(File1.PostedFile.FileName);
"File1" es uno de los controles de nuestra página, más específicamente un control HTML que incluye un cuadro de texto más un botón de comando que permite navegar los directorios locales del usuario del sitio por intermedio del explorador y seleccionar un archivo que será enviado (Upload) al servidor.
De este control obtendremos el nombre de ese archivo, propiedad FileName. Este nombre corresponde a la ubicación del archivo en el directorio del usuario cliente. Dado que nosotros necesitamos el nombre del archivo con su ruta completa en la estructura de directorios del servidor Web, es que utilizamos el método GetFileName de la clase Path y concatenamos el valor devuelto por él a la cadena formada por el directorio de nuestra aplicación en el servidor (Server.MapPath(null)) y el subdirectorio "\\Upload\\" en la variable Destino, con lo que podremos tener acceso al archivo "subido" por el visitante a nuestro servidor.
OleDbConnection oConn = new OleDbConnection();
OleDbCommand oCmd = new OleDbCommand();
OleDbDataAdapter oDa = new OleDbDataAdapter();
DataSet oDs = new DataSet();
Estas cuatro líneas corresponden a la declaración de las referencias a los objetos de ADO.NET (Connection, Command, DataAdapter y DataSet respectivamente). Describiremos brevemente cada uno de estos objetos fundamentales del modelo ADO.NET:
- Connection: Es el objeto que maneja todo lo concerniente a la conexión de nuestra aplicación a la fuente de datos externa (Transacciones, objetos Command implícitos, TimeOut's, etc), a través de un proveedor de datos (data provider) administrado, en nuestro caso, OleDb Data Provider. Representa una única sesión con un origen de datos, lo que nos permite utilizar una misma conexión (a un mismo origen de datos) para realizar diferentes tareas, ahorrando valiosos recursos.
- Command: Representa una instrucción SQL o un procedimiento almacenado (stored procedure) a ejecutar contra la base de datos. Utiliza un objeto connection para su ejecución.
- DataAdapter: Es un "puente" entre el origen de datos y el objeto DataSet. Es un objeto de "ida y vuelta" ya que permite actualizar el DataSet a partir de cambios ocurridos en el origen de datos (método Fill) y actualizar el origen de datos por cambios que hayan ocurrido en el DataSet en nuestra aplicación (método Update).
- DataSet: Es un repositorio en memoria de datos obtenidos desde un origen de datos (a través de un DataAdapter). Se lo denomina "Contenedor Universal" dado que puede utilizarse para diferentes orígenes de datos que no sean únicamente bases de datos. Maneja a través de colecciones más de una tabla a la vez (DataTables), además de las relaciones existentes entre ellas (DataRelations). Está basado en el estándar XML por lo que incluye todos los beneficios de esta tecnología (uso de esquemas, serialización, etc.).
File1.PostedFile.SaveAs(Destino);
Mensaje.Text = "Su archivo ha sido cargado en : <b>" + Destino + "</b><br>Tamaño: " + File1.PostedFile.ContentLength + " bytes.";
El método SaveAs graba el cuerpo de un mensaje MIME, en nuestro caso el documento Excel "subido" al servidor, a un archivo. Recordemos que "Destino" contiene la ruta completa en la estructura de directorios del servidor. La siguiente línea le avisa al usuario que el archivo fue subido exitosamente informando además la ruta donde fue grabado y el tamaño (File1.PostedFile.ContentLength) utilizando el control denominado "Mensaje" (que es un control de la clase WebControls.Label).
Hasta aquí el código corresponde al envío del archivo. Las instrucciones que se describen a continuación corresponden a la utilización de ADO.NET para el acceso a los datos almacenados en la planilla Excel.
ADO.NET en acción
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Destino + ";Extended Properties=Excel 8.0;";
En esta línea, se puede ver que la propiedad ConnectionString del objeto Connection especifica que en la conexión se utilizará el proveedor Microsoft Jet OLEDB 4.0 para acceder a los datos contenidos en la planilla. Obsérvese que se estableció además el parámetro "Extended Properties=Excel 8.0".
A partir de allí, utilizaremos los objetos de ADO.NET mencionados anteriormente como si estuviéramos accediendo a una tabla almacenada en una base de datos "de las clásicas".
Recordemos que en la línea:
oCmd.CommandText = "SELECT * FROM miTabla";
"miTabla" corresponde al nombre que asignamos al rango de celdas que contienen nuestros datos.
Luego resta indicar al objeto Command que conexión utilizará para su ejecución ...
oCmd.Connection = oConn;
... asignarlo al objeto SelectCommand del DataAdapter ...
oDa.SelectCommand = oCmd;
... llenar el DataSet mediante el método Fill del objeto DataAdapter ...
oDa.Fill(oDs);
... enlazarlo al DataGrid para visualizar los registros en la grilla ...
dataGrid1.DataSource = oDs.Tables[0].DefaultView;
... mostrar los datos en la grilla obtenidos a través de su propiedad DataSource ...
DataGrid1.DataBind();
... y cerrar la conexión ...
oConn.Close();
Si ejecutamos la aplicación veremos los registros de nuestra tabla Excel:
Conclusiones
Hemos visto aquí cómo realizar de manera simple y en forma combinada dos tareas muy comunes en el desarrollo de aplicaciones Web, la necesidad de subir archivos utilizando ASP.NET y el acceso a datos en otros formatos que no sean bases de datos para su procesamiento mediante ADO.NET.
Ahora bien, cabe mencionar que aunque en este artículo utilizamos ADO.NET para acceder a datos almacenados en formato Excel, esto no es mérito del Framework .NET sino de ADO propiamente dicho, dado que éste ya nos permitía, en forma similar, acceder a datos en Excel desde sus versiones anteriores, utilizado desde aplicaciones desarrolladas con Visual Basic 6.0, por ejemplo.
Descargar código fuente
Referencias
Este artículo fue publicado originalmente para la revista MTJ.NET de MSDN Latinoamérica en Noviembre de 2002
Coautor: Victor H. Passador es Analista de Sistemas desde el año 1996 y profesor en las carreras de Licenciatura en Análisis de Sistemas e Ingeniería en Electrónica de la Universidad Tecnológica Nacional. Su primer contacto con la programación fue con el lenguaje Basic de las viejas Timex Sinclair 2068, luego con Clipper (ver. 5.0), Visual Basic (desde la versión 3.1) y actualmente con lenguajes .NET. Actualmente se desempeña como desarrollador en la consultora Vemn Sistemas.
