Skip to content

Instantly share code, notes, and snippets.

@natergj
Created September 16, 2018 15:16
Show Gist options
  • Save natergj/b548fe8d2ea00c5b9fa94597c2cf9fd2 to your computer and use it in GitHub Desktop.
Save natergj/b548fe8d2ea00c5b9fa94597c2cf9fd2 to your computer and use it in GitHub Desktop.
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');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment