/// <summary>
/// If the supplied <paramref name="cell"/> contains an Excel error
/// an <see cref="ExcelErrorValueException"/> with that errorcode will be thrown
/// </summary>
/// <param name="cell"></param>
protected void CheckForAndHandleExcelError(ExcelDataProvider.ICellInfo cell)
{
if (cell.IsExcelError)
{
throw (new ExcelErrorValueException(ExcelErrorValue.Parse(cell.Value.ToString())));
}
}
public virtual IEnumerable <ExcelDoubleCellValue> ConvertArgs(bool ignoreHidden, bool ignoreErrors, IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
return(base.FuncArgsToFlatEnumerable(arguments, (arg, argList) =>
{
if (arg.IsExcelRange)
{
foreach (var cell in arg.ValueAsRangeInfo)
{
if (!ignoreErrors && cell.IsExcelError)
{
throw new ExcelErrorValueException(ExcelErrorValue.Parse(cell.Value.ToString()));
}
if (!CellStateHelper.ShouldIgnore(ignoreHidden, cell, context) && ConvertUtil.IsNumeric(cell.Value))
{
var val = new ExcelDoubleCellValue(cell.ValueDouble, cell.Row);
argList.Add(val);
}
}
}
else
{
if (!ignoreErrors && arg.ValueIsExcelError)
{
throw new ExcelErrorValueException(arg.ValueAsExcelErrorValue);
}
if (ConvertUtil.IsNumeric(arg.Value) && !CellStateHelper.ShouldIgnore(ignoreHidden, arg, context))
{
var val = new ExcelDoubleCellValue(ConvertUtil.GetValueDouble(arg.Value));
argList.Add(val);
}
}
}));
}
public void IsErrorShouldReturnTrueIfArgIsAnErrorCode()
{
var args = FunctionsHelper.CreateArgs(ExcelErrorValue.Parse("#DIV/0!"));
var func = new IsError();
var result = func.Execute(args, _context);
Assert.IsTrue((bool)result.Result);
}
public void FromCompileResultExcelErrorUnknown()
{
var compileResult = new CompileResult(ExcelErrorValue.Parse("#N/A"), DataType.Unknown);
var result = new ExpressionConverter().FromCompileResult(compileResult);
Assert.IsInstanceOfType(result, typeof(ExcelErrorExpression));
Assert.AreEqual(ExcelErrorValue.Parse("#N/A"), result.Compile().Result);
}
public void FromCompileResultErrorTypeEnum()
{
var compileResult = new CompileResult(eErrorType.Value, DataType.ExcelError);
var result = new ExpressionConverter().FromCompileResult(compileResult);
Assert.IsInstanceOfType(result, typeof(ExcelErrorExpression));
Assert.AreEqual(ExcelErrorValue.Parse("#VALUE!"), result.Compile().Result);
}
public void CeilingShouldThrowExceptionIfNumberIsPositiveAndSignificanceIsNegative()
{
var expectedValue = ExcelErrorValue.Parse("#NUM!");
var func = new Ceiling();
var args = FunctionsHelper.CreateArgs(22.35d, -1);
var result = func.Execute(args, _parsingContext).Result;
Assert.AreEqual(expectedValue, result);
}
public void IfsShouldReturnValueErrorIfUnevenNumberOfArgs()
{
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("test");
sheet.Cells["A1"].Formula = "IFS(1>2,1,2>1)";
sheet.Calculate();
Assert.AreEqual(ExcelErrorValue.Parse("#VALUE!"), sheet.Cells["A1"].Value);
}
}
public void IfsShouldHaveAtLeastTwoArgs()
{
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("test");
sheet.Cells["A1"].Formula = "IFS(1>2)";
sheet.Calculate();
Assert.AreEqual(ExcelErrorValue.Parse("#VALUE!"), sheet.Cells["A1"].Value);
}
}
public void ParseValidErrorStrings()
{
Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Value), ExcelErrorValue.Parse("#VALUE!"));
Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Name), ExcelErrorValue.Parse("#NAME?"));
Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Div0), ExcelErrorValue.Parse("#DIV/0!"));
Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Null), ExcelErrorValue.Parse("#NULL!"));
Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Num), ExcelErrorValue.Parse("#NUM!"));
Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Ref), ExcelErrorValue.Parse("#REF!"));
Assert.AreEqual(ExcelErrorValue.Create(eErrorType.NA), ExcelErrorValue.Parse("#N/A"));
}
public void IsNaShouldReturnTrueCodeIsNa()
{
using (var pck = new ExcelPackage())
{
var sheet = pck.Workbook.Worksheets.Add("Test");
sheet.Cells["A1"].Value = ExcelErrorValue.Parse("#N/A");
sheet.Cells["A2"].Formula = "ISNA(A1)";
sheet.Calculate();
var result = sheet.Cells["A2"].Value;
Assert.IsTrue((bool)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
case DataType.Time:
case DataType.Date:
return(new DecimalExpression((double)compileResult.Result));
}
return(null);
}
private void AddItems(EPPlusReadOnlyList <Object> items, XmlNode itemsNode, bool updateCacheLookup)
{
if (updateCacheLookup)
{
_cacheLookup = new Dictionary <object, int>(new CacheComparer());
}
foreach (XmlElement c in itemsNode.ChildNodes)
{
if (c.LocalName == "s")
{
items.Add(c.Attributes["v"].Value);
}
else if (c.LocalName == "d")
{
if (ConvertUtil.TryParseDateString(c.Attributes["v"].Value, out DateTime d))
{
items.Add(d);
}
else
{
items.Add(c.Attributes["v"].Value);
}
}
else if (c.LocalName == "n")
{
if (ConvertUtil.TryParseNumericString(c.Attributes["v"].Value, out double num))
{
items.Add(num);
}
else
{
items.Add(c.Attributes["v"].Value);
}
}
else if (c.LocalName == "b")
{
if (ConvertUtil.TryParseBooleanString(c.Attributes["v"].Value, out bool b))
{
items.Add(b);
}
else
{
items.Add(c.Attributes["v"].Value);
}
}
else if (c.LocalName == "e")
{
if (ExcelErrorValue.Values.StringIsErrorValue(c.Attributes["v"].Value))
{
items.Add(ExcelErrorValue.Parse(c.Attributes["v"].Value));
}
else
{
items.Add(c.Attributes["v"].Value);
}
}
else
{
items.Add(null);
}
if (updateCacheLookup)
{
_cacheLookup.Add(items[items.Count - 1] ?? "", items.Count - 1);
}
}
}
/// <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));
}
}
public void ParseSimilarErrorStringThrowsException()
{
ExcelErrorValue.Parse("#VALUE");
}
public void ParseNonErrorStringThrowsException()
{
ExcelErrorValue.Parse("not an error");
}
public void ParseNullThrowsException()
{
ExcelErrorValue.Parse(string.Empty);
}
