Programación, Tecnología y Más...

Programación, Tecnología y Más...

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

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

Si quieren donarme para una soda lo pueden hacer aqui.


Publicar un comentario

17 Comentarios

  1. 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.)

    ResponderEliminar
    Respuestas
    1. 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.

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

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

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

    ResponderEliminar
    Respuestas
    1. Pues solo usas datos string

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

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

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

      Saludos.

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

    ResponderEliminar
  5. Wow, no sabes cuánto te agradezco :)

    ResponderEliminar
  6. @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.

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

    ResponderEliminar
  8. 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.

    ResponderEliminar
  9. 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.

    ResponderEliminar
  10. 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

    ResponderEliminar