Command line execution of Excel functions

Can Excel functions be called from the command line?

I don’t want any interactions with any file. I’d just like to use these functions the way other command line tools work. It should merely serve some one-time purpose.

For instance, I’d like it if there were a way to do something like this:

$ excel roman(15)
XV

$ excel randbetween(10,20)
14

Of course, not all of the available functions can be expected to be run like this, but the more, the better.

1. Are there any native applications (either on Linux or Windows, but preferably cross-platform) that provide this kind of options?

2. Or is there any way we can manipulate excel itself to do this (although, based on whatever I’ve searched for, this doesn’t seem to be possible)?

Answer

libformula is probably a good place to start. Here’s a proof of concept:

$ excel 'TRIM(" abc 123  ")'
abc 123

where excel is this simple shell script:

#!/bin/sh

java -cp /usr/share/java/commons-logging.jar:libformula/demo:libbase/dist/libbase-6.1-SNAPSHOT.jar:libformula/dist/libformula-6.1-SNAPSHOT.jar:/home/cwarden/git/excel/src org.xerus.excel.Excel "$1"

and org.xerus.excel.Excel takes a string from argv and evaluates it as a formula:

package org.xerus.excel;

import org.pentaho.reporting.libraries.formula.EvaluationException;
import org.pentaho.reporting.libraries.formula.Formula;
import org.pentaho.reporting.libraries.formula.DefaultFormulaContext;
import org.pentaho.reporting.libraries.formula.parser.ParseException;

public class Excel {
        public static void main(final String[] args) throws ParseException, EvaluationException {
                final Formula f = new Formula(args[0]);
                f.initialize(new DefaultFormulaContext());
                final Object o = f.evaluate();
                System.out.println(o);
        }
}

libformula includes a demo program, org.pentaho.reporting.libraries.formula.demo.PrintAllFunctions, which prints out all of the supported functions:

Category User-Defined
ARRAYCONCATENATE, ARRAYCONTAINS, ARRAYLEFT, ARRAYMID, CSVARRAY, CSVTEXT, NORMALIZEARRAY, NULL, PARSEDATE, SEQUENCEQUOTER
Category Information
CHOOSE, COUNT, COUNTA, COUNTBLANK, ERROR, HASCHANGED, INDEX, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, LOOKUP, NA, VALUE
Category Rounding
INT
Category Mathematical
ABS, ACOS, ACOSH, ASIN, ATAN, ATAN2, AVERAGE, AVERAGEA, COS, EVEN, EXP, LN, LOG10, MAX, MAXA, MIN, MINA, MOD, N, ODD, PI, POWER, SIN, SQRT, SUM, SUMA, VAR
Category Text
ARRAYRIGHT, ASC, CHAR, CLEAN, CODE, CONCATENATE, EXACT, FIND, FIXED, FIXED, LEFT, LEN, LOWER, MESSAGE, MID, PROPER, REPLACE, REPT, RIGHT, SEARCH, STRINGCOUNT, SUBSTITUTE, T, TEXT, TRIM, UNICHAR, UNICODE, UPPER, URLENCODE
Category Date/Time
DATE, DATEDIF, DATETIMEVALUE, DATEVALUE, DAY, DAYS, HOUR, MINUTE, MONTH, MONTHEND, NOW, PREVWEEKDAY, SECOND, TIME, TIMEVALUE, TODAY, WEEKDAY, YEAR, YESTERDAY
Category Logical
AND, FALSE, IF, IFNA, NOT, OR, TRUE, XOR
Category Database
BEGINSWITH, CONTAINS, ENDSWITH, EQUALS, IN, LIKE