excel4node multi-level groupings.js

const xl = require('excel4node');

const costsObj = {
  'supplies': {
    'paper': 100,
    'toner': 50,
  },
  'equipment': {
    'laptops': 3500,
    'accessories': 150
  }
}

const wb = new xl.Workbook();
const dollarStyle = wb.createStyle({ numberFormat: '$#,##0.00; ($#,##0.00); -' });

const ws = wb.addWorksheet('Groupings', {
  outline: {
    summaryBelow: true, // Change to False if your summary row is above your item rows
  }
});

let curRow = 1;
const typeSumRows = [];
for (const type in costsObj) {
  const typeStartRow = curRow;
  let typeEndRow = curRow;
  for (const item in costsObj[type]) {
    ws.cell(curRow, 1).string(item);
    ws.cell(curRow, 2).number(costsObj[type][item]).style(dollarStyle);

    // The first argument to the group method is the level of grouping the row should belong to
    // The 2nd argument is whether the group should be collapsed by default
    ws.row(curRow).group(2, true)
    typeEndRow = curRow;
    curRow = curRow + 1;
  }

  ws.cell(curRow, 1).string(type);
  ws.cell(curRow, 2).formula(`SUM(B${typeStartRow}:B${typeEndRow})`).style(dollarStyle);
  typeSumRows.push(curRow);

  // Summary row is at one group level higher than items
  ws.row(curRow).group(1);
  curRow = curRow + 1;
}

// When no grouping is specified, a group level of '0' is implied
ws.cell(curRow, 1).string('total');
const typeSumCells = typeSumRows.map(r => `B${r}`);
ws.cell(curRow, 2).formula(`SUM(${typeSumCells.join(',')})`).style(dollarStyle);

wb.write('Cost Projections.xlsx');