Achieving nested header structure while writing excel using SheetJS/ExcelJS

Question

I want to achieve the below shown nested structure while writing the excel file in nodeJS application. I am not able to comprehend how to proceed. Is there a way to define multilevel headers or any JSON is possible using which I can achieve this structure.

Answer 1

For SheetJS I was able to achieve it by some workaround.

Lets say we are trying to achieve this table format

First I added empty rows that will make way for our headers.

    // lets say this is our JSON data
    const workers = [{'Name':'George', 'Height':'69', 'Weight':'112'},
                     {'Name':'John', 'Height':'71', 'Weight':'120'}]
    // we add empty rows in the beginning
    workers.unshift({'Name':'', 'Height':'', 'Weight':''},
                    {'Name':'', 'Height':'', 'Weight':''})

Then we need to hide the headers (which is normally the keys of our JSON).

    // Hide headers by adding skipHeader: true
    const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet(food, {skipHeader: true})

Then we can now fill out the empty rows. (Manually..)

    ws.A1={t: 's', v: 'Name'}
    ws.B1={t: 's', v: 'Measurements'}
    ws.B2={t: 's', v: 'Height'}
    ws.C2={t: 's', v: 'Weight'}

Finally we merge the cells.

    // s - start, e - end, r - row, c - col (0 based)
    const merge = [{ s: { r: 0, c: 0 }, e: { r: 1, c: 0 } }, // Name
                   { s: { r: 0, c: 1 }, e: { r: 0, c: 2 } }  // Measurements
    ws['!merges'] = merge;