Class FormulaUtil

This class provides functionality needed for formula parsing.

Inheritance
System.Object
CommonObject
FormulaUtil
Implements
System.IDisposable
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: Syncfusion.XlsIO.Implementation
Assembly: Syncfusion.XlsIO.Base.dll
Syntax
public class FormulaUtil : CommonObject, IParentApplication, IDisposable

Constructors

FormulaUtil(IApplication, Object)

Initializes new instance of the formula parsing utility.

Declaration
public FormulaUtil(IApplication application, object parent)
Parameters
Type Name Description
IApplication application

Application object.
System.Object parent

Parent object.

FormulaUtil(IApplication, Object, NumberFormatInfo, Char, Char)

Initializes new instance of the formula parsing utility.

Declaration
public FormulaUtil(IApplication application, object parent, NumberFormatInfo numberFormat, char chArgumentsSeparator, char chRowSeparator)
Parameters
Type Name Description
IApplication application

Application object.
System.Object parent

Parent object.
System.Globalization.NumberFormatInfo numberFormat

Number format information.
System.Char chArgumentsSeparator

Argument separator.
System.Char chRowSeparator

Row separator.

Fields

Cell3DRegex

Regular expression for 3d reference detection.

Declaration
public static readonly Regex Cell3DRegex
Field Value
Type
System.Text.RegularExpressions.Regex

CellR1C13DRegex

Regular expression for 3d reference detection in R1C1 notation.

Declaration
public static readonly Regex CellR1C13DRegex
Field Value
Type
System.Text.RegularExpressions.Regex

CellR1C1Regex

Regular expression for checking if specified string is cell reference.

Declaration
public static readonly Regex CellR1C1Regex
Field Value
Type
System.Text.RegularExpressions.Regex

CellRange3DRegex

Regular expression for 3d cell range detection.

Declaration
public static readonly Regex CellRange3DRegex
Field Value
Type
System.Text.RegularExpressions.Regex

CellRange3DRegex2

Regular expression for 3d cell range detection second possible case.

Declaration
public static readonly Regex CellRange3DRegex2
Field Value
Type
System.Text.RegularExpressions.Regex

CellRangeR1C13DRegex

Regular expression for 3d cell range detection.

Declaration
public static readonly Regex CellRangeR1C13DRegex
Field Value
Type
System.Text.RegularExpressions.Regex

CellRangeR1C13DRegex2

Regular expression for 3d cell range detection second possible case.

Declaration
public static readonly Regex CellRangeR1C13DRegex2
Field Value
Type
System.Text.RegularExpressions.Regex

CellRangeR1C13DShortRegex

Regular expression for checking if specified string is cell range in R1C1 notation.

Declaration
public static readonly Regex CellRangeR1C13DShortRegex
Field Value
Type
System.Text.RegularExpressions.Regex

CellRangeR1C1Regex

Regular expression for checking if specified string is cell range in R1C1 notation.

Declaration
public static readonly Regex CellRangeR1C1Regex
Field Value
Type
System.Text.RegularExpressions.Regex

CellRangeR1C1ShortRegex

Regular expression for checking if specified string is cell range in R1C1 notation.

Declaration
public static readonly Regex CellRangeR1C1ShortRegex
Field Value
Type
System.Text.RegularExpressions.Regex

CellRangeRegex

Regular expression for checking if specified string is cell range.

Declaration
public static readonly Regex CellRangeRegex
Field Value
Type
System.Text.RegularExpressions.Regex

CellReferenceRegex

Regular expression for checking if specified string is cell reference.

Declaration
public static readonly Regex CellReferenceRegex
Field Value
Type
System.Text.RegularExpressions.Regex

CellRegex

Regular expression for checking if specified string is cell reference.

Declaration
public static readonly Regex CellRegex
Field Value
Type
System.Text.RegularExpressions.Regex

CloseBrackets

T corresponding close brackets.

Declaration
public static char[] CloseBrackets
Field Value
Type
System.Char[]

DEF_ARRAY_INDEX

Default reference index for array token.

Declaration
public const int DEF_ARRAY_INDEX = 2
Field Value
Type
System.Int32

DEF_BOOKNAME_GROUP

Name of the book name group.

Declaration
public const string DEF_BOOKNAME_GROUP = "BookName"
Field Value
Type
System.String

DEF_COLUMN_GROUP

Name of the first column name group.

Declaration
public const string DEF_COLUMN_GROUP = "Column1"
Field Value
Type
System.String

DEF_GROUP_COLUMN1

Column of the first cell.

Declaration
public const string DEF_GROUP_COLUMN1 = "Column1"
Field Value
Type
System.String

DEF_GROUP_COLUMN2

Column of the second cell.

Declaration
public const string DEF_GROUP_COLUMN2 = "Column2"
Field Value
Type
System.String

DEF_GROUP_ROW1

Row of the first cell.

Declaration
public const string DEF_GROUP_ROW1 = "Row1"
Field Value
Type
System.String

DEF_GROUP_ROW2

Row of the second cell.

Declaration
public const string DEF_GROUP_ROW2 = "Row2"
Field Value
Type
System.String

DEF_NAME_INDEX

Default index inside named range formula.

Declaration
public const int DEF_NAME_INDEX = 1
Field Value
Type
System.Int32

DEF_PATH_GROUP

Name of the path group.

Declaration
public const string DEF_PATH_GROUP = "Path"
Field Value
Type
System.String

DEF_RANGENAME_GROUP

Name of the range name group.

Declaration
public const string DEF_RANGENAME_GROUP = "RangeName"
Field Value
Type
System.String

DEF_REFERENCE_INDEX

Default reference index.

Declaration
public const int DEF_REFERENCE_INDEX = 2
Field Value
Type
System.Int32

DEF_ROW_GROUP

Name of the first row name group.

Declaration
public const string DEF_ROW_GROUP = "Row1"
Field Value
Type
System.String

DEF_SHEETNAME_GROUP

