Original Data Object:
var data = [{ "monoTotalMonthly" : 490,
"colorTotalMonthly" : 0,
"lifeTotalMonthly" : 490,
"colorA3Monthly" : 0,
"monoA3Monthly" : 0,
"copyTotalMonthly" : 0,},{ "monoTotalMonthly" : 490,
"colorTotalMonthly" : 0,
"lifeTotalMonthly" : 490,
"colorA3Monthly" : 0,
"monoA3Monthly" : 0,
"copyTotalMonthly" : 0,},{ "monoTotalMonthly" : 490,
"colorTotalMonthly" : 0,
"lifeTotalMonthly" : 490,
"colorA3Monthly" : 0,
"monoA3Monthly" : 0,
"copyTotalMonthly" : 0,},{ "monoTotalMonthly" : 490,
"colorTotalMonthly" : 0,
"lifeTotalMonthly" : 490,
"colorA3Monthly" : 0,
"monoA3Monthly" : 0,
"copyTotalMonthly" : 0,},{ "monoTotalMonthly" : 490,
"colorTotalMonthly" : 0,
"lifeTotalMonthly" : 490,
"colorA3Monthly" : 0,
"monoA3Monthly" : 0,
"copyTotalMonthly" : 0,},{ "monoTotalMonthly" : 490,
"colorTotalMonthly" : 0,
"lifeTotalMonthly" : 490,
"colorA3Monthly" : 0,
"monoA3Monthly" : 0,
"copyTotalMonthly" : 0,}]
Load XLSX library (since this is node)
var XLSX = require('xlsx')
Build Worksheet
var worksheet = {};
var range = {s:{r:0,c:0},e:{r:0,c:8}};
data.forEach(function(row, n) {
range.e.r = n;
/* data cells, I just took the order from your example
but you can change the `row["..."]` part to reorder the cols */
worksheet['A' + (n+1)] = {t:'n', v:row["monoTotalMonthly"]};
worksheet['B' + (n+1)] = {t:'n', v:row["colorTotalMonthly"]};
worksheet['D' + (n+1)] = {t:'n', v:row["lifeTotalMonthly"]};
worksheet['E' + (n+1)] = {t:'n', v:row["colorA3Monthly"]};
worksheet['G' + (n+1)] = {t:'n', v:row["monoA3Monthly"]};
worksheet['H' + (n+1)] = {t:'n', v:row["copyTotalMonthly"]};
/* column C is going to be the sum from A and B */
worksheet['C' + (n+1)] = {t:'n', v:worksheet['A' + (n+1)].v + worksheet['B' + (n+1)].v, f:'A' + (n+1)+'+'+'B'+(n+1)};
/* column F is going to be the sum from D and E */
worksheet['F' + (n+1)] = {t:'n', v:worksheet['D' + (n+1)].v + worksheet['E' + (n+1)].v, f:'D' + (n+1)+'+'+'E'+(n+1)};
/* column I is going to be the sum from G and H */
worksheet['I' + (n+1)] = {t:'n', v:worksheet['G' + (n+1)].v + worksheet['H' + (n+1)].v, f:'G' + (n+1)+'+'+'H'+(n+1)};
});
worksheet['!ref'] = XLSX.utils.encode_range(range);
Save to file (In the buffer view, click the dropdown and choose "Download" then hit "Download as .zip", then rename the file as xlsx -- unfortunately runkit doesn't support download as XLSX so you have to do this)
var wb = {SheetNames:["Sheet1"], Sheets:{Sheet1:worksheet}};
//XLSX.writeFile(wb, "test634.xlsx"); // <-- this saves to a file
XLSX.write(wb, {type:"buffer"}); // <-- this generates a buffer
no comments
sign in to comment