java 将电子表格公式转换为java
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2043614/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
convert spreadsheet formulas to java
提问by martinr
What tools are available to convert an OpenOffice or Excel spreadsheet (with all its formulas) into a Java object that can be called at run-time?
有哪些工具可以将 OpenOffice 或 Excel 电子表格（及其所有公式）转换为可在运行时调用的 Java 对象？
Obviously this would only make a calculation engine and just be about numbers and text, not timing or API calls.
显然，这只会创建一个计算引擎，并且只是关于数字和文本，而不是计时或 API 调用。
Even with named cell ranges being used to (effectively) name variables the output code would presumably be difficult to understand. It would need refactoring to get more like normal Java code. However I think it would be useful for prototyping some data processing type jobs. Or for embedding some calculation engines maintained by an advanced Excel user.
即使使用命名的单元格范围（有效地）命名变量，输出代码也可能难以理解。它需要重构以变得更像普通的 Java 代码。但是，我认为这对于设计一些数据处理类型的工作很有用。或者嵌入一些由高级 Excel 用户维护的计算引擎。
Edit: A trivial example:
编辑：一个简单的例子：
APPEARANCE
外貌
A B C D
1 Mortgage Value 100,000.00
2 Interest rate 4.5%
3 Type Interest-only
4 Years 3
5 Regular payment 4,500.00
6 Total interest 13,500.00
CELL NAMES
细胞名称
A B C D
1 Mortgage Value VALUE
2 Interest rate INTEREST
3 Type TYPE
4 Years YEARS
5 Regular payment REGPYMT
6 Total interest TOTALPYMT
FORMULAS
公式
A B C D
1 Mortgage Value 100,000.00
2 Interest rate 4.5%
3 Type Interest-only
4 Years 3
5 Regular payment =VALUE*INTEREST
6 Total interest =YEARS*REGPYMT
would translate into Java as something like:
将转换为 Java 如下：
package example.calcengine;
import java.math.*;
public class MyCalcEngine {
// unnamed cells
public String A1 = "Mortgage Value";
public String A2 = "Interest rate";
public String A3 = "Type";
public String A4 = "Years";
public String A5 = "Regular payment";
public String A6 = "Total interest";
// named cells
public BigDecimal VALUE = new BigDecimal(100000.00);
public BigDecimal INTEREST = new BigDecimal(0.045);
public String TYPE = "Interest-only";
public BigDecimal YEARS = new BigDecimal(3);
public BigDecimal REGPYMT = new BigDecimal(0);
public BigDecimal TOTALPYMT = new BigDecimal(0);
// formulas
public void calculate() {
REGPYMT = VALUE.multiply(INTEREST);
TOTALPYMT = REGPYMT.multiply(YEARS);
}
}
I'd assume fixed type for cells - either a java.math.BigDecimal or a String.
我假设单元格的固定类型 - java.math.BigDecimal 或字符串。
回答by Carl Smotricz
The Apache POI project provides Java code that can read (mostly) Excel spreadsheets.
Another project, Jacob, provides a Java interface for COM automation of arbitrary Windows programs, of course including Excel. You're essentially working Excel's API from the outside, in Java.
Apache POI 项目提供了可以读取（主要是）Excel 电子表格的 Java 代码。
另一个项目 Jacob 为任意 Windows 程序（当然包括 Excel）的 COM 自动化提供了 Java 接口。您实际上是在 Java 中从外部使用 Excel 的 API。
回答by torbjoernwh
JExcelApi lets you load a .xls file and read/write to cells.
JExcelApi 允许您加载 .xls 文件并读取/写入单元格。
回答by liya
We had done a similar project with SmartXLS for java. It had a runtime calculate engine and support add-in formulas.The source workbook is converted to a java class which can be embedded in java program and calls with parameters just like input in Excel.
我们用SmartXLS for java做了一个类似的项目。它有一个运行时计算引擎并支持插件公式。源工作簿被转换为一个java类，它可以嵌入到java程序中，并像在Excel中输入一样使用参数调用。
回答by Peter Smith
One more option is Expr4J. It implements a general purpose expression language parser that is compatible with Excel and has a dependency engine for calculating expression graphs. It also implements the vast majorit of Excel's built in functions.
另一种选择是Expr4J。它实现了与 Excel 兼容的通用表达式语言解析器，并具有用于计算表达式图的依赖引擎。它还实现了 Excel 的绝大多数内置函数。
From the website, an example is:
从网站上，一个例子是：
public class DependencyExample
{
public static void main(String[] args) throws Exception {
DependencyEngine e = new DependencyEngine(new BasicEngineProvider());
e.set("B1", "=A1*2");
e.set("A1", "=12*2");
e.set("C1", "=B1*A1");
System.out.println(e.getValue(Range.valueOf("B1")));
System.out.println(e.getValue(Range.valueOf("C1")));
e.set("A1", "2");
System.out.println(e.getValue(Range.valueOf("B1")));
System.out.println(e.getValue(Range.valueOf("C1")));
}
}
(I work on the project so would obviously be interested in any feedback)
（我在这个项目上工作，所以显然会对任何反馈感兴趣）