Name of the sheet name group.

Declaration
public const string DEF_SHEETNAME_GROUP = "SheetName"
Field Value
Type
System.String

ErrorNameToConstructor

Provides access to constructor that takes one string argument by name of the error.

Declaration
public static Dictionary<string, ConstructorInfo> ErrorNameToConstructor
Field Value
Type
System.Collections.Generic.Dictionary<System.String, System.Reflection.ConstructorInfo>

Excel2010FunctionPrefix

Represents the Excel2010 function prefix

Declaration
public const string Excel2010FunctionPrefix = "_xlfn."
Field Value
Type
System.String

Full3DColumnRangeRegex

Regular expression for checking if specified string is 3D full column range.

Declaration
public static readonly Regex Full3DColumnRangeRegex
Field Value
Type
System.Text.RegularExpressions.Regex

Full3DRowRangeRegex

Regular expression for checking if specified string is 3D full row range.

Declaration
public static readonly Regex Full3DRowRangeRegex
Field Value
Type
System.Text.RegularExpressions.Regex

FullColumnRangeR1C1Regex

Regular expression for checking if specified string is full column range in R1C1 notation.

Declaration
public static readonly Regex FullColumnRangeR1C1Regex
Field Value
Type
System.Text.RegularExpressions.Regex

FullColumnRangeRegex

Regular expression for checking if specified string is full column range.

Declaration
public static readonly Regex FullColumnRangeRegex
Field Value
Type
System.Text.RegularExpressions.Regex

FullRowRangeR1C1Regex

Regular expression for checking if specified string is full row range in R1C1 notation

Declaration
public static readonly Regex FullRowRangeR1C1Regex
Field Value
Type
System.Text.RegularExpressions.Regex

FullRowRangeRegex

Regular expression for checking if specified string is full row range.

Declaration
public static readonly Regex FullRowRangeRegex
Field Value
Type
System.Text.RegularExpressions.Regex

FunctionAliasToId

Provides access to function id by its name.

Declaration
public static Dictionary<string, ExcelFunction> FunctionAliasToId
Field Value
Type
System.Collections.Generic.Dictionary<System.String, ExcelFunction>

FunctionIdToAlias

Gives access to the function name by its id.

Declaration
public static Dictionary<ExcelFunction, string> FunctionIdToAlias
Field Value
Type
System.Collections.Generic.Dictionary<ExcelFunction, System.String>

FunctionIdToIndex

Provides access to Dictionary that contains all Reference index by type of the argument token.

Declaration
public static Dictionary<ExcelFunction, Dictionary<Type, ReferenceIndexAttribute>> FunctionIdToIndex
Field Value
Type
System.Collections.Generic.Dictionary<ExcelFunction, System.Collections.Generic.Dictionary<System.Type, ReferenceIndexAttribute>>

FunctionIdToParamCount

Gives access to the number of parameter needed by the function.

Declaration
public static Dictionary<ExcelFunction, int> FunctionIdToParamCount
Field Value
Type
System.Collections.Generic.Dictionary<ExcelFunction, System.Int32>

NameCodes

Declaration
public static FormulaToken[] NameCodes
Field Value
Type
FormulaToken[]

NameXCodes

Declaration
public static FormulaToken[] NameXCodes
Field Value
Type
FormulaToken[]

OpenBrackets

Array of all open brackets.

Declaration
public static char[] OpenBrackets
Field Value
Type
System.Char[]

PlusMinusArray

Plus and minus signs.

Declaration
public static string[] PlusMinusArray
Field Value
Type
System.String[]

StringBrackets

String 'brackets'.

Declaration
public static char[] StringBrackets
Field Value
Type
System.Char[]

UnaryOperations

All known unary operations.

Declaration
public static string[] UnaryOperations
Field Value
Type
System.String[]

Properties

ArrayRowSeparator

Returns array row separator. Read-only.

Declaration
public string ArrayRowSeparator { get; }
Property Value
Type
System.String

ErrorCodeToName

Returns IDictionary error code - to - name. Read-only.

Declaration
public static Dictionary<int, string> ErrorCodeToName { get; }
Property Value
Type
System.Collections.Generic.Dictionary<System.Int32, System.String>

ErrorNameToCode

Returns IDictionary error name - to - error code. Read-only.

Declaration
public static Dictionary<string, int> ErrorNameToCode { get; }
Property Value
Type
System.Collections.Generic.Dictionary<System.String, System.Int32>

NumberFormat

Gets or sets number format for parsing double value.

Declaration
public NumberFormatInfo NumberFormat { get; set; }
Property Value
Type
System.Globalization.NumberFormatInfo

OperandsSeparator

Returns operands separator. Read-only.

Declaration
public string OperandsSeparator { get; }
Property Value
Type
System.String

ParentWorkbook

Declaration
public IWorkbook ParentWorkbook { get; }
Property Value
Type
IWorkbook

Methods

CheckFormulaVersion(Ptg[])

Checks the formula version.

Declaration
public void CheckFormulaVersion(Ptg[] ptgs)
Parameters
Type Name Description
Ptg[] ptgs

The PTGS.

ConvertSharedFormulaTokens(SharedFormulaRecord, IWorkbook, Int32, Int32)

Converts SharedFormula tokens into regular formula tokens.

Declaration
[CLSCompliant(false)]
public static Ptg[] ConvertSharedFormulaTokens(SharedFormulaRecord shared, IWorkbook book, int iRow, int iColumn)
Parameters
Type Name Description
SharedFormulaRecord shared

Shared formula to convert.
IWorkbook book

Parent workbook.
System.Int32 iRow

Row of the destination formula.
System.Int32 iColumn

Column of the destination formula.
Returns
Type Description
Ptg[]

Converted token array.

ConvertTokensToShared(Ptg[], Int32, Int32, IWorkbook)

Converts to shared formula tokens

