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);
}
//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)
{
return(ErrorEval.NUM_ERROR);
}
if (returnType != 1 && returnType != 2)
{
return(ErrorEval.NUM_ERROR);
}
return(new NumberEval(getWeekNo(serialNumCalendar, returnType)));
}
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex)
{
int nInnerArgs = args.Length - 1; // -1: first arg is used to select from a basic aggregate function
if (nInnerArgs < 1)
{
return(ErrorEval.VALUE_INVALID);
}
Function innerFunc;
try
{
ValueEval ve = OperandResolver.GetSingleValue(args[0], srcRowIndex, srcColumnIndex);
int functionCode = OperandResolver.CoerceValueToInt(ve);
innerFunc = FindFunction(functionCode);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
ValueEval[] innerArgs = new ValueEval[nInnerArgs];
Array.Copy(args, 1, innerArgs, 0, nInnerArgs);
return(innerFunc.Evaluate(innerArgs, srcRowIndex, srcColumnIndex));
}
protected override double Eval(ValueEval[] args, int srcCellRow, int srcCellCol)
{
if (args.Length != 4)
{
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
double result;
ValueEval v1 = OperandResolver.GetSingleValue(args[0], srcCellRow, srcCellCol);
ValueEval v2 = OperandResolver.GetSingleValue(args[1], srcCellRow, srcCellCol);
ValueEval v3 = OperandResolver.GetSingleValue(args[2], srcCellRow, srcCellCol);
ValueEval v4 = OperandResolver.GetSingleValue(args[3], srcCellRow, srcCellCol);
double interestRate = OperandResolver.CoerceValueToDouble(v1);
int period = OperandResolver.CoerceValueToInt(v2);
int numberPayments = OperandResolver.CoerceValueToInt(v3);
double PV = OperandResolver.CoerceValueToDouble(v4);
result = Finance.IPMT(interestRate, period, numberPayments, PV);
CheckValue(result);
return(result);
}
private ValueEval doFixed(
ValueEval numberParam, ValueEval placesParam,
ValueEval skipThousandsSeparatorParam,
int srcRowIndex, int srcColumnIndex)
{
try
{
ValueEval numberValueEval =
OperandResolver.GetSingleValue(
numberParam, srcRowIndex, srcColumnIndex);
decimal number = (decimal)OperandResolver.CoerceValueToDouble(numberValueEval);
ValueEval placesValueEval =
OperandResolver.GetSingleValue(
placesParam, srcRowIndex, srcColumnIndex);
int places = OperandResolver.CoerceValueToInt(placesValueEval);
ValueEval skipThousandsSeparatorValueEval =
OperandResolver.GetSingleValue(
skipThousandsSeparatorParam, srcRowIndex, srcColumnIndex);
bool?skipThousandsSeparator =
OperandResolver.CoerceValueToBoolean(
skipThousandsSeparatorValueEval, false);
// Round number to respective places.
//number = number.SetScale(places, RoundingMode.HALF_UP);
if (places < 0)
{
number = number / (decimal)Math.Pow(10, -places);
number = Math.Round(number, 0);
number = number * (decimal)Math.Pow(10, -places);
}
else
{
number = Math.Round(number, places);
}
// Format number conditionally using a thousands separator.
/*NumberFormat nf = NumberFormat.GetNumberInstance(Locale.US);
* DecimalFormat formatter = (DecimalFormat)nf;
* formatter.setGroupingUsed(!skipThousandsSeparator);
* formatter.setMinimumFractionDigits(places >= 0 ? places : 0);
* formatter.setMaximumFractionDigits(places >= 0 ? places : 0);
* String numberString = formatter.Format(number);*/
//System.Globalization.CultureInfo culture = System.Globalization.CultureInfo.CurrentCulture;
string numberString = skipThousandsSeparator != null && skipThousandsSeparator.Value ?
number.ToString(places > 0 ? "F" + places : "F0")
: number.ToString(places > 0 ? "N" + places : "N0", System.Globalization.CultureInfo.InvariantCulture);
// Return the result as a StringEval.
return(new StringEval(numberString));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
private int EvalArg(ValueEval arg, int srcRowIndex, int srcColumnIndex)
{
if (arg == MissingArgEval.instance)
{
return(0);
}
ValueEval ev = OperandResolver.GetSingleValue(arg, srcRowIndex, srcColumnIndex);
// Excel silently tRuncates double values to integers
return(OperandResolver.CoerceValueToInt(ev));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval numberVE, ValueEval formVE)
{
int number = 0;
try
{
ValueEval ve = OperandResolver.GetSingleValue(numberVE, srcRowIndex, srcColumnIndex);
number = OperandResolver.CoerceValueToInt(ve);
}
catch (EvaluationException)
{
return(ErrorEval.VALUE_INVALID);
}
if (number < 0)
{
return(ErrorEval.VALUE_INVALID);
}
if (number > 3999)
{
return(ErrorEval.VALUE_INVALID);
}
if (number == 0)
{
return(new StringEval(""));
}
int form = 0;
try
{
ValueEval ve = OperandResolver.GetSingleValue(formVE, srcRowIndex, srcColumnIndex);
form = OperandResolver.CoerceValueToInt(ve);
}
catch (EvaluationException)
{
return(ErrorEval.NUM_ERROR);
}
if (form > 4 || form < 0)
{
return(ErrorEval.VALUE_INVALID);
}
String result = this.integerToRoman(number);
if (form == 0)
{
return(new StringEval(result));
}
return(new StringEval(MakeConcise(result, form)));
}
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex)
{
int nInnerArgs = args.Length - 1; // -1: first arg is used to select from a basic aggregate function
if (nInnerArgs < 1)
{
return(ErrorEval.VALUE_INVALID);
}
Function innerFunc;
try
{
ValueEval ve = OperandResolver.GetSingleValue(args[0], srcRowIndex, srcColumnIndex);
int functionCode = OperandResolver.CoerceValueToInt(ve);
innerFunc = FindFunction(functionCode);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
// ignore the first arg, this is the function-type, we check for the length above
IList <ValueEval> list = new List <ValueEval>(Arrays.AsList(args).GetRange(1, args.Length - 1));
IEnumerator <ValueEval> it = list.GetEnumerator();
// See https://support.office.com/en-us/article/SUBTOTAL-function-7b027003-f060-4ade-9040-e478765b9939
// "If there are other subtotals within ref1, ref2,... (or nested subtotals), these nested subtotals are ignored to avoid double counting."
// For array references it is handled in1 other evaluation steps, but we need to handle this here for references to subtotal-functions
IList <ValueEval> toRemove = new List <ValueEval>();
while (it.MoveNext())
{
ValueEval eval = it.Current;
if (eval is LazyRefEval)
{
LazyRefEval lazyRefEval = (LazyRefEval)eval;
if (lazyRefEval.IsSubTotal)
{
toRemove.Add(eval);
}
}
}
foreach (var x in toRemove)
{
list.Remove(x);
}
return(innerFunc.Evaluate(list.ToArray(), srcRowIndex, srcColumnIndex));
}
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)));
}
private ValueEval _evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex)
{
if (args.Length < 2)
{
return(ErrorEval.VALUE_INVALID);
}
LookupUtils.MatchMode matchMode = LookupUtils.MatchMode.ExactMatch;
if (args.Length > 2)
{
try
{
ValueEval matchModeValue = OperandResolver.GetSingleValue(args[2], srcRowIndex, srcColumnIndex);
int matchInt = OperandResolver.CoerceValueToInt(matchModeValue);
matchMode = LookupUtils.GetMatchMode(matchInt);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
catch (Exception e)
{
return(ErrorEval.VALUE_INVALID);
}
}
LookupUtils.SearchMode searchMode = LookupUtils.SearchMode.IterateForward;
if (args.Length > 3)
{
try
{
ValueEval searchModeValue = OperandResolver.GetSingleValue(args[3], srcRowIndex, srcColumnIndex);
int searchInt = OperandResolver.CoerceValueToInt(searchModeValue);
searchMode = LookupUtils.GetSearchMode(searchInt);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
catch (Exception e)
{
return(ErrorEval.VALUE_INVALID);
}
}
return(evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], matchMode, searchMode));
}
/**
* Processes the third argument to VLOOKUP, or HLOOKUP (<b>col_index_num</b>
* or <b>row_index_num</b> respectively).<br/>
* Sample behaviour:
* <table border="0" cellpAdding="1" cellspacing="2" summary="Sample behaviour">
* <tr><th>Input Return</th><th>Value </th><th>Thrown Error</th></tr>
* <tr><td>5</td><td>4</td><td> </td></tr>
* <tr><td>2.9</td><td>2</td><td> </td></tr>
* <tr><td>"5"</td><td>4</td><td> </td></tr>
* <tr><td>"2.18e1"</td><td>21</td><td> </td></tr>
* <tr><td>"-$2"</td><td>-3</td><td>*</td></tr>
* <tr><td>FALSE</td><td>-1</td><td>*</td></tr>
* <tr><td>TRUE</td><td>0</td><td> </td></tr>
* <tr><td>"TRUE"</td><td> </td><td>#REF!</td></tr>
* <tr><td>"abc"</td><td> </td><td>#REF!</td></tr>
* <tr><td>""</td><td> </td><td>#REF!</td></tr>
* <tr><td><blank></td><td> </td><td>#VALUE!</td></tr>
* </table><br/>
*
* * Note - out of range errors (both too high and too low) are handled by the caller.
* @return column or row index as a zero-based value
*
*/
public static int ResolveRowOrColIndexArg(ValueEval rowColIndexArg, int srcCellRow, int srcCellCol)
{
if (rowColIndexArg == null)
{
throw new ArgumentException("argument must not be null");
}
ValueEval veRowColIndexArg;
try
{
veRowColIndexArg = OperandResolver.GetSingleValue(rowColIndexArg, srcCellRow, (short)srcCellCol);
}
catch (EvaluationException)
{
// All errors get translated to #REF!
throw EvaluationException.InvalidRef();
}
int oneBasedIndex;
if (veRowColIndexArg is StringEval)
{
StringEval se = (StringEval)veRowColIndexArg;
string strVal = se.StringValue;
Double dVal = OperandResolver.ParseDouble(strVal);
if (Double.IsNaN(dVal))
{
// String does not resolve to a number. Raise #REF! error.
throw EvaluationException.InvalidRef();
// This includes text booleans "TRUE" and "FALSE". They are not valid.
}
// else - numeric value parses OK
}
// actual BoolEval values get interpreted as FALSE->0 and TRUE->1
oneBasedIndex = OperandResolver.CoerceValueToInt(veRowColIndexArg);
if (oneBasedIndex < 1)
{
// note this is asymmetric with the errors when the index is too large (#REF!)
throw EvaluationException.InvalidValue();
}
return(oneBasedIndex - 1); // convert to zero based
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval numberVE)
{
int number;
try
{
number = OperandResolver.CoerceValueToInt(numberVE);
}
catch (EvaluationException)
{
return(ErrorEval.VALUE_INVALID);
}
if (number < 0)
{
return(ErrorEval.NUM_ERROR);
}
return(new NumberEval(factorial(number).LongValue()));
}
/**
* @param arg a 1-based index.
* @return the Resolved 1-based index. Zero if the arg was missing or blank
* @throws EvaluationException if the arg Is an error value evaluates to a negative numeric value
*/
private static int ResolveIndexArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
ValueEval ev = OperandResolver.GetSingleValue(arg, srcCellRow, srcCellCol);
if (ev == MissingArgEval.instance)
{
return(0);
}
if (ev == BlankEval.instance)
{
return(0);
}
int result = OperandResolver.CoerceValueToInt(ev);
if (result < 0)
{
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
return(result);
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2)
{
double result;
try
{
ValueEval ve = OperandResolver.GetSingleValue(arg0, srcRowIndex, srcColumnIndex);
result = OperandResolver.CoerceValueToDouble(ve);
if (Double.IsNaN(result) || Double.IsInfinity(result))
{
throw new EvaluationException(ErrorEval.NUM_ERROR);
}
bool order = false;
ve = OperandResolver.GetSingleValue(arg2, srcRowIndex, srcColumnIndex);
int order_value = OperandResolver.CoerceValueToInt(ve);
if (order_value == 0)
{
order = true;
}
else if (order_value == 1)
{
order = false;
}
else
{
throw new EvaluationException(ErrorEval.NUM_ERROR);
}
if (arg1 is RefListEval)
{
return(eval(result, ((RefListEval)arg1), order));
}
AreaEval aeRange = ConvertRangeArg(arg1);
return(eval(result, aeRange, order));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
public ValueEval Evaluate(ValueEval[] operands, int srcCellRow, int srcCellCol)
{
if (operands.Length != 1)
{
return(ErrorEval.VALUE_INVALID);
}
int val;
try
{
ValueEval ve = OperandResolver.GetSingleValue(operands[0], srcCellRow, srcCellCol);
val = OperandResolver.CoerceValueToInt(ve);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
if (val < 0)
{
return(ErrorEval.NUM_ERROR);
}
return(new NumberEval(GetCalField(val)));
}
private static int EvaluateIntArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
ValueEval ve = OperandResolver.GetSingleValue(arg, srcCellRow, srcCellCol);
return(OperandResolver.CoerceValueToInt(ve));
}
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex)
{
if (args.Length < 2)
{
return(ErrorEval.VALUE_INVALID);
}
double x;
try
{
ValueEval ev = OperandResolver.GetSingleValue(args[1], srcRowIndex, srcColumnIndex);
x = OperandResolver.CoerceValueToDouble(ev);
}
catch (EvaluationException e)
{
ValueEval error = e.GetErrorEval();
if (error == ErrorEval.NUM_ERROR)
{
return(error);
}
return(ErrorEval.NUM_ERROR);
}
List <Double> numbers = new List <double>();
try
{
List <ValueEval> values = getValues(args[0], srcRowIndex, srcColumnIndex);
foreach (ValueEval ev in values)
{
if (ev is BlankEval || ev is MissingArgEval)
{
//skip
}
else
{
numbers.Add(OperandResolver.CoerceValueToDouble(ev));
}
}
}
catch (EvaluationException e)
{
ValueEval error = e.GetErrorEval();
if (error != ErrorEval.NA)
{
return(error);
}
return(ErrorEval.NUM_ERROR);
}
if (numbers.Count == 0)
{
return(ErrorEval.NUM_ERROR);
}
int significance = 3;
if (args.Length > 2)
{
try
{
ValueEval ev = OperandResolver.GetSingleValue(args[2], srcRowIndex, srcColumnIndex);
significance = OperandResolver.CoerceValueToInt(ev);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
return(calculateRank(numbers, x, significance, true));
}
public static int EvaluateFirstArg(ValueEval arg0, int srcRowIndex, int srcColumnIndex)
{
ValueEval ev = OperandResolver.GetSingleValue(arg0, srcRowIndex, srcColumnIndex);
return(OperandResolver.CoerceValueToInt(ev));
}
private ValueEval _evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex, bool isSingleValue)
{
if (args.Length < 3)
{
return(ErrorEval.VALUE_INVALID);
}
String notFound = null;
if (args.Length > 3)
{
try
{
ValueEval notFoundValue = OperandResolver.GetSingleValue(args[3], srcRowIndex, srcColumnIndex);
String notFoundText = LaxValueToString(notFoundValue);
if (notFoundText != null)
{
String trimmedText = notFoundText.Trim();
if (trimmedText.Length > 0)
{
notFound = trimmedText;
}
}
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
LookupUtils.MatchMode matchMode = LookupUtils.MatchMode.ExactMatch;
if (args.Length > 4)
{
try
{
ValueEval matchModeValue = OperandResolver.GetSingleValue(args[4], srcRowIndex, srcColumnIndex);
int matchInt = OperandResolver.CoerceValueToInt(matchModeValue);
matchMode = LookupUtils.GetMatchMode(matchInt);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
catch (Exception)
{
return(ErrorEval.VALUE_INVALID);
}
}
LookupUtils.SearchMode searchMode = LookupUtils.SearchMode.IterateForward;
if (args.Length > 5)
{
try
{
ValueEval searchModeValue = OperandResolver.GetSingleValue(args[5], srcRowIndex, srcColumnIndex);
int searchInt = OperandResolver.CoerceValueToInt(searchModeValue);
searchMode = LookupUtils.GetSearchMode(searchInt);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
catch (Exception)
{
return(ErrorEval.VALUE_INVALID);
}
}
return(evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], notFound, matchMode, searchMode, isSingleValue));
}
/* for Var1or2ArgFunction:
* @Override
* public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
* }
*
* @Override
* public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
* }
*/
/**
* Perform WEEKDAY(date, returnOption) function.
* Note: Parameter texts are from German EXCEL-2010 help.
* Parameters in args[]:
* args[0] serialDate
* EXCEL-date value
* Standardmaessig ist der 1. Januar 1900 die fortlaufende Zahl 1 und
* der 1. Januar 2008 die fortlaufende Zahl 39.448, da dieser Tag nach 39.448 Tagen
* auf den 01.01.1900 folgt.
* @return Option (optional)
* Bestimmt den Rueckgabewert:
* 1 oder nicht angegeben Zahl 1 (Sonntag) bis 7 (Samstag). Verhaelt sich wie fruehere Microsoft Excel-Versionen.
* 2 Zahl 1 (Montag) bis 7 (Sonntag).
* 3 Zahl 0 (Montag) bis 6 (Sonntag).
* 11 Die Zahlen 1 (Montag) bis 7 (Sonntag)
* 12 Die Zahlen 1 (Dienstag) bis 7 (Montag)
* 13 Die Zahlen 1 (Mittwoch) bis 7 (Dienstag)
* 14 Die Zahlen 1 (Donnerstag) bis 7 (Mittwoch)
* 15 Die Zahlen 1 (Freitag) bis 7 (Donnerstag)
* 16 Die Zahlen 1 (Samstag) bis 7 (Freitag)
* 17 Die Zahlen 1 (Sonntag) bis 7 (Samstag)
*/
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex)
{
try
{
if (args.Length < 1 || args.Length > 2)
{
return(ErrorEval.VALUE_INVALID);
}
// extract first parameter
ValueEval serialDateVE = OperandResolver.GetSingleValue(args[0], srcRowIndex, srcColumnIndex);
double serialDate = OperandResolver.CoerceValueToDouble(serialDateVE);
if (!DateUtil.IsValidExcelDate(serialDate))
{
return(ErrorEval.NUM_ERROR); // EXCEL uses this and no VALUE_ERROR
}
DateTime date = DateUtil.GetJavaCalendar(serialDate, false); // (XXX 1904-windowing not respected)
int weekday = (int)date.DayOfWeek; // => sunday = 1, monday = 2, ..., saturday = 7
// extract second parameter
int returnOption = 1; // default value
if (args.Length == 2)
{
ValueEval ve = OperandResolver.GetSingleValue(args[1], srcRowIndex, srcColumnIndex);
if (ve == MissingArgEval.instance || ve == BlankEval.instance)
{
return(ErrorEval.NUM_ERROR); // EXCEL uses this and no VALUE_ERROR
}
returnOption = OperandResolver.CoerceValueToInt(ve);
if (returnOption == 2)
{
returnOption = 11; // both mean the same
}
} // if
// perform calculation
double result;
if (returnOption == 1)
{
result = weekday;
// value 2 is handled above (as value 11)
}
else if (returnOption == 3)
{
result = (weekday + 6 - 1) % 7;
}
else if (returnOption >= 11 && returnOption <= 17)
{
result = (weekday + 6 - (returnOption - 10)) % 7 + 1; // rotate in the value range 1 to 7
}
else
{
return(ErrorEval.NUM_ERROR); // EXCEL uses this and no VALUE_ERROR
}
return(new NumberEval(result));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
} // evaluate()