Rellenar un Excel desde C# - Rellenar un Excel desde C#

Como eso de repetir las cosas va en contra de mi religión, consideré que lo que correspondía era intentar hacer un programa que lo hiciera por mí, con lo cual me ahorraría unas cuantas horas y las que dedicase, al menos serían entretenidas. El programa debía constar de dos partes, una encargada de realizar las consultas y otra encargada de rellenar una hoja de excel con los resultados obtenidos. La primera parte no tiene ningún misterio, sin embargo la segunda puede parecer algo más complicada, aunque como veremos, nada más lejos de la realidad.

Hay dos formas principales de modificar el contenido de un archivo excel desde .NET, la primera y más potente se basa en la utilización de mecanismos de automatización, mediante el uso de los objetos COM de Office XP. Una segunda opción se basa en la utilización de ADO.NET para obtener o modificar las celdas del excel. Pese a que la primera opción es muy potente y nos permite controlar al 100% un archivo excel, también es algo más compleja, por lo que debido a que con las funcionalidades ofrecidas por la solución basada en ADO.NET era suficiente, me decidí por esta segunda opción.

Trabajar con esta opción basada en ADO.NET, es muy parecido a como trabajaríamos con cualquier otra base de datos, basta con abrir una conexión con el servidor utilizando el driver adecuado y ejecutar a continuación sentencias SQL ligeramente adaptadas.

Lo primero que necesitamos es referenciar los assemblies necesarios:

using System.Data;
using System.Data.OleDb;

Para este ejemplo vamos a utilizar un archivo de excel sencillo, en el que hay que rellenar varias celdas con valores calculados previamente, en concreto hay que rellenar las celdas B2 a la B5, con números que indican el número de estaciones disponibles de cada tipo, por ejemplo.

Para rellenar estas celdas lo que vamos a hacer es abrir una conexión utilizando el driver OLE e indicando el archivo excel a rellenar, lo cual se indica en el código de a continuación dentro de la variable strConnectionOle, que contiene la cadena de conexión que se pasará al constructor de la clase OleDbConnection para obtener la conexión necesaria para acceder al contenido del excel.

int[] data = new int[]{12,123,3,7};

string strConnnectionOle = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=ejemplo-excel.xls;" +
@"Extended Properties="+'"'+"Excel 8.0;HDR=NO"+'"';
OleDbConnection oleConn = new OleDbConnection(strConnnectionOle);
oleConn.Open();

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = oleConn;

cmd.CommandText = "UPDATE [Hoja1$B2:B2] SET F1="+data[0];
cmd.ExecuteNonQuery();

cmd.CommandText = "UPDATE [Hoja1$B3:B3] SET F1="+data[1];
cmd.ExecuteNonQuery();

cmd.CommandText = "UPDATE [Hoja1$B4:B4] SET F1="+data[2];
cmd.ExecuteNonQuery();

cmd.CommandText = "UPDATE [Hoja1$B5:B5] SET F1="+data[3];
cmd.ExecuteNonQuery();

oleConn.Close();

Una vez establecida la conexión podemos realizar diferentes acciones como inserciones, borrados o actualizaciones, sobre cualquiera de las hojas existentes en el excel. En nuestro ejemplo nos vamos a limitar a rellenar las celdas indicadas, B2:B5, para lo cual creamos un objeto de la clase OleDbCommand, con el que realizaremos la ejecución de las sentencias SQL. Los datos con los que vamos a rellenar las celdas los tenemos disponibles en la matriz data con datos prefijados, que en una aplicación real habríamos calculado mediante consultas SQL o algún otro mecanismo.

Para rellenar las celdas utilizamos sentencias SQL independientes, una para cada celda, ya que están ubicadas en diferentes filas. Cada fila del excel, o del rango que seleccionemos de él, es contemplada como un registro, por lo que tenemos que actuar como si de una tabla normal de SQL se tratara. Para ello podemos seleccionar un rango consistente en varias filas y utilizar un where para modificar algunas de ellas, o podemos modificar todos los “registros” de un rango formado por un único registro y hacerlo varias veces. Dado que los rangos que seleccionemos pueden no tener nombre de columna, la forma de acceder a ellos es mediante F1, F2, F3, nombres de columnas que se generan automáticamente si en la cadena de conexión hemos utilizado HDR=NO. En el caso que hayamos conectado usando HDR=YES, la primera fila de cada rango elegido se tomaría como nombres de columnas, lo cual en nuestro caso no es necesario. En algunos casos pueden producirse errores al ejecutar la aplicación si las celdas sobre las que se escribe no tienen asignado un formato específico, así por ejemplo, si deseamos escribir números como es el caso, las celdas deberían estar preparadas con formato número para que no dé fallos.

Con todo esto, la consulta SQL utilizada realiza una actualizaciones sobre los rangos B2:B2 al B5:B5, lo cual indica que se desea modificar las celdas B2, B3, B4 y B5 de la hoja con nombre Hoja1. En la parte de los SQL correspondientes al SET, se dispone de una variable auxiliar para cada elemento del rango a modificar, en nuestro caso F1, a la que asignamos el valor correspondiente de la matriz de datos.

Una vez asignado el SQL al comando únicamente queda ejecutarlo con ExecuteNonQuery y cerrar la conexión tras haber ejecutado todas las consultas.

Actualización: he subido un archivo comprimido con un proyecto de ejemplo hecho con SharpDevelop. Para hacerlo funcionar basta con compilar los fuentes y copiar el archivo xls a la misma carpeta donde se cree el ejecutable. Si utilizas Visual Studio deberás crearte un nuevo proyecto a partir de los fuentes.

Más información

• How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET
• Programming Microsoft Word 2002 and Excel 2002 with Microsoft Visual C#