public void FromCompileResultShouldCreateBooleanExpressionIfCompileResultIsBoolean()
{
var compileResult = new CompileResult("true", DataType.Boolean);
var result = _converter.FromCompileResult(compileResult);
Assert.IsInstanceOfType(result, typeof(BooleanExpression));
Assert.IsTrue((bool)result.Compile().Result);
}
public Expression FromCompileResult(CompileResult compileResult)
{
switch (compileResult.DataType)
{
case DataType.Integer:
return compileResult.Result is string
? new IntegerExpression(compileResult.Result.ToString())
: new IntegerExpression(Convert.ToDouble(compileResult.Result));
case DataType.String:
return new StringExpression(compileResult.Result.ToString());
case DataType.Decimal:
return compileResult.Result is string
? new DecimalExpression(compileResult.Result.ToString())
: new DecimalExpression(((double) compileResult.Result));
case DataType.Boolean:
return compileResult.Result is string
? new BooleanExpression(compileResult.Result.ToString())
: new BooleanExpression((bool) compileResult.Result);
//case DataType.Enumerable:
// return
case DataType.ExcelError:
//throw (new OfficeOpenXml.FormulaParsing.Exceptions.ExcelErrorValueException((ExcelErrorValue)compileResult.Result)); //Added JK
return compileResult.Result is string
? new ExcelErrorExpression(compileResult.Result.ToString(),
ExcelErrorValue.Parse(compileResult.Result.ToString()))
: new ExcelErrorExpression((ExcelErrorValue) compileResult.Result);
case DataType.Empty:
return new IntegerExpression(0); //Added JK
}
return null;
}
public void FromCompileResultShouldCreateStringExpressionIfCompileResultIsString()
{
var compileResult = new CompileResult("abc", DataType.String);
var result = _converter.FromCompileResult(compileResult);
Assert.IsInstanceOfType(result, typeof(StringExpression));
Assert.AreEqual("abc", result.Compile().Result);
}
public void FromCompileResultShouldCreateDecimalExpressionIfCompileResultIsDecimal()
{
var compileResult = new CompileResult(2.5d, DataType.Decimal);
var result = _converter.FromCompileResult(compileResult);
Assert.IsInstanceOfType(result, typeof(DecimalExpression));
Assert.AreEqual(2.5d, result.Compile().Result);
}
public void FromCompileResultShouldCreateIntegerExpressionIfCompileResultIsInteger()
{
var compileResult = new CompileResult(1, DataType.Integer);
var result = _converter.FromCompileResult(compileResult);
Assert.IsInstanceOfType(result, typeof(IntegerExpression));
Assert.AreEqual(1d, result.Compile().Result);
}
public void NumericStringCompileResult()
{
var expected = 124.24;
string numericString = expected.ToString("n");
CompileResult result = new CompileResult(numericString, DataType.String);
Assert.IsFalse(result.IsNumeric);
Assert.IsTrue(result.IsNumericString);
Assert.AreEqual(expected, result.ResultNumeric);
}
public void DateStringCompileResult()
{
var expected = new DateTime(2013, 1, 15);
string dateString = expected.ToString("d");
CompileResult result = new CompileResult(dateString, DataType.String);
Assert.IsFalse(result.IsNumeric);
Assert.IsTrue(result.IsDateString);
Assert.AreEqual(expected.ToOADate(), result.ResultNumeric);
}
public CompileResult Apply(CompileResult left, CompileResult right)
{
if (left.Result is ExcelErrorValue)
{
return new CompileResult(left.Result, DataType.ExcelError);
//throw(new ExcelErrorValueException((ExcelErrorValue)left.Result));
}
else if (right.Result is ExcelErrorValue)
{
return new CompileResult(right.Result, DataType.ExcelError);
//throw(new ExcelErrorValueException((ExcelErrorValue)right.Result));
}
return _implementation(left, right);
}
private static bool EitherIsError(CompileResult l, CompileResult r, out ExcelErrorValue errorVal)
{
if (l.DataType == DataType.ExcelError)
{
errorVal = (ExcelErrorValue) l.Result;
return true;
}
if (r.DataType == DataType.ExcelError)
{
errorVal = (ExcelErrorValue) r.Result;
return true;
}
errorVal = null;
return false;
}
private static CompileResult Compare(CompileResult l, CompileResult r, Func<int, bool> comparison )
{
ExcelErrorValue errorVal;
if (EitherIsError(l, r, out errorVal))
{
return new CompileResult(errorVal);
}
object left, right;
left = GetObjFromOther(l, r);
right = GetObjFromOther(r, l);
if (ConvertUtil.IsNumeric(left) && ConvertUtil.IsNumeric(right))
{
var lnum = ConvertUtil.GetValueDouble(left);
var rnum = ConvertUtil.GetValueDouble(right);
if (Math.Abs(lnum - rnum) < double.Epsilon)
{
return new CompileResult(comparison(0), DataType.Boolean);
}
var comparisonResult = lnum.CompareTo(rnum);
return new CompileResult(comparison(comparisonResult), DataType.Boolean);
}
else
{
var comparisonResult = CompareString(left, right);
return new CompileResult(comparison(comparisonResult), DataType.Boolean);
}
}
private static object GetObjFromOther(CompileResult obj, CompileResult other)
{
if (obj.Result == null)
{
if (other.DataType == DataType.String) return string.Empty;
else return 0d;
}
return obj.ResultValue;
}
private CompileResult CompileSingleCell(ExcelDataProvider.IRangeInfo result)
{
var cell = result.First();
var factory = new CompileResultFactory();
var compileResult = factory.Create(cell.Value);
if (_negate && compileResult.IsNumeric)
{
compileResult = new CompileResult(compileResult.ResultNumeric * -1, compileResult.DataType);
}
compileResult.IsHiddenCell = cell.IsHiddenRow;
return compileResult;
}
private static int Compare(CompileResult l, CompileResult r)
{
CheckForErrors(l, r);
object left, right;
left = GetObjFromOther(l, r);
right = GetObjFromOther(r, l);
if (ConvertUtil.IsNumeric(left) && ConvertUtil.IsNumeric(right))
{
var lnum = ConvertUtil.GetValueDouble(left);
var rnum = ConvertUtil.GetValueDouble(right);
if (Math.Abs(lnum - rnum) < double.Epsilon) return 0;
return lnum.CompareTo(rnum);
}
else
{
return CompareString(left, right);
}
}
private static void CheckForErrors(CompileResult l, CompileResult r)
{
if (l.DataType == DataType.ExcelError)
{
throw new ExcelErrorValueException((ExcelErrorValue)l.Result);
}
if (r.DataType == DataType.ExcelError)
{
throw new ExcelErrorValueException((ExcelErrorValue)r.Result);
}
}
public void OperatorsActingOnDateStrings()
{
const string dateFormat = "M-dd-yyyy";
DateTime date1 = new DateTime(2015, 2, 20);
DateTime date2 = new DateTime(2015, 12, 1);
var numericDate1 = date1.ToOADate();
var numericDate2 = date2.ToOADate();
CompileResult result1 = new CompileResult(date1.ToString(dateFormat), DataType.String); // 2/20/2015
CompileResult result2 = new CompileResult(date2.ToString(dateFormat), DataType.String); // 12/1/2015
var operatorResult = Operator.Concat.Apply(result1, result2);
Assert.AreEqual($"{date1.ToString(dateFormat)}{date2.ToString(dateFormat)}", operatorResult.Result);
operatorResult = Operator.Divide.Apply(result1, result2);
Assert.AreEqual(numericDate1 / numericDate2, operatorResult.Result);
operatorResult = Operator.Exp.Apply(result1, result2);
Assert.AreEqual(Math.Pow(numericDate1, numericDate2), operatorResult.Result);
operatorResult = Operator.Minus.Apply(result1, result2);
Assert.AreEqual(numericDate1 - numericDate2, operatorResult.Result);
operatorResult = Operator.Multiply.Apply(result1, result2);
Assert.AreEqual(numericDate1 * numericDate2, operatorResult.Result);
operatorResult = Operator.Percent.Apply(result1, result2);
Assert.AreEqual(numericDate1 * numericDate2, operatorResult.Result);
operatorResult = Operator.Plus.Apply(result1, result2);
Assert.AreEqual(numericDate1 + numericDate2, operatorResult.Result);
// Comparison operators always compare string-wise and don't parse out the actual numbers.
operatorResult = Operator.Eq.Apply(result1, new CompileResult(date1.ToString("f"), DataType.String));
Assert.IsFalse((bool)operatorResult.Result);
operatorResult = Operator.NotEqualsTo.Apply(result1, new CompileResult(date1.ToString("f"), DataType.String));
Assert.IsTrue((bool)operatorResult.Result);
operatorResult = Operator.GreaterThan.Apply(result1, result2);
Assert.IsTrue((bool)operatorResult.Result);
operatorResult = Operator.GreaterThanOrEqual.Apply(result1, result2);
Assert.IsTrue((bool)operatorResult.Result);
operatorResult = Operator.LessThan.Apply(result1, result2);
Assert.IsFalse((bool)operatorResult.Result);
operatorResult = Operator.LessThanOrEqual.Apply(result1, result2);
Assert.IsFalse((bool)operatorResult.Result);
}
public void OperatorsActingOnNumericStrings()
{
double number1 = 42.0;
double number2 = -143.75;
CompileResult result1 = new CompileResult(number1.ToString("n"), DataType.String);
CompileResult result2 = new CompileResult(number2.ToString("n"), DataType.String);
var operatorResult = Operator.Concat.Apply(result1, result2);
Assert.AreEqual($"{number1.ToString("n")}{number2.ToString("n")}", operatorResult.Result);
operatorResult = Operator.Divide.Apply(result1, result2);
Assert.AreEqual(number1 / number2, operatorResult.Result);
operatorResult = Operator.Exp.Apply(result1, result2);
Assert.AreEqual(Math.Pow(number1, number2), operatorResult.Result);
operatorResult = Operator.Minus.Apply(result1, result2);
Assert.AreEqual(number1 - number2, operatorResult.Result);
operatorResult = Operator.Multiply.Apply(result1, result2);
Assert.AreEqual(number1 * number2, operatorResult.Result);
operatorResult = Operator.Percent.Apply(result1, result2);
Assert.AreEqual(number1 * number2, operatorResult.Result);
operatorResult = Operator.Plus.Apply(result1, result2);
Assert.AreEqual(number1 + number2, operatorResult.Result);
// Comparison operators always compare string-wise and don't parse out the actual numbers.
operatorResult = Operator.NotEqualsTo.Apply(result1, new CompileResult(number1.ToString("n0"), DataType.String));
Assert.IsTrue((bool)operatorResult.Result);
operatorResult = Operator.Eq.Apply(result1, new CompileResult(number1.ToString("n0"), DataType.String));
Assert.IsFalse((bool)operatorResult.Result);
operatorResult = Operator.GreaterThan.Apply(result1, result2);
Assert.IsTrue((bool)operatorResult.Result);
operatorResult = Operator.GreaterThanOrEqual.Apply(result1, result2);
Assert.IsTrue((bool)operatorResult.Result);
operatorResult = Operator.LessThan.Apply(result1, result2);
Assert.IsFalse((bool)operatorResult.Result);
operatorResult = Operator.LessThanOrEqual.Apply(result1, result2);
Assert.IsFalse((bool)operatorResult.Result);
}
/// <summary>
/// Converts the given <see cref="CompileResult"/> into an <see cref="Expression"/>.
/// </summary>
/// <param name="compileResult">The <see cref="CompileResult"/> to convert.</param>
/// <returns>Returns the <see cref="Expression"/> representation of the given <see cref="CompileResult"/>.</returns>
public Expression FromCompileResult(CompileResult compileResult)
{
switch (compileResult.DataType)
{
case DataType.Integer:
return(compileResult.Result is string
?new IntegerExpression(compileResult.Result.ToString())
: new IntegerExpression(Convert.ToDouble(compileResult.Result)));
case DataType.Time:
case DataType.Decimal:
return(compileResult.Result is string
?new DecimalExpression(compileResult.Result.ToString())
: new DecimalExpression(Convert.ToDouble(compileResult.Result)));
case DataType.String:
return(new StringExpression(compileResult.Result.ToString()));
case DataType.Boolean:
return(compileResult.Result is string
?new BooleanExpression(compileResult.Result.ToString())
: new BooleanExpression((bool)compileResult.Result));
case DataType.Date:
if (compileResult.Result is DateTime dateTimeResult || DateTime.TryParse(compileResult.Result.ToString(), out dateTimeResult))
{
return(new DateExpression(dateTimeResult.ToOADate().ToString()));
}
if (double.TryParse(compileResult.Result.ToString(), out double oaDate))
{
return(new DateExpression(oaDate.ToString()));
}
return(new ExcelErrorExpression(ExcelErrorValue.Create(eErrorType.Value)));
case DataType.ExcelError:
if (compileResult.Result is ExcelErrorValue errorValueResult)
{
return(new ExcelErrorExpression(errorValueResult));
}
else if (compileResult.Result is eErrorType eErrorTypeResult)
{
return(new ExcelErrorExpression(ExcelErrorValue.Create(eErrorTypeResult)));
}
else
{
return(new ExcelErrorExpression(compileResult.Result?.ToString(), ExcelErrorValue.Parse(compileResult.Result?.ToString())));
}
case DataType.Empty:
return(new IntegerExpression(0));
case DataType.ExcelAddress:
return(new StringExpression(compileResult.Result.ToString()));
case DataType.Enumerable:
case DataType.Unknown:
default:
// Enumerable results only end up with the first item in the collection.
// The result factory will itself return an enumerable CompileResult for List<object> so
// in order to prevent infinite recursion there is an explicit check for that specific type.
// The other form of enumerable result is IRangeInfo which is safely reduced in the result factory.
var resultToProcess = compileResult.Result;
if (resultToProcess is List <object> listResult)
{
resultToProcess = listResult.FirstOrDefault();
}
var result = this.ResultFactory.Create(resultToProcess);
return(this.FromCompileResult(result));
}
}