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 bool Matches(ValueEval x)
{
int testValue = 0;
if (x is StringEval)
{
if (true)
{ // Change to false to observe more intuitive behaviour
// Note - Unlike with numbers, it seems that COUNTA never Matches
// bool values when the tarGet(x) Is a string
return(false);
}
StringEval se = (StringEval)x;
bool? val = ParseBoolean(se.StringValue);
if (val == null)
{
// x is text that is not a boolean
return(false);
}
testValue = BoolToInt(val);
}
else if (x is BoolEval)
{
BoolEval be = (BoolEval)x;
testValue = BoolToInt(be.BooleanValue);
}
else
{
return(false);
}
return(_operator.Evaluate(testValue - _value));
}
/**
* If cell Contains a formula, the formula is Evaluated and returned,
* else the CellValue simply copies the appropriate cell value from
* the cell and also its cell type. This method should be preferred over
* EvaluateInCell() when the call should not modify the contents of the
* original cell.
*
* @param sheetName the name of the sheet Containing the cell
* @param rowIndex zero based
* @param columnIndex zero based
* @return <code>null</code> if the supplied cell is <code>null</code> or blank
*/
public ValueEval Evaluate(String sheetName, int rowIndex, int columnIndex)
{
IEvaluationCell cell = _sewb.GetEvaluationCell(sheetName, rowIndex, columnIndex);
switch (cell.CellType)
{
case CellType.Boolean:
return(BoolEval.ValueOf(cell.BooleanCellValue));
case CellType.Error:
return(ErrorEval.ValueOf(cell.ErrorCellValue));
case CellType.Formula:
return(_evaluator.Evaluate(cell));
case CellType.Numeric:
return(new NumberEval(cell.NumericCellValue));
case CellType.String:
return(new StringEval(cell.StringCellValue));
case CellType.Blank:
return(null);
}
throw new InvalidOperationException("Bad cell type (" + cell.CellType + ")");
}
/**
* 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 + ")");
}
/**
* Gets the value from a non-formula cell.
* @param cell may be <c>null</c>
* @return {@link BlankEval} if cell is <c>null</c> or blank, never <c>null</c>
*/
/* package */
internal static ValueEval GetValueFromNonFormulaCell(IEvaluationCell cell)
{
if (cell == null)
{
return(BlankEval.instance);
}
CellType cellType = cell.CellType;
switch (cellType)
{
case CellType.NUMERIC:
return(new NumberEval(cell.NumericCellValue));
case CellType.STRING:
return(new StringEval(cell.StringCellValue));
case CellType.BOOLEAN:
return(BoolEval.ValueOf(cell.BooleanCellValue));
case CellType.BLANK:
return(BlankEval.instance);
case CellType.ERROR:
return(ErrorEval.ValueOf(cell.ErrorCellValue));
}
throw new Exception("Unexpected cell type (" + cellType + ")");
}
/**
* If cell Contains a formula, the formula is Evaluated and returned,
* else the CellValue simply copies the appropriate cell value from
* the cell and also its cell type. This method should be preferred over
* EvaluateInCell() when the call should not modify the contents of the
* original cell.
*
* @param sheetName the name of the sheet Containing the cell
* @param rowIndex zero based
* @param columnIndex zero based
* @return <code>null</code> if the supplied cell is <code>null</code> or blank
*/
public ValueEval Evaluate(String sheetName, int rowIndex, int columnIndex)
{
IEvaluationCell cell = _sewb.GetEvaluationCell(sheetName, rowIndex, columnIndex);
switch (cell.CellType)
{
case CellType.BOOLEAN:
return(BoolEval.ValueOf(cell.BooleanCellValue));
case CellType.ERROR:
return(ErrorEval.ValueOf(cell.ErrorCellValue));
case CellType.FORMULA:
return(_evaluator.Evaluate(cell));
case CellType.NUMERIC:
return(new NumberEval(cell.NumericCellValue));
case CellType.STRING:
return(new StringEval(cell.StringCellValue));
case CellType.BLANK:
return(null);
}
throw new InvalidOperationException("Bad cell type (" + cell.CellType + ")");
}
public override bool Matches(ValueEval x)
{
int testValue;
if (x is StringEval)
{
#if !HIDE_UNREACHABLE_CODE
if (true)
{ // change to false to observe more intuitive behaviour
// Note - Unlike with numbers, it seems that COUNTIF never matches
// boolean values when the target(x) is a string
return(false);
}
StringEval se = (StringEval)x;
Boolean? val = ParseBoolean(se.StringValue);
if (val == null)
{
// x is text that is not a boolean
return(false);
}
testValue = BoolToInt(val.Value);
#else
return(false);
#endif
}
else if ((x is BoolEval))
{
BoolEval be = (BoolEval)x;
testValue = BoolToInt(be.BooleanValue);
}
else if ((x is BlankEval))
{
switch (Code)
{
case CmpOp.NE:
// Excel counts blank values in range as not equal to any value. See Bugzilla 51498
return(true);
default:
return(false);
}
}
else if ((x is NumberEval))
{
switch (Code)
{
case CmpOp.NE:
// not-equals comparison of a number to boolean always returnes false
return(true);
default:
return(false);
}
}
else
{
return(false);
}
return(Evaluate(testValue - _value));
}
/**
* 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 + ")");
}
/**
* 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 + ")");
}
private void CollectValue(ValueEval ve, bool isViaReference, DoubleList temp)
{
if (ve == null)
{
throw new ArgumentException("ve must not be null");
}
if (ve is BoolEval)
{
if (!isViaReference || _isReferenceBoolCounted)
{
BoolEval boolEval = (BoolEval)ve;
temp.Add(boolEval.NumberValue);
}
return;
}
if (ve is NumberEval)
{
NumberEval ne = (NumberEval)ve;
temp.Add(ne.NumberValue);
return;
}
if (ve is StringEval)
{
if (isViaReference)
{
// ignore all ref strings
return;
}
String s = ((StringEval)ve).StringValue;
Double d = OperandResolver.ParseDouble(s);
if (double.IsNaN(d))
{
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
temp.Add(d);
return;
}
if (ve is ErrorEval)
{
throw new EvaluationException((ErrorEval)ve);
}
if (ve == BlankEval.instance)
{
if (_isBlankCounted)
{
temp.Add(0.0);
}
return;
}
if (ve is NumberValueArrayEval nvae)
{
temp.Add(nvae.NumberValues);
return;
}
throw new InvalidOperationException("Invalid ValueEval type passed for conversion: ("
+ ve.GetType() + ")");
}
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));
}
/**
* Resolves the last (optional) parameter (<b>range_lookup</b>) to the VLOOKUP and HLOOKUP functions.
* @param rangeLookupArg
* @param srcCellRow
* @param srcCellCol
* @return
* @throws EvaluationException
*/
public static bool ResolveRangeLookupArg(ValueEval rangeLookupArg, int srcCellRow, int srcCellCol)
{
ValueEval valEval = OperandResolver.GetSingleValue(rangeLookupArg, srcCellRow, srcCellCol);
if (valEval == MissingArgEval.instance)
{
// Tricky:
// forth arg exists but is not supplied: "=VLOOKUP(A1,A2:A4,2,)"
return(false);
}
if (valEval is BlankEval)
{
// Tricky:
// fourth arg supplied but Evaluates to blank
// this does not Get the default value
return(false);
}
if (valEval is BoolEval)
{
// Happy day flow
BoolEval boolEval = (BoolEval)valEval;
return(boolEval.BooleanValue);
}
if (valEval is StringEval)
{
String stringValue = ((StringEval)valEval).StringValue;
if (stringValue.Length < 1)
{
// More trickiness:
// Empty string Is not the same as BlankEval. It causes #VALUE! error
throw EvaluationException.InvalidValue();
}
// TODO move parseBoolean to OperandResolver
bool?b = Countif.ParseBoolean(stringValue);
if (b != null)
{
// string Converted to bool OK
return(b == true ? true : false);
}
//// Even more trickiness:
//// Note - even if the StringEval represents a number value (for example "1"),
//// Excel does not resolve it to a bool.
throw EvaluationException.InvalidValue();
//// This Is in contrast to the code below,, where NumberEvals values (for
//// example 0.01) *do* resolve to equivalent bool values.
}
if (valEval is NumericValueEval)
{
NumericValueEval nve = (NumericValueEval)valEval;
// zero Is FALSE, everything else Is TRUE
return(0.0 != nve.NumberValue);
}
throw new Exception("Unexpected eval type (" + valEval.GetType().Name + ")");
}
public override ValueEval EvaluateFunc(ValueEval[] args, int srcCellRow, int srcCellCol)
{
if (args.Length != 2)
{
return(ErrorEval.VALUE_INVALID);
}
String s0 = EvaluateStringArg(args[0], srcCellRow, srcCellCol);
String s1 = EvaluateStringArg(args[1], srcCellRow, srcCellCol);
return(BoolEval.ValueOf(s0.Equals(s1)));
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length < 3)
{
return(ErrorEval.NA);
}
ValueEval expression;
try
{
expression = OperandResolver.GetSingleValue(args[0], ec.RowIndex, ec.ColumnIndex);
}
catch (Exception)
{
return(ErrorEval.NA);
}
for (int i = 1; i < args.Length; i = i + 2)
{
try
{
ValueEval value = OperandResolver.GetSingleValue(args[i], ec.RowIndex, ec.ColumnIndex);
ValueEval result = args[i + 1];
//ValueEval result = OperandResolver.getSingleValue(args[i+1],ec.RowIndex,ec.ColumnIndex) ;
ValueEval evaluate = (new EqualEval()).Evaluate(new ValueEval[] { expression, value }, ec.RowIndex, ec.ColumnIndex);
if (evaluate is BoolEval)
{
BoolEval boolEval = (BoolEval)evaluate;
bool booleanValue = boolEval.BooleanValue;
if (booleanValue)
{
return(result);
}
}
}
catch (EvaluationException)
{
return(ErrorEval.NA);
}
if (i + 2 == args.Length - 1)
{
//last value in args is the default one
return(args[args.Length - 1]);
}
}
return(ErrorEval.NA);
}
private static bool EvalStringCmp(String a, String b, HSSFFunctions.Function cmpOp)
{
ValueEval[] args =
{
new StringEval(a),
new StringEval(b),
};
ValueEval result = Evaluate(cmpOp, args, 10, 20);
Assert.AreEqual(typeof(BoolEval), result.GetType());
BoolEval be = (BoolEval)result;
return(be.BooleanValue);
}
protected override CompareResult CompareSameType(ValueEval other)
{
BoolEval be = (BoolEval)other;
bool otherVal = be.BooleanValue;
if (_value == otherVal)
{
return(CompareResult.EQUAL);
}
// TRUE > FALSE
if (_value)
{
return(CompareResult.GREATER_THAN);
}
return(CompareResult.LESS_THAN);
}
protected override CompareResult CompareSameType(ValueEval other)
{
BoolEval be = (BoolEval)other;
bool otherVal = be.BooleanValue;
if (_value == otherVal)
{
return(CompareResult.Equal);
}
// TRUE > FALSE
if (_value)
{
return(CompareResult.GreaterThan);
}
return(CompareResult.LessThan);
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0)
{
bool boolArgVal;
try
{
ValueEval ve = OperandResolver.GetSingleValue(arg0, srcRowIndex, srcColumnIndex);
bool? b = OperandResolver.CoerceValueToBoolean(ve, false);
boolArgVal = b == null ? false : (bool)b;
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(BoolEval.ValueOf(!boolArgVal));
}
public void TestZeroEquality_bug47198()
{
NumberEval zero = new NumberEval(0.0);
NumberEval mZero = (NumberEval)Evaluate(UnaryMinusEval.instance, new ValueEval[] { zero, }, 0, 0);
if (BitConverter.DoubleToInt64Bits(mZero.NumberValue) == unchecked ((long)0x8000000000000000L))
{
throw new AssertFailedException("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 AssertFailedException("Identified bug 47198: -0.0 != 0.0");
}
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length % 2 != 0)
{
return(ErrorEval.VALUE_INVALID);
}
for (int i = 0; i < args.Length; i = i + 2)
{
BoolEval logicalTest = (BoolEval)args[i];
if (logicalTest.BooleanValue)
{
return(args[i + 1]);
}
}
return(ErrorEval.NA);
}
public void TestBlankEqualToEmptyString()
{
ValueEval[] args =
{
new StringEval(""),
BlankEval.instance,
};
ValueEval result = Evaluate(EvalInstances.Equal, args, 10, 10);
Assert.AreEqual(typeof(BoolEval), result.GetType());
BoolEval be = (BoolEval)result;
if (!be.BooleanValue)
{
throw new AssertFailedException("Identified bug blank/empty string Equality");
}
Assert.IsTrue(be.BooleanValue);
}
public ValueEval Evaluate(ValueEval[] args, int srcRow, int srcCol)
{
if (args.Length < 1)
{
return(ErrorEval.VALUE_INVALID);
}
bool boolResult;
try
{
boolResult = Calculate(args);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(BoolEval.ValueOf(boolResult));
}
public override bool Matches(ValueEval x)
{
int testValue;
if (x is StringEval)
{
return(false);
}
else if ((x is BoolEval))
{
BoolEval be = (BoolEval)x;
testValue = BoolToInt(be.BooleanValue);
}
else if ((x is BlankEval))
{
switch (Code)
{
case CmpOp.NE:
// Excel counts blank values in range as not equal to any value. See Bugzilla 51498
return(true);
default:
return(false);
}
}
else if ((x is NumberEval))
{
switch (Code)
{
case CmpOp.NE:
// not-equals comparison of a number to boolean always returnes false
return(true);
default:
return(false);
}
}
else
{
return(false);
}
return(Evaluate(testValue - _value));
}
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 AssertFailedException("Identified bug 47598: 1+1.0028-0.9973 != 1.0055");
}
}
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));
}
Assert.IsTrue(ve is NumberEval, "wrong return type (" + ve.GetType().Name + ")");
NumberEval numberEval = (NumberEval)ve;
String err = String.Format("days360({0},{1},{2}) wrong result", firstArg, secondArg, method);
Assert.AreEqual(expResult, numberEval.NumberValue, err);
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length != 1)
{
return(ErrorEval.VALUE_INVALID);
}
int val;
try
{
val = EvaluateArgParity(args[0], ec.RowIndex, ec.ColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(BoolEval.ValueOf(val == _desiredParity));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0)
{
ValueEval ve;
try
{
ve = OperandResolver.GetSingleValue(arg0, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
//if (false)
//{
// // Note - it is more usual to propagate error codes straight to the result like this:
// return e.GetErrorEval();
// // but logical functions behave a little differently
//}
// this will usually cause a 'FALSE' result except for ISNONTEXT()
ve = e.GetErrorEval();
}
return(BoolEval.ValueOf(Evaluate(ve)));
}
public override bool Matches(ValueEval x)
{
int testValue;
if (x is StringEval)
{
#if !HIDE_UNREACHABLE_CODE
if (true)
{ // change to false to observe more intuitive behaviour
// Note - Unlike with numbers, it seems that COUNTIF never matches
// boolean values when the target(x) is a string
return(false);
}
StringEval se = (StringEval)x;
Boolean? val = ParseBoolean(se.StringValue);
if (val == null)
{
// x is text that is not a boolean
return(false);
}
testValue = BoolToInt(val.Value);
#else
return(false);
#endif
}
else if ((x is BoolEval))
{
BoolEval be = (BoolEval)x;
testValue = BoolToInt(be.BooleanValue);
}
else
{
return(false);
}
return(Evaluate(testValue - _value));
}
private ValueEval convertObjectEval(Object token)
{
if (token == null)
{
throw new ArgumentNullException("Array item cannot be null");
}
if (token is String)
{
return(new StringEval((String)token));
}
if (token is Double)
{
return(new NumberEval(((Double)token)));
}
if (token is Boolean)
{
return(BoolEval.ValueOf((Boolean)token));
}
if (token is Constant.ErrorConstant)
{
return(ErrorEval.ValueOf(((Constant.ErrorConstant)token).ErrorCode));
}
throw new ArgumentException("Unexpected constant class (" + token.GetType().Name + ")");
}
