public void TestSpaceAtStartOfFormula()
        {
            // Simulating cell formula of "= 4" (note space)
            // The same Ptg array can be observed if an excel file is1 saved with that exact formula

            AttrPtg spacePtg = AttrPtg.CreateSpace(AttrPtg.SpaceType.SPACE_BEFORE, 1);

            Ptg[]  ptgs = { spacePtg, new IntPtg(4), };
            String formulaString;

            try
            {
                formulaString = HSSFFormulaParser.ToFormulaString(null, ptgs);
            }
            catch (InvalidOperationException e)
            {
                if (e.Message.Equals("too much stuff left on the stack", StringComparison.InvariantCultureIgnoreCase))
                {
                    throw new AssertFailedException("Identified bug 44609");
                }
                // else some unexpected error
                throw e;
            }
            // FormulaParser strips spaces anyway
            Assert.AreEqual("4", formulaString);

            ptgs          = new Ptg[] { new IntPtg(3), spacePtg, new IntPtg(4), spacePtg, AddPtg.instance, };
            formulaString = HSSFFormulaParser.ToFormulaString(null, ptgs);
            Assert.AreEqual("3+4", formulaString);
        }
 private static bool IsSingleArgSum(Ptg token)
 {
     if (token is AttrPtg)
     {
         AttrPtg attrPtg = (AttrPtg)token;
         return(attrPtg.IsSum);
     }
     return(false);
 }
        private static void ConfirmAttrData(Ptg[] ptgs, int i, int expectedData)
        {
            Ptg ptg = ptgs[i];

            if (!(ptg is AttrPtg))
            {
                throw new AssertionException("Token[" + i + "] was not AttrPtg as expected");
            }
            AttrPtg attrPtg = (AttrPtg)ptg;

            Assert.AreEqual(expectedData, attrPtg.Data);
        }
        public void TestNestedFunctionIf()
        {
            Ptg[] ptgs = ParseFormula("IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))");
            Assert.AreEqual(11, 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[5] is FuncVarPtg), "Average Function Set correctly");
        }
