How to render a date picker for date value cells in JavaScript Spreadsheet

3 mins read

This knowledge base explains how to render a date picker for date value cells in JavaScript Spreadsheet. On created event, bind a function to insert the DatePicker control. Also, check whether the header cell is Date column and then render the DatePicker control to the specified date cells in the beforeCellRender event.

[HTML]

<div id="spreadsheet"></div>

[JS]

//Initialize Spreadsheet component.
let spreadsheet = new ej.spreadsheet.Spreadsheet({
 sheets: [
   {
     name: 'Car Sales Report',
     ranges: [{ dataSource: defaultData }],
     columns: [
       { width: 180 },
       { width: 130 },
       { width: 130 },
       { width: 180 },
       { width: 130 },
       { width: 120 },
     ],
   },
 ],
 created: function () {
   spreadsheet.cellFormat(
     { fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' },
     'A1:F1'
   );
   // Bind the method to update date picker.
   bindDatePicker();
 },
 beforeCellRender: function (args) {
   // Condition to check the sheet index and the rendered cell is not the header.
   if (
     spreadsheet.activeSheetIndex === 0 &&
     !args.element.classList.contains('e-header-cell')
   ) {
     // Render templates dynamically for date valued cells.
     let target = args.element.firstElementChild;
     // Convert the input element as an EJ2 datepicker component.
     new ej.calendars.DatePicker(
       {
         placeholder: 'Delivery Date',
         value: spreadsheet.getDisplayText(args.cell),
         change: onChange,
       },
       target
     );
   }
 },
});

spreadsheet.appendTo('#spreadsheet');

function onChange(args) {
 let sheet = spreadsheet.getActiveSheet();
 let cellvalue = args.value && args.value.toLocaleDateString('en-US');
 // update the changed date picker value in the cell model.
 spreadsheet.updateCell({ value: cellvalue }, sheet.activeCell);
}
function bindDatePicker() {
 //Fetch active sheet.
 let sheet = spreadsheet.getActiveSheet();
 // Fetch used range of the sheet.
 let range = sheet.usedRange;
 let rangeCollection = [];
 // Iteration between the used range to update template on date valued cells.
 for (let i = 0; i <= range.rowIndex; i++) {
   let cells = sheet.rows[i].cells;
   for (let j = 0; j <= range.colIndex; j++) {
     // Condition to check whether the value is date or not.
     if (cells[j] && cells[j].value && cells[j].format == 'mm-dd-yyyy') {
       let address = ejs.spreadsheet.getCellAddress(i, j);
       let ranges = {
         template: '<input />',
         address: address, // Specify the range you want.
       };
       rangeCollection.push(ranges);
     }
   }
 }
 // Update the range collection in which the date picker is rendered.
 spreadsheet.sheets[0].ranges = rangeCollection;
 spreadsheet.resize(); // To refresh the spreadsheet.
}

Sample Link:

https://stackblitz.com/edit/zykkem-r3qwle?file=index.js

Output: