Excel函数的命令行执行

Can Excel functions be called from the command line?

可以从命令行调用Excel函数吗?

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.

当然，并不是所有可用的函数都可以像这样运行，但是越多越好。

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

有任何本地应用程序(Linux或Windows上的，但最好是跨平台的)提供这种选项吗?
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)?

或者我们有什么方法可以操作excel本身来做这个(尽管，根据我搜索的内容，这似乎是不可能的)?

3 个解决方案

#1

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

libformula可能是一个很好的起点。的概念:

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

where excel is this simple shell script:

excel就是这个简单的shell脚本:

#!/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:

和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 includes a demo program, org.pentaho.reporting.libraries.formula.demo.PrintAllFunctions, which prints out all of the supported functions:

libformula包括一个演示程序org.pentaho.report .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

#2

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.

这可以在Java中使用Apache POI作为Microsoft文档的Java API来完成。在内存中创建一个Excel工作表，从命令行中读取公式，并打印结果。

The program below does this:

下面的程序是这样做的:

package *.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:

简单的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>*</groupId>
  <artifactId>excel_formula_cli</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <description>https://*.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>*.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"

使用mvn clean包构建代码，并使用java -jar目标/excel_formula a_cli0.0.1快照执行。jar“公式”

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

这样做的缺点是不支持所有的Excel函数。例如，没有实现ROMAN()函数。

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

目标/ excel_formula_cli-0.0.1-SNAPSHOT java jar。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

目标/ excel_formula_cli-0.0.1-SNAPSHOT java jar。jar“罗马(15)”= > org.apache.poi.ss.formula.eval。NotImplementedException:罗马

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

目标/ excel_formula_cli-0.0.1-SNAPSHOT java jar。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.

如果您对Apache POI公式支持的局限性没有意见，这将提供一个可移植的跨平台解决方案。参见开发公式评估-附录A中支持的函数列表。

#3

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

一种方法是编写脚本(VBScript或其他脚本环境):

open an instance of Excel (non-visible)
打开Excel实例(不可见)
append your string to an "=" sign
将字符串附加到“=”符号上
deposit the formula in a cell
把配方放在细胞中
calculate the worksheet
计算工作表
display the result to the user
向用户显示结果
close the instance of Excel

This would require Excel to be installed or OneDrive to be accessible.
关闭Excel实例这需要安装Excel或OneDrive才能访问。

#1