How to Programmatically Find & Replace in Excel XLSX Using .NET C#

As the world continues to collect data at a continuous and furious pace, the need to be able to find information speedily and simply is more important than ever. When dealing with excel documents, it is especially important when working with large workbooks containing many records and sheets to be able to manage text and data quickly, easily, and accurately.

This is where the simple yet powerful “Find and Replace” is used for fast referencing and revisions to the document, saving time and effort.

GcExcel for .NET offers to make programmatic changes to small or large blocks of text simple, quick, and easy with the Find and Replace feature.

In this article, we'll use this feature to cover the scenarios that follow:

• Lookup specific text
• Find and modify Specific text
• Correct a misspelled word or number in the entire document
• Exchange a word or phrase for other relevant text

Let's explore the feature in detail.

The Approach Behind Find and Replace

Identify the Search Range

To find a particular text, the first step is to identify the range of data where you need to make the necessary updates. You can choose either of the various options for selecting the desired range:

1. Select the specific range, for example, B1:H100 using the IWorkSheet.Range Property

IRange range = worksheet.Range["B1:H100"];

2. Select the used range in the worksheet using the IWorkSheet.GetUsedRange() Method

IRange usedRange = worksheet.GetUsedRange(UsedRangeType.Data);

3. Select all the cells of the worksheet using IRange.Cells Property

IRange fullSheet = worksheet.Cells;

Because excel has many rows (and sheets), we’ve used the IRange.GetUsedRange() method for this blog to fetch only the cells with data.

Lookup Specific Text

Once you have the desired range, you can use the IRange.Find() method. It takes three arguments:

1. Object to find
2. Cell position after which the search should begin
3. Options to configure your search (This is optional)

(see full demo)

Find and Modify Specific Text

When you have the range where you want to make modifications, use the IRange.Replace()method. It takes three arguments:

1. The object that needs replacement
2. The object that will be the replacement
3. Options to configure the search and replacement process (This is an optional argument)

(see full demo)

Use-Case

You are an IT professional working with a high-performing sales organization. The sales director has requested a review of this data to ensure accuracy and compliance with all company policies and procedures.

Typically, this could take hours or even days to analyze and make changes, but this project will be done in no time with the GcExcel C# .NET API Library!

Below is a snapshot of a spreadsheet the company has provided for you to analyze.

The director has asked for the following analysis and changes:

• Find and highlight the payments made using Cash
• Exchange the coded Branch with actual Branch names
• Correct the misspelled part “accessories” in the product line with the correct text

To complete the tasks requested by the director, we’ll make use of the find and replace feature with GcExcel for .NET as illustrated in the code snippet below:

static void Main(string[] args)
{
    Workbook workbook = new Workbook();
    workbook.Open("supermarket_sales.xlsx");

    IRange searchRange = null;
    int countofSheet = workbook.Worksheets.Count;

    #region Highlight Cash Payment

    //Find and highlight the payments made using Cash

    IRange cellWithCashText = null;  //cell to begin search with

    for (int i = 0; i < countofSheet; i++)
    {
        searchRange = workbook.Worksheets[i].GetUsedRange(UsedRangeType.Data);
        cellWithCashText = searchRange.Find("Cash", cellWithCashText, null);
        do
        {
            cellWithCashText.Interior.Color = Color.Gold;            
            cellWithCashText = searchRange.Find("Cash", cellWithCashText, null); //change search cell position to next one
        }
        while (cellWithCashText != null); //iterate until cell with search text is not found
            }
    #endregion


    #region Branch Name Update

    //Exchange the coded Branch with actual Branch names

    IRange cellWithBranch = null;
    FindOptions fo = new FindOptions();
    string[] branches = { "A", "B", "G" };

    for (int i = 0; i < countofSheet; i++)
    {
        searchRange = workbook.Worksheets[i].GetUsedRange(UsedRangeType.Data);
        fo.LookAt = LookAt.Whole;
        foreach (var branch in branches)
        {
            cellWithBranch = searchRange.Find(branch, cellWithBranch, fo);
            do
            {
                switch (cellWithBranch.Value)
                {
                    case "A":
                        cellWithBranch.Value = "Alpha";
                        break;
                    case "B":
                        cellWithBranch.Value = "Beta";
                        break;
                    case "G":
                        cellWithBranch.Value = "Gamma";
                        break;
                    default:
                        break;
                }
                cellWithBranch = searchRange.Find(branch, cellWithBranch, null);
            }
            while (cellWithBranch != null);
        }
    }
    #endregion


    #region Spelling Correction

    //Correct the misspelled part “accesories” in the product line with the correct text. 

    ReplaceOptions ro = new ReplaceOptions();

    for (int i = 0; i < countofSheet; i++)
    {
        searchRange = workbook.Worksheets[i].GetUsedRange(UsedRangeType.Data);
        ro.LookAt = LookAt.Part;                
        searchRange.Replace("accesories", "accessories", ro);                
    }
    #endregion

    workbook.Save("supermarket_sales_updated.xlsx");

}

After running the code above, the resulting Excel file should look like this image:

Download the sample and try this feature yourself!

You can also check out the following demos covering scenarios like the above business requirement:

• How to Lookup specific text
• How to Find and modify particular text
• How to Correct a misspelled word or number in the entire document
• How to Exchange a word or phrase for other relevant text

Find and Replace Options

You may find FindOptions and ReplaceOptions classes within the GrapeCity.Documents.Excelnamespace. They wrap the optional parameters for the respective IRange.Find and IRange.Replace methods.

You can control or manage your search/replace process using the various properties provided by these classes. You can follow the demos to see these properties in action.

Advanced Search Scenarios

Apart from the primary search and replace scenarios, GcExcel also provides advanced searching capabilities such as search comments, formulas, merged cells, regular expressions, etc. You can follow the demos to see them in action.