Documentation

Calculation extends CalculationLocale
in package

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.
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  : array<string|int, mixed>|null
$functionReplaceToExcel  : array<string|int, mixed>|null
$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.
$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|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.
$functionReplaceFromExcel  : array<string|int, mixed>|null
$functionReplaceToLocale  : array<string|int, mixed>|null
$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|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.
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()  : array<string|int, mixed>|bool
executeNumericBinaryOperation()  : mixed
getArgumentDefaultValue()  : mixed
internalParseFormula()  : array<int, mixed>|false
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

Constants

CALCULATION_REGEXP_CELLREF

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

CALCULATION_REGEXP_CELLREF_RELATIVE

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

CALCULATION_REGEXP_CELLREF_SPILL

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

CALCULATION_REGEXP_COLUMN_RANGE

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

CALCULATION_REGEXP_COLUMNRANGE_RELATIVE

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

CALCULATION_REGEXP_DEFINEDNAME

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

CALCULATION_REGEXP_ERROR

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

CALCULATION_REGEXP_FUNCTION

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

CALCULATION_REGEXP_NUMBER

Regular Expressions

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

CALCULATION_REGEXP_OPENBRACE

public mixed CALCULATION_REGEXP_OPENBRACE = '\('

CALCULATION_REGEXP_ROW_RANGE

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

CALCULATION_REGEXP_ROWRANGE_RELATIVE

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

CALCULATION_REGEXP_STRING

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

CALCULATION_REGEXP_STRIP_XLFN_XLWS

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

CALCULATION_REGEXP_STRUCTURED_REFERENCE

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

RETURN_ARRAY_AS_ARRAY

public mixed RETURN_ARRAY_AS_ARRAY = 'array'

RETURN_ARRAY_AS_ERROR

constants

public mixed RETURN_ARRAY_AS_ERROR = 'error'

RETURN_ARRAY_AS_VALUE

public mixed RETURN_ARRAY_AS_VALUE = 'value'

BINARY_OPERATORS

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

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

CALCULATION_OPERATORS

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

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

COMPARISON_OPERATORS

Comparison (Boolean) Operators.

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

These operators work on two values, but always return a boolean result.

EXCEL_CONSTANTS

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

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

OPERATOR_PRECEDENCE

Operator Precedence.

private mixed 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, ]

This list includes all valid operators, whether binary (including boolean) or unary (such as %). Array key is the operator, the value is its precedence.

Properties

$cyclicFormulaCount

Number of iterations for cyclic formulae.

public int $cyclicFormulaCount = 1

$formulaError

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

public string|null $formulaError = null

$falseTrueArray

protected static array<int, array<int, string>> $falseTrueArray = []

$functionReplaceFromLocale

protected static array<string|int, mixed>|null $functionReplaceFromLocale

$functionReplaceToExcel

protected static array<string|int, mixed>|null $functionReplaceToExcel

$localeArgumentSeparator

Locale-specific argument separator for function arguments.

protected static string $localeArgumentSeparator = ','

$localeBoolean

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

protected static array<string, string> $localeBoolean = ['TRUE' => 'TRUE', 'FALSE' => 'FALSE', 'NULL' => 'NULL']

$localeFunctions

protected static array<string|int, mixed> $localeFunctions = []

$localeLanguage

The current locale setting.

protected static string $localeLanguage = 'en_us'

$validLocaleLanguages

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

protected static array<string|int, string> $validLocaleLanguages = ['en']

$branchPruningEnabled

private bool $branchPruningEnabled = true

$calculationCache

Calculation cache.

private array<string|int, mixed> $calculationCache = []

$calculationCacheEnabled

Calculation cache enabled.

private bool $calculationCacheEnabled = true

$cellStack

private array<string|int, mixed> $cellStack = []

$controlFunctions

Internal functions used for special control purposes.

private static array<string|int, mixed> $controlFunctions = ['MKMATRIX' => ['argumentCount' => '*', 'functionCall' => [\PhpOffice\PhpSpreadsheet\Calculation\Internal\MakeMatrix::class, 'make']], 'NAME.ERROR' => ['argumentCount' => '*', 'functionCall' => [\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError::class, 'NAME']], 'WILDCARDMATCH' => ['argumentCount' => '2', 'functionCall' => [\PhpOffice\PhpSpreadsheet\Calculation\Internal\WildcardMatch::class, 'compare']]]

