Help please... C# search at excel file

Abdullah Yassin

I've watched a tutorial on youtube that explain how to search in an excel file but when I search for a specific value in a row I must type at the search box column name = 'value at the row' and the column name must be a single word, not 2 words split with space or I must write column name like '%value%' to get similar results. when I search for a specific value in a row

to search for similar results

column name must be a single word

First: How I can search by writing a specific keyword at any row. Second: How I can load column names at combo box and search by column name and make it optional to select column. Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace excel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void label1_Click(object sender, EventArgs e)
{
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void btnOpen_Click(object sender, EventArgs e)
{
using(OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Workbook|*.xlsx",Multiselect = false})
{
if (ofd.ShowDialog() == DialogResult.OK)
{
Cursor.Current = Cursors.WaitCursor;
DataTable dt = new DataTable();
using(XLWorkbook workbook = new XLWorkbook(ofd.FileName))
{
bool isFirstRow = true;
var rows = workbook.Worksheet(1).RowsUsed();
foreach(var row in rows)
{
if (isFirstRow)
{
foreach (IXLCell cell in row.Cells())
dt.Columns.Add(cell.Value.ToString());
isFirstRow = false;
}
else
{
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells())
dt.Rows[dt.Rows.Count - 1][i++] = cell.Value.ToString();
}
}
dataGridView1.DataSource = dt.DefaultView;
lblTotal.Text = $"Total Records:{dataGridView1.RowCount}";
Cursor.Current = Cursors.Default;
}
}
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
try
{
DataView dv = dataGridView1.DataSource as DataView;
if (dv != null)
dv.RowFilter = txtSearch.Text;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void txtSearch_KeyPress(object sender, KeyPressEventArgs e)
{
if (e.KeyChar == (char)13)
btnSearch.PerformClick();
}
private void label2_Click(object sender, EventArgs e)
{
}
private void txtSearch_TextChanged(object sender, EventArgs e)
{
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
}
}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nikunj Satasiya5y ago
Hi Abdullah Yassin,

You getting this error as white space contains your column name 'Unit Price' So, you have to use square brackets if your column name comes with white space.

Currently, in your case you not taken any dropdown or textbox for search operators like '=', '<', '>' and etc. So I suggest take a separate selection for the same, for the better user experience of the filter.

Here, I explained both ways using only the '=' operator as you used in your code example, and second, I will show how you can create a dynamic filter using search operators like '=', '<', '>'.

Method 1: using split search string by ('=')
DataView dv = dataGridView1.DataSource as DataView;
if (dv != null)
{
    string strSearch = txtSearch.Text;
    if (!string.IsNullOrWhiteSpace(strSearch) && strSearch.Split('=').Count() == 2)
    {
        //split string by separator (=)
        string[] searchcriteria = strSearch.Split('=');
        //check if name of column contain white space or not
        if (Convert.ToString(searchcriteria[0].Trim()).Contains(' '))
        {
            //append square brackets in column name if name of column contain white space
            searchcriteria[0] = "[" + searchcriteria[0].Trim() + "]";
            //join string with separator (=)
            strSearch = string.Join("=", searchcriteria);
        }
        dv.RowFilter = "(" + strSearch + ")";

    }
}
Method 2: using split search string by dynamic selection of search oprator like '=', '<', '>'.
DataView dv = dataGridView1.DataSource as DataView;
if (dv != null)
{
    string strSearch = txtSearch.Text;
    char charSearchOperator = Convert.ToChar(txtSearchOperator.Text);
    if (!string.IsNullOrWhiteSpace(strSearch) && strSearch.Split(charSearchOperator).Count() == 2)
    {
        //split string by separator (=)
        string[] searchcriteria = strSearch.Split(charSearchOperator);
        //check if name of column contain white space or not
        if (Convert.ToString(searchcriteria[0].Trim()).Contains(' '))
        {
            //append square brackets in column name if name of column contain white space
            searchcriteria[0] = "[" + searchcriteria[0].Trim() + "]";
            //join string with separator (=)
            strSearch = string.Join(Convert.ToString(charSearchOperator), searchcriteria);
        }
        dv.RowFilter = "(" + strSearch + ")";
    }
}
To generate a dynamic search string you can modify the given logic and implement it, this is just for your demonstration, I have taken only a single parameter and single condition and try to resolve your issue or syntax error that you getting.
Please let me know if you have any doubts on it or need any help in the same.

I hope this works for you.
Aman Gupta5y ago
Sorry But we are not here to refer other sites for your problem, if you can't post question properly then please don't.
Nikunj Satasiya5y ago
Please, describe your questions or issue that you facing so we can help you.
Mahesh Chand5y ago
Its better to post your clear and detailed question here. I'm not going to other website to read your question because you're too lazy to post it here :)