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);
}
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 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 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);
}
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);
}
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, "");
}
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 TestTextWithFractionFormatSecondArg()
{
ValueEval numArg = new NumberEval(321.321);
ValueEval formatArg = new StringEval("# #/#");
ValueEval[] args = { numArg, formatArg };
ValueEval result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
ValueEval testResult = new StringEval("321 1/3");
Assert.AreEqual(testResult.ToString(), result.ToString()); //this bug is caused by DecimalFormat
formatArg = new StringEval("# #/##");
args[1] = formatArg;
result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
testResult = new StringEval("321 26/81");
Assert.AreEqual(testResult.ToString(), result.ToString());
formatArg = new StringEval("#/##");
args[1] = formatArg;
result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
testResult = new StringEval("26027/81");
Assert.AreEqual(testResult.ToString(), result.ToString());
}
/**
* @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 = invokeAndDereference(args);
Assert.AreEqual(expectedResult, actual, 0D);
}
public void TestTextWithDateFormatSecondArg()
{
// Test with Java style M=Month
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("en-US");
ValueEval numArg = new NumberEval(321.321);
ValueEval formatArg = new StringEval("dd:MM:yyyy hh:mm:ss");
ValueEval[] args = { numArg, formatArg };
ValueEval result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
ValueEval testResult = new StringEval("16:11:1900 07:42:14");
Assert.AreEqual(testResult.ToString(), result.ToString());
// Excel also supports "m before h is month"
formatArg = new StringEval("dd:mm:yyyy hh:mm:ss");
args[1] = formatArg;
result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
testResult = new StringEval("16:11:1900 07:42:14");
//Assert.AreEqual(testResult.ToString(), result.ToString());
// this line is intended to compute how "November" would look like in the current locale
String november = new SimpleDateFormat("MMMM").Format(new DateTime(2010, 11, 15), CultureInfo.CurrentCulture);
// Again with Java style
formatArg = new StringEval("MMMM dd, yyyy");
args[1] = formatArg;
//fix error in non-en Culture
NPOI.SS.Formula.Functions.Text.Formatter = new NPOI.SS.UserModel.DataFormatter(CultureInfo.CurrentCulture);
result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
testResult = new StringEval(november + " 16, 1900");
Assert.AreEqual(testResult.ToString(), result.ToString());
// And Excel style
formatArg = new StringEval("mmmm dd, yyyy");
args[1] = formatArg;
result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
testResult = new StringEval(november + " 16, 1900");
Assert.AreEqual(testResult.ToString(), result.ToString());
}
private static int CompareBlank(ValueEval v)
{
if (v == BlankEval.instance)
{
return(0);
}
if (v is BoolEval)
{
BoolEval boolEval = (BoolEval)v;
return(boolEval.BooleanValue ? -1 : 0);
}
if (v is NumberEval)
{
NumberEval ne = (NumberEval)v;
//return ne.NumberValue.CompareTo(0.0);
return(NumberComparer.Compare(0.0, ne.NumberValue));
}
if (v is StringEval)
{
StringEval se = (StringEval)v;
return(se.StringValue.Length < 1 ? 0 : -1);
}
throw new ArgumentException("bad value class (" + v.GetType().Name + ")");
}
public void TestTextWithDateFormatSecondArg()
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("en-US");
ValueEval numArg = new NumberEval(321.321);
ValueEval formatArg = new StringEval("dd:MM:yyyy hh:mm:ss");
ValueEval[] args = { numArg, formatArg };
ValueEval result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
ValueEval testResult = new StringEval("16:11:1900 07:42:14");
Assert.AreEqual(testResult.ToString(), result.ToString());
// this line is intended to compute how "November" would look like in the current locale
String november = new SimpleDateFormat("MMMM").Format(new DateTime(2010, 11, 15));
formatArg = new StringEval("MMMM dd, yyyy");
args[1] = formatArg;
result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
testResult = new StringEval(november + " 16, 1900");
Assert.AreEqual(testResult.ToString(), result.ToString());
}
public void TestBug56688_4()
{
XSSFWorkbook excel = XSSFTestDataSamples.OpenSampleWorkbook("56688_4.xlsx");
Calendar calendar = new GregorianCalendar(GregorianCalendarTypes.USEnglish);
DateTime time = calendar.AddMonths(DateTime.Now, 2);
double excelDate = DateUtil.GetExcelDate(time);
NumberEval eval = new NumberEval(Math.Floor(excelDate));
//CheckValue(excel, eval.StringValue + ".0");
CheckValue(excel, eval.StringValue);
}
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);
}
private static void Confirm(double expected, NumberEval ne)
{
// only asserting accuracy to 4 fractional digits
Assert.AreEqual(expected, ne.NumberValue, 0.00005);
}
private ValueEval[] CreateMockNumberArray(int size, double value)
{
ValueEval[] result = new ValueEval[size];
for (int i = 0; i < result.Length; i++)
{
result[i] = new NumberEval(value);
}
return result;
}
public void TestZeroEquality_bug47198()
{
NumberEval zero = new NumberEval(0.0);
NumberEval mZero = (NumberEval)Evaluate(UnaryMinusEval.instance, new ValueEval[] { zero, }, 0, 0);
if ((ulong)BitConverter.DoubleToInt64Bits(mZero.NumberValue) == 0x8000000000000000L)
{
throw new AssertionException("Identified bug 47198: unary minus should convert -0.0 to 0.0");
}
ValueEval[] args = { zero, mZero, };
BoolEval result = (BoolEval)Evaluate(EvalInstances.Equal, args, 0, 0);
if (!result.BooleanValue)
{
throw new AssertionException("Identified bug 47198: -0.0 != 0.0");
}
}
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
}
/**
* @return never <c>null</c>, never {@link BlankEval}
*/
private ValueEval EvaluateAny(IEvaluationCell srcCell, int sheetIndex,
int rowIndex, int columnIndex, EvaluationTracker tracker)
{
bool shouldCellDependencyBeRecorded = _stabilityClassifier == null ? true
: !_stabilityClassifier.IsCellFinal(sheetIndex, rowIndex, columnIndex);
ValueEval result;
if (srcCell == null || srcCell.CellType != CellType.Formula)
{
result = GetValueFromNonFormulaCell(srcCell);
if (shouldCellDependencyBeRecorded)
{
tracker.AcceptPlainValueDependency(_workbookIx, sheetIndex, rowIndex, columnIndex, result);
}
return result;
}
FormulaCellCacheEntry cce = _cache.GetOrCreateFormulaCellEntry(srcCell);
if (shouldCellDependencyBeRecorded || cce.IsInputSensitive)
{
tracker.AcceptFormulaDependency(cce);
}
IEvaluationListener evalListener = _evaluationListener;
if (cce.GetValue() == null)
{
if (!tracker.StartEvaluate(cce))
{
return ErrorEval.CIRCULAR_REF_ERROR;
}
OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex, columnIndex, tracker);
try
{
Ptg[] ptgs = _workbook.GetFormulaTokens(srcCell);
if (evalListener == null)
{
result = EvaluateFormula(ec, ptgs);
}
else
{
evalListener.OnStartEvaluate(srcCell, cce);
result = EvaluateFormula(ec, ptgs);
evalListener.OnEndEvaluate(cce, result);
}
tracker.UpdateCacheResult(result);
}
catch (NotImplementedException e)
{
throw AddExceptionInfo(e, sheetIndex, rowIndex, columnIndex);
}
catch (RuntimeException re)
{
if (re.InnerException is WorkbookNotFoundException && _ignoreMissingWorkbooks)
{
LogInfo(re.InnerException.Message + " - Continuing with cached value!");
switch (srcCell.CachedFormulaResultType)
{
case CellType.Numeric:
result = new NumberEval(srcCell.NumericCellValue);
break;
case CellType.String:
result = new StringEval(srcCell.StringCellValue);
break;
case CellType.Blank:
result = BlankEval.instance;
break;
case CellType.Boolean:
result = BoolEval.ValueOf(srcCell.BooleanCellValue);
break;
case CellType.Error:
result = ErrorEval.ValueOf(srcCell.ErrorCellValue);
break;
case CellType.Formula:
default:
throw new RuntimeException("Unexpected cell type '" + srcCell.CellType + "' found!");
}
}
else
{
throw re;
}
}
finally
{
tracker.EndEvaluate(cce);
}
}
else
{
if (evalListener != null)
{
evalListener.OnCacheHit(sheetIndex, rowIndex, columnIndex, cce.GetValue());
}
return cce.GetValue();
}
if (IsDebugLogEnabled())
{
String sheetName = GetSheetName(sheetIndex);
CellReference cr = new CellReference(rowIndex, columnIndex);
LogDebug("Evaluated " + sheetName + "!" + cr.FormatAsString() + " To " + cce.GetValue());
}
// Usually (result === cce.getValue())
// But sometimes: (result==ErrorEval.CIRCULAR_REF_ERROR, cce.getValue()==null)
// When circular references are detected, the cache entry is only updated for
// the top evaluation frame
//return cce.GetValue();
return result;
}
private static int DoCompare(ValueEval va, ValueEval vb)
{
// special cases when one operand is blank
if (va == BlankEval.instance)
{
return(CompareBlank(vb));
}
if (vb == BlankEval.instance)
{
return(-CompareBlank(va));
}
if (va is BoolEval)
{
if (vb is BoolEval)
{
BoolEval bA = (BoolEval)va;
BoolEval bB = (BoolEval)vb;
if (bA.BooleanValue == bB.BooleanValue)
{
return(0);
}
return(bA.BooleanValue ? 1 : -1);
}
return(1);
}
if (vb is BoolEval)
{
return(-1);
}
if (va is StringEval)
{
if (vb is StringEval)
{
StringEval sA = (StringEval)va;
StringEval sB = (StringEval)vb;
return(string.Compare(sA.StringValue, sB.StringValue, StringComparison.OrdinalIgnoreCase));
}
return(1);
}
if (vb is StringEval)
{
return(-1);
}
if (va is NumberEval)
{
if (vb is NumberEval)
{
NumberEval nA = (NumberEval)va;
NumberEval nB = (NumberEval)vb;
if (nA.NumberValue == nB.NumberValue)
{
// Excel considers -0.0 == 0.0 which is different to Double.compare()
return(0);
}
return(NumberComparer.Compare(nA.NumberValue, nB.NumberValue));
}
}
throw new ArgumentException("Bad operand types (" + va.GetType().Name + "), ("
+ vb.GetType().Name + ")");
}
public NumberLookupComparer(NumberEval ne)
: base(ne)
{
_value = ne.NumberValue;
}
public void TestRounding_bug47598()
{
double x = 1 + 1.0028 - 0.9973; // should be 1.0055, but has IEEE rounding
Assert.IsFalse(x == 1.0055);
NumberEval a = new NumberEval(x);
NumberEval b = new NumberEval(1.0055);
Assert.AreEqual("1.0055", b.StringValue);
ValueEval[] args = { a, b, };
BoolEval result = (BoolEval)Evaluate(EvalInstances.Equal, args, 0, 0);
if (!result.BooleanValue)
{
throw new AssertionException("Identified bug 47598: 1+1.0028-0.9973 != 1.0055");
}
}
public void TestCountifAreaCriteria()
{
int srcColIx = 2; // anything but column A
ValueEval v0 = new NumberEval(2.0);
ValueEval v1 = new StringEval("abc");
ValueEval v2 = ErrorEval.DIV_ZERO;
AreaEval ev = EvalFactory.CreateAreaEval("A10:A12", new ValueEval[] { v0, v1, v2, });
I_MatchPredicate mp;
mp = Countif.CreateCriteriaPredicate(ev, 9, srcColIx);
ConfirmPredicate(true, mp, srcColIx);
ConfirmPredicate(false, mp, "abc");
ConfirmPredicate(false, mp, ErrorEval.DIV_ZERO);
mp = Countif.CreateCriteriaPredicate(ev, 10, srcColIx);
ConfirmPredicate(false, mp, srcColIx);
ConfirmPredicate(true, mp, "abc");
ConfirmPredicate(false, mp, ErrorEval.DIV_ZERO);
mp = Countif.CreateCriteriaPredicate(ev, 11, srcColIx);
ConfirmPredicate(false, mp, srcColIx);
ConfirmPredicate(false, mp, "abc");
ConfirmPredicate(true, mp, ErrorEval.DIV_ZERO);
ConfirmPredicate(false, mp, ErrorEval.VALUE_INVALID);
// tricky: indexing outside of A10:A12
// even this #VALUE! error Gets used by COUNTIF as valid criteria
mp = Countif.CreateCriteriaPredicate(ev, 12, srcColIx);
ConfirmPredicate(false, mp, srcColIx);
ConfirmPredicate(false, mp, "abc");
ConfirmPredicate(false, mp, ErrorEval.DIV_ZERO);
ConfirmPredicate(true, mp, ErrorEval.VALUE_INVALID);
}
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 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
}
public void TestUnusualArgs2()
{
ValueEval[] values = { new NumberEval(1), new NumberEval(2), };
ValueEval percentile = new NumberEval(-0.1);
ConfirmPercentile(percentile, values, ErrorEval.NUM_ERROR);
}
private static ValueEval Evaluate(Function instance, params double[] dArgs)
{
ValueEval[] evalArgs;
evalArgs = new ValueEval[dArgs.Length];
for (int i = 0; i < evalArgs.Length; i++)
{
evalArgs[i] = new NumberEval(dArgs[i]);
}
ValueEval r = instance.Evaluate(evalArgs, -1, (short)-1);
return r;
}