Library to evaluate excel/other language expression evaluation in java without using excel

Question

I have been searching the internet for a library which can evaluate excel formula-like expressions in java The formula is not embedded in the excel.

Let's say, the values are not in excel either Let's say 3 variables a, b, c I want an evaluator in java which given a string like CONCAT(a,b,c) written in excel expression or in some other language, can give me the output.

I have found some expression evaluator libraries for java online, but there I need to define my own expression language, is there something which directly uses excel language or some other language?

I have looked at apache POI and jxls also looked at jsp.el as far as I have understood poi and jxls do the evaluations from formulas already embedded in the excel file or we need to create a spreadsheet to actually run the formula which imo will be heavier than running the formula in java. For jsp.el I'll have to define my own expression language, which is not robust enough.

Answer 1

It is correct that apache poi's WorkbookEvaluator needs a workbook. And since you are talking about evaluating "excel formula-like expressions", this is necessary because all variables in such formulas must either be cell references or names in that workbook. Your given example CONCATENATE(a,b,c) can only work as an Excel formula when a, b, and c are Excel names . Else it would lead to #Name? error in Excel. And btw.: The Excel function is CONCATENATE and not CONCAT.

But this workbook must not necessarily stored somewhere. It can be only in random access memory too.

And the formulas itself need not to be in the sheet somewhere. The formula can also be given as a string since there is WorkbookEvaluator.evaluate(java.lang.String formula, CellReference ref).

Example:

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Name;

import org.apache.poi.ss.formula.BaseFormulaEvaluator;
import org.apache.poi.ss.formula.WorkbookEvaluator;

import org.apache.poi.ss.formula.eval.*;

import org.apache.poi.ss.util.CellReference;

public class EvaluateExcelFunctions {

 static Object evaluateExcelFormula(String formula, Workbook workbookWithVariables) {
  if (workbookWithVariables.getNumberOfSheets() < 1) workbookWithVariables.createSheet();
  CellReference reference = new CellReference(workbookWithVariables.getSheetName(0), 0 , 0, false, false);
  CreationHelper helper = workbookWithVariables.getCreationHelper();
  FormulaEvaluator formulaevaluator = helper.createFormulaEvaluator();
  WorkbookEvaluator workbookevaluator = ((BaseFormulaEvaluator)formulaevaluator)._getWorkbookEvaluator();
  ValueEval valueeval = null;
  try {
   valueeval = workbookevaluator.evaluate(formula, reference);
  } catch (Exception ex) {
   return ex.toString();
  }
  if (valueeval instanceof StringValueEval) {
   String result = ((StringValueEval)valueeval).getStringValue();
   return result;
  } else if (valueeval instanceof NumericValueEval) {
   double result = ((NumericValueEval)valueeval).getNumberValue();
   return result;
  } else if (valueeval instanceof ErrorEval) {
   String result = ((ErrorEval)valueeval).getErrorString();
   return result;
  }
  return null;  
 }

 public static void main(String[] args) throws Exception {

  Workbook workbook = 
   //new XSSFWorkbook();
   new HSSFWorkbook();

  Name name;
  String formula;
  Object result;

  // example 1 concatenating strings - your example
  name = workbook.createName();
  name.setNameName("_a");
  name.setRefersToFormula("\"Text A \"");
  name = workbook.createName();
  name.setNameName("_b");
  name.setRefersToFormula("\"Text B \"");
  name = workbook.createName();
  name.setNameName("_c");
  name.setRefersToFormula("\"Text C \"");

  formula = "CONCATENATE(_a, _b, _c)";
  result = evaluateExcelFormula(formula, workbook);
  System.out.println(result);

  // example 2 Pythagorean theorem
  name = workbook.getName("_a"); 
  name.setRefersToFormula("12.34");
  name = workbook.getName("_b");
  name.setRefersToFormula("56.78");

  formula = "SQRT(_a^2 + _b^2)";
  result = evaluateExcelFormula(formula, workbook);
  System.out.println(result);

  // example 3 complex math formula
  name = workbook.getName("_a"); 
  name.setRefersToFormula("12.34");
  name = workbook.getName("_b");
  name.setRefersToFormula("56.78");
  name = workbook.getName("_c");
  name.setRefersToFormula("90.12");

  formula = "((_a+_b+_c)*_c/_b-_a)/2";
  result = evaluateExcelFormula(formula, workbook);
  System.out.println(result);

  // example 4 faulty formulas
  name = workbook.getName("_a"); 
  name.setRefersToFormula("56.78");
  name = workbook.getName("_b");
  name.setRefersToFormula("190.12");
  name = workbook.getName("_c");
  name.setRefersToFormula("\"text\"");

  formula = "_a + _c";
  result = evaluateExcelFormula(formula, workbook);
  System.out.println(result);

  formula = "((_a + _b";
  result = evaluateExcelFormula(formula, workbook);
  System.out.println(result);

  formula = "_a \\ 2";
  result = evaluateExcelFormula(formula, workbook);
  System.out.println(result);

  formula = "_a^_b";
  result = evaluateExcelFormula(formula, workbook);
  System.out.println(result);

  formula = "_a/(_b-_b)";
  result = evaluateExcelFormula(formula, workbook);
  System.out.println(result);

  formula = "CONCAT(_a, _b)";
  result = evaluateExcelFormula(formula, workbook);
  System.out.println(result);

  workbook.close();
 }  
}

This code is tested using apache poi 4.1.0.

Note, Excel names cannot be all possible variable names. For example a Excel name cannot be c or C because this would be in conflict with possible R1C1 cell references. That's why I have named my names _a, _b and _c.