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
como puedo leer datos de excel 2007?
Uso microsoft.ACE … pero me da un error «no se encuestra el archivo ISAM instalable» o algo asi.
Me puedes ayudar. Gracias!!!
hola…
por favor, su valiosa ayuda con el siguiente error:
no se encuestra el archivo ISAM instalable
el programa funciona perfectamente en windows xp, pero saca este error en w2000…. por favor, ayuda…. quedaré totalmente agradecida
Alejandra, prueba lo que se indica en esta página, puede deberse a que no tengas instalado el office o a que la instalación esté corrompida: http://support.microsoft.com/kb/209805/es
Hola… me ha sido muy util tu ejemplo…
El problema es que necesito desde visual 2 cosas:
1. crear hojas en excel con el nombre como fecha (es decir 25/11/07)
2. Hacer lo mismo que colocas en tu ejemplo pero con un «autonumerico» es decir, que cada vez que cree un nuevo registro lo haga en el siguiente renglon…
3. Calcular totales en la misma hoja de excel de determinado campo…
Si pueden ayudarme con alguno de estos problemillas les estoy MUY agradecida
Atte: Monica (mmonik15@gmail.com)
Hola Patxi muy bueno el articulo, muchas gracias por la aplicacion hace dias estaba buscando algo similar para hacer un control de asistencia para mi empresa y capturar cdigos de barras mostrando una interfaz desde un excel por comodidad, me vino como anillo al dedo….
Ahh Patxi deberias arreglar tu problema personal con Luz
Es que soy cubano y no podia dejarlo pasar..
Hola Dairo 😉 la verdad es que no tengo ningún problema con Luz, más bien parecía que lo tenía ella con el blog, pero de eso hace mucho tiempo, desde el 2005, seguro que ahora nos llevaríamos bien 😉
Un saludo para ti, tu gente y tu pais, que sois de lo mejor!!!
Muchas gracias, seguro serian amigos.. jaja Un saludo para ti tambien y saludos para los tuyos , bueno aqui no dice tu nacionalidad pero asumo que seas español.
Puede que mas alante requiera de tu ayuda.. gracias de antemano
Salu2.
Patxi te tengo una interrogante:
En el caso de que en el archivo excel existan columnas que se unen para formar una, en caso de por ejemplo b y c a cual de ellas se hace referencia?? a «b» o «c»
Gracias de antemano..
Bueno la pregunta esta mal hecha, que tal si quiesiera modificar A3, A4, A5 y luego B2 y luego H5 por ejemplo????
Thanks
Te hare una ultima pregunta (no tiene que ver con la aplicacion), necesito hacer una interfaz en c# que me reproduzca un documento word o excel para capturar los datos que se introducen mediante un scanner y volcarlos en una base de datos, a lo mejor sabes algo de eso o lo has visto en algun sitio….
Gracias de nuevo
hola Praxi estoy teniendo problemas al guardar datos en el excel, se me cuelga cuanod hace el cmd.ExecuteNonQuery(); Estoy usando Excel 2003 de Microsoft. Tenes idea que esta pasando y como puedo arreglarlo?
hola tengo problemas con el ejemplo que lo subiste, el error que me aparece es que tengo dos main, y no se como solucionarlo sera posible que me puedas ayudar porfa.
y al ELIMAR UN MAIN ME SALE EL SIGUIENTE ERROR
Error1 Atributo ‘AssemblyVersion’ duplicado E:\Cliza\rellenar-excel\rellenar-excel\Properties\AssemblyInfo.cs 32 12 rellenar-excel
Excelente articulo!!!! justo lo que necesitaba
Hola Patxi que bueno articulo me gusto mucho tu trabajo, con ese mismo ejemplo fijate que codifique con access pero me sale error porfavor te pido que me ayudes por que ya no se que hacer mas.
nota: el error que me sale es: no se puede modificar el diseño de la tabla.esta en una base de datos de solo lectura
sub Page_Load(Src As Object, E As EventArgs)
If Not IsPostBack Then
Dim conexion As New OleDbConnection
Dim ruta As String=»C:\inetpub\wwwroot\datosaspnet\clientes.mdb»
Dim conectar As String=»Provider=Microsoft.Jet.OLEDB.4.0;Data Source=»
Dim sql As New OleDbCommand
conexion.ConnectionString = conectar & ruta
conexion.open
sql.Connection = conexion
sql.CommandText = «CREATE TABLE Prueba1 » & _
«(Id INTEGER IDENTITY PRIMARY KEY, Nombre VARCHAR(40), Teléfono CHAR(9));»
sql.ExecuteNonQuery()
conexion.close
end if
End sub
Hola carlos, según el error que te da, tiene pinta de que no puedes acceder al access desde el programa que estás haciendo, comprueba que el usuario que ejecuta el programa tenga permisos de escritura en la carpeta C:\inetpub\wwwroot\datosaspnet\ y en la base de datos, así como que no esté protegida.
Hola Patxi,
Estoy trabajando con datasets y quiero generar un listado en excel …
Me encuentro con que tu ejemplo me funciona perfectamente con números, pero no sirve para strings… 🙁 lo he probado todo… (almenos lo que se dice y comenta en la página (o eso creo).
me iría bien una manita… ¿alguna idea?
Muchas Gracias!
Hola Nomad, debería funcionarte tanto para enteros como para strings, a mi al menos me funciona. Asegúrate lo único de que el tipo de la celda donde intentas insertar/actualizar o seleccionar valores, es del tipo que requieres.
Consulta también la primera de las referencia que pongo al final del post.
Hola a todos, por fa una ayuda con esto;
Como hago una función que permita introducir un dato en (ej)la celda D2 para que presente el la celda E2 los datos que estan en B2 y para ello el dato de D2 es igual a B1.
saludos
hola, y en el caso que quiera hacer un UPDATE para una cadena?? como haria en C#??, porque estoy haciendo un programa que actualice un archivo en excel y me esta saliendo: Error de sintaxis en la instrucción UPDATE. En tu codigo si pongo: string[] data = new string[] {«luis»,»cesar»,»jorge»,»jose» }, sale error: No se han especificado valores para algunos de los parámetros requeridos;
gracias
«…
luz dice:
November 24th, 2005 a las 8:00 pm
no encontre lo que necesitaba no entren a esta pagina
…»
Luz, como dice patxi, que lastima que no encontraste lo que buscabas, por lo que veo en los comentarios, hay gente que no tiene ni la menor idea de nada y solo quieren que les den el codigo … si no saben poner una referencia .. como saben que buscan ??
Por cierto, esta muy bueno el articulo, me dio una idea mas para lo que quiero hacer, no se me habia ocurrido intentar por este lado.
Saludos
Andres
Cordoba – Argentina
hola a todos con respecto al tema de que no funciona el update con string recominedo que al inicio y al final del texto pongan ‘ eso te permite cargar todo tipo de datos en ele excel
Todo Excelente pero requiero leer datos de excel 2007 que toca modificar
como puedo rellenar una hoja de excel desde c# y la conexion a oracle, aproximadamente son 24.000 registros, ya lo tengo pero quiero saber un método mas rápido ya que el que tengo es bastante lento y tarda un aproximado de 16 minutos.
hola!soy nuevo en esto ayudenme porfa que operaciones puedo hacer con un rango?
quiero saber como puedo agregar datos en celdas que ye tienen predefinidas formulas dentro de ellas, o sea la celda tiene una formula y quiero sobrescribir sobre ella, porfa ayuda en el colegio para el cual estoy q hago su sistema de notas me estan apurando que termine el programa
muy bueno che , Patxi, estaba pensando hasta que llegue aca.
salu2
Hola,
A la hora de realizar un Update sobre el fichero Excel, en concreto cuando quiero insertar números, me aparece en la celda el número precedido de comilla simple, p.ej.: ‘9. ¿Alguien sabe como quitarlo?
He intentado quitarlo con un replce y nada…
Este es el código que tengo:
//Establecemos la conexión
string connstring = @»Provider=Microsoft.Jet.OLEDB.4.0;» + @»Data Source=» + machineUpload + rootUpload + «\\» + ruta_Informe + «\\» + informe + «;» + @»Extended Properties=» + ‘»‘ + «Excel 8.0;HDR=NO» + ‘»‘;
connection.ConnectionString = connstring;
connection.Open();
insert = «INSERT INTO [Datos$C» + (3 + j) + «:C» + (3 + j) + «] VALUES(‘» + ds.Tables[0].Rows[0][0].ToString().Replace(«‘»,»») + «‘)»;
cmd.CommandText = insert;
cmd.ExecuteNonQuery();
Cualquier ayuda será bienvenida.
salu2
Esta página me sirvio de mucho.
un post muy interesante.
Grasias.
Hola, estoy trabajando sobre C# para ejecutar una excel llena de datos. Mi problema reside en que me gustaria que me orientarás , por favor, en decirme como puedo enviarle un numero a su celda correspondiente y que ésta tenga un formato Número y no General como el resto.
Muchas gracias y un saludo.
Hola necesito ayuda con una cadena de conexión que no me hace caso.
«provider=Microsoft.Jet.OLEDB.4.0;» & _
«data source=» + B3_DMZN_DMAD + «;Extended Properties=’Excel 8.0;HDR=Yes;IMEX=1′»
En teoria esta cadena de conexión me deberÃa de tratar los datos de todas las columnas como datos mixtos, pero en lugar de eso, sigue ignorandome y poniendome algunos datos como DBNull.
Me he metido incluso en el excell y he intentado incluso formatear todas las columnas a texto, pero ni aún asà me funciona.
Sabeis que puede pasar?
Hola, probe tu ejemplo y si me funciono, ahora lo que quiero es saber como puedo mandar los valores de una consulta de sql a excel pero mediante un boton de aspx con C#, es decir , que al dar clic me traiga los registros a mi pagina aspx y que tambien envie los datos al reporte en excel
que tal me parece muy bien tu articulo pero no tendras un ejemplo al reves lo que yo necesito es de una hoja de excel vaciarlo a un grid view
Saludos,
Eres un genio, he buscado por miles de páginas por un error que me daba el connectionstring y por fin lo pude solucionar con tu código. Puedes creer que ni en las páginas de microsoft está claro que «Extended Properties=»+'»‘+»Excel 8.0;HDR=NO»+'»‘ tiene que tener el formato de string. Un millón de gracias.
Saludos.
Una vez que carge mi excel necesito que se archivo el usuario eliga una ruta para alojarlo?
Como podria realizar esa tarea?
gRACIAS
La cadena de conexion para visual basic?
¿Cual es el errror? No entiendo
Dim strConnnectionOle As String = «Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ejemplo-excel.xls;Extended Properties=»»Excel 8.0;HDR=NO»»»
‘string strConnnectionOle = @»Provider=Microsoft.Jet.OLEDB.4.0;» +@»Data Source=ejemplo-excel.xls;» + @»Extended Properties=»+'»‘+»Excel 8.0;HDR=NO»+'»‘;
Dim oleConn As OleDbConnection = New OleDbConnection(strConnnectionOle)
oleConn.Open()
Dim cmd As OleDbCommand = New OleDbCommand
cmd.Connection = oleConn
Dim a, b, c, d As Integer
a = 1
b = 2
c = 3
d = 4
cmd.CommandText = «UPDATE [Hoja1$B2:B2] SET F1=» & a
cmd.ExecuteNonQuery()
cmd.CommandText = «UPDATE [Hoja1$B3:B3] SET F1=» & b
cmd.ExecuteNonQuery()
cmd.CommandText = «UPDATE [Hoja1$B4:B4] SET F1=» & c
cmd.ExecuteNonQuery()
cmd.CommandText = «UPDATE [Hoja1$B5:B5] SET F1=» & d
cmd.ExecuteNonQuery()
oleConn.Close()
Catch ex As Exception
End Try
una pregunta saben cual es el codigo para poder elimnar un registro de excel que esta mostrado en un datagridview desde c#??
Muy buen artículo, muchas gracias!
espero sigas haciendo esta clase de trabajos geniales
good work!
Hola, tengo un problema….. Estoy leyendo información de un archivo de excel tal como se muestra en el ejemplo, Lo raro es que solamente me funciona cuando tengo abierto el archivo de excel, si lo cierro me aparece el siguiente error: «La tabla externa no tiene el formato esperado».
Es como si no encontrara el archivo cuando no lo tengo abierto con el excel!!!. Alguien sabe qué puede ser???
juanrod1 , hola a mi me paso eso, pero era porque el archivo estaba guardado como web en vez de un libro xls… modificalo y deberia funcionarte.
saludos
Para modificar propiedades de un graphics que ‘tire’ de datos de los updates de las celdas del excel… ¿como podremos hacerlo?
Buen día hice un codigo para crear una planilla excel muy compleja con diferentes columnas filas etc., pero que se crean dinamicamente al ejecutarse y dependera de los registros que obtengo desde una base de datos que pueden variar. Lo que hace el codigo es crear la planilla y dejarla abierta , pero localmente desde V.studio funciona ok, al publicar el sitio en el hosting o en el localhost no me genera la planilla., y es lo gico ya que tal vez el cliente no tenga el excel instalado o si no importa.. el tema es que lo que quiero hacer es que en lugar de que abra la planilla la genere y la deje alojada en el disco local o le permita guardarla en una ubicacion. como hago eso? por favor si alguien sabe le agradeceria., el que quiera ver el codigo publique el email asi se lo paso. Gracias.
Hola, el ejemplo está bueno pero lo que realente me haría falta saber es como insertar un fila de la tabla con sus valores en una posicion determinada.
Hola Paxti, gracias por facilitarme el trabajo, sencillo,facíl y sobre todo con muy pocas líneas, pero ahora tengo otro problema a la hora de tratar las hojas lo hace por columnas y en mi caso, tengo columnas que las celdas puedes se númericas y con fórmulas, y texto; sabes si hay alguna posiblidad de que me respete a nivel de celda el contenido, se que en java se utilizan la dll poi-2.5.1-dev-20040708.dll pero para VS 2008?
A mi no me marca ningun error solo que ahor quiero saber donde guardo el archivo d excel y en que momento se llama, y en que parte pego el codigo que proporcionas … 🙁
Aqui tengo otra forma de llamar un archivo de exel pero esta algo confuso cuando se abre si alguien sabe corregirlo le agradecere me envie la respuesta por cierto la parte de myRange1 nombre asi en exel la columna si le ponen otro nombre solo cambien ese parte.
Este codigo se pega en pageLoad para lo que no saben ok.
String sConnectionString = «Provider=Microsoft.Jet.OLEDB.4.0;» + «Data Source=» + «E:\\» + «Imagenes_SIPCE» + «\\» + «ExcelData.xls» + «;» + «Extended Properties=Excel 8.0;»;
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand objCmdSelect =new OleDbCommand(«SELECT * FROM myRange1», objConn);
//OleDbCommand objCmdSelect1 = new OleDbCommand(«SELECT * FROM myRange2», objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();
objAdapter1.Fill (objDataset1, «XLData»);
DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
DataGrid1.DataBind();
objConn.Close();
Cotizacion_Madera();
ESPERO LES SIRVA
String sConnectionString = «Provider=Microsoft.Jet.OLEDB.4.0;» + «Data Source=» + «E:\\» + «Imagenes_SIPCE» + «\\» + «ExcelData.xls» + «;» + «Extended Properties=Excel 8.0;»;
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand objCmdSelect =new OleDbCommand(«SELECT * FROM myRange1», objConn);
//OleDbCommand objCmdSelect1 = new OleDbCommand(«SELECT * FROM myRange2», objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();
objAdapter1.Fill (objDataset1, «XLData»);
DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
DataGrid1.DataBind();
objConn.Close();
Cotizacion_Madera();
Muy bueno el codigo, me esta ayudando a hacer un par de cosas. Se te ocurre como podria pasar un grafico que tengo ya calculado en excel como foto a una carpeta??(desde csharp claro)