public void TestComplexIRR_bug45041() { String formula = "(1+IRR(SUMIF(A:A,ROW(INDIRECT(MIN(A:A)&\":\"&MAX(A:A))),B:B),0))^365-1"; Ptg[] ptgs = ParseFormula(formula); FuncVarPtg rowFunc = (FuncVarPtg)ptgs[10]; FuncVarPtg sumifFunc = (FuncVarPtg)ptgs[12]; Assert.AreEqual("ROW", rowFunc.Name); Assert.AreEqual("SUMIF", sumifFunc.Name); if (rowFunc.PtgClass == Ptg.CLASS_VALUE || sumifFunc.PtgClass == Ptg.CLASS_VALUE) { throw new AssertionException("Identified bug 45041"); } ConfirmTokenClass(ptgs, 1, Ptg.CLASS_REF); ConfirmTokenClass(ptgs, 2, Ptg.CLASS_REF); ConfirmFuncClass(ptgs, 3, "MIN", Ptg.CLASS_VALUE); ConfirmTokenClass(ptgs, 6, Ptg.CLASS_REF); ConfirmFuncClass(ptgs, 7, "MAX", Ptg.CLASS_VALUE); ConfirmFuncClass(ptgs, 9, "INDIRECT", Ptg.CLASS_REF); ConfirmFuncClass(ptgs, 10, "ROW", Ptg.CLASS_ARRAY); ConfirmTokenClass(ptgs, 11, Ptg.CLASS_REF); ConfirmFuncClass(ptgs, 12, "SUMIF", Ptg.CLASS_ARRAY); ConfirmFuncClass(ptgs, 14, "IRR", Ptg.CLASS_VALUE); }
private static bool IsIf(Ptg token) { if (token is FuncVarPtg) { FuncVarPtg func = (FuncVarPtg)token; if (FunctionMetadataRegistry.FUNCTION_NAME_IF.Equals(func.Name)) { return(true); } } return(false); }
public void TestWithConcat() { // =CHOOSE(2,A2,A3,A4) byte[] data = { 6, 0, 68, 0, 1, 0, 1, 0, 15, 0, 0, 0, 0, 0, 0, 0, 57, 64, 0, 0, 12, 0, 12, unchecked ((byte)-4), 46, 0, 30, 2, 0, // Int - 2 25, 4, 3, 0, // Attr 8, 0, 17, 0, 26, 0, // jumpTable 35, 0, // chooseOffSet 36, 1, 0, 0,unchecked ((byte)-64), // Ref - A2 25, 8, 21, 0, // Attr 36, 2, 0, 0,unchecked ((byte)-64), // Ref - A3 25, 8, 12, 0, // Attr 36, 3, 0, 0,unchecked ((byte)-64), // Ref - A4 25, 8, 3, 0, // Attr 66, 4, 100, 0 // CHOOSE }; RecordInputStream inp = new RecordInputStream(new MemoryStream(data)); inp.NextRecord(); FormulaRecord fr = new FormulaRecord(inp); Ptg[] ptgs = fr.ParsedExpression; Assert.AreEqual(9, ptgs.Length); Assert.AreEqual(typeof(IntPtg), ptgs[0].GetType()); Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType()); Assert.AreEqual(typeof(RefPtg), ptgs[2].GetType()); Assert.AreEqual(typeof(AttrPtg), ptgs[3].GetType()); Assert.AreEqual(typeof(RefPtg), ptgs[4].GetType()); Assert.AreEqual(typeof(AttrPtg), ptgs[5].GetType()); Assert.AreEqual(typeof(RefPtg), ptgs[6].GetType()); Assert.AreEqual(typeof(AttrPtg), ptgs[7].GetType()); Assert.AreEqual(typeof(FuncVarPtg), ptgs[8].GetType()); FuncVarPtg choose = (FuncVarPtg)ptgs[8]; Assert.AreEqual("CHOOSE", choose.Name); }
public void TestIfSingleCondition() { Ptg[] ptgs = ParseFormula("IF(1=1,10)"); Assert.AreEqual(7, ptgs.Length); Assert.IsTrue((ptgs[3] is AttrPtg), "IF Attr Set correctly"); AttrPtg ifFunc = (AttrPtg)ptgs[3]; Assert.IsTrue(ifFunc.IsOptimizedIf, "It is1 not an if"); Assert.IsTrue((ptgs[4] is IntPtg), "Single Value is1 not an IntPtg"); IntPtg intPtg = (IntPtg)ptgs[4]; Assert.AreEqual((short)10, intPtg.Value, "Result"); Assert.IsTrue((ptgs[6] is FuncVarPtg), "Ptg is1 not a Variable Function"); FuncVarPtg funcPtg = (FuncVarPtg)ptgs[6]; Assert.AreEqual(2, funcPtg.NumberOfOperands, "Arguments"); }
/** * Generates the variable function ptg for the formula. * <p> * For IF Formulas, Additional PTGs are Added To the Tokens * @param name * @param numArgs * @return Ptg a null Is returned if we're in an IF formula, it needs extreme manipulation and Is handled in this function */ private ParseNode GetFunction(String name, NamePtg namePtg, ParseNode[] args) { FunctionMetadata fm = FunctionMetadataRegistry.GetFunctionByName(name.ToUpper()); int numArgs = args.Length; if (fm == null) { if (namePtg == null) { throw new InvalidOperationException("NamePtg must be supplied for external functions"); } // must be external function ParseNode[] allArgs = new ParseNode[numArgs + 1]; allArgs[0] = new ParseNode(namePtg); Array.Copy(args, 0, allArgs, 1, numArgs); return(new ParseNode(new FuncVarPtg(name, (byte)(numArgs + 1)), allArgs)); } if (namePtg != null) { throw new InvalidOperationException("NamePtg no applicable To internal functions"); } bool IsVarArgs = !fm.HasFixedArgsLength; int funcIx = fm.Index; ValidateNumArgs(args.Length, fm); AbstractFunctionPtg retval; if (IsVarArgs) { retval = new FuncVarPtg(name, (byte)numArgs); } else { retval = new FuncPtg(funcIx); } return(new ParseNode(retval, args)); }
/** * @return whether cell at rowIndex and columnIndex is a subtotal * @see org.apache.poi.ss.formula.functions.Subtotal */ public bool IsSubTotal(int rowIndex, int columnIndex) { bool subtotal = false; IEvaluationCell cell = Sheet.GetCell(rowIndex, columnIndex); if (cell != null && cell.CellType == CellType.Formula) { IEvaluationWorkbook wb = _bookEvaluator.Workbook; foreach (Ptg ptg in wb.GetFormulaTokens(cell)) { if (ptg is FuncVarPtg) { FuncVarPtg f = (FuncVarPtg)ptg; if ("SUBTOTAL".Equals(f.Name)) { subtotal = true; break; } } } } return(subtotal); }
// visibility raised for testing /* package */ public ValueEval EvaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs) { Stack <ValueEval> stack = new Stack <ValueEval>(); for (int i = 0, iSize = ptgs.Length; i < iSize; i++) { // since we don't know how To handle these yet :( Ptg ptg = ptgs[i]; if (ptg is AttrPtg) { AttrPtg attrPtg = (AttrPtg)ptg; if (attrPtg.IsSum) { // Excel prefers To encode 'SUM()' as a tAttr Token, but this evaluator // expects the equivalent function Token byte nArgs = 1; // tAttrSum always Has 1 parameter ptg = new FuncVarPtg("SUM", nArgs); } if (attrPtg.IsOptimizedChoose) { ValueEval arg0 = stack.Pop(); int[] jumpTable = attrPtg.JumpTable; int dist; int nChoices = jumpTable.Length; try { int switchIndex = Choose.EvaluateFirstArg(arg0, ec.RowIndex, ec.ColumnIndex); if (switchIndex < 1 || switchIndex > nChoices) { stack.Push(ErrorEval.VALUE_INVALID); dist = attrPtg.ChooseFuncOffset + 4; // +4 for tFuncFar(CHOOSE) } else { dist = jumpTable[switchIndex - 1]; } } catch (EvaluationException e) { stack.Push(e.GetErrorEval()); dist = attrPtg.ChooseFuncOffset + 4; // +4 for tFuncFar(CHOOSE) } // Encoded dist for tAttrChoose includes size of jump table, but // countTokensToBeSkipped() does not (it counts whole tokens). dist -= nChoices * 2 + 2; // subtract jump table size i += CountTokensToBeSkipped(ptgs, i, dist); continue; } if (attrPtg.IsOptimizedIf) { ValueEval arg0 = stack.Pop(); bool evaluatedPredicate; try { evaluatedPredicate = If.EvaluateFirstArg(arg0, ec.RowIndex, ec.ColumnIndex); } catch (EvaluationException e) { stack.Push(e.GetErrorEval()); int dist = attrPtg.Data; i += CountTokensToBeSkipped(ptgs, i, dist); attrPtg = (AttrPtg)ptgs[i]; dist = attrPtg.Data + 1; i += CountTokensToBeSkipped(ptgs, i, dist); continue; } if (evaluatedPredicate) { // nothing to skip - true param folows } else { int dist = attrPtg.Data; i += CountTokensToBeSkipped(ptgs, i, dist); Ptg nextPtg = ptgs[i + 1]; if (ptgs[i] is AttrPtg && nextPtg is FuncVarPtg) { // this is an if statement without a false param (as opposed to MissingArgPtg as the false param) i++; stack.Push(BoolEval.FALSE); } } continue; } if (attrPtg.IsSkip) { int dist = attrPtg.Data + 1; i += CountTokensToBeSkipped(ptgs, i, dist); if (stack.Peek() == MissingArgEval.instance) { stack.Pop(); stack.Push(BlankEval.instance); } continue; } } if (ptg is ControlPtg) { // skip Parentheses, Attr, etc continue; } if (ptg is MemFuncPtg) { // can ignore, rest of Tokens for this expression are in OK RPN order continue; } if (ptg is MemErrPtg) { continue; } ValueEval opResult; if (ptg is OperationPtg) { OperationPtg optg = (OperationPtg)ptg; if (optg is UnionPtg) { continue; } int numops = optg.NumberOfOperands; ValueEval[] ops = new ValueEval[numops]; // storing the ops in reverse order since they are popping for (int j = numops - 1; j >= 0; j--) { ValueEval p = (ValueEval)stack.Pop(); ops[j] = p; } // logDebug("Invoke " + operation + " (nAgs=" + numops + ")"); opResult = OperationEvaluatorFactory.Evaluate(optg, ops, ec); } else { opResult = GetEvalForPtg(ptg, ec); } if (opResult == null) { throw new Exception("Evaluation result must not be null"); } // logDebug("push " + opResult); stack.Push(opResult); } ValueEval value = ((ValueEval)stack.Pop()); if (stack.Count != 0) { throw new InvalidOperationException("evaluation stack not empty"); } value = DereferenceValue(value, ec.RowIndex, ec.ColumnIndex); if (value == BlankEval.instance) { // Note Excel behaviour here. A blank value is converted To zero. return(NumberEval.ZERO); // Formulas _never_ evaluate To blank. If a formula appears To have evaluated To // blank, the actual value is empty string. This can be verified with ISBLANK(). } return(value); }