Friday, 26 May 2017

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



via s-rupali

No comments:

Post a Comment