/// <summary> /// Throws an <see cref="ExcelErrorValueException"/> with the type of given <paramref name="value"/> set. /// </summary> /// <param name="value"></param> protected void ThrowExcelErrorValueException(ExcelErrorValue value) { if (value != null) { throw new ExcelErrorValueException(value.Type); } }
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 ExternalWorkbookReferencesPoundRefAsNamedRanges() { var testFile = new FileInfo(@"externalreferences.xlsx"); var tempFile = new FileInfo(Path.GetTempFileName()); if (tempFile.Exists) { tempFile.Delete(); } testFile.CopyTo(tempFile.FullName); try { using (var package = new ExcelPackage(tempFile)) { Assert.AreEqual(2, package.Workbook.ExternalReferences.References.Count); var sheet = package.Workbook.Worksheets.First(); sheet.Cells["F15"].Calculate(); Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Ref), sheet.Cells["F15"].Value); } } finally { tempFile.Delete(); } }
public override object Parse(object obj) { Require.That(obj).Named("argument").IsNotNull(); if (obj is ExcelDataProvider.IRangeInfo) { var r = ((ExcelDataProvider.IRangeInfo)obj).FirstOrDefault(); return(r == null ? 0 : r.ValueDouble); } if (obj is double) { return(obj); } if (obj.IsNumeric()) { return(util.ConvertUtil.GetValueDouble(obj)); } var str = obj != null?obj.ToString() : string.Empty; try { double d; if (double.TryParse(str, NumberStyles.Any, CultureInfo.InvariantCulture, out d)) { return(d); } return(System.DateTime.Parse(str, CultureInfo.CurrentCulture, DateTimeStyles.None).ToOADate()); } catch// (Exception e) { throw new ExcelErrorValueException(ExcelErrorValue.Create(eErrorType.Value)); } }
/// <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 void DAverageShouldReturnCorrectResult() { using (var package = new ExcelPackage()) { var sheet = package.Workbook.Worksheets.Add("test"); // database sheet.Cells["A1"].Value = "crit1"; sheet.Cells["B1"].Value = "crit2"; sheet.Cells["A2"].Value = "test"; sheet.Cells["B2"].Value = 2; sheet.Cells["A3"].Value = "tesst"; sheet.Cells["B3"].Value = 1; // criteria sheet.Cells["D1"].Value = "crit1"; sheet.Cells["D2"].Value = "t*t"; // function sheet.Cells["F1"].Formula = "DAVERAGE(A1:B3,\"Crit2\",D1:E2)"; sheet.Cells["F2"].Formula = "DAVERAGE(\"Too few arguments\")"; sheet.Workbook.Calculate(); Assert.AreEqual(1.5d, sheet.Cells["F1"].Value); Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Value), sheet.Cells["F2"].Value); } }
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context) { var functionArguments = arguments as FunctionArgument[] ?? arguments.ToArray(); if (this.ArgumentsAreValid(functionArguments, 2, out eErrorType argumentError) == false) { return(new CompileResult(argumentError)); } var search = ArgToString(functionArguments, 0); var searchIn = ArgToString(functionArguments, 1); var startIndex = 0; if (functionArguments.Count() > 2) { startIndex = ArgToInt(functionArguments, 2); } var result = searchIn.IndexOf(search, startIndex, System.StringComparison.Ordinal); if (result == -1) { return(CreateResult(ExcelErrorValue.Create(eErrorType.Value), DataType.ExcelError)); } // Adding 1 because Excel uses 1-based index return(CreateResult(result + 1, DataType.Integer)); }
public override object Parse(object obj) { Require.That(obj).Named("argument").IsNotNull(); int result; if (obj is ExcelDataProvider.IRangeInfo) { var r = ((ExcelDataProvider.IRangeInfo)obj).FirstOrDefault(); return(r == null ? 0 : Convert.ToInt32(r.ValueDouble)); } var objType = obj.GetType(); if (objType == typeof(int)) { return((int)obj); } if (objType == typeof(double) || objType == typeof(decimal)) { return(Convert.ToInt32(obj)); } if (!int.TryParse(obj.ToString(), out result)) { throw new ExcelErrorValueException(ExcelErrorValue.Create(eErrorType.Value)); } return(result); }
public override CompileResult Compile(IEnumerable <Expression> children) { if (children.Count() != 2) { return(new CompileResult(eErrorType.Value)); } var args = new List <FunctionArgument>(); Function.BeforeInvoke(Context); var firstChild = children.First(); var lastChild = children.ElementAt(1); try { var result = firstChild.Compile(); if (result.DataType == DataType.ExcelError && (Equals(result.Result, ExcelErrorValue.Create(eErrorType.NA)))) { args.Add(new FunctionArgument(lastChild.Compile().Result)); } else { args.Add(new FunctionArgument(result.Result)); } } catch (ExcelErrorValueException) { args.Add(new FunctionArgument(lastChild.Compile().Result)); } return(Function.Execute(args, Context)); }
internal CompileResult Execute(string dateString) { System.DateTime.TryParse(dateString, out System.DateTime result); return(result != System.DateTime.MinValue ? CreateResult(result.ToOADate(), DataType.Date) : CreateResult(ExcelErrorValue.Create(eErrorType.Value), DataType.ExcelError)); }
public void ObjectMatchesCriteriaWithInequalitiesAndErrorValues() { var nullError = ExcelErrorValue.Create(eErrorType.Null); var div0Error = ExcelErrorValue.Create(eErrorType.Div0); var valueError = ExcelErrorValue.Create(eErrorType.Value); var refError = ExcelErrorValue.Create(eErrorType.Ref); var nameError = ExcelErrorValue.Create(eErrorType.Name); var numError = ExcelErrorValue.Create(eErrorType.Num); var naError = ExcelErrorValue.Create(eErrorType.NA); var nullLessThanDiv = IfHelper.ObjectMatchesCriterion(nullError, "<#DIV/0!"); var divLessThanValue = IfHelper.ObjectMatchesCriterion(div0Error, "<#VALUE!");; var valueLessThanRef = IfHelper.ObjectMatchesCriterion(valueError, "<#REF!");; var refLessThanName = IfHelper.ObjectMatchesCriterion(refError, "<#NAME?");; var nameLessThanNum = IfHelper.ObjectMatchesCriterion(nameError, "<#NUM!");; var numLessThanNA = IfHelper.ObjectMatchesCriterion(numError, "<#N/A"); var naGreaterThanNum = IfHelper.ObjectMatchesCriterion(naError, ">#NUM!");; Assert.AreEqual(nullLessThanDiv, true); Assert.AreEqual(divLessThanValue, true); Assert.AreEqual(valueLessThanRef, true); Assert.AreEqual(refLessThanName, true); Assert.AreEqual(nameLessThanNum, true); Assert.AreEqual(numLessThanNA, true); Assert.AreEqual(naGreaterThanNum, true); }
public override CompileResult Compile() { try { var funcName = ExpressionString; // older versions of Excel (pre 2007) adds "_xlfn." in front of some function names for compatibility reasons. // EPPlus implements most of these functions, so we just remove this. if (funcName.StartsWith("_xlfn.")) { funcName = funcName.Replace("_xlfn.", string.Empty); } var function = _parsingContext.Configuration.FunctionRepository.GetFunction(funcName); if (function == null) { // Handle unrecognized func name var pipeline = new FunctionsPipeline(_parsingContext, Children); function = pipeline.FindFunction(funcName); if (function == null) { if (_parsingContext.Debug) { _parsingContext.Configuration.Logger.Log(_parsingContext, string.Format("'{0}' is not a supported function", funcName)); } return(new CompileResult(ExcelErrorValue.Create(eErrorType.Name), DataType.ExcelError)); } } if (_parsingContext.Debug) { _parsingContext.Configuration.Logger.LogFunction(funcName); } var compiler = _functionCompilerFactory.Create(function); var result = compiler.Compile(HasChildren ? Children : Enumerable.Empty <Expression>()); if (_isNegated) { if (!result.IsNumeric) { if (_parsingContext.Debug) { var msg = string.Format("Trying to negate a non-numeric value ({0}) in function '{1}'", result.Result, funcName); _parsingContext.Configuration.Logger.Log(_parsingContext, msg); } return(new CompileResult(ExcelErrorValue.Create(eErrorType.Value), DataType.ExcelError)); } return(new CompileResult(result.ResultNumeric * -1, result.DataType)); } return(result); } catch (ExcelErrorValueException e) { if (_parsingContext.Debug) { _parsingContext.Configuration.Logger.Log(_parsingContext, e); } return(new CompileResult(e.ErrorValue, DataType.ExcelError)); } }
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context) { ValidateArguments(arguments, 1); double result = 0d; List <List <double> > results = new List <List <double> >(); foreach (var arg in arguments) { results.Add(new List <double>()); var currentResult = results.Last(); if (arg.Value is IEnumerable <FunctionArgument> ) { foreach (var val in (IEnumerable <FunctionArgument>)arg.Value) { AddValue(val.Value, currentResult); } } else if (arg.Value is FunctionArgument) { AddValue(arg.Value, currentResult); } else if (arg.IsExcelRange) { var r = arg.ValueAsRangeInfo; for (int col = r.Address._fromCol; col <= r.Address._toCol; col++) { for (int row = r.Address._fromRow; row <= r.Address._toRow; row++) { AddValue(r.GetValue(row, col), currentResult); } } } else if (IsNumeric(arg.Value)) { AddValue(arg.Value, currentResult); } } // Validate that all supplied lists have the same length var arrayLength = results.First().Count; foreach (var list in results) { if (list.Count != arrayLength) { throw new ExcelErrorValueException(ExcelErrorValue.Create(eErrorType.Value)); //throw new ExcelFunctionException("All supplied arrays must have the same length", ExcelErrorCodes.Value); } } for (var rowIndex = 0; rowIndex < arrayLength; rowIndex++) { double rowResult = 1; for (var colIndex = 0; colIndex < results.Count; colIndex++) { rowResult *= results[colIndex][rowIndex]; } result += rowResult; } return(CreateResult(result, DataType.Decimal)); }
public void IfErrorWithStringAndErrorReturnsCorrectResult() { var function = new IfError(); var arguments = FunctionsHelper.CreateArgs("word", ExcelErrorValue.Create(eErrorType.Num)); var result = function.Execute(arguments, this.ParsingContext); Assert.AreEqual("word", result.Result); }
public void IfErrorWithErrorAndNullArgumentReturnsCorrectResult() { var function = new IfError(); var arguments = FunctionsHelper.CreateArgs(ExcelErrorValue.Create(eErrorType.Name), null); var result = function.Execute(arguments, this.ParsingContext); Assert.AreEqual(0, result.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 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 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 IfErrorWithErrorAndEmptyStringReturnsCorrectResult() { var function = new IfError(); var arguments = FunctionsHelper.CreateArgs(ExcelErrorValue.Create(eErrorType.Div0), string.Empty); var result = function.Execute(arguments, this.ParsingContext); Assert.AreEqual(string.Empty, result.Result); }
public void FloorWithOneArgumentPoundValues() { var func = new Floor(); var args = FunctionsHelper.CreateArgs(9); var result = func.Execute(args, _parsingContext); Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Value), result.Result); }
public void FloorOfPositiveIntoNegativeNumberIsPoundNum() { var func = new Floor(); var args = FunctionsHelper.CreateArgs(2.5, -2); var result = func.Execute(args, _parsingContext); Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Num), result.Result); }
public void FloorWithZeroSignificancePoundDivZeroes() { var func = new Floor(); var args = FunctionsHelper.CreateArgs(9, 0); var result = func.Execute(args, _parsingContext); Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Div0), result.Result); }
public void CreateNameErrorStringResult() { string value = "#NAME?"; CompileResult compileResult = new CompileResultFactory().Create(value); Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Name), compileResult.Result); Assert.AreEqual(DataType.ExcelError, compileResult.DataType); }
public void MatchExactNotFound() { this.Worksheet.Cells["A1"].Value = 5d; this.Worksheet.Cells["A2"].Value = 1d; this.Worksheet.Cells["A3"].Value = 3d; this.Worksheet.Cells["A4"].Formula = "MATCH(2,A1:A3,0)"; this.Worksheet.Calculate(); Assert.AreEqual(ExcelErrorValue.Create(eErrorType.NA), this.Worksheet.Cells["A4"].Value); }
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 ShouldIgnoreErrorInCriteriaRange() { _sheet.Cells["B3"].Value = ExcelErrorValue.Create(eErrorType.Div0); _sheet.Cells["A5"].Formula = "SUMIFS(A1:A4;B1:B5;\">5\";C1:C5;\">4\")"; _sheet.Calculate(); Assert.AreEqual(6d, _sheet.Cells["A5"].Value); }
/// <summary> /// Instantiates a new <see cref="CompileResult"/> for an error value. /// </summary> /// <param name="errorValue">The <see cref="ExcelErrorValue"/> result.</param> public CompileResult(ExcelErrorValue errorValue) { if (errorValue == null) { throw new ArgumentNullException(nameof(errorValue)); } this.Result = errorValue; this.DataType = DataType.ExcelError; }
public void IndexHandlesNAError() { this.Worksheet.Cells["A1"].Value = 1d; this.Worksheet.Cells["A2"].Value = 3d; this.Worksheet.Cells["A3"].Value = 5d; this.Worksheet.Cells["A4"].Value = ExcelErrorValue.Create(eErrorType.NA); this.Worksheet.Cells["A5"].Formula = "INDEX(A1:A3,A4)"; this.Worksheet.Calculate(); Assert.AreEqual(ExcelErrorValue.Create(eErrorType.NA), this.Worksheet.Cells["A5"].Value); }
public void CalculateWithInvalidDateValue() { using (ExcelPackage package = new ExcelPackage()) { var sheet = package.Workbook.Worksheets.Add("Sheet1"); sheet.Cells[2, 2].Formula = "YEAR(\"30/12/15\")"; sheet.Calculate(); Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Value), sheet.Cells[2, 2].Value); } }
internal CompileResult Execute(string dateString) { this.TryParseDateStringToDouble(dateString, out double resultAfterParse); var resultDecimalsOnly = resultAfterParse - System.Math.Truncate(resultAfterParse); return(resultAfterParse != -1 ? //The '-1' is used to throw an error if an invalid input is supplied. CreateResult(resultDecimalsOnly, DataType.Decimal) : CreateResult(ExcelErrorValue.Create(eErrorType.Value), DataType.ExcelError)); }
public CompileResult(ExcelErrorValue errorValue) { Require.Argument(errorValue).IsNotNull("errorValue"); Result = errorValue; DataType = DataType.ExcelError; }
public ExcelErrorExpression(ExcelErrorValue error) : this(error.ToString(), error) { }
public ExcelErrorExpression(string expression, ExcelErrorValue error) : base(expression) { _error = error; }
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; }
public ExcelErrorValueException(string message, ExcelErrorValue error) : base(message) { ErrorValue = error; }
public ExcelErrorValueException(ExcelErrorValue error) : this(error.ToString(), error) { }