How to parse ‘excel formula' using node js to get the actual value from the excel sheet.

[vishal2159]

Hi,
we have the formula defined in a excel sheet that formula is reference to multiple cell values from another sheet in same excel file. we have to evalute that formula to get the value/result using node js. we have tried multiple npm packages but receiving formula instead of its value as the result.

Below are the packages we tried
1.exceljs
2. xlsx
3. 'xlsx-calc' and formulajs/formulajs'
4. hyperformula

const Excel = require('exceljs');
const workbook = new Excel.Workbook();
//Reading excel sheet here with passing path of sheet
workbook.xlsx.readFile("./SimpleExampleToTestExcelFormulaSettingInNodeJS.xlsx").then(() => {
var worksheet = workbook.getWorksheet("Sheet1");// In excel sheet1 is reading here
var templateSheet = workbook.getWorksheet("Sheet2");// In excel sheet2 is reading here

    //I want to read excel cell and formula value also here I have tried below scenario.

    //Scenario 1
    console.log("getValue", worksheet.getCell('A' + 15).value);
    //getValue { formula: 'B9', result: 'Action for Step 1' }
    //that time getting this result.

    //Scenario 2
    var cellFormula = 'B9';
    //Here i am try to set B9 value in A20 
    worksheet.getCell('A' + 20).formula = { formula: cellFormula };
    console.log("getValue", worksheet.getCell('A' + 20).value);
    //That getting result getValue null

    //Scenario 3
    var cellFormula = 'B9';
    worksheet.getCell('A' + 20).value = { formula: cellFormula };
    console.log("getValue", worksheet.getCell('A' + 20).value);
    //That time getting getValue { formula: 'B9' }

    //Scenario 5
    var cellFormula = '=B9';
    worksheet.getCell('A' + 20).value = cellFormula;
    console.log("getValue", worksheet.getCell('A' + 20).value);
    //That time getting getValue =B9

    //Scenario 6
    var cellFormula = '=B9';
    worksheet.getCell('A' + 20).formula = cellFormula;
    console.log("getValue", worksheet.getCell('A' + 20).value);
    //That time getting getValue =B9

    //But i want in A20 { formula: 'B9', result: 'Action for Step 1' }

    //Scenario 7
    //This one is trying to read another sheet2 and set value in A3 is Sheet1B9 value.
    var cellFormula = '=Sheet1!B9';
    worksheet1.getCell('A' + 3).formula = cellFormula;
    console.log("getValue", worksheet1.getCell('A' + 3).value);
    //That time getting getValue null

    //Scenario 8
    var cellFormula = '=Sheet1!B9';
    templateSheet.getCell('A' + 3).value = cellFormula;
    console.log("getValue", worksheet1.getCell('A' + 3).value);
    //That time getting getValue =Sheet1!B9

});

using 'xlsx-calc' and formulajs/formulajs' NPM
var XLSX_CALC = require('xlsx-calc');
var formulajs = require('@formulajs/formulajs');

//scenario 1
XLSX_CALC.import_functions(formulajs);
worksheet.C30 = { f: '=Template!H11'};
console.log("value", worksheet.C30.value);
//that time getting also same formula here

//scenario 2

XLSX_CALC.import_functions(formulajs);
worksheet.C30 = { f: '=Template!H11', v: "ghjhg" };
console.log("value", worksheet.C30.value);
//that time getting formula and value but value also set here

5.using xlsx npm

const reader = require('xlsx');
const workbook1 = reader.readFile(req.file.path);
var wb = workbook1.Sheets["FieldMapping"];
//scenario 1
var data = reader.utils.getCell(wb, 'A'+100 ,"=Template!E11",1)
//this one also not working proper

//scenario 2

reader.utils.sheet_set_array_formula(wb, "C1", "=Template!E11", 1);
//this time also get undefined value.

4 using 'hyperformula' Npm

const HyperFormula = require('hyperformula');
const data = ['=Template!E11'];
var setFormula = HyperFormula.buildFromArray(data ,’A’+100);
var getResult = hfInstance.getCellValue('A'+100);
console.log(getResult);
//This is also not working as expected.