Declaration
public Ptg[] ConvertTokensToShared(Ptg[] tokens, int row, int column, IWorkbook book)
Parameters
Type Name Description
Ptg[] tokens
System.Int32 row
System.Int32 column
IWorkbook book
Returns
Type
Ptg[]

CreateError(String, Int32)

Creates token that describes specified error.

Declaration
public static Ptg CreateError(string strFormula, int errorIndex)
Parameters
Type Name Description
System.String strFormula

Formula string that contains error string.
System.Int32 errorIndex

Index of the error name.
Returns
Type Description
Ptg

Created token that contains error.

CreatePtg(DataProvider, ref Int32, ExcelVersion)

Creates new Ptg and sets offset to point just after its data.

Declaration
public static Ptg CreatePtg(DataProvider provider, ref int offset, ExcelVersion version)
Parameters
Type Name Description
DataProvider provider

Object that provides access to the data.
System.Int32 offset

Offset to the token data.
ExcelVersion version

Excel version that was used to infill data provider.
Returns
Type Description
Ptg

Parsed Ptg token.
Exceptions
Type Condition
System.ArgumentOutOfRangeException

When data array is smaller than token that should be stored in it.

CreatePtg(FormulaToken)

Creates formula token using token code.

Declaration
public static Ptg CreatePtg(FormulaToken token)
Parameters
Type Name Description
FormulaToken token

Token code.
Returns
Type Description
Ptg

Newly created token.

CreatePtg(FormulaToken, ExcelFunction)

Creates specified token, passes function index to its constructor.

Declaration
[CLSCompliant(false)]
public static Ptg CreatePtg(FormulaToken token, ExcelFunction functionIndex)
Parameters
Type Name Description
FormulaToken token

Token to create.
ExcelFunction functionIndex

Function index.
Returns
Type Description
Ptg

Newly created token.

CreatePtg(FormulaToken, Int32, Int32, Int32, String, String, String, String, Boolean, IWorkbook)

Creates token using two string values.

Declaration
public static Ptg CreatePtg(FormulaToken token, int iCellRow, int iCellColumn, int iRefIndex, string strParam1, string strParam2, string strParam3, string strParam4, bool bR1C1, IWorkbook book)
Parameters
Type Name Description
FormulaToken token

Token to create.
System.Int32 iCellRow

Row index of the cell that contains formula to parse.
System.Int32 iCellColumn

Column index of the cell that contains formula to parse.
System.Int32 iRefIndex

Worksheet reference index.
System.String strParam1

First value.
System.String strParam2

Second value.
System.String strParam3

Third value.
System.String strParam4

Fourth value.
System.Boolean bR1C1

Indicates whether R1C1 notation is used.
IWorkbook book

Parent workbook.
Returns
Type Description
Ptg

Created formula token.

CreatePtg(FormulaToken, Int32, Int32, String, String, Boolean)

Creates token using two string values.

Declaration
public static Ptg CreatePtg(FormulaToken token, int iCellRow, int iCellColumn, string strParam1, string strParam2, bool bR1C1)
Parameters
Type Name Description
FormulaToken token

Token to create.
System.Int32 iCellRow

Row index of the cell that contains formula to parse.
System.Int32 iCellColumn

Column index of the cell that contains formula to parse.
System.String strParam1

First value.
System.String strParam2

Second value.
System.Boolean bR1C1

Indicates whether R1C1 notation is used.
Returns
Type Description
Ptg

Created formula token.

CreatePtg(FormulaToken, Int32, Int32, String, String, String, String, Boolean, IWorkbook)

Creates token using two string values.

Declaration
public static Ptg CreatePtg(FormulaToken token, int iCellRow, int iCellColumn, string strParam1, string strParam2, string strParam3, string strParam4, bool bR1C1, IWorkbook book)
Parameters
Type Name Description
FormulaToken token

Token to create.
System.Int32 iCellRow

Row index of the cell that contains formula to parse.
System.Int32 iCellColumn

Column index of the cell that contains formula to parse.
System.String strParam1

First value.
System.String strParam2

Second value.
System.String strParam3

Third value.
System.String strParam4

Fourth value.
System.Boolean bR1C1

Indicates whether R1C1 notation is used.
IWorkbook book

Parent workbook.
Returns
Type Description
Ptg

Created formula token.

CreatePtg(FormulaToken, Object[])

Creates specified token, passes specified parameters to its constructor.

Declaration
public static Ptg CreatePtg(FormulaToken token, params object[] arrParams)
Parameters
Type Name Description
FormulaToken token

Code of the token that will be created.
System.Object[] arrParams

Constructor parameters.
Returns
Type Description
Ptg

Newly created token.

CreatePtg(FormulaToken, String)

Creates token using token id and string representing this token.

Declaration
public static Ptg CreatePtg(FormulaToken token, string tokenString)
Parameters
Type Name Description
FormulaToken token

Token id of the token that should be created.
System.String tokenString

String that will be passed to the token constructor.
Returns
Type Description
Ptg

Newly created token.

CreatePtg(FormulaToken, String, IWorkbook)

Creates token using token code, token string, and parent workbook.

Declaration
public static Ptg CreatePtg(FormulaToken token, string tokenString, IWorkbook parent)
Parameters
Type Name Description
FormulaToken token

Code of the new token.
System.String tokenString

String representation of the token.
IWorkbook parent

Parent workbook.
Returns
Type Description
Ptg

Newly created token.

CreatePtg(FormulaToken, UInt16, UInt16)

Creates specified token, passes two integers to its constructor.

Declaration
[CLSCompliant(false)]
public static Ptg CreatePtg(FormulaToken token, ushort iParam1, ushort iParam2)
Parameters
Type Name Description
FormulaToken token

Token to create.
System.UInt16 iParam1

First integer argument.
System.UInt16 iParam2

Second integer argument.
Returns
Type Description
Ptg

Newly created token.

CreatePtgByType(FormulaToken)

Creates formula token using token code.

Declaration
public static Ptg CreatePtgByType(FormulaToken token)
Parameters
Type Name Description
FormulaToken token

Token code that is used to get constructor and as constructor argument as well.
Returns
Type Description
Ptg

Newly created token.

EditRegisteredFunction(String, ExcelFunction, ReferenceIndexAttribute[], Int32)

Registers function in internal collections.

Declaration
[CLSCompliant(false)]
public static void EditRegisteredFunction(string functionName, ExcelFunction index, ReferenceIndexAttribute[] paramIndexes, int paramCount)
Parameters
Type Name Description
System.String functionName

Name of the function that must be registered.
ExcelFunction index

Index of the function that must be registered.
ReferenceIndexAttribute[] paramIndexes

Array of ReferenceIndexAttribute that contains information about proper token index.
System.Int32 paramCount

Number of parameters in the function, -1, for variable parameters.

FindCorrespondingBracket(String, Int32)

Searches for position of corresponding bracket.

Declaration
public static int FindCorrespondingBracket(string strFormula, int BracketPos)
Parameters
Type Name Description
System.String strFormula

String to search.
System.Int32 BracketPos

Position of bracket( "(){}" ).
Returns
Type Description
System.Int32

Position of corresponding bracket if there is one, otherwise -1.
Exceptions
Type Condition
System.ArgumentOutOfRangeException

When specified position does not contain bracket.

GetFunctionOperand(String, Int32)

Extracts function operand from formula string.

Declaration
public string GetFunctionOperand(string strFormula, int iFirstChar)
Parameters
Type Name Description
System.String strFormula

Formula string to extract operand from.
System.Int32 iFirstChar

First character of the operand.
Returns
Type Description
System.String

Extracted operand.

GetIndex(Type, Int32, Dictionary<Type, ReferenceIndexAttribute>, Int32, ExcelParseFormulaOptions)

Returns index of the token that should be used at position i by type targetType in function call.

Declaration
public static int GetIndex(Type targetType, int valueType, Dictionary<Type, ReferenceIndexAttribute> indexes, int i, ExcelParseFormulaOptions options)
Parameters
Type Name Description
System.Type targetType

Target type for which index will be searched.
System.Int32 valueType
System.Collections.Generic.Dictionary<System.Type, ReferenceIndexAttribute> indexes

Dictionary with indexes.
System.Int32 i

Position of the function parameter.
ExcelParseFormulaOptions options

Parse options.
Returns
Type Description
System.Int32

Index of the token code.

GetLeftBinaryOperand(String, Int32)

Returns left operand for binary operation.

Declaration
public static string GetLeftBinaryOperand(string strFormula, int OpIndex)
Parameters
Type Name Description
System.String strFormula

Formula string.
System.Int32 OpIndex

Index of operation.
Returns
Type Description
System.String

Left operand for specified operation.

GetLeftUnaryOperand(String, Int32)

Returns operand that is placed before unary operation.

Declaration
public string GetLeftUnaryOperand(string strFormula, int OpIndex)
Parameters
Type Name Description
System.String strFormula

Formula string that contains operand and unary operation.
System.Int32 OpIndex

Index of unary operation in the string.
Returns
Type Description
System.String

Left operand of the specified unary operation.

GetOperand(String, Int32, SortedList, Boolean)

Returns operand of the operation.

Declaration
public static string GetOperand(string strFormula, int OpIndex, SortedList arrBreakStrings, bool IsLeft)
Parameters
Type Name Description
System.String strFormula

Formula string that contains operation and operands.
System.Int32 OpIndex

Index of the operation.
System.Collections.SortedList arrBreakStrings

Delimiters between operands.
System.Boolean IsLeft

Search direction (TRUE -right to left, FALSE - left to right).
Returns
Type Description
System.String

Operand defined by function parameters.
Exceptions
Type Condition
System.ArgumentException

When a open bracket is found without a corresponding closing bracket.

GetRightBinaryOperand(String, Int32, String)

Returns right operand for binary operation.

Declaration
public string GetRightBinaryOperand(string strFormula, int iFirstChar, string operation)
Parameters
Type Name Description
System.String strFormula

Formula string.
System.Int32 iFirstChar

Index of first operand character.
System.String operation

String with operation.
Returns
Type Description
System.String

Right operand for the specified operation.

GetRightUnaryOperand(String, Int32)

Returns operand that is placed after unary operation.

Declaration
public string GetRightUnaryOperand(string strFormula, int OpIndex)
Parameters
Type Name Description
System.String strFormula

Formula string that contains operation and operand.
System.Int32 OpIndex

Index of the operation.
Returns
Type Description
System.String

Right operand of the specified unary operation.

IndexOf(ExcelFunction[], ExcelFunction)

Searches for the specified object and returns the index of the first occurrence within the entire one-dimensional array.

Declaration
[CLSCompliant(false)]
public static int IndexOf(ExcelFunction[] array, ExcelFunction value)
Parameters
Type Name Description
ExcelFunction[] array

Array to search.
ExcelFunction value

Value to locate in the array.
Returns
Type Description
System.Int32

The index of the first occurrence of value within the entire array, if found; otherwise, -1.

IndexOf(FormulaToken[], FormulaToken)

Searches for the specified object and returns the index of the first occurrence within the entire one-dimensional array.

Declaration
public static int IndexOf(FormulaToken[] array, FormulaToken value)
Parameters
Type Name Description
FormulaToken[] array

Array to search.
FormulaToken value

Value to locate in the array.
Returns
Type Description
System.Int32

The index of the first occurrence of value within the entire array, if found; otherwise, -1.

IndexOf(String[], String)

Searches for the specified object and returns the index of the first occurrence within the entire one-dimensional array.

Declaration
public static int IndexOf(string[] array, string value)
Parameters
Type Name Description
System.String[] array

Array to search.
System.String value

Value to locate in the array.
Returns
Type Description
System.Int32

The index of the first occurrence of value within the entire array, if found; otherwise, -1.

IsCell(String, Boolean, out String, out String)

Indicates if specified string is cell name.

Declaration
public static bool IsCell(string strFormula, bool bR1C1, out string strRow, out string strColumn)
Parameters
Type Name Description
System.String strFormula

Formula string that will be checked.
System.Boolean bR1C1

Indicates whether R1C1 notation must be used.
System.String strRow

String representation of the row.
System.String strColumn

String representation of the column.
Returns
Type Description
System.Boolean

True if parameter is cell name, i.e. "A1", False otherwise.

IsCell3D(String, Boolean, out String, out String, out String)

Indicates if specified strings is 3d cell reference.

Declaration
public static bool IsCell3D(string strFormula, bool bR1C1, out string strSheetName, out string strRow, out string strColumn)
Parameters
Type Name Description
System.String strFormula

Formula string that will be checked.
System.Boolean bR1C1

Indicates whether R1C1 notation must be used.
System.String strSheetName

Name of the worksheet if succeeded; otherwise - NULL.
System.String strRow

String representation of the row part of the cell reference.
System.String strColumn

String representation of the column part of the cell reference.
Returns
Type Description
System.Boolean

True if parameter is 3d cell reference, i.e. "Sheet1!A1", False otherwise.

IsCellRange(String, Boolean, out String, out String, out String, out String)

Indicates if specified string is cell range.

Declaration
public bool IsCellRange(string strFormula, bool bR1C1, out string strRow1, out string strColumn1, out string strRow2, out string strColumn2)
Parameters
Type Name Description
System.String strFormula

Formula string that will be checked.
System.Boolean bR1C1

Indicates whether R1C1 notation must be used.
System.String strRow1

The first cell's row.
System.String strColumn1

The first cell's column.
System.String strRow2

The second cell's row.
System.String strColumn2

The second cell's column
Returns
Type Description
System.Boolean

True if parameter is cell range, i.e. "A1:D1", False otherwise.

IsCellRange3D(String, Boolean, out String, out String, out String, out String, out String)

Indicates whether specified string is 3d cell range.

Declaration
public bool IsCellRange3D(string strFormula, bool bR1C1, out string strSheetName, out string strRow1, out string strColumn1, out string strRow2, out string strColumn2)
Parameters
Type Name Description
System.String strFormula

String that should be checked.
System.Boolean bR1C1

Indicates whether R1C1 notation must be used.
System.String strSheetName

String that initialize by current sheet name.
System.String strRow1

String representation of the row part of the first cell reference.
System.String strColumn1

String representation of the column part of the first cell reference.
System.String strRow2

String representation of the row part of the second cell reference.
System.String strColumn2

String representation of the column part of the second cell reference.
Returns
Type Description
System.Boolean

True if string is 3d cell range.

IsExcel2007Function(ExcelFunction)

Indicates whether specified function is supported just in Excel 2007.

Declaration
public static bool IsExcel2007Function(ExcelFunction functionIndex)
Parameters
Type Name Description
ExcelFunction functionIndex

Function id.
Returns
Type Description
System.Boolean

Value indicating whether specified function appeared in Excel 2007.

IsExcel2010Function(ExcelFunction)

Indicates whether specified function is supported just in Excel 2010.

Declaration
public static bool IsExcel2010Function(ExcelFunction functionIndex)
Parameters
Type Name Description
ExcelFunction functionIndex

Function id.
Returns
Type Description
System.Boolean

Value indicating whether specified function appeared in Excel 2010.

IsExcel2013Function(ExcelFunction)

Indicates whether specified function is supported just in Excel 2013.

Declaration
public static bool IsExcel2013Function(ExcelFunction functionIndex)
Parameters
Type Name Description
ExcelFunction functionIndex

Function id.
Returns
Type Description
System.Boolean

Value indicating whether specified function appeared in Excel 2013.

IsExcel2016Function(ExcelFunction)

Indicates whether specified function is supported just in Excel 2016.

Declaration
public static bool IsExcel2016Function(ExcelFunction functionIndex)
Parameters
Type Name Description
ExcelFunction functionIndex

Function id.
Returns
Type Description
System.Boolean

Value indicating whether specified function appeared in Excel 2016.

MarkUsedReferences(Ptg[], Boolean[])

Marks used references.

Declaration
public static void MarkUsedReferences(Ptg[] tokens, bool[] usedItems)
Parameters
Type Name Description
Ptg[] tokens

Tokens to get used references from.
System.Boolean[] usedItems

Array to mark used references in.

ParseExpression(DataProvider, Int32, ExcelVersion)

Converts byte array to Ptg array.

Declaration
public static Ptg[] ParseExpression(DataProvider provider, int iLength, ExcelVersion version)
Parameters
Type Name Description
DataProvider provider

Object that provides access to the data.
System.Int32 iLength

Number of bytes to parse (there can be array data after of all tokens).
ExcelVersion version

Excel version that was used to infill data provider.
Returns
Type Description
Ptg[]

String representation of the specified byte array that contains tokens.

ParseExpression(DataProvider, Int32, Int32, out Int32, ExcelVersion)

Converts byte array to Ptg array.

Declaration
public static Ptg[] ParseExpression(DataProvider provider, int offset, int iExpressionLength, out int finalOffset, ExcelVersion version)
Parameters
Type Name Description
DataProvider provider

Object that provides access to the data.
System.Int32 offset

Start position in data array to parse from.
System.Int32 iExpressionLength

Number of bytes to parse (there can be array data after of all tokens).
System.Int32 finalOffset

Receives offset of first byte after all tokens' data.
ExcelVersion version

Excel version that was used to infill data provider.
Returns
Type Description
Ptg[]

Converted Ptg array.

ParseFormulaRecord(FormulaRecord)

This method converts FormulaRecord to its string representation.

Declaration
[CLSCompliant(false)]
public string ParseFormulaRecord(FormulaRecord formula)
Parameters
Type Name Description
FormulaRecord formula

