How to get the modified data as a key-value pair in a JavaScript Spreadsheet

This knowledge base article explains how to get the modified data as a key-value pair in a JavaScript Spreadsheet.

To begin, retrieve the column header range for processing the header value and store it in a collection to build the modified key-value pair. And then use the getData method to retrieve the values from the used range. Construct the key value object from the returned values and the header value object.

[HTML]

<div class="control-section">
   <button id="customBtn" class="e-btn"> To get data </button>
   <div id="spreadsheet"></div>
</div>

[TS]

import { enableRipple } from '@syncfusion/ej2-base';
enableRipple(true);
import { getRangeAddress, getRangeIndexes, SheetModel, Spreadsheet, UsedRangeModel } from '@syncfusion/ej2-spreadsheet';
import * as dataSource from './default-data.json';

//Initialize Spreadsheet component
let spreadsheet: Spreadsheet = new Spreadsheet({
   sheets: [
       {
           name: 'Car Sales Report',
           ranges: [{ dataSource: (dataSource as any).defaultData }],
           columns: [
               { width: 180 }, { width: 130 }, { width: 130 }, { width: 180 },
               { width: 130 }, { width: 120 }
           ]
       }],
   created: (): void => {
       //Applies cell and number formatting to specified range of the active sheet
       spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:F1');
   }
});

//Render initialized Spreadsheet component
spreadsheet.appendTo('#spreadsheet');

document.getElementById('customBtn').addEventListener('click', getRowDetails);

function getRowDetails() {
   let json: object[] = [];
   let colJson: object[] = [];
   let colObj: object = {};
   let obj: object = {};
   // Get the active sheet
   let sheet: SheetModel = spreadsheet.getActiveSheet();
   // Get the used range indexes.
   let usedRange: UsedRangeModel = sheet.usedRange;
   // get range address
   let selIndex: number[] = [1, 0, usedRange.rowIndex, usedRange.colIndex];
   let range: string = sheet.name + '!' + getRangeAddress(selIndex);
   // get the column header range for processing the header value
   let colRange: string =
       sheet.name + '!' + getRangeAddress([0, 0, 0, usedRange.colIndex]);
   spreadsheet.getData(colRange).then((value) => {
       value.forEach((cell, key) => {
           colObj[key] = cell.value;
           // push the header values with address
           colJson.push(colObj);
           colObj = {};
       });
   });
   spreadsheet.getData(range).then((value) => {
       value.forEach((cell, key) => {
           let indexes: number[] = getRangeIndexes(key);
           if (cell && selIndex[2] >= indexes[0]) {
               // Get the header key for the cell
               let datakey: string =
                   colJson[indexes[1]][
                   getRangeAddress([0, indexes[1], 0, indexes[1]]).split(':')[0]
                   ];
               // construct the object key value pair based on datasource
               obj[datakey] = cell.value;
               if (indexes[1] === selIndex[3]) {
                   // row last index
                   json.push(obj);
                   obj = {};
               }
           }
       });
       console.log(json);
   });
}

Sample Link:

https://stackblitz.com/edit/22oa7g-v5warq?file=index.ts

Output:

For further information about this, see the documentation that follows.

https://ej2.syncfusion.com/documentation/spreadsheet/getting-started

https://ej2.syncfusion.com/documentation/spreadsheet/editing