Hi.
I have the file test.xls in which I have the following cells:
A1=48 (number_payments)
A2=-200 (payment)
A3=8000 (PV or present value)
A4=RATE(A1;A2;A3)*12*100=9.24
Then I wrote the following class (using poi-3.8-beta4):
[omitted]...package...[omitted]...import...[omitted]
public class TestExcel {
public static void main(String[] args) {
try {
String fileName = "test.xls";
Map<String, Double> inputField= new HashMap<String, Double>();
inputField.put("A1", 48.0); inputField.put("A2", -200.0);
inputField.put("A3", 8000.0);
Map<String, Double> outputField= new HashMap<String, Double>();
outputField.put("A4", 0.0);
FileInputStream myInput = new FileInputStream(fileName);
Workbook myWorkBook = WorkbookFactory.create(myInput);
Sheet mySheet = myWorkBook.getSheetAt(0);
for (String cell : campiInput.keySet()){
CellReference cr = new CellReference(cell); int row =
cr.getRow(); int column = cr.getCol();
Row myRow = mySheet.getRow(row); Cell myCell =
myRow.getCell(column);
myCell.setCellValue(inputField.get(cell));
}
String[] functionNames = { "RATE" };
FreeRefFunction[] functionImpls = { new CalculateRate() };
UDFFinder udfs = new DefaultUDFFinder( functionNames,
functionImpls );
UDFFinder udfToolpack = new AggregatingUDFFinder( udfs );
myWorkBook.addToolPack(udfToolpack);
FormulaEvaluator evaluator =
myWorkBook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateAll();
for (String cell : outputField.keySet()){
CellReference cr = new CellReference(cell); int riga =
cr.getRow(); int colonna = cr.getCol();
Row myRow = mySheet.getRow(row); Cell myCell =
myRow.getCell(column);
outputField.put(cell, myCell.getNumericCellValue());
}
for (String cell : outputField.keySet()){
System.out.println(cell+": "+outputField.get(cell));
}
myInput.close();
} catch (FileNotFoundException e) {
System.out.println(e.getMessage());
} catch (IOException e) {
System.out.println(e.getMessage());
} catch (InvalidFormatException e) {
System.out.println(e.getMessage());
}
}
}
Here is also the code to implement the excel function to calculate the rate:
[omitted]...package...[omitted]...import...[omitted]
public class CalculateRate implements FreeRefFunction {
public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext
ec ) {
if (args.length < 3) { //First 3 parameters are mandatory
return ErrorEval.VALUE_INVALID;
}
double periods, payment, present_val, val_future_val = 0, type = 0,
estimate = 0.1, rate;
try {
ValueEval v1 = OperandResolver.getSingleValue(args[0],
ec.getRowIndex(), ec.getColumnIndex());
ValueEval v2 = OperandResolver.getSingleValue(args[1],
ec.getRowIndex(), ec.getColumnIndex());
ValueEval v3 = OperandResolver.getSingleValue(args[2],
ec.getRowIndex(), ec.getColumnIndex());
ValueEval v4 = null;
if (args.length >= 4)
v4 = OperandResolver.getSingleValue(args[3],
ec.getRowIndex(), ec.getColumnIndex());
ValueEval v5 = null;
if (args.length >= 5)
v5 = OperandResolver.getSingleValue(args[4],
ec.getRowIndex(), ec.getColumnIndex());
ValueEval v6 = null;
if (args.length >= 6)
v6 = OperandResolver.getSingleValue(args[5],
ec.getRowIndex(), ec.getColumnIndex());
periods = OperandResolver.coerceValueToDouble(v1);
payment = OperandResolver.coerceValueToDouble(v2);
present_val = OperandResolver.coerceValueToDouble(v3);
if (args.length >= 4)
future_val = OperandResolver.coerceValueToDouble(v4);
if (args.length >= 5)
type = OperandResolver.coerceValueToDouble(v5);
if (args.length >= 6)
estimate = OperandResolver.coerceValueToDouble(v6);
rate = calculateRate(periods , payment, present_val, future_val,
type, estimate) ;
checkValue(rate);
} catch (EvaluationException e) {
e.printStackTrace() ;
return e.getErrorEval();
}
return new NumberEval( rate ) ;
}
private double calculateRate(double nper, double pmt, double pv, double
fv, double type, double guess) {
int FINANCIAL_MAX_ITERATIONS = 20;
double FINANCIAL_PRECISION = 0.0000001;
double y, y0, y1, x0, x1 = 0, f = 0, i = 0;
double rate = guess;
if (Math.abs(rate) < FINANCIAL_PRECISION) {
y = pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper +
fv;
} else {
f = Math.exp(nper * Math.log(1 + rate));
y = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
}
y0 = pv + pmt * nper + fv;
y1 = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
// find root by Newton secant method
i = x0 = 0.0;
x1 = rate;
while ((Math.abs(y0 - y1) > FINANCIAL_PRECISION) && (i <
FINANCIAL_MAX_ITERATIONS)) {
rate = (y1 * x0 - y0 * x1) / (y1 - y0);
x0 = x1;
x1 = rate;
if (Math.abs(rate) < FINANCIAL_PRECISION) {
y = pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper
+ fv;
} else {
f = Math.exp(nper * Math.log(1 + rate));
y = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
}
y0 = y1;
y1 = y;
++i;
}
return rate;
}
/**
* Excel does not support infinities and NaNs, rather, it gives a #NUM!
error in these cases
*
* @throws EvaluationException (#NUM!) if <tt>result</tt> is <tt>NaN</>
or <tt>Infinity</tt>
*/
static final void checkValue(double result) throws EvaluationException
{
if (Double.isNaN(result) || Double.isInfinite(result)) {
throw new EvaluationException(ErrorEval.NUM_ERROR);
}
}
}
So my expectation was to have the output
A4: 9.24
instead of the error message in the subject
I did another test using an excel file and 2 user defined VBA functions and
their java implementation with the same approach above described and it
worked PERFECTLY. If I try my own implementation for the standard function
RATE I always get the error message in the subject. So it seems to me that
internal excel function like RATE cannot be overwritten with an own java
implementation if not implemented in the poi library.
Is this right? Is there any way to use my implementation for the excel
function RATE ?
Thanks in advance,
Carlo.
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Caused-by-org-apache-poi-ss-formula-eval-NotImplementedException-RATE-tp4914060p4914060.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
