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
xlsx
'xlsx-calc' and formulajs/formulajs'
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 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.
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
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.