/**
* @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, ValueEval arg3)
{
try
{
// Evaluation order:
// arg0 lookup_value, arg1 table_array, arg3 range_lookup, find lookup value, arg2 col_index, fetch result
ValueEval lookupValue = OperandResolver.GetSingleValue(arg0, srcRowIndex, srcColumnIndex);
AreaEval tableArray = LookupUtils.ResolveTableArrayArg(arg1);
bool isRangeLookup = LookupUtils.ResolveRangeLookupArg(arg3, srcRowIndex, srcColumnIndex);
int rowIndex = LookupUtils.LookupIndexOfValue(lookupValue, LookupUtils.CreateColumnVector(tableArray, 0), isRangeLookup);
int colIndex = LookupUtils.ResolveRowOrColIndexArg(arg2, srcRowIndex, srcColumnIndex);
ValueVector resultCol = CreateResultColumnVector(tableArray, colIndex);
return(resultCol.GetItem(rowIndex));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0)
{
ValueEval ve;
try
{
ve = OperandResolver.GetSingleValue(arg0, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
//if (false)
//{
// // Note - it is more usual to propagate error codes straight to the result like this:
// return e.GetErrorEval();
// // but logical functions behave a little differently
//}
// this will usually cause a 'FALSE' result except for ISNONTEXT()
ve = e.GetErrorEval();
}
return(BoolEval.ValueOf(Evaluate(ve)));
}
/**
* 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 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 ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval lookupEval, ValueEval indexEval,
LookupUtils.MatchMode matchMode, LookupUtils.SearchMode searchMode)
{
try
{
ValueEval lookupValue = OperandResolver.GetSingleValue(lookupEval, srcRowIndex, srcColumnIndex);
TwoDEval tableArray = LookupUtils.ResolveTableArrayArg(indexEval);
ValueVector vector;
if (tableArray.IsColumn)
{
vector = LookupUtils.CreateColumnVector(tableArray, 0);
}
else
{
vector = LookupUtils.CreateRowVector(tableArray, 0);
}
int matchedIdx = LookupUtils.XlookupIndexOfValue(lookupValue, vector, matchMode, searchMode);
return(new NumberEval((double)matchedIdx + 1));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
public static String EvaluateStringArg(ValueEval eval, int srcRow, int srcCol)
{
ValueEval ve = OperandResolver.GetSingleValue(eval, srcRow, srcCol);
return(OperandResolver.CoerceValueToString(ve));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval number, ValueEval places)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(number, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
String strText1 = OperandResolver.CoerceValueToString(veText1);
Double number1 = OperandResolver.ParseDouble(strText1);
//If this number argument is non numeric, this function returns the #VALUE! error value.
if (double.IsNaN(number1))
{
return(ErrorEval.VALUE_INVALID);
}
//If number < -549,755,813,888 or if number > 549,755,813,887, this function returns the #NUM! error value.
if (number1 < MinValue || number1 > MaxValue)
{
return(ErrorEval.NUM_ERROR);
}
int placesNumber = 0;
if (number1 < 0)
{
placesNumber = DEFAULT_PLACES_VALUE;
}
else if (places != null)
{
ValueEval placesValueEval;
try
{
placesValueEval = OperandResolver.GetSingleValue(places, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
String placesStr = OperandResolver.CoerceValueToString(placesValueEval);
Double placesNumberDouble = OperandResolver.ParseDouble(placesStr);
//non numeric value
if (double.IsNaN(placesNumberDouble))
{
return(ErrorEval.VALUE_INVALID);
}
//If this argument Contains a decimal value, this function ignores the numbers to the right side of the decimal point.
placesNumber = (int)placesNumberDouble;
if (placesNumber < 0)
{
return(ErrorEval.NUM_ERROR);
}
}
String hex = "";
if (placesNumber != 0)
{
hex = String.Format("{0:X" + placesNumber + "}", (int)number1);
}
else
{
hex = String.Format("{0:X}", (int)number1);
}
if (number1 < 0)
{
hex = "FF" + hex.Substring(2);
}
return(new StringEval(hex.ToUpper()));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval numberVE, ValueEval placesVE)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(numberVE, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
string strText1 = OperandResolver.CoerceValueToString(veText1);
Double number = OperandResolver.ParseDouble(strText1);
//If this number argument is non numeric, this function returns the #VALUE! error value.
if (double.IsNaN(number))
{
return(ErrorEval.VALUE_INVALID);
}
//If number < -512 or if number > 512, this function returns the #NUM! error value.
if (number < MinValue || number > MaxValue)
{
return(ErrorEval.NUM_ERROR);
}
int placesNumber;
if (number < 0 || placesVE == null)
{
placesNumber = DEFAULT_PLACES_VALUE;
}
else
{
ValueEval placesValueEval;
try
{
placesValueEval = OperandResolver.GetSingleValue(placesVE, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
string placesStr = OperandResolver.CoerceValueToString(placesValueEval);
Double placesNumberDouble = OperandResolver.ParseDouble(placesStr);
//non numeric value
if (double.IsNaN(placesNumberDouble))
{
return(ErrorEval.VALUE_INVALID);
}
//If this argument Contains a decimal value, this function ignores the numbers to the right side of the decimal point.
placesNumber = (int)Math.Floor(placesNumberDouble);
if (placesNumber < 0 || placesNumber == 0)
{
return(ErrorEval.NUM_ERROR);
}
}
string binary = Convert.ToString((int)Math.Floor(number), 2);
if (binary.Length > DEFAULT_PLACES_VALUE)
{
binary = binary.Substring(binary.Length - DEFAULT_PLACES_VALUE);
}
//If DEC2BIN requires more than places characters, it returns the #NUM! error value.
if (binary.Length > placesNumber)
{
return(ErrorEval.NUM_ERROR);
}
return(new StringEval(binary));
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
DateTime date;
double numberOfMonths, result;
if (args.Length != 2)
{
return(ErrorEval.VALUE_INVALID);
}
try
{
// resolve the arguments
date = DateUtil.GetJavaDate(OperandResolver.CoerceValueToDouble(OperandResolver.GetSingleValue(args[0], ec.RowIndex, ec.ColumnIndex)));
numberOfMonths = OperandResolver.CoerceValueToDouble(OperandResolver.GetSingleValue(args[1], ec.RowIndex, ec.ColumnIndex));
// calculate the result date (Excel rounds the second argument always to zero; but we have be careful about negative numbers)
DateTime resultDate = date.AddMonths((int)Math.Floor(Math.Abs(numberOfMonths)) * Math.Sign(numberOfMonths));
result = DateUtil.GetExcelDate(resultDate);
NumericFunction.CheckValue(result);
return(new NumberEval(result));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
string decSep = Thread.CurrentThread.CurrentCulture.NumberFormat.NumberDecimalSeparator;
string groupSep = Thread.CurrentThread.CurrentCulture.NumberFormat.NumberGroupSeparator;
String text = null;
Double result = Double.NaN;
ValueEval v1 = null;
ValueEval v2 = null;
ValueEval v3 = null;
try
{
if (args.Length == 1)
{
v1 = OperandResolver.GetSingleValue(args[0], ec.RowIndex, ec.ColumnIndex);
text = OperandResolver.CoerceValueToString(v1);
}
else if (args.Length == 2)
{
v1 = OperandResolver.GetSingleValue(args[0], ec.RowIndex, ec.ColumnIndex);
v2 = OperandResolver.GetSingleValue(args[1], ec.RowIndex, ec.ColumnIndex);
text = OperandResolver.CoerceValueToString(v1);
decSep = OperandResolver.CoerceValueToString(v2).Substring(0, 1); //If multiple characters are used in the Decimal_separator or Group_separator arguments, only the first character is used.
}
else if (args.Length == 3)
{
v1 = OperandResolver.GetSingleValue(args[0], ec.RowIndex, ec.ColumnIndex);
v2 = OperandResolver.GetSingleValue(args[1], ec.RowIndex, ec.ColumnIndex);
v3 = OperandResolver.GetSingleValue(args[2], ec.RowIndex, ec.ColumnIndex);
text = OperandResolver.CoerceValueToString(v1);
decSep = OperandResolver.CoerceValueToString(v2).Substring(0, 1); //If multiple characters are used in the Decimal_separator or Group_separator arguments, only the first character is used.
groupSep = OperandResolver.CoerceValueToString(v3).Substring(0, 1); //If multiple characters are used in the Decimal_separator or Group_separator arguments, only the first character is used.
}
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
if (text == "")
{
text = "0"; //If an empty string ("") is specified as the Text argument, the result is 0.
}
text = text.Replace(" ", ""); //Empty spaces in the Text argument are ignored, even in the middle of the argument. For example, " 3 000 " is returned as 3000.
String[] parts = text.Split(new string[] { decSep }, StringSplitOptions.RemoveEmptyEntries);
String sigPart = "";
String decPart = "";
if (parts.Length > 2)
{
return(ErrorEval.VALUE_INVALID); //If a decimal separator is used more than once in the Text argument, NUMBERVALUE returns the #VALUE! error value.
}
if (parts.Length > 1)
{
sigPart = parts[0];
decPart = parts[1];
if (decPart.Contains(groupSep))
{
return(ErrorEval.VALUE_INVALID); //If the group separator occurs after the decimal separator in the Text argument, NUMBERVALUE returns the #VALUE! error value.
}
sigPart = sigPart.Replace(groupSep, ""); //If the group separator occurs before the decimal separator in the Text argument , the group separator is ignored.
text = sigPart + "." + decPart;
}
else if (parts.Length > 0)
{
sigPart = parts[0];
sigPart = sigPart.Replace(groupSep, ""); //If the group separator occurs before the decimal separator in the Text argument , the group separator is ignored.
text = sigPart;
}
// If the Text argument ends in one or more percent signs(%), they are used in the calculation of the result.
//Multiple percent signs are additive if they are used in the Text argument just as they are if they are used in a formula.
//For example, =NUMBERVALUE("9%%") returns the same result (0.0009) as the formula =9%%.
int countPercent = 0;
while (text.EndsWith("%"))
{
countPercent++;
text = text.Substring(0, text.Length - 1);
}
try
{
result = Double.Parse(text);
result = result / Math.Pow(100, countPercent); //If the Text argument ends in one or more percent signs (%), they are used in the calculation of the result.
checkValue(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
catch (Exception)
{
return(ErrorEval.VALUE_INVALID); //If any of the arguments are not valid, NUMBERVALUE returns the #VALUE! error value.
}
return(new NumberEval(result));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval real_num, ValueEval i_num, ValueEval suffix)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(real_num, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
double realNum = 0;
try
{
realNum = OperandResolver.CoerceValueToDouble(veText1);
}
catch (EvaluationException)
{
return(ErrorEval.VALUE_INVALID);
}
ValueEval veINum;
try
{
veINum = OperandResolver.GetSingleValue(i_num, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
double realINum = 0;
try
{
realINum = OperandResolver.CoerceValueToDouble(veINum);
}
catch (EvaluationException)
{
return(ErrorEval.VALUE_INVALID);
}
string suffixValue = OperandResolver.CoerceValueToString(suffix);
if (suffixValue.Length == 0)
{
suffixValue = DEFAULT_SUFFIX;
}
if (suffixValue.Equals(DEFAULT_SUFFIX.ToUpper()) || suffixValue.Equals(SUPPORTED_SUFFIX.ToUpper()))
{
return(ErrorEval.VALUE_INVALID);
}
if (!(suffixValue.Equals(DEFAULT_SUFFIX) || suffixValue.Equals(SUPPORTED_SUFFIX)))
{
return(ErrorEval.VALUE_INVALID);
}
StringBuilder strb = new StringBuilder("");
if (realNum != 0)
{
if (isDoubleAnInt(realNum))
{
strb.Append((int)realNum);
}
else
{
strb.Append(realNum);
}
}
if (realINum != 0)
{
if (strb.Length != 0)
{
if (realINum > 0)
{
strb.Append("+");
}
}
if (realINum != 1 && realINum != -1)
{
if (isDoubleAnInt(realINum))
{
strb.Append((int)realINum);
}
else
{
strb.Append(realINum);
}
}
strb.Append(suffixValue);
}
return(new StringEval(strb.ToString()));
}
public ValueEval Evaluate(int srcRowIndex, int srcColumnIndex,
ValueEval database, ValueEval filterColumn, ValueEval conditionDatabase)
{
// Input Processing and error Checks.
if (!(database is AreaEval) || !(conditionDatabase is AreaEval))
{
return(ErrorEval.VALUE_INVALID);
}
AreaEval db = (AreaEval)database;
AreaEval cdb = (AreaEval)conditionDatabase;
try
{
filterColumn = OperandResolver.GetSingleValue(filterColumn, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
int fc;
try
{
fc = GetColumnForName(filterColumn, db);
}
catch (EvaluationException)
{
return(ErrorEval.VALUE_INVALID);
}
if (fc == -1)
{ // column not found
return(ErrorEval.VALUE_INVALID);
}
// Create an algorithm runner.
IDStarAlgorithm algorithm = null;
switch (algoType)
{
case DStarAlgorithmEnum.DGET: algorithm = new DGet(); break;
case DStarAlgorithmEnum.DMIN: algorithm = new DMin(); break;
default:
throw new InvalidOperationException("Unexpected algorithm type " + algoType + " encountered.");
}
// Iterate over all db entries.
int height = db.Height;
for (int row = 1; row < height; ++row)
{
bool matches = true;
try
{
matches = FullFillsConditions(db, row, cdb);
}
catch (EvaluationException)
{
return(ErrorEval.VALUE_INVALID);
}
// Filter each entry.
if (matches)
{
ValueEval currentValueEval = ResolveReference(db, row, fc);
// Pass the match to the algorithm and conditionally abort the search.
bool shouldContinue = algorithm.ProcessMatch(currentValueEval);
if (!shouldContinue)
{
break;
}
}
}
// Return the result of the algorithm.
return(algorithm.Result);
}