FormulaRecord that will be parsed.
Returns
Type Description
System.String

String representation of the specified formula.

ParseFormulaRecord(FormulaRecord, Boolean)

This method converts FormulaRecord to its string representation.

Declaration
[CLSCompliant(false)]
public string ParseFormulaRecord(FormulaRecord formula, bool bR1C1)
Parameters
Type Name Description
FormulaRecord formula

FormulaRecord that will be parsed.
System.Boolean bR1C1

Indicates whether formula must be parsed using R1C1 notation.
Returns
Type Description
System.String

String representation of the specified formula.

ParsePtgArray(Ptg[])

Converts array of tokens into string.

Declaration
public string ParsePtgArray(Ptg[] ptgs)
Parameters
Type Name Description
Ptg[] ptgs

Tokens to convert.
Returns
Type Description
System.String

String representation of the specified tokens array.

ParsePtgArray(Ptg[], Int32, Int32, Boolean, Boolean)

This method converts array of Ptg to its string representation.

Declaration
public string ParsePtgArray(Ptg[] ptgs, int row, int col, bool bR1C1, bool isForSerialization)
Parameters
Type Name Description
Ptg[] ptgs

Ptg array that will be parsed.
System.Int32 row

First row to convert.
System.Int32 col

First column to convert.
System.Boolean bR1C1

Indicates whether R1C1 notation must be used.
System.Boolean isForSerialization
Returns
Type Description
System.String

String representation of the specified Ptg array.

ParsePtgArray(Ptg[], Int32, Int32, Boolean, NumberFormatInfo, Boolean)

This method converts array of Ptg to its string representation.

Declaration
public string ParsePtgArray(Ptg[] ptgs, int row, int col, bool bR1C1, NumberFormatInfo numberInfo, bool isForSerialization)
Parameters
Type Name Description
Ptg[] ptgs

Ptg array that will be parsed.
System.Int32 row

First row to convert.
System.Int32 col

First column to convert.
System.Boolean bR1C1

Indicates whether R1C1 notation must be used.
System.Globalization.NumberFormatInfo numberInfo

Represents current number info, can be null.
System.Boolean isForSerialization
Returns
Type Description
System.String

String representation of the specified Ptg array.

ParsePtgArray(Ptg[], Int32, Int32, Boolean, NumberFormatInfo, Boolean, Boolean, IWorksheet)

This method converts array of Ptg to its string representation.

Declaration
public string ParsePtgArray(Ptg[] ptgs, int row, int col, bool bR1C1, NumberFormatInfo numberInfo, bool bRemoveSheetNames, bool isForSerialization, IWorksheet sheet)
Parameters
Type Name Description
Ptg[] ptgs

Ptg array that will be parsed.
System.Int32 row

First row to convert.
System.Int32 col

First column to convert.
System.Boolean bR1C1

Indicates whether R1C1 notation must be used.
System.Globalization.NumberFormatInfo numberInfo

Represents current number info, can be null.
System.Boolean bRemoveSheetNames

Indicates whether to remove worksheet name from 3d tokens.
System.Boolean isForSerialization
IWorksheet sheet
Returns
Type Description
System.String

String representation of the specified Ptg array.

ParseSharedFormula(ISharedFormula)

Parses shared formula.

Declaration
[CLSCompliant(false)]
public string ParseSharedFormula(ISharedFormula sharedFormula)
Parameters
Type Name Description
ISharedFormula sharedFormula

Formula to parse.
Returns
Type Description
System.String

String representation of the specified formula.

ParseSharedFormula(ISharedFormula, Int32, Int32)

Parses shared formula.

Declaration
[CLSCompliant(false)]
public string ParseSharedFormula(ISharedFormula sharedFormula, int row, int col)
Parameters
Type Name Description
ISharedFormula sharedFormula

Formula to parse.
System.Int32 row

Zero-based row index of the cell with shared formula.
System.Int32 col

Zero-based column index of the cell with shared formula.
Returns
Type Description
System.String

String representation of the specified formula.

ParseSharedFormula(ISharedFormula, Int32, Int32, Boolean, Boolean)

Parses shared formula.

Declaration
[CLSCompliant(false)]
public string ParseSharedFormula(ISharedFormula sharedFormula, int row, int col, bool bR1C1, bool isForSerialization)
Parameters
Type Name Description
ISharedFormula sharedFormula

Formula to parse.
System.Int32 row

Zero-based row index of the cell with shared formula.
System.Int32 col

Zero-based column index of the cell with shared formula.
System.Boolean bR1C1

Indicates whether formula must be parsed using R1C1 notation.
System.Boolean isForSerialization
Returns
Type Description
System.String

String representation of the specified formula.

ParseSharedString(String, Int32, Int32, IWorksheet)

Converts string to shared formula token array.

Declaration
public Ptg[] ParseSharedString(string strFormula, int iFirstRow, int iFirstColumn, IWorksheet sheet)
Parameters
Type Name Description
System.String strFormula

Represents formula string.
System.Int32 iFirstRow

Represents first row from cells range. One-based.
System.Int32 iFirstColumn

Represents first column from cells range. One-based.
IWorksheet sheet

Parent worksheet object.
Returns
Type Description
Ptg[]

Shared formula tokens.

ParseString(String)

Converts string to token array.

Declaration
public Ptg[] ParseString(string strFormula)
Parameters
Type Name Description
System.String strFormula

String that should be parsed into Ptg array.
Returns
Type Description
Ptg[]

Token array representing specified string.
Exceptions
Type Condition
System.ArgumentException

When any error occurs in the specified formula string.

ParseString(String, IWorksheet, Dictionary<String, String>)

Converts string to token array. Used when copying worksheets into another workbook.

Declaration
public Ptg[] ParseString(string strFormula, IWorksheet sheet, Dictionary<string, string> hashWorksheetNames)
Parameters
Type Name Description
System.String strFormula

String that should be parsed into Ptg array.
IWorksheet sheet