$cyclicFormulaCell

private string $cyclicFormulaCell = ''

$cyclicFormulaCounter

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.

private int $cyclicFormulaCounter = 1

$cyclicReferenceStack

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

private CyclicReferenceStack $cyclicReferenceStack

$debugLog

The debug log generated by the calculation engine.

private Logger $debugLog

$functionReplaceFromExcel

private static array<string|int, mixed>|null $functionReplaceFromExcel

$functionReplaceToLocale

private static array<string|int, mixed>|null $functionReplaceToLocale

$instanceArrayReturnType

Preferable to use this instance variable rather than static returnArrayAsType

private string|null $instanceArrayReturnType = null

$matchIndex10

private static int $matchIndex10 = 10

$processingAnchorArray

private bool $processingAnchorArray = false

$returnArrayAsType

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

private static string $returnArrayAsType = self::RETURN_ARRAY_AS_VALUE

$spreadsheet

Instance of the spreadsheet this Calculation Engine is using.

private Spreadsheet|null $spreadsheet

$suppressFormulaErrors

private bool $suppressFormulaErrors = false

Methods

__clone()

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

public final __clone() : mixed

_calculateFormulaValue()

Parse a cell formula and calculate its value.

public _calculateFormulaValue(string $formula[, string|null $cellID = null ][, Cell|null $cell = null ][, bool $ignoreQuotePrefix = false ]) : mixed
Parameters
$formula : string

The formula to parse and calculate

$cellID : string|null = null

The ID (e.g. A3) of the cell that we are calculating

$cell : Cell|null = null

Cell to calculate

$ignoreQuotePrefix : bool = false

If set to true, evaluate the formyla even if the referenced cell is quote prefixed

addFunction()

public static addFunction(string $key, array<string, array<string, mixed>> $value) : bool
Parameters
$key : string
$value : array<string, array<string, mixed>>
Return values
bool

boolToString()

public static boolToString(mixed $operand1) : mixed
Parameters
$operand1 : mixed

calculate()

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

public calculate([Cell|null $cell = null ]) : mixed
Parameters
$cell : Cell|null = null

Cell to calculate

calculateCellValue()

Calculate the value of a cell formula.

public calculateCellValue([Cell|null $cell = null ][, bool $resetLog = true ]) : mixed
Parameters
$cell : Cell|null = null

Cell to calculate

$resetLog : bool = true

Flag indicating whether the debug log should be reset or not

calculateFormula()

Calculate the value of a formula.

public calculateFormula(string $formula[, string|null $cellID = null ][, Cell|null $cell = null ]) : mixed
Parameters
$formula : string

Formula to parse

$cellID : string|null = null

Address of the cell to calculate

$cell : Cell|null = null

Cell to calculate

checkMatrixOperands()

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

public static checkMatrixOperands(mixed &$operand1, mixed &$operand2[, int $resize = 1 ]) : array<string|int, mixed>
Parameters
$operand1 : mixed

First matrix operand

$operand2 : mixed

Second matrix operand

$resize : int = 1

Flag indicating whether the matrices should be resized to match and (if so), whether the smaller dimension should grow or the larger should shrink. 0 = no resize 1 = shrink to fit 2 = extend to fit

Tags
param-out

array $operand1

param-out

array $operand2

Return values
array<string|int, mixed>

clearCalculationCache()

Clear calculation cache.

public clearCalculationCache() : void

clearCalculationCacheForWorksheet()

Clear calculation cache for a specified worksheet.

public clearCalculationCacheForWorksheet(string $worksheetName) : void
Parameters
$worksheetName : string

disableBranchPruning()

public disableBranchPruning() : void

disableCalculationCache()

Disable calculation cache.

public disableCalculationCache() : void

enableBranchPruning()

public enableBranchPruning() : void

enableCalculationCache()

Enable calculation cache.

public enableCalculationCache() : void

extractCellRange()

Extract range values.

public extractCellRange([string &$range = 'A1' ][, Worksheet|null $worksheet = null ][, bool $resetLog = true ]) : array<string|int, mixed>
Parameters
$range : string = 'A1'

String based range representation

$worksheet : Worksheet|null = null

