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;

Answer 2

1. xlsx-renderer way:

https://github.com/Siemienik/xlsx-renderer/

Doing it by using exceljs or sheetjs may be quite problematic, it's much easier to use xlsx-renderer, which separate templates from data:

and data {..} gives:

BTW. Xlsx-renderer is powered by ExcelJS.

2. ExcelJs way:

https://github.com/exceljs/exceljs#merged-cells

worksheet.mergeCells('A1:A3');
worksheet.getCell('A1').value = "Process Flag";
worksheet.mergeCells('B1:B3');
worksheet.getCell('B1').value = "Response";
worksheet.mergeCells('C1:F1');
worksheet.getCell('C1').value = "...";
worksheet.mergeCells('C2:C3');
worksheet.getCell('C2').value = "...";
worksheet.mergeCells('D2:D3');
worksheet.getCell('D2').value = "...";
worksheet.mergeCells('E2:E3');
worksheet.getCell('E2').value = "...";
//...

3. SheetJS way:

You have to manipulate with worksheet['!merges'], but it is quite poorly described ... :( https://github.com/SheetJS/sheetjs#worksheet-object