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

3 mins read

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

[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');

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

Screenshot: