在XIRR功能中设置的Excel中读取单元格时遇到的问题。
我用Java编写了代码。下面是设置公式的代码。请帮助我如何从单元格而不是公式中读取值。
cell.setCellFormula("XIRR(E2:E10, B2:B10");
CellStyle style = workbook.createCellStyle();
style.setDataFormat(workbook.createDataFormat().getFormat("0.00%"));
cell.setCellStyle(style);
以下是使用 FormulaEvaluator 评估单元格时出现的错误
org.apache.poi.ss.formula.eval.NotImplementedFunctionException: XIRR
at org.apache.poi.ss.formula.atp.AnalysisToolPak$NotImplemented.evaluate(AnalysisToolPak.java:59)
at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:61)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:550)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:317)
... 18 more
最佳答案
在不使用XIRR函数修补apache poi的情况下，可以使用apache poi中的XIRR function直接计算类似Excel的User Defined Function的结果。
以下代码正是提供了这一点。
它定义了一个CalculateXIRR类，然后将其用作apache poi中的myXIRR函数。 CalculateXIRR使用JXIRR - v1.0.0(C)2005 Gautam Satpathy或从java program to calculate XIRR without using excel or any other library派生的Xirr类来计算XIRR。
它还提供了测试用例的代码。首先，该测试用例与Excel的XIRR文档中的示例相同。然后使用随机值和日期进行随机测试。这些测试用例将写入Excel工作簿中。编写的结果是对用户定义的myXIRR函数以及Excel原始XIRR函数的评估结果。这样我们就可以比较结果了。
我的测试表明，两种XIRR计算方法都非常像Excel，使用合理的值和日期。两种方法仅使用导致Excel XIRR较高的负百分比(小于-60％)或非常高的百分比(大于1000％)的值和日期，这两种方法都不同于Excel。
来自Gautam Satpathy的JXIRR - v1.0.0更适合作为Excel的Xirr类。原因很清楚，因为如果Xirr中的x低于Math.pow((x + 1d), (dt0-dt) / 365d)，则-1d类将始终失败。如果是这样，则Math.pow函数的基数为负，并且由于指数(dt0-dt) / 365d)是分数，因此只有一种虚构的解。如果Excel的XIRR导致较高的负百分比，并且近似值尝试低于-100％，则会发生这种情况。 JXIRR使用一种目标搜索方法，该方法似乎更像Excel本身之一。
码:
import java.io.* ;
import org.apache.poi.ss.formula.functions.* ;
import org.apache.poi.ss.formula.udf.* ;
import org.apache.poi.ss.usermodel.* ;
import org.apache.poi.xssf.usermodel.* ;
import org.apache.poi.ss.formula.* ;
import org.apache.poi.ss.formula.eval.* ;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.util.Random;
/*
https://github.com/ept/jxirr
(C) 2005 Gautam Satpathy
*/
import in.satpathy.financial.*;
public class XIRREvaluator {
private Workbook workbook;
private Sheet sheet;
private Row row;
private Cell cell;
private CellStyle percentStyle;
private CellStyle dateStyle;
private FormulaEvaluator evaluator;
private String[] labels;
private char c1;
private char c2;
private String[] formulas;
private Double[] values;
private SimpleDateFormat sdf;
private Date[] dates;
public XIRREvaluator() {
this.workbook = new XSSFWorkbook();
String[] functionNames = { "myXIRR" } ;
FreeRefFunction[] functionImpls = { new CalculateXIRR() } ;
UDFFinder udfs = new DefaultUDFFinder( functionNames, functionImpls ) ;
UDFFinder udfToolpack = new AggregatingUDFFinder( udfs ) ;
workbook.addToolPack(udfToolpack);
this.percentStyle = workbook.createCellStyle();
percentStyle.setDataFormat(workbook.createDataFormat().getFormat("0.00%"));
this.dateStyle = workbook.createCellStyle();
dateStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd"));
this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
this.sheet = workbook.createSheet("Sheet1");
this.labels = new String[]{"XIRR", "myXIRR", "diff"};
this.sdf = new SimpleDateFormat("yyyy-MM-dd");
}
public void save() {
try {
workbook.write(new FileOutputStream("ExcelWorkbookXIRR.xlsx"));
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private void testCaseFromExcelDocu(int startCol, int startRow) {
/*
This provides a test case as from the example in Excel's XIRR documentation:
https://support.office.com/en-us/article/XIRR-function-de1242ec-6477-445b-b11b-a303ad9adc9d
*/
if (startCol > 24) return;
try {
c1 = (char)(65+startCol);
c2 = (char)(65+startCol+1);
formulas = new String[]{"XIRR("+c1+(startRow+4)+":"+c1+(startRow+8)+","+c2+(startRow+4)+":"+c2+(startRow+8)+")",
"myXIRR("+c1+(startRow+4)+":"+c1+(startRow+8)+","+c2+(startRow+4)+":"+c2+(startRow+8)+")",
""+c2+(startRow+1)+"-"+c2+(startRow+2)};
values = new Double[]{-10000d, 2750d, 4250d, 3250d, 2750d};
dates = new Date[]{sdf.parse("2008-01-01"), sdf.parse("2008-03-01"), sdf.parse("2008-10-30"), sdf.parse("2009-02-15"), sdf.parse("2009-04-01")};
for (int r = startRow; r < startRow+3; r++) {
row = (sheet.getRow(r)==null)?sheet.createRow(r):sheet.getRow(r);
cell = row.createCell(startCol);
cell.setCellValue(labels[r-startRow]);
}
for (int r = startRow+3; r < startRow+8; r++) {
row = (sheet.getRow(r)==null)?sheet.createRow(r):sheet.getRow(r);
cell = row.createCell(startCol);
cell.setCellValue(values[r-startRow-3]);
cell = row.createCell(startCol+1);
cell.setCellValue(dates[r-startRow-3]);
cell.setCellStyle(dateStyle);
}
for (int r = startRow; r < startRow+2; r++) {
cell = sheet.getRow(r).createCell(startCol+1);
cell.setCellFormula(formulas[r-startRow]);
cell.setCellStyle(percentStyle);
if (r == startRow+1) {
cell = evaluator.evaluateInCell(cell);
System.out.println(new DataFormatter().formatCellValue(cell));
}
}
cell = sheet.getRow(startRow+2).createCell(startCol+1);
cell.setCellFormula(formulas[2]);
sheet.autoSizeColumn(startCol);
sheet.autoSizeColumn(startCol+1);
} catch (Exception e) {
e.printStackTrace();
}
}
private void randomTestCases(int startCol, int startRow, int count) {
/*
This provides randon test cases
*/
try {
long day = 24L*60L*60L*1000L;
long startDate = sdf.parse("2010-01-01").getTime();
for (int test = startCol; test < startCol+3*count; test+=3) {
if (test > 24) return;
c1 = (char)(65+test);
c2 = (char)(65+test+1);
Random rnd = new Random();
int rows = 5+rnd.nextInt(5);
formulas = new String[]{"XIRR("+c1+(startRow+4)+":"+c1+(startRow+3+rows)+","+c2+(startRow+4)+":"+c2+(startRow+3+rows)+")",
"myXIRR("+c1+(startRow+4)+":"+c1+(startRow+3+rows)+", "+c2+(startRow+4)+":"+c2+(startRow+3+rows)+")",
""+c2+(startRow+1)+"-"+c2+(startRow+2)};
values = new Double[rows];
values[0] = -1d*(rows-1d)*(1000+rnd.nextInt(5000));
for (int i = 1; i < rows; i++) {
values[i] = 1d*(1000+rnd.nextInt(5000));
}
dates = new Date[rows];
for (int i = 0; i < rows; i++) {
dates[i] = sdf.parse(sdf.format(new Date(startDate+=day*(1L+rnd.nextInt(150)))));
}
for (int r = startRow; r < startRow+3; r++) {
row = (sheet.getRow(r)==null)?sheet.createRow(r):sheet.getRow(r);
cell = row.createCell(test);
cell.setCellValue(labels[r-startRow]);
}
for (int r = startRow+3; r < startRow+3+rows; r++) {
row = (sheet.getRow(r)==null)?sheet.createRow(r):sheet.getRow(r);
cell = row.createCell(test);
cell.setCellValue(values[r-startRow-3]);
cell = row.createCell(test+1);
cell.setCellValue(dates[r-startRow-3]);
cell.setCellStyle(dateStyle);
}
for (int r = startRow; r < startRow+2; r++) {
cell = sheet.getRow(r).createCell(test+1);
cell.setCellFormula(formulas[r-startRow]);
cell.setCellStyle(percentStyle);
if (r == startRow+1) {
evaluator.clearAllCachedResultValues();
cell = evaluator.evaluateInCell(cell);
System.out.println(new DataFormatter().formatCellValue(cell));
}
}
cell = sheet.getRow(startRow+2).createCell(test+1);
cell.setCellFormula(formulas[2]);
sheet.autoSizeColumn(test);
sheet.autoSizeColumn(test+1);
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main( String[] args ) {
XIRREvaluator xirrEvaluator = new XIRREvaluator();
//test case as from the example in Excel's XIRR documentation
//starting on column 0, row 0
xirrEvaluator.testCaseFromExcelDocu(0,0);
//9 random test cases
//starting on column 0, row 10
xirrEvaluator.randomTestCases(0,10,9);
//9 random test cases
//starting on column 0, row 25
xirrEvaluator.randomTestCases(0,25,9);
xirrEvaluator.save();
}
}
/*
Class for user defined function myXIRR
*/
class CalculateXIRR implements FreeRefFunction {
@Override
public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {
if (args.length < 2 || args.length > 3) {
return ErrorEval.VALUE_INVALID;
}
double result;
try {
double[] values = ValueCollector.collectValues(args[0]);
double[] dates = ValueCollector.collectValues(args[1]);
double guess;
if(args.length == 3) {
ValueEval v = OperandResolver.getSingleValue(args[2], ec.getRowIndex(), ec.getColumnIndex()) ;
guess = OperandResolver.coerceValueToDouble(v);
} else {
guess = 0.1d;
}
result = calculateXIRR( values, dates, guess ) ;
checkValue(result);
} catch (EvaluationException e) {
//e.printStackTrace();
return e.getErrorEval();
}
return new NumberEval( result ) ;
}
public double calculateXIRR(double[] values, double[] dates, double guess ) {
double result;
/*
Either calculating XIRR using https://github.com/ept/jxirr (C) 2005 Gautam Satpathy
*/
XIRRData data = new XIRRData(values.length, guess, values, dates);
result = XIRR.xirr(data) - 1d;
/*
Or calculating XIRR Class Xirr
from https://stackoverflow.com/questions/36789967/java-program-to-calculate-xirr-without-using-excel-or-any-other-library
*/
//result = Xirr.Newtons_method(guess, values, dates);
return result;
}
static final void checkValue(double result) throws EvaluationException {
if (Double.isNaN(result) || Double.isInfinite(result)) {
throw new EvaluationException(ErrorEval.NUM_ERROR);
}
}
static final class ValueCollector extends MultiOperandNumericFunction {
private static final ValueCollector instance = new ValueCollector();
public ValueCollector() {
super(false, false);
}
public static double[] collectValues(ValueEval...operands) throws EvaluationException {
return instance.getNumberArray(operands);
}
protected double evaluate(double[] values) {
throw new IllegalStateException("should not be called");
}
}
}
/*
Class Xirr from https://stackoverflow.com/questions/36789967/java-program-to-calculate-xirr-without-using-excel-or-any-other-library
*/
final class Xirr {
private static final double tol = 0.00000001;
private static double f_xirr(double p, double dt, double dt0, double x) {
double resf = p * Math.pow((x + 1d), (dt0-dt) / 365d);
return resf;
}
private static double df_xirr(double p, double dt, double dt0, double x) {
double resf = (1d / 365d) * (dt0-dt) * p * Math.pow((x + 1d), ((dt0-dt) / 365d) - 1d);
return resf;
}
private static double total_f_xirr(double[] payments, double[] days, double x) {
double resf = 0d;
for (int i = 0; i < payments.length; i++) {
resf = resf + f_xirr(payments[i], days[i], days[0], x);
}
return resf;
}
private static double total_df_xirr(double[] payments, double[] days, double x) {
double resf = 0d;
for (int i = 0; i < payments.length; i++) {
resf = resf + df_xirr(payments[i], days[i], days[0], x);
}
return resf;
}
public static double Newtons_method(double guess, double[] payments, double[] days) {
double x0 = guess;
double x1 = 0d;
double err = 1e+100;
while (err > tol) {
x1 = x0 - total_f_xirr(payments, days, x0) / total_df_xirr(payments, days, x0);
err = Math.abs(x1 - x0);
x0 = x1;
}
return x0;
}
}
关于java - Java中XIRR函数的公式评估器，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42422429/