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.


Y nuestra aplicacion tendrá el siguiente diseño.


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


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.





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.