I'm using node.js to process my excel sheets with exceljs module.
I'm writing values into few cells and other cells are already containing formulas. I want to trigger those formulas and want to store values in sheet programatically.
For E.g. I have below sheet structure:
I'hv written below code to insert values in cells A1 and B1.
var workbook = new Excel.Workbook();
workbook.xlsx.readFile(__base + 'test.xlsx')
.then(function() {
var worksheet = workbook.getWorksheet(2);
var row = worksheet.getRow(1);
row.getCell(1).value = 2; // A1's value set to 2
row.getCell(2).value = 8; // B1's value set to 8
};
row.commit();
workbook.xlsx.writeFile(__base + 'test.xlsx');
});
When I'm trying to fetch value of c1 then its returning me formula with result 0. Below is the code to fetch values.
workbook.xlsx.readFile(__base + 'test.xlsx')
.then(function() {
var worksheet = workbook.getWorksheet(2);
var row = worksheet.getRow(1);
console.log(row.getCell(1).value);
console.log(row.getCell(2).value);
console.log(row.getCell(3).value);
});
OUTPUT:
How to get computed values or how to trigger computation in excel programatically and to store it in cell?
Output result of cell C1 should be 10 in console i.e. A1+B1 (2+8).
