Help please... C# search at excel file

Mar 30 2021 7:06 PM
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:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel;  
  4. using System.Data;  
  5. using System.Drawing;  
  6. using System.Linq;  
  7. using System.Text;  
  8. using System.Threading.Tasks;  
  9. using System.Windows.Forms;  
  10.   
  11. namespace excel  
  12. {  
  13.     public partial class Form1 : Form  
  14.     {  
  15.         public Form1()  
  16.         {  
  17.             InitializeComponent();  
  18.         }  
  19.         private void label1_Click(object sender, EventArgs e)  
  20.         {  
  21.         }  
  22.         private void Form1_Load(object sender, EventArgs e)  
  23.         {  
  24.         }  
  25.         private void btnOpen_Click(object sender, EventArgs e)  
  26.         {  
  27.             using(OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Workbook|*.xlsx",Multiselect = false})  
  28.             {  
  29.                 if (ofd.ShowDialog() == DialogResult.OK)  
  30.                 {  
  31.                     Cursor.Current = Cursors.WaitCursor;  
  32.                     DataTable dt = new DataTable();  
  33.                     using(XLWorkbook workbook = new XLWorkbook(ofd.FileName))  
  34.                     {  
  35.                         bool isFirstRow = true;  
  36.                         var rows = workbook.Worksheet(1).RowsUsed();  
  37.                         foreach(var row in rows)  
  38.                         {  
  39.                             if (isFirstRow)  
  40.                             {  
  41.                                 foreach (IXLCell cell in row.Cells())  
  42.                                     dt.Columns.Add(cell.Value.ToString());  
  43.                                 isFirstRow = false;  
  44.                             }  
  45.                             else  
  46.                             {  
  47.                                 dt.Rows.Add();  
  48.                                 int i = 0;  
  49.                                 foreach (IXLCell cell in row.Cells())  
  50.                                     dt.Rows[dt.Rows.Count - 1][i++] = cell.Value.ToString();  
  51.                             }  
  52.                         }  
  53.                         dataGridView1.DataSource = dt.DefaultView;  
  54.                         lblTotal.Text = $"Total Records:{dataGridView1.RowCount}";  
  55.                         Cursor.Current = Cursors.Default;  
  56.                     }  
  57.                 }  
  58.             }  
  59.         }  
  60.         private void btnSearch_Click(object sender, EventArgs e)  
  61.         {  
  62.             try  
  63.             {  
  64.                 DataView dv = dataGridView1.DataSource as DataView;  
  65.                 if (dv != null)  
  66.                     dv.RowFilter = txtSearch.Text;  
  67.             }  
  68.             catch(Exception ex)  
  69.             {  
  70.                 MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);  
  71.             }  
  72.         }  
  73.         private void txtSearch_KeyPress(object sender, KeyPressEventArgs e)  
  74.         {  
  75.             if (e.KeyChar == (char)13)  
  76.                 btnSearch.PerformClick();  
  77.         }  
  78.         private void label2_Click(object sender, EventArgs e)  
  79.         {  
  80.         }  
  81.         private void txtSearch_TextChanged(object sender, EventArgs e)  
  82.         {  
  83.         }  
  84.         private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)  
  85.         {  
  86.         }  
  87.     }  
  88. }

Answers (4)