Find and Replace in Angular Spreadsheet component

30 Jul 2021 / 4 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.

The default value for allowFindAndReplace property is true.

Find

Find feature is used to select the matched contents of a cell within the sheet or workbook. It is extremely useful when working with large set of data source.

User Interface:

Find can be done by any of the following ways:

Select the Search icon in the Ribbon toolbar or use Ctrl + F key to open the Find dialog.
Use find Next and find Previous buttons to search the given value in the workbook.
Select the option button in Find dialog to open the Find and Replace dialog. Then, select the below properties for enhanced searching.

Search within: To search the target in a sheet (default) or in an entire workbook.

Search by: It enhance the search, either By Rows (default), or By Columns.

Match case: To find the matched value with case sensitive.

Match exact cell contents: To find the exact matched cell value with entire match cases.

Using find() method to perform find operation.

Replace

Replace feature is used to change the find contents of a cell within sheet or workbook. Replace All is used to change all the matched contents of a cell within sheet or workbook.

User Interface:

Replace can be done by any of the following ways:

Use Ctrl + H key to open the Find and Replace dialog.
Use Replace button to change the found value in sheet or workbook.
Using Replace All button, all the matched criteria can be replaced with find value based on sheet or workbook.
Using replace() method to perform replace operation by passing the argument args.replaceby as replace.
Using replace() method to perform replace all operation by passing the argument args.replaceby as replaceall.

Go to

Go to feature is used to navigate to a specific cell address in the sheet or workbook.

User Interface:

Use Ctrl + G key to open the Go To dialog.
Use goTo() method to perform Go To operation.

In the following sample, searching can be done by following ways:

Select the Home tab in the Ribbon toolbar, and then select the Search icon.
Enter any value in the search textbox.
Select the next (or) previous button to find the entered value in the spreadsheet.
You can have more options to find values by selecting the more options in the search toolbar.

Source

Preview

app.component.ts

app.module.ts

datasource.ts

main.ts

import { Component, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';
import { enableRipple } from '@syncfusion/ej2-base';
import { dataSource1, dataSource2 } from './datasource';

enableRipple(true);

@Component({
selector: 'app-container',
template: `<ejs-spreadsheet #spreadsheet (created)="created()" [showFormulaBar]="false">
            <e-sheets>
              <e-sheet name="Price Details">
                <e-ranges>
                  <e-range [dataSource]="priceData"></e-range>
                </e-ranges>
                <e-columns>
                  <e-column [width]=150></e-column>
                  <e-column [width]=110></e-column>
                  <e-column [width]=110></e-column>
                  <e-column [width]=85></e-column>
                  <e-column [width]=85></e-column>
                  <e-column [width]=85></e-column>
                  <e-column [width]=85></e-column>
                  <e-column [width]=85></e-column>
                </e-columns>
              </e-sheet>
              <e-sheet name="Budget Details">
                <e-ranges>
                  <e-range [dataSource]="budgetData"></e-range>
                </e-ranges>
                <e-columns>
                  <e-column [width]=100></e-column>
                  <e-column [width]=100></e-column>
                  <e-column [width]=100></e-column>
                  <e-column [width]=100></e-column>
                </e-columns>
              </e-sheet>
            </e-sheets>
          </ejs-spreadsheet>`
})
export class AppComponent {
@ViewChild('spreadsheet')
spreadsheetObj: SpreadsheetComponent;

priceData: object[] = dataSource1;

budgetData: object[] = dataSource2;

created() {
    this.spreadsheetObj.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
    this.spreadsheetObj.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'Budget Details!A1:D1');
}
}

import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { SpreadsheetAllModule } from '@syncfusion/ej2-angular-spreadsheet';
import { AppComponent } from './app.component';

/**
 * Module
 */
@NgModule({
    imports: [
        BrowserModule,
        SpreadsheetAllModule
    ],
    declarations: [AppComponent],
    bootstrap: [AppComponent]
})
export class AppModule { }

/**
 * Find and replace data sources
 */