Worksheet

$resetLog : bool = true

Flag indicating whether calculation log should be reset or not

Return values
array<string|int, mixed>

Array of values in range if range contains more than one element. Otherwise, a single value is returned.

extractNamedRange()

Extract range values.

public extractNamedRange([string &$range = 'A1' ][, null|Worksheet $worksheet = null ][, bool $resetLog = true ]) : array<string|int, mixed>|string
Parameters
$range : string = 'A1'

String based range representation

$worksheet : null|Worksheet = null

Worksheet

$resetLog : bool = true

Flag indicating whether calculation log should be reset or not

Return values
array<string|int, mixed>|string

Array of values in range if range contains more than one element. Otherwise, a single value is returned.

flushInstance()

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

public flushInstance() : void

getArrayReturnType()

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

public static getArrayReturnType() : string
Return values
string

$returnType Array return type

getBranchPruningEnabled()

public getBranchPruningEnabled() : bool
Return values
bool

getCalculationCacheEnabled()

Is calculation caching enabled?

public getCalculationCacheEnabled() : bool
Return values
bool

getDebugLog()

Get the Logger for this calculation engine instance.

public getDebugLog() : Logger
Return values
Logger

getExcelConstants()

public static getExcelConstants(string $key) : bool|null
Parameters
$key : string
Return values
bool|null

getFALSE()

Return the locale-specific translation of FALSE.

public static getFALSE() : string
Return values
string

locale-specific translation of FALSE

getFalseTrueArray()

public getFalseTrueArray() : array<int, array<int, string>>
Return values
array<int, array<int, string>>

getFunctions()

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

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

return array<string, array<string, mixed>>

Return values
array<string|int, mixed>

getImplementedFunctionNames()

Get a list of implemented Excel function names.

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

getInstance()

Get an instance of this class.

public static getInstance([Spreadsheet|null $spreadsheet = null ]) : self
Parameters
$spreadsheet : Spreadsheet|null = null

Injected spreadsheet for working with a PhpSpreadsheet Spreadsheet object, or NULL to create a standalone calculation engine

Return values
self

getInstanceArrayReturnType()

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

public getInstanceArrayReturnType() : string
Return values
string

$returnType Array return type for instance if non-null, otherwise static property

getLocale()

Get the currently defined locale code.

public getLocale() : string
Return values
string

getLocaleBoolean()

public static getLocaleBoolean(string $index) : string
Parameters
$index : string
Return values
string

getMatrixDimensions()

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

public static getMatrixDimensions(array<string|int, mixed> &$matrix) : array<string|int, int>
Parameters
$matrix : array<string|int, mixed>

matrix operand

Return values
array<string|int, int>

An array comprising the number of rows, and number of columns

getSuppressFormulaErrors()

public getSuppressFormulaErrors() : bool
Return values
bool

getTRUE()

Return the locale-specific translation of TRUE.

public static getTRUE() : string
Return values
string

locale-specific translation of TRUE

getValueFromCache()

public getValueFromCache(string $cellReference, mixed &$cellValue) : bool
Parameters
$cellReference : string
$cellValue : mixed
Return values
bool

isImplemented()

Is a specific function implemented?

public isImplemented(string $function) : bool
Parameters
$function : string

Function Name

Return values
bool

keyInExcelConstants()

public static keyInExcelConstants(string $key) : bool
Parameters
$key : string
Return values
bool

localeFunc()

public static localeFunc(string $function) : string
Parameters
$function : string
Return values
string

parseFormula()

Validate and parse a formula string.

public parseFormula(string $formula) : array<string|int, mixed>|bool
Parameters
$formula : string

Formula to parse

Return values
array<string|int, mixed>|bool

removeFunction()

public static removeFunction(string $key) : bool
Parameters
$key : string
Return values
bool

renameCalculationCacheForWorksheet()

Rename calculation cache for a specified worksheet.

public renameCalculationCacheForWorksheet(string $fromWorksheetName, string $toWorksheetName) : void
Parameters
$fromWorksheetName : string
$toWorksheetName : string

saveValueToCache()

public saveValueToCache(string $cellReference, mixed $cellValue) : void
Parameters
$cellReference : string
$cellValue : mixed

setArrayReturnType()

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

