Class ExcelFunction

Base class for Excel function implementations.

Inheritance
System.Object
ExcelFunction
ConvertFunction
Erf
HiddenValuesHandlingFunction
Text
Inherited Members
System.Object.Equals(System.Object)
System.Object.Equals(System.Object, System.Object)
System.Object.GetHashCode()
System.Object.GetType()
System.Object.MemberwiseClone()
System.Object.ReferenceEquals(System.Object, System.Object)
System.Object.ToString()
Namespace: OfficeOpenXml.FormulaParsing.Excel.Functions
Assembly: EPPlus.dll
Syntax
public abstract class ExcelFunction

Constructors

ExcelFunction()

Default constructor

Declaration
public ExcelFunction()

Fields

NumberOfSignificantFigures

Number of significant figures used in roundings, etc.

Declaration
protected readonly int NumberOfSignificantFigures
Field Value
Type Description
System.Int32

Properties

ArgumentMinLength

Returns the minimum arguments for the function. Number of arguments are validated before calling the execute. If lesser arguments are supplied a #VALUE! error will be returned.

Declaration
public abstract int ArgumentMinLength { get; }
Property Value
Type Description
System.Int32

ArrayBehaviour

Describes how the function works with input ranges and returning arrays.

Declaration
public virtual ExcelFunctionArrayBehaviour ArrayBehaviour { get; }
Property Value
Type Description
ExcelFunctionArrayBehaviour

HandlesVariables

Indicates whether the function handles variables (eg. LET, LAMBDA).

Declaration
public virtual bool HandlesVariables { get; }
Property Value
Type Description
System.Boolean

IsAllowedInCalculatedPivotTableField

If the function is allowed in a pivot table calculated field. Default is true, if not overridden.

Declaration
public virtual bool IsAllowedInCalculatedPivotTableField { get; }
Property Value
Type Description
System.Boolean

IsErrorHandlingFunction

Indicates that the function is an ErrorHandlingFunction.

Declaration
public virtual bool IsErrorHandlingFunction { get; }
Property Value
Type Description
System.Boolean

IsVolatile

If the function returns a different value with the same parameters.

Declaration
public virtual bool IsVolatile { get; }
Property Value
Type Description
System.Boolean

NamespacePrefix

Information of individual arguments of the function used internally by the formula parser .

Declaration
public virtual string NamespacePrefix { get; }
Property Value
Type Description
System.String

Function argument information

ParametersInfo

Provides information about the functions parameters.

Declaration
public virtual ExcelFunctionParametersInfo ParametersInfo { get; }
Property Value
Type Description
ExcelFunctionParametersInfo

ReturnsReference

If the function returns a range reference

Declaration
public virtual bool ReturnsReference { get; }
Property Value
Type Description
System.Boolean

Methods

AreEqual(Double, Double)

Helper method for comparison of two doubles.

Declaration
protected bool AreEqual(double d1, double d2)
Parameters
Type Name Description
System.Double d1
System.Double d2
Returns
Type Description
System.Boolean

ArgsToDoubleEnumerable(FunctionArgument, ParsingContext, out ExcelErrorValue)

Will return the arguments as an enumerable of doubles using default parameters

Declaration
protected virtual IList<double> ArgsToDoubleEnumerable(FunctionArgument argument, ParsingContext context, out ExcelErrorValue error)
Parameters
Type Name Description
FunctionArgument argument
ParsingContext context
ExcelErrorValue error
Returns
Type Description
System.Collections.Generic.IList<System.Double>

ArgsToDoubleEnumerable(FunctionArgument, ParsingContext, Action<DoubleEnumerableParseOptions>, out ExcelErrorValue)

Will return the arguments as an enumerable of doubles.

Declaration
protected virtual IList<double> ArgsToDoubleEnumerable(FunctionArgument argument, ParsingContext context, Action<DoubleEnumerableParseOptions> configHandler, out ExcelErrorValue error)
Parameters
Type Name Description
FunctionArgument argument
ParsingContext context
System.Action<DoubleEnumerableParseOptions> configHandler
ExcelErrorValue error
Returns
Type Description
System.Collections.Generic.IList<System.Double>

ArgsToDoubleEnumerable(IEnumerable<FunctionArgument>, ParsingContext, out ExcelErrorValue)

Will return the arguments as an enumerable of doubles.

Declaration
protected virtual IList<double> ArgsToDoubleEnumerable(IEnumerable<FunctionArgument> arguments, ParsingContext context, out ExcelErrorValue error)
Parameters
Type Name Description
System.Collections.Generic.IEnumerable<FunctionArgument> arguments
ParsingContext context
ExcelErrorValue error
Returns
Type Description
System.Collections.Generic.IList<System.Double>

ArgsToDoubleEnumerable(IEnumerable<FunctionArgument>, ParsingContext, Action<DoubleEnumerableParseOptions>, out ExcelErrorValue)

Will return the arguments as an enumerable of doubles.

Declaration
protected virtual IList<double> ArgsToDoubleEnumerable(IEnumerable<FunctionArgument> arguments, ParsingContext context, Action<DoubleEnumerableParseOptions> configHandler, out ExcelErrorValue error)
Parameters
Type Name Description
System.Collections.Generic.IEnumerable<FunctionArgument> arguments
ParsingContext context
System.Action<DoubleEnumerableParseOptions> configHandler
ExcelErrorValue error
Returns
Type Description
System.Collections.Generic.IList<System.Double>
Exceptions
Type Condition
ExcelErrorValueException

ArgsToObjectEnumerable(Boolean, Boolean, Boolean, IEnumerable<FunctionArgument>, ParsingContext)

Will return the arguments as an enumerable of objects.

Declaration
protected virtual IEnumerable<object> ArgsToObjectEnumerable(bool ignoreHiddenCells, bool ignoreErrors, bool ignoreNestedSubtotalAggregate, IEnumerable<FunctionArgument> arguments, ParsingContext context)
Parameters
Type Name Description
System.Boolean ignoreHiddenCells

If a cell is hidden and this value is true the value of that cell will be ignored
System.Boolean ignoreErrors
System.Boolean ignoreNestedSubtotalAggregate
System.Collections.Generic.IEnumerable<FunctionArgument> arguments
ParsingContext context
Returns
Type Description
System.Collections.Generic.IEnumerable<System.Object>

ArgToAddress(IList<FunctionArgument>, Int32)

Returns a string representation of an arguments address.

Declaration
protected string ArgToAddress(IList<FunctionArgument> arguments, int index)
Parameters
Type Name Description
System.Collections.Generic.IList<FunctionArgument> arguments
System.Int32 index
Returns
Type Description
System.String

ArgToBool(IList<FunctionArgument>, Int32)

If the argument is a boolean value its value will be returned. If the argument is an integer value, true will be returned if its value is not 0, otherwise false.

Declaration
protected bool ArgToBool(IList<FunctionArgument> arguments, int index)
Parameters
Type Name Description
System.Collections.Generic.IList<FunctionArgument> arguments
System.Int32 index
Returns
Type Description
System.Boolean

ArgToBool(IList<FunctionArgument>, Int32, Boolean)

If the argument is a boolean value its value will be returned. If the argument is an integer value, true will be returned if its value is not 0, otherwise false. fallback to ValueIfEmpty if datatype is empty

Declaration
protected bool ArgToBool(IList<FunctionArgument> arguments, int index, bool valueIfEmpty)
Parameters
Type Name Description
System.Collections.Generic.IList<FunctionArgument> arguments
System.Int32 index
System.Boolean valueIfEmpty
Returns
Type Description
System.Boolean

ArgToDecimal(IList<FunctionArgument>, Int32, out ExcelErrorValue, PrecisionAndRoundingStrategy)

Returns the value of the argument att the position of the 0-based index as a System.Double.

Declaration
protected double ArgToDecimal(IList<FunctionArgument> arguments, int index, out ExcelErrorValue error, PrecisionAndRoundingStrategy precisionAndRoundingStrategy = PrecisionAndRoundingStrategy.DotNet)
Parameters
Type Name Description
System.Collections.Generic.IList<FunctionArgument> arguments
System.Int32 index
ExcelErrorValue error

Will be set if an error occurs during conversion
PrecisionAndRoundingStrategy precisionAndRoundingStrategy

strategy for handling precision and rounding of double values
Returns
Type Description
System.Double

Value of the argument as an integer.
Exceptions
Type Condition
ExcelErrorValueException

ArgToDecimal(IList<FunctionArgument>, Int32, Double, out ExcelErrorValue)

Returns the value of the argument att the position of the 0-based index as a System.Double. If the the value is null, zero will be returned.

Declaration
protected double ArgToDecimal(IList<FunctionArgument> arguments, int index, double valueIfNull, out ExcelErrorValue error)
Parameters
Type Name Description
System.Collections.Generic.IList<FunctionArgument> arguments
System.Int32 index
System.Double valueIfNull
ExcelErrorValue error

