I have a datagridview which display the data was imported from an excel file. I also have a search box (text box) I want to type in the text box then it filter automatically the data inside the datagridview.

I search around and see people use the data table but I don't know how can I implement that in my program.

Here's my code:

 private void runFileImport()
    {
        Microsoft.Office.Interop.Excel.Application xlApp;
        Microsoft.Office.Interop.Excel.Workbook xlWorkbook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        Microsoft.Office.Interop.Excel.Range xlRange;

        int xlRow;
        string fileName = "";
        xlApp = null;
        xlWorkbook = null;
        xlWorkSheet = null;
        xlRange = null;


        OpenFileDialog ofd = new OpenFileDialog();
        ofd.Filter = "Excel office |*.xls; *xlsx";
        ofd.ShowDialog();
        fileName = ofd.FileName;

        if(fileName != "")
        {
            dgvData.Rows.Clear();
            dgvData.Refresh();

            xlApp = new Microsoft.Office.Interop.Excel.Application();

            try
            {
                xlWorkbook = xlApp.Workbooks.Open(fileName);
                xlWorkSheet = xlWorkbook.Worksheets[1];
                xlRange = xlWorkSheet.UsedRange;

            } catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }

            // Read values from settings
            string startTime = Properties.Settings.Default.startTime;
            bool isHighlightLt = Properties.Settings.Default.highlightLt;
            bool isCalculateLt = Properties.Settings.Default.calculateLt;

            string lt = "";
           
            int i = 0;

            for(xlRow = 2; xlRow <= xlRange.Rows.Count; xlRow  )
            {
                

                if (xlRange.Cells[xlRow, 1].Text != "")
                {

                    string clockIn = xlRange.Cells[xlRow, 3].Text;
                    
                    if(string.IsNullOrEmpty(clockIn) == false)
                    {
                        DateTime work_start = DateTime.Parse(startTime);
                        DateTime employee_start = DateTime.Parse(clockIn);

                        if ((employee_start > work_start) && isCalculateLt)
                        {
                            lt = employee_start.Subtract(work_start).ToString().Substring(0, 5);
                        }
                        else
                        {
                            lt = "";
                            
                        }
                    } else
                    {
                        lt = "";
                    }

                    i  ;
                    dgvData.Rows.Add(i, xlRange.Cells[xlRow, 1].Text, xlRange.Cells[xlRow, 2].Text, xlRange.Cells[xlRow, 3].Text, xlRange.Cells[xlRow, 4].Text, lt);
                 
                }

                var liteRed = System.Drawing.ColorTranslator.FromHtml("#fccaca");

                if (isHighlightLt)
                {
                    foreach (DataGridViewRow myrow in dgvData.Rows)
                    {
                        if (myrow.Cells[5].Value.ToString() != "")
                        {
                            myrow.DefaultCellStyle.BackColor = liteRed;
                        }
                    }
                }
            }
            xlWorkbook.Close();
            xlApp.Quit();
        }


    }

CodePudding user response：

Do it step by step.

Start with putting all of your excel data into a dataset.
Then you create a DataView from the DataSet with something like DataView dView = myDataSet.Tables[0].DefaultView;
You connect the DataGridView to the DataView with something like myDataGridView.DataSource = dView; This DataView can be filtered through RowFilter.