Lectura/Escritura de archivos Excel con ADO.NET

Hace poco me plantearon la necesidad de cargar un archivo Excel en base de datos a través de una aplicación web. En un primer momento pensé en utilizar un paquete DTSX y me puse a investigar como SSIS realiza la conexión a este tipo de archivos. Esta se realiza a través de MDAC (Microsoft Data Access Framework) evitando la necesidad de tener instalado Microsoft Office en el servidor. Por lo tanto, para leer un archivo Excel programáticamente se puede realizar a través de una conexión OLE DB. A continuación os explico como hacerlo:

 

Conexión al archivo:

En el siguiente código creamos la cadena de conexión dependiendo de la versión a través de la clase OleDbConnectionStringBuilder:

string rutaExcel = "c:\ArchivoExcel.xls";

 

//Creamos la cadena de conexión con el fichero excel

OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder();

cb.DataSource = rutaExcel;

 

if (Path.GetExtension(rutaExcel).ToUpper() == ".XLS")

{

    cb.Provider = "Microsoft.Jet.OLEDB.4.0";

    cb.Add("Extended Properties", "Excel 8.0;HDR=YES;IMEX=0;");

}

else if (Path.GetExtension(rutaExcel).ToUpper() == ".XLSX")

{

    cb.Provider = "Microsoft.ACE.OLEDB.12.0";

    cb.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES;IMEX=0;");

}

El proveedor para archivos .xls se encuentra en el MDAC, pero a partir de la versión 2007, hay que utilizar el proveedor 2007 Office System Driver: Data Connectivity Components. En las propiedades extendidas de la cadena de conexión se establece la versión del archivo y los siguientes parámetros:

  • HDR: Indica si la primera fila corresponde al nombre de los campos.
  • IMEX: Este parámetro indica como tratar los tipos de datos y puede llevar los siguientes parámetros:
    • 0: Modo de exportación. Si se quieren mantener los tipos de datos este es el modo a utilizar.
    • 1: Modo de importación. Con la configuración por defecto del driver, se evalúan los 8 primeros caracteres y dependiendo de estos se establece como texto o número.
    • 2: Modo enlazado. Permite actualizar los datos del archivo

Para este ejemplo utilizaremos el modo IMEX=0 para poder trabajar con datos tipados.

 

Lectura de datos:

Una vez tenemos la cadena de conexión solo falta crear el comando que leerá los datos:

DataTable dt = new DataTable("Datos");

using (OleDbConnection conn = new OleDbConnection(cb.ConnectionString))

{

    //Abrimos la conexión

    conn.Open();

 

    using (OleDbCommand cmd = conn.CreateCommand())

    {

        cmd.CommandType = CommandType.Text;

        cmd.CommandText = "SELECT * FROM [Datos$]";

 

        //Guardamos los datos en el DataTable

        OleDbDataAdapter da = new OleDbDataAdapter(cmd);

        da.Fill(dt);

    }

 

   

    //Cerramos la conexión

    conn.Close();

}

Este comando leerá todas las filas de la hoja “Datos”. Como en la cadena de conexión utilizamos el parámetro HDR=true, la primera fila deberá corresponder con los nombres de las columnas. Para los nombres de las tablas se usa la siguiente nomenclatura:

  • Nombre de hoja: El nombre de una hoja seguida del carácter $. Ejemplo [Hoja1$].
  • Nombre de rango: Se puede utilizar un rango de datos con un nombre definido.
  • Rango: Nombre de hoja + dirección del rango. Ejemplo: [Hoja1$A1:D100].

 

Creación de Hojas

Para crear una nueva hoja utilizamos el comando CREATE TABLE:

using (OleDbConnection conn = new OleDbConnection(cb.ConnectionString))

{

    //Abrimos la conexión

    conn.Open();

 

    //Creamos la ficha

    using (OleDbCommand cmd = conn.CreateCommand())

    {

        cmd.CommandType = CommandType.Text;

        cmd.CommandText = @"CREATE TABLE [Coches]

                            (

                                IdCoche INTEGER,

                                Marca TEXT,

                                Modelo TEXT,

                                FxCompra DATETIME

                            )";

        cmd.ExecuteNonQuery();

    }

 

    //Cerramos la conexión

    conn.Close();

}

 

En este ejemplo se creará una hoja llamada “Coches” cuya primera fila corresponderá al nombre de las columnas. En el siguiente ejemplo insertaremos datos en la nueva hoja.

 

Inserción de datos

Para insertar datos utilizamos el siguiente comando:

using (OleDbConnection conn = new OleDbConnection(cb.ConnectionString))

{

    //Abrimos la conexión

    conn.Open();

 

    //Creamos la ficha

    using (OleDbCommand cmd = conn.CreateCommand())

    {

        cmd.CommandType = CommandType.Text;

        cmd.CommandText = @"INSERT INTO [Coches$]

                            (

                                IdCoche,

                                Marca,

                                Modelo,

                                FxCompra   

                            ) VALUES

                            (

                                @IdCoche,

                                @Marca,

                                @Modelo,

                                @FxCompra,

                            )";

 

        cmd.Parameters.AddWithValue("@IdCoche", 1);

        cmd.Parameters.AddWithValue("@Marca", "Ferrari");

        cmd.Parameters.AddWithValue("@Modelo", "599 GTB");

        cmd.Parameters.AddWithValue("@FxCompra", DateTime.Now);

 

        cmd.ExecuteNonQuery();

    }

 

    //Cerramos la conexión

    conn.Close();

}

 

Conclusión

Existen otras soluciones para importar ficheros Excel, pero todas obligan a instalar Microsoft Office en el servidor, o bien utilizar algún componente de terceros, que al final se basan en esta técnica. En cuanto a la exportación, si existen otras alternativas, como la que se puede ver en este otro artículo.

En los siguientes enlaces explican en detalle esta solución, que viene usándose desde los inicios de ADO:

Lectura/Escritura de archivos Excel con ADO.NET
David Andres .

David es analista y desarrollador de aplicaciones web cliente-servidor especializado en ASP.Net. En sus años de experiencia ha participado en proyectos para importantes empresas y entidades como Panda Security, Merck Sharp And Dohm, el gobierno de Navarra o Repsol. Conoce a fondo todas las versiones del framework .Net, bases de datos SQL Server y Oracle, y otras tecnologías como MS SharePoint y W4 Workflow. En desarrollo domina los lenguajes C#.Net, VB.Net, Java (J2EE, Struts), C, C++, JavaScript, T-SQL y PL/SQL entre otros.
Fecha de alta:10/04/2011
Última actualizacion:10/04/2011
Visitas totales:40332
Valorar el contenido:
Últimas consultas realizadas en los foros
Últimas preguntas sin contestar en los foros de devjoker.com