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()); } }