Class ExcelFunction¶

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

Base class for Excel function implementations.

public abstract class ExcelFunction

Inheritance¶

object ← ExcelFunction

Derived¶

ConvertFunction
Erf
HiddenValuesHandlingFunction
Text

Inherited Members¶

object.Equals(object?, object?)
object.Equals(object?)
object.GetHashCode()
object.GetType()
object.MemberwiseClone()
object.ReferenceEquals(object?, object?)
object.ToString()

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

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(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

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

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, 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

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

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(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

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(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>

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<object>

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(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

CreateResult(eErrorType)¶

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

protected CompileResult CreateResult(eErrorType errorType)

Parameters¶

errorType eErrorType

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 IRangeInfo?text=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(eErrorType)¶

Throws an ExcelErrorValueException with the given errorType set.

protected void ThrowExcelErrorValueException(eErrorType errorType)

Parameters¶

errorType eErrorType

ThrowExcelErrorValueException(ExcelErrorValue)¶

Throws an ExcelErrorValueException with the type of given value set.

protected void ThrowExcelErrorValueException(ExcelErrorValue value)

Parameters¶

value ExcelErrorValue

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, eErrorType)¶

This functions validates that the supplied arguments contains at least (the value of) minLength elements. If one of the arguments is an IRangeInfo?text=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.

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 IRangeInfo?text=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