Here is my excel file I generated using exceljs.I need to add the weight for every grnNo and display the result as a total at the end of that particular grnNo,Something like this.
Here is my code
var userss = inward.aggregate([{
$match: {
$and: [
{ "defaultAccountId": { "$gte": parseInt(userParam.defaultAccountId), "$lte": parseInt(userParam.defaultAccountId) } },
],
$or: [{ "createdDate": { "$gte": start, "$lte": end } }]
}
},
{
$unwind: "$fabricArray"
},
{
$project: {
grnNo: "$grnNo",
grnDate: "$grnDate",
party: "$party",
orderNo: "$orderNo",
lotNo: "$lotNo",
buyer: "$buyer",
vehicleNo: "$vehicleNo",
dyeingName: "$dyeingName",
dyeingDcNo: "$dyeingDcNo",
partyDcNo: "$partyDcNo",
weight: "$weight",
fabricName: "$fabricArray.fabricName",
fabricColor: "$fabricArray.color",
fabricDia: "$fabricArray.dia",
fabricRolls: "$fabricArray.rolls",
fabricTotalKgs: "$fabricArray.totalKgs",
fabricRemarks: "$fabricArray.remarks",
color: "$color",
fabricCondition: "$fabricCondition",
partyDcRolls: "$partyDcRolls"
}
}
], function(err, result) {
if (err) {
console.log(err);
return;
}
var workbook = new Excel.Workbook();
var worksheet = workbook.addWorksheet('Inwards')
worksheet.columns = [
{ key: "grnNo", header: "GRN_No" },
{ key: "grnDate", header: "Date" },
{ key: "party", header: "Party" },
{ key: "orderNo", header: "OrderNo" },
{ key: "lotNo", header: "LotNo" },
{ key: "buyer", header: "Buyer" },
{ key: "vehicleNo", header: "VehicleNo" },
{ key: "dyeingName", header: "dyeingName" },
{ key: "dyeingDcNo", header: "DyeingDcNo" },
{ key: "partyDcNo", header: "PartyDcNo" },
{ key: "color", header: "Color" },
{ key: "fabricCondition", header: "Fabric_Condition" },
{ key: "partyDcRolls", header: "PartyDcRolls" },
{ key: "weight", header: "Weight" },
{ key: "fabricName", header: "Fabric_Name" },
{ key: "fabricColor", header: "Fabric_Color" },
{ key: "fabricDia", header: "Fabric_Dia" },
{ key: "fabricRolls", header: "Fabric_Rolls" },
{ key: "fabricTotalKgs", header: "Total_Kgs" },
{ key: "fabricRemarks", header: "Remarks" },
]
worksheet.getCell('A1').font = {
bold: true
};
worksheet.getCell('B1').font = {
bold: true
};
worksheet.getCell('C1').font = {
bold: true
};
worksheet.getCell('D1').font = {
bold: true
};
worksheet.getCell('E1').font = {
bold: true
};
worksheet.getCell('F1').font = {
bold: true
};
worksheet.getCell('G1').font = {
bold: true
};
worksheet.getCell('H1').font = {
bold: true
};
worksheet.getCell('I1').font = {
bold: true
};
worksheet.getCell('J1').font = {
bold: true
};
worksheet.getCell('K1').font = {
bold: true
};
worksheet.getCell('L1').font = {
bold: true
};
worksheet.getCell('M1').font = {
bold: true
};
worksheet.getCell('N1').font = {
bold: true
};
worksheet.getCell('O1').font = {
bold: true
};
worksheet.getCell('P1').font = {
bold: true
};
worksheet.getCell('Q1').font = {
bold: true
};
worksheet.getCell('R1').font = {
bold: true
};
worksheet.getCell('S1').font = {
bold: true
};
worksheet.getCell('T1').font = {
bold: true
};
worksheet.getCell('U1').value = { formula: 'N2+N5', result: undefined };
result.forEach(function(row, rows) {
worksheet.addRow(row);
})
workbook.xlsx.writeFile(filePath)
.then(function() {
console.log("excel file created successfully");
});
}).allowDiskUse(true);
return await (userss)
}
I am a total beginner, any idea how to do it?. I don't know whether to calculate my expected total values while fetching data's from the database or is there any function that can do this task in exceljs itself. Thanks in advance:)
I found the answer, hope it will help someone. I didn't get my expected result by using exceljs, so I just switched to excel4node. Here I calculated the value and inserted into the cell
async function getInwards(userParam) {
const dateTime = new Date().toISOString().slice(-24).replace(/\D/g,
'').slice(0, 14);
const filePath = "Inwards" + dateTime + ".xlsx";
const start = new Date(userParam.fromDate);
const end = new Date(userParam.toDate);
var wb = new xl.Workbook();
var ws = wb.addWorksheet('Inwards')
if (userParam.fromDate && userParam.toDate) {
var inw = await inward.find({
defaultAccountId: { "$gte": parseInt(userParam.defaultAccountId), "$lte": parseInt(userParam.defaultAccountId) },
createdDate: { "$gte": start, "$lte": end }
})
var style = wb.createStyle({
font: {
bold: true
},
numberFormat: '$#,##0.00; ($#,##0.00); -',
});
ws.cell(1, 1)
.string('GrnNo')
.style(style);
ws.cell(1, 2)
.string('GrnDate')
.style(style);
ws.cell(1, 3)
.string('Party')
.style(style);
ws.cell(1, 4)
.string('OrderNo')
.style(style);
ws.cell(1, 5)
.string('LotNo')
.style(style);
ws.cell(1, 6)
.string('Buyer')
.style(style);
ws.cell(1, 7)
.string('VehicleNo')
.style(style);
ws.cell(1, 8)
.string('DyeingName')
.style(style);
ws.cell(1, 9)
.string('DyeingDcNo')
.style(style);
ws.cell(1, 10)
.string('PartyDcNo')
.style(style);
ws.cell(1, 11)
.string('Color')
.style(style);
ws.cell(1, 12)
.string('FabricCondition')
.style(style);
ws.cell(1, 13)
.string('PartyDcRolls')
.style(style);
ws.cell(1, 14)
.string('Weight')
.style(style);
ws.cell(1, 15)
.string('FabricName')
.style(style);
ws.cell(1, 16)
.string('FabricColor')
.style(style);
ws.cell(1, 17)
.string('FabricDia')
.style(style);
ws.cell(1, 18)
.string('FabricRolls')
.style(style);
ws.cell(1, 19)
.string('TotalKgs')
.style(style);
ws.cell(1, 20)
.string('Remarks')
.style(style);
const n = inw.length
var count = 0;
for (a = 0; a < n; a++) {
const nn = inw[a].fabricArray
const nnn = nn.length
var addrolls = 0;
var addtotal = 0;
ws.cell(a + 2 + count, 1)
.string(inw[a].grnNo)
ws.cell(a + 2 + count, 2)
.date(inw[a].grnDate)
ws.cell(a + 2 + count, 3)
.string(inw[a].party)
ws.cell(a + 2 + count, 4)
.string(inw[a].orderNo)
ws.cell(a + 2 + count, 5)
.string(inw[a].lotNo)
ws.cell(a + 2 + count, 6)
.string(inw[a].buyer)
ws.cell(a + 2 + count, 7)
.string(inw[a].vehicleNo)
ws.cell(a + 2 + count, 8)
.string(inw[a].dyeingName)
ws.cell(a + 2 + count, 9)
.string(inw[a].dyeingDcNo)
ws.cell(a + 2 + count, 10)
.string(inw[a].partyDcNo)
ws.cell(a + 2 + count, 11)
.string(inw[a].color)
ws.cell(a + 2 + count, 12)
.string(inw[a].fabricCondition)
ws.cell(a + 2 + count, 13)
.number(inw[a].partyDcRolls.value)
ws.cell(a + 2 + count, 14)
.number(inw[a].weight.value)
for (i = 0; i < nnn; i++) {
ws.cell(a + 2 + count, 15)
.string(inw[a].fabricArray[i].fabricName)
ws.cell(a + 2 + count, 16)
.string(inw[a].fabricArray[i].color)
ws.cell(a + 2 + count, 17)
.string(inw[a].fabricArray[i].dia)
ws.cell(a + 2 + count, 18)
.number(inw[a].fabricArray[i].rolls.value)
ws.cell(a + 2 + count, 19)
.number(inw[a].fabricArray[i].totalKgs.value)
ws.cell(a + 2 + count, 20)
.string(inw[a].fabricArray[i].remarks)
count = count + 1
addrolls = addrolls + inw[a].fabricArray[i].rolls.value
addtotal = addtotal + inw[a].fabricArray[i].totalKgs.value
}
ws.cell(a + count + 2, 17)
.string('Total')
.style(style);
ws.cell(a + count + 2, 18)
.number(addrolls)
ws.cell(a + count + 2, 19)
.number(addtotal)
}
wb.write(filePath, function(err, stats) {
if (err) {
console.error(err);
return err
}
});
if (inw) {
return await (inw)
}
}
}