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

March 22 2022

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.

Document Solutions for Excel (DsExcel, previously GcExcel), .NET edition, 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:

Object to find
Cell position after which the search should begin
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:

The object that needs replacement
The object that will be the replacement
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 DsExcel C# .NET API Library!

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

data

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 DsExcel 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:

data

Download the sample and try this feature yourself!

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

Find and Replace Options

You may find FindOptions and ReplaceOptions classes within the GrapeCity.Documents.Excel namespace. 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, DsExcel 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.