public static setArrayReturnType(string $returnType) : bool
Parameters
$returnType : string

Array return type

Return values
bool

Success or failure

setBranchPruningEnabled()

public setBranchPruningEnabled(mixed $enabled) : self
Parameters
$enabled : mixed
Return values
self

setCalculationCacheEnabled()

Enable/disable calculation cache.

public setCalculationCacheEnabled(bool $calculationCacheEnabled) : self
Parameters
$calculationCacheEnabled : bool
Return values
self

setInstanceArrayReturnType()

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

public setInstanceArrayReturnType(string $returnType) : bool
Parameters
$returnType : string

Array return type

Return values
bool

Success or failure

setLocale()

Set the locale code.

public setLocale(string $locale) : bool
Parameters
$locale : string

The locale to use for formula translation, eg: 'en_us'

Return values
bool

setSuppressFormulaErrors()

public setSuppressFormulaErrors(bool $suppressFormulaErrors) : self
Parameters
$suppressFormulaErrors : bool
Return values
self

translateFormulaToEnglish()

public translateFormulaToEnglish(string $formula) : string
Parameters
$formula : string
Return values
string

translateFormulaToLocale()

public translateFormulaToLocale(string $formula) : string
Parameters
$formula : string
Return values
string

translateSeparator()

public static translateSeparator(string $fromSeparator, string $toSeparator, string $formula, int &$inBracesLevel[, string $openBrace = self::FORMULA_OPEN_FUNCTION_BRACE ][, string $closeBrace = self::FORMULA_CLOSE_FUNCTION_BRACE ]) : string
Parameters
$fromSeparator : string
$toSeparator : string
$formula : string
$inBracesLevel : int
$openBrace : string = self::FORMULA_OPEN_FUNCTION_BRACE
$closeBrace : string = self::FORMULA_CLOSE_FUNCTION_BRACE
Return values
string

unwrapResult()

Remove quotes used as a wrapper to identify string values.

public static unwrapResult(mixed $value) : mixed
Parameters
$value : mixed

wrapResult()

Wrap string values in quotes.

public static wrapResult(mixed $value) : mixed
Parameters
$value : mixed

getFunctionsAddress()

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

protected static & getFunctionsAddress() : array<string, array<string, mixed>>
Return values
array<string, array<string, mixed>>

getLocaleFile()

protected getLocaleFile(string $localeDir, string $locale, string $language, string $file) : string
Parameters
$localeDir : string
$locale : string
$language : string
$file : string
Return values
string

raiseFormulaError()

Trigger an error, but nicely, if need be.

protected raiseFormulaError(string $errorMessage[, int $code = 0 ][, Throwable|null $exception = null ]) : false
Parameters
$errorMessage : string
$code : int = 0
$exception : Throwable|null = null
Return values
false

translateFormula()

protected static translateFormula(array<string|int, mixed> $from, array<string|int, mixed> $to, string $formula, string $fromSeparator, string $toSeparator) : string
Parameters
$from : array<string|int, mixed>
$to : array<string|int, mixed>
$formula : string
$fromSeparator : string
$toSeparator : string
Return values
string

translateFormulaBlock()

protected static translateFormulaBlock(array<string|int, mixed> $from, array<string|int, mixed> $to, string $formula, int &$inFunctionBracesLevel, int &$inMatrixBracesLevel, string $fromSeparator, string $toSeparator) : string
Parameters
$from : array<string|int, mixed>
$to : array<string|int, mixed>
$formula : string
$inFunctionBracesLevel : int
$inMatrixBracesLevel : int
$fromSeparator : string
$toSeparator : string
Return values
string

addCellReference()

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

private addCellReference(array<string|int, mixed> $args, bool $passCellReference, array<string|int, mixed>|string $functionCall[, Cell|null $cell = null ]) : array<string|int, mixed>
Parameters
$args : array<string|int, mixed>
$passCellReference : bool
$functionCall : array<string|int, mixed>|string
$cell : Cell|null = null
Return values
array<string|int, mixed>

addDefaultArgumentValues()

private addDefaultArgumentValues(array<string|int, mixed> $functionCall, array<string|int, mixed> $args, array<string|int, mixed> $emptyArguments) : array<string|int, mixed>
Parameters
$functionCall : array<string|int, mixed>
$args : array<string|int, mixed>
$emptyArguments : array<string|int, mixed>
Return values
array<string|int, mixed>