Will be set if an error occurs during conversion
Returns
Type Description
System.Double

Value of the argument as an integer.
Exceptions
Type Condition
ExcelErrorValueException

ArgToDecimal(Object, out ExcelErrorValue)

Returns the value of the argument att the position of the 0-based

Declaration
protected double ArgToDecimal(object obj, out ExcelErrorValue error)
Parameters
Type Name Description
System.Object obj
ExcelErrorValue error

Will be set if the conversion generated an error
Returns
Type Description
System.Double

Value of the argument as a double.
Exceptions
Type Condition
ExcelErrorValueException

ArgToDecimal(Object, PrecisionAndRoundingStrategy, out ExcelErrorValue)

Returns the value of the argument att the position of the 0-based

Declaration
protected double ArgToDecimal(object obj, PrecisionAndRoundingStrategy precisionAndRoundingStrategy, out ExcelErrorValue error)
Parameters
Type Name Description
System.Object obj
PrecisionAndRoundingStrategy precisionAndRoundingStrategy

strategy for handling precision and rounding of double values
ExcelErrorValue error

An error type if the operation returns an error.
Returns
Type Description
System.Double

Value of the argument as a double.
Exceptions
Type Condition
ExcelErrorValueException

ArgToInt(IList<FunctionArgument>, Int32, out ExcelErrorValue, Int32)

Returns the value of the argument att the position of the 0-based index index as an integer.

Declaration
protected int ArgToInt(IList<FunctionArgument> arguments, int index, out ExcelErrorValue error, int emptyValue = 0)
Parameters
Type Name Description
System.Collections.Generic.IList<FunctionArgument> arguments
System.Int32 index
ExcelErrorValue error

If an error occurs during the conversion it will be returned via this parameter
System.Int32 emptyValue

Value returned if datatype is empty
Returns
Type Description
System.Int32

Value of the argument as an integer.
Exceptions
Type Condition
ExcelErrorValueException

ArgToInt(IList<FunctionArgument>, Int32, RoundingMethod)

Returns the value of the argument att the position of the 0-based index as an integer.

Declaration
protected int ArgToInt(IList<FunctionArgument> arguments, int index, RoundingMethod roundingMethod)
Parameters
Type Name Description
System.Collections.Generic.IList<FunctionArgument> arguments
System.Int32 index
RoundingMethod roundingMethod
Returns
Type Description
System.Int32

Value of the argument as an integer.
Exceptions
Type Condition
ExcelErrorValueException

ArgToInt(IList<FunctionArgument>, Int32, Boolean, out ExcelErrorValue)

Returns the value of the argument att the position of the 0-based index

Declaration
protected int ArgToInt(IList<FunctionArgument> arguments, int index, bool ignoreErrors, out ExcelErrorValue error)
Parameters
Type Name Description
System.Collections.Generic.IList<FunctionArgument> arguments
System.Int32 index
System.Boolean ignoreErrors

If true an Excel error in the cell will be ignored
ExcelErrorValue error

If an error occurs during the conversion it will be returned via this parameter
Returns
Type Description
System.Int32

Value of the argument as an integer.
Exceptions
Type Condition
ExcelErrorValueException

ArgToRangeInfo(IList<FunctionArgument>, Int32)

Declaration
protected IRangeInfo ArgToRangeInfo(IList<FunctionArgument> arguments, int index)
Parameters
Type Name Description
System.Collections.Generic.IList<FunctionArgument> arguments
System.Int32 index
Returns
Type Description
IRangeInfo

ArgToString(IList<FunctionArgument>, Int32)

Returns the value of the argument att the position of the 0-based index as a string.

Declaration
protected string ArgToString(IList<FunctionArgument> arguments, int index)
Parameters
Type Name Description
System.Collections.Generic.IList<FunctionArgument> arguments
System.Int32 index
Returns
Type Description
System.String

Value of the argument as a string.

CheckForAndHandleExcelError(FunctionArgument, out ExcelErrorValue)

if the supplied arg contains an Excel error an ExcelErrorValueException with that errorcode will be thrown

Declaration
protected void CheckForAndHandleExcelError(FunctionArgument arg, out ExcelErrorValue err)
Parameters
Type Name Description
FunctionArgument arg
ExcelErrorValue err

If the cell contains an error the error will be assigned to this variable

CheckForAndHandleExcelError(ICellInfo, out ExcelErrorValue)

