Class ExcelFunction

Base class for Excel function implementations.

Inheritance

System.Object

ExcelFunction

ConvertFunction

Erf

ErrorHandlingFunction

HiddenValuesHandlingFunction

IfsWithMultipleMatchesBase

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

Declaration

public ExcelFunction()

ExcelFunction(ArgumentCollectionUtil, ArgumentParsers, CompileResultValidators)

Declaration

public ExcelFunction(ArgumentCollectionUtil argumentCollectionUtil, ArgumentParsers argumentParsers, CompileResultValidators compileResultValidators)

Parameters

Type	Name	Description
ArgumentCollectionUtil	argumentCollectionUtil
ArgumentParsers	argumentParsers
CompileResultValidators	compileResultValidators

Fields

_argumentParsers

Declaration

protected readonly ArgumentParsers _argumentParsers

Field Value

Type	Description
ArgumentParsers

NumberOfSignificantFigures

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

IsErrorHandlingFunction

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(Boolean, Boolean, Boolean, IEnumerable<FunctionArgument>, ParsingContext)

Will return the arguments as an enumerable of doubles.

Declaration

protected virtual IEnumerable<ExcelDoubleCellValue> ArgsToDoubleEnumerable(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	If a cell contains an error, that error will be ignored if this method is set to true
System.Boolean	ignoreNestedSubtotalAggregate	If cells which value comes from the calculation of a SUBTOTAL or an AGGREGATE function should be ignored, set this to true
System.Collections.Generic.IEnumerable<FunctionArgument>	arguments
ParsingContext	context

Returns

Type	Description
System.Collections.Generic.IEnumerable<ExcelDoubleCellValue>

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

Will return the arguments as an enumerable of doubles.

Declaration

protected virtual IEnumerable<ExcelDoubleCellValue> ArgsToDoubleEnumerable(bool ignoreHiddenCells, bool ignoreErrors, bool ignoreNestedSubtotalAggregate, IEnumerable<FunctionArgument> arguments, ParsingContext context, bool ignoreNonNumeric)

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	If a cell contains an error, that error will be ignored if this method is set to true
System.Boolean	ignoreNestedSubtotalAggregate	If cells which value comes from the calculation of a SUBTOTAL or an AGGREGATE function should be ignored, set this to true
System.Collections.Generic.IEnumerable<FunctionArgument>	arguments
ParsingContext	context
System.Boolean	ignoreNonNumeric

Returns

Type	Description
System.Collections.Generic.IEnumerable<ExcelDoubleCellValue>

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

Will return the arguments as an enumerable of doubles.

Declaration

protected virtual IEnumerable<ExcelDoubleCellValue> ArgsToDoubleEnumerable(bool ignoreHiddenCells, bool ignoreErrors, 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	If a cell contains an error, that error will be ignored if this method is set to true
System.Collections.Generic.IEnumerable<FunctionArgument>	arguments
ParsingContext	context

Returns

Type	Description
System.Collections.Generic.IEnumerable<ExcelDoubleCellValue>

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

Will return the arguments as an enumerable of doubles.

Declaration

protected virtual IEnumerable<ExcelDoubleCellValue> ArgsToDoubleEnumerable(bool ignoreHiddenCells, bool ignoreNestedSubtotalAggregate, IEnumerable<FunctionArgument> arguments, ParsingContext context, bool ignoreNonNumeric)

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	ignoreNestedSubtotalAggregate	If cells which value comes from the calculation of a SUBTOTAL or an AGGREGATE function should be ignored, set this to true
System.Collections.Generic.IEnumerable<FunctionArgument>	arguments
ParsingContext	context
System.Boolean	ignoreNonNumeric

Returns

Type	Description
System.Collections.Generic.IEnumerable<ExcelDoubleCellValue>

ArgsToDoubleEnumerable(Boolean, IEnumerable<FunctionArgument>, ParsingContext)

Will return the arguments as an enumerable of doubles.

Declaration

protected virtual IEnumerable<ExcelDoubleCellValue> ArgsToDoubleEnumerable(bool ignoreHiddenCells, 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.Collections.Generic.IEnumerable<FunctionArgument>	arguments
ParsingContext	context

Returns

Type	Description
System.Collections.Generic.IEnumerable<ExcelDoubleCellValue>

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

Will return the arguments as an enumerable of doubles.

Declaration

protected virtual IEnumerable<ExcelDoubleCellValue> ArgsToDoubleEnumerable(bool ignoreHiddenCells, IEnumerable<FunctionArgument> arguments, ParsingContext context, bool ignoreNonNumeric)

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.Collections.Generic.IEnumerable<FunctionArgument>	arguments
ParsingContext	context
System.Boolean	ignoreNonNumeric

Returns

Type	Description
System.Collections.Generic.IEnumerable<ExcelDoubleCellValue>

ArgsToDoubleEnumerable(IEnumerable<FunctionArgument>, ParsingContext)

Will return the arguments as an enumerable of doubles.

Declaration

protected virtual IEnumerable<ExcelDoubleCellValue> ArgsToDoubleEnumerable(IEnumerable<FunctionArgument> arguments, ParsingContext context)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<FunctionArgument>	arguments
ParsingContext	context

Returns

Type	Description
System.Collections.Generic.IEnumerable<ExcelDoubleCellValue>

ArgsToDoubleEnumerableZeroPadded(Boolean, IRangeInfo, ParsingContext)

Declaration

protected virtual IEnumerable<double> ArgsToDoubleEnumerableZeroPadded(bool ignoreHiddenCells, IRangeInfo rangeInfo, ParsingContext context)

Parameters

Type	Name	Description
System.Boolean	ignoreHiddenCells
IRangeInfo	rangeInfo
ParsingContext	context

Returns

Type	Description
System.Collections.Generic.IEnumerable<System.Double>

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)

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

ArgToDecimal(IList<FunctionArgument>, Int32, 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, PrecisionAndRoundingStrategy precisionAndRoundingStrategy = PrecisionAndRoundingStrategy.DotNet)

Parameters

Type	Name	Description
System.Collections.Generic.IList<FunctionArgument>	arguments
System.Int32	index
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)

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)

