Rellenar un Excel desde C#
Patxi Echarte,
27. Sep, 2005,
106 Comentarios
.NET
No hay nada peor para un informático que hacer tareas repetitivas, lo que Neil Stephenson en su libro Criptonomicon llama hacer matrículas. Esta semana me ha tocado un «pequeño» marrón, consistente en ejecutar un procedimiento almacenado de SQL Server formado por varias consultas y rellenar con los resultados un excel, y todo ello repetirlo 120 veces, es decir, un auténtico marrón.
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
Hola! el codigo me esta sirviendo en el proyecto que estoy haciendo pero el problema es que no encuentro como crear un nuevo archivo exel en caso de que no exista ninguno, y como le doy una nueva ruta…
Dea ntemanos muchas gracias y espero me puedan ayudar.
Saludos!!
hola a todos
tengo un codigo que me exporta desde un datagirdview a exel sin problemas:
public void exporta_a_excel()
{
Microsoft.Office.Interop.Excel.ApplicationClass excel = new ApplicationClass();
excel.Application.Workbooks.Add(true);
int ColumnIndex = 0;
foreach (DataGridViewColumn col in TuDataGrid.Columns )
{
ColumnIndex++;
excel.Cells[1, ColumnIndex] = col.Name;
}
int rowIndex = 0;
foreach (DataGridViewRow row in TuDataGrid.Rows )
{
rowIndex++;
ColumnIndex = 0;
foreach (DataGridViewColumn col in TuDataGrid.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex + 1, ColumnIndex] = row.Cells[col.Name].Value ;
}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();
}
pero a ala ves que le pongo los datos a la celda quisiera ponerle un color de fondo a dicha celda
Pudieran ayudarme con eso
graciaaasss
Muchas gracias, me ayudaste bastante n_n
Solo esto necesitaba
un hello world!!!!!
XD
gracias a este articulo realice la prmera version de una aplicacion de escritorio muy interesante. Pero ahora estoy realizando una segunda version y no se como guardar datos en la columnna «C» y «D» de un Excel. Espero que alguien sepa a pertir de este ejemplo como hacerlo pues guiandome por aqui solo me salvan los valores en la promera columnna
X Q CUANDO PONGO LETRAS ME MARCA HERROR ???
muy bueno y util, pero como solucionaste lo de tener que hacerlo 120? en mi caso tengo que imprimir el formao un numero N de veces.
si alguien sabe como mandarlo a imprimir desde el programa, le agradeseria mucho que me explicara.