convertMatrixReferences()

private convertMatrixReferences(string $formula) : false|string
Parameters
$formula : string
Return values
false|string

False indicates an error

dataTestReference()

private static dataTestReference(array<string|int, mixed> &$operandData) : mixed
Parameters
$operandData : array<string|int, mixed>

executeArrayComparison()

private executeArrayComparison(mixed $operand1, mixed $operand2, string $operation, Stack &$stack, bool $recursingArrays) : array<string|int, mixed>
Parameters
$operand1 : mixed
$operand2 : mixed
$operation : string
$stack : Stack
$recursingArrays : bool
Return values
array<string|int, mixed>

executeBinaryComparisonOperation()

private executeBinaryComparisonOperation(mixed $operand1, mixed $operand2, string $operation, Stack &$stack[, bool $recursingArrays = false ]) : array<string|int, mixed>|bool
Parameters
$operand1 : mixed
$operand2 : mixed
$operation : string
$stack : Stack
$recursingArrays : bool = false
Return values
array<string|int, mixed>|bool

executeNumericBinaryOperation()

private executeNumericBinaryOperation(mixed $operand1, mixed $operand2, string $operation, Stack &$stack) : mixed
Parameters
$operand1 : mixed
$operand2 : mixed
$operation : string
$stack : Stack

getArgumentDefaultValue()

private getArgumentDefaultValue(ReflectionParameter $methodArgument) : mixed
Parameters
$methodArgument : ReflectionParameter

internalParseFormula()

private internalParseFormula(string $formula[, Cell|null $cell = null ]) : array<int, mixed>|false
Parameters
$formula : string
$cell : Cell|null = null
Return values
array<int, mixed>|false

isNumericOrBool()

private static isNumericOrBool(mixed $operand) : bool
Parameters
$operand : mixed
Return values
bool

makeError()

private static makeError([mixed $operand = '' ]) : string
Parameters
$operand : mixed = ''
Return values
string

processTokenStack()

private processTokenStack(false|array<string|int, mixed> $tokens[, string|null $cellID = null ][, Cell|null $cell = null ]) : array<int, mixed>|false|string
Parameters
$tokens : false|array<string|int, mixed>
$cellID : string|null = null
$cell : Cell|null = null
Return values
array<int, mixed>|false|string

resizeMatricesExtend()

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

private static resizeMatricesExtend(array<string|int, mixed> &$matrix1, array<string|int, mixed> &$matrix2, int $matrix1Rows, int $matrix1Columns, int $matrix2Rows, int $matrix2Columns) : void
Parameters
$matrix1 : array<string|int, mixed>

First matrix operand

$matrix2 : array<string|int, mixed>

Second matrix operand

$matrix1Rows : int

Row size of first matrix operand

$matrix1Columns : int

Column size of first matrix operand

$matrix2Rows : int

Row size of second matrix operand

$matrix2Columns : int

Column size of second matrix operand

resizeMatricesShrink()

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

private static resizeMatricesShrink(array<string|int, mixed> &$matrix1, array<string|int, mixed> &$matrix2, int $matrix1Rows, int $matrix1Columns, int $matrix2Rows, int $matrix2Columns) : void
Parameters
$matrix1 : array<string|int, mixed>

First matrix operand

$matrix2 : array<string|int, mixed>

Second matrix operand

$matrix1Rows : int

Row size of first matrix operand

$matrix1Columns : int

Column size of first matrix operand

$matrix2Rows : int

Row size of second matrix operand

$matrix2Columns : int

Column size of second matrix operand

showTypeDetails()

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

private showTypeDetails(mixed $value) : string|null
Parameters
$value : mixed

First matrix operand

Return values
string|null

showValue()

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

private showValue(mixed $value) : mixed
Parameters
$value : mixed

First matrix operand

swapOperands()

private static swapOperands(Stack $stack, string $opCharacter) : bool
Parameters
$stack : Stack
$opCharacter : string
Return values
bool

validateBinaryOperand()

private validateBinaryOperand(mixed &$operand, Stack &$stack) : bool
Parameters
$operand : mixed
$stack : Stack
Return values
bool

        
On this page

Search results