Agregar y Buscar Datos en Excel C# - VB.Net

jueves, 5 de julio de 2012

Algunas veces como programadores nos toca manejar datos de excel en nuestras aplicaciones, pues ahora veremos como hacerlo usando ado.net y un poco de linq.

En este articulo trataremos los siguientes puntos.
Cargar datos de excel en un datagriedview.
Agregar datos a excel.
Buscar registros usando linq.
Usar linq sobre un datatable

Algo importante de aclarar es que para este ejemplo uso un archivo de excel .xls si quieren hacerlo con archivos .xlsx revisar las distintas cadenas de conexión Connection strings for Excel 2007

El ejemplo esta desarrollado con visual estudio 2010 en c# y vb.net.

Nuestro archivo excel llamado DatosExcel.xls tiene lo siguientes datos.

DatosExcel

Y nuestra aplicacion tendrá el siguiente diseño.

Diseño

Las columnas del datagriedview están definidas en tiempo de diseño.

Columnas

Una vez explicado nuestro diseño veamos el código de nuestro formulario.

Lo primero sera agregar el siguiente namespace.
using System.Data.OleDb;

Imports System.Data.OleDb

Ahora veamos como recuperar los datos de excel y mostrarlos en nuestro datagriedview.
string cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DatosExcel.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=0'";
    public DataTable Datos()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Id");
        dt.Columns.Add("Nombre");
        dt.Columns.Add("Sueldo", typeof(int));
        using (OleDbConnection cnn = new OleDbConnection(cn))
        {
            string sql = "SELECT *FROM [Hoja1$]";

            OleDbCommand command = new OleDbCommand(sql, cnn);

            OleDbDataAdapter da = new OleDbDataAdapter(command);

            da.Fill(dt);
            return dt;
        }
    }

    public void CargarDatos()
    {
        dataGridView1.AutoGenerateColumns = false;
        dataGridView1.DataSource = Datos();
    }

Public Function Datos() As DataTable
    Dim dt As New DataTable()
    dt.Columns.Add("Id")
    dt.Columns.Add("Nombre")
    dt.Columns.Add("Sueldo", GetType(Integer))
    Using cnn As New OleDbConnection(cn)
        Dim sql As String = "SELECT * FROM [Hoja1$]"

        Dim command As New OleDbCommand(sql, cnn)

        Dim da As New OleDbDataAdapter(command)

        da.Fill(dt)
        Return dt
    End Using
End Function
Public Sub CargarDatos()
    dataGridView1.AutoGenerateColumns = False
    dataGridView1.DataSource = Datos()
End Sub

Primeramente se establece la cadena de conexión de nuestro archivo a nivel de clase, y luego cree un método que retornara un datatable con los datos y como pueden notar se recuperan los datos de nuestro archivo excel de igual manera como se aria con una bd, algo importante que hay que mencionar es que en la consulta select tenemos [Hoja1$] donde Hoja1 es el nombre de nuestra hoja de excel.
Por ultimo tenemos el método CargarDatos donde lo único que se hace es mostrar los datos que recuperamos en nuestro datagriedview.

Ahora veamos como agregar registros a nuestro archivo excel.
using (OleDbConnection cnn = new OleDbConnection(cn))
        {
            cnn.Open();
            using (OleDbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO [Hoja1$] (Id,Nombre,Sueldo) values(@id,@nom,@suel)";
                cmd.Parameters.AddWithValue("@id", textBox1.Text);
                cmd.Parameters.AddWithValue("@nom", textBox2.Text);
                cmd.Parameters.AddWithValue("@suel", textBox3.Text);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Datos Ingresados Correctamente...");
            }
            cnn.Close();
        }
        CargarDatos();
        Limpiar();

Using cnn As New OleDbConnection(cn)
        cnn.Open()
        Using cmd As OleDbCommand = cnn.CreateCommand()
            cmd.CommandText = "INSERT INTO [Hoja1$] (Id,Nombre,Sueldo) values(@id,@nom,@suel)"
            cmd.Parameters.AddWithValue("@id", textBox1.Text)
            cmd.Parameters.AddWithValue("@nom", textBox2.Text)
            cmd.Parameters.AddWithValue("@suel", textBox3.Text)
            cmd.ExecuteNonQuery()
            MessageBox.Show("Datos Ingresados Correctamente...")
        End Using
        cnn.Close()
    End Using
    CargarDatos()
    Limpiar()

Hacemos un insert de la misma manera que se haría con un bd solo que en este caso indicamos el nombre de nuestra hoja de excel (Hoja1) y el nombre de las columnas. Luego llamamos al método CargarDatos para que vuelva a cargar el datagriedview con los registros ingresados y por ultimo el método Limpiar que lo único que hace es limpiar los textbox después de insertar un registro.

