private static void ConfirmText(String text)
{
ValueEval arg = new StringEval(text);
ValueEval eval = invokeT(arg);
StringEval se = (StringEval)eval;
Assert.AreEqual(text, se.StringValue);
}
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 TestTRuncWithStringArg()
{
ValueEval strArg = new StringEval("abc");
ValueEval[] args = { strArg, new NumberEval(2) };
ValueEval result = NumericFunction.TRUNC.Evaluate(args, -1, (short)-1);
Assert.AreEqual(ErrorEval.VALUE_INVALID, result);
}
public void TestTextWithStringFirstArg()
{
ValueEval strArg = new StringEval("abc");
ValueEval formatArg = new StringEval("abc");
ValueEval[] args = { strArg, formatArg };
ValueEval result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
Assert.AreEqual(ErrorEval.VALUE_INVALID, result);
}
/**
* uses the relevant flags to decode the StringEval
* @param eval
*/
private ValueEval XlateRefStringEval(StringEval sve)
{
if ((flags & REF_STRING_IS_PARSED) > 0)
{
String s = sve.StringValue;
double d = OperandResolver.ParseDouble(s);
if (double.IsNaN(d))
{
return(ErrorEval.VALUE_INVALID);
}
return(new NumberEval(d));
}
// strings are blanks
return(BlankEval.instance);
}
/**
* uses the relevant flags to decode the StringEval
* @param eval
*/
private ValueEval XlateStringEval(StringEval eval)
{
if ((flags & STRING_IS_PARSED) > 0)
{
String s = eval.StringValue;
double d = OperandResolver.ParseDouble(s);
if (double.IsNaN(d))
{
return(ErrorEval.VALUE_INVALID);
}
return(new NumberEval(d));
}
// strings are errors?
if ((flags & STRING_IS_INVALID_VALUE) > 0)
{
return(ErrorEval.VALUE_INVALID);
}
// ignore strings
return(XlateBlankEval(BLANK_IS_PARSED));
}
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());
}
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 + ")");
}
/**
* When the second argument is a string, many things are possible
*/
private static I_MatchPredicate CreateGeneralMatchPredicate(StringEval stringEval)
{
String value = stringEval.StringValue;
CmpOp operator1 = CmpOp.GetOperator(value);
value = value.Substring(operator1.Length);
bool? booleanVal = ParseBoolean(value);
if (booleanVal != null)
{
return new BooleanMatcher(booleanVal.Value, operator1);
}
Double doubleVal = OperandResolver.ParseDouble(value);
if (!double.IsNaN(doubleVal))
{
return new NumberMatcher(doubleVal, operator1);
}
ErrorEval ee = ParseError(value);
if (ee != null)
{
return new ErrorMatcher(ee.ErrorCode, operator1);
}
//else - just a plain string with no interpretation.
return new StringMatcher(value, operator1);
}
/**
* uses the relevant flags to decode the StringEval
* @param eval
*/
private ValueEval XlateRefStringEval(StringEval sve)
{
if ((flags & REF_STRING_IS_PARSED) > 0)
{
String s = sve.StringValue;
double d = OperandResolver.ParseDouble(s);
if (double.IsNaN(d))
{
return ErrorEval.VALUE_INVALID;
}
return new NumberEval(d);
}
// strings are blanks
return BlankEval.instance;
}
/**
* uses the relevant flags to decode the StringEval
* @param eval
*/
private ValueEval XlateStringEval(StringEval eval)
{
if ((flags & STRING_IS_PARSED) > 0)
{
String s = eval.StringValue;
double d = OperandResolver.ParseDouble(s);
if (double.IsNaN(d))
{
return ErrorEval.VALUE_INVALID;
}
return new NumberEval(d);
}
// strings are errors?
if ((flags & STRING_IS_INVALID_VALUE) > 0)
{
return ErrorEval.VALUE_INVALID;
}
// ignore strings
return XlateBlankEval(BLANK_IS_PARSED);
}
/**
* @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 StringLookupComparer(StringEval se)
: base(se)
{
_value = se.StringValue;
}
public StringLookupComparer(StringEval se, bool matchExact, bool isMatchFunction)
: base(se)
{
_value = se.StringValue;
_wildCardPattern = Countif.StringMatcher.GetWildCardPattern(_value);
_matchExact = matchExact;
_isMatchFunction = isMatchFunction;
}
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 TestReturnWorkdaysWhenStartIsWeekendSubtractingDays()
{
String startDate = "2013/10/06";
int days = -1;
String expectedWorkDay = "2013/10/04";
StringEval stringEval = new StringEval(startDate);
double numberValue = ((NumberEval)WorkdayFunction.instance.Evaluate(new ValueEval[]{
stringEval, new NumberEval(days) }, EC)).NumberValue;
Assert.AreEqual(expectedWorkDay, formatter.Format(DateUtil.GetJavaDate(numberValue)));
}
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 TestCriteriaPredicateNe_Bug46647()
{
I_MatchPredicate mp = Countif.CreateCriteriaPredicate(new StringEval("<>aa"), 0, 0);
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 TestReturnWorkdaysSpanningAWeekendAddingDays()
{
String startDate = "2013/09/27";
int days = 1;
String expectedWorkDay = "2013/09/30";
StringEval stringEval = new StringEval(startDate);
double numberValue = ((NumberEval)WorkdayFunction.instance.Evaluate(new ValueEval[]{
stringEval, new NumberEval(days) }, EC)).NumberValue;
Assert.AreEqual(expectedWorkDay, formatter.Format(DateUtil.GetJavaDate(numberValue)));
}
