Table of Contents

Class ExcelFunction

Namespace
OfficeOpenXml.FormulaParsing.Excel.Functions
Assembly
EPPlus.dll

Base class for Excel function implementations.

public abstract class ExcelFunction
Inheritance
ExcelFunction
Derived
Inherited Members

Constructors

ExcelFunction()

Default constructor

public ExcelFunction()

Fields

NumberOfSignificantFigures

Number of significant figures used in roundings, etc.

protected readonly int NumberOfSignificantFigures

Field Value

int

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.

public abstract int ArgumentMinLength { get; }

Property Value

int

ArrayBehaviour

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

public virtual ExcelFunctionArrayBehaviour ArrayBehaviour { get; }

Property Value

ExcelFunctionArrayBehaviour

HandlesVariables

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

public virtual bool HandlesVariables { get; }

Property Value

bool

IsAllowedInCalculatedPivotTableField

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

public virtual bool IsAllowedInCalculatedPivotTableField { get; }

Property Value

bool

IsErrorHandlingFunction

Indicates that the function is an ErrorHandlingFunction.

public virtual bool IsErrorHandlingFunction { get; }

Property Value

bool

IsVolatile

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

public virtual bool IsVolatile { get; }

Property Value

bool

NamespacePrefix

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

public virtual string NamespacePrefix { get; }

Property Value

string

Function argument information

ParametersInfo

Provides information about the functions parameters.

public virtual ExcelFunctionParametersInfo ParametersInfo { get; }

Property Value

ExcelFunctionParametersInfo

ReturnsReference

If the function returns a range reference

public virtual bool ReturnsReference { get; }

Property Value

bool

Methods

AreEqual(double, double)

Helper method for comparison of two doubles.

protected bool AreEqual(double d1, double d2)

Parameters

d1 double
d2 double

Returns

bool

ArgToAddress(IList<FunctionArgument>, int)

Returns a string representation of an arguments address.

protected string ArgToAddress(IList<FunctionArgument> arguments, int index)

Parameters

arguments IList<FunctionArgument>
index int

Returns

string

ArgToBool(IList<FunctionArgument>, int)

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.

protected bool ArgToBool(IList<FunctionArgument> arguments, int index)

Parameters

arguments IList<FunctionArgument>
index int

Returns

bool

ArgToBool(IList<FunctionArgument>, int, bool)

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

protected bool ArgToBool(IList<FunctionArgument> arguments, int index, bool valueIfEmpty)

Parameters

arguments IList<FunctionArgument>
index int
valueIfEmpty bool

Returns

bool

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

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

protected double ArgToDecimal(IList<FunctionArgument> arguments, int index, out ExcelErrorValue error, PrecisionAndRoundingStrategy precisionAndRoundingStrategy = PrecisionAndRoundingStrategy.DotNet)

Parameters

arguments IList<FunctionArgument>
index int
error ExcelErrorValue

Will be set if an error occurs during conversion

precisionAndRoundingStrategy PrecisionAndRoundingStrategy

strategy for handling precision and rounding of double values

Returns

double

Value of the argument as an integer.

Exceptions

ExcelErrorValueException

ArgToDecimal(IList<FunctionArgument>, int, double, out ExcelErrorValue)

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

protected double ArgToDecimal(IList<FunctionArgument> arguments, int index, double valueIfNull, out ExcelErrorValue error)

Parameters

arguments IList<FunctionArgument>
index int
valueIfNull double
error ExcelErrorValue

Will be set if an error occurs during conversion

Returns

double

Value of the argument as an integer.

Exceptions

ExcelErrorValueException

ArgToDecimal(object, out ExcelErrorValue)

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

protected double ArgToDecimal(object obj, out ExcelErrorValue error)

Parameters

obj object
error ExcelErrorValue

Will be set if the conversion generated an error

Returns

double

Value of the argument as a double.

Exceptions

ExcelErrorValueException

ArgToDecimal(object, PrecisionAndRoundingStrategy, out ExcelErrorValue)

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

protected double ArgToDecimal(object obj, PrecisionAndRoundingStrategy precisionAndRoundingStrategy, out ExcelErrorValue error)

Parameters

obj object
precisionAndRoundingStrategy PrecisionAndRoundingStrategy

strategy for handling precision and rounding of double values

error ExcelErrorValue

An error type if the operation returns an error.

Returns

double

Value of the argument as a double.

Exceptions

ExcelErrorValueException

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

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

protected int ArgToInt(IList<FunctionArgument> arguments, int index, out ExcelErrorValue error, int emptyValue = 0)

Parameters

arguments IList<FunctionArgument>
index int
error ExcelErrorValue

