Mapping Nested JSON Array to Excel

Tags: #<Tag:0x00007efc6493aaf0>
#1

Following is the JSON format

var myJson = {
“propertyValues”: [
{
“propertyName”: “Physico-Chemical Properties”,
“propertyId”: 1,
“unit”: “000’$”,
“isEditable”: true,
“keyName”: “physioChemical”,
“lastUpdatedById”: “99300”,
“lastUpdatedDate”: “24-Oct-2017 22:37”,
“propertyValues”: [
{
“id”: 21000,
“year”: 2019,
“value”: “”,
“name”: null,
“editTable”: false,

		"quartes": [
        {
          "value": "20",
          "name": "Q1"
        },
        {
          "value": "20",
          "name": "Q2"
        },
        {
          "value": "20",
          "name": "Q3"
        },
        {
          "value": "20",
          "name": "Q4"
        }
      ]
    },
    {
      "id": 21001,
      "year": 2020,
      "value": "",
      "name": null,
      "editTable": false,
      "quartes": [
        {
          "value": "20",
          "name": "Q1"
        },
        {
          "value": "20",
          "name": "Q2"
        },
        {
          "value": "20",
          "name": "Q3"
        },
        {
          "value": "20",
          "name": "Q4"
        }
      ]
    },
    {
      "id": 21002,
      "year": 2021,
      "value": "",
      "name": null,
      "editTable": false,
      "quartes": [
        {
          "value": "20",
          "name": "Q1"
        },
        {
          "value": "20",
          "name": "Q2"
        },
        {
          "value": "20",
          "name": "Q3"
        },
        {
          "value": "20",
          "name": "Q4"
        }
      ]
    },
    {
      "id": 21003,
      "year": 2022,
      "value": "",
      "name": null,
      "editTable": false,
      "quartes": [
        {
          "value": "20",
          "name": "Q1"
        },
        {
          "value": "20",
          "name": "Q2"
        },
        {
          "value": "20",
          "name": "Q3"
        },
        {
          "value": "20",
          "name": "Q4"
        }
      ]
    },
    {
      "id": 21004,
      "year": 2023,
      "value": "",
      "name": null,
      "editTable": false,
      "quartes": [
        {
          "value": "20",
          "name": "Q1"
        },
        {
          "value": "20",
          "name": "Q2"
        },
        {
          "value": "20",
          "name": "Q3"
        },
        {
          "value": "20",
          "name": "Q4"
        }
      ]
    },
    {
      "id": 21005,
      "year": 2024,
      "value": "",
      "name": null,
      "editTable": false,
      "quartes": [
        {
          "value": "20",
          "name": "Q1"
        },
        {
          "value": "20",
          "name": "Q2"
        },
        {
          "value": "20",
          "name": "Q3"
        },
        {
          "value": "20",
          "name": "Q4"
        }
      ]
    },
    {
      "id": 21006,
      "year": 2025,
      "value": "",
      "name": null,
      "editTable": false,
      "quartes": [
        {
          "value": "20",
          "name": "Q1"
        },
        {
          "value": "20",
          "name": "Q2"
        },
        {
          "value": "20",
          "name": "Q3"
        },
        {
          "value": "20",
          "name": "Q4"
        }
      ]
    },
    {
      "id": 38212,
      "year": 2026,
      "value": "",
      "name": null,
      "editTable": false,
      "quartes": [
        {
          "value": "20",
          "name": "Q1"
        },
        {
          "value": "20",
          "name": "Q2"
        },
        {
          "value": "20",
          "name": "Q3"
        },
        {
          "value": "20",
          "name": "Q4"
        }
      ]
    },
    {
      "id": 47098,
      "year": 2027,
      "value": "",
      "name": null,
      "editTable": false,
      "quartes": [
        {
          "value": "20",
          "name": "Q1"
        },
        {
          "value": "20",
          "name": "Q2"
        },
        {
          "value": "20",
          "name": "Q3"
        },
        {
          "value": "20",
          "name": "Q4"
        }
      ]
    }
  ]
},
{
  "propertyName": "Methods of Analysis",
  "propertyId": 2,
  "unit": "000'$",
  "isEditable": true,
  "keyName": "analysisMethod",
  "lastUpdatedById": "99300",
  "lastUpdatedDate": "24-Oct-2017 22:37",
  "propertyValues": [
    {
      "id": 21000,
      "year": 2019,
      "value": "",
      "name": null,
      "editTable": false
    },
    {
      "id": 21001,
      "year": 2020,
      "value": "",
      "name": null,
      "editTable": false
    },
    {
      "id": 21002,
      "year": 2021,
      "value": "",
      "name": null,
      "editTable": false
    },
    {
      "id": 21003,
      "year": 2022,
      "value": "",
      "name": null,
      "editTable": false
    },
    {
      "id": 21004,
      "year": 2023,
      "value": "",
      "name": null,
      "editTable": false
    },
    {
      "id": 21005,
      "year": 2024,
      "value": "",
      "name": null,
      "editTable": false
    },
    {
      "id": 21006,
      "year": 2025,
      "value": "",
      "name": null,
      "editTable": false
    },
    {
      "id": 38212,
      "year": 2026,
      "value": "",
      "name": null,
      "editTable": false
    },
    {
      "id": 47098,
      "year": 2027,
      "value": "",
      "name": null,
      "editTable": false
    }
  ]
}

]
};

Following is the Excel structure we are looking to build >> Particular wise, year wise and every year quarterwise data in the same row

![image|690x324](upload://mgTVFrvVNWrkz7VI

image
image.png1172×551 48.5 KB
MMT9vsfjXL.png)

Which of the databinding technique do I need to use?

#2

To further add, how to work with dynamic data set is not very clear from documentation. All examples have been taken with static dataset. In this case for every row, data can be for 5 years or even for 10 years and each year would hold data for 4 quarters

Also note that we also want to utilize HandsonTable’s DataBinding feature i.e. any change in table would want to persist the changes in the same object to push information to server

Is this achieveable with Handson?

#3

Following is the solution which I have worked out:

https://jsfiddle.net/pdarshan85/p84xbLnt/1/

What I lose out on this solution is data binding with original object.

With this solution I would need to update the changes back in the original object which is cumbersome

#4

Hey @priyadarshan.patil

that is a complex task. Can you write me at support@handsontable.com? I guess that it will be easier to make a couple of smaller tasks and move from one to another.

#5

Hi Aleksandra,

I have sent email at support ID

pls note that we are also in process of procuring commercial license

This is part of small POC we are doing to ensure there is fitment of the product

#6

Thank you. We will continue on emails.

#7