How to prevent the custom date (DD-MMM) format conversion in TypeScript Spreadsheet while loading data from the dataSource property

3 mins read

This knowledge base explains how to skip the custom date (DD-MMM) format conversion while loading data from dataSource property in TypeScript Spreadsheet. Use updateCell method to achieve this requirement. By iterating the used range values, apply the Text format for custom date format (DD-MMM) applied cells.

[HTML]

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

[TS]

import {
 CellModel,
 SheetModel,
 Spreadsheet,
 UsedRangeModel
} from '@syncfusion/ej2-spreadsheet';
import * as dataSource from './default-data.json';
import {
 getCellAddress,
 getFormatFromType,
 getCell
} from '@syncfusion/ej2-spreadsheet';

let spreadsheet: Spreadsheet = new Spreadsheet({
 sheets: [
   {
     name: 'Car Sales Report',
     ranges: [{ dataSource: (dataSource as any).defaultData }],
   },
 ],
 created: (): void => {
   // Applies cell and number formatting to specified range of the active sheet
   spreadsheet.cellFormat(
     { fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' },
     'A1:F1'
   );
   let sheet: SheetModel = spreadsheet.getActiveSheet();
   //Get the used range of data loaded.
   let usedRange: UsedRangeModel = sheet.usedRange;
   let cell: CellModel;
   let reqData: string;
   // Condition for iterating between the used range.
   for (let i: number = 0; i <= usedRange.rowIndex; i++) {
     for (let j: number = 0; j <= usedRange.colIndex; j++) {
       // Get the cell model.
       cell = sheet.rows[i].cells[j];
       // Condition to check whether the format has been applied to the cell.
       if (cell && cell.format === 'dd-MMM') {
         // Get the address of the format applied cell.
         let address: string = getCellAddress(i, j);
         // Get the header value of the cell contained format.
         let headerValue: string = getCell(0, j, sheet).value;
         // Get the datasource provided.
         let data: object =
           spreadsheet.sheets[sheet.index].ranges[sheet.index].dataSource[
             i - 1
           ];
         // Condition to check whether the header value is present on the fetched data source.
         if (Object.keys(data).includes(headerValue)) {
           reqData = data[headerValue];
           // Update the value to the cell with TEXT format.
           spreadsheet.updateCell(
             { value: reqData, format: getFormatFromType('Text') },
             address
           );
         }
       }
     }
   }
 }
});

spreadsheet.appendTo('#spreadsheet');

Sample Link: https://stackblitz.com/edit/xlvr93-1agfz3?file=index.ts,default-data.json

Screenshot: