How to parse ‘excel formula' using node js to get the actual value from the excel sheet. #2470
Unanswered
vishal2159
asked this question in
Q&A
Replies: 3 comments 4 replies
-
Did you find a solution? |
Beta Was this translation helpful? Give feedback.
0 replies
-
@vishal2159 Any solution for this issue? |
Beta Was this translation helpful? Give feedback.
0 replies
-
@vishal2159 Did you find the solution for this? |
Beta Was this translation helpful? Give feedback.
4 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
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
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.
Beta Was this translation helpful? Give feedback.
All reactions