Blog personal de Fernando F. Gallego

En muchos sitios se explica como insertar filas en excel por OleDb así que voy a poner un fragmento y una breve explicación de cómo se hace porque lo realmente interesante es como corregir el error “La operación debe usar una consulta actualizable” que me surgió a mi y que me costó un montón averiguar dado que no me funcionaban las indicaciones que encontraba en otras páginas.

1
2
3
4
5
6
7

        string file = Server.MapPath(@"pruebaExcel.xls");
        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=0;'";
        System.Data.OleDb.OleDbConnection oCon = new System.Data.OleDb.OleDbConnection(connectionString);
        oCon.Open();
        string q = "INSERT INTO [Hoja1$B1:B1] VALUES (1)";
        int r = new System.Data.OleDb.OleDbCommand(q, oCon).ExecuteNonQuery();
        oCon.Close();

Este código busca la ruta física del archivo de excel en el servidor de ASP.NET para crear la cadena de conexión que nos conectará al archivo de Excel que hemos especificado. Importante el código en negrita IMEX=0 ya que tiene que ver con los tipos de los datos de las columnas y puede dar problemas a la hora de escribir desde código y es el problema que estaba teniendo. Si lo ponemos a uno nos dará un error que dice “Operation must use an updateable query” o “La operación debe usar una consulta actualizable”, para solucionarlo ponemos IMEX=0 o lo omitimos del connection string.

Después creamos la conexión con OleDB usando la connection string que acabamos de crear la abrimos para poder empezar a operar.

La notación sql de Excel y Access difiere un poco a lo que estamos acostumbrados ya que es posible que los nombres de columnas tengan caracteres raros como acentos o espacios, por eso, al decirle en que tabla queremos operar, se lo ponemos entre corchetes, y en excel además ponemos un $ al final para indicarle que hoja del libro vamos a usar.

SELECT * FROM [Hoja1$]

nos devolvería todas las filas de la hoja 1 del libro de excel, salvo que hayamos indicado en el connection string la propiedad HDR=1 con lo cual le indicamos que la primera fila no contiene datos sino los nombre de columnas.
Si solo queremos usar un rango de celdas podemos decirle

SELECT * FROM [Hoja1$A2:H10]

que nos devolvería las filas de la 2 a la 10 y las columnas de la A a la H.

En excel también podemos definir nombres a un conjunto de celdas. Para ello, dentro de Excel, seleccionamos un rango de celdas y vamos al menu Insertar > Nombre > Definir y le damos un nombre. Este nombre también es accesible desde código pudiendo hacer:

INSERT INTO NombreQueHemosDado (col1, col2, col3) VALUES (1,2,3)

Si nuestras columnas tienen nombres raros, hay que ponerlas entre corchetes pero no pasa nada si se los ponemos a todas aunque no tengan nombres raros:

INSERT INTO NombreQueHemosDado ([col1], [col2], [col3]) VALUES (1,2,3)

también es válido.

Aprovecho para recomendar la página connectionstrings.com donde se puede buscar qué cadena de conexión hay que escribir para conectarse a cualquier base de datos y dónde encontré una pista para solucionar el error que tenía.