Documentation

Calculation extends CalculationLocale

in package

• Application

Table of Contents

Constants

CALCULATION_REGEXP_CELLREF  = '((([^\s,!&%^\/\*\+<>=:`-]*)|(\'(?:[^\']|\'[^!])+?\')|(\"(?:[^\"]|\"[^!])+?\"))!)?\$?\b([a-z]{1,3})\$?(\d{1,7})(?![\w.])'

CALCULATION_REGEXP_CELLREF_RELATIVE  = '((([^\s\(,!&%^\/\*\+<>=:`-]*)|(\'(?:[^\']|\'[^!])+?\')|(\"(?:[^\"]|\"[^!])+?\"))!)?(\$?\b[a-z]{1,3})(\$?\d{1,7})(?![\w.])'

CALCULATION_REGEXP_CELLREF_SPILL  = '/' . self::CALCULATION_REGEXP_CELLREF . '#/i'

CALCULATION_REGEXP_COLUMN_RANGE  = '(((([^\s\(,!&%^\/\*\+<>=:`-]*)|(\'(?:[^\']|\'[^!])+?\')|(\".(?:[^\"]|\"[^!])?\"))!)?(\$?[a-z]{1,3})):(?![.*])'

CALCULATION_REGEXP_COLUMNRANGE_RELATIVE  = '(\$?[a-z]{1,3}):(\$?[a-z]{1,3})'

CALCULATION_REGEXP_DEFINEDNAME  = '((([^\s,!&%^\/\*\+<>=-]*)|(\'(?:[^\']|\'[^!])+?\')|(\"(?:[^\"]|\"[^!])+?\"))!)?([_\p{L}][_\p{L}\p{N}\.]*)'

CALCULATION_REGEXP_ERROR  = '\#[A-Z][A-Z0_\/]*[!\?]?'

CALCULATION_REGEXP_FUNCTION  = '@?(?:_xlfn\.)?(?:_xlws\.)?([\p{L}][\p{L}\p{N}\.]*)[\s]*\('

CALCULATION_REGEXP_NUMBER  = '[-+]?\d*\.?\d+(e[-+]?\d+)?'

Regular Expressions

CALCULATION_REGEXP_OPENBRACE  = '\('

CALCULATION_REGEXP_ROW_RANGE  = '(((([^\s\(,!&%^\/\*\+<>=:`-]*)|(\'(?:[^\']|\'[^!])+?\')|(\"(?:[^\"]|\"[^!])+?\"))!)?(\$?[1-9][0-9]{0,6})):(?![.*])'

CALCULATION_REGEXP_ROWRANGE_RELATIVE  = '(\$?\d{1,7}):(\$?\d{1,7})'

CALCULATION_REGEXP_STRING  = '"(?:[^"]|"")*"'

CALCULATION_REGEXP_STRIP_XLFN_XLWS  = '/(_xlfn[.])?(_xlws[.])?(?=[\p{L}][\p{L}\p{N}\.]*[\s]*[(])/'

CALCULATION_REGEXP_STRUCTURED_REFERENCE  = '([\p{L}_\\\\][\p{L}\p{N}\._]+)?(\[(?:[^\d\]+-])?)'

FORMULA_CLOSE_FUNCTION_BRACE  = ')'

FORMULA_CLOSE_MATRIX_BRACE  = '}'

FORMULA_OPEN_FUNCTION_BRACE  = '('

FORMULA_OPEN_MATRIX_BRACE  = '{'

FORMULA_STRING_QUOTE  = '"'

RETURN_ARRAY_AS_ARRAY  = 'array'

RETURN_ARRAY_AS_ERROR  = 'error'

constants

RETURN_ARRAY_AS_VALUE  = 'value'

BINARY_OPERATORS  = ['+' => true, '-' => true, '*' => true, '/' => true, '^' => true, '&' => true, '>' => true, '<' => true, '=' => true, '>=' => true, '<=' => true, '<>' => true, '∩' => true, '∪' => true, ':' => true]

List of binary operators (those that expect two operands).

CALCULATION_OPERATORS  = ['+' => true, '-' => true, '*' => true, '/' => true, '^' => true, '&' => true, '%' => false, '~' => false, '>' => true, '<' => true, '=' => true, '>=' => true, '<=' => true, '<>' => true, '∩' => true, '∪' => true, ':' => true]

