C# Perform a Search in Excel

Sometimes there is a need to interact with an Excel file through our application. The purpose of this example is to show how this is possible. We will use ADO.Net, namely the OleDb namespace used to manage Microsoft Access files. This namespace provides classes that we can use to interact with data sources from the Excel, Access and other file types. We analyze this code where we find the OleDbConnection and OleDbCommand classes that are the main historical instruments with which we connect to a data source.

private DataSet _ds;
private DataTable _tb;
private BindingSource _bs;

private void Form1Load(object sender, EventArgs e)
{
    _ds = new DataSet();
    _tb = new DataTable();
    _bs = new BindingSource();

    var myConnection = new OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;data source=E:\demo.XLSx;Extended Properties=Excel 12.0;");
    var myCommand = new OleDbDataAdapter("SELECT * FROM [Foglio1$]", myConnection);
    myCommand.Fill(_tb);
    _ds.Tables.Add(_tb);
    _bs.DataSource = _ds.Tables[0];
}

This code will run on a startup application being under the load event of Form1, first declare a DataSet that is null, but a container inside with one or more tables (DataTable) and allows us to work disconnected from the source data.

We declare a DataTable control, consisting of rows and columns where they will end all the information that we read from the data source, in a last control BindingSource, the latter will be used to do the search and display screen information and the search result.

Immediately after using the OleDbConnection class, define the connection string to the data source, it should be called the data provider, the latter varies depending on the version of Excel that we're going to use, in this case being an Excel 2007 file, we will use Microsoft .ACE.OLEDB.12.0, then define the connection string that should correspond to the exact location of the file on disk, note that if the file is not found then it generates a runtime exception of type "File not found exception", then ensure that the file is in the path specified in the connection string.

Afterwards we just need to define the class OledbDataAdapter, representing the controls of a set of data and a database connection used to fill a DataSet and update the data source.

To fill a DataSet, we must start using the Fill method, this command is responsible for filling in the DataSet or DataTable using the Select statement that we set as a parameter in the query class OledbDataAdapter.

At this point, using the Add method, we add a table to the control DataSet and assign the DataSource property of the BindingSource table populated with any information taken from the source data.

After this step we have in memory all the information needed to do a search. Suppose you have a file Excel called demo.xlsx with inside 3 columns, "NOME", "COGNOME" and "MESE".

 

 

We will carry out the search using the BindingSource control, namely the Filter property, let us consider this example code.

private void TextBoxValidated(object sender, EventArgs e)
{
    if (ReferenceEquals(sender, txtNome))
    {
        Trova((TextBox)sender, "NOME");
    }
    else if (ReferenceEquals(sender, txtCognome))
    {
        Trova((TextBox)sender, "COGNOME");
    }
    else if (ReferenceEquals(sender, txtMese))
    {
        Trova((TextBox)sender, "MESE");
    }
}

private void Trova(Control textBox, string nomeColonna)
{
    _bs.Filter = String.Concat(nomeColonna, " ", "LIKE '", textBox.Text, "*'");
    DataGridView1.DataSource = _bs;
}

We manage events Validated TextBox controls txtNome, txtCognome and txtMese, all connected to a Event TextBoxValidated, using the variable sender we go to retrieve the TextBox control and on which ran the event Validated, then calling the method Trova. This method requires two parameters, the first TextBox and that was selected, the second type of research consists of a string of characters, the example and can search by name, last name and Month.

As said, we use the Filter property that gets or sets the expression filter for displaying rows displayed. It is basically a SQL query, but we can also adapt to our needs. To finish the search result it will assign to the DataGridView control using the DataSource property.

To search, you simply type in the text boxes a name, surname or month and the content will be displayed on the control DtaGridView, so if we put the name Carmel and we move to another TextBox then we have this situation.

The following is the complete code.

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;

namespace ExcelTextCS
{
    public partial class Form1 : Form
    {
        private DataSet _ds;
        private DataTable _tb;
        private BindingSource _bs;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1Load(object sender, EventArgs e)
        {
            _ds = new DataSet();
            _tb = new DataTable();
            _bs = new BindingSource();

            var myConnection = new OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;data source=E:\demo.XLSx;Extended Properties=Excel 12.0;");
            var myCommand = new OleDbDataAdapter("SELECT * FROM [Foglio1$]", myConnection);
            myCommand.Fill(_tb);
            _ds.Tables.Add(_tb);
            _bs.DataSource = _ds.Tables[0];
        }

        private void TextBoxValidated(object sender, EventArgs e)
        {
            if (ReferenceEquals(sender, txtNome))
            {
                Trova((TextBox)sender, "NOME");
            }
            else if (ReferenceEquals(sender, txtCognome))
            {
                Trova((TextBox)sender, "COGNOME");
            }
            else if (ReferenceEquals(sender, txtMese))
            {
                Trova((TextBox)sender, "MESE");
            }
        }

        private void Trova(Control textBox, string nomeColonna)
        {
            _bs.Filter = String.Concat(nomeColonna, " ", "LIKE '", textBox.Text, "*'");
            DataGridView1.DataSource = _bs;
        }
    }
}