Search text in Excel file through C#

The following code snippet shows how we can search some text in your excel sheet in C#, this is the helpful source code to know that some specified text is exist in the excel worksheet or not.

        private void SearchText()
        {
            string File_name = "D:\\test.xlsx";
            Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook oWB;
            Microsoft.Office.Interop.Excel.Worksheet oSheet;
            try
            {
                object missing = System.Reflection.Missing.Value;
                oWB = oXL.Workbooks.Open(File_name, missing, missing, missing, missing,
                    missing, missing, missing, missing, missing, missing,
                    missing, missing, missing, missing);
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[1];
                Microsoft.Office.Interop.Excel.Range oRng = GetSpecifiedRange("test", oSheet);
                if (oRng != null)
                {
                    MessageBox.Show("Text found, position is Row:" + oRng.Row + " and column:" + oRng.Column);
                }
                else
                {
                    MessageBox.Show("Text is not found");
                }
                oWB.Close(false, missing, missing);
 
                oSheet = null;
                oWB = null;
                oXL.Quit();
            }
            catch (Exception ex)
            {
 
            }
        }

We are using Find() method that Finds specific text in a range and returns a Range object that represents the first cell where that text is found. Below function returns Nothing if no match is found and this method doesn’t affect the selection or the active cell. One more thing you can also use FindNext() and FindPrevious() methods to repeat the search.

        private Microsoft.Office.Interop.Excel.Range GetSpecifiedRange(string matchStr, Microsoft.Office.Interop.Excel.Worksheet objWs)
        {
            object missing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Range currentFind = null;
            Microsoft.Office.Interop.Excel.Range firstFind = null;
            currentFind = objWs.get_Range("A1", "AM100").Find(matchStr, missing,
                           Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
                           Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
                           Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
                           Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, missing, missing);
            return currentFind;
        }

4 thoughts on “Search text in Excel file through C#”

  3. What if I want to retrieve a value from a different cell?
    Example, I’ve found the value I was looking for in row 8 column 3, but I need to get the value of the row 7 column 3. Is this possible? Thanks.

    1. once you get the range of the searched item. you can easily get the another range object that have row decremented by one and same column index.

      start the writing code to do this and let me know if you have any problem..

Comments are closed.