Category / Section
How to Dynamically Add Cell Template in JavaScript Spreadsheet?
6 mins read
This Knowledge Base explains the way to add cell templates dynamically in JavaScript Spreadsheet using ng-template in Angular. You can use “ng-template” support of Angular and “template” support of Spreadsheet to achieve this requirement. In the below solution we have dynamically added Button, CheckBox, TextBox, DropDownList, MultiSelect components as cell template in Spreadsheet. Similarly you can use your required components as template in Spreadsheet.
[app.component.html]
<div style="height: 500px;">
<div id='container'>
<div id='dialog'></div>
</div>
<ejs-spreadsheet #spreadsheet id="spreadsheet" (created)="created()"
(contextMenuBeforeOpen)="contextMenuBeforeOpen()" (contextMenuItemSelect)="contextMenuItemSelect($event)"
[height]="700">
<e-sheets>
<e-sheet name="Registration Form">
<e-columns>
<e-column [index]=1 [width]=190></e-column>
<e-column [width]=350></e-column>
</e-columns>
<e-ranges>
<e-range address="A1:CV200">
<ng-template #template let-data>
<ng-container *ngIf="data.template">
<ejs-dropdownlist *ngIf="data.template === 'dropDownTemplate'; else elseBlock"
[dataSource]="data.dataSource" [value]="data.templateValue"
(select)="dropDownSelect($event)"></ejs-dropdownlist>
<ng-template #elseBlock>
<ejs-multiselect *ngIf="data.template === 'multiSelectTemplate'; else elseBlock"
[dataSource]="data.dataSource" [value]="data.templateValue"
[showClearButton]="false" (change)="multiSelect($event)"></ejs-multiselect>
<ng-template #elseBlock>
<ejs-textbox *ngIf="data.template === 'textBoxTemplate'; else elseBlock"
value="{{ data.templateValue }}" placeholder={{data.placeholder}}>
</ejs-textbox>
<ng-template #elseBlock>
<ejs-checkbox *ngIf="data.template === 'checkBoxTemplate'; else elseBlock"
checked="{{ data.templateValue }}" [label]="data.label"></ejs-checkbox>
<ng-template #elseBlock>
<ejs-radiobutton
*ngIf="data.template === 'radioButtonTemplate'; else elseBlock"
[checked]="data.templateValue[0]" [label]="data.label[0]"
[name]="data.name"> </ejs-radiobutton>
<ejs-radiobutton *ngIf="data.template === 'radioButtonTemplate'"
[checked]="data.templateValue[1]" [label]="data.label[1]"
[name]="data.name">
</ejs-radiobutton>
<ng-template #elseBlock>
<button ejs-button *ngIf="data.template === 'buttonTemplate'"
[content]="data.templateValue"></button>
</ng-template>
</ng-template>
</ng-template>
</ng-template>
</ng-template>
</ng-container>
</ng-template>
</e-range>
</e-ranges>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
<div>
<ejs-dialog [visible]="ddDialog" id='dialog' #template1 showCloseIcon='true' [height]='height'
[target]='targetElement' width='400px' (close)="ddDialogClose()" (open)="open()" [header]='dialogHeader'>
<ng-template #content>
<div class="dialogContent">
<input id="inVal" class="e-input" type="text" placeholder="Enter value!"/>
<button id="sendButton" class="e-control e-btn e-primary" data-ripple="true" (click)="btnClick()">Update</button>
</div>
</ng-template>
</ejs-dialog>
</div>
</div>
[app.component.ts]
import { Component, ViewChild, ChangeDetectionStrategy, ViewEncapsulation, OnInit
} from '@angular/core';
import { Spreadsheet } from '@syncfusion/ej2-spreadsheet';
import { Custom } from './custom.script';
@Component({
selector: 'app-root',
templateUrl: './app.component.html',
styleUrls: ['./app.component.css'],
changeDetection: ChangeDetectionStrategy.OnPush,
encapsulation: ViewEncapsulation.None
})
export class AppComponent implements OnInit {
title = 'SStemplate';
static myapp;
ngOnInit() {
AppComponent.myapp = this;
}
@ViewChild('spreadsheet')
spreadsheetObj: Spreadsheet;
public ddDialog: boolean;
created() {
Custom.prototype.created();
}
contextMenuItemSelect(args) {
Custom.prototype.contextMenuItemSelect(args);
}
contextMenuBeforeOpen() {
Custom.prototype.contextMenuBeforeOpen();
}
btnClick() {
Custom.prototype.btnClick();
}
open() {
Custom.prototype.open();
}
ddDialogClose() {
Custom.prototype.ddDialogClose();
}
multiSelect(args: any) {
Custom.prototype.multiSelect(args);
}
dropDownSelect(args: any) {
Custom.prototype.dropDownSelect(args);
}
}
[custom.script.ts]
import { AppComponent } from './app.component';
import {
getRangeIndexes,
getCellIndexes,
getCell
} from '@syncfusion/ej2-spreadsheet';
import { closest } from '@syncfusion/ej2-base';
export class Custom {
spreadsheetObj;
public targetElement: HTMLElement;
public height: string = '150px';
public ddData: string[];
public ddDialog: boolean;
public isDataUpdate: boolean;
public isLabelUpdate: boolean;
public inputValue: string = '';
public dialogHeader: string = '';
public isPlaceholderUpdate: boolean;
public grpcount: number = 0;
public ssElement: Element;
public experience: string[] = [
'0 - 1 year',
'1 - 3 years',
'3 - 5 years',
'5 - 10 years'
];
public languages: string[] = ['JAVA', 'C#', 'SQL'];
created() {
this.spreadsheetObj = AppComponent.myapp.spreadsheetObj;
var ribbonTabItem = [
{
header: { text: 'Template' },
content: [
{ text: 'Add Dropdown', click: this.addDropDown.bind(this) },
{ text: 'Add MultiSelect', click: this.addMultiSelect.bind(this) },
{ text: 'Add TextBox', click: this.addTextBox.bind(this) },
{ text: 'Add CheckBox', click: this.addCheckBox.bind(this) },
{ text: 'Add RadioButton', click: this.addRadioButton.bind(this) },
{ text: 'Add Button', click: this.addButton.bind(this) }
]
}
];
this.spreadsheetObj.addRibbonTabs(ribbonTabItem);
let _this = this;
this.ssElement = this.spreadsheetObj.element;
this.ssElement.addEventListener('keydown', function(event) {
if (
event.code == 'ArrowRight' ||
event.code == 'ArrowLeft' ||
event.code == 'ArrowDown' ||
event.code == 'ArrowUp' ||
event.code == 'Tab' ||
(event.code == 'Tab' &&
(event.code == 'ShiftLeft' || event.code == 'ShiftLeft'))
) {
if (event.target.classList.contains('e-textbox'))
event.target.parentElement.parentElement.parentElement.focus();
} else {
_this.spreadsheetObj;
const activeCell: string = _this.spreadsheetObj.getActiveSheet()
.activeCell;
let sheet = _this.spreadsheetObj.getActiveSheet();
let cell = getCellIndexes(sheet.activeCell);
let cellElement = _this.spreadsheetObj.getCell(cell[0], cell[1]);
if (
cellElement.firstElementChild &&
cellElement.firstElementChild.tagName == 'EJS-TEXTBOX'
) {
(cellElement.firstElementChild.firstElementChild
.firstElementChild as HTMLElement).focus();
if (event.code == 'Delete') {
(cellElement.firstElementChild.firstElementChild
.firstElementChild as HTMLInputElement).value = null;
}
}
}
});
}
addDropDown() {
(this.spreadsheetObj as any).updateCell(
{ template: 'dropDownTemplate', dataSource: ['0-1', '1-2'] },
this.spreadsheetObj.getActiveSheet().activeCell
);
let sheet = this.spreadsheetObj.getActiveSheet();
let cell = getCellIndexes(sheet.activeCell);
let row = this.spreadsheetObj.getRow(cell[0]);
if (row.offsetHeight <= 36) {
this.spreadsheetObj.setRowHeight(
36,
getRangeIndexes(this.spreadsheetObj.getActiveSheet().activeCell)[0]
);
} else {
this.spreadsheetObj.setRowHeight(
row.offsetHeight,
getRangeIndexes(this.spreadsheetObj.getActiveSheet().activeCell)[0]
);
}
}
addButton() {
(this.spreadsheetObj as any).updateCell(
{ template: 'buttonTemplate', templateValue: 'add' },
this.spreadsheetObj.getActiveSheet().activeCell
);
let sheet = this.spreadsheetObj.getActiveSheet();
let cell = getCellIndexes(sheet.activeCell);
let row = this.spreadsheetObj.getRow(cell[0]);
if (row.offsetHeight <= 29) {
this.spreadsheetObj.setRowHeight(
29,
getRangeIndexes(this.spreadsheetObj.getActiveSheet().activeCell)[0]
);
} else {
this.spreadsheetObj.setRowHeight(
row.offsetHeight,
getRangeIndexes(this.spreadsheetObj.getActiveSheet().activeCell)[0]
);
}
}
addTextBox() {
(this.spreadsheetObj as any).updateCell(
{
template: 'textBoxTemplate',
templateValue: 'Test',
placeholder: 'value'
},
this.spreadsheetObj.getActiveSheet().activeCell
);
this.spreadsheetObj.setRowHeight(
38,
getRangeIndexes(this.spreadsheetObj.getActiveSheet().activeCell)[0]
);
}
addCheckBox() {
(this.spreadsheetObj as any).updateCell(
{ template: 'checkBoxTemplate', templateValue: true, label: 'check' },
this.spreadsheetObj.getActiveSheet().activeCell
);
}
addRadioButton() {
(this.spreadsheetObj as any).updateCell(
{
template: 'radioButtonTemplate',
templateValue: [true, false],
label: ['Option 1', 'Option 2'],
name: 'group' + this.grpcount
},
this.spreadsheetObj.getActiveSheet().activeCell
);
this.grpcount = this.grpcount + 1;
}
addMultiSelect() {
const activeCell: string = this.spreadsheetObj.getActiveSheet().activeCell;
(this.spreadsheetObj as any).updateCell(
{ template: 'multiSelectTemplate', dataSource: ['C', 'C++', 'Java'] },
activeCell
);
let sheet = this.spreadsheetObj.getActiveSheet();
let cell = getCellIndexes(sheet.activeCell);
let row = this.spreadsheetObj.getRow(cell[0]);
let cellElement = this.spreadsheetObj.getCell(cell[0], cell[1]);
if (row.offsetHeight <= 36) {
this.spreadsheetObj.setRowHeight(36, getRangeIndexes(activeCell)[0]);
} else {
this.spreadsheetObj.setRowHeight(
row.offsetHeight,
getRangeIndexes(activeCell)[0]
);
}
}
dropDownSelect(args: any) {
const dropDownId: string = closest(args.e.target, '.e-popup').id.split(
'_popup'
)[0];
const ddl: Element = this.spreadsheetObj.element.querySelector(
'#' + dropDownId
);
const colIndex: number =
parseInt(ddl.parentElement.getAttribute('aria-colindex')) - 1;
const rowIndex: number =
parseInt(ddl.parentElement.parentElement.getAttribute('aria-rowindex')) -
1;
(this.spreadsheetObj.getActiveSheet().rows[rowIndex].cells[
colIndex
] as any).templateValue = args.itemData.value;
}
multiSelect(args: any) {
const colIndex: number =
parseInt(args.element.parentElement.getAttribute('aria-colindex')) - 1;
const rowIndex: number =
parseInt(
args.element.parentElement.parentElement.getAttribute('aria-rowindex')
) - 1;
(this.spreadsheetObj.getActiveSheet().rows[rowIndex].cells[
colIndex
] as any).templateValue = args.value;
}
contextMenuBeforeOpen() {
// To add context menu items.
let sheet = this.spreadsheetObj.getActiveSheet();
let cell = getCellIndexes(sheet.activeCell);
let value = getCell(cell[0], cell[1], sheet) as any;
if (value && value.template) {
this.spreadsheetObj.addContextMenuItems(
[{ text: 'Edit Template' }],
'Paste Special',
false
); //To pass the items, Item before / after that the element to be inserted, Set false if the items need to be inserted before the text.
}
}
contextMenuItemSelect(args) {
if ((args.item.text = 'Edit Template')) {
AppComponent.myapp.ddDialog = true;
}
}
ddDialogClose() {
AppComponent.myapp.ddDialog = false;
}
btnClick() {
let enteredVal: HTMLInputElement = document.getElementById(
'inVal'
) as HTMLInputElement;
if (this.isDataUpdate) {
(this.spreadsheetObj as any).updateCell(
{ dataSource: enteredVal.value.split(',') },
this.spreadsheetObj.getActiveSheet().activeCell
);
} else if (this.isLabelUpdate) {
(this.spreadsheetObj as any).updateCell(
{ label: enteredVal.value },
this.spreadsheetObj.getActiveSheet().activeCell
);
} else if (this.isPlaceholderUpdate) {
(this.spreadsheetObj as any).updateCell(
{ placeholder: enteredVal.value },
this.spreadsheetObj.getActiveSheet().activeCell
);
}
enteredVal.value = null;
AppComponent.myapp.ddDialog = this.isDataUpdate = this.isLabelUpdate = this.isPlaceholderUpdate = false;
}
open() {
let _this = this;
let sheet = this.spreadsheetObj.getActiveSheet();
let cell = getCellIndexes(sheet.activeCell);
let value = getCell(cell[0], cell[1], sheet) as any;
let enteredVal: HTMLInputElement = document.getElementById(
'inVal'
) as HTMLInputElement;
if (enteredVal) {
enteredVal.addEventListener('keydown', function(event) {
if (event.code == 'Enter') {
_this.btnClick();
}
});
}
switch (value.template) {
case 'dropDownTemplate':
case 'multiSelectTemplate':
this.dialogHeader = 'Edit DataSource';
enteredVal.value =
value.dataSource != null ? value.dataSource.toString() : '';
value.dataSource = null;
this.isDataUpdate = true;
break;
case 'checkBoxTemplate':
case 'radioButtonTemplate':
enteredVal.value = value.label;
this.dialogHeader = 'Edit Label';
this.isLabelUpdate = true;
break;
case 'textBoxTemplate':
enteredVal.value = value.placeholder;
this.dialogHeader = 'Edit Placeholder';
this.isPlaceholderUpdate = true;
break;
}
}
}
Screenshot:
Sample: https://stackblitz.com/edit/angular-opugvx-orzhwb?file=app.component.html
Please refer the below UG Documentation link for information.
https://ej2.syncfusion.com/angular/documentation/spreadsheet/getting-started/
Conclusion
I hope you enjoyed learning about how to dynamically add cell template in JavaScript Spreadsheet.
You can refer to our JavaScript Spreadsheet page to know about its other groundbreaking feature representations. You can also explore our JavaScript Spreadsheet Documentation to understand how to manipulate data.
For current customers you can check out on our JavaScript components from the License and Download page. If you are new to Syncfusion, you can try our 30-day free trial to check out our JavaScript Spreadsheet and other JavaScript components.
If you have any queries or require clarifications, please let us know in the comment section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
