Searching for cells which contains a specific string

Aspose.Cells Product Family
1

I am working with Aspose.Cell library for DotNet. I am searching for string in entire worksheet and highlight the cell.

FindOptions find = new FindOptions();
find.LookInType = LookInType.Values;
find.LookAtType = LookAtType.Contains;
Cells cell = sheet.Cells;
Cell id = cell.Find("Test", null, find);

Cell.Find return the first cell only which contains the string. I am new to this Library, Kindly guide me how can I get list of Cell which contains the same string.

2

@hashimali,

See the sample code for your reference, you may update the code accordingly for your needs:
e.g
Sample code:

LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);
            Workbook rs = new Workbook("e:\\test2\\Book1.xlsx", loadOptions);
            string searchValue = "Test"
            string replaceValue = "newval";
            //Scan all the worksheets
            foreach (Worksheet s in rs.Worksheets)
            {
                FindOptions opts = new FindOptions();
                opts.LookInType = LookInType.Values;
                opts.LookAtType = LookAtType.Contains;

                Cell cell = null;
                do
                {
                    cell = s.Cells.Find(searchValue, cell, opts);

                    if (cell != null)
                    {
                        string value = cell.StringValue;
                        value = value.Replace(searchValue, replaceValue);
                        cell.PutValue(value);
                    }
                } while (cell != null);
            }
            rs.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.

3

Thanks, that work for me…

4

@hashimali,

Good to know that the suggested code works for your needs. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.