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)
- {
- }
- }
- }