c# - How to populate a DataGridView based on Excel search results and remove blank headers from the DataGridView?

Question

I have 1 Form with 1 TextBox where an user will type ProductId(FCID), press the enter key and, based on that, I want to populate my DataGridView with the results of a search.

There are 2 problems here:

• How do I filter records from Excel based on user entered FCID?

• While binding the DataSource from Excel, how to remove blank records as shown in the second image?

Code:

private void txtProductId_KeyUp(object sender, KeyEventArgs e)
{
    if (e.KeyCode == Keys.Enter)
    {
        string pathName = txtFilePath.Text;
        string fileName = System.IO.Path.GetFileNameWithoutExtension(txtFilePath.Text);
        DataTable tbContainer = new DataTable();
        string strConn = string.Empty;
        string sheetName = fileName;

        FileInfo file = new FileInfo(pathName);
        if (!file.Exists) { throw new Exception("Error, file doesn't exists!"); }
        string extension = file.Extension;

        switch (extension)
        {
            case ".xls":
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                break;
            case ".xlsx":
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                break;
            default:
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                break;
        }
        OleDbConnection cnnxls = new OleDbConnection(strConn);
        OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), cnnxls);
        oda.Fill(tbContainer);
        grdProductList.DataSource = tbContainer;
        e.Handled = true;
    }
}

I have not worked previously on Windows Forms.

Answer 1

You can specify a Range of cells that contain the Data you want to retrieve. In the form:

[sheet1$[Start Cell]:[End Cell]]

In your case, you can change the query, including only the Cells from B6 to ZZ (which seems to be the range that contains your Data. Adapt it as needed):
Remember to Dispose of the disposable objects you created or declare them in using blocks)

string sheetName = "Sheet1";
string query = $"SELECT * FROM [{sheetName}$B6:ZZ]"
OleDbDataAdapter oda = new OleDbDataAdapter(query, cnnxls); 
//(...)
oda.Dispose();

You can of course specify a Filter:

int fieldID = 204;
//
string query = $"SELECT * FROM [{sheetName}$B6:ZZ] WHERE FCID = {fieldID}"

using (OleDbConnection cnnxls = new OleDbConnection(strConn))
using (OleDbDataAdapter oda = new OleDbDataAdapter(query, cnnxls))
{
    oda.Fill(tbContainer);
    grdProductList.DataSource = tbContainer;
}

or a Range filter:

string query = $"SELECT * FROM [{sheetName}$B6:ZZ] WHERE FCID BETWEEN 204 AND 300"

or, using variable values:

int minValue = 204;
int maxValue = 300;
string query = $"SELECT * FROM [{sheetName}$B6:ZZ] WHERE FCID BETWEEN {minValue} AND {maxValue}"

The .xls format doesn't support a generic Range:

[sheet1$B6:ZZ] will not be accepted.
[sheet1$B6:O65535] is accepted: the data is in the columns range B6:O*.

UPDATE: (specific to this question):

Given the composition of the Excel Sheet, the Field Names need to be specified explicitly, otherwise the resulting DataTable will contain empty columns:

int fieldID = 204;    
string fieldSelector = "[ FCID], [Product Name], [Category], [Sub Category], " +
                       "[Brand], [MRP], [Disc %], [Stock], [Discount Type]";

query = $"SELECT {fieldSelector} FROM [{sheetName}$B6:O65535] WHERE [ FCID] = {fieldID}";