Class ExcelFunction

Base class for Excel function implementations.

Inheritance

object

ExcelFunction

ConvertFunction

Erf

HiddenValuesHandlingFunction

Text

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.MemberwiseClone()

object.ReferenceEquals(object, object)

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

Declaration

public abstract int ArgumentMinLength { get; }

Property Value

Type	Description
int

ArrayBehaviour

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

Declaration

public virtual ExcelFunctionArrayBehaviour ArrayBehaviour { get; }

Property Value

Type	Description
ExcelFunctionArrayBehaviour

ExecutesLambda

Indicates if the function is Executing a LAMBDA.

Declaration

public virtual bool ExecutesLambda { get; }

Property Value

Type	Description
bool

HandlesVariables

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

Declaration

public virtual bool HandlesVariables { get; }

Property Value

Type	Description
bool

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
bool

IsErrorHandlingFunction

Indicates that the function is an ErrorHandlingFunction.

Declaration

public virtual bool IsErrorHandlingFunction { get; }

Property Value

Type	Description
bool

IsVolatile

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

Declaration

public virtual bool IsVolatile { get; }

Property Value

Type	Description
bool

NamespacePrefix

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

Declaration

public virtual string NamespacePrefix { get; }

Property Value

Type	Description
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
bool

Methods

AreEqual(double, double)

Helper method for comparison of two doubles.

Declaration

protected bool AreEqual(double d1, double d2)

Parameters

Type	Name	Description
double	d1
double	d2

Returns

Type	Description
bool

ArgToAddress(IList<FunctionArgument>, int)

Returns a string representation of an arguments address.

Declaration

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

Parameters

Type	Name	Description
IList<FunctionArgument>	arguments
int	index

Returns

Type	Description
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.

Declaration

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

Parameters

Type	Name	Description
IList<FunctionArgument>	arguments
int	index

Returns

Type	Description
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

Declaration

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

Parameters

Type	Name	Description
IList<FunctionArgument>	arguments
int	index
bool	valueIfEmpty

Returns

Type	Description
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.

Declaration

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

Parameters

Type	Name	Description
IList<FunctionArgument>	arguments
int	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
double	Value of the argument as an integer.

Exceptions

Type	Condition
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.

Declaration

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

Parameters

Type	Name	Description
IList<FunctionArgument>	arguments
int	index
double	valueIfNull
ExcelErrorValue	error	Will be set if an error occurs during conversion

Returns

Type	Description
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
object	obj
ExcelErrorValue	error	Will be set if the conversion generated an error

Returns

Type	Description
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
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
double	Value of the argument as a double.

Exceptions

Type	Condition
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.

Declaration

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

Parameters

Type	Name	Description
IList<FunctionArgument>	arguments
int	index
ExcelErrorValue	error	If an error occurs during the conversion it will be returned via this parameter
int	emptyValue	Value returned if datatype is empty

Returns

Type	Description
int	Value of the argument as an integer.

Exceptions

Type	Condition
ExcelErrorValueException