If an error occurs during the conversion it will be returned via this parameter

emptyValue int

Value returned if datatype is empty

Returns

int

Value of the argument as an integer.

Exceptions

ExcelErrorValueException

ArgToInt(IList<FunctionArgument>, int, RoundingMethod)

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

protected int ArgToInt(IList<FunctionArgument> arguments, int index, RoundingMethod roundingMethod)

Parameters

arguments IList<FunctionArgument>
index int
roundingMethod RoundingMethod

Returns

int

Value of the argument as an integer.

Exceptions

ExcelErrorValueException

ArgToInt(IList<FunctionArgument>, int, bool, out ExcelErrorValue)

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

protected int ArgToInt(IList<FunctionArgument> arguments, int index, bool ignoreErrors, out ExcelErrorValue error)

Parameters

arguments IList<FunctionArgument>
index int
ignoreErrors bool

If true an Excel error in the cell will be ignored

error ExcelErrorValue

If an error occurs during the conversion it will be returned via this parameter

Returns

int

Value of the argument as an integer.

Exceptions

ExcelErrorValueException

ArgToRangeInfo(IList<FunctionArgument>, int) 

protected IRangeInfo ArgToRangeInfo(IList<FunctionArgument> arguments, int index)

Parameters

arguments IList<FunctionArgument>
index int

Returns

IRangeInfo

ArgToString(IList<FunctionArgument>, int)

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

protected string ArgToString(IList<FunctionArgument> arguments, int index)

Parameters

arguments IList<FunctionArgument>
index int

Returns

string

Value of the argument as a string.

ArgsToDoubleEnumerable(FunctionArgument, ParsingContext, out ExcelErrorValue)

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

protected virtual IList<double> ArgsToDoubleEnumerable(FunctionArgument argument, ParsingContext context, out ExcelErrorValue error)

Parameters

argument FunctionArgument
context ParsingContext
error ExcelErrorValue

Returns

IList<double>

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

Will return the arguments as an enumerable of doubles.

protected virtual IList<double> ArgsToDoubleEnumerable(FunctionArgument argument, ParsingContext context, Action<DoubleEnumerableParseOptions> configHandler, out ExcelErrorValue error)

Parameters

argument FunctionArgument
context ParsingContext
configHandler Action<DoubleEnumerableParseOptions>
error ExcelErrorValue

Returns

IList<double>

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

Will return the arguments as an enumerable of doubles.

protected virtual IList<double> ArgsToDoubleEnumerable(IEnumerable<FunctionArgument> arguments, ParsingContext context, out ExcelErrorValue error)

Parameters

arguments IEnumerable<FunctionArgument>
context ParsingContext
error ExcelErrorValue

Returns

IList<double>

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

Will return the arguments as an enumerable of doubles.

protected virtual IList<double> ArgsToDoubleEnumerable(IEnumerable<FunctionArgument> arguments, ParsingContext context, Action<DoubleEnumerableParseOptions> configHandler, out ExcelErrorValue error)

Parameters

arguments IEnumerable<FunctionArgument>
context ParsingContext
configHandler Action<DoubleEnumerableParseOptions>
error ExcelErrorValue

Returns

IList<double>

Exceptions

ExcelErrorValueException

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

Will return the arguments as an enumerable of objects.

protected virtual IEnumerable<object> ArgsToObjectEnumerable(bool ignoreHiddenCells, bool ignoreErrors, bool ignoreNestedSubtotalAggregate, IEnumerable<FunctionArgument> arguments, ParsingContext context)

Parameters

ignoreHiddenCells bool

If a cell is hidden and this value is true the value of that cell will be ignored

ignoreErrors bool
ignoreNestedSubtotalAggregate bool
arguments IEnumerable<FunctionArgument>
context ParsingContext

Returns

IEnumerable<dynamic>

CheckForAndHandleExcelError(FunctionArgument, out ExcelErrorValue)

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

protected void CheckForAndHandleExcelError(FunctionArgument arg, out ExcelErrorValue err)

Parameters

arg FunctionArgument
err ExcelErrorValue

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

protected void CheckForAndHandleExcelError(ICellInfo cell, out ExcelErrorValue err)

Parameters

cell ICellInfo
err ExcelErrorValue

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.

public virtual void ConfigureArrayBehaviour(ArrayBehaviourConfig config)

Parameters

config ArrayBehaviourConfig

CreateAddressResult(IRangeInfo, DataType)

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

protected CompileResult CreateAddressResult(IRangeInfo result, DataType dataType)

Parameters

result IRangeInfo
dataType DataType

Returns

CompileResult

CreateDynamicArrayResult(object, DataType)

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

protected CompileResult CreateDynamicArrayResult(object result, DataType dataType)