Exemple #5
0
        /**
         * The IF() function Gets marked up with two or three tAttr Tokens.
         * Similar logic will be required for CHOOSE() when it is supported
         *
         * See excelfileformat.pdf sec 3.10.5 "tAttr (19H)
         */
        private void CollectIfPtgs(TokenCollector temp)
        {
            // condition goes first
            GetChildren()[0].CollectPtgs(temp);

            // placeholder for tAttrIf
            int ifAttrIndex = temp.CreatePlaceholder();

            // true parameter
            GetChildren()[1].CollectPtgs(temp);

            // placeholder for first skip attr
            int skipAfterTrueParamIndex = temp.CreatePlaceholder();
            int trueParamSize           = temp.sumTokenSizes(ifAttrIndex + 1, skipAfterTrueParamIndex);

            AttrPtg attrIf = AttrPtg.CreateIf(trueParamSize + 4);// distance to start of false parameter/tFuncVar. +4 for tAttrSkip after true

            if (GetChildren().Length > 2)
            {
                // false param present

                // false parameter
                GetChildren()[2].CollectPtgs(temp);

                int skipAfterFalseParamIndex = temp.CreatePlaceholder();
                int falseParamSize           = temp.sumTokenSizes(skipAfterTrueParamIndex + 1, skipAfterFalseParamIndex);

                AttrPtg attrSkipAfterTrue  = AttrPtg.CreateSkip(falseParamSize + 4 + 4 - 1); // 1 less than distance to end of if FuncVar(size=4). +4 for attr skip before
                AttrPtg attrSkipAfterFalse = AttrPtg.CreateSkip(4 - 1);                      // 1 less than distance to end of if FuncVar(size=4).

                temp.SetPlaceholder(ifAttrIndex, attrIf);
                temp.SetPlaceholder(skipAfterTrueParamIndex, attrSkipAfterTrue);
                temp.SetPlaceholder(skipAfterFalseParamIndex, attrSkipAfterFalse);
            }
            else
            {
                // false parameter not present
                AttrPtg attrSkipAfterTrue = AttrPtg.CreateSkip(4 - 1); // 1 less than distance to end of if FuncVar(size=4).

                temp.SetPlaceholder(ifAttrIndex, attrIf);
                temp.SetPlaceholder(skipAfterTrueParamIndex, attrSkipAfterTrue);
            }

            temp.Add(GetToken());
        }
        public void TestYN()
        {
            Ptg[] ptgs = ParseFormula("IF(TRUE,\"Y\",\"N\")");
            Assert.AreEqual(7, ptgs.Length);

            BoolPtg   flag  = (BoolPtg)ptgs[0];
            AttrPtg   funif = (AttrPtg)ptgs[1];
            StringPtg y     = (StringPtg)ptgs[2];
            AttrPtg   goto1 = (AttrPtg)ptgs[3];
            StringPtg n     = (StringPtg)ptgs[4];


            Assert.AreEqual(true, flag.Value);
            Assert.AreEqual("Y", y.Value);
            Assert.AreEqual("N", n.Value);
            Assert.AreEqual("IF", funif.ToFormulaString());
            Assert.IsTrue(goto1.IsSkip, "Goto ptg exists");
        }
        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");
        }
        /**
         * Static method To convert an array of {@link Ptg}s in RPN order
         * To a human readable string format in infix mode.
         * @param book  used for defined names and 3D references
         * @param ptgs  must not be <c>null</c>
         * @return a human readable String
         */
        public static String ToFormulaString(IFormulaRenderingWorkbook book, Ptg[] ptgs)
        {
            if (ptgs == null || ptgs.Length == 0)
            {
                throw new ArgumentException("ptgs must not be null");
            }
            Stack stack = new Stack();

            for (int i = 0; i < ptgs.Length; i++)
            {
                Ptg ptg = ptgs[i];
                // TODO - what about MemNoMemPtg?
                if (ptg is MemAreaPtg || ptg is MemFuncPtg || ptg is MemErrPtg)
                {
                    // marks the start of a list of area expressions which will be naturally combined
                    // by their trailing operators (e.g. UnionPtg)
                    // TODO - Put comment and throw exception in ToFormulaString() of these classes
                    continue;
                }
                if (ptg is ParenthesisPtg)
                {
                    String contents = (String)stack.Pop();
                    stack.Push("(" + contents + ")");
                    continue;
                }
                if (ptg is AttrPtg)
                {
                    AttrPtg attrPtg = ((AttrPtg)ptg);
                    if (attrPtg.IsOptimizedIf || attrPtg.IsOptimizedChoose || attrPtg.IsSkip)
                    {
                        continue;
                    }
                    if (attrPtg.IsSpace)
                    {
                        // POI currently doesn't render spaces in formulas
                        continue;
                        // but if it ever did, care must be taken:
                        // tAttrSpace comes *before* the operand it applies To, which may be consistent
                        // with how the formula text appears but is against the RPN ordering assumed here
                    }
                    if (attrPtg.IsSemiVolatile)
                    {
                        // similar To tAttrSpace - RPN is violated
                        continue;
                    }
                    if (attrPtg.IsSum)
                    {
                        String[] operands = GetOperands(stack, attrPtg.NumberOfOperands);
                        stack.Push(attrPtg.ToFormulaString(operands));
                        continue;
                    }
                    throw new Exception("Unexpected tAttr: " + attrPtg.ToString());
                }

                if (ptg is WorkbookDependentFormula)
                {
                    WorkbookDependentFormula optg = (WorkbookDependentFormula)ptg;
                    stack.Push(optg.ToFormulaString(book));
                    continue;
                }
                if (!(ptg is OperationPtg))
                {
                    stack.Push(ptg.ToFormulaString());
                    continue;
                }

                OperationPtg o         = (OperationPtg)ptg;
                String[]     operands1 = GetOperands(stack, o.NumberOfOperands);
                stack.Push(o.ToFormulaString(operands1));
            }
            if (stack.Count == 0)
            {
                // inspection of the code above reveals that every stack.pop() is followed by a
                // stack.push(). So this is either an internal error or impossible.
                throw new InvalidOperationException("Stack underflow");
            }
            String result = (String)stack.Pop();

            if (stack.Count != 0)
            {
                // Might be caused by some Tokens like AttrPtg and Mem*Ptg, which really shouldn't
                // Put anything on the stack
                throw new InvalidOperationException("too much stuff left on the stack");
            }
            return(result);
        }
        // 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 = FuncVarPtg.SUM;//.Create("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");
            }
            return(DereferenceResult(value, ec.RowIndex, ec.ColumnIndex));
        }
        // visibility raised for testing
        /* package */

        public ValueEval EvaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs)
        {
            string dbgIndentStr = "";           // always init. to non-null just for defensive avoiding NPE

            if (dbgEvaluationOutputForNextEval)
            {
                // first evaluation call when ouput is desired, so iit. this evaluator instance
                dbgEvaluationOutputIndent      = 1;
                dbgEvaluationOutputForNextEval = false;
            }
            if (dbgEvaluationOutputIndent > 0)
            {
                // init. indent string to needed spaces (create as substring vom very long space-only string;
                // limit indendation for deep recursions)
                dbgIndentStr = "                                                                                                    ";
                dbgIndentStr = dbgIndentStr.Substring(0, Math.Min(dbgIndentStr.Length, dbgEvaluationOutputIndent * 2));
                EVAL_LOG.Log(POILogger.WARN, dbgIndentStr
                             + "- evaluateFormula('" + ec.GetRefEvaluatorForCurrentSheet().SheetNameRange
                             + "'/" + new CellReference(ec.RowIndex, ec.ColumnIndex).FormatAsString()
                             + "): " + Arrays.ToString(ptgs).Replace("\\Qorg.apache.poi.ss.formula.ptg.\\E", ""));
                dbgEvaluationOutputIndent++;
            }

            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 (dbgEvaluationOutputIndent > 0)
                {
                    EVAL_LOG.Log(POILogger.INFO, dbgIndentStr + "  * ptg " + i + ": " + ptg.ToString());
                }
                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 = FuncVarPtg.SUM;//.Create("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 &&
                                // in order to verify that there is no third param, we need to check
                                // if we really have the IF next or some other FuncVarPtg as third param, e.g. ROW()/COLUMN()!
                                ((FuncVarPtg)nextPtg).FunctionIndex == FunctionMetadataRegistry.FUNCTION_INDEX_IF)
                            {
                                // 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 || ptg is MemAreaPtg)
                {
                    // 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);
                if (dbgEvaluationOutputIndent > 0)
                {
                    EVAL_LOG.Log(POILogger.INFO, dbgIndentStr + "    = " + opResult.ToString());
                }
            }

            ValueEval value = ((ValueEval)stack.Pop());

            if (stack.Count != 0)
            {
                throw new InvalidOperationException("evaluation stack not empty");
            }
            ValueEval result = DereferenceResult(value, ec.RowIndex, ec.ColumnIndex);

            if (dbgEvaluationOutputIndent > 0)
            {
                EVAL_LOG.Log(POILogger.INFO, dbgIndentStr + "finshed eval of "
                             + new CellReference(ec.RowIndex, ec.ColumnIndex).FormatAsString()
                             + ": " + result.ToString());
                dbgEvaluationOutputIndent--;
                if (dbgEvaluationOutputIndent == 1)
                {
                    // this evaluation is done, reset indent to stop logging
                    dbgEvaluationOutputIndent = -1;
                }
            } // if
            return(result);
        }
Exemple #11
0
        public void ParseStructuredReferences()
        {
            XSSFWorkbook           wb  = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
            XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);

            Ptg[] ptgs;

            /*
             * The following cases are tested (copied from FormulaParser.parseStructuredReference)
             * 1 Table1[col]
             * 2 Table1[[#Totals],[col]]
             * 3 Table1[#Totals]
             * 4 Table1[#All]
             * 5 Table1[#Data]
             * 6 Table1[#Headers]
             * 7 Table1[#Totals]
             * 8 Table1[#This Row]
             * 9 Table1[[#All],[col]]
             * 10 Table1[[#Headers],[col]]
             * 11 Table1[[#Totals],[col]]
             * 12 Table1[[#All],[col1]:[col2]]
             * 13 Table1[[#Data],[col1]:[col2]]
             * 14 Table1[[#Headers],[col1]:[col2]]
             * 15 Table1[[#Totals],[col1]:[col2]]
             * 16 Table1[[#Headers],[#Data],[col2]]
             * 17 Table1[[#This Row], [col1]]
             * 18 Table1[ [col1]:[col2] ]
             */
            String tbl = "\\_Prime.1";
            String noTotalsRowReason = ": Tables without a Totals row should return #REF! on [#Totals]";

            ////// Case 1: Evaluate Table1[col] with apostrophe-escaped #-signs ////////
            ptgs = Parse(fpb, "SUM(" + tbl + "[calc='#*'#])");
            Assert.AreEqual(2, ptgs.Length);
            // Area3DPxg [sheet=Table ! A2:A7]
            Assert.IsTrue(ptgs[0] is Area3DPxg);
            Area3DPxg ptg0 = (Area3DPxg)ptgs[0];

            Assert.AreEqual("Table", ptg0.SheetName);
            Assert.AreEqual("A2:A7", ptg0.Format2DRefAsString());
            // Note: structured references are evaluated and resolved to regular 3D area references.
            Assert.AreEqual("Table!A2:A7", ptg0.ToFormulaString());
            // AttrPtg [sum ]
            Assert.IsTrue(ptgs[1] is AttrPtg);
            AttrPtg ptg1 = (AttrPtg)ptgs[1];

            Assert.IsTrue(ptg1.IsSum);

            ////// Case 1: Evaluate "Table1[col]" ////////
            ptgs = Parse(fpb, tbl + "[Name]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!B2:B7", ptgs[0].ToFormulaString(), "Table1[col]");

            ////// Case 2: Evaluate "Table1[[#Totals],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Totals],[col]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]]" + noTotalsRowReason);

            ////// Case 3: Evaluate "Table1[#Totals]" ////////
            ptgs = Parse(fpb, tbl + "[#Totals]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[#Totals]" + noTotalsRowReason);

            ////// Case 4: Evaluate "Table1[#All]" ////////
            ptgs = Parse(fpb, tbl + "[#All]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!A1:C7", ptgs[0].ToFormulaString(), "Table1[#All]");

            ////// Case 5: Evaluate "Table1[#Data]" (excludes Header and Data rows) ////////
            ptgs = Parse(fpb, tbl + "[#Data]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!A2:C7", ptgs[0].ToFormulaString(), "Table1[#Data]");

            ////// Case 6: Evaluate "Table1[#Headers]" ////////
            ptgs = Parse(fpb, tbl + "[#Headers]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!A1:C1", ptgs[0].ToFormulaString(), "Table1[#Headers]");

            ////// Case 7: Evaluate "Table1[#Totals]" ////////
            ptgs = Parse(fpb, tbl + "[#Totals]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[#Totals]" + noTotalsRowReason);

            ////// Case 8: Evaluate "Table1[#This Row]" ////////
            ptgs = Parse(fpb, tbl + "[#This Row]", 2);
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!A3:C3", ptgs[0].ToFormulaString(), "Table1[#This Row]");

            ////// Evaluate "Table1[@]" (equivalent to "Table1[#This Row]") ////////
            ptgs = Parse(fpb, tbl + "[@]", 2);
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!A3:C3", ptgs[0].ToFormulaString());

            ////// Evaluate "Table1[#This Row]" when rowIndex is outside Table ////////
            ptgs = Parse(fpb, tbl + "[#This Row]", 10);
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.VALUE_INVALID, ptgs[0], "Table1[#This Row]");

            ////// Evaluate "Table1[@]" when rowIndex is outside Table ////////
            ptgs = Parse(fpb, tbl + "[@]", 10);
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.VALUE_INVALID, ptgs[0], "Table1[@]");
            ////// Evaluate "Table1[[#Data],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Data], [Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!C2:C7", ptgs[0].ToFormulaString(), "Table1[[#Data],[col]]");

            ////// Case 9: Evaluate "Table1[[#All],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#All], [Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!C1:C7", ptgs[0].ToFormulaString(), "Table1[[#All],[col]]");

            ////// Case 10: Evaluate "Table1[[#Headers],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Headers], [Number]]");
            Assert.AreEqual(1, ptgs.Length);
            // also acceptable: Table1!B1
            Assert.AreEqual("Table!C1:C1", ptgs[0].ToFormulaString(), "Table1[[#Headers],[col]]");
            ////// Case 11: Evaluate "Table1[[#Totals],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Totals],[Name]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]]" + noTotalsRowReason);

            ////// Case 12: Evaluate "Table1[[#All],[col1]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[#All], [Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!B1:C7", ptgs[0].ToFormulaString(), "Table1[[#All],[col1]:[col2]]");

            ////// Case 13: Evaluate "Table1[[#Data],[col]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Data], [Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!B2:C7", ptgs[0].ToFormulaString(), "Table1[[#Data],[col]:[col2]]");

            ////// Case 14: Evaluate "Table1[[#Headers],[col1]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Headers], [Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!B1:C1", ptgs[0].ToFormulaString(), "Table1[[#Headers],[col1]:[col2]]");

            ////// Case 15: Evaluate "Table1[[#Totals],[col]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Totals], [Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]:[col2]]" + noTotalsRowReason);

            ////// Case 16: Evaluate "Table1[[#Headers],[#Data],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Headers],[#Data],[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!C1:C7", ptgs[0].ToFormulaString(), "Table1[[#Headers],[#Data],[col]]");

            ////// Case 17: Evaluate "Table1[[#This Row], [col1]]" ////////
            ptgs = Parse(fpb, tbl + "[[#This Row], [Number]]", 2);
            Assert.AreEqual(1, ptgs.Length);
            // also acceptable: Table!C3
            Assert.AreEqual("Table!C3:C3", ptgs[0].ToFormulaString(), "Table1[[#This Row], [col1]]");

            ////// Case 18: Evaluate "Table1[[col]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!B2:C7", ptgs[0].ToFormulaString(), "Table1[[col]:[col2]]");
            wb.Close();
        }