OLEDB para cargar archivos de excel

Recientemente debíamos cargar un archivo al servidor y cargar sus celdas en una base de datos. Ademas debíamos cumplir el requerimiento de no instalar ningún componente de MS Office en los servidores.

Como sugerencia del departamento de sistemas del cliente se nos indicó que utilizáramos Dtsx para realizar esa operación y de hecho llegamos un solución con paquetes SSIS. Sin embargo se nos complicó por una cuestión de permisos. Finalmente optamos por hacer una solución basada en OleDB más sencilla y más fácil para darle mantenimiento.

¿Y el rendimiento? pues ninguna diferencia porque resulta que los Dtsx para manejar excel utiliza OleDb. He aquí parte de la solución con .Net y OleDB

Primeramente recordar usar este using:

 using System.Data.OleDb;

Todo el secreto esta en la conexión

   OleDbConnection cnnOle = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Pruebas\test.xls;Extended Properties=\"EXCEL 8.0;HDR=NO;IMEX=1\";");
   cnnOle.Open();  

Aqui hay varios aspectos a tomar en cuenta:
- Primeramente el Data Source puede ser tanto una carpeta local, como en este caso, o bien una carpeta compartida o DFS (p.e. \\DFS\Pruebas\test.xls)

- Lo que vamos a hacer es una consulta SQL del Excel por lo que si en Extended Properties indicamos que HDR=YES, estamos diciendo que el primer valor de las celdas es el nombre con el que se conoceran las columas. Asi si tenemos un excel en el primer valor de cada columna, o encabezado, es PRODUCTO, CANTIDAD, PRECIO entonces podriamos hacer:

SELECT PRODUCTO, CANTIDAD, PRECIO FROM [Sheet1$]

Pero si usamos HDR=NO, usamos el nombre generico (F1, F2.,..) de las columnas, incluyendo, obviamente, el primer valor de cada columna como valores que nos devolvera la consulta

-Ademas debemos tener en cuenta que el IMEX=1 en Extended Properties significa que se tomaran los valor tal y como se FORMATEAN en las celdas, esto es muy importante porque por ejemplo si el valor de la celda es un número, supongamos 1000000 y se encuentra formateado como monto (1,000,000.00) es posible que sea mejor eliminar el tag IMEX del todo para usar el valor real de la celda en lugar del ya formateado para evitar errores de conversión de tipos.

Seguidamente hacemos la consulta:

DataTable schemaTable = cnnOle.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

OleDbCommand cmdOle = new OleDbCommand("SELECT F1,F2,F3  FROM [" + schemaTable.Rows[0]["Table_Name"].ToString() + "]", cnnOle);

OleDbDataAdapter daOle = new OleDbDataAdapter(cmdOle);
dt = new DataTable();
daOle.Fill(dt);

La primera instrucción lo que hace es extraer en un DataTable el schema del excel para poder sacar el nombre de las hojas, esto es importante porque en el si Excel esta en español las hojas se denominan Hoja1$, Hoja2$, etc.. y si esta ingles Sheet1$, Sheet2$, etc ademas puede tener un nombre personalizado y con esta instrucción podemos desentendernos de este aspecto.

Las demás instrucciones realizan la consulta usando OleDbCommand y OleDbDataAdapter para dejar la información que necesitamos en un DataTable y a partir de que lo podemos manipular como mejor nos convenga

Gracias una vez más a Pigo Sama por su inmensa sabiduría.

Roy {aka. Foy}

Autor & Editor

Desarrallador y líder técnico, con experiencia en tecnologías Microsoft desde los tiempos del VB6 y el asp clásico hasta el .Net Core, pasando por COM+, javascript, angularjs, Ionic, xaml, cordova, MVC, Web Api, Sql Server, Oracle... . Ávido lector, apasionado programador.