Parameters

Type	Name	Description
System.Collections.Generic.IList<FunctionArgument>	arguments
System.Int32	index
System.Double	valueIfNull

Returns

Type	Description
System.Double	Value of the argument as an integer.

Exceptions

Type	Condition
ExcelErrorValueException

ArgToDecimal(Object)

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

Declaration

protected double ArgToDecimal(object obj)

Parameters

Type	Name	Description
System.Object	obj

Returns

Type	Description
System.Double	Value of the argument as a double.

Exceptions

Type	Condition
ExcelErrorValueException

ArgToDecimal(Object, PrecisionAndRoundingStrategy)

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

Declaration

protected double ArgToDecimal(object obj, PrecisionAndRoundingStrategy precisionAndRoundingStrategy)

Parameters

Type	Name	Description
System.Object	obj
PrecisionAndRoundingStrategy	precisionAndRoundingStrategy	strategy for handling precision and rounding of double values

Returns

Type	Description
System.Double	Value of the argument as a double.

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)

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)

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

Returns

Type	Description
System.Int32	Value of the argument as an integer.

Exceptions

Type	Condition
ExcelErrorValueException

ArgToInt(IList<FunctionArgument>, Int32, 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, int emptyValue = 0)

Parameters

Type	Name	Description
System.Collections.Generic.IList<FunctionArgument>	arguments
System.Int32	index
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

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.

BeforeInvoke(ParsingContext)

If overridden, this method is called before Execute is called.

Declaration

public virtual void BeforeInvoke(ParsingContext context)

Parameters

Type	Name	Description
ParsingContext	context

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)

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)

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)

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)

Declaration

protected double Divide(double left, double right)

Parameters

Type	Name	Description
System.Double	left
System.Double	right

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

Declaration

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

Parameters

Type	Name	Description
System.Collections.Generic.IList<CompileResult>	args
System.Int32	index
System.Collections.Generic.Queue<FormulaRangeAddress>	addresses

GetResultByObject(Object)

Declaration

protected CompileResult GetResultByObject(object result)

Parameters

Type	Name	Description
System.Object	result

Returns

Type	Description
CompileResult

IsBool(Object)

Declaration

protected bool IsBool(object val)

Parameters

Type	Name	Description
System.Object	val

Returns

Type	Description
System.Boolean

IsInteger(Object)

Declaration

protected bool IsInteger(object n)

Parameters

Type	Name	Description
System.Object	n

Returns

Type	Description
System.Boolean

IsNumeric(Object)

Declaration

protected bool IsNumeric(object val)

Parameters

Type	Name	Description
System.Object	val

Returns

Type	Description
System.Boolean

IsNumericString(Object)

Declaration

protected bool IsNumericString(object value)

Parameters

Type	Name	Description
System.Object	value

Returns

Type	Description
System.Boolean

IsString(Object, Boolean)

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

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

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.