public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1)
{
double result;
try
{
double d0 = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
double d1 = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
double multi = Math.Pow(10d, d1);
if (d0 < 0)
{
result = -Math.Floor(-d0 * multi) / multi;
}
else
{
result = Math.Floor(d0 * multi) / multi;
}
//result = Math.Floor(d0 * multi) / multi;
NumericFunction.CheckValue(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(new NumberEval(result));
}
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex)
{
int nArgs = args.Length;
if (nArgs < 2)
{
return(ErrorEval.VALUE_INVALID);
}
try
{
double rate = NumericFunction.SingleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
// convert tail arguments into an array of doubles
ValueEval[] vargs = new ValueEval[args.Length - 1];
Array.Copy(args, 1, vargs, 0, vargs.Length);
double[] values = AggregateFunction.ValueCollector.CollectValues(vargs);
double result = FinanceLib.npv(rate, values);
NumericFunction.CheckValue(result);
return(new NumberEval(result));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval serialNumVE, ValueEval returnTypeVE)
{
double serialNum = 0.0;
try
{
serialNum = NumericFunction.SingleOperandEvaluate(serialNumVE, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(ErrorEval.VALUE_INVALID);
}
//Calendar serialNumCalendar = new GregorianCalendar();
//serialNumCalendar.setTime(DateUtil.GetJavaDate(serialNum, false));
DateTime serialNumCalendar = DateUtil.GetJavaDate(serialNum, false);
int returnType = 0;
try
{
ValueEval ve = OperandResolver.GetSingleValue(returnTypeVE, srcRowIndex, srcColumnIndex);
returnType = OperandResolver.CoerceValueToInt(ve);
}
catch (EvaluationException e)
{
return(ErrorEval.NUM_ERROR);
}
if (returnType != 1 && returnType != 2)
{
return(ErrorEval.NUM_ERROR);
}
return(new NumberEval(this.getWeekNo(serialNumCalendar, returnType)));
}
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex)
{
if (args.Length == 0 || args.Length > 2)
{
// Wrong number of arguments
return(ErrorEval.VALUE_INVALID);
}
try
{
double[] values = AggregateFunction.ValueCollector.CollectValues(args[0]);
double guess;
if (args.Length == 2)
{
guess = NumericFunction.SingleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
}
else
{
guess = 0.1d;
}
double result = irr(values, guess);
NumericFunction.CheckValue(result);
return(new NumberEval(result));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
double result;
if (args.Length != 2)
{
return(ErrorEval.VALUE_INVALID);
}
try
{
double startDateAsNumber = GetValue(args[0]);
int offsetInMonthAsNumber = (int)GetValue(args[1]);
// resolve the arguments
DateTime startDate = DateUtil.GetJavaDate(startDateAsNumber);
DateTime resultDate = startDate.AddMonths(offsetInMonthAsNumber);
result = DateUtil.GetExcelDate(resultDate);
NumericFunction.CheckValue(result);
return(new NumberEval(result));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0,
ValueEval arg1)
{
double dn;
try
{
ValueEval ve1 = OperandResolver.GetSingleValue(arg1, srcRowIndex, srcColumnIndex);
dn = OperandResolver.CoerceValueToDouble(ve1);
}
catch (EvaluationException e1)
{
// all errors in the second arg translate to #VALUE!
return(ErrorEval.VALUE_INVALID);
}
if (dn < 0 || dn > 1)
{ // has to be percentage
return(ErrorEval.NUM_ERROR);
}
double result;
try
{
double[] ds = NPOI.SS.Formula.Functions.AggregateFunction.ValueCollector.CollectValues(arg0);
int N = ds.Length;
if (N == 0 || N > 8191)
{
return(ErrorEval.NUM_ERROR);
}
double n = (N - 1) * dn + 1;
if (n == 1d)
{
result = StatsLib.kthSmallest(ds, 1);
}
else if (n == N)
{
result = StatsLib.kthLargest(ds, 1);
}
else
{
int k = (int)n;
double d = n - k;
result = StatsLib.kthSmallest(ds, k) + d
* (StatsLib.kthSmallest(ds, k + 1) - StatsLib.kthSmallest(ds, k));
}
NumericFunction.CheckValue(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(new NumberEval(result));
}
protected double Eval(ValueEval[] args, int srcCellRow, short srcCellCol)
{
if (args.Length != 1)
{
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
double d = NumericFunction.SingleOperandEvaluate(args[0], srcCellRow, srcCellCol);
return(Evaluate(d));
}
private bool CheckArgument(double aDouble)
{
NumericFunction.CheckValue(aDouble);
// make sure that the number is positive
if (aDouble < 0)
{
throw new EvaluationException(ErrorEval.NUM_ERROR);
}
return(true);
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1)
{
try
{
double d0 = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
double d1 = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
return(new NumberEval(Evaluate(d0, d1, false)));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval serialNumVE, ValueEval returnTypeVE)
{
double serialNum = 0.0;
try
{
serialNum = NumericFunction.SingleOperandEvaluate(serialNumVE, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException)
{
return(ErrorEval.VALUE_INVALID);
}
DateTime serialNumCalendar;
try
{
serialNumCalendar = DateUtil.GetJavaDate(serialNum, false);
}
catch (Exception)
{
return(ErrorEval.NUM_ERROR);
}
int returnType = 0;
try
{
ValueEval ve = OperandResolver.GetSingleValue(returnTypeVE, srcRowIndex, srcColumnIndex);
returnType = OperandResolver.CoerceValueToInt(ve);
if (ve is MissingArgEval)
{
returnType = (int)DEFAULT_RETURN_TYPE.NumberValue;
}
else
{
returnType = OperandResolver.CoerceValueToInt(ve);
}
}
catch (EvaluationException)
{
return(ErrorEval.NUM_ERROR);
}
if (!VALID_RETURN_TYPES.Contains(returnType))
{
return(ErrorEval.NUM_ERROR);
}
return(new NumberEval(this.getWeekNo(serialNumCalendar, returnType)));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2)
{
try
{
double d0 = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
double d1 = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
ValueEval ve = OperandResolver.GetSingleValue(arg2, srcRowIndex, srcColumnIndex);
bool? method = OperandResolver.CoerceValueToBoolean(ve, false);
return(new NumberEval(Evaluate(d0, d1, method != null && (bool)method)));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0)
{
double result;
try
{
double d0 = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
result = Math.Log(d0) / NumericFunction.LOG_10_TO_BASE_e;
NumericFunction.CheckValue(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(new NumberEval(result));
}
public ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1)
{
double result;
try
{
double rate = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
double d1 = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
result = Evaluate(rate, d1);
NumericFunction.CheckValue(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(new NumberEval(result));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2)
{
double result;
try
{
double d0 = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
double d1 = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
double d2 = NumericFunction.SingleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex);
result = Evaluate(GetYear(d0), (int)d1, (int)d2);
NumericFunction.CheckValue(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(new NumberEval(result));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2)
{
// arguments/result for this function
double mean = 0;
double x = 0;
bool cumulative = ((BoolEval)arg2).BooleanValue;
double result = 0;
try
{
x = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
mean = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
// check for default result : excel implementation for 0,0
// is different to Math Common.
if (IsDefaultResult(x, mean))
{
return(new NumberEval(DEFAULT_RETURN_RESULT));
}
// check the arguments : as per excel function def
CheckArgument(x);
CheckArgument(mean);
// truncate x : as per excel function def
if (cumulative)
{
result = cumulativeProbability((int)x, mean);
}
else
{
result = probability((int)x, mean);
}
// check the result
NumericFunction.CheckValue(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(new NumberEval(result));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0,
ValueEval arg1)
{
double dn;
try
{
ValueEval ve1 = OperandResolver.GetSingleValue(arg1, srcRowIndex, srcColumnIndex);
dn = OperandResolver.CoerceValueToDouble(ve1);
}
catch (EvaluationException e1)
{
// all errors in the second arg translate to #VALUE!
return(ErrorEval.VALUE_INVALID);
}
// weird Excel behaviour on second arg
if (dn < 1.0)
{
// values between 0.0 and 1.0 result in #NUM!
return(ErrorEval.NUM_ERROR);
}
// all other values are rounded up to the next integer
int k = (int)Math.Ceiling(dn);
double result;
try
{
double[] ds = NPOI.SS.Formula.Functions.AggregateFunction.ValueCollector.CollectValues(arg0);
if (k > ds.Length)
{
return(ErrorEval.NUM_ERROR);
}
result = _isLarge ? StatsLib.kthLargest(ds, k) : StatsLib.kthSmallest(ds, k);
NumericFunction.CheckValue(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(new NumberEval(result));
}
public ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
ValueEval arg2, ValueEval arg3, ValueEval arg4)
{
double result;
try
{
double d0 = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
double d1 = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
double d2 = NumericFunction.SingleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex);
double d3 = NumericFunction.SingleOperandEvaluate(arg3, srcRowIndex, srcColumnIndex);
double d4 = NumericFunction.SingleOperandEvaluate(arg4, srcRowIndex, srcColumnIndex);
result = Evaluate(d0, d1, d2, d3, d4 != 0.0);
NumericFunction.CheckValue(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(new NumberEval(result));
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length != 2)
{
return(ErrorEval.VALUE_INVALID);
}
try
{
double startDateAsNumber = NumericFunction.SingleOperandEvaluate(args[0], ec.RowIndex, ec.ColumnIndex);
int months = (int)NumericFunction.SingleOperandEvaluate(args[1], ec.RowIndex, ec.ColumnIndex);
// Excel treats date 0 as 1900-01-00; EOMONTH results in 1900-01-31
if (startDateAsNumber >= 0.0 && startDateAsNumber < 1.0)
{
startDateAsNumber = 1.0;
}
DateTime startDate = DateUtil.GetJavaDate(startDateAsNumber, false);
//the month
DateTime dtEnd = startDate.AddMonths(months);
//next month
dtEnd = dtEnd.AddMonths(1);
//first day of next month
dtEnd = new DateTime(dtEnd.Year, dtEnd.Month, 1);
//last day of the month
dtEnd = dtEnd.AddDays(-1);
return(new NumberEval(DateUtil.GetExcelDate(dtEnd)));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0,
ValueEval arg1)
{
double result;
try {
double d0 = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
double d1 = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
double logE = Math.Log(d0);
double base1 = d1;
if (base1 == Math.E) // TODO: Double.compare(base, Math.E) == 0, , DOSE THE "==" operator equals Double.compare
{
result = logE;
}
else
{
result = logE / Math.Log(base1);
}
NumericFunction.CheckValue(result);
} catch (EvaluationException e) {
return(e.GetErrorEval());
}
return(new NumberEval(result));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0)
{
if (arg0 is AreaEval)
{
double[] result = null;
double[,] resultArray;
int width = 1, height = 1;
try
{
double[] values = CollectValues(arg0);
double[,] array = fillDoubleArray(values, ((AreaEval)arg0).Height, ((AreaEval)arg0).Width);
resultArray = Evaluate(array);
width = resultArray.GetLength(1);
height = resultArray.GetLength(0);
result = extractDoubleArray(resultArray);
CheckValues(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
ValueEval[] vals = new ValueEval[result.Length];
for (int idx = 0; idx < result.Length; idx++)
{
vals[idx] = new NumberEval(result[idx]);
}
if (result.Length == 1)
{
return(vals[0]);
}
else
{
/* find a better solution */
return(new CacheAreaEval(((AreaEval)arg0).FirstRow, ((AreaEval)arg0).FirstColumn,
((AreaEval)arg0).FirstRow + height - 1,
((AreaEval)arg0).FirstColumn + width - 1, vals));
}
}
else
{
double[,] result = null;
try
{
double value = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
double[,] temp = { { value } };
result = Evaluate(temp);
NumericFunction.CheckValue(result[0, 0]);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(new NumberEval(result[0, 0]));
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1)
{
double[] result;
int width = 1, height = 1;
try
{
double[,] array0, array1, resultArray;
if (arg0 is AreaEval)
{
try
{
double[] values = CollectValues(arg0);
array0 = fillDoubleArray(values, ((AreaEval)arg0).Height, ((AreaEval)arg0).Width);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
else
{
try
{
double value = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
array0 = new double[, ] {
{ value }
};
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
if (arg1 is AreaEval)
{
try
{
double[] values = CollectValues(arg1);
array1 = fillDoubleArray(values, ((AreaEval)arg1).Height, ((AreaEval)arg1).Width);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
else
{
try
{
double value = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
array1 = new double[, ] {
{ value }
};
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
resultArray = Evaluate(array0, array1);
width = resultArray.GetLength(1);
height = resultArray.GetLength(0);
result = extractDoubleArray(resultArray);
CheckValues(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
catch (ArgumentException)
{
return(ErrorEval.VALUE_INVALID);
}
ValueEval[] vals = new ValueEval[result.Length];
for (int idx = 0; idx < result.Length; idx++)
{
vals[idx] = new NumberEval(result[idx]);
}
if (result.Length == 1)
{
return(vals[0]);
}
else
{
return(new CacheAreaEval(((AreaEval)arg0).FirstRow, ((AreaEval)arg0).FirstColumn,
((AreaEval)arg0).FirstRow + height - 1,
((AreaEval)arg0).FirstColumn + width - 1, vals));
}
}
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex,
int srcColumnIndex)
{
if (args.Length < 2 || args.Length > 5)
{
return(ErrorEval.VALUE_INVALID);
}
try
{
bool pAbsRow, pAbsCol;
int row = (int)NumericFunction.SingleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
int col = (int)NumericFunction.SingleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
int refType;
if (args.Length > 2 && args[2] != MissingArgEval.instance)
{
refType = (int)NumericFunction.SingleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
}
else
{
refType = REF_ABSOLUTE; // this is also the default if parameter is not given
}
switch (refType)
{
case REF_ABSOLUTE:
pAbsRow = true;
pAbsCol = true;
break;
case REF_ROW_ABSOLUTE_COLUMN_RELATIVE:
pAbsRow = true;
pAbsCol = false;
break;
case REF_ROW_RELATIVE_RELATIVE_ABSOLUTE:
pAbsRow = false;
pAbsCol = true;
break;
case REF_RELATIVE:
pAbsRow = false;
pAbsCol = false;
break;
default:
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
bool a1;
if (args.Length > 3)
{
ValueEval ve = OperandResolver.GetSingleValue(args[3], srcRowIndex, srcColumnIndex);
// TODO R1C1 style is not yet supported
a1 = ve == MissingArgEval.instance ? true : OperandResolver.CoerceValueToBoolean(ve, false).Value;
}
else
{
a1 = true;
}
String sheetName;
if (args.Length == 5)
{
ValueEval ve = OperandResolver.GetSingleValue(args[4], srcRowIndex, srcColumnIndex);
sheetName = ve == MissingArgEval.instance ? null : OperandResolver.CoerceValueToString(ve);
}
else
{
sheetName = null;
}
CellReference ref1 = new CellReference(row - 1, col - 1, pAbsRow, pAbsCol);
StringBuilder sb = new StringBuilder(32);
if (sheetName != null)
{
SheetNameFormatter.AppendFormat(sb, sheetName);
sb.Append('!');
}
sb.Append(ref1.FormatAsString());
return(new StringEval(sb.ToString()));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
