How to customize an Angular Spreadsheet to highlight cells based on details found in the data source

This article explains how to highlight cells based on the details present in the data source in Angular Spreadsheet.

We iterated over the data source object to find the details of the cell address that needed to be highlighted, and highlighted those cells in the spreadsheet by using the cellFormat method based on the data source object.

[app.component.html]

<div class="control-section">
 <ejs-spreadsheet #default="" (created)="created()">
   <e-sheets>
     <e-sheet name="Car Sales Report">
       <e-ranges>
         <e-range [datasource]="data"></e-range>
       </e-ranges>

       <e-columns>
         <e-column [width]="180"></e-column>
         <e-column [width]="130"></e-column>
         <e-column [width]="130"></e-column>
         <e-column [width]="180"></e-column>
         <e-column [width]="130"></e-column>
         <e-column [width]="120"></e-column>
       </e-columns>
     </e-sheet>
   </e-sheets>
 </ejs-spreadsheet>
</div>

[app.component.ts]

import { Component, ViewEncapsulation, Inject, ViewChild } from '@angular/core';
import { getDefaultData } from './data';
import {
 getRangeAddress,
 SpreadsheetComponent,
} from '@syncfusion/ej2-angular-spreadsheet';

@Component({
 selector: 'app-root',
 templateUrl: 'app.component.html',
 styleUrls: ['app.component.css'],
 encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
 @ViewChild('default')
 public spreadsheetObj: SpreadsheetComponent;
 public data: any = getDefaultData();

 created() {
   this.spreadsheetObj.cellFormat(
     { fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' },
     'A1:F1'
   );

   // Logic to highlight the data based on the details present in the data source object.
   let colHeaderObj: string[] = Object.keys(this.data[0]);
   // Get the validation column address to clear the API values otherwise it displayed with [object object] in G column.
   let validationColIndex: string = getRangeAddress([
     0,
     colHeaderObj.length - 1,
     this.data.length, 
     colHeaderObj.length - 1,
   ]);
   // Clear the content on validation details columns
   this.spreadsheetObj.clear({ type: 'Clear All', range: validationColIndex });
   for (let i: number = 0; i < this.data.length; i++) {
     let validationObj: {field: string}[] = this.data[i].ValidationError;
     if (validationObj.length > 0) {
       for (let j: number = 0; j < validationObj.length; j++) {
         let field: string = validationObj[j].field;
         let colIndex: number = colHeaderObj.indexOf(field);
         // Get the highlighted cell address based on key value.
         let cellAddr: string = getRangeAddress([
           i + 1,
           colIndex,
           i + 1,
           colIndex,
         ]);
         // Highlight the cell value with background color.
         this.spreadsheetObj.cellFormat(
           { fontWeight: 'bold', backgroundColor: '#FF0000' },
           cellAddr
         );
       }
     }
   }
 }
}

Sample Link:

Refer to the working sample for additional details and implementation: sample

Output:

Conclusion

We hope you enjoyed learning about how to customize an Angular Spreadsheet to highlight cells based on details found in the data source.

You can refer to our Angular Spreadsheet’s feature tour page to know about its other groundbreaking feature representations and documentations. You can also explore our Angular Spreadsheet example to understand how to present and manipulate data.

For current customers, you can check out our Angular components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our Angular Spreadsheet and other Angular components.If you have any queries or require clarifications, please let us know in comments below. You can also contact us through our support forums, BoldDesk Support, or feedback portal. We are always happy to assist you!