I have a Excel file where I need to enter certain values from a JSON file. The excel sheet has pre-defined formulas that will use the values I will enter from JSON file using Exceljs.
Now I know that Exceljs doesn't do calculation. But here's the problem. Let's take an example to understand better.
A1 = 5
B1 = 4
C1 = 7
E1 = (A1+B1+C1) / 3 ----> which means the value of E1 = 5.33
Excel (or any spreadsheet really) gives us the convenience of dragging a formula cell and spreading it down the column and the formula automatically changes to, say,
E2 = (A2+B2+C2) / 3
E3 = (A3+B3+C3) / 3
E4 = (A4+B4+C4) / 3 and so on...
But, when I access these cells in exceljs, the formula they show is,
E2 = (A1+B1+C1) / 3
E3 = (A1+B1+C1) / 3
And their shareType is set to shared.
Is there anyway to update these shared formula depending on position of cell.
These cells only get updated to correct formula when I open the file in excel and then very intentionally click on the formula and accept the formula. Then the formula is recognized by exceljs correctly and the formula is not 'shared' anymore in the shareType.
Any feedback or help here will be appreciated. If you know of another library which can recognize these kind of shared formula will also be helpful. Thanks.
try creating each formula manually, and also setting cell reference while iterating data:
for (let i = 1; i <= 4; i++) {
worksheet.getCell(`E${i}`).value = {
formula: `(A${i}+B${i}+C${i})/3`
};
}