Executing command line functions of Excel

Question

Is it possible to call Excel functions from the command line?

I do not need interactions with any file. I just wanted to use these features, as other command line tools work. It should just serve some one-time goal.

For example, I would like if there was a way to do something like this:

$ excel roman(15)
XV

$ excel randbetween(10,20)
14

Of course, one cannot expect that not all available functions will perform like this, but the more the better.

• Are there any native applications (either on Linux or on Windows, but preferably cross-platform) that provide such options?

• Or can we somehow manipulate excel itself to do this (although, based on what I was looking for, this doesn't seem possible)?

Answer 1

libformula, , . :

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

excel - 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"

org.xerus.excel.Excel argv :

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 org.pentaho.reporting.libraries.formula.demo.PrintAllFunctions, :

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

Answer 2

Java, Apache POI Java API Microsoft. Excel , .

:

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;
        }
    }

}

Maven pom.xml :

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

:

mvn clean package java -jar target/excel_formula_cli-0.0.1-SNAPSHOT.jar "YOUR FORMULA HERE"

, Excel. , ROMAN() .

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

Apache POI, - . . - A .

Answer 3

- script (VBScript ):

• Excel ()
• "="
• close instance of Excel
  
  This will require the installation of Excel or OneDrive for access.