<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
error_reporting(E_ALL);
set_time_limit(0);
date_default_timezone_set('UTC');
// Adjust the path as required to reference the PHPSpreadsheet Bootstrap file
require_once __DIR__ . '/../src/Bootstrap.php';
$spreadSheet = new Spreadsheet();
$workSheet = $spreadSheet->getActiveSheet();
// Set details for the formula that we want to evaluate, together with any data on which it depends
$workSheet->setCellValue('A1', 1)
->setCellValue('A2', 2)
->setCellValue('A3', '=IFERROR(AVERAGE(A1:A2),NA())');
// Initialise the calculation engine for debug logging
$calculationEngine = Calculation::getInstance($spreadSheet);
$debugLog = $calculationEngine->getDebugLog();
$calculationEngine->flushInstance();
$debugLog->setWriteDebugLog(true);
// The cell address in the worksheet that contains the formula
$cell = 'A3';
$formulaValue = $workSheet->getCell($cell)->getValue();
echo PHP_EOL, 'Formula value for evaluation is ', $formulaValue, PHP_EOL;
$canExecuteCalculation = false;
try {
$tokens = $calculationEngine->parseFormula($formulaValue, $workSheet->getCell($cell));
echo 'Parser Stack :-', PHP_EOL;
print_r($tokens);
$canExecuteCalculation = true;
} catch (Exception $e) {
echo "PARSER ERROR: ", $e->getMessage(), PHP_EOL;
echo 'Parser Stack :-';
print_r($tokens);
}
if ($canExecuteCalculation) {
// calculate
try {
$cellValue = $workSheet->getCell($cell)->getCalculatedValue();
echo PHP_EOL, 'Result is ', $cellValue, PHP_EOL;
echo 'Evaluation Log:', PHP_EOL;
print_r($debugLog->getLog());
} catch (Exception $e) {
echo "CALCULATION ENGINE ERROR: ", $e->getMessage(), PHP_EOL;
echo 'Evaluation Log:', PHP_EOL;
print_r($debugLog->getLog());
}
}
Last active
May 17, 2022 21:55
-
-
Save MarkBaker/3ea8664c73bfe6af5bc6f437803589f2 to your computer and use it in GitHub Desktop.
The following code can be used to submit an issue with the PHPSpreadsheet calculation engine
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment