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);

Cazares

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

Galvez

it wont help my friend because the second string value C VALUE2 may exist or not what I mean if I found B VALUE2 and C VALUE2 tougher OR B VALUE2 OR C VALUE2 all these previous string cases will be considered as match.. and if I concatenate the two string it will ignore the last two match – Cazares

@Cazares - I see what you mean. Now it looks a bit better. :) – Galvez

your right my friend the xlXlLookAt.xlPart will Like from SQL but that within same column but if the values are from two or more column will fail ... what I really want is something like string concatenated = string.Concat(oWB.Range["B2"].Value2, " ", oWB.Range["C2"].Value2) with or option between values – Cazares

I tried the List<string> suggestion but till now I am getting some error and things get complicated since this method not that simple gets data – Cazares

btw when I used the string.Concat the string wont work work with xlXlLookAt.xlPart it will also fail example when string B VALUE2 and C VALUE2 is equal to sam dev and it wont find sa dev because it will concatenate the string and look for it within one column not within multiple column I think there should be way to concatenate same column also that comes from excel sheet – Cazares

@Cazares - my understanding is that you need to look at 2 things instead of 3. E.g., let's say that you have Vit, yata and Vityata to look at. Any string, containing Vityata would contain Vit or yata as well as substrings. Thus, checking only whether a string contains Vit and yata makes it enough, you do not need the third check for Vityata. – Galvez

Answer 2

Can you change in the above program , last cell information in the Range to the one mentioned below and try.

Application.get_Range("A1", "D2");

Also you may want to check an example given How to: Programmatically Search for Text in Worksheet Ranges

Athamas

I actually checked the example you suggested before posting here...and the Application.get_Range it same as 'oXL' I have declare it ... post updated to included the declaration Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application(); – Cazares