We have an Excel file json.xlsx: 

EMPId	Name	Date	Periodname	TimUnitDuration	charge entry
					Type	Price
21	abc	2015/12/31	15 data	15	fixed	100
			45 data	45	fixed	100
42	def	2015/12/31	15 data	15	fixed	100
			30 data	30	fixed	100
			45 data	45	fixed	100

We need to convert the grouped Excel table containing merged cells into JSON format. Below is the expected result:

[

    {

        "EMPId":21,

        "Name":"abc",

        "Date":"2015-12-31",

        "Period":[

            {

                "Periodname":"15 data",

                "TimUnitDuration":15,

                "ChargeEntry":[

                    {

                        "Type":"fixed",

                        "Price":100

                    }

                ]

            },

            {

                "Periodname":"45 data",

                "TimUnitDuration":45,

                "ChargeEntry":[

                    {

                        "Type":"fixed",

                        "Price":100

                    }

                ]

            }

        ]

    },

    {

        "EMPId":42,

        "Name":"def",

        "Date":"2015-12-31",

        "Period":[

            {

                "Periodname":"15 data",

                "TimUnitDuration":15,

                "ChargeEntry":[

                    {

                        "Type":"fixed",

                        "Price":100

                    }

                ]

            },

            {

                "Periodname":"30 data",

                "TimUnitDuration":30,

                "ChargeEntry":[

                    {

                        "Type":"fixed",

                        "Price":100

                    }

                ]

            },

            {

                "Periodname":"45 data",

                "TimUnitDuration":45,

                "ChargeEntry":[

                    {

                        "Type":"fixed",

                        "Price":100

                    }

                ]

            }

        ]

    }

]

It’s simple to accomplish this with esProc.
Download esProc DSK edition and free license HERE.

1.        Write script p1.dfxin esProc:

	A
1	=file("json.xlsx").xlsimport@w(;,3)
2	=A1.group@i(~(1))
3	=A2.(~.(~.(if(~==null,A2.~(1)(#),~))))
4	=create(EMPId,Name,Date,Periodname,TimUnitDuration,CT,CP)
5	=A3.(~.(A4.record(~)))
6	=A4.group(#1,#2,#3;~.group(Periodname,TimUnitDuration;~.new(CT:Type,CP:Price):ChargeEntry):Period)
7	=json(A6)

A1   Import data of json.xlsxfrom the third row; @t option enables reading data as a sequence of sequences.

A2   Create a new group if the first value of each sequence is not null.

A3   Assign null to the empty cell in each merged cell.

A4   Create an empty table sequence.

A5   Insert data to the table sequence.

A6   Group rows according to Excel rules.

A7   Convert the table sequence to JSON format.

2.     Execute script to get desired result in A7.