Documentation

Calculation

Table of Contents

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

$cyclicFormulaCount  : int

Number of iterations for cyclic formulae.

$formulaError  : null|string

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

$suppressFormulaErrors  : bool|null

Flag to determine how formula errors should be handled If true, then a user error will be triggered If false, then an exception will be thrown.

$branchPruner  : BranchPruner

$branchPruningEnabled  : bool

$calculationCache  : array<string|int, mixed>

Calculation cache.

$calculationCacheEnabled  : bool

Calculation cache enabled.

$cellStack  : array<string|int, mixed>

$comparisonOperators  : array<string|int, mixed>

Comparison (Boolean) Operators.

$controlFunctions  : array<string|int, mixed>

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.

$excelConstants  : array<string, mixed>

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

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

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

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

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

$instance  : Calculation|null

Instance of this class.

$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, mixed>

$localeLanguage  : string

The current locale setting.

$operatorAssociativity  : array<string|int, mixed>

Binary Operators.

$operatorPrecedence  : array<string|int, mixed>

Operator Precedence.

$phpSpreadsheetFunctions  : array<string|int, mixed>

Array of functions usable on Spreadsheet.

$referenceHelper  : ReferenceHelper

Reference Helper.

$returnArrayAsType  : string

$spreadsheet  : Spreadsheet|null

Instance of the spreadsheet this Calculation Engine is using.

$suppressFormulaErrorsNew  : bool

$validLocaleLanguages  : array<string|int, string>

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

__clone()  : mixed

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

__construct()  : mixed

_calculateFormulaValue()  : mixed

Parse a cell formula and calculate its value.

_translateFormulaToEnglish()  : string

_translateFormulaToLocale()  : string

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.

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

Extract range values.

extractNamedRange()  : mixed

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

getCalculationCacheEnabled()  : bool

Is calculation caching enabled?

getDebugLog()  : Logger

Get the Logger for this calculation engine instance.

getExcelConstants()  : mixed

getFALSE()  : string

Return the locale-specific translation of FALSE.

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

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

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

Get a list of implemented Excel function names.

getInstance()  : self

Get an instance of this class.

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.

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.

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

Enable/disable calculation cache.

setCalculationCacheEnabled()  : void

Enable/disable calculation cache.

setLocale()  : bool

Set the locale code.

setSuppressFormulaErrors()  : void

translateSeparator()  : string

unwrapResult()  : mixed

Remove quotes used as a wrapper to identify string values.

wrapResult()  : mixed

Wrap string values in quotes.

raiseFormulaError()  : false

Trigger an error, but nicely, if need be.

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

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

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

boolToString()  : mixed

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

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

convertMatrixReferences()  : false|string

dataTestReference()  : mixed

doNothing()  : bool

evaluateDefinedName()  : mixed|string

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

executeBinaryComparisonOperation()  : mixed

executeNumericBinaryOperation()  : bool|mixed

getArgumentDefaultValue()  : null|mixed

getLocaleFile()  : string

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

isNumericOrBool()  : bool

loadLocales()  : void

makeError()  : string

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

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()  : null|string

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

translateFormula()  : string

translateFormulaBlock()  : string

validateBinaryOperand()  : bool