Looping FindText method from GemBox.Spreadsheet

Question

Took an example from the website and trying to create a loop that would tag certain cells based on their cell content which would be identified through the FindText Method from the Gembox component

My goal is:

find cell with a partial match of the keyword
going to the last column of that row
changing the color of that row to a specific color
keep going down document repeating previous commands
stopping once the document has ended

The search works in a sense of finding the query then doing what I instructed it to do, but it stops after the 1st search result.

Is there a way to loop the search using this method or can I use it and another method to test a cell to see if it has a partial piece of what I'm searching for?

This is the link that I'm basing my knowledge on:
https://www.gemboxsoftware.com/spreadsheet/examples/excel-search/109

Thanks again guys.

Below is me working out how the system works on a 1 query basis I'd like to do this for the whole document

using System;
using System.Drawing;
using System.Text;
using System.IO;
using GemBox.Spreadsheet;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace autoexcel2
{
    class Program
    {
        [STAThread]
        static void Main(string[] args)
        {
            //IF USING PRO PUT YOUR SERIAL BELOW
            SpreadsheetInfo.SetLicense("FREE-lIMITED-KEY");

            ExcelFile ef = ExcelFile.Load("sample.xlsx");

            string searchText = "pharma";
            var ws = ef.Worksheets[0];

            StringBuilder sb = new StringBuilder();

            int row;
            int col;

            ws.Cells.FindText(searchText, false, false, out row, out col);;

            if (row == -1 || col == -1)
            {
                sb.AppendLine("cant find nada");
                Console.WriteLine(sb.ToString());
            }
            else
            {
                ws.Cells[row,5].Style.FillPattern.SetSolid(Color.Aqua);
            }

           ef.Save("done.xlsx");
       }
    }
}

Mario Z · Answer 1 · 2023-03-22T04:40:27.633

Try the following:

var workbook = ExcelFile.Load("sample.xlsx");
var worksheet = workbook.Worksheets[0];
var searchText = "pharma";

foreach (var currnetRow in worksheet.Rows)
{
    int row, col;
    if (currnetRow.Cells.FindText(searchText, false, false, out row, out col))
        currnetRow.AllocatedCells.Last().Style.FillPattern.SetSolid(Color.Aqua);
}

workbook.Save("done.xlsx");

With this, you can find the first occurrence of searched text in the row and then format the row's last cell as needed.

But if you need to format those found cells, then the above might not work for you because a single row could have multiple cells with searched text.

In that case, you could use something like the following:

var workbook = ExcelFile.Load("sample.xlsx");
var worksheet = workbook.Worksheets[0];
var searchText = "pharma";

foreach (var row in worksheet.Rows)
{
    var range = row.Cells.GetSubrangeAbsolute(row.Index, 0, row.Index, row.AllocatedCells.Count);
    while (range.FindText(searchText, out int r, out int c))
    {
        worksheet.Cells[r, c].Style.FillPattern.SetSolid(Color.Aqua);
        range = range.GetSubrangeAbsolute(r, c + 1, r, range.LastColumnIndex);
    }
}

workbook.Save("done.xlsx");

EDIT:

In the newer versions of GemBox.Spreadsheet, there are now CellRange.FindAllText methods that can be used, for example:

foreach (ExcelCell cell in worksheet.Cells.FindAllText(searchText, false, false))
{
    // ...
}

Looping FindText method from GemBox.Spreadsheet

1 Answers1