Documentation Index
Fetch the complete documentation index at: https://mintlify.com/PHPOffice/PhpSpreadsheet/llms.txt
Use this file to discover all available pages before exploring further.
The Calculation Engine is responsible for evaluating formulas in PhpSpreadsheet. It provides comprehensive support for Excel-compatible formula calculations.
Getting the Calculation Instance
getInstance()
Get a singleton instance of the Calculation engine.
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
// Get instance for a spreadsheet
$calculation = Calculation::getInstance($spreadsheet);
// Get standalone instance
$calculation = Calculation::getInstance();
$spreadsheet (Spreadsheet|null): Optional spreadsheet object to work with
Returns: Calculation instance
Calculation Methods
calculate()
Calculate the value of a cell.
$result = $calculation->calculate($cell);
$cell (Cell|null): The cell to calculate
Returns: Mixed - The calculated cell value
calculateCellValue()
Calculate a cell value with optional debug log reset.
$result = $calculation->calculateCellValue($cell, true);
$cell (Cell|null): The cell to calculate$resetLog (bool): Whether to reset the debug log (default: true)
Returns: Mixed - The calculated cell value
Calculate a formula string directly.
$result = $calculation->calculateFormula('=SUM(A1:A10)', 'B1', $cell);
$formula (string): The formula to calculate (with or without leading =)$cellID (string|null): Optional cell reference for context$cell (Cell|null): Optional cell object for context
Returns: Mixed - The calculated result
Example:
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
$calculation = Calculation::getInstance($spreadsheet);
// Calculate a simple formula
$result = $calculation->calculateFormula('=2+2');
// Returns: 4
// Calculate with cell references
$result = $calculation->calculateFormula('=A1*B1', 'C1');
// Calculate complex formula
$result = $calculation->calculateFormula('=SUM(A1:A10) + AVERAGE(B1:B10)');
$tokens = $calculation->parseFormula('=SUM(A1:A10)');
$formula (string): The formula to parse
Returns: array|bool - Array of formula tokens or false on failure
Calculation Cache
The calculation engine includes a cache to improve performance when recalculating formulas.
enableCalculationCache()
Enable the calculation cache.
$calculation->enableCalculationCache();
disableCalculationCache()
Disable the calculation cache.
$calculation->disableCalculationCache();
clearCalculationCache()
Clear all cached calculation results.
$calculation->clearCalculationCache();
getCalculationCacheEnabled()
Check if calculation cache is enabled.
$isEnabled = $calculation->getCalculationCacheEnabled();
bool - True if cache is enabled
Example:
// Disable cache for one-time calculations
$calculation->disableCalculationCache();
$result = $calculation->calculate($cell);
// Re-enable cache for multiple calculations
$calculation->enableCalculationCache();
foreach ($cells as $cell) {
$result = $calculation->calculate($cell);
}
clearCalculationCacheForWorksheet()
Clear cache for a specific worksheet.
$calculation->clearCalculationCacheForWorksheet('Sheet1');
$worksheetName (string): Name of the worksheet to clear cache for
Branch Pruning
Branch pruning optimizes conditional formula evaluation by skipping unnecessary branches.
enableBranchPruning()
Enable branch pruning optimization.
$calculation->enableBranchPruning();
disableBranchPruning()
Disable branch pruning optimization.
$calculation->disableBranchPruning();
getBranchPruningEnabled()
Check if branch pruning is enabled.
$isEnabled = $calculation->getBranchPruningEnabled();
bool - True if branch pruning is enabled
Example:
// Branch pruning improves performance for formulas like:
// =IF(A1>100, ExpensiveCalculation1(), ExpensiveCalculation2())
$calculation->enableBranchPruning();
// Only the TRUE or FALSE branch will be evaluated, not both
Array Return Type
setInstanceArrayReturnType()
Set how array formulas should return results.
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
$calculation->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
$returnType (string): One of:
Calculation::RETURN_ARRAY_AS_VALUE - Return first element onlyCalculation::RETURN_ARRAY_AS_ERROR - Return error for arraysCalculation::RETURN_ARRAY_AS_ARRAY - Return full array
Returns: bool - Success or failure
getInstanceArrayReturnType()
Get current array return type setting.
$returnType = $calculation->getInstanceArrayReturnType();
string - Current array return type
isImplemented()
Check if a function is implemented.
$isImplemented = $calculation->isImplemented('SUM');
$function (string): Function name to check
Returns: bool - True if function is implemented
getImplementedFunctionNames()
Get list of all implemented function names.
$functions = $calculation->getImplementedFunctionNames();
array - Array of function names
Example:
// Check if a function is supported before using it
if ($calculation->isImplemented('XLOOKUP')) {
$result = $calculation->calculateFormula('=XLOOKUP("value", A1:A10, B1:B10)');
} else {
// Use alternative function
$result = $calculation->calculateFormula('=VLOOKUP("value", A1:B10, 2, FALSE)');
}
// Get all available functions
$allFunctions = $calculation->getImplementedFunctionNames();
echo count($allFunctions) . " functions available";
Error Handling
Control whether formula errors should be suppressed.
$calculation->setSuppressFormulaErrors(true);
$suppressFormulaErrors (bool): Whether to suppress errors
Returns: Calculation - Returns self for method chaining
Check if formula errors are being suppressed.
$isSuppressed = $calculation->getSuppressFormulaErrors();
bool - True if errors are suppressed
Debug Logging
getDebugLog()
Get the debug logger instance.
use PhpOffice\PhpSpreadsheet\Calculation\Engine\Logger;
$logger = $calculation->getDebugLog();
Logger - The debug logger instance
Example:
// Enable debug logging
$logger = $calculation->getDebugLog();
$logger->setWriteDebugLog(true);
// Calculate a formula
$result = $calculation->calculateFormula('=SUM(A1:A10)');
// Get debug information
$debugInfo = $logger->getDebugLog();
$values = $calculation->extractCellRange($range, $worksheet);
$range (string): Cell range (e.g., ‘A1:B10’)$worksheet (Worksheet|null): Optional worksheet object$resetLog (bool): Whether to reset debug log (default: true)$createCell (bool): Whether to create cells if they don’t exist (default: false)
Returns: array - Array of cell values
Extract values from a named range.
$values = $calculation->extractNamedRange($rangeName, $worksheet);
$range (string): Named range name$worksheet (Worksheet|null): Optional worksheet object$resetLog (bool): Whether to reset debug log (default: true)
Returns: string|array - Range definition or array of values
Complete Example
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// Set some values
$sheet->setCellValue('A1', 10);
$sheet->setCellValue('A2', 20);
$sheet->setCellValue('A3', 30);
// Get calculation engine
$calculation = Calculation::getInstance($spreadsheet);
// Enable optimizations
$calculation->enableCalculationCache();
$calculation->enableBranchPruning();
// Calculate formulas
$sheet->setCellValue('B1', '=SUM(A1:A3)');
$result = $calculation->calculateCellValue($sheet->getCell('B1'));
echo "Sum: $result\n"; // Output: Sum: 60
// Direct formula calculation
$average = $calculation->calculateFormula('=AVERAGE(A1:A3)');
echo "Average: $average\n"; // Output: Average: 20
// Check function support
if ($calculation->isImplemented('TEXTJOIN')) {
$joined = $calculation->calculateFormula('=TEXTJOIN(", ", TRUE, A1:A3)');
echo "Joined: $joined\n";
}
// Clear cache when done
$calculation->clearCalculationCache();
See Also
