5

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)?

Improve this question
5
  • Maybe doable with LibreOffice (which might be Python scriptable). But this is a rumor, I don't know the details Commented Jan 8, 2015 at 16:45
  • 1
    If your formulae are that simple, you could code them in some other language (Common Lisp, Python, Lua, C++, ...) Commented Jan 8, 2015 at 17:13
  • If you just want the functions and not to interact with excel files, I'd also suggest looking into something like Python. Commented Jan 9, 2015 at 10:03
  • @Trengot, I am aware of python, but I hoped I'd be able to find some tool that had the same set of functions like Excel does. I don't wanna sound as if I think python's not as powerful as excel. In my opinion, it's the other way around. Anyway, it was a great suggestion. Now, I'm trying to find through google if I can run the function alone in the command line without having to type 'python -c ...' Commented Jan 9, 2015 at 14:40
  • @BasileStarynkevitch, that was what I thought I'd do as a last resort. I'm lazy and I'd assumed somebody else might have already done it... :) Commented Jan 9, 2015 at 14:46

3 Answers 3

Reset to default
4

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks. That sounds like exactly what I'd wanted.
1

This can be done in Java using Apache POI the Java API for Microsoft documents. Create an Excel worksheet in memory, read a formula from the command line, and print the result.

The program below does this:

package stackoverflow.excel.formula;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Main {

    public static void main(String[] args) {
        String formula = args[0];

        // Create a cell and load the formula.
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellFormula(formula);

        // Evaluate the formula.
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        CellValue cellValue = evaluator.evaluate(cell);
        switch (cellValue.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.println(cellValue.getBooleanValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            System.out.println(cellValue.getNumberValue());
            break;
        case Cell.CELL_TYPE_STRING:
            System.out.println(cellValue.getStringValue());
            break;
        default: 
            break;
        }
    }

}

Simple Maven pom.xml to build and package the program:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>stackoverflow</groupId>
  <artifactId>excel_formula_cli</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <description>https://stackoverflow.com/questions/27843945/command-line-execution-of-excel-functions</description>

  <dependencies>
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>3.9</version>
      </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-shade-plugin</artifactId>
        <version>2.2</version>
        <executions>
          <execution>
            <phase>package</phase>
            <goals>
              <goal>shade</goal>
            </goals>
            <configuration>
              <transformers>
                <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                  <mainClass>stackoverflow.excel.formula.Main</mainClass>
                </transformer>
              </transformers>
            </configuration>
          </execution>
        </executions>
      </plugin>
    </plugins>
  </build>


</project>

Sample usage:

Build the code with mvn clean package and execute with java -jar target/excel_formula_cli-0.0.1-SNAPSHOT.jar "YOUR FORMULA HERE"

This has the drawback that not all Excel functions are supported. For example the ROMAN() function is not implemented.

java -jar target/excel_formula_cli-0.0.1-SNAPSHOT.jar "6*7" => 42.0

java -jar target/excel_formula_cli-0.0.1-SNAPSHOT.jar "roman(15)" => org.apache.poi.ss.formula.eval.NotImplementedException: ROMAN

java -jar target/excel_formula_cli-0.0.1-SNAPSHOT.jar "randbetween(10,20)" => 19.0

If you are OK with the limitations of the Apache POI formula support, this will provide a portable cross-platform solution. See Developing Formula Evaluation - Appendix A for a list of supported functions.

Improve this answer

Comments

0

One approach would be to write a script (VBScript or other scripting environment) to:

  1. open an instance of Excel (non-visible)
  2. append your string to an "=" sign
  3. deposit the formula in a cell
  4. calculate the worksheet
  5. display the result to the user
  6. close the instance of Excel

    This would require Excel to be installed or OneDrive to be accessible.
Improve this answer

1 Comment

Could you edit your answer with an example? I'm not at all familiar with VBScript. But I'm guessing it's not very different from VBA or VB.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.