Parameters

result object
dataType DataType

Returns

CompileResult

CreateDynamicArrayResult(object, DataType, FormulaRangeAddress)

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

protected CompileResult CreateDynamicArrayResult(object result, DataType dataType, FormulaRangeAddress address)

Parameters

result object
dataType DataType
address FormulaRangeAddress

Returns

CompileResult

CreateResult(eErrorType)

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

protected CompileResult CreateResult(eErrorType errorType)

Parameters

errorType eErrorType

Returns

CompileResult

CreateResult(object, DataType)

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

protected CompileResult CreateResult(object result, DataType dataType)

Parameters

result object
dataType DataType

Returns

CompileResult

CreateResult(object, DataType, FormulaRangeAddress)

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

protected CompileResult CreateResult(object result, DataType dataType, FormulaRangeAddress address)

Parameters

result object
dataType DataType
address FormulaRangeAddress

Returns

CompileResult

Divide(double, double)

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

protected static double Divide(double left, double right)

Parameters

left double

Numerator

right double

Denominator

Returns

double

Execute(IList<FunctionArgument>, ParsingContext) 

public abstract CompileResult Execute(IList<FunctionArgument> arguments, ParsingContext context)

Parameters

arguments IList<FunctionArgument>

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

context ParsingContext

The ParsingContext contains various data that can be useful in functions.

Returns

CompileResult

A CompileResult containing the calculated value

GetDecimalSingleArgument(FunctionArgument)

Returns the value as if the

protected double? GetDecimalSingleArgument(FunctionArgument arg)

Parameters

arg FunctionArgument

Returns

double?

GetFirstValue(IEnumerable<FunctionArgument>)

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

protected object GetFirstValue(IEnumerable<FunctionArgument> val)

Parameters

val IEnumerable<FunctionArgument>

Returns

object

GetNewParameterAddress(IList<CompileResult>, int, 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.

public virtual void GetNewParameterAddress(IList<CompileResult> args, int index, ref Queue<FormulaRangeAddress> addresses)

Parameters

args IList<CompileResult>

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

index int

The index of the argument that should be adjusted.

addresses Queue<FormulaRangeAddress>

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

GetResultByObject(object)

Get result by object

protected CompileResult GetResultByObject(object result)

Parameters

result object

Returns

CompileResult

IsBool(object)

Is bool

protected bool IsBool(object val)

Parameters

val object

Returns

bool

IsInteger(object)

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

protected bool IsInteger(object n)

Parameters

n object

The value to test

Returns

bool

IsNumeric(object)

Is numeric

protected bool IsNumeric(object val)

Parameters

val object

Returns

bool

IsNumericString(object)

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

protected bool IsNumericString(object value)

Parameters

value object

The value to test

Returns

bool

IsString(object, bool)

Is string

protected bool IsString(object val, bool allowNullOrEmpty = true)

Parameters

val object
allowNullOrEmpty bool

Returns

bool

ThrowArgumentExceptionIf(Func<bool>, string)

Throws an ArgumentException if condition evaluates to true.

protected void ThrowArgumentExceptionIf(Func<bool> condition, string message)

Parameters

condition Func<bool>
message string

Exceptions

ArgumentException

ThrowArgumentExceptionIf(Func<bool>, string, params object[])

Throws an ArgumentException if condition evaluates to true.

protected void ThrowArgumentExceptionIf(Func<bool> condition, string message, params object[] formats)

Parameters

condition Func<bool>
message string
formats object[]

Formats to the message string.

ThrowExcelErrorValueException(ExcelErrorValue)

Throws an ExcelErrorValueException with the type of given value set.

protected void ThrowExcelErrorValueException(ExcelErrorValue value)

Parameters

value ExcelErrorValue

ThrowExcelErrorValueException(eErrorType)

Throws an ExcelErrorValueException with the given errorType set.

protected void ThrowExcelErrorValueException(eErrorType errorType)

Parameters

errorType eErrorType

ThrowExcelErrorValueExceptionIf(Func<bool>, eErrorType)

Throws an ArgumentException if condition evaluates to true.

protected void ThrowExcelErrorValueExceptionIf(Func<bool> condition, eErrorType errorType)

Parameters

condition Func<bool>
errorType eErrorType

Exceptions

ExcelErrorValueException

ValidateArguments(IEnumerable<FunctionArgument>, int)

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.

[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

arguments IEnumerable<FunctionArgument>
minLength int

Exceptions

ArgumentException

ValidateArguments(IEnumerable<FunctionArgument>, int, 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.

[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

arguments IEnumerable<FunctionArgument>
minLength int
errorTypeToThrow eErrorType

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