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

    });

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

[SiboutVanLoo]

Did you find a solution?

[saravana-immo]

@vishal2159 Any solution for this issue?

[ghost]

@vishal2159 Did you find the solution for this?

[saravana-immo]

I used Microsoft Graph API and Excel Js to get the updated value from the formula cell. It's working 100%. You can try that too.

[ghost]

But ig Microsoft graph api only works when files are uploaded to oneDrive or sharepoint, but in my use case the excel file is uploaded to S3, will it work with S3 as well?

[saravana-immo]

Yes. You need to upload the file to OneDrive and let the calculation happens and read the value from the excel using Graph APi. And delete the file. That's the workaround I follow.

[ghost]

Okay i'll try that, Thank you.

[gregkonush]

ExcelJS will not calculate that value for you. It can read/write the formula object and any cached result already stored in the file, but it is not a formula engine. So these are different cases:

worksheet.getCell('A15').value
// { formula: 'B9', result: 'Action for Step 1' }

That result came from the workbook file. If you create or change the formula in Node, ExcelJS will not evaluate it before you read the cell again.

The practical choices are:

1. If you need exact Excel behavior, use Excel itself somewhere in the loop: Microsoft Graph / Excel Online, LibreOffice headless, or a desktop Excel automation process. That is what the Graph workaround above is doing.
2. If the formula set is small enough and you already have a SheetJS-shaped workbook, xlsx-calc can work, but unsupported functions / cross-sheet / shared-formula cases can still block you.
3. If you want a mature JS formula engine, try HyperFormula and map the workbook data into its model.
4. If you can treat the workbook as service-owned state instead of a general Excel file, another option is to use a workbook runtime and keep XLSX import/export at the boundary.

Disclosure: I maintain @bilig/headless, which is built for the fourth case: write known input cells, recalculate in Node, read known output cells, persist the workbook state as JSON, and optionally import/export XLSX. It is not full Excel compatibility and it will not solve macros/charts/every Excel function. The relevant example is here: https://github.com/proompteng/bilig/tree/main/examples/xlsx-recalculation-node

For your specific snippet, I would not expect this to ever work with ExcelJS alone:

worksheet.getCell('A20').value = { formula: 'B9' }
console.log(worksheet.getCell('A20').value)

At that point you have assigned a formula object; you have not run a calculation engine. You either need to pass the workbook through a calculator/runtime, or open/save it in an Excel-compatible engine that fills the cached results.