How to apply data validation for particular values present in the cells of an Angular Spreadsheet

This knowledge base explains how to apply data validation for particular values present in the cells of an Angular spreadsheet. You can achieve this requirement by using the addDataValidation method and the created event. Apply the data validation for particular cell values by iterating the used range cells in the created event.

[app.component.html]

<div class="control-section">
   <ejs-spreadsheet #default (created)="created()">
       <e-sheets>
           <e-sheet>
               <e-ranges>
                   <e-range [dataSource]="data"></e-range>
               </e-ranges>
               <e-columns>
                   <e-column [width]=130></e-column>
                   <e-column [width]=130></e-column>
                   <e-column [width]=130></e-column>
               </e-columns>
           </e-sheet>
       </e-sheets>
   </ejs-spreadsheet>
</div>

[app.component.ts]

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { getDefaultData } from './data';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';
import { CellModel, getCell, getCellAddress, SheetModel, UsedRangeModel } from '@syncfusion/ej2-spreadsheet';
import { isNullOrUndefined } from '@syncfusion/ej2-base';

@Component({
 selector: 'app-root',
 templateUrl: 'app.component.html',
 styleUrls: ['app.component.css'],
 encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
 constructor() {}
 @ViewChild('default')
 public spreadsheetObj: SpreadsheetComponent;
 public data: Object[] = getDefaultData();
 created() {
   this.spreadsheetObj.cellFormat(
     { fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' },
     'A1:C1'
   );
   let sheet: SheetModel = this.spreadsheetObj.getActiveSheet();
   let usedRange: UsedRangeModel = sheet.usedRange;
   // Iterate between used range to check whether the value is present or not.
   for (let i: number = 0; i <= usedRange.rowIndex; i++) {
     // Fetch the cells in the row.
     let cells: CellModel[] = sheet.rows[i] ? sheet.rows[i].cells : undefined;
     for (let j: number = 0; j <= usedRange.colIndex; j++) {
       // Fetch the cell model.
       let cell: CellModel = getCell(i, j, sheet);
       // Condition to check whether the cell value is present or not.
       let cellValue: string = isNullOrUndefined(cell)
         ? undefined
         : cell.value;
       if (cells && cellValue === 'Valid') {
         // Get the address of validation applying cell.
         let newAddress: string = getCellAddress(i, j);
         // Add validation to the corresponding cell using addDataValidation method.
         this.spreadsheetObj.addDataValidation(
           { type: 'List', value1: 'Red,Yellow,Green,Blue' },
           newAddress
         );
       }
     }
   }
 }
}

Sample Link:

https://stackblitz.com/edit/angular-bdanxu-gktxdb?file=app.component.ts

Output:

Documentation link:

https://ej2.syncfusion.com/angular/documentation/spreadsheet/cell-range#data-validation

Demo link:

https://ej2.syncfusion.com/angular/demos/#/material3/spreadsheet/data-validation