public void TestMatchArgTypeArea()
{
ValueEval[] values =
{
new NumberEval(4),
new NumberEval(5),
new NumberEval(10),
new NumberEval(10),
new NumberEval(25),
};
AreaEval ae = EvalFactory.CreateAreaEval("A1:A5", values);
AreaEval matchAE = EvalFactory.CreateAreaEval("C1:C1", new ValueEval[] { MATCH_LARGEST_LTE, });
try
{
ConfirmInt(4, InvokeMatch(new NumberEval(10), ae, matchAE));
}
catch (Exception e)
{
if (e.Message.StartsWith("Unexpected match_type type"))
{
// identified bug 44421
Assert.Fail(e.Message);
}
// some other error ??
throw e;
}
}
public void TestCountA()
{
ValueEval[] args;
args = new ValueEval[] {
new NumberEval(0),
};
ConfirmCountA(1, args);
args = new ValueEval[] {
new NumberEval(0),
new NumberEval(0),
new StringEval(""),
};
ConfirmCountA(3, args);
args = new ValueEval[] {
EvalFactory.CreateAreaEval("D2:F5", new ValueEval[12]),
};
ConfirmCountA(12, args);
args = new ValueEval[] {
EvalFactory.CreateAreaEval("D1:F5", new ValueEval[15]), // 15
EvalFactory.CreateRefEval("A1"),
EvalFactory.CreateAreaEval("A1:G6", new ValueEval[42]), // 42
new NumberEval(0),
};
ConfirmCountA(59, args);
}
/**
* @param areaRefString in Excel notation e.g. 'D2:E97'
* @param dValues array of evaluated values for the area reference
* @param rowNum 1-based
* @param colNum 1-based, pass -1 to signify argument not present
*/
private static void ConfirmAreaEval(String areaRefString, double[] dValues,
int rowNum, int colNum, double expectedResult)
{
ValueEval[] values = new ValueEval[dValues.Length];
for (int i = 0; i < values.Length; i++)
{
values[i] = new NumberEval(dValues[i]);
}
AreaEval arg0 = EvalFactory.CreateAreaEval(areaRefString, values);
ValueEval[] args;
if (colNum > 0)
{
args = new ValueEval[] { arg0, new NumberEval(rowNum), new NumberEval(colNum), };
}
else
{
args = new ValueEval[] { arg0, new NumberEval(rowNum), };
}
double actual = NumericFunctionInvoker.Invoke(new Index(), args);
Assert.AreEqual(expectedResult, actual, 0D);
}
private static void ConfirmRowsFunc(String areaRefStr, int nCols, int nRows)
{
ValueEval[] args = { EvalFactory.CreateAreaEval(areaRefStr, new ValueEval[nCols * nRows]), };
double actual = NumericFunctionInvoker.Invoke(new Rows(), args);
Assert.AreEqual(nRows, actual, 0D);
}
public void TestMismatchAreaDimensions()
{
AreaEval aeA = EvalFactory.CreateAreaEval("A1:A3", new ValueEval[3]);
AreaEval aeB = EvalFactory.CreateAreaEval("B1:D1", new ValueEval[3]);
ValueEval[] args;
args = new ValueEval[] { aeA, aeB, };
Assert.AreEqual(ErrorEval.VALUE_INVALID, InvokeSumproduct(args));
args = new ValueEval[] { aeA, new NumberEval(5), };
Assert.AreEqual(ErrorEval.VALUE_INVALID, InvokeSumproduct(args));
}
public void TestRows()
{
ConfirmRowsFunc("A1:F1", 6, 1);
ConfirmRowsFunc("A1:C2", 3, 2);
ConfirmRowsFunc("A1:B3", 2, 3);
ConfirmRowsFunc("A1:A6", 1, 6);
ValueEval[] args = { EvalFactory.CreateRefEval("C5"), };
double actual = NumericFunctionInvoker.Invoke(new Rows(), args);
Assert.AreEqual(1, actual, 0D);
}
public void TestScalarSimple()
{
RefEval refEval = EvalFactory.CreateRefEval("A1", new NumberEval(3));
ValueEval[] args =
{
refEval,
new NumberEval(2),
};
ValueEval result = InvokeSumproduct(args);
ConfirmDouble(6D, result);
}
public void TestOneByOneArea()
{
AreaEval ae = EvalFactory.CreateAreaEval("A1:A1", new ValueEval[] { new NumberEval(7), });
ValueEval[] args =
{
ae,
new NumberEval(2),
};
ValueEval result = InvokeSumproduct(args);
ConfirmDouble(14D, result);
}
public void TestAreaWithErrorCell()
{
ValueEval[] aValues =
{
ErrorEval.REF_INVALID,
null,
};
AreaEval aeA = EvalFactory.CreateAreaEval("A1:A2", aValues);
AreaEval aeB = EvalFactory.CreateAreaEval("B1:B2", new ValueEval[2]);
ValueEval[] args = { aeA, aeB, };
Assert.AreEqual(ErrorEval.REF_INVALID, InvokeSumproduct(args));
}
public void TestRow()
{
Function tarGet = new Row();
{
ValueEval[] args = { EvalFactory.CreateRefEval("C5"), };
double actual = NumericFunctionInvoker.Invoke(tarGet, args);
Assert.AreEqual(5, actual, 0D);
}
{
ValueEval[] args = { EvalFactory.CreateAreaEval("E2:H12", new ValueEval[44]), };
double actual = NumericFunctionInvoker.Invoke(tarGet, args);
Assert.AreEqual(2, actual, 0D);
}
}
public void TestSimpleBoolean()
{
ValueEval[] values =
{
BoolEval.FALSE,
BoolEval.FALSE,
BoolEval.TRUE,
BoolEval.TRUE,
};
AreaEval ae = EvalFactory.CreateAreaEval("A1:A4", values);
// Note String comparisons are case insensitive
ConfirmInt(2, InvokeMatch(BoolEval.FALSE, ae, MATCH_LARGEST_LTE));
ConfirmInt(1, InvokeMatch(BoolEval.FALSE, ae, MATCH_EXACT));
ConfirmInt(4, InvokeMatch(BoolEval.TRUE, ae, MATCH_LARGEST_LTE));
ConfirmInt(3, InvokeMatch(BoolEval.TRUE, ae, MATCH_EXACT));
}
public void TestReversedNumber()
{
ValueEval[] values =
{
new NumberEval(25),
new NumberEval(10),
new NumberEval(10),
new NumberEval(10),
new NumberEval(4),
};
AreaEval ae = EvalFactory.CreateAreaEval("A1:A5", values);
ConfirmInt(2, InvokeMatch(new NumberEval(10), ae, MATCH_SMALLEST_GTE));
ConfirmInt(2, InvokeMatch(new NumberEval(10), ae, MATCH_EXACT));
ConfirmInt(4, InvokeMatch(new NumberEval(9), ae, MATCH_SMALLEST_GTE));
ConfirmInt(1, InvokeMatch(new NumberEval(20), ae, MATCH_SMALLEST_GTE));
Assert.AreEqual(ErrorEval.NA, InvokeMatch(new NumberEval(20), ae, MATCH_EXACT));
Assert.AreEqual(ErrorEval.NA, InvokeMatch(new NumberEval(26), ae, MATCH_SMALLEST_GTE));
}
public void TestSimpleNumber()
{
ValueEval[] values =
{
new NumberEval(4),
new NumberEval(5),
new NumberEval(10),
new NumberEval(10),
new NumberEval(25),
};
AreaEval ae = EvalFactory.CreateAreaEval("A1:A5", values);
ConfirmInt(2, InvokeMatch(new NumberEval(5), ae, MATCH_LARGEST_LTE));
ConfirmInt(2, InvokeMatch(new NumberEval(5), ae, MATCH_EXACT));
ConfirmInt(4, InvokeMatch(new NumberEval(10), ae, MATCH_LARGEST_LTE));
ConfirmInt(3, InvokeMatch(new NumberEval(10), ae, MATCH_EXACT));
ConfirmInt(4, InvokeMatch(new NumberEval(20), ae, MATCH_LARGEST_LTE));
Assert.AreEqual(ErrorEval.NA, InvokeMatch(new NumberEval(20), ae, MATCH_EXACT));
}
public void TestHeterogeneous()
{
ValueEval[] values =
{
new NumberEval(4),
BoolEval.FALSE,
new NumberEval(5),
new StringEval("Albert"),
BoolEval.FALSE,
BoolEval.TRUE,
new NumberEval(10),
new StringEval("Charles"),
new StringEval("Ed"),
new NumberEval(10),
new NumberEval(25),
BoolEval.TRUE,
new StringEval("Ed"),
};
AreaEval ae = EvalFactory.CreateAreaEval("A1:A13", values);
Assert.AreEqual(ErrorEval.NA, InvokeMatch(new StringEval("Aaron"), ae, MATCH_LARGEST_LTE));
ConfirmInt(5, InvokeMatch(BoolEval.FALSE, ae, MATCH_LARGEST_LTE));
ConfirmInt(2, InvokeMatch(BoolEval.FALSE, ae, MATCH_EXACT));
ConfirmInt(3, InvokeMatch(new NumberEval(5), ae, MATCH_LARGEST_LTE));
ConfirmInt(3, InvokeMatch(new NumberEval(5), ae, MATCH_EXACT));
ConfirmInt(8, InvokeMatch(new StringEval("CHARLES"), ae, MATCH_EXACT));
ConfirmInt(4, InvokeMatch(new StringEval("Ben"), ae, MATCH_LARGEST_LTE));
ConfirmInt(13, InvokeMatch(new StringEval("ED"), ae, MATCH_LARGEST_LTE));
ConfirmInt(9, InvokeMatch(new StringEval("ED"), ae, MATCH_EXACT));
ConfirmInt(13, InvokeMatch(new StringEval("Hugh"), ae, MATCH_LARGEST_LTE));
Assert.AreEqual(ErrorEval.NA, InvokeMatch(new StringEval("Hugh"), ae, MATCH_EXACT));
ConfirmInt(11, InvokeMatch(new NumberEval(30), ae, MATCH_LARGEST_LTE));
ConfirmInt(12, InvokeMatch(BoolEval.TRUE, ae, MATCH_LARGEST_LTE));
}
public void TestCountIfWithCriteriaReference()
{
ValueEval[] values =
{
new NumberEval(22),
new NumberEval(25),
new NumberEval(21),
new NumberEval(25),
new NumberEval(25),
new NumberEval(25),
};
AreaEval arg0 = EvalFactory.CreateAreaEval("C1:C6", values);
ValueEval criteriaArg = EvalFactory.CreateRefEval("A1", new NumberEval(25));
ValueEval[] args = { arg0, criteriaArg, };
double actual = NumericFunctionInvoker.Invoke(new Countif(), args);
Assert.AreEqual(4, actual, 0D);
}
public void TestSimpleString()
{
ValueEval[] values =
{
new StringEval("Albert"),
new StringEval("Charles"),
new StringEval("Ed"),
new StringEval("Greg"),
new StringEval("Ian"),
};
AreaEval ae = EvalFactory.CreateAreaEval("A1:A5", values);
// Note String comparisons are case insensitive
ConfirmInt(3, InvokeMatch(new StringEval("Ed"), ae, MATCH_LARGEST_LTE));
ConfirmInt(3, InvokeMatch(new StringEval("eD"), ae, MATCH_LARGEST_LTE));
ConfirmInt(3, InvokeMatch(new StringEval("Ed"), ae, MATCH_EXACT));
ConfirmInt(3, InvokeMatch(new StringEval("ed"), ae, MATCH_EXACT));
ConfirmInt(4, InvokeMatch(new StringEval("Hugh"), ae, MATCH_LARGEST_LTE));
Assert.AreEqual(ErrorEval.NA, InvokeMatch(new StringEval("Hugh"), ae, MATCH_EXACT));
}
public void TestCountIf()
{
AreaEval range;
ValueEval[] values;
// when criteria is1 a bool value
values = new ValueEval[] {
new NumberEval(0),
new StringEval("TRUE"), // note - does not match bool TRUE
BoolEval.TRUE,
BoolEval.FALSE,
BoolEval.TRUE,
BlankEval.instance,
};
range = EvalFactory.CreateAreaEval("A1:B3", values);
ConfirmCountIf(2, range, BoolEval.TRUE);
// when criteria is1 numeric
values = new ValueEval[] {
new NumberEval(0),
new StringEval("2"),
new StringEval("2.001"),
new NumberEval(2),
new NumberEval(2),
BoolEval.TRUE,
};
range = EvalFactory.CreateAreaEval("A1:B3", values);
ConfirmCountIf(3, range, new NumberEval(2));
// note - same results when criteria is1 a string that parses as the number with the same value
ConfirmCountIf(3, range, new StringEval("2.00"));
//if (false)
//{ // not supported yet:
// // when criteria is1 an expression (starting with a comparison operator)
// ConfirmCountIf(4, range, new StringEval(">1"));
//}
}
public void TestUnusualArgs()
{
// startPos with fractional digits
ConfirmMid(new StringEval("galactic"), new NumberEval(3.1), new NumberEval(4), "lact");
// string startPos
ConfirmMid(new StringEval("galactic"), new StringEval("3"), new NumberEval(4), "lact");
// text (first) arg type is1 number, other args are strings with fractional digits
ConfirmMid(new NumberEval(123456), new StringEval("3.1"), new StringEval("2.9"), "34");
// startPos is1 1x1 area ref, numChars is1 cell ref
AreaEval aeStart = EvalFactory.CreateAreaEval("A1:A1", new ValueEval[] { new NumberEval(2), });
RefEval reNumChars = EvalFactory.CreateRefEval("B1", new NumberEval(3));
ConfirmMid(new StringEval("galactic"), aeStart, reNumChars, "ala");
ConfirmMid(new StringEval("galactic"), new NumberEval(3.1), BlankEval.instance, "");
ConfirmMid(new StringEval("galactic"), new NumberEval(3), BoolEval.FALSE, "");
ConfirmMid(new StringEval("galactic"), new NumberEval(3), BoolEval.TRUE, "l");
ConfirmMid(BlankEval.instance, new NumberEval(3), BoolEval.TRUE, "");
}
public void TestAreaSimple()
{
ValueEval[] aValues =
{
new NumberEval(2),
new NumberEval(4),
new NumberEval(5),
};
ValueEval[] bValues =
{
new NumberEval(3),
new NumberEval(6),
new NumberEval(7),
};
AreaEval aeA = EvalFactory.CreateAreaEval("A1:A3", aValues);
AreaEval aeB = EvalFactory.CreateAreaEval("B1:B3", bValues);
ValueEval[] args = { aeA, aeB, };
ValueEval result = InvokeSumproduct(args);
ConfirmDouble(65D, result);
}
private static ValueEval CreateAreaEval(ValueEval[] values)
{
String refStr = "A1:A" + values.Length;
return(EvalFactory.CreateAreaEval(refStr, values));
}
/**
* Simulates call: T(A1)
* where cell A1 has the specified innerValue
*/
private ValueEval InvokeTWithReference(ValueEval innerValue)
{
ValueEval arg = EvalFactory.CreateRefEval("$B$2", innerValue);
return(InvokeT(arg));
}