How to get handson table data in json format with column header as key

Question

I have handsontable and I want to get data enter on handsontable cell into server side. I have tried to ran below code but data is not in expected format. I was expecting to get the data in pure json format as column header as key.

Html code

<div class="handsontable" id="example"></div>
<input type="button" name="submit" value="submit" onclick="submitForm()" />

code for creating the handsontable

   $(document).ready(function () {
       $('#example').handsontable({
           startRows: 2,
           startCols: 2,
            rowHeaders: true,
            colHeaders: true,
            contextMenu: true,
       });
   });

code for extracting the information from handsontable

   function submitForm(){
        var $container = $('#example');
        var htContents = JSON.stringify($container.handsontable('getData'));
        alert(htContents);
    }

Currently handsontable has 2 rows and 2 column. Now if I press the button with cell value (1,1)=11,(1,2)=12,(2,1)=21 and (2,2)=22, result I am getting is in alert window

[["11","12"],["21","22"]]

But result I am expecting is

 [{"A":"11","B":"12"},{"A":"21","B":"22"}] 

where A and B is column header.

Duane

Answer 1

For others who didn't discover the answer immediately, see @hakuna1811's comment above that since version 0.20.0 of Handsontable the .getSourceData() call should be used instead if you want to get your data back in the same format as you provided it - for example as an array of objects. It is not clear why the .getData() call's behavior was modified and it is not explained in the related GitHub issue noted in @hakuna1811's comment, but at least we have a working solution - thanks again to @hakuna1811 for the answer - it saved a lot of hunting around!

Reg

Answer 2

That's great that you're expecting that, but that's just not how that function works :P

Here's what you actually want:

For starters, you don't show us where you set the data option. If you look at this fiddle, I use the different notation to generate a Handsontable object which allows me to specify the format of data.

If data is given how I show it, as an array of row Objects, where each Object is in the format you describe, then the hot1.getData() method returns what you expect.

As it stands, I have no idea what data format you're using so either adopt this way of instantiating HOT or show us how you're doing it.

Good luck!

Darbies

when page is render to the user, I am displaying blank on all cells. Even in the blank cell, we need to specify the data format? If so how we can do that? – Duane

kind of, yes. You'll have to specify the data but you can just give them blank values – Darbies

since version 0.20.0, we should use .getSourceData() to get object, instead of .getData, details in github issue – Governor

@Governor you should add this as a separate answer. Took too long for me to find your comment. – Travelled

Answer 3

You need mapping the result. let's assume htContents is variable which contains [["11","12"],["21","22"]]

function buildObject(data) {
    return {
         'A': data[0], 
         'B': data[1]
    };
}
var newResult = htContents.map(buildObject); // newResult must be expected data

Rossner

Answer 4

The getSourceData() method returns the desired format, but will not reflect the correct row and column order as seen on screen. The following typescript code works for me:

protected getVisualTableData(): object[] {
    const tableData = [];
    for (let i = 0; i < this.hot.countRows(); i++) {
        tableData.push(this.visualObjectRow(i));
    }
    return tableData;
}

protected visualObjectRow(row: number): object {
    const obj = {};
    for (let i = 0; i < this.hot.countCols(); i++) {
        obj[this.hot.colToProp(i)] = this.hot.getDataAtCell(row, i);
    }
    return obj;
}

Orton