If the supplied cell contains an Excel error an ExcelErrorValueException with that errorcode will be thrown

Declaration
protected void CheckForAndHandleExcelError(ICellInfo cell, out ExcelErrorValue err)
Parameters
Type Name Description
ICellInfo cell
ExcelErrorValue err

If the cell contains an error the error will be assigned to this variable

ConfigureArrayBehaviour(ArrayBehaviourConfig)

Configures parameters of a function that can be arrays (multi-cell ranges) even if the function itself treats them as single values.

Declaration
public virtual void ConfigureArrayBehaviour(ArrayBehaviourConfig config)
Parameters
Type Name Description
ArrayBehaviourConfig config

CreateAddressResult(IRangeInfo, DataType)

Use this method to create a result to return from Excel functions.

Declaration
protected CompileResult CreateAddressResult(IRangeInfo result, DataType dataType)
Parameters
Type Name Description
IRangeInfo result
DataType dataType
Returns
Type Description
CompileResult

CreateDynamicArrayResult(Object, DataType)

Use this method to create a result to return from Excel functions.

Declaration
protected CompileResult CreateDynamicArrayResult(object result, DataType dataType)
Parameters
Type Name Description
System.Object result
DataType dataType
Returns
Type Description
CompileResult

CreateDynamicArrayResult(Object, DataType, FormulaRangeAddress)

Use this method to create a result to return from Excel functions.

Declaration
protected CompileResult CreateDynamicArrayResult(object result, DataType dataType, FormulaRangeAddress address)
Parameters
Type Name Description
System.Object result
DataType dataType
FormulaRangeAddress address
Returns
Type Description
CompileResult

CreateResult(eErrorType)

Use this method to create a result to return from Excel functions.

Declaration
protected CompileResult CreateResult(eErrorType errorType)
Parameters
Type Name Description
eErrorType errorType
Returns
Type Description
CompileResult

CreateResult(Object, DataType)

Use this method to create a result to return from Excel functions.

Declaration
protected CompileResult CreateResult(object result, DataType dataType)
Parameters
Type Name Description
System.Object result
DataType dataType
Returns
Type Description
CompileResult

CreateResult(Object, DataType, FormulaRangeAddress)

Use this method to create a result to return from Excel functions.

Declaration
protected CompileResult CreateResult(object result, DataType dataType, FormulaRangeAddress address)
Parameters
Type Name Description
System.Object result
DataType dataType
FormulaRangeAddress address
Returns
Type Description
CompileResult

Divide(Double, Double)

Divides two numbers. If right is zero double.PositiveInfinity will be returned.

Declaration
protected static double Divide(double left, double right)
Parameters
Type Name Description
System.Double left

Numerator
System.Double right

Denominator
Returns
Type Description
System.Double

Execute(IList<FunctionArgument>, ParsingContext)

Declaration
public abstract CompileResult Execute(IList<FunctionArgument> arguments, ParsingContext context)
Parameters
Type Name Description
System.Collections.Generic.IList<FunctionArgument> arguments

Arguments to the function, each argument can contain primitive types, lists or Excel ranges
ParsingContext context

The ParsingContext contains various data that can be useful in functions.
Returns
Type Description
CompileResult

A CompileResult containing the calculated value

GetDecimalSingleArgument(FunctionArgument)

Returns the value as if the

Declaration
protected double? GetDecimalSingleArgument(FunctionArgument arg)
Parameters
Type Name Description
FunctionArgument arg
Returns
Type Description
System.Nullable<System.Double>

GetFirstValue(IEnumerable<FunctionArgument>)

Used for some Lookupfunctions to indicate that function arguments should not be compiled before the function is called.

Declaration
protected object GetFirstValue(IEnumerable<FunctionArgument> val)
Parameters
Type Name Description
System.Collections.Generic.IEnumerable<FunctionArgument> val
Returns
Type Description
System.Object

GetNewParameterAddress(IList<CompileResult>, Int32, ref Queue<FormulaRangeAddress>)

If overridden, this method will be called before the Execute(IList<FunctionArgument>, ParsingContext) method is called with the arguments for any parameter having ParametersInfo set to AdjustParameterAddress and that argument is a range with an address. It can be used to narrow the dependency check for the function returning a queue with addresses to check dependency before executing.

Declaration
public virtual void GetNewParameterAddress(IList<CompileResult> args, int index, ref Queue<FormulaRangeAddress> addresses)
Parameters
Type Name Description
System.Collections.Generic.IList<CompileResult> args