Parent sheet.
System.Collections.Generic.Dictionary<System.String, System.String> hashWorksheetNames

Dictionary that contains old name of the worksheet as a key and new name of the worksheet as value.
Returns
Type Description
Ptg[]

Token array representing specified string.
Exceptions
Type Condition
System.ArgumentException

When any error occurs in the specified formula string.

ParseString(String, IWorksheet, Dictionary<String, String>, Int32, Int32, Boolean)

Converts string to token array. Used when copying worksheets into another workbook.

Declaration
public Ptg[] ParseString(string strFormula, IWorksheet sheet, Dictionary<string, string> hashWorksheetNames, int iCellRow, int iCellColumn, bool bR1C1)
Parameters
Type Name Description
System.String strFormula

String that should be parsed into Ptg array.
IWorksheet sheet

Parent sheet.
System.Collections.Generic.Dictionary<System.String, System.String> hashWorksheetNames

Dictionary that contains old name of the worksheet as a key and new name of the worksheet as value.
System.Int32 iCellRow

Row index of the cell that contains formula to parse.
System.Int32 iCellColumn

Column index of the cell that contains formula to parse.
System.Boolean bR1C1

Indicates whether R1C1 notation is used.
Returns
Type Description
Ptg[]

Token array representing specified string.
Exceptions
Type Condition
System.ArgumentException

When any error occurs in the specified formula string.

ParseString(String, IWorksheet, Dictionary<Type, ReferenceIndexAttribute>, Int32, Dictionary<String, String>, ExcelParseFormulaOptions, Int32, Int32)

Converts string to token array.

Declaration
public Ptg[] ParseString(string strFormula, IWorksheet sheet, Dictionary<Type, ReferenceIndexAttribute> indexes, int i, Dictionary<string, string> hashWorksheetNames, ExcelParseFormulaOptions options, int iCellRow, int iCellColumn)
Parameters
Type Name Description
System.String strFormula

String that should be parsed into Ptg array.
IWorksheet sheet

Worksheet that contains formula.
System.Collections.Generic.Dictionary<System.Type, ReferenceIndexAttribute> indexes

Token indexes, indicates whether to use reference token, value token, or array token.
System.Int32 i

Index in string.
System.Collections.Generic.Dictionary<System.String, System.String> hashWorksheetNames

Hash table with worksheet names.
ExcelParseFormulaOptions options

Parse options.
System.Int32 iCellRow

Row index of the cell that contains formula to parse.
System.Int32 iCellColumn

Column index of the cell that contains formula to parse.
Returns
Type Description
Ptg[]

Parsed formula.
Exceptions
Type Condition
System.ArgumentException

When formula string is empty or if there is another error in formula string or when string consists only of %.

PtgArrayToByteArray(Ptg[], ExcelVersion)

Converts token array to corresponding byte array.

Declaration
public static byte[] PtgArrayToByteArray(Ptg[] tokens, ExcelVersion version)
Parameters
Type Name Description
Ptg[] tokens

Ptg array that will be converted to byte array.
ExcelVersion version

Excel version that should be used to infill data.
Returns
Type Description
System.Byte[]

Converted byte array

PtgArrayToByteArray(Ptg[], out Int32, ExcelVersion)

Converts token array to corresponding byte array.

Declaration
public static byte[] PtgArrayToByteArray(Ptg[] arrTokens, out int formulaLen, ExcelVersion version)
Parameters
Type Name Description
Ptg[] arrTokens

Ptg array that will be converted to byte array.
System.Int32 formulaLen

Length of formula without tArray data if there is tArray in Tokens.
ExcelVersion version

Excel version that should be used to infill data.
Returns
Type Description
System.Byte[]

Converted byte array.

PushOperandToStack(Stack<Object>, String)

Pushes operand into stack correctly.

Declaration
public static void PushOperandToStack(Stack<object> operands, string operand)
Parameters
Type Name Description
System.Collections.Generic.Stack<System.Object> operands
System.String operand

RaiseFormulaEvaluation(Object, EvaluateEventArgs)

This method raises the FormulaEvaluation event.

Declaration
public static void RaiseFormulaEvaluation(object sender, EvaluateEventArgs e)
Parameters
Type Name Description
System.Object sender

Range that caused FormulaEvaluation event.
EvaluateEventArgs e

Object that contains event arguments.

RegisterAdditionalAlias(String, ExcelFunction)

Registers new function alias.

Declaration
[CLSCompliant(false)]
public static void RegisterAdditionalAlias(string aliasName, ExcelFunction functionIndex)
Parameters
Type Name Description
System.String aliasName

New alias name.
ExcelFunction functionIndex

Function index.

RegisterFunction(String, ExcelFunction)

Registers function in internal collections.

Declaration
[CLSCompliant(false)]
public static void RegisterFunction(string functionName, ExcelFunction index)
Parameters
Type Name Description
System.String functionName

Name of the function that must be registered.
ExcelFunction index

Index of the function that must be registered.

RegisterFunction(String, ExcelFunction, ReferenceIndexAttribute[])

Registers function in internal collections.

Declaration
[CLSCompliant(false)]
public static void RegisterFunction(string functionName, ExcelFunction index, ReferenceIndexAttribute[] paramIndexes)
Parameters
Type Name Description
System.String functionName

Name of the function that must be registered.
ExcelFunction index

Index of the function that must be registered.
ReferenceIndexAttribute[] paramIndexes

Array of ReferenceIndexAttribute that contains information about proper token index.

RegisterFunction(String, ExcelFunction, ReferenceIndexAttribute[], Int32)

Registers function in internal collections.

Declaration
[CLSCompliant(false)]
public static void RegisterFunction(string functionName, ExcelFunction index, ReferenceIndexAttribute[] paramIndexes, int paramCount)
Parameters
Type Name Description
System.String functionName

Name of the function that must be registered.
ExcelFunction index

Index of the function that must be registered.
ReferenceIndexAttribute[] paramIndexes