ArgToInt(IList<FunctionArgument>, int, 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
IList<FunctionArgument>	arguments
int	index
RoundingMethod	roundingMethod

Returns

Type	Description
int	Value of the argument as an integer.

Exceptions

Type	Condition
ExcelErrorValueException

ArgToInt(IList<FunctionArgument>, int, bool, 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
IList<FunctionArgument>	arguments
int	index
bool	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
int	Value of the argument as an integer.

Exceptions

Type	Condition
ExcelErrorValueException

ArgToRangeInfo(IList<FunctionArgument>, int)

Declaration

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

Parameters

Type	Name	Description
IList<FunctionArgument>	arguments
int	index

Returns

Type	Description
IRangeInfo

ArgToString(IList<FunctionArgument>, int)

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
IList<FunctionArgument>	arguments
int	index

Returns

Type	Description
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

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
IList<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
Action<DoubleEnumerableParseOptions>	configHandler
ExcelErrorValue	error

Returns

Type	Description
IList<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
IEnumerable<FunctionArgument>	arguments
ParsingContext	context
ExcelErrorValue	error

Returns

Type	Description
IList<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
IEnumerable<FunctionArgument>	arguments
ParsingContext	context
Action<DoubleEnumerableParseOptions>	configHandler
ExcelErrorValue	error

Returns

Type	Description
IList<double>

Exceptions

Type	Condition
ExcelErrorValueException

ArgsToObjectEnumerable(bool, bool, bool, 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
bool	ignoreHiddenCells	If a cell is hidden and this value is true the value of that cell will be ignored
bool	ignoreErrors
bool	ignoreNestedSubtotalAggregate
IEnumerable<FunctionArgument>	arguments
ParsingContext	context

Returns

Type	Description
IEnumerable<dynamic>

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
object	result
DataType	dataType

Returns

Type	Description
CompileResult

CreateDynamicArrayResult(object, DataType, CompileResultType)

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

Declaration

protected CompileResult CreateDynamicArrayResult(object result, DataType dataType, CompileResultType compileResultType)

Parameters

Type	Name	Description
object	result
DataType	dataType
CompileResultType	compileResultType

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
object	result
DataType	dataType
FormulaRangeAddress	address

Returns

Type	Description
CompileResult

CreateDynamicArrayResult(object, DataType, FormulaRangeAddress, CompileResultType)

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

Declaration

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

Parameters

Type	Name	Description
object	result
DataType	dataType
FormulaRangeAddress	address
CompileResultType	compileResultType

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
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
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
double	left	Numerator
double	right	Denominator

Returns

Type	Description
double

Execute(IList<FunctionArgument>, ParsingContext)

Declaration

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

Parameters

Type	Name	Description
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 static double? GetDecimalSingleArgument(FunctionArgument arg)

Parameters

Type	Name	Description
FunctionArgument	arg

Returns

Type	Description
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
IEnumerable<FunctionArgument>	val

Returns

Type	Description
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.

Declaration

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

Parameters

Type	Name	Description
IList<CompileResult>	args	The function arguments that will be supplied to the execute method.
int	index	The index of the argument that should be adjusted.
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
object	result

Returns

Type	Description
CompileResult

IsBool(object)

Is bool

Declaration

protected bool IsBool(object val)

Parameters

Type	Name	Description
object	val

Returns

Type	Description
bool

IsInteger(object)

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

Declaration

protected bool IsInteger(object n)

Parameters

Type	Name	Description
object	n	The value to test

Returns

Type	Description
bool

IsNumeric(object)

Is numeric

Declaration

protected bool IsNumeric(object val)

Parameters

Type	Name	Description
object	val

Returns

Type	Description
bool

IsNumericString(object)

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

Declaration

protected bool IsNumericString(object value)

Parameters

Type	Name	Description
object	value	The value to test

Returns

Type	Description
bool

IsString(object, bool)

Is string

Declaration

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

Parameters

Type	Name	Description
object	val
bool	allowNullOrEmpty

Returns

Type	Description
bool

ThrowArgumentExceptionIf(Func<bool>, string)

Throws an ArgumentException if condition evaluates to true.

Declaration

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

Parameters

Type	Name	Description
Func<bool>	condition
string	message

Exceptions

Type	Condition
ArgumentException

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

Throws an ArgumentException if condition evaluates to true.

Declaration

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

Parameters

Type	Name	Description
Func<bool>	condition
string	message
object[]	formats	Formats to the message string.

ThrowExcelErrorValueException(ExcelErrorValue)

Throws an ExcelErrorValueException with the type of given value set.

Declaration

protected void ThrowExcelErrorValueException(ExcelErrorValue value)

Parameters

Type	Name	Description
ExcelErrorValue	value

ThrowExcelErrorValueException(eErrorType)

Throws an ExcelErrorValueException with the given errorType set.

Declaration

protected void ThrowExcelErrorValueException(eErrorType errorType)

Parameters

Type	Name	Description
eErrorType	errorType

ThrowExcelErrorValueExceptionIf(Func<bool>, eErrorType)

Throws an ArgumentException if condition evaluates to true.

Declaration

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

Parameters

Type	Name	Description
Func<bool>	condition
eErrorType	errorType

Exceptions

Type	Condition
ExcelErrorValueException