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(this.getWeekNo(serialNumCalendar, returnType));
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
int srcCellRow = ec.RowIndex;
int srcCellCol = ec.ColumnIndex;
double result;
try
{
int basis = 0; // default
switch (args.Length)
{
case 3:
basis = EvaluateIntArg(args[2], srcCellRow, srcCellCol);
break;
case 2:
//basis = EvaluateIntArg(args[2], srcCellRow, srcCellCol);
break;
default:
return ErrorEval.VALUE_INVALID;
}
double startDateVal = EvaluateDateArg(args[0], srcCellRow, srcCellCol);
double endDateVal = EvaluateDateArg(args[1], srcCellRow, srcCellCol);
result = YearFracCalculator.Calculate(startDateVal, endDateVal, basis);
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
return new NumberEval(result);
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0,
ValueEval arg1)
{
String arg;
int index;
try
{
arg = TextFunction.EvaluateStringArg(arg0, srcRowIndex, srcColumnIndex);
index = TextFunction.EvaluateIntArg(arg1, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
if (index < 0)
{
return ErrorEval.VALUE_INVALID;
}
String result;
if (_isLeft)
{
result = arg.Substring(0, Math.Min(arg.Length, index));
}
else
{
result = arg.Substring(Math.Max(0, arg.Length - index));
}
return new StringEval(result);
}
/**
* returns the OperationEval concrete impl instance corresponding
* to the supplied operationPtg
*/
public static ValueEval Evaluate(OperationPtg ptg, ValueEval[] args,
OperationEvaluationContext ec)
{
if(ptg == null) {
throw new ArgumentException("ptg must not be null");
}
Function result = _instancesByPtgClass[ptg] as Function;
if (result != null) {
return result.Evaluate(args, ec.RowIndex, (short) ec.ColumnIndex);
}
if (ptg is AbstractFunctionPtg) {
AbstractFunctionPtg fptg = (AbstractFunctionPtg)ptg;
int functionIndex = fptg.GetFunctionIndex();
switch (functionIndex) {
case NPOI.HSSF.Record.Formula.Function.FunctionMetadataRegistry.FUNCTION_INDEX_INDIRECT:
return Indirect.instance.Evaluate(args, ec);
case NPOI.HSSF.Record.Formula.Function.FunctionMetadataRegistry.FUNCTION_INDEX_EXTERNAL:
return UserDefinedFunction.instance.Evaluate(args, ec);
}
return FunctionEval.GetBasicFunction(functionIndex).Evaluate(args, ec.RowIndex, ec.ColumnIndex);
}
throw new Exception("Unexpected operation ptg class (" + ptg.GetType().Name + ")");
}
public override ValueEval EvaluateFunc(ValueEval[] args, int srcCellRow, int srcCellCol)
{
if (args.Length != 3)
{
return ErrorEval.VALUE_INVALID;
}
String text = EvaluateStringArg(args[0], srcCellRow, srcCellCol);
int startCharNum = EvaluateIntArg(args[1], srcCellRow, srcCellCol);
int numChars = EvaluateIntArg(args[2], srcCellRow, srcCellCol);
int startIx = startCharNum - 1; // convert to zero-based
// Note - for start_num arg, blank/zero causes error(#VALUE!),
// but for num_chars causes empty string to be returned.
if (startIx < 0)
{
return ErrorEval.VALUE_INVALID;
}
if (numChars < 0)
{
return ErrorEval.VALUE_INVALID;
}
int len = text.Length;
if (numChars < 0 || startIx > len)
{
return new StringEval("");
}
int endIx = Math.Min(startIx + numChars, len);
String result = text.Substring(startIx, endIx-startIx);
return new StringEval(result);
}
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();
}
}
private void ConfirmPercentile(ValueEval percentile, ValueEval[] args, double expected)
{
ValueEval result = invokePercentile(args, percentile);
Assert.AreEqual(typeof(NumberEval), result.GetType());
double delta = 0.00000001;
Assert.AreEqual(expected, ((NumberEval)result).NumberValue, delta);
}
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex)
{
if (args.Length < 2)
{
return ErrorEval.VALUE_INVALID;
}
try
{
int ix = EvaluateFirstArg(args[0], srcRowIndex, srcColumnIndex);
if (ix < 1 || ix >= args.Length)
{
return ErrorEval.VALUE_INVALID;
}
ValueEval result = OperandResolver.GetSingleValue(args[ix], srcRowIndex, srcColumnIndex);
if (result == MissingArgEval.instance)
{
return BlankEval.instance;
}
return result;
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1)
{
double result;
try
{
double d0 = SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
double d1 = SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
result = Evaluate(d0, d1);
if (result == 0.0)
{ // this '==' matches +0.0 and -0.0
// Excel Converts -0.0 to +0.0 for '*', '/', '%', '+' and '^'
if (!(this is SubtractEval))
{
return NumberEval.ZERO;
}
}
if (Double.IsNaN(result) || Double.IsInfinity(result))
{
return ErrorEval.NUM_ERROR;
}
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
return new NumberEval(result);
}
public void SetValue(ValueEval value)
{
Type cls = value.GetType();
if (cls == typeof(NumberEval))
{
_cellType = CellType.NUMERIC;
_numberValue = ((NumberEval)value).NumberValue;
return;
}
if (cls == typeof(StringEval))
{
_cellType = CellType.STRING;
_stringValue = ((StringEval)value).StringValue;
return;
}
if (cls == typeof(BoolEval))
{
_cellType = CellType.BOOLEAN;
_boolValue = ((BoolEval)value).BooleanValue;
return;
}
if (cls == typeof(ErrorEval))
{
_cellType = CellType.ERROR;
_errorValue = ((ErrorEval)value).ErrorCode;
return;
}
if (cls == typeof(BlankEval))
{
_cellType = CellType.BLANK;
return;
}
throw new ArgumentException("Unexpected value class (" + cls.Name + ")");
}
private bool Calculate(ValueEval[] args)
{
bool result = InitialResultValue;
bool atleastOneNonBlank = false;
bool? tempVe;
/*
* Note: no short-circuit bool loop exit because any ErrorEvals will override the result
*/
for (int i = 0, iSize = args.Length; i < iSize; i++)
{
ValueEval arg = args[i];
if (arg is AreaEval)
{
AreaEval ae = (AreaEval)arg;
int height = ae.Height;
int width = ae.Width;
for (int rrIx = 0; rrIx < height; rrIx++)
{
for (int rcIx = 0; rcIx < width; rcIx++)
{
ValueEval ve = ae.GetRelativeValue(rrIx, rcIx);
tempVe = OperandResolver.CoerceValueToBoolean(ve, true);
if (tempVe != null)
{
result = PartialEvaluate(result, Convert.ToBoolean(tempVe));
atleastOneNonBlank = true;
}
}
}
continue;
}
if (arg is RefEval)
{
ValueEval ve = ((RefEval)arg).InnerValueEval;
tempVe = OperandResolver.CoerceValueToBoolean(ve, true);
}
else if (arg is ValueEval)
{
ValueEval ve = (ValueEval)arg;
tempVe = OperandResolver.CoerceValueToBoolean(ve, false);
}
else
{
throw new InvalidOperationException("Unexpected eval (" + arg.GetType().Name + ")");
}
if (tempVe != null)
{
result = PartialEvaluate(result, Convert.ToBoolean(tempVe));
atleastOneNonBlank = true;
}
}
if (!atleastOneNonBlank)
{
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
return result;
}
private static bool AreValuesEqual(ValueEval a, ValueEval b)
{
if (a == null)
{
return false;
}
Type cls = a.GetType();
if (cls != b.GetType())
{
// value type is changing
return false;
}
if (a == BlankEval.instance)
{
return b == a;
}
if (cls == typeof(NumberEval))
{
return ((NumberEval)a).NumberValue == ((NumberEval)b).NumberValue;
}
if (cls == typeof(StringEval))
{
return ((StringEval)a).StringValue.Equals(((StringEval)b).StringValue);
}
if (cls == typeof(BoolEval))
{
return ((BoolEval)a).BooleanValue == ((BoolEval)b).BooleanValue;
}
if (cls == typeof(ErrorEval))
{
return ((ErrorEval)a).ErrorCode == ((ErrorEval)b).ErrorCode;
}
throw new InvalidOperationException("Unexpected value class (" + cls.Name + ")");
}
public ValueEval Evaluate(ValueEval[] args, int srcCellRow, int srcCellCol)
{
ValueEval arg3 = null;
switch (args.Length)
{
case 4:
arg3 = args[3]; // important: assumed array element Is never null
break;
case 3:
break;
default:
// wrong number of arguments
return ErrorEval.VALUE_INVALID;
}
try
{
// Evaluation order:
// arg0 lookup_value, arg1 table_array, arg3 range_lookup, Find lookup value, arg2 row_index, fetch result
ValueEval lookupValue = OperandResolver.GetSingleValue(args[0], srcCellRow, srcCellCol);
AreaEval tableArray = LookupUtils.ResolveTableArrayArg(args[1]);
bool IsRangeLookup = LookupUtils.ResolveRangeLookupArg(arg3, srcCellRow, srcCellCol);
int colIndex = LookupUtils.LookupIndexOfValue(lookupValue, LookupUtils.CreateRowVector(tableArray, 0), IsRangeLookup);
int rowIndex = LookupUtils.ResolveRowOrColIndexArg(args[2], srcCellRow, srcCellCol);
ValueVector resultCol = CreateResultColumnVector(tableArray, rowIndex);
return resultCol.GetItem(colIndex);
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
}
public override ValueEval Evaluate(int srcCellRow, int srcCellCol, ValueEval arg0)
{
double d;
try
{
ValueEval ve = OperandResolver.GetSingleValue(arg0, srcCellRow, srcCellCol);
if (ve is BlankEval)
{
return NumberEval.ZERO;
}
if (ve is StringEval)
{
// Note - asymmetric with UnaryMinus
// -"hello" Evaluates to #VALUE!
// but +"hello" Evaluates to "hello"
return ve;
}
d = OperandResolver.CoerceValueToDouble(ve);
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
return new NumberEval(+d);
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
int nIncomingArgs = args.Length;
if (nIncomingArgs < 1)
{
throw new Exception("function name argument missing");
}
ValueEval nameArg = args[0];
String functionName = string.Empty ;
if (nameArg is NameEval)
{
functionName = ((NameEval)nameArg).FunctionName;
}
else if (nameArg is NameXEval)
{
functionName = ec.GetWorkbook().ResolveNameXText(((NameXEval)nameArg).Ptg);
}
else
{
throw new Exception("First argument should be a NameEval, but got ("
+ nameArg.GetType().Name + ")");
}
FreeRefFunction targetFunc = ec.FindUserDefinedFunction(functionName);
if (targetFunc == null)
{
throw new NotImplementedException(functionName);
}
int nOutGoingArgs = nIncomingArgs - 1;
ValueEval[] outGoingArgs = new ValueEval[nOutGoingArgs];
Array.Copy(args, 1, outGoingArgs, 0, nOutGoingArgs);
return targetFunc.Evaluate(outGoingArgs, ec);
}
/**
* Evaluate for NETWORKDAYS. Given two dates and a optional date or interval of holidays, determines how many working days are there
* between those dates.
*
* @return {@link ValueEval} for the number of days between two dates.
*/
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length < 2 || args.Length > 3)
{
return ErrorEval.VALUE_INVALID;
}
int srcCellRow = ec.RowIndex;
int srcCellCol = ec.ColumnIndex;
double start, end;
double[] holidays;
try
{
start = this.evaluator.EvaluateDateArg(args[0], srcCellRow, srcCellCol);
end = this.evaluator.EvaluateDateArg(args[1], srcCellRow, srcCellCol);
if (start > end)
{
return ErrorEval.NAME_INVALID;
}
ValueEval holidaysCell = args.Length == 3 ? args[2] : null;
holidays = this.evaluator.EvaluateDatesArg(holidaysCell, srcCellRow, srcCellCol);
return new NumberEval(WorkdayCalculator.instance.CalculateWorkdays(start, end, holidays));
}
catch (EvaluationException)
{
return ErrorEval.VALUE_INVALID;
}
}
/**
* Replaces part of a text string based on the number of Chars
* you specify, with another text string.
*
* @see org.apache.poi.hssf.record.formula.eval.Eval
*/
public override ValueEval EvaluateFunc(ValueEval[] args, int srcCellRow, int srcCellCol)
{
if (args.Length != 4)
{
return ErrorEval.VALUE_INVALID;
}
String oldStr = EvaluateStringArg(args[0], srcCellRow, srcCellCol);
int startNum = EvaluateIntArg(args[1], srcCellRow, srcCellCol);
int numChars = EvaluateIntArg(args[2], srcCellRow, srcCellCol);
String newStr = EvaluateStringArg(args[3], srcCellRow, srcCellCol);
if (startNum < 1 || numChars < 0)
{
return ErrorEval.VALUE_INVALID;
}
StringBuilder strBuff = new StringBuilder(oldStr);
// remove any characters that should be replaced
if (startNum <= oldStr.Length && numChars != 0)
{
strBuff.Remove(startNum - 1, startNum - 1 + numChars);
}
// now insert (or append) newStr
if (startNum > strBuff.Length)
{
strBuff.Append(newStr);
}
else
{
strBuff.Insert(startNum - 1, newStr);
}
return new StringEval(strBuff.ToString());
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2)
{
AreaEval aeRange;
double result;
bool order = false;
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);
}
aeRange = ConvertRangeArg(arg1);
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);
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
return eval(srcRowIndex, srcColumnIndex, result, aeRange, order);
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval venumerator, ValueEval vedenominator)
{
double enumerator = 0;
try
{
enumerator = OperandResolver.CoerceValueToDouble(venumerator);
}
catch (EvaluationException)
{
return ErrorEval.VALUE_INVALID;
}
double denominator = 0;
try
{
denominator = OperandResolver.CoerceValueToDouble(vedenominator);
}
catch (EvaluationException)
{
return ErrorEval.VALUE_INVALID;
}
if (denominator == 0)
{
return ErrorEval.DIV_ZERO;
}
return new NumberEval(((int)(enumerator / denominator)));
}
/**
* Evaluate a generic {@link ValueEval} argument to an array of double values that represents dates in POI.
*
* @param arg {@link ValueEval} an argument.
* @param srcCellRow number cell row.
* @param srcCellCol number cell column.
* @return an array of doubles representing dates in POI.
* @throws EvaluationException exception upon argument evaluation.
*/
public double[] EvaluateDatesArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
if (arg == null)
{
return new double[0];
}
if (arg is StringEval)
{
return new double[] { EvaluateDateArg(arg, srcCellRow, srcCellCol) };
}
else if (arg is AreaEvalBase)
{
List<Double> valuesList = new List<Double>();
AreaEvalBase area = (AreaEvalBase)arg;
for (int i = area.FirstRow; i <= area.LastRow; i++)
{
for (int j = area.FirstColumn; j <= area.LastColumn; j++)
{
valuesList.Add(EvaluateDateArg(area.GetValue(i, j), i, j));
}
}
double[] values = new double[valuesList.Count];
for (int i = 0; i < valuesList.Count; i++)
{
values[i] = valuesList[(i)];
}
return values;
}
return new double[] { OperandResolver.CoerceValueToDouble(arg) };
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length < 1)
{
return ErrorEval.VALUE_INVALID;
}
bool isA1style;
String text;
try
{
ValueEval ve = OperandResolver.GetSingleValue(args[0], ec.RowIndex, ec
.ColumnIndex);
text = OperandResolver.CoerceValueToString(ve);
switch (args.Length)
{
case 1:
isA1style = true;
break;
case 2:
isA1style = EvaluateBooleanArg(args[1], ec);
break;
default:
return ErrorEval.VALUE_INVALID;
}
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
return EvaluateIndirect(ec, text, isA1style);
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
ValueEval arg2)
{
bool b;
try
{
b = EvaluateFirstArg(arg0, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
if (b)
{
if (arg1 == MissingArgEval.instance)
{
return BlankEval.instance;
}
return arg1;
}
if (arg2 == MissingArgEval.instance)
{
return BlankEval.instance;
}
return arg2;
}
public void TestCountBlank()
{
AreaEval range;
ValueEval[] values;
values = new ValueEval[] {
new NumberEval(0),
new StringEval(""), // note - does not match blank
BoolEval.TRUE,
BoolEval.FALSE,
ErrorEval.DIV_ZERO,
BlankEval.instance,
};
range = EvalFactory.CreateAreaEval("A1:B3", values);
ConfirmCountBlank(1, range);
values = new ValueEval[] {
new NumberEval(0),
new StringEval(""), // note - does not match blank
BlankEval.instance,
BoolEval.FALSE,
BoolEval.TRUE,
BlankEval.instance,
};
range = EvalFactory.CreateAreaEval("A1:B3", values);
ConfirmCountBlank(2, range);
}
/**
*Substitutes text in a text string with new text, some number of times.
*
* @see org.apache.poi.hssf.record.formula.eval.Eval
*/
public override ValueEval EvaluateFunc(ValueEval[] args, int srcCellRow, int srcCellCol)
{
if (args.Length < 3 || args.Length > 4)
{
return ErrorEval.VALUE_INVALID;
}
String oldStr = EvaluateStringArg(args[0], srcCellRow, srcCellCol);
String searchStr = EvaluateStringArg(args[1], srcCellRow, srcCellCol);
String newStr = EvaluateStringArg(args[2], srcCellRow, srcCellCol);
String result;
switch (args.Length)
{
case 4:
int instanceNumber = EvaluateIntArg(args[3], srcCellRow, srcCellCol);
if (instanceNumber < 1)
{
return ErrorEval.VALUE_INVALID;
}
result = ReplaceOneOccurrence(oldStr, searchStr, newStr, instanceNumber);
break;
case 3:
result = ReplaceAllOccurrences(oldStr, searchStr, newStr);
break;
default:
throw new InvalidOperationException("Cannot happen");
}
return new StringEval(result);
}
public void TestCriteriaArgRange()
{
ValueEval[] arg0values = new ValueEval[] { _50, _60, _50, _50, _50, _30, };
ValueEval[] arg1values = new ValueEval[] { _30, _40, _50, _60, };
AreaEval arg0;
AreaEval arg1;
ValueEval ve;
arg0 = EvalFactory.CreateAreaEval("A3:B5", arg0values);
arg1 = EvalFactory.CreateAreaEval("A2:D2", arg1values); // single row range
ve = invokeSumif(0, 2, arg0, arg1); // invoking from cell C1
if (ve is NumberEval)
{
NumberEval ne = (NumberEval)ve;
if (ne.NumberValue == 30.0)
{
throw new AssertionException("identified error in SUMIF - criteria arg not Evaluated properly");
}
}
ConfirmDouble(200, ve);
arg0 = EvalFactory.CreateAreaEval("C1:D3", arg0values);
arg1 = EvalFactory.CreateAreaEval("B1:B4", arg1values); // single column range
ve = invokeSumif(3, 0, arg0, arg1); // invoking from cell A4
ConfirmDouble(60, ve);
}
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);
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
double result;
if (args.Length != 2)
{
return ErrorEval.VALUE_INVALID;
}
try
{
double startDateAsNumber = GetValue(args[0]);
NumberEval offsetInYearsValue = (NumberEval)args[1];
int offsetInMonthAsNumber = (int)offsetInYearsValue.NumberValue;
// 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 arg = arg0;
if (arg is RefEval)
{
// always use the first sheet
RefEval re = (RefEval)arg;
arg = re.GetInnerValueEval(re.FirstSheetIndex);
}
else if (arg is AreaEval)
{
// when the arg is an area, choose the top left cell
arg = ((AreaEval)arg).GetRelativeValue(0, 0);
}
if (arg is StringEval)
{
// Text values are returned unmodified
return arg;
}
if (arg is ErrorEval)
{
// Error values also returned unmodified
return arg;
}
// for all other argument types the result is empty string
return StringEval.EMPTY_INSTANCE;
}
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, int srcCellRow, int srcCellCol)
{
int nArgs = args.Length;
if (nArgs < 1)
{
// too few arguments
return ErrorEval.VALUE_INVALID;
}
if (nArgs > 30)
{
// too many arguments
return ErrorEval.VALUE_INVALID;
}
int temp = 0;
// Note - observed behavior of Excel:
// Error values like #VALUE!, #REF!, #DIV/0!, #NAME? etc don't cause this COUNTA to return an error
// in fact, they seem to Get Counted
for (int i = 0; i < nArgs; i++)
{
temp += CountUtils.CountArg(args[i], _predicate);
}
return new NumberEval(temp);
}
public static String EvaluateStringArg(ValueEval eval, int srcRow, int srcCol)
{
ValueEval ve = OperandResolver.GetSingleValue(eval, srcRow, srcCol);
return(OperandResolver.CoerceValueToString(ve));
}
public abstract bool Matches(ValueEval x);
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
ValueEval arg2)
{
bool b;
try
{
b = EvaluateFirstArg(arg0, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
if (b)
{
if (arg1 == MissingArgEval.instance)
{
return(BlankEval.instance);
}
return(arg1);
}
if (arg2 == MissingArgEval.instance)
{
return(BlankEval.instance);
}
return(arg2);
}
private static int DoCompare(ValueEval va, ValueEval vb)
{
// special cases when one operand is blank
if (va == BlankEval.instance)
{
return(CompareBlank(vb));
}
if (vb == BlankEval.instance)
{
return(-CompareBlank(va));
}
if (va is BoolEval)
{
if (vb is BoolEval)
{
BoolEval bA = (BoolEval)va;
BoolEval bB = (BoolEval)vb;
if (bA.BooleanValue == bB.BooleanValue)
{
return(0);
}
return(bA.BooleanValue ? 1 : -1);
}
return(1);
}
if (vb is BoolEval)
{
return(-1);
}
if (va is StringEval)
{
if (vb is StringEval)
{
StringEval sA = (StringEval)va;
StringEval sB = (StringEval)vb;
return(string.Compare(sA.StringValue, sB.StringValue, StringComparison.OrdinalIgnoreCase));
}
return(1);
}
if (vb is StringEval)
{
return(-1);
}
if (va is NumberEval)
{
if (vb is NumberEval)
{
NumberEval nA = (NumberEval)va;
NumberEval nB = (NumberEval)vb;
if (nA.NumberValue == nB.NumberValue)
{
// Excel considers -0.0 == 0.0 which is different to Double.compare()
return(0);
}
return(NumberComparer.Compare(nA.NumberValue, nB.NumberValue));
}
}
throw new ArgumentException("Bad operand types (" + va.GetType().Name + "), ("
+ vb.GetType().Name + ")");
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1)
{
ValueEval vA;
ValueEval vB;
try
{
vA = OperandResolver.GetSingleValue(arg0, srcRowIndex, srcColumnIndex);
vB = OperandResolver.GetSingleValue(arg1, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
int cmpResult = DoCompare(vA, vB);
bool result = ConvertComparisonResult(cmpResult);
return(BoolEval.ValueOf(result));
}
public static double EvaluateDoubleArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
ValueEval ve = OperandResolver.GetSingleValue(arg, srcCellRow, srcCellCol);
return(OperandResolver.CoerceValueToDouble(ve));
}
public static int EvaluateIntArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
ValueEval ve = OperandResolver.GetSingleValue(arg, srcCellRow, srcCellCol);
return(OperandResolver.CoerceValueToInt(ve));
}
private static ValueEval invokeBack(string number1)
{
ValueEval[] args = new ValueEval[] { new StringEval(number1) };
return(new Bin2Dec().Evaluate(args, -1, -1));
}
private void ConfirmError(ValueEval xArray, ValueEval yArray, ErrorEval expectedError)
{
ConfirmError(SLOPE, xArray, yArray, expectedError);
}
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);
result = Evaluate(d0, d1);
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)
{
double result;
try
{
double rate = 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);
result = Evaluate(rate, d1, d2, d3);
NumericFunction.CheckValue(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(new NumberEval(result));
}
private static ValueEval invokeValue(string number1)
{
ValueEval[] args = new ValueEval[] { new StringEval(number1) };
return(new Dec2Bin().Evaluate(args, -1, -1));
}
private static ValueEval invokeValue(String numerator, String denominator)
{
ValueEval[] args = new ValueEval[] { new StringEval(numerator), new StringEval(denominator) };
return(new Quotient().Evaluate(args, -1, -1));
}
private static ValueEval invoke(Function function, ValueEval xArray, ValueEval yArray)
{
ValueEval[] args = new ValueEval[] { xArray, yArray, };
return(function.Evaluate(args, -1, (short)-1));
}
public SingleCellValueArray(ValueEval value)
: base(1)
{
_value = value;
}
private bool Calculate(ValueEval[] args)
{
bool result = InitialResultValue;
bool atleastOneNonBlank = false;
/*
* Note: no short-circuit bool loop exit because any ErrorEvals will override the result
*/
for (int i = 0, iSize = args.Length; i < iSize; i++)
{
bool? tempVe;
ValueEval arg = args[i];
if (arg is TwoDEval)
{
TwoDEval ae = (TwoDEval)arg;
int height = ae.Height;
int width = ae.Width;
for (int rrIx = 0; rrIx < height; rrIx++)
{
for (int rcIx = 0; rcIx < width; rcIx++)
{
ValueEval ve = ae.GetValue(rrIx, rcIx);
tempVe = OperandResolver.CoerceValueToBoolean(ve, true);
if (tempVe != null)
{
result = PartialEvaluate(result, Convert.ToBoolean(tempVe, CultureInfo.InvariantCulture));
atleastOneNonBlank = true;
}
}
}
continue;
}
if (arg is RefEval)
{
RefEval re = (RefEval)arg;
for (int sIx = re.FirstSheetIndex; sIx <= re.LastSheetIndex; sIx++)
{
ValueEval ve = re.GetInnerValueEval(sIx);
tempVe = OperandResolver.CoerceValueToBoolean(ve, true);
if (tempVe != null)
{
result = PartialEvaluate(result, tempVe.Value);
atleastOneNonBlank = true;
}
}
continue;
}
//else if (arg is ValueEval)
//{
// ValueEval ve = (ValueEval)arg;
// tempVe = OperandResolver.CoerceValueToBoolean(ve, false);
//}
if (arg == MissingArgEval.instance)
{
tempVe = null; // you can leave out parameters, they are simply ignored
}
else
{
tempVe = OperandResolver.CoerceValueToBoolean(arg, false);
}
if (tempVe != null)
{
result = PartialEvaluate(result, Convert.ToBoolean(tempVe, CultureInfo.InvariantCulture));
atleastOneNonBlank = true;
}
}
if (!atleastOneNonBlank)
{
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
return(result);
}
public void UpdateFormulaResult(ValueEval result, CellCacheEntry[] sensitiveInputCells, FormulaUsedBlankCellSet usedBlankAreas)
{
UpdateValue(result);
SetSensitiveInputCells(sensitiveInputCells);
_usedBlankCellGroup = usedBlankAreas;
}
protected override bool Evaluate(ValueEval arg)
{
return(!(arg is StringEval));
}
public abstract ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1);
private double EvaluateInternal(ValueVector x, ValueVector y, int size)
{
// error handling is as if the x is fully Evaluated before y
ErrorEval firstXerr = null;
ErrorEval firstYerr = null;
bool accumlatedSome = false;
// first pass: read in data, compute xbar and ybar
double sumx = 0.0, sumy = 0.0;
for (int i = 0; i < size; i++)
{
ValueEval vx = x.GetItem(i);
ValueEval vy = y.GetItem(i);
if (vx is ErrorEval)
{
if (firstXerr == null)
{
firstXerr = (ErrorEval)vx;
continue;
}
}
if (vy is ErrorEval)
{
if (firstYerr == null)
{
firstYerr = (ErrorEval)vy;
continue;
}
}
// only count pairs if both elements are numbers
if (vx is NumberEval && vy is NumberEval)
{
accumlatedSome = true;
NumberEval nx = (NumberEval)vx;
NumberEval ny = (NumberEval)vy;
sumx += nx.NumberValue;
sumy += ny.NumberValue;
}
else
{
// all other combinations of value types are silently ignored
}
}
double xbar = sumx / size;
double ybar = sumy / size;
// second pass: compute summary statistics
double xxbar = 0.0, xybar = 0.0;
for (int i = 0; i < size; i++)
{
ValueEval vx = x.GetItem(i);
ValueEval vy = y.GetItem(i);
if (vx is ErrorEval)
{
if (firstXerr == null)
{
firstXerr = (ErrorEval)vx;
continue;
}
}
if (vy is ErrorEval)
{
if (firstYerr == null)
{
firstYerr = (ErrorEval)vy;
continue;
}
}
// only count pairs if both elements are numbers
if (vx is NumberEval && vy is NumberEval)
{
NumberEval nx = (NumberEval)vx;
NumberEval ny = (NumberEval)vy;
xxbar += (nx.NumberValue - xbar) * (nx.NumberValue - xbar);
xybar += (nx.NumberValue - xbar) * (ny.NumberValue - ybar);
}
else
{
// all other combinations of value types are silently ignored
}
}
double beta1 = xybar / xxbar;
double beta0 = ybar - beta1 * xbar;
if (firstXerr != null)
{
throw new EvaluationException(firstXerr);
}
if (firstYerr != null)
{
throw new EvaluationException(firstYerr);
}
if (!accumlatedSome)
{
throw new EvaluationException(ErrorEval.DIV_ZERO);
}
if (function == FUNCTION.INTERCEPT)
{
return(beta0);
}
else
{
return(beta1);
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1)
{
double result;
try
{
ValueVector vvX = CreateValueVector(arg0);
ValueVector vvY = CreateValueVector(arg1);
int size = vvX.Size;
if (size == 0 || vvY.Size != size)
{
return(ErrorEval.NA);
}
result = EvaluateInternal(vvX, vvY, size);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
if (Double.IsNaN(result) || Double.IsInfinity(result))
{
return(ErrorEval.NUM_ERROR);
}
return(new NumberEval(result));
}
private static ValueEval invokeMatch(ValueEval Lookup_value, ValueEval Lookup_array, ValueEval match_type)
{
ValueEval[] args = { Lookup_value, Lookup_array, match_type, };
return(new Match().Evaluate(args, -1, (short)-1));
}
private static void ConfirmPredicate(bool expectedResult, IMatchPredicate matchPredicate, String value)
{
ValueEval ev = (value == null) ? BlankEval.instance : (ValueEval) new StringEval(value);
Assert.AreEqual(expectedResult, matchPredicate.Matches(ev));
}
private void ConfirmError(ValueEval xArray, ValueEval yArray, ErrorEval expectedError)
{
ConfirmError(INTERCEPT, xArray, yArray, expectedError);
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1)
{
double s0;
String s1;
try
{
s0 = TextFunction.EvaluateDoubleArg(arg0, srcRowIndex, srcColumnIndex);
s1 = TextFunction.EvaluateStringArg(arg1, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
if (Regex.Match(s1, "[y|m|M|d|s|h]+").Success)
{
//may be datetime string
ValueEval result = TryParseDateTime(s0, s1);
if (result != ErrorEval.VALUE_INVALID)
{
return(result);
}
}
//The regular expression needs ^ and $.
if (Regex.Match(s1, @"^[\d,\#,\.,\$,\,]+$").Success)
{
//TODO: simulate DecimalFormat class in java.
FormatBase formatter = new DecimalFormat(s1);
return(new StringEval(formatter.Format(s0)));
}
else if (s1.IndexOf("/", StringComparison.Ordinal) == s1.LastIndexOf("/", StringComparison.Ordinal) && s1.IndexOf("/", StringComparison.Ordinal) >= 0 && !s1.Contains("-"))
{
double wholePart = Math.Floor(s0);
double decPart = s0 - wholePart;
if (wholePart * decPart == 0)
{
return(new StringEval("0"));
}
String[] parts = s1.Split(' ');
String[] fractParts;
if (parts.Length == 2)
{
fractParts = parts[1].Split('/');
}
else
{
fractParts = s1.Split('/');
}
if (fractParts.Length == 2)
{
double minVal = 1.0;
double currDenom = Math.Pow(10, fractParts[1].Length) - 1d;
double currNeum = 0;
for (int i = (int)(Math.Pow(10, fractParts[1].Length) - 1d); i > 0; i--)
{
for (int i2 = (int)(Math.Pow(10, fractParts[1].Length) - 1d); i2 > 0; i2--)
{
if (minVal >= Math.Abs((double)i2 / (double)i - decPart))
{
currDenom = i;
currNeum = i2;
minVal = Math.Abs((double)i2 / (double)i - decPart);
}
}
}
FormatBase neumFormatter = new DecimalFormat(fractParts[0]);
FormatBase denomFormatter = new DecimalFormat(fractParts[1]);
if (parts.Length == 2)
{
FormatBase wholeFormatter = new DecimalFormat(parts[0]);
String result = wholeFormatter.Format(wholePart) + " " + neumFormatter.Format(currNeum) + "/" + denomFormatter.Format(currDenom);
return(new StringEval(result));
}
else
{
String result = neumFormatter.Format(currNeum + (currDenom * wholePart)) + "/" + denomFormatter.Format(currDenom);
return(new StringEval(result));
}
}
else
{
return(ErrorEval.VALUE_INVALID);
}
}
else
{
return(TryParseDateTime(s0, s1));
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0)
{
return(Evaluate(srcRowIndex, srcColumnIndex, arg0, DEFAULT_ARG1));
}
protected override bool Evaluate(ValueEval arg)
{
return(arg is NumberEval);
}
private static IMatchPredicate CreateCriteriaPredicate(ValueEval ev)
{
return(Countif.CreateCriteriaPredicate(ev, 0, 0));
}
protected abstract CompareResult CompareSameType(ValueEval other);
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);
TwoDEval 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());
}
}
