/**
* @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);
}
/**
* Returns a CellValue wrapper around the supplied ValueEval instance.
*/
protected override CellValue EvaluateFormulaCellValue(ICell cell)
{
IEvaluationCell evalCell = ToEvaluationCell(cell);
ValueEval eval = _bookEvaluator.Evaluate(evalCell);
if (eval is NumberEval)
{
NumberEval ne = (NumberEval)eval;
return(new CellValue(ne.NumberValue));
}
if (eval is BoolEval)
{
BoolEval be = (BoolEval)eval;
return(CellValue.ValueOf(be.BooleanValue));
}
if (eval is StringEval)
{
StringEval ne = (StringEval)eval;
return(new CellValue(ne.StringValue));
}
if (eval is ErrorEval)
{
return(CellValue.GetError(((ErrorEval)eval).ErrorCode));
}
throw new Exception("Unexpected eval class (" + eval.GetType().Name + ")");
}
public bool Matches(ValueEval x)
{
double testValue;
if (x is StringEval)
{
// if the tarGet(x) Is a string, but Parses as a number
// it may still Count as a match
StringEval se = (StringEval)x;
double val = OperandResolver.ParseDouble(se.StringValue);
if (double.IsNaN(val))
{
// x Is text that Is not a number
return(false);
}
testValue = val;
}
else if (x is NumberEval)
{
NumberEval ne = (NumberEval)x;
testValue = ne.NumberValue;
}
else
{
return(false);
}
return(_operator.Evaluate(testValue.CompareTo(_value)));
}
public void CheckOffset()
{
for (int offset = -12; offset <= 12; offset++)
{
DateTime startDate = DateTime.Now;
//the month
DateTime expDate = startDate.AddMonths(offset);
//next month
expDate = expDate.AddMonths(1);
//first day of next month
expDate = new DateTime(expDate.Year, expDate.Month, 1);
//last day of the month
expDate = expDate.AddDays(-1);
ValueEval[] ve =
{
new NumberEval(DateUtil.GetExcelDate(startDate)),
new NumberEval(offset)
};
NumberEval result = (NumberEval)eOMonth.Evaluate(ve, ec);
DateTime actDate = DateUtil.GetJavaDate(result.NumberValue);
Assert.AreEqual(expDate, actDate);
}
}
private void CheckValue(double startDate, int monthInc, double expectedResult)
{
ValueEval[] ve = new ValueEval[] { new NumberEval(startDate), new NumberEval(monthInc) };
NumberEval result = (NumberEval)eOMonth.Evaluate(ve, ec);
Assert.AreEqual(expectedResult, result.NumberValue);
}
public void TestGetValue_bug44950()
{
// TODO - this Test probably isn't Testing much anymore
AreaPtg ptg = new AreaPtg("B2:D3");
NumberEval one = new NumberEval(1);
ValueEval[] values =
{
one,
new NumberEval(2),
new NumberEval(3),
new NumberEval(4),
new NumberEval(5),
new NumberEval(6),
};
AreaEval ae = EvalFactory.CreateAreaEval(ptg, values);
if (one == ae.GetAbsoluteValue(1, 2))
{
throw new AssertionException("Identified bug 44950 a");
}
Confirm(1, ae, 1, 1);
Confirm(2, ae, 1, 2);
Confirm(3, ae, 1, 3);
Confirm(4, ae, 2, 1);
Confirm(5, ae, 2, 2);
Confirm(6, ae, 2, 3);
}
/**
* Returns a CellValue wrapper around the supplied ValueEval instance.
*/
private CellValue EvaluateFormulaCellValue(ICell cell)
{
if (!(cell is XSSFCell))
{
throw new ArgumentException("Unexpected type of cell: " + cell.GetType() + "." +
" Only XSSFCells can be Evaluated.");
}
ValueEval eval = _bookEvaluator.Evaluate(new XSSFEvaluationCell((XSSFCell)cell));
if (eval is NumberEval)
{
NumberEval ne = (NumberEval)eval;
return(new CellValue(ne.NumberValue));
}
if (eval is BoolEval)
{
BoolEval be = (BoolEval)eval;
return(CellValue.ValueOf(be.BooleanValue));
}
if (eval is StringEval)
{
StringEval ne = (StringEval)eval;
return(new CellValue(ne.StringValue));
}
if (eval is ErrorEval)
{
return(CellValue.GetError(((ErrorEval)eval).ErrorCode));
}
throw new Exception("Unexpected eval class (" + eval.GetType().Name + ")");
}
public void TestRefEvalStartDate()
{
ValueEval[] ve = new ValueEval[] { new RefEvalImplementation(new NumberEval(DATE_1902_09_26)), new NumberEval(0) };
NumberEval result = (NumberEval)eOMonth.Evaluate(ve, ec);
Assert.AreEqual(DATE_1902_09_30, result.NumberValue);
}
public void TestEOMonthBlankValueEval()
{
ValueEval[] ve = new ValueEval[] { BlankEval.instance, new NumberEval(0) };
NumberEval Evaluate = (NumberEval)eOMonth.Evaluate(ve, ec);
Assert.AreEqual(DATE_1900_01_31, Evaluate.NumberValue, "Blank is handled as 0");
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
double result;
if (args.Length != 2)
{
return(ErrorEval.VALUE_INVALID);
}
try
{
double startDateAsNumber = GetValue(args[0]);
NumberEval offsetInYearsValue = (NumberEval)args[1];
int offsetInMonthAsNumber = (int)offsetInYearsValue.NumberValue;
// resolve the arguments
DateTime startDate = DateUtil.GetJavaDate(startDateAsNumber);
DateTime resultDate = startDate.AddMonths(offsetInMonthAsNumber);
result = DateUtil.GetExcelDate(resultDate);
NumericFunction.CheckValue(result);
return(new NumberEval(result));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
/**
* Returns a CellValue wrapper around the supplied ValueEval instance.
* @param eval
*/
private CellValue EvaluateFormulaCellValue(ICell cell)
{
ValueEval eval = _bookEvaluator.Evaluate(new HSSFEvaluationCell((HSSFCell)cell));
if (eval is NumberEval)
{
NumberEval ne = (NumberEval)eval;
return(new CellValue(ne.NumberValue));
}
if (eval is BoolEval)
{
BoolEval be = (BoolEval)eval;
return(CellValue.ValueOf(be.BooleanValue));
}
if (eval is StringEval)
{
StringEval ne = (StringEval)eval;
return(new CellValue(ne.StringValue));
}
if (eval is ErrorEval)
{
return(CellValue.GetError(((ErrorEval)eval).ErrorCode));
}
throw new InvalidOperationException("Unexpected eval class (" + eval.GetType().Name + ")");
}
private static void Confirm(int expResult, DateTime firstArg, DateTime secondArg, bool method)
{
ValueEval ve;
if (method)
{
// TODO enable 3rd arg -
ve = invokeDays360(Convert(firstArg), Convert(secondArg), BoolEval.ValueOf(method));
}
else
{
ve = invokeDays360(Convert(firstArg), Convert(secondArg));
}
if (ve is NumberEval)
{
NumberEval numberEval = (NumberEval)ve;
if (numberEval.NumberValue != expResult)
{
throw new AssertionException(fmt(firstArg) + " " + fmt(secondArg) + " " + method +
" wrong result got (" + numberEval.NumberValue
+ ") but expected (" + expResult + ")");
}
// System.err.println(fmt(firstArg) + " " + fmt(secondArg) + " " + method + " success got (" + expResult + ")");
return;
}
throw new AssertionException("wrong return type (" + ve.GetType().Name + ")");
}
public void TestGetValue_bug44950()
{
AreaPtg ptg = new AreaPtg("B2:D3");
NumberEval one = new NumberEval(1);
ValueEval[] values =
{
one,
new NumberEval(2),
new NumberEval(3),
new NumberEval(4),
new NumberEval(5),
new NumberEval(6),
};
AreaEval ae = EvalFactory.CreateAreaEval(ptg, values);
if (one == ae.GetValueAt(1, 2))
{
throw new AssertFailedException("Identified bug 44950 a");
}
Confirm(1, ae, 1, 1);
Confirm(2, ae, 1, 2);
Confirm(3, ae, 1, 3);
Confirm(4, ae, 2, 1);
Confirm(5, ae, 2, 2);
Confirm(6, ae, 2, 3);
}
public void TestRefEvalStartDate()
{
EDate eDate = new EDate();
NumberEval result = (NumberEval)eDate.Evaluate(new ValueEval[] { new RefEvalImplementation(new NumberEval(1000)), new NumberEval(0) }, null);
Assert.AreEqual(1000d, result.NumberValue);
}
private void checkValue(int startDate, int monthInc, double expectedResult)
{
EDate eDate = new EDate();
NumberEval result = (NumberEval)eDate.Evaluate(new ValueEval[] { new NumberEval(startDate), new NumberEval(monthInc) }, null);
Assert.AreEqual(expectedResult, result.NumberValue);
}
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);
}
public void TestTextWithDeciamlFormatSecondArg()
{
ValueEval numArg = new NumberEval(321321.321);
ValueEval formatArg = new StringEval("#,###.00000");
ValueEval[] args = { numArg, formatArg };
ValueEval result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
//char groupSeparator = new DecimalFormatSymbols(Locale.GetDefault()).GetGroupingSeparator();
//char decimalSeparator = new DecimalFormatSymbols(Locale.GetDefault()).GetDecimalSeparator();
System.Globalization.CultureInfo ci = System.Globalization.CultureInfo.InstalledUICulture;
string groupSeparator = ci.NumberFormat.NumberGroupSeparator;
string decimalSeparator = ci.NumberFormat.NumberDecimalSeparator;;
ValueEval testResult = new StringEval("321" + groupSeparator + "321" + decimalSeparator + "32100");
Assert.AreEqual(testResult.ToString(), result.ToString());
numArg = new NumberEval(321.321);
formatArg = new StringEval("00000.00000");
args[0] = numArg;
args[1] = formatArg;
result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
testResult = new StringEval("00321" + decimalSeparator + "32100");
Assert.AreEqual(testResult.ToString(), result.ToString());
formatArg = new StringEval("$#.#");
args[1] = formatArg;
result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
testResult = new StringEval("$321" + decimalSeparator + "3");
Assert.AreEqual(testResult.ToString(), result.ToString());
}
public void TestEDateProperValues()
{
EDate eDate = (EDate)EDate.Instance;
NumberEval result = (NumberEval)eDate.Evaluate(new ValueEval[] { new NumberEval(1000), new NumberEval(0) }, null);
Assert.AreEqual(1000d, result.NumberValue);
}
private String FormatValue(Object value)
{
if (value is Ptg[])
{
Ptg[] ptgs = (Ptg[])value;
return(HSSFFormulaParser.ToFormulaString(_book, ptgs));
}
if (value is NumberEval)
{
NumberEval ne = (NumberEval)value;
return(ne.StringValue);
}
if (value is StringEval)
{
StringEval se = (StringEval)value;
return("'" + se.StringValue + "'");
}
if (value is BoolEval)
{
BoolEval be = (BoolEval)value;
return(be.StringValue);
}
if (value == BlankEval.instance)
{
return("#BLANK#");
}
if (value is ErrorEval)
{
ErrorEval ee = (ErrorEval)value;
return(ErrorEval.GetText(ee.ErrorCode));
}
throw new ArgumentException("Unexpected value class ("
+ value.GetType().Name + ")");
}
public void TestErrors2()
{
ValueEval[] values = { new NumberEval(1), new NumberEval(2), new NumberEval(3), ErrorEval.DIV_ZERO, };
ValueEval percentile = new NumberEval(0.95);
ConfirmPercentile(percentile, values, ErrorEval.DIV_ZERO);
}
public void TestUnusualArgs()
{
ValueEval[] values = { new NumberEval(1), new NumberEval(2), BoolEval.TRUE, BoolEval.FALSE };
ValueEval percentile = new NumberEval(0.95);
ConfirmPercentile(percentile, values, 1.95);
}
public void TestUnusualArgs3()
{
ValueEval[] values = { new NumberEval(1), new NumberEval(2) };
ValueEval percentile = new NumberEval(1.1);
ConfirmPercentile(percentile, values, ErrorEval.NUM_ERROR);
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
Double result = double.NaN;
if (args.Length == 0 || args.Length % 2 > 0)
{
return(ErrorEval.VALUE_INVALID);
}
for (int i = 0; i < args.Length;)
{
ValueEval firstArg = args[i];
ValueEval secondArg = args[i + 1];
i += 2;
NumberEval Evaluate = (NumberEval) new Countif().Evaluate(new ValueEval[] { firstArg, secondArg }, ec.RowIndex, ec.ColumnIndex);
if (double.IsNaN(result))
{
result = Evaluate.NumberValue;
}
else if (Evaluate.NumberValue < result)
{
result = Evaluate.NumberValue;
}
}
return(new NumberEval(double.IsNaN(result) ? 0 : result));
}
public void TestBlanks()
{
ValueEval[] values = { new NumberEval(210.128), new NumberEval(65.2182), new NumberEval(32.231),
BlankEval.instance, new NumberEval(45.32) };
ValueEval percentile = new NumberEval(0.95);
ConfirmPercentile(percentile, values, 188.39153);
}
public void TestBasic()
{
ValueEval[] values = { new NumberEval(210.128), new NumberEval(65.2182), new NumberEval(32.231),
new NumberEval(12.123), new NumberEval(45.32) };
ValueEval percentile = new NumberEval(0.95);
ConfirmPercentile(percentile, values, 181.14604);
}
private static ValueEval invokePoisson(double x, double mean, bool cumulative)
{
ValueEval[] valueEvals = new ValueEval[3];
valueEvals[0] = new NumberEval(x);
valueEvals[1] = new NumberEval(mean);
valueEvals[2] = BoolEval.ValueOf(cumulative);
return(NumericFunction.POISSON.Evaluate(valueEvals, -1, -1));
}
private double EvaluateInternal(ValueVector x, ValueVector y, int size)
{
Accumulator acc = CreateAccumulator();
// error handling is as if the x is fully evaluated before y
ErrorEval firstXerr = null;
ErrorEval firstYerr = null;
bool accumlatedSome = false;
double result = 0.0;
for (int i = 0; i < size; i++)
{
ValueEval vx = x.GetItem(i);
ValueEval vy = y.GetItem(i);
if (vx is ErrorEval)
{
if (firstXerr == null)
{
firstXerr = (ErrorEval)vx;
continue;
}
}
if (vy is ErrorEval)
{
if (firstYerr == null)
{
firstYerr = (ErrorEval)vy;
continue;
}
}
// only count pairs if both elements are numbers
if (vx is NumberEval && vy is NumberEval)
{
accumlatedSome = true;
NumberEval nx = (NumberEval)vx;
NumberEval ny = (NumberEval)vy;
result += acc.Accumulate(nx.NumberValue, ny.NumberValue);
}
else
{
// all other combinations of value types are silently ignored
}
}
if (firstXerr != null)
{
throw new EvaluationException(firstXerr);
}
if (firstYerr != null)
{
throw new EvaluationException(firstYerr);
}
if (!accumlatedSome)
{
throw new EvaluationException(ErrorEval.DIV_ZERO);
}
return(result);
}
private ValueEval[] CreateMockNumberArray(int size, double value)
{
ValueEval[] result = new ValueEval[size];
for (int i = 0; i < result.Length; i++)
{
result[i] = new NumberEval((i + 1) % value);
}
return(result);
}
public void TestLargeArrays()
{
ValueEval[] yValues = CreateMockNumberArray(100, 3); // [1,2,0,1,2,0,...,0,1]
yValues[0] = new NumberEval(2.0); // Changes first element to 2
ValueEval[] xValues = CreateMockNumberArray(100, 101); // [1,2,3,4,...,99,100]
Confirm(SLOPE, CreateAreaEval(xValues), CreateAreaEval(yValues), -1.231527093596059);
// Excel 2010 gives -1.23152709359606
}
public void TestLargeArrays()
{
ValueEval[] yValues = CreateMockNumberArray(100, 3); // [1,2,0,1,2,0,...,0,1]
yValues[0] = new NumberEval(2.0); // Changes first element to 2
ValueEval[] xValues = CreateMockNumberArray(100, 101); // [1,2,3,4,...,99,100]
Confirm(INTERCEPT, CreateAreaEval(xValues), CreateAreaEval(yValues), 51.74384236453202);
// Excel 2010 gives 51.74384236453200
}