List of operators that can be used within formulae The true/false value indicates whether it is a binary operator or a unary operator.

COMPARISON_OPERATORS  = ['>' => true, '<' => true, '=' => true, '>=' => true, '<=' => true, '<>' => true]

Comparison (Boolean) Operators.

EXCEL_CONSTANTS  = ['TRUE' => true, 'FALSE' => false, 'NULL' => null]

Excel constant string translations to their PHP equivalents Constant conversion from text name/value to actual (datatyped) value.

MATRIX_REPLACE_FROM  = [self::FORMULA_OPEN_MATRIX_BRACE, ';', self::FORMULA_CLOSE_MATRIX_BRACE]

MATRIX_REPLACE_TO  = ['MKMATRIX(MKMATRIX(', '),MKMATRIX(', '))']

OPERATOR_PRECEDENCE  = [ ':' => 9, // Range '∩' => 8, // Intersect '∪' => 7, // Union '~' => 6, // Negation '%' => 5, // Percentage '^' => 4, // Exponentiation '*' => 3, '/' => 3, // Multiplication and Division '+' => 2, '-' => 2, // Addition and Subtraction '&' => 1, // Concatenation '>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0, ]

Operator Precedence.

Properties

$cyclicFormulaCount  : int

Number of iterations for cyclic formulae.

$formulaError  : string|null

Error message for any error that was raised/thrown by the calculation engine.

$falseTrueArray  : array<int, array<int, string>>

$functionReplaceFromLocale  : null|array<string|int, string>

$functionReplaceToExcel  : null|array<string|int, string>

$localeArgumentSeparator  : string

Locale-specific argument separator for function arguments.

$localeBoolean  : array<string, string>

Locale-specific translations for Excel constants (True, False and Null).

$localeFunctions  : array<string|int, string>

$localeLanguage  : string

The current locale setting.

$validLocaleLanguages  : array<string|int, string>

List of available locale settings Note that this is read for the locale subdirectory only when requested.

$branchPruner  : BranchPruner

$branchPruningEnabled  : bool

$calculationCache  : array<string|int, mixed>

Calculation cache.

$calculationCacheEnabled  : bool

Calculation cache enabled.

$cellStack  : array<string|int, mixed>

$controlFunctions  : array<string, array<string, array<string|int, string>|string>>

Internal functions used for special control purposes.

$cyclicFormulaCell  : string

$cyclicFormulaCounter  : int

Current iteration counter for cyclic formulae If the value is 0 (or less) then cyclic formulae will throw an exception, otherwise they will iterate to the limit defined here before returning a result.

$cyclicReferenceStack  : CyclicReferenceStack

An array of the nested cell references accessed by the calculation engine, used for the debug log.

$debugLog  : Logger

The debug log generated by the calculation engine.

$functionReplaceFromExcel  : null|array<string|int, string>

$functionReplaceToLocale  : null|array<string|int, string>

$instance  : Calculation|null

Instance of this class.

$instanceArrayReturnType  : string|null

Preferable to use this instance variable rather than static returnArrayAsType

$matchIndex10  : int

$matchIndex8  : int

$matchIndex9  : int

$processingAnchorArray  : bool

$returnArrayAsType  : string

Preferable to use instance variable instanceArrayReturnType rather than this static property.

$spreadsheet  : Spreadsheet|null

Instance of the spreadsheet this Calculation Engine is using.

$suppressFormulaErrors  : bool

Methods

__clone()  : mixed

__clone implementation. Cloning should not be allowed in a Singleton!

__construct()  : mixed

_calculateFormulaValue()  : mixed

Parse a cell formula and calculate its value.

addFunction()  : bool

boolToString()  : mixed

calculate()  : mixed

Calculate cell value (using formula from a cell ID) Retained for backward compatibility.

calculateCellValue()  : mixed

Calculate the value of a cell formula.

calculateFormula()  : mixed

Calculate the value of a formula.

checkMatrixOperands()  : array<string|int, mixed>

Ensure that paired matrix operands are both matrices and of the same size.

clearCalculationCache()  : void

Clear calculation cache.

clearCalculationCacheForWorksheet()  : void

Clear calculation cache for a specified worksheet.

disableBranchPruning()  : void

disableCalculationCache()  : void

Disable calculation cache.

enableBranchPruning()  : void

enableCalculationCache()  : void

Enable calculation cache.

extractCellRange()  : array<string|int, mixed>

Extract range values.

extractNamedRange()  : array<string|int, mixed>|string

Extract range values.

flushInstance()  : void

Flush the calculation cache for any existing instance of this class but only if a Calculation instance exists.

getArrayReturnType()  : string

Return the Array Return Type (Array or Value of first element in the array).

getBranchPruningEnabled()  : bool

getCalculationCacheEnabled()  : bool

Is calculation caching enabled?

getDebugLog()  : Logger

Get the Logger for this calculation engine instance.

getExcelConstants()  : bool|null

getFALSE()  : string

Return the locale-specific translation of FALSE.

getFalseTrueArray()  : array<int, array<int, string>>

getFunctions()  : array<string, array{category: string, functionCall: string|string[], argumentCount: string, passCellReference?: bool, passByReference?: bool[], custom?: bool}>

Get a list of all implemented functions as an array of function objects.

getImplementedFunctionNames()  : array<string|int, string>

Get a list of implemented Excel function names.

getInstance()  : self

Get an instance of this class.

getInstanceArrayReturnType()  : string

Return the Array Return Type (Array or Value of first element in the array).

getLocale()  : string

Get the currently defined locale code.

getLocaleBoolean()  : string

getMatrixDimensions()  : array<string|int, int>

Read the dimensions of a matrix, and re-index it with straight numeric keys starting from row 0, column 0.

getSpreadsheet()  : Spreadsheet|null

getSuppressFormulaErrors()  : bool

getTRUE()  : string

Return the locale-specific translation of TRUE.

getValueFromCache()  : bool

isImplemented()  : bool

Is a specific function implemented?

keyInExcelConstants()  : bool

localeFunc()  : string

parseFormula()  : array<string|int, mixed>|bool

Validate and parse a formula string.

removeFunction()  : bool

renameCalculationCacheForWorksheet()  : void

Rename calculation cache for a specified worksheet.

saveValueToCache()  : void

setArrayReturnType()  : bool

Set the Array Return Type (Array or Value of first element in the array).

setBranchPruningEnabled()  : self

setCalculationCacheEnabled()  : self

Enable/disable calculation cache.

setInstanceArrayReturnType()  : bool

Set the Instance Array Return Type (Array or Value of first element in the array).

setLocale()  : bool

Set the locale code.

setSuppressFormulaErrors()  : self

translateFormulaToEnglish()  : string

translateFormulaToLocale()  : string

translateSeparator()  : string

unwrapResult()  : mixed

Remove quotes used as a wrapper to identify string values.

wrapResult()  : mixed

Wrap string values in quotes.

getFunctionsAddress()  : array<string, array<string, mixed>>

Get address of list of all implemented functions as an array of function objects.

getLocaleFile()  : string

loadLocales()  : void

raiseFormulaError()  : false

Trigger an error, but nicely, if need be.

translateFormula()  : string

translateFormulaBlock()  : string

addCellReference()  : array<string|int, mixed>

Add cell reference if needed while making sure that it is the last argument.

addDefaultArgumentValues()  : array<string|int, mixed>

convertMatrixReferences()  : false|string

dataTestReference()  : mixed

evaluateDefinedName()  : mixed

executeArrayComparison()  : array<string|int, mixed>

executeBinaryComparisonOperation()  : bool|array<string|int, mixed>

executeNumericBinaryOperation()  : mixed

getArgumentDefaultValue()  : mixed

internalParseFormula()  : array<int, mixed>|false

isDummy()  : bool

isNumericOrBool()  : bool

makeError()  : string

processTokenStack()  : array<int, mixed>|false|string

resizeMatricesExtend()  : void

Ensure that paired matrix operands are both matrices of the same size.

resizeMatricesShrink()  : void

Ensure that paired matrix operands are both matrices of the same size.

showTypeDetails()  : string|null

Format type and details of an operand for display in the log (based on operand type).

showValue()  : mixed

Format details of an operand for display in the log (based on operand type).

swapOperands()  : bool

validateBinaryOperand()  : bool