CODEWITHSUNDEEP
javascriptnode.jsexcelsails.jsexceljs
Rupali
Rupali

Reputation: 1078

get computed value of excel sheet cell in Node.js

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: enter image description here

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:

enter image description here

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).

Upvotes: 1

Views: 12799

Answers (2)

davidhq
davidhq

Reputation: 4760

hot-formula-parser can parse formulas... use in combination with exceljs

Here is the possible connecting code.

Upvotes: 4

Nico
Nico

Reputation: 13840

exceljs allows you to modify the file directly without using Excel.
There won't be any calcultation done at the file level, the result value will be the latest calculted by Excel last time you opened it.

If you open Excel, it will calculte everything and value will be correct.

Upvotes: 2

Related Questions