Nsryjdtyk

We have a requirement to incorporate an excel based tool in java web application. This excel tool has set of master data and couple of result outputs using formula calculations on master data.

Master data can be captured in database with relational tables. We are looking for the best way to provide capability to capture, validate and evaluate. formulas.

So far looked at using scripting engines nashorn and provide formula support using eval. We would like to know how people are doing in other places.

I've searched and found two possible libraries that could be useful for you please have a look.

http://mathparser.org/

http://mathparser.org/mxparser-hello-world/mxparser-hello-world-java/

https://lallafa.objecthunter.net/exp4j/

https://lallafa.objecthunter.net/exp4j/#Evaluating_an_expression_asynchronously

Depends on how big your data is and what your required SLA is. Also on what kind of formulas/other functions that you want to support.

For example, consider a function like sum or max. Now, the master data is in some relation table containing 10k rows. You could pull in all this data inside a java app and do a sum (or run any function). However, imagine if the table contained 500K rows. This would take some time to stream all 500K rows to Java app but consumes lot of cpu and network bandwidth (database resources, local cpu resources). A better optimized scenario in that case would be index that column in the database and let database do all the hard work for you.

Personally, I don't like using eval. I would rather parse the user input to determine what actions to take.

I am assuming that data is not big to use big data tools.