ActiveXLS
Class Formula

System.Object
  ActiveXLS.Formula

public class Formula

extends System.Object

This class is used for formulas computation.

Constructor Summary

Formula()
Defines a new instance of the object that parses formulas.

Formula(ExcelDocument excel)
Defines a new instance of the object that parses formulas.

Formula(ExcelDocument excel, bool bR1C1ReferenceStyle)
Defines a new instance of the object that parses formulas.

Formula(ExcelDocument excel, ActiveXLS.OpenXML.Workbook workbook)
Defines a new instance of the object that parses formulas.

Method Summary

FormulaResult	computeFormula(System.String sFormula, ExcelWorksheet currentWorksheet_arg, ExcelWorksheet formulaWorksheet_arg, int formulaRow, int formulaColumn) Returns the result of an Excel formula; the formula should not start with the = (equal) sign like in Excel.
static int[]	get2DCellElements(System.String sCellReference) Returns an array of integers containing the elements of the specified sCellReference
static int[]	get2DCellElementsR1C1style(System.String sCellReference, int formulaRow, int formulaColumn) Returns an array of integers containing the elements of the specified sCellReference
static int[]	get2DColumnCellElementsR1C1style(System.String sCellReference, int formulaRow, int formulaColumn) Returns an array of integers containing the elements of the specified sCellReference
static int[]	get2DColumnRangeElementsR1C1style(System.String sRange, int formulaRow, int formulaColumn) Returns an array of integers containing the elements of the specified sRange
static int[]	get2DRangeElements(System.String sRange) Returns an array of integers containing the elements of the specified sRange
static int[]	get2DRangeElementsR1C1style(System.String sRange, int formulaRow, int formulaColumn) Returns an array of integers containing the elements of the specified sRange
static int[]	get2DRowCellElementsR1C1style(System.String sCellReference, int formulaRow, int formulaColumn) Returns an array of integers containing the elements of the specified sCellReference
static int[]	get2DRowRangeElementsR1C1style(System.String sRange, int formulaRow, int formulaColumn) Returns an array of integers containing the elements of the specified sRange
System.Object[]	get3DCellElements(System.String sCellReference) Returns an array of objects containing the elements of the specified sCellReference
System.Object[]	get3DCellElementsR1C1style(System.String sCellReference, int formulaRow, int formulaColumn) Returns an array of objects containing the elements of the specified sCellReference
System.Object[]	get3DColumnRangeElementsR1C1style(System.String sRange, int formulaRow, int formulaColumn) Returns an array of objects containing the elements of the specified sRange
System.Object[]	get3DRangeElements(System.String sRange) Returns an array of objects containing the elements of the specified sRange
System.Object[]	get3DRangeElementsR1C1style(System.String sRange, int formulaRow, int formulaColumn) Returns an array of objects containing the elements of the specified sRange
System.Object[]	get3DRowRangeElementsR1C1style(System.String sRange, int formulaRow, int formulaColumn) Returns an array of objects containing the elements of the specified sRange
static int	getColumnNumberFromLetters(System.String sColumnName) Returns the index of the column identified by sColumnName
static int	getColumnNumberFromLetters(System.StringBuffer sColumnName)
ExcelName	getExcelName(System.String sName) Returns the ExcelName with the specified name.
static System.String	getLettersFromColumnNumber(int nColumnNumber) Returns the column name corresponding to the nColumnNumber index
static bool	Is2DCellReference(System.String sValue) Returns true if the sValue is a simple cell reference (e.g. A1).
static bool	Is2DCellReferenceR1C1style(System.String sValue) Returns true if the sValue is a simple cell reference in R1C1 reference style(e.g.
static bool	Is2DColumnRange(System.String sRange) Returns true if the sRange is a column range.
static bool	Is2DColumnRangeR1C1style(System.String sValue) Returns true if the sValue is a simple column range reference in R1C1 reference style(e.g. C[1]:C[2]).
static bool	Is2DRange(System.String sValue) Returns true if the sValue is a simple range reference (e.g. A1:B9).
static bool	Is2DRangeR1C1style(System.String sValue) Returns true if the sValue is a simple range reference in R1C1 reference style (e.g. R1C1:R9C2).
static bool	Is2DRowRange(System.String sRange) Returns true if the sRange is a simple row range (e.g. 4:6)
static bool	Is2DRowRangeR1C1style(System.String sValue) Returns true if the sRange is a simple row range in R1C1 reference style (e.g. R4:R6).
static bool	Is3DCellReference(System.String sValue) Returns true if the sValue is a complex cell reference (e.g. Sheet1!A1).
static bool	Is3DCellReferenceR1C1style(System.String sValue) Returns true if the sValue is a complex cell reference in (e.g. Sheet1!R1C1).
static bool	Is3DColumnRangeR1C1style(System.String sValue) Returns true if the sValue is a complex column range reference in R1C1 reference style(e.g. Sheet1!C[1]:C[2]).
static bool	Is3DRange(System.String sValue) Returns true if the sValue is a complex range reference (e.g. Sheet1!A1:B9).
static bool	Is3DRangeR1C1style(System.String sValue) Returns true if the sValue is a complex range reference in R1C1 reference style (e.g. Sheet1!R1C1:R9C2).
static bool	Is3DRowRangeR1C1style(System.String sValue) Returns true if the sRange is a complex row range in R1C1 reference style (e.g. Sheet1!R4:R6).
bool	IsArray(System.String sValue) Returns true if the sValue represents an Excel array (e.g. {1,2,3} )
static bool	IsBoolean(System.String sValue) Returns true if the sValue represents a bool value.
static bool	IsDouble(System.String sValue) Returns true if the sValue represents a double value.
static bool	isError(System.String sValue) Returns whether or not sValue is one of the errors recognized by Excel
bool	IsFunction(System.String token) Returns true if the token represents a name of a function recognized by Microsoft Excel
static bool	IsInteger(System.String sValue) Returns true if the sValue represents an integer value.
bool	IsName(System.String sValue) Returns true if the string represents an Excel Name.
static bool	IsString(System.String sValue) Returns true if the sValue represents a string value.
List[]	parse_formula(System.String sFormula, int nInitialClass, int nFormulaRow, int nFormulaColumn, bool bNameX, bool bBIFF12) Internal use only
static System.String	SheetNameFromCanonicalExpression(System.String sSheetName) Returns the original sheet name from a string that represents the sheet name in canonical expression.
static System.String	SheetNameToCanonicalExpression(System.String sSheetName) Returns the sheet name in canonical expression.

Constructor Detail

Formula

public Formula()

Defines a new instance of the object that parses formulas.

Formula

public Formula(ExcelDocument excel)

Defines a new instance of the object that parses formulas.

Parameters:

excel - the ExcelDocument object that contains the formulas that will be parsed

Formula

public Formula(ExcelDocument excel,
               bool bR1C1ReferenceStyle)

Defines a new instance of the object that parses formulas.

Parameters:

excel - the ExcelDocument object that contains the formulas that will be parsed

bR1C1ReferenceStyle - true if the formulas contains references that are in R1C1 style (e.g. SUM(R1C1:R2C4))

Formula

public Formula(ExcelDocument excel,
               ActiveXLS.OpenXML.Workbook workbook)

Defines a new instance of the object that parses formulas.

Parameters:

excel - the ExcelDocument object that contains the formulas that will be parsed

workbook - the internal workbook of the Microsoft Office 2007 document

Method Detail

parse_formula

public List[] parse_formula(System.String sFormula,
                            int nInitialClass,
                            int nFormulaRow,
                            int nFormulaColumn,
                            bool bNameX,
                            bool bBIFF12)

Internal use only

SheetNameToCanonicalExpression

public static System.String SheetNameToCanonicalExpression(System.String sSheetName)

Returns the sheet name in canonical expression. A sheet name in canonical expression is the transformed sheet name because of the special chars (' + - & = < > ^ ( ) , | { } % ~ ; ! space).

Parameters:

sSheetName - the original sheet name

Returns:

the original sheet name

IsFunction

public bool IsFunction(System.String token)

Returns true if the token represents a name of a function recognized by Microsoft Excel

Parameters:

token - the value to be checked .

Returns:

true if the token is a name of a function form Excel.

IsInteger

public static bool IsInteger(System.String sValue)

Returns true if the sValue represents an integer value.

Parameters:

sValue - the value to be checked .

Returns:

true if the value represents an integer value.

IsDouble

public static bool IsDouble(System.String sValue)

Returns true if the sValue represents a double value.

Parameters:

sValue - the value to be checked .

Returns:

true if the value represents a double value.

IsBoolean

public static bool IsBoolean(System.String sValue)

Returns true if the sValue represents a bool value. Boolean values are "True" or "False" - case insensitive.

Parameters:

sValue - the value to be checked .

Returns:

true if the value represents a bool value.

Is2DCellReference

public static bool Is2DCellReference(System.String sValue)

Returns true if the sValue is a simple cell reference (e.g. A1).

Parameters:

sValue - the cell reference .

Returns:

true if the the sValue is a simple cell reference (such as A1 or D9 or IV65536).

Is2DRange

public static bool Is2DRange(System.String sValue)

Returns true if the sValue is a simple range reference (e.g. A1:B9).

Parameters:

sValue - the range reference .

Returns:

true if the the sValue is a simple range reference (such as A1:B9).

Is3DCellReference

public static bool Is3DCellReference(System.String sValue)

Returns true if the sValue is a complex cell reference (e.g. Sheet1!A1).

Parameters:

sValue - the cell reference .

Returns:

true if the the sValue is a complex cell reference (such as Sheet1!A1 or Sheet2!D9 or Sheet3!IV65536).

Is3DRange

public static bool Is3DRange(System.String sValue)

Returns true if the sValue is a complex range reference (e.g. Sheet1!A1:B9).

Parameters:

sValue - the range reference .

Returns:

true if the the sValue is a complex range reference (such as Sheet1!A1:B9).

IsName

public bool IsName(System.String sValue)

Returns true if the string represents an Excel Name.

Parameters:

sValue - the string whose value will be tested if it represents an Excel Name.

Returns:

true if the string is an Excel Name.

IsString

public static bool IsString(System.String sValue)

Returns true if the sValue represents a string value. A string value is any string that starts and ends with " (double quotes)

Parameters:

sValue - the value to be checked .

Returns:

true if the value represents a string value.

IsArray

public bool IsArray(System.String sValue)

Returns true if the sValue represents an Excel array (e.g. {1,2,3} )

Parameters:

sValue - the value to be checked .

Returns:

true if the value represents an Excel array such as {1,2,3}

SheetNameFromCanonicalExpression

public static System.String SheetNameFromCanonicalExpression(System.String sSheetName)

Returns the original sheet name from a string that represents the sheet name in canonical expression. A sheet name in canonical expression is the transformed sheet name because of the special chars (' + - & = < > ^ ( ) , | { } % ~ ; ! space).

Parameters:

sSheetName - the sheet name in canonical expression

Returns:

the original sheet name

getColumnNumberFromLetters

public static int getColumnNumberFromLetters(System.String sColumnName)

Returns the index of the column identified by sColumnName

Parameters:

sColumnName - the column name; it can be A, B, C to IV

Returns:

the index of the column starting from 1

getColumnNumberFromLetters

public static int getColumnNumberFromLetters(System.StringBuffer sColumnName)

getLettersFromColumnNumber

public static System.String getLettersFromColumnNumber(int nColumnNumber)

Returns the column name corresponding to the nColumnNumber index

Parameters:

nColumnNumber - the column index

Returns:

the column name; it can be A, B, C to IV

get2DCellElements

public static int[] get2DCellElements(System.String sCellReference)

Returns an array of integers containing the elements of the specified sCellReference

Parameters:

sCellReference - the range whose elements will be returned.

Returns:

int[0] - the number of the column; int[1] - the number of the row; int[2] - 1 if is absolute column; int[3] - 1 if is absolute row;

get2DRangeElements

public static int[] get2DRangeElements(System.String sRange)

Returns an array of integers containing the elements of the specified sRange

Parameters:

sRange - the range whose elements will be returned.

Returns:

int[0] - the number of the first column; int[1] - the number of the first row; int[2] - the number of the second column; int[3] - the number of the second row. int[4] - 1 if is absolute first column; int[5] - 1 if is absolute first row; int[6] - 1 if is absolute second column; int[7] - 1 if is absolute second row;

get3DCellElements

public System.Object[] get3DCellElements(System.String sCellReference)

Returns an array of objects containing the elements of the specified sCellReference

Parameters:

sCellReference - the range whose elements will be returned.

Returns:

Object[0] - ExcelWorksheet object; Object[1] - the number of the column; Object[2] - the number of the row; Object[3] - 1 if is absolute column; Object[4] - 1 if is absolute row;

get3DRangeElements

public System.Object[] get3DRangeElements(System.String sRange)

Returns an array of objects containing the elements of the specified sRange

Parameters:

sRange - the range whose elements will be returned.

Returns:

Object[0] - ExcelWorksheet object; Object[1] - the number of the first column; Object[2] - the number of the first row; Object[3] - the number of the second column; Object[4] - the number of the second row. Object[5] - 1 if is absolute first column; Object[6] - 1 if is absolute first row; Object[7] - 1 if is absolute second column; Object[8] - 1 if is absolute second row;

Is2DRowRange

public static bool Is2DRowRange(System.String sRange)

Returns true if the sRange is a simple row range (e.g. 4:6)

Parameters:

sRange - the range .

Returns:

true if the range is a simple row range (such as 4:6).

Is2DColumnRange

public static bool Is2DColumnRange(System.String sRange)

Returns true if the sRange is a column range.

Parameters:

sRange - the range .

Returns:

true if the range is a column range.

computeFormula

public FormulaResult computeFormula(System.String sFormula,
                                    ExcelWorksheet currentWorksheet_arg,
                                    ExcelWorksheet formulaWorksheet_arg,
                                    int formulaRow,
                                    int formulaColumn)
                             

Returns the result of an Excel formula; the formula should not start with the = (equal) sign like in Excel.

Parameters:

sFormula - - the formula to be computed ( e.g. SUM(SQRT(SUM(WKS2!$B2:C$7))) )

currentWorksheet_arg - - the ExcelWorksheet that the TableReference belongs to

formulaWorksheet_arg - - the ExcelWorksheet that the formula belongs to

formulaRow - - the row number of the cell that the formula belongs to

formulaColumn - - the column number of the cell that the formula belongs to

Returns:

a FormulaResult object that contains all the properties of the computation ( the result as text, the default number format, the result data type etc) If we have an ExcelDocument with two ExcelWorksheets named WKS1 and WKS2 and in the WKS1 we have in the A1 a formula like SUM(SQRT(SUM(WKS2!$B2:C$7))) then we have: currentWorksheet_arg = WKS2; formulaWorksheet_arg = WKS1; formulaRow = 1; formulaCol = 1;

Throws:

System.Exception

getExcelName

public ExcelName getExcelName(System.String sName)

Returns the ExcelName with the specified name.

Parameters:

sName - the string containing the name of the ExcelName.

Returns:

the ExcelName.

isError

public static bool isError(System.String sValue)

Returns whether or not sValue is one of the errors recognized by Excel

Parameters:

sValue - a string value to be checked if represents an Excel error

Returns:

true if sValue is one of the following values #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!

Is2DCellReferenceR1C1style

public static bool Is2DCellReferenceR1C1style(System.String sValue)

Returns true if the sValue is a simple cell reference in R1C1 reference style(e.g. R1C1).

Parameters:

sValue - the cell reference .

Returns:

true if the the sValue is a simple cell reference in R1C1 reference style (such as R1C1 or R9C4 or R65536C256).

Is2DRangeR1C1style

public static bool Is2DRangeR1C1style(System.String sValue)

Returns true if the sValue is a simple range reference in R1C1 reference style (e.g. R1C1:R9C2).

Parameters:

sValue - the range reference .

Returns:

true if the the sValue is a simple range reference in R1C1 reference style (such as R1C1:R9C2).

Is2DRowRangeR1C1style

public static bool Is2DRowRangeR1C1style(System.String sValue)

Returns true if the sRange is a simple row range in R1C1 reference style (e.g. R4:R6).

Parameters:

sValue - the range .

Returns:

true if the range is a simple row range in R1C1 reference style (such as R4:R6).

Is2DColumnRangeR1C1style

public static bool Is2DColumnRangeR1C1style(System.String sValue)

Returns true if the sValue is a simple column range reference in R1C1 reference style(e.g. C[1]:C[2]).

Parameters:

sValue - the column range reference .

Returns:

true if the the sValue is a simple column range reference in R1C1 reference style (such as C[1]:C[2] or C3:C4).

Is3DCellReferenceR1C1style

public static bool Is3DCellReferenceR1C1style(System.String sValue)

Returns true if the sValue is a complex cell reference in (e.g. Sheet1!R1C1).

Parameters:

sValue - the cell reference .

Returns:

true if the the sValue is a complex cell reference in R1C1 reference style (such as Sheet1!R1C1 or Sheet2!R9C4 or Sheet3!R65536C256).

Is3DRangeR1C1style

public static bool Is3DRangeR1C1style(System.String sValue)

Returns true if the sValue is a complex range reference in R1C1 reference style (e.g. Sheet1!R1C1:R9C2).

Parameters:

sValue - the range reference .

Returns:

true if the the sValue is a complex range reference in R1C1 reference style (such as Sheet1!R1C1:R9C2).

Is3DRowRangeR1C1style

public static bool Is3DRowRangeR1C1style(System.String sValue)

Returns true if the sRange is a complex row range in R1C1 reference style (e.g. Sheet1!R4:R6).

Parameters:

sValue - the range .

Returns:

true if the range is a complex row range in R1C1 reference style (such as Sheet1!R4:R6).

Is3DColumnRangeR1C1style

public static bool Is3DColumnRangeR1C1style(System.String sValue)

Returns true if the sValue is a complex column range reference in R1C1 reference style(e.g. Sheet1!C[1]:C[2]).

Parameters:

sValue - the column range reference .

Returns:

true if the the sValue is a complex column range reference in R1C1 reference style (such as Sheet1!C[1]:C[2] or Sheet1!C3:C4).

get2DCellElementsR1C1style

public static int[] get2DCellElementsR1C1style(System.String sCellReference,
                                               int formulaRow,
                                               int formulaColumn)

Returns an array of integers containing the elements of the specified sCellReference

Parameters:

sCellReference - the range whose elements will be returned.

formulaRow - - the row number of the cell that formula belongs to

formulaColumn - - the column number of the cell that formula belongs to

Returns:

int[0] - the number of the column; int[1] - the number of the row; int[2] - 1 if is absolute column; int[3] - 1 if is absolute row;

get2DRangeElementsR1C1style

public static int[] get2DRangeElementsR1C1style(System.String sRange,
                                                int formulaRow,
                                                int formulaColumn)

Returns an array of integers containing the elements of the specified sRange

Parameters:

sRange - the range whose elements will be returned.

formulaRow - - the row number of the cell that formula belongs to

formulaColumn - - the column number of the cell that formula belongs to

Returns:

int[0] - the number of the first column; int[1] - the number of the first row; int[2] - the number of the second column; int[3] - the number of the second row. int[4] - 1 if is absolute first column; int[5] - 1 if is absolute first row; int[6] - 1 if is absolute second column; int[7] - 1 if is absolute second row;

get2DRowCellElementsR1C1style

public static int[] get2DRowCellElementsR1C1style(System.String sCellReference,
                                                  int formulaRow,
                                                  int formulaColumn)

Returns an array of integers containing the elements of the specified sCellReference

Parameters:

sCellReference - the range whose elements will be returned.

formulaRow - - the row number of the cell that formula belongs to

formulaColumn - - the column number of the cell that formula belongs to

Returns:

int[0] - the number of the column; int[1] - the number of the row; int[2] - 1 if is absolute column; int[3] - 1 if is absolute row;

get2DColumnCellElementsR1C1style

public static int[] get2DColumnCellElementsR1C1style(System.String sCellReference,
                                                     int formulaRow,
                                                     int formulaColumn)

Returns an array of integers containing the elements of the specified sCellReference

Parameters:

sCellReference - the range whose elements will be returned.

formulaRow - - the row number of the cell that formula belongs to

formulaColumn - - the column number of the cell that formula belongs to

Returns:

int[0] - the number of the column; int[1] - the number of the row; int[2] - 1 if is absolute column; int[3] - 1 if is absolute row;

get2DRowRangeElementsR1C1style

public static int[] get2DRowRangeElementsR1C1style(System.String sRange,
                                                   int formulaRow,
                                                   int formulaColumn)

Returns an array of integers containing the elements of the specified sRange

Parameters:

sRange - the range whose elements will be returned.

formulaRow - - the row number of the cell that formula belongs to

formulaColumn - - the column number of the cell that formula belongs to

Returns:

int[0] - the number of the first column; int[1] - the number of the first row; int[2] - the number of the second column; int[3] - the number of the second row. int[4] - 1 if is absolute first column; int[5] - 1 if is absolute first row; int[6] - 1 if is absolute second column; int[7] - 1 if is absolute second row;

get2DColumnRangeElementsR1C1style

public static int[] get2DColumnRangeElementsR1C1style(System.String sRange,
                                                      int formulaRow,
                                                      int formulaColumn)

Returns an array of integers containing the elements of the specified sRange

Parameters:

sRange - the range whose elements will be returned.

formulaRow - - the row number of the cell that formula belongs to

formulaColumn - - the column number of the cell that formula belongs to

Returns:

int[0] - the number of the first column; int[1] - the number of the first row; int[2] - the number of the second column; int[3] - the number of the second row. int[4] - 1 if is absolute first column; int[5] - 1 if is absolute first row; int[6] - 1 if is absolute second column; int[7] - 1 if is absolute second row;

get3DCellElementsR1C1style

public System.Object[] get3DCellElementsR1C1style(System.String sCellReference,
                                                     int formulaRow,
                                                     int formulaColumn)

Returns an array of objects containing the elements of the specified sCellReference

Parameters:

sCellReference - the cell reference whose elements will be returned.

formulaRow - - the row number of the cell that formula belongs to

formulaColumn - - the column number of the cell that formula belongs to

Returns:

Object[0] - ExcelWorksheet object; Object[1] - the number of the column; Object[2] - the number of the row; Object[3] - 1 if is absolute column; Object[4] - 1 if is absolute row;

get3DRangeElementsR1C1style

public System.Object[] get3DRangeElementsR1C1style(System.String sRange,
                                                      int formulaRow,
                                                      int formulaColumn)

Returns an array of objects containing the elements of the specified sRange

Parameters:

sRange - the range whose elements will be returned.

formulaRow - - the row number of the cell that formula belongs to

formulaColumn - - the column number of the cell that formula belongs to

Returns:

Object[0] - ExcelWorksheet object; Object[1] - the number of the first column; Object[2] - the number of the first row; Object[3] - the number of the second column; Object[4] - the number of the second row. Object[5] - 1 if is absolute first column; Object[6] - 1 if is absolute first row; Object[7] - 1 if is absolute second column; Object[8] - 1 if is absolute second row;

get3DRowRangeElementsR1C1style

public System.Object[] get3DRowRangeElementsR1C1style(System.String sRange,
                                                         int formulaRow,
                                                         int formulaColumn)

Returns an array of objects containing the elements of the specified sRange

Parameters:

sRange - the range whose elements will be returned.

formulaRow - - the row number of the cell that formula belongs to

formulaColumn - - the column number of the cell that formula belongs to

Returns:

Object[0] - ExcelWorksheet object; Object[1] - the number of the first column; Object[2] - the number of the first row; Object[3] - the number of the second column; Object[4] - the number of the second row. Object[5] - 1 if is absolute first column; Object[6] - 1 if is absolute first row; Object[7] - 1 if is absolute second column; Object[8] - 1 if is absolute second row;

get3DColumnRangeElementsR1C1style

public System.Object[] get3DColumnRangeElementsR1C1style(System.String sRange,
                                                            int formulaRow,
                                                            int formulaColumn)

Returns an array of objects containing the elements of the specified sRange

Parameters:

sRange - the range whose elements will be returned.

formulaRow - - the row number of the cell that formula belongs to

formulaColumn - - the column number of the cell that formula belongs to

Returns:

Object[0] - ExcelWorksheet object; Object[1] - the number of the first column; Object[2] - the number of the first row; Object[3] - the number of the second column; Object[4] - the number of the second row. Object[5] - 1 if is absolute first column; Object[6] - 1 if is absolute first row; Object[7] - 1 if is absolute second column; Object[8] - 1 if is absolute second row;