Array of ReferenceIndexAttribute that contains information about proper token index.
System.Int32 paramCount

Number of parameters in the function, -1, for variable parameters.

RegisterFunction(String, ExcelFunction, Int32)

Registers function in internal collections.

Declaration
[CLSCompliant(false)]
public static void RegisterFunction(string functionName, ExcelFunction index, int paramCount)
Parameters
Type Name Description
System.String functionName

Name of the function that must be registered.
ExcelFunction index

Index of the function that must be registered.
System.Int32 paramCount

Number of parameter in the function, -1, for variable parameters number.

RegisterTokenClass(Type)

Registers token class (can be user defined).

Declaration
public static void RegisterTokenClass(Type type)
Parameters
Type Name Description
System.Type type

Token class that will be registered.
Exceptions
Type Condition
System.ArgumentException

When class is not derived from Ptg.
System.ArgumentNullException

When parameter type is NULL.

SetSeparators(Char, Char)

Sets separators.

Declaration
public void SetSeparators(char operandsSeparator, char arrayRowsSeparator)
Parameters
Type Name Description
System.Char operandsSeparator

Operand separator to set.
System.Char arrayRowsSeparator

Array rows separator to set.

SplitArray(String, String)

Splits array. Each string in result represents single row of the array.

Declaration
public List<string> SplitArray(string strFormula, string strSeparator)
Parameters
Type Name Description
System.String strFormula

Array string to split.
System.String strSeparator

Separator
Returns
Type Description
System.Collections.Generic.List<System.String>

Splitted array.

UpdateFormula(Ptg[], Int32, Int32, WorkbookImpl, WorkbookImpl, WorksheetImpl)

Updates formula after move / copy operation.

Declaration
public Ptg[] UpdateFormula(Ptg[] arrPtgs, int iRowDelta, int iColumnDelta, WorkbookImpl sourceBook, WorkbookImpl destBook, WorksheetImpl destSheet)
Parameters
Type Name Description
Ptg[] arrPtgs

Tokens to update.
System.Int32 iRowDelta

Value to add to the row index.
System.Int32 iColumnDelta

Value to add to the column index.
WorkbookImpl sourceBook
WorkbookImpl destBook
WorksheetImpl destSheet
Returns
Type Description
Ptg[]

Updated tokens array.

UpdateFormula(Ptg[], Int32, Int32, Rectangle, Int32, Rectangle, Int32, Int32)

Updates formula after move / copy operation.

Declaration
public Ptg[] UpdateFormula(Ptg[] arrPtgs, int iCurIndex, int iSourceIndex, Rectangle sourceRect, int iDestIndex, Rectangle destRect, int iRow, int iColumn)
Parameters
Type Name Description
Ptg[] arrPtgs

Tokens to update.
System.Int32 iCurIndex

Current worksheet index.
System.Int32 iSourceIndex

Source worksheet index.
System.Drawing.Rectangle sourceRect

Source rectangle.
System.Int32 iDestIndex

Destination worksheet index.
System.Drawing.Rectangle destRect

Destination rectangle.
System.Int32 iRow

One-based row index.
System.Int32 iColumn

One-based column index.
Returns
Type Description
Ptg[]

Updated tokens array.

UpdateNameIndex(Ptg, IDictionary<Int32, Int32>)

Updates index of the name.

Declaration
public bool UpdateNameIndex(Ptg ptg, IDictionary<int, int> dicNewIndex)
Parameters
Type Name Description
Ptg ptg

Token to update.
System.Collections.Generic.IDictionary<System.Int32, System.Int32> dicNewIndex

Dictionary with new named ranges indexes.
Returns
Type
System.Boolean

UpdateNameIndex(Ptg, Int32, Int32)

Updates index of the name.

Declaration
public static void UpdateNameIndex(Ptg ptg, int iOldIndex, int iNewIndex)
Parameters
Type Name Description
Ptg ptg

Ptg to Update
System.Int32 iOldIndex

Old index.
System.Int32 iNewIndex

New index.

UpdateNameIndex(Ptg, Int32[])

Updates index of the name.

Declaration
public bool UpdateNameIndex(Ptg ptg, int[] arrNewIndex)
Parameters
Type Name Description
Ptg ptg

Token to update.
System.Int32[] arrNewIndex

Array with new named ranges indexes.
Returns
Type
System.Boolean

UpdateNameIndex(Ptg[], IDictionary<Int32, Int32>)

Updates name indexes.

Declaration
public bool UpdateNameIndex(Ptg[] arrExpression, IDictionary<int, int> dicNewIndex)
Parameters
Type Name Description
Ptg[] arrExpression

Parsed expression to update.
System.Collections.Generic.IDictionary<System.Int32, System.Int32> dicNewIndex

Dictionary with new indexes.
Returns
Type Description
System.Boolean

True if at least one of references to named ranges was updated.

UpdateNameIndex(Ptg[], Int32[])

Updates name indexes.

Declaration
public bool UpdateNameIndex(Ptg[] arrExpression, int[] arrNewIndex)
Parameters
Type Name Description
Ptg[] arrExpression

Parsed expression to update.
System.Int32[] arrNewIndex

Array with new indexes.
Returns
Type Description
System.Boolean

True if at least one of references to named ranges was updated.

UpdateReferenceIndexes(Ptg[], Int32[])

Updates reference indexes.

Declaration
public static bool UpdateReferenceIndexes(Ptg[] tokens, int[] arrUpdatedIndexes)
Parameters
Type Name Description
Ptg[] tokens

Tokens to get used references from.
System.Int32[] arrUpdatedIndexes

Array with updated indexes.
Returns
Type
System.Boolean

Events

FormulaEvaluator

Event handler that will receive array of Ptg after parsing.

Declaration
public static event EvaluateEventHandler FormulaEvaluator
Event Type
Type
Syncfusion.XlsIO.Implementation.EvaluateEventHandler

Implements

System.IDisposable