How to use .Find() method with like expression using c#

Question

looking for a string within excel file range with like expression

Example

file excel look like below:

----------------------------------------------------------
 # |     A     |      B      |      C      |      D      |
----------------------------------------------------------
 1 | A VALUE1  |   B VALUE1  |   C VALUE1  |   D VALUE1  |
----------------------------------------------------------
 2 | A VALUE2  |   B VALUE2  |   C VALUE2  |   D VALUE2  |
----------------------------------------------------------

now what I want to do is enter this string B VALUE2 C VALUE2 in TB_Search_Text.Text to search for it

UPDATE

here is some more explanation for the case

Second string value C VALUE2 may exist or not what I mean

if I found B VALUE2 and C VALUE2 together

OR B VALUE2

OR C VALUE2

all these previous string cases will be considered as match.. I cannot concatenate the two string because it will ignore the last two match

for below method it will return string not found so what should I do to make it working ?

    Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook oWB;
    Microsoft.Office.Interop.Excel.Range currentFind = null;
    Microsoft.Office.Interop.Excel.Range firstFind = null;

    Excel.Range oRng = oXL.get_Range("A1", "XFD1048576");

    currentFind = oRng.Find(TB_Search_Text.Text,
                            missing,
                            Excel.XlFindLookIn.xlValues,
                            Excel.XlLookAt.xlPart,
                            Excel.XlSearchOrder.xlByRows,
                            Excel.XlSearchDirection.xlNext,
                            false,
                            missing,
                            missing);

Answer 1

If you are looking for any of the 3 optinons - concatenated or single values, you may simply try the following:

• Read the two values from the workbook and write them to a list in C#. (in the code below I have hardcoded them)
• Then loop within the list, until you do not find something or the list is empty. This is the condition of the loop:

while (currentFind == null & cnt < lookForList.Count)

• At the end print the row and the column to see that you have found something.

---

using System;
using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;

class StartUp
{
    static void Main()
    {
        Excel.Application excel = null;
        excel = new Excel.Application();
        excel.Visible = true;        
        string filePath = @"C:\YourOwnPath\TestWB.xlsx";
        Excel.Workbook wkb = null;
        wkb = Open(excel, filePath);

        string part1 = "some value";
        string part2 = "some other value";
        string part12 = string.Concat(part1, part2);
        List<string> lookForList = new List<string> { part1, part2, part12 };
        Excel.Range currentFind = null;
        Excel.Range searchedRange = excel.get_Range("A1", "XFD1048576");
        int cnt = 0;
        while (currentFind == null & cnt < lookForList.Count)
        {
            //make sure to specify all the parameters you need in .Find()
            currentFind = searchedRange.Find(lookForList[cnt]);
            cnt++;
        }
        if (currentFind!=null)
        {
            Console.WriteLine("Found:");
            Console.WriteLine(currentFind.Column);
            Console.WriteLine(currentFind.Row);
        }        
        wkb.Close(true);
        excel.Quit();
    }

    public static Excel.Workbook Open(Excel.Application excelInstance, 
                            string fileName, bool readOnly = false, bool editable = true, 
                            bool updateLinks = true)
    {
        Excel.Workbook book = excelInstance.Workbooks.Open(
            fileName, updateLinks, readOnly,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing);
        return book;
    }
}

In general, if you want to mimic the Like from SQL, then xlXlLookAt.xlPart would do enough. You even do not need to concatenate the two values you are searching.

---

If you want to look for both with some space, then concatenating them looks like a good idea:

string concatenated = string.Concat(oWB.Range["B2"].Value2, " ", oWB.Range["C2"].Value2)

or

currentFind = oRng.Find(concatenated,
                                            missing,
                                            Excel.XlFindLookIn.xlValues,
                                            Excel.XlLookAt.xlPart,
                                            Excel.XlSearchOrder.xlByRows,
                                            Excel.XlSearchDirection.xlNext,
                                            false,
                                            missing,
                                            missing);

String Concat MSDN