Ahora veamos como usar linq sobre un datatable para obtener datos filtrados en este caso usaremos el sueldo para obtener los datos filtrados.
           DataTable dt = Datos();
        int sueldo = Convert.ToInt32(txtbuscar.Text);
        var query = from datos in dt.AsEnumerable() where datos.Field<int>("Sueldo") >= sueldo select datos;
        //var query = dt.AsEnumerable().Where(datos=> datos.Field<int>("Sueldo") >= sueldo);
        if(query.Count() > 0)
        {
            dataGridView1.AutoGenerateColumns = false;
            dataGridView1.DataSource = query.CopyToDataTable();
        }
        else
            MessageBox.Show("No Se Encontraron Registros");

       Dim dt As DataTable = Datos()
    Dim sueldo As Integer = Convert.ToInt32(txtbuscar.Text)
    Dim query = From datos In dt.AsEnumerable() Where datos.Field(Of Integer)("Sueldo") >= sueldo Select datos
    'Dim query = dt.AsEnumerable().Where(Function(datos) datos.Field(Of Integer)("Sueldo") >= sueldo)
    If query.Count() > 0 Then
        DataGridView1.AutoGenerateColumns = False
        DataGridView1.DataSource = query.CopyToDataTable()
    Else
        MessageBox.Show("No Se Encontraron Registros")
    End If

Lo primero que hacemos es crear un datatable al cual le asignamos todos los datos de nuestro archivo excel (con el método Datos), luego usamos linq para filtrar los datos del datatable y así ahorrarnos el volver hacer otra consulta a nuestra hoja de excel.
var query = from datos in dt.AsEnumerable() where datos.Field<int>("Sueldo") >= sueldo select datos;

Dim query = From datos In dt.AsEnumerable() Where datos.Field(Of Integer)("Sueldo") >= sueldo Select datos

Usamos la columna sueldo para filtrar los datos, lo que hacemos con linq es recuperar los registros que sean mayores o iguales al sueldo digitado en el textbox(txtbuscar).
Lo que aparece como comentario es otra manera de obtener los registros filtrados usando expresión lambda.

Datos

Filtro


Eso es todo espero les sirva de algo saludos desde El Salvador.

Descargar Proyecto C#

Descargar Proyecto VB.Net

17 comentarios:

luis de la cruz dijo...

unas preguntas cuando se inserta el valor tengo que ir al excel para guardarlo, hice una prueba y cuando fui al excel estaba cuando compile de nuevo no estaba en el excel.

usando tus códigos de insertar tengo un error en executenonquery
es (Data type mismatch in criteria expression.)

Cristian Torres dijo...

Hola contestándote la primera pregunta eso se debe a que quizás tienes el archivo excel dentro del proyecto y cada vez que compilas el archivo se vuelve a copiar al a carpeta bin, lo que tienes que hacer es poner el archivo excel en otra carpeta ejemplo en disco c y cambias la ruta en la cadena de conexión, con respecto a la segunda pregunta algo estas haciendo mal porque los códigos de este ejemplo funcionan correctamente.

Estiven dijo...

Cuando llega a esta linea cmd.ExecuteNonQuery(); sale error de que La operación debe usar una consulta actualizable...Que debo hacer??

Cristian Torres dijo...

Que raro he bajado el código y lo e ejecutado sin ningún problema, que versión de vs estas usando?

DALR dijo...

En la opción buscar datos, en su ejemplo esta con datos numéricos, en el caso de buscar los nombres ¿como seria?

Cristian Torres dijo...

Pues solo usas datos string

string nombre = txtnombre.Text;
var query = from datos in dt.AsEnumerable() where datos.Field("Nombres") == nombre select datos;

DALR dijo...

Cristian muchísimas gracias, por su ayuda y su blog, me han servido de mucho.

Cristian Torres dijo...

De nada y me alegro que el blog sea de ayuda.

Saludos.

Guidex dijo...

Gracias, me ahorraste parte del trabajo, voy a ver si es compatible en WPF

Andrés dijo...

Wow, no sabes cuánto te agradezco :)

Cristian Torres dijo...

Hola.
De nada y espero te sirva.
Saludos.

Cristian Torres dijo...

Hola.
Me alegro que te haya servido.
Saludos.

Anónimo dijo...

@Estiven Cuando copias en otra ruta el archivo .xls fijarse en los permisos de escritura, ya que al insertar y si no tiene los permisos de escritura aparece el error: system.data.oledb.oledbexception (0x80004005) Error : "Operation must use an updateable query".

Por otra parte Cristian Gracias por la info.

Saludos.

Ignacio.

Anónimo dijo...

Gracias Man...... Es informacion de mucha utilidad

Anónimo dijo...

Me ha servido mucho tu publicación, pero me gustaría saber si me puedes ayudar con otra cosa, quiero buscar el nombre, y guardar el sueldo de esa persona en una variable. De antemano muchas gracias.

Cristian Torres dijo...

Hola.
Claro puedes hacerlo con linq.
int myvariable

var query = from datos in dt.AsEnumerable() where datos.Field("Nombre") == textbox1.Text
select new
{
Sueldo = datos.Field("Sueldo"),
Nombre = datos.Field("Nombre")
};
foreach(var d in query)
{
d.Sueldo = myvariable
}

Como veras lo que tienes que hacer es recorrer la query y guardar el dato de la propiedad que definiste en la consulta en la variable.

Saludos.

Anónimo dijo...

Muchas gracias por haber respondido mi duda, solo que sigo teniendo un problema en la ultima linea me marca error en
d.Sueldo = myvariable
específicamente en d.Sueldo me dice que

No se puede asignar la propiedad o el indizador AnonimusType#7.Sueldo(es de solo lectura).

Te dejo mi correo me gustaría ponerme en contacto contigo. ob3d_8a@hotmail.com

Publicar un comentario en la entrada

Nota: solo los miembros de este blog pueden publicar comentarios.