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