Search results

Templates in JavaScript Spreadsheet control

20 Apr 2021 / 4 minutes to read

Cell Template is used for adding HTML elements into Spreadsheet. You can add the cell template in spreadsheet by using the template property and specify the address using the address property inside the ranges property. You can customize the Html elements similar to Syncfusion components (TextBox, DropDownList, RadioButton, MultiSelect, DatePicker etc) by using the beforeCellRender event. In this demo, Cell template is applied to C2:C9 and instantiated with Html input components like TextBox, RadioButton, TextArea. You need to bind the events to perform any operations through HTML elements or Syncfusion components. Here, we have added change event in to the MultiSelect control, and we have updated the selected data into the spreadsheet cell through that change event.

The following code example describes the above behavior.

Source
Preview
app.ts
index.html
Copied to clipboard
import { TextBox } from '@syncfusion/ej2-inputs';
import { RadioButton } from '@syncfusion/ej2-buttons';
import { DatePicker } from '@syncfusion/ej2-calendars';
import { DropDownList, MultiSelect } from '@syncfusion/ej2-dropdowns';
import { Spreadsheet, CellRenderEventArgs, BeforeSelectEventArgs, getRangeIndexes, ChangeEventArgs } from '@syncfusion/ej2-spreadsheet';

/**
 * Cell template
 */
    //Initialize Spreadsheet component
    let spreadsheet: Spreadsheet = new Spreadsheet({
        showRibbon: false,
        showFormulaBar: false,
        allowOpen: false,
        allowSave: false,
        allowEditing: false,
        selectionSettings: { mode: 'None' },
        scrollSettings: {
            isFinite: true
        },
        sheets: [{
            rowCount: 40,
            showGridLines: false,
            name: 'Registration Form',
            rows: [{
                height: 55,
                cells: [{
                    index: 1,
                    value: 'Interview Registration Form',
                    style: {
                        fontSize: '12pt',
                        fontWeight: 'bold',
                        textAlign: 'center',
                        verticalAlign: 'middle',
                        textDecoration: 'underline'
                    }
                }]
            }, {
                height: 45,
                cells: [{
                    index: 1,
                    value: 'Name:'
                }],
            }, {
                height: 45,
                cells: [{
                    index: 1,
                    value: 'Date of Birth:'
                }]
            }, {
                height: 45,
                cells: [{
                    index: 1,
                    value: 'Gender:'
                }]
            }, {
                height: 45,
                cells: [{
                    index: 1,
                    value: 'Year of Experience:'
                }]
            }, {
                height: 45,
                cells: [{
                    index: 1,
                    value: 'Areas of Interest:'
                }]
            }, {
                height: 45,
                cells: [{
                    index: 1,
                    value: 'Mobile Number:'
                }]
            }, {
                height: 45,
                cells: [{
                    index: 1,
                    value: 'Email:'
                }]
            }, {
                height: 82,
                cells: [{
                    index: 1,
                    value: 'Address:'
                }]
            }],
            columns: [{
                index: 1,
                width: 190
            }, {
                width: 350
            }],
            ranges: [{
                template: '<input />',
                address: 'C2:C3'
            }, {
                template: '<div><input type="radio" name="gender" value="male" /><input type="radio" name="gender" value="female"/></div>',
                address: 'C4'
            }, {
                template: '<input />',
                address: 'C5:C8'
            }, {
                template: '<textarea rows="3"/>',
                address: 'C9'
            }, {
                template: '<button class="e-btn e-flat" style="float:right">Add</button>',
                address: 'C11'
            }]
        }],
        beforeCellRender: (args: CellRenderEventArgs) => {
            //Initializing input components before cell rendering
            if (spreadsheet.activeSheetIndex === 0) {
                let target: HTMLInputElement = args.element.firstElementChild as HTMLInputElement;
                switch (args.address) {
                    case 'B1':
                        (args.element as HTMLTableCellElement).colSpan = 2;
                        break;
                    case 'C2':
                        new TextBox({ placeholder: 'Name' }, target);
                        break;
                    case 'C3':
                        new DatePicker({ placeholder: 'DOB',  }, target);
                        break;
                    case 'C4':
                        new RadioButton({ label: 'Male' }, args.element.firstElementChild.firstElementChild as HTMLInputElement);
                        new RadioButton({ label: 'Female' }, args.element.firstElementChild.lastElementChild as HTMLInputElement);
                        break;
                    case 'C5':
                        let experience: string[] =  ['0 - 1 year', '1 - 3 years', '3 - 5 years', '5 - 10 years'];
                        new DropDownList(
                            { placeholder: 'Experience', dataSource: experience},  target );
                        break;
                    case 'C6':
                        let languages: string[] = ['JAVA', 'C#', 'SQL'];
                        new MultiSelect(
                            { showClearButton: false, placeholder: 'Areas of Interest', dataSource: languages, change: (evt: ChangeEventArgs) => {
                            if (args.cell) {
                                args.cell.value = evt.value;
                            } else {
                                let range: number[] = getRangeIndexes(evt.address);
                                spreadsheet.sheets[spreadsheet.activeSheetIndex].rows[range[0]].cells[range[1]] = { value: evt.value };
                            }
                        } }, target);
                        break;
                    case 'C7':
                        new TextBox({ placeholder: 'Mobile Number' }, target);
                        break;
                    case 'C8':
                        new TextBox({ placeholder: 'Email'}, target);
                        break;
                    case 'C9':
                        new TextBox(null, target);
                        break;
                }
            }
        },
        created: () => {
            //Applies format to specified range
            spreadsheet.cellFormat({ fontWeight: 'bold' }, 'B2:B9');
        },
        beforeSelect: (args: BeforeSelectEventArgs) => {
            //Prevents selection
            args.cancel = true;
        }
    });

spreadsheet.appendTo('#spreadsheet');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <meta name="description" content="Typescript UI Controls" />
        <meta name="author" content="Syncfusion" />
        <link rel="shortcut icon" href="resources/favicon.ico" />
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/ej2-base/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/ej2-inputs/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/ej2-buttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/ej2-lists/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/ej2-navigations/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/ej2-popups/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/ej2-dropdowns/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/ej2-spreadsheet/styles/material.css" rel="stylesheet" />
        <link href="styles.css" rel="stylesheet" />
        <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
        <script src="system.config.js"></script>
</head>

<body>
       <!--Element which is going to render-->
       <div id='loader'>Loading....</div>
       <div id='container'>
       <div id="spreadsheet"></div>
       </div>
</body>

</html>

See Also