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]),
EvalFactory.CreateRefEval("A1"),
EvalFactory.CreateAreaEval("A1:G6", new ValueEval[42]),
new NumberEval(0),
};
ConfirmCountA(59, args);
}
public void TestMissingArg()
{
ValueEval[] values =
{
new NumberEval(25.0),
new NumberEval(26.0),
new NumberEval(28.0),
};
AreaEval arg0 = EvalFactory.CreateAreaEval("A10:C10", values);
ValueEval[] args = new ValueEval[] { arg0, MissingArgEval.instance, new NumberEval(2), };
ValueEval actualResult;
try
{
actualResult = FUNC_INST.Evaluate(args, -1, -1);
}
catch (Exception e)
{
if (e.Message.Equals("Unexpected arg eval type (Npoi.Core.hssf.Record.Formula.Eval.MissingArgEval"))
{
throw new AssertionException("Identified bug 47048b - INDEX() should support missing-arg");
}
throw e;
}
// result should be an area eval "B10:B10"
AreaEval ae = ConfirmAreaEval("B10:B10", actualResult);
actualResult = ae.GetValue(0, 0);
Assert.AreEqual(typeof(NumberEval), actualResult.GetType());
Assert.AreEqual(26.0, ((NumberEval)actualResult).NumberValue, 0.0);
}
public void TestSimpleWildcardValuesString()
{
// Arrange
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("e*"), ae, MATCH_EXACT));
ConfirmInt(3, invokeMatch(new StringEval("*d"), ae, MATCH_EXACT));
ConfirmInt(1, invokeMatch(new StringEval("Al*"), ae, MATCH_EXACT));
ConfirmInt(2, invokeMatch(new StringEval("Char*"), ae, MATCH_EXACT));
ConfirmInt(4, invokeMatch(new StringEval("*eg"), ae, MATCH_EXACT));
ConfirmInt(4, invokeMatch(new StringEval("G?eg"), ae, MATCH_EXACT));
ConfirmInt(4, invokeMatch(new StringEval("??eg"), ae, MATCH_EXACT));
ConfirmInt(4, invokeMatch(new StringEval("G*?eg"), ae, MATCH_EXACT));
ConfirmInt(4, invokeMatch(new StringEval("Hugh"), ae, MATCH_LARGEST_LTE));
ConfirmInt(5, invokeMatch(new StringEval("*Ian*"), ae, MATCH_EXACT));
ConfirmInt(5, invokeMatch(new StringEval("*Ian*"), ae, MATCH_LARGEST_LTE));
}
public void TestUnusualArgs()
{
CultureShim.SetCurrentCulture("en-US");
// 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 is number, other args are strings with fractional digits
ConfirmMid(new NumberEval(123456), new StringEval("3.1"), new StringEval("2.9"), "34");
// startPos is 1x1 area ref, numChars is 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 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 TestBug56655()
{
ValueEval[] a2a9 = new ValueEval[] {
new NumberEval(5),
new NumberEval(4),
new NumberEval(15),
new NumberEval(3),
new NumberEval(22),
new NumberEval(12),
new NumberEval(10),
new NumberEval(33)
};
ValueEval[] args = new ValueEval[] {
EvalFactory.CreateAreaEval("A2:A9", a2a9),
ErrorEval.VALUE_INVALID,
new StringEval("A*"),
};
ValueEval result = InvokeSumifs(args, EC);
Assert.IsTrue(result is ErrorEval, "Expect to have an error when an input is an invalid value, but had: " + result.GetType());
args = new ValueEval[] {
EvalFactory.CreateAreaEval("A2:A9", a2a9),
EvalFactory.CreateAreaEval("A2:A9", a2a9),
ErrorEval.VALUE_INVALID,
};
result = InvokeSumifs(args, EC);
Assert.IsTrue(result is ErrorEval, "Expect to have an error when an input is an invalid value, but had: " + result.GetType());
}
public void TestBug56655c()
{
/*
* setCellFormula(sheet, 0, 0, "B1*C1");
* sheet.getRow(0).createCell(1).setCellValue("A");
* setCellFormula(sheet, 1, 0, "B1*C1");
* sheet.getRow(1).createCell(1).setCellValue("A");
* setCellFormula(sheet, 0, 3, "SUMIFS(A:A,A:A,A2)");
*/
ValueEval[] a0a1 = new ValueEval[] {
NumberEval.ZERO,
NumberEval.ZERO
};
ValueEval[] args = new ValueEval[] {
EvalFactory.CreateAreaEval("A0:A1", a0a1),
EvalFactory.CreateAreaEval("A0:A1", a0a1),
ErrorEval.NAME_INVALID
};
ValueEval result = InvokeSumifs(args, EC);
Assert.IsTrue(result is ErrorEval, "Expect to have an error when an input is an invalid value, but had: " + result.GetType());
Assert.AreEqual(ErrorEval.NAME_INVALID, result);
}
public void TestCriteriaArgRange()
{
ValueEval[] arg0values = new ValueEval[] { _50, _60, _50, _50, _50, _30, };
ValueEval[] arg1values = new ValueEval[] { _30, _40, _50, _60, };
AreaEval arg0;
AreaEval arg1;
ValueEval ve;
arg0 = EvalFactory.CreateAreaEval("A3:B5", arg0values);
arg1 = EvalFactory.CreateAreaEval("A2:D2", arg1values); // single row range
ve = invokeSumif(0, 2, arg0, arg1); // invoking from cell C1
if (ve is NumberEval)
{
NumberEval ne = (NumberEval)ve;
if (ne.NumberValue == 30.0)
{
throw new AssertionException("identified error in SUMIF - criteria arg not Evaluated properly");
}
}
ConfirmDouble(200, ve);
arg0 = EvalFactory.CreateAreaEval("C1:D3", arg0values);
arg1 = EvalFactory.CreateAreaEval("B1:B4", arg1values); // single column range
ve = invokeSumif(3, 0, arg0, arg1); // invoking from cell A4
ConfirmDouble(60, ve);
}
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 TestCountBlank()
{
AreaEval range;
ValueEval[] values;
values = new ValueEval[] {
new NumberEval(0),
new StringEval(""), // note - does not match blank
BoolEval.TRUE,
BoolEval.FALSE,
ErrorEval.DIV_ZERO,
BlankEval.instance,
};
range = EvalFactory.CreateAreaEval("A1:B3", values);
ConfirmCountBlank(1, range);
values = new ValueEval[] {
new NumberEval(0),
new StringEval(""), // note - does not match blank
BlankEval.instance,
BoolEval.FALSE,
BoolEval.TRUE,
BlankEval.instance,
};
range = EvalFactory.CreateAreaEval("A1:B3", values);
ConfirmCountBlank(2, range);
}
public void TestCriteriaPredicateNe_Bug46647()
{
IMatchPredicate mp = Countif.CreateCriteriaPredicate(new StringEval("<>aa"), 0, 0);
Assert.IsNotNull(mp);
StringEval seA = new StringEval("aa"); // this should not match the criteria '<>aa'
StringEval seB = new StringEval("bb"); // this should match
if (mp.Matches(seA) && !mp.Matches(seB))
{
throw new AssertionException("Identified bug 46647");
}
Assert.IsFalse(mp.Matches(seA));
Assert.IsTrue(mp.Matches(seB));
// general Tests for not-equal (<>) operator
AreaEval range;
ValueEval[] values;
values = new ValueEval[] {
new StringEval("aa"),
new StringEval("def"),
new StringEval("aa"),
new StringEval("ghi"),
new StringEval("aa"),
new StringEval("aa"),
};
range = EvalFactory.CreateAreaEval("A1:A6", values);
ConfirmCountIf(2, range, new StringEval("<>aa"));
values = new ValueEval[] {
new StringEval("ab"),
new StringEval("aabb"),
new StringEval("aa"), // match
new StringEval("abb"),
new StringEval("aab"),
new StringEval("ba"), // match
};
range = EvalFactory.CreateAreaEval("A1:A6", values);
ConfirmCountIf(2, range, new StringEval("<>a*b"));
values = new ValueEval[] {
new NumberEval(222),
new NumberEval(222),
new NumberEval(111),
new StringEval("aa"),
new StringEval("111"),
};
range = EvalFactory.CreateAreaEval("A1:A5", values);
ConfirmCountIf(4, range, new StringEval("<>111"));
}
public void TestReferenceResult()
{
ValueEval[] values = new ValueEval[4];
Arrays.Fill(values, NumberEval.ZERO);
AreaEval arg0 = EvalFactory.CreateAreaEval("A1:B2", values);
ValueEval[] args = new ValueEval[] { arg0, new NumberEval(2), new NumberEval(1), };
ValueEval ve = FUNC_INST.Evaluate(args, -1, -1);
ConfirmAreaEval("A2:A2", ve);
}
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 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 TestTildeString()
{
ValueEval[] values =
{
new StringEval("what?"),
new StringEval("all*")
};
AreaEval ae = EvalFactory.CreateAreaEval("A1:A2", values);
ConfirmInt(1, invokeMatch(new StringEval("what~?"), ae, MATCH_EXACT));
ConfirmInt(2, invokeMatch(new StringEval("all~*"), ae, MATCH_EXACT));
}
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 TestScalarSimple()
{
RefEval refEval = EvalFactory.CreateRefEval("A1", new NumberEval(3));
ValueEval[] args =
{
refEval,
new NumberEval(2),
};
ValueEval result = invokeSumproduct(args);
ConfirmDouble(6D, 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 TestExample2()
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
ValueEval[] b2e2 = new ValueEval[]
{
new NumberEval(100),
new NumberEval(390),
new NumberEval(8321),
new NumberEval(500)
};
// 1% 0.5% 3% 4%
ValueEval[] b3e3 = new ValueEval[]
{
new NumberEval(0.01),
new NumberEval(0.005),
new NumberEval(0.03),
new NumberEval(0.04)
};
// 1% 1.3% 2.1% 2%
ValueEval[] b4e4 = new ValueEval[]
{
new NumberEval(0.01),
new NumberEval(0.013),
new NumberEval(0.021),
new NumberEval(0.02)
};
// 0.5% 3% 1% 4%
ValueEval[] b5e5 = new ValueEval[]
{
new NumberEval(0.005),
new NumberEval(0.03),
new NumberEval(0.01),
new NumberEval(0.04)
};
// "=SUMIFS(B2:E2, B3:E3, ">3%", B4:E4, ">=2%")"
ValueEval[] args = new ValueEval[]
{
EvalFactory.CreateAreaEval("B2:E2", b2e2),
EvalFactory.CreateAreaEval("B3:E3", b3e3),
new StringEval(">0.03"), // 3% in the MSFT example
EvalFactory.CreateAreaEval("B4:E4", b4e4),
new StringEval(">=0.02"), // 2% in the MSFT example
};
Confirm(500.0, args);
}
public void TestExample1()
{
ValueEval[] b2b5 = new ValueEval[]
{
new NumberEval(7000),
new NumberEval(14000),
new NumberEval(21000),
new NumberEval(28000),
};
ValueEval[] a2a5 = new ValueEval[]
{
new NumberEval(100000),
new NumberEval(200000),
new NumberEval(300000),
new NumberEval(400000),
};
// "=AVERAGEIF(B2:B5,"<23000")"
ValueEval[] args = new ValueEval[]
{
EvalFactory.CreateAreaEval("B2:B5", b2b5),
new StringEval("<23000"),
};
Confirm(14000, args);
// "=AVERAGEIF(A2:A5,"<250000")"
args = new ValueEval[]
{
EvalFactory.CreateAreaEval("A2:A5", a2a5),
new StringEval("<250000"),
};
Confirm(150000, args);
//"=AVERAGEIF(A2:A5,"<95000")"
args = new ValueEval[]
{
EvalFactory.CreateAreaEval("A2:A5", a2a5),
new StringEval("<95000"),
};
Assert.AreEqual(ErrorEval.VALUE_INVALID, InvokeAverageif(args, EC));
// "=AVERAGEIF(A2:A5,">250000",B2:B5)"
args = new ValueEval[]
{
EvalFactory.CreateAreaEval("A2:A5", a2a5),
new StringEval(">250000"),
EvalFactory.CreateAreaEval("B2:B5", b2b5),
};
Confirm(24500, args);
}
public void TestCol()
{
Function target = new Column();
{
ValueEval[] args = { EvalFactory.CreateRefEval("C5"), };
double actual = NumericFunctionInvoker.Invoke(target, args);
Assert.AreEqual(3, actual, 0D);
}
{
ValueEval[] args = { EvalFactory.CreateAreaEval("E2:H12", new ValueEval[44]), };
double actual = NumericFunctionInvoker.Invoke(target, args);
Assert.AreEqual(5, actual, 0D);
}
}
public void TestBasic()
{
ValueEval[] arg0values = new ValueEval[] { _30, _30, _40, _40, _50, _50 };
ValueEval[] arg2values = new ValueEval[] { _30, _40, _50, _60, _60, _60 };
AreaEval arg0;
AreaEval arg2;
arg0 = EvalFactory.CreateAreaEval("A3:B5", arg0values);
arg2 = EvalFactory.CreateAreaEval("D1:E3", arg2values);
Confirm(60.0, arg0, new NumberEval(30.0));
Confirm(70.0, arg0, new NumberEval(30.0), arg2);
Confirm(100.0, arg0, new StringEval(">45"));
}
public void TestRow()
{
//throw new NotImplementedException();
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 TestAreaArg()
{
ValueEval[] areaValues = new ValueEval[] {
new StringEval("abc"), new StringEval("def"),
new StringEval("ghi"), new StringEval("jkl"),
};
AreaEval ae = EvalFactory.CreateAreaEval("C10:D11", areaValues);
ValueEval ve;
ve = invokeT(ae);
ConfirmString(ve, "abc");
areaValues[0] = new NumberEval(5.0);
ve = invokeT(ae);
ConfirmString(ve, "");
}
public void TestInvalidMatchType()
{
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));
Assert.AreEqual(ErrorEval.REF_INVALID, invokeMatch(new StringEval("Ben"), ae, MATCH_INVALID), "Should return #REF! for invalid match type");
}
private static void ConfirmSubtotal(int function, double expected)
{
ValueEval[] values = new ValueEval[TEST_VALUES0.Length];
for (int i = 0; i < TEST_VALUES0.Length; i++)
{
values[i] = new NumberEval(TEST_VALUES0[i]);
}
AreaEval arg1 = EvalFactory.CreateAreaEval("C1:D5", values);
ValueEval[] args = { new NumberEval(function), arg1 };
ValueEval result = new Subtotal().Evaluate(args, 0, 0);
Assert.AreEqual(typeof(NumberEval), result.GetType());
Assert.AreEqual(expected, ((NumberEval)result).NumberValue, 0.0);
}
public void TestCaseInsensitiveStringComparison()
{
AreaEval range;
ValueEval[] values;
values = new ValueEval[] {
new StringEval("no"),
new StringEval("NO"),
new StringEval("No"),
new StringEval("Yes")
};
range = EvalFactory.CreateAreaEval("A1:A4", values);
ConfirmCountIf(3, range, new StringEval("no"));
ConfirmCountIf(3, range, new StringEval("NO"));
ConfirmCountIf(3, range, new StringEval("No"));
}
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 TestExample2()
{
ValueEval[] b2e2 = new ValueEval[] {
new NumberEval(100),
new NumberEval(390),
new NumberEval(8321),
new NumberEval(500)
};
// 1% 0.5% 3% 4%
ValueEval[] b3e3 = new ValueEval[] {
new NumberEval(0.01),
new NumberEval(0.005),
new NumberEval(0.03),
new NumberEval(0.04)
};
// 1% 1.3% 2.1% 2%
ValueEval[] b4e4 = new ValueEval[] {
new NumberEval(0.01),
new NumberEval(0.013),
new NumberEval(0.021),
new NumberEval(0.02)
};
// 0.5% 3% 1% 4%
ValueEval[] b5e5 = new ValueEval[] {
new NumberEval(0.005),
new NumberEval(0.03),
new NumberEval(0.01),
new NumberEval(0.04)
};
ValueEval[] args;
// "=SUMIFS(B2:E2, B3:E3, ">3%", B4:E4, ">=2%")"
args = new ValueEval[] {
EvalFactory.CreateAreaEval("B2:E2", b2e2),
EvalFactory.CreateAreaEval("B3:E3", b3e3),
new StringEval(">0.03"), // 3% in the MSFT example
EvalFactory.CreateAreaEval("B4:E4", b4e4),
new StringEval(">=0.02"), // 2% in the MSFT example
};
Confirm(500.0, args);
}
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));
}
