Find and Replace in ASP.NET MVC Spreadsheet Control

30 Jul 20267 minutes to read

Find and Replace helps you to search for the target text and replace the found text with alternative text within the sheet or workbook. You can use the allowFindAndReplace property to enable or disable the Find and Replace functionality.

NOTE

  • The default value for allowFindAndReplace property is true.

Find

The Find feature selects cells containing content that matches the search value within the current worksheet or workbook. It is useful when working with large data sources.

To find a value:

  1. Select the Search icon in the ribbon or press Ctrl + F to open the Find dialog.
  2. Enter the value to search for.
  3. Select Find Next or Find Previous to navigate between matching cells.
  4. Open the additional search options to configure the search scope, direction, case sensitivity, or exact-cell matching.

You can also use the find() method to perform the find operation programmatically.

  • The Find dialog provides the following options:
  • Search within: Searches within the current worksheet or the entire workbook. The default scope is the current worksheet.
  • Search by: Searches by rows or columns. The default direction is by rows.
  • Match case: Finds values that match the capitalization of the search value.
  • Match exact cell contents: Finds cells whose complete content exactly matches the search value.

Replace

The Replace feature changes matching cell content within the current worksheet or workbook. The Replace All option changes all matching cells within the selected search scope.

To replace a value:

  1. Press Ctrl + H to open the Find and Replace dialog.
  2. Enter the value to find and the replacement value.
  3. Select Replace to replace the current match.
  4. Select Replace All to replace all matching cells within the selected worksheet or workbook scope.

You can also use the replace() method to perform replace and replace All operations programmatically.

Go to

The Go To feature navigates to a specified cell address in a worksheet.

To navigate to a cell:

  1. Press Ctrl + G to open the Go To dialog.
  2. Enter the required cell address.
  3. Confirm the operation to navigate to the specified cell.

You can also use the goTo() method to navigate to a cell programmatically.

The following example demonstrates how to find values in the Spreadsheet:

  1. Open the Home tab in the ribbon and select the Search icon.
  2. Enter a value in the search box.
  3. Select Next or Previous to navigate between matching cells.
  4. Open the additional search options to customize the search.
@Html.EJS().Spreadsheet("spreadsheet").AllowFindAndReplace(true).Sheets(sheet =>
{
    sheet.Ranges(ranges =>
    {
        ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).Add();
  
    }).Columns(column =>
    {
        column.Width(110).Add();
        column.Width(220).Add();
        column.Width(90).Add();
        column.Width(140).Add();
        column.Width(90).Add();
        column.Width(100).Add();
        column.Width(100).Add();
    }).Add();
}).Render()
public ActionResult Index()
{
    List<object> data = new List<object>()
    {
        new { CustomerName= "Romona Heaslip",  Model= "Taurus",  Color= "Aquamarine",  PaymentMode= "Debit Card",  DeliveryDate= "07/11/2015",  Amount= "8529.22" },
        new { CustomerName= "Clare Batterton",  Model= "Sparrow",  Color= "Pink",  PaymentMode= "Cash On Delivery",  DeliveryDate= "7/13/2016",  Amount= "17866.19" },
        new { CustomerName= "Eamon Traise",  Model= "Grand Cherokee",  Color= "Blue",  PaymentMode= "Net Banking",  DeliveryDate= "09/04/2015",  Amount= "13853.09" },
        new { CustomerName= "Julius Gorner",  Model= "GTO",  Color= "Aquamarine",  PaymentMode= "Credit Card",  DeliveryDate= "12/15/2017",  Amount= "2338.74" },
        new { CustomerName= "Jenna Schoolfield",  Model= "LX",  Color= "Yellow",  PaymentMode= "Credit Card",  DeliveryDate= "10/08/2014",  Amount= "9578.45" },
        new { CustomerName= "Marylynne Harring",  Model= "Catera",  Color= "Green",  PaymentMode= "Cash On Delivery",  DeliveryDate= "7/01/2017",  Amount= "19141.62" },
        new { CustomerName= "Vilhelmina Leipelt",  Model= "7 Series",  Color= "Goldenrod",  PaymentMode= "Credit Card",  DeliveryDate= "12/20/2015",  Amount= "6543.30" },
        new { CustomerName= "Barby Heisler",  Model= "Corvette",  Color= "Red",  PaymentMode= "Credit Card",  DeliveryDate= "11/24/2014",  Amount= "13035.06" },
        new { CustomerName= "Karyn Boik",  Model= "Regal",  Color= "Indigo",  PaymentMode= "Debit Card",  DeliveryDate= "05/12/2014",  Amount= "18488.80" },
        new { CustomerName= "Jeanette Pamplin",  Model= "S4",  Color= "Fuscia",  PaymentMode= "Net Banking",  DeliveryDate= "12/30/2014",  Amount= "12317.04" },
        new { CustomerName= "Cristi Espinos",  Model= "TL",  Color= "Aquamarine",  PaymentMode= "Credit Card",  DeliveryDate= "12/18/2013",  Amount= "6230.13" },
        new { CustomerName= "Issy Humm",  Model= "Club Wagon",  Color= "Pink",  PaymentMode= "Cash On Delivery",  DeliveryDate= "02/02/2015",  Amount= "9709.49" },
        new { CustomerName= "Tuesday Fautly",  Model= "V8 Vantage",  Color= "Crimson",  PaymentMode= "Debit Card",  DeliveryDate= "11/19/2014",  Amount= "9766.10" },
        new { CustomerName= "Rosemaria Thomann",  Model= "Caravan",  Color= "Violet",  PaymentMode= "Net Banking",  DeliveryDate= "02/08/2014",  Amount= "7685.49" },
    };
    ViewBag.DefaultData = data;
    return View();
}

Limitations

  • Undo/redo for Replace All is not supported in this feature.