export let dataSource1: Object[] = [
  {
      'Item Name': 'Casual Shoes',
      'Date': '02/14/2019',
      'Time': '11:34:32 AM',
      'Quantity': 10,
      'Price': 20,
      'Amount': '=D2*E2',
      'Discount': 1,
      'Profit': 10
  },
  {
      'Item Name': 'Sports Shoes',
      'Date': '06/11/2019',
      'Time': '05:56:32 AM',
      'Quantity': 20,
      'Price': 30,
      'Amount': '=D3*E3',
      'Discount': 5,
      'Profit': 50
  },
  {
      'Item Name': 'Formal Shoes',
      'Date': '07/27/2019',
      'Time': '03:32:44 AM',
      'Quantity': 20,
      'Price': 15,
      'Amount': '=D4*E4',
      'Discount': 7,
      'Profit': 27
  },
  {
      'Item Name': 'Sandals & Floaters',
      'Date': '11/21/2019',
      'Time': '06:23:54 AM',
      'Quantity': 15,
      'Price': 20,
      'Amount': '=D5*E5',
      'Discount': 11,
      'Profit': 67
  },
  {
      'Item Name': 'Flip- Flops & Slippers',
      'Date': '06/23/2019',
      'Time': '12:43:59 AM',
      'Quantity': 30,
      'Price': 10,
      'Amount': '=D6*E6',
      'Discount': 10,
      'Profit': 70
  },
  {
      'Item Name': 'Sneakers',
      'Date': '07/22/2019',
      'Time': '10:55:53 AM',
      'Quantity': 40,
      'Price': 20,
      'Amount': '=D7*E7',
      'Discount': 13,
      'Profit': 66
  },
  {
      'Item Name': 'Running Shoes',
      'Date': '02/04/2019',
      'Time': '03:44:34 AM',
      'Quantity': 20,
      'Price': 10,
      'Amount': '=D8*E8',
      'Discount': 3,
      'Profit': 14
  },
  {
      'Item Name': 'Loafers',
      'Date': '11/30/2019',
      'Time': '03:12:52 AM',
      'Quantity': 31,
      'Price': 10,
      'Amount': '=D9*E9',
      'Discount': 6,
      'Profit': 29
  },
  {
      'Item Name': 'Cricket Shoes',
      'Date': '07/09/2019',
      'Time': '11:32:14 AM',
      'Quantity': 41,
      'Price': 30,
      'Amount': '=D10*E10',
      'Discount': 12,
      'Profit': 166
  },
  {
      'Item Name': 'T-Shirts',
      'Date': '10/31/2019',
      'Time': '12:01:44 AM',
      'Quantity': 50,
      'Price': 10,
      'Amount': '=D11*E11',
      'Discount': 9,
      'Profit': 55
  }
];

export let dataSource2: Object[] = [
  {
    'Expense Type': 'Housing',
    'Projected Cost': 7000,
    'Actual Cost': 7500,
    'Difference': -500,
  },
  {
    'Expense Type': 'Transportation',
    'Projected Cost': 500,
    'Actual Cost': 500,
    'Difference': 0,
  },
  {
    'Expense Type': 'Insurance',
    'Projected Cost': 1000,
    'Actual Cost': 1000,
    'Difference': 0,
  },
  {
    'Expense Type': 'Food',
    'Projected Cost': 2000,
    'Actual Cost': 1800,
    'Difference': 200,
  },
  {
    'Expense Type': 'Pets',
    'Projected Cost': 300,
    'Actual Cost': 200,
    'Difference': 100,
  },
  {
    'Expense Type': 'Personel Care',
    'Projected Cost': 500,
    'Actual Cost': 500,
    'Difference': 0,
  },
  {
    'Expense Type': 'Loan',
    'Projected Cost': 1000,
    'Actual Cost': 1000,
    'Difference': 0,
  },
  {
    'Expense Type': 'Tax',
    'Projected Cost': 200,
    'Actual Cost': 200,
    'Difference': 0,
  },
  {
    'Expense Type': 'Savings',
    'Projected Cost': 1000,
    'Actual Cost': 900,
    'Difference': 100,
  },
  {
    'Expense Type': 'Total',
    'Projected Cost': 13500,
    'Actual Cost': 13600,
    'Difference': -100,
  }
];

import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { enableProdMode } from '@angular/core';
import { AppModule } from './app.module';

enableProdMode();
platformBrowserDynamic().bootstrapModule(AppModule);

Limitations

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

Note

You can refer to our Angular Spreadsheet feature tour page for its groundbreaking feature representations. You can also explore our Angular Spreadsheet example to knows how to present and manipulate data.

Contents

Find
Replace
Go to
Limitations
Note

Contents

Find
Replace
Go to
Limitations
Note

Please provide additional information

Thank you for your feedback and comments.We will rectify this as soon as possible!