The function arguments that will be supplied to the execute method.
System.Int32 index

The index of the argument that should be adjusted.
System.Collections.Generic.Queue<FormulaRangeAddress> addresses

A queue of addresses that will be calculated before calling the Execute function.

GetResultByObject(Object)

Get result by object

Declaration
protected CompileResult GetResultByObject(object result)
Parameters
Type Name Description
System.Object result
Returns
Type Description
CompileResult

IsBool(Object)

Is bool

Declaration
protected bool IsBool(object val)
Parameters
Type Name Description
System.Object val
Returns
Type Description
System.Boolean

IsInteger(Object)

Returns true if the parameter n is an integer, otherwise false.

Declaration
protected bool IsInteger(object n)
Parameters
Type Name Description
System.Object n

The value to test
Returns
Type Description
System.Boolean

IsNumeric(Object)

Is numeric

Declaration
protected bool IsNumeric(object val)
Parameters
Type Name Description
System.Object val
Returns
Type Description
System.Boolean

IsNumericString(Object)

Returns true if the parameter value is a numeric string, otherwise false.

Declaration
protected bool IsNumericString(object value)
Parameters
Type Name Description
System.Object value

The value to test
Returns
Type Description
System.Boolean

IsString(Object, Boolean)

Is string

Declaration
protected bool IsString(object val, bool allowNullOrEmpty = true)
Parameters
Type Name Description
System.Object val
System.Boolean allowNullOrEmpty
Returns
Type Description
System.Boolean

ThrowArgumentExceptionIf(Func<Boolean>, String)

Throws an System.ArgumentException if condition evaluates to true.

Declaration
protected void ThrowArgumentExceptionIf(Func<bool> condition, string message)
Parameters
Type Name Description
System.Func<System.Boolean> condition
System.String message
Exceptions
Type Condition
System.ArgumentException

ThrowArgumentExceptionIf(Func<Boolean>, String, Object[])

Throws an System.ArgumentException if condition evaluates to true.

Declaration
protected void ThrowArgumentExceptionIf(Func<bool> condition, string message, params object[] formats)
Parameters
Type Name Description
System.Func<System.Boolean> condition
System.String message
System.Object[] formats

Formats to the message string.

ThrowExcelErrorValueException(eErrorType)

Throws an ExcelErrorValueException with the given errorType set.

Declaration
protected void ThrowExcelErrorValueException(eErrorType errorType)
Parameters
Type Name Description
eErrorType errorType

ThrowExcelErrorValueException(ExcelErrorValue)

Throws an ExcelErrorValueException with the type of given value set.

Declaration
protected void ThrowExcelErrorValueException(ExcelErrorValue value)
Parameters
Type Name Description
ExcelErrorValue value

ThrowExcelErrorValueExceptionIf(Func<Boolean>, eErrorType)

Throws an System.ArgumentException if condition evaluates to true.

Declaration
protected void ThrowExcelErrorValueExceptionIf(Func<bool> condition, eErrorType errorType)
Parameters
Type Name Description
System.Func<System.Boolean> condition
eErrorType errorType
Exceptions
Type Condition
ExcelErrorValueException

ValidateArguments(IEnumerable<FunctionArgument>, Int32)

This functions validates that the supplied arguments contains at least (the value of) minLength elements. If one of the arguments is an Excel range the number of cells in that range will be counted as well.

Declaration
[Obsolete("Don't use this method from EPPlus 7.x and up. Use property ArgumentMinLength instead.")]
protected void ValidateArguments(IEnumerable<FunctionArgument> arguments, int minLength)
Parameters
Type Name Description
System.Collections.Generic.IEnumerable<FunctionArgument> arguments
System.Int32 minLength
Exceptions
Type Condition
System.ArgumentException

ValidateArguments(IEnumerable<FunctionArgument>, Int32, eErrorType)

This functions validates that the supplied arguments contains at least (the value of) minLength elements. If one of the arguments is an Excel range the number of cells in that range will be counted as well.

Declaration
[Obsolete("Don't use this method from EPPlus 7.x and up. Use property ArgumentMinLength instead.")]
protected void ValidateArguments(IEnumerable<FunctionArgument> arguments, int minLength, eErrorType errorTypeToThrow)
Parameters
Type Name Description
System.Collections.Generic.IEnumerable<FunctionArgument> arguments
System.Int32 minLength
eErrorType errorTypeToThrow

The eErrorType of the ExcelErrorValueException that will be thrown if minLength is not met.