/**
* When the second argument is a string, many things are possible
*/
private static IMatchPredicate 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));
}
private static Double evaluateValue(ValueEval arg, int srcRowIndex, int srcColumnIndex)
{
ValueEval veText = OperandResolver.GetSingleValue(arg, srcRowIndex, srcColumnIndex);
String strText1 = OperandResolver.CoerceValueToString(veText);
return(OperandResolver.ParseDouble(strText1));
}
private static double EvaluateMatchTypeArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
ValueEval match_type = OperandResolver.GetSingleValue(arg, srcCellRow, srcCellCol);
if (match_type is ErrorEval)
{
throw new EvaluationException((ErrorEval)match_type);
}
if (match_type is NumericValueEval)
{
NumericValueEval ne = (NumericValueEval)match_type;
return(ne.NumberValue);
}
if (match_type is StringEval)
{
StringEval se = (StringEval)match_type;
double d = OperandResolver.ParseDouble(se.StringValue);
if (double.IsNaN(d))
{
// plain string
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
// if the string Parses as a number, it Is OK
return(d);
}
throw new Exception("Unexpected match_type type (" + match_type.GetType().Name + ")");
}
private static double EvaluateDoubleArg(ValueEval eval, int srcCellRow, int srcCellCol)
{
ValueEval ve = OperandResolver.GetSingleValue(eval, srcCellRow, srcCellCol);
if (ve is NumericValueEval)
{
return(((NumericValueEval)ve).NumberValue);
}
if (ve is StringEval)
{
StringEval se = (StringEval)ve;
double d = OperandResolver.ParseDouble(se.StringValue);
if (double.IsNaN(d))
{
throw new EvalEx(ErrorEval.VALUE_INVALID);
}
return(d);
}
if (ve is BoolEval)
{
// in the context of OFFSet, bools resolve to 0 and 1.
if (((BoolEval)ve).BooleanValue)
{
return(1);
}
return(0);
}
throw new Exception("Unexpected eval type (" + ve.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 optr = CmpOp.GetOperator(value);
value = value.Substring(optr.Length);
bool?boolVal = ParseBoolean(value);
if (boolVal != null)
{
return(new BooleanMatcher(boolVal == true?true:false, optr));
}
Double doubleVal = OperandResolver.ParseDouble(value);
if (!double.IsNaN(doubleVal))
{
return(new NumberMatcher(doubleVal, optr));
}
//else - just a plain string with no interpretation.
return(new StringMatcher(value, optr));
}
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)));
}
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() + ")");
}
public override 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, only for the equality operator
switch (Code)
{
case CmpOp.EQ:
case CmpOp.NONE:
break;
case CmpOp.NE:
// Always matches (inconsistent with above two cases).
// for example '<>123' matches '123', '4', 'abc', etc
return(true);
default:
// never matches (also inconsistent with above three cases).
// for example '>5' does not match '6',
return(false);
}
StringEval se = (StringEval)x;
Double val = OperandResolver.ParseDouble(se.StringValue);
if (double.IsNaN(val))
{
// x is text that is not a number
return(false);
}
return(_value == val);
}
else if ((x is NumberEval))
{
NumberEval ne = (NumberEval)x;
testValue = ne.NumberValue;
}
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
{
return(false);
}
return(Evaluate(testValue.CompareTo(_value)));
}
public void TestParseDoubleInvalidStrings()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
String[] values = new String[] { "-", "ABC", "-X", "1E5a", "Infinity", "NaN", ".5F" }; //, "1,000" };
foreach (String value in values)
{
Assert.AreEqual(double.NaN, OperandResolver.ParseDouble(value));
}
}
public void TestParseDoubleInvalidStrings()
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
String[] values = new String[] { "-", "ABC", "-X", "1E5a", "Infinity", "NaN", ".5F" }; //, "1,000" };
foreach (String value in values)
{
Assert.AreEqual(double.NaN, OperandResolver.ParseDouble(value));
}
}
public void TestParseDouble_bug49723()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
String value = ".1";
Double ResolvedValue;
ResolvedValue = OperandResolver.ParseDouble(value);
Assert.IsNotNull(ResolvedValue, "Identified bug 49723");
}
public void TestParseDouble_bug49723()
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
String value = ".1";
Double ResolvedValue;
ResolvedValue = OperandResolver.ParseDouble(value);
Assert.IsNotNull(ResolvedValue, "Identified bug 49723");
}
public void TestParseDoubleValidStrings()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
String[] values = new String[] { ".19", "0.19", "1.9", "1E4", "-.19", "-0.19", "8.5", "-1E4", ".5E6", "+1.5", "+1E5", " +1E5 " };
foreach (String value in values)
{
Assert.AreNotEqual(double.NaN, OperandResolver.ParseDouble(value)); //this bug is caused by double.Parse
Assert.AreEqual(OperandResolver.ParseDouble(value), Double.Parse(value));
}
}
public void TestParseDoubleValidStrings()
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
String[] values = new String[] { ".19", "0.19", "1.9", "1E4", "-.19", "-0.19", "8.5", "-1E4", ".5E6", "+1.5", "+1E5", " +1E5 " };
foreach (String value in values)
{
Assert.AreNotEqual(double.NaN, OperandResolver.ParseDouble(value)); //this bug is caused by double.Parse
Assert.AreEqual(OperandResolver.ParseDouble(value), Double.Parse(value));
}
}
private static double EvaluateDateArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
ValueEval ve = OperandResolver.GetSingleValue(arg, srcCellRow, (short)srcCellCol);
if (ve is StringEval)
{
String strVal = ((StringEval)ve).StringValue;
Double dVal = OperandResolver.ParseDouble(strVal);
if (!double.IsNaN(dVal))
{
return(dVal);
}
DateTime date = ParseDate(strVal);
return(NPOI.SS.UserModel.DateUtil.GetExcelDate(date, false));
}
return(OperandResolver.CoerceValueToDouble(ve));
}
/**
* Evaluate a generic {@link ValueEval} argument to a double value that represents a date in POI.
*
* @param arg {@link ValueEval} an argument.
* @param srcCellRow number cell row.
* @param srcCellCol number cell column.
* @return a double representing a date in POI.
* @throws EvaluationException exception upon argument evaluation.
*/
public double EvaluateDateArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
ValueEval ve = OperandResolver.GetSingleValue(arg, srcCellRow, (short)srcCellCol);
if (ve is StringEval)
{
String strVal = ((StringEval)ve).StringValue;
Double dVal = OperandResolver.ParseDouble(strVal);
if (!Double.IsNaN(dVal))
{
return(dVal);
}
DateTime dt = DateParser.ParseDate(strVal);
return(DateUtil.GetExcelDate(dt, false));
}
return(OperandResolver.CoerceValueToDouble(ve));
}
public void TestParseDouble_bug48472()
{
String value = "-";
Double ResolvedValue;
try
{
ResolvedValue = OperandResolver.ParseDouble(value);
}
catch (Exception)
{
throw new AssertionException("Identified bug 48472");
}
Assert.AreEqual(double.NaN, ResolvedValue);
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg1, ValueEval arg2)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(arg1, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
String strText1 = OperandResolver.CoerceValueToString(veText1);
Double number1 = OperandResolver.ParseDouble(strText1);
if (double.IsNaN(number1))
{
return(ErrorEval.VALUE_INVALID);
}
ValueEval veText2;
try
{
veText2 = OperandResolver.GetSingleValue(arg2, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
String strText2 = OperandResolver.CoerceValueToString(veText2);
Double number2 = OperandResolver.ParseDouble(strText2);
if (double.IsNaN(number2))
{
return(ErrorEval.VALUE_INVALID);
}
//int result = new BigDecimal(number1).CompareTo(new BigDecimal(number2));
int result = NumberComparer.Compare(number1, number2);
return(result == 0 ? ONE : ZERO);
}
private static ValueVector EvaluateLookupRange(ValueEval eval)
{
if (eval is RefEval)
{
RefEval re = (RefEval)eval;
if (re.NumberOfSheets == 1)
{
return(new SingleValueVector(re.GetInnerValueEval(re.FirstSheetIndex)));
}
else
{
return(LookupUtils.CreateVector(re));
}
}
if (eval is TwoDEval)
{
ValueVector result = LookupUtils.CreateVector((TwoDEval)eval);
if (result == null)
{
throw new EvaluationException(ErrorEval.NA);
}
return(result);
}
// Error handling for lookup_range arg Is also Unusual
if (eval is NumericValueEval)
{
throw new EvaluationException(ErrorEval.NA);
}
if (eval is StringEval)
{
StringEval se = (StringEval)eval;
double d = OperandResolver.ParseDouble(se.StringValue);
if (double.IsNaN(d))
{
// plain string
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
// else looks like a number
throw new EvaluationException(ErrorEval.NA);
}
throw new Exception("Unexpected eval type (" + eval.GetType().Name + ")");
}
/**
* Processes the third argument to VLOOKUP, or HLOOKUP (<b>col_index_num</b>
* or <b>row_index_num</b> respectively).<br/>
* Sample behaviour:
* <table border="0" cellpAdding="1" cellspacing="2" summary="Sample behaviour">
* <tr><th>Input Return</th><th>Value </th><th>Thrown Error</th></tr>
* <tr><td>5</td><td>4</td><td> </td></tr>
* <tr><td>2.9</td><td>2</td><td> </td></tr>
* <tr><td>"5"</td><td>4</td><td> </td></tr>
* <tr><td>"2.18e1"</td><td>21</td><td> </td></tr>
* <tr><td>"-$2"</td><td>-3</td><td>*</td></tr>
* <tr><td>FALSE</td><td>-1</td><td>*</td></tr>
* <tr><td>TRUE</td><td>0</td><td> </td></tr>
* <tr><td>"TRUE"</td><td> </td><td>#REF!</td></tr>
* <tr><td>"abc"</td><td> </td><td>#REF!</td></tr>
* <tr><td>""</td><td> </td><td>#REF!</td></tr>
* <tr><td><blank></td><td> </td><td>#VALUE!</td></tr>
* </table><br/>
*
* * Note - out of range errors (both too high and too low) are handled by the caller.
* @return column or row index as a zero-based value
*
*/
public static int ResolveRowOrColIndexArg(ValueEval rowColIndexArg, int srcCellRow, int srcCellCol)
{
if (rowColIndexArg == null)
{
throw new ArgumentException("argument must not be null");
}
ValueEval veRowColIndexArg;
try
{
veRowColIndexArg = OperandResolver.GetSingleValue(rowColIndexArg, srcCellRow, (short)srcCellCol);
}
catch (EvaluationException)
{
// All errors get translated to #REF!
throw EvaluationException.InvalidRef();
}
int oneBasedIndex;
if (veRowColIndexArg is StringEval)
{
StringEval se = (StringEval)veRowColIndexArg;
string strVal = se.StringValue;
Double dVal = OperandResolver.ParseDouble(strVal);
if (Double.IsNaN(dVal))
{
// String does not resolve to a number. Raise #REF! error.
throw EvaluationException.InvalidRef();
// This includes text booleans "TRUE" and "FALSE". They are not valid.
}
// else - numeric value parses OK
}
// actual BoolEval values get interpreted as FALSE->0 and TRUE->1
oneBasedIndex = OperandResolver.CoerceValueToInt(veRowColIndexArg);
if (oneBasedIndex < 1)
{
// note this is asymmetric with the errors when the index is too large (#REF!)
throw EvaluationException.InvalidValue();
}
return(oneBasedIndex - 1); // convert to zero based
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval number, ValueEval places)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(number, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
String strText1 = OperandResolver.CoerceValueToString(veText1);
Double number1 = OperandResolver.ParseDouble(strText1);
//If this number argument is non numeric, this function returns the #VALUE! error value.
if (double.IsNaN(number1))
{
return(ErrorEval.VALUE_INVALID);
}
//If number < -549,755,813,888 or if number > 549,755,813,887, this function returns the #NUM! error value.
if (number1 < MinValue || number1 > MaxValue)
{
return(ErrorEval.NUM_ERROR);
}
int placesNumber = 0;
if (number1 < 0)
{
placesNumber = DEFAULT_PLACES_VALUE;
}
else if (places != null)
{
ValueEval placesValueEval;
try
{
placesValueEval = OperandResolver.GetSingleValue(places, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
String placesStr = OperandResolver.CoerceValueToString(placesValueEval);
Double placesNumberDouble = OperandResolver.ParseDouble(placesStr);
//non numeric value
if (double.IsNaN(placesNumberDouble))
{
return(ErrorEval.VALUE_INVALID);
}
//If this argument Contains a decimal value, this function ignores the numbers to the right side of the decimal point.
placesNumber = (int)placesNumberDouble;
if (placesNumber < 0)
{
return(ErrorEval.NUM_ERROR);
}
}
String hex = "";
if (placesNumber != 0)
{
hex = String.Format("{0:X" + placesNumber + "}", (int)number1);
}
else
{
hex = String.Format("{0:X}", (int)number1);
}
if (number1 < 0)
{
hex = "FF" + hex.Substring(2);
}
return(new StringEval(hex.ToUpper()));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval numberVE, ValueEval placesVE)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(numberVE, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
string strText1 = OperandResolver.CoerceValueToString(veText1);
Double number = OperandResolver.ParseDouble(strText1);
//If this number argument is non numeric, this function returns the #VALUE! error value.
if (double.IsNaN(number))
{
return(ErrorEval.VALUE_INVALID);
}
//If number < -512 or if number > 512, this function returns the #NUM! error value.
if (number < MinValue || number > MaxValue)
{
return(ErrorEval.NUM_ERROR);
}
int placesNumber;
if (number < 0 || placesVE == null)
{
placesNumber = DEFAULT_PLACES_VALUE;
}
else
{
ValueEval placesValueEval;
try
{
placesValueEval = OperandResolver.GetSingleValue(placesVE, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
string placesStr = OperandResolver.CoerceValueToString(placesValueEval);
Double placesNumberDouble = OperandResolver.ParseDouble(placesStr);
//non numeric value
if (double.IsNaN(placesNumberDouble))
{
return(ErrorEval.VALUE_INVALID);
}
//If this argument Contains a decimal value, this function ignores the numbers to the right side of the decimal point.
placesNumber = (int)Math.Floor(placesNumberDouble);
if (placesNumber < 0 || placesNumber == 0)
{
return(ErrorEval.NUM_ERROR);
}
}
string binary = Convert.ToString((int)Math.Floor(number), 2);
if (binary.Length > DEFAULT_PLACES_VALUE)
{
binary = binary.Substring(binary.Length - DEFAULT_PLACES_VALUE);
}
//If DEC2BIN requires more than places characters, it returns the #NUM! error value.
if (binary.Length > placesNumber)
{
return(ErrorEval.NUM_ERROR);
}
return(new StringEval(binary));
}
