Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
The following code can be used to submit an issue with the PHPSpreadsheet calculation engine
<?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());
    }
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment