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