PHPExcel formula debugging (prior to 1.7.9)

PHPExcel_Formula_Debugging.md

PHPExcel formula debugging

Prior to version 1.7.9

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

define('EOL', (PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

/** Include PHPExcel */
require_once '../PHPExcel/Classes/PHPExcel.php';


// Create new PHPExcel object
echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();

// Add some data
echo date('H:i:s') , " Add some data" , EOL;
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 5)
            ->setCellValue('B1', 6)
            ->setCellValue('C1', "=A1-B1");
            
// Rename worksheet
echo date('H:i:s') , " Rename worksheet" , EOL;
$objPHPExcel->getActiveSheet()->setTitle('Simple');


function testFormula($sheet,$cell) {
    $formulaValue = $sheet->getCell($cell)->getValue();
    echo 'Formula Value is ' , $formulaValue , EOL;
    $expectedValue = $sheet->getCell($cell)->getOldCalculatedValue();
    echo 'Expected Value is '  , 
        ((!is_null($expectedValue)) ? 
            $expectedValue : 
            'UNKNOWN'
        ) , 
        PHP_EOL;

    $calculate = false;
    try {
        $tokens = PHPExcel_Calculation::getInstance()
            ->parseFormula(
                $formulaValue, 
                $sheet->getCell($cell)
            );
        echo 'Parser Stack :-' , EOL;
        print_r($tokens);
        echo EOL;
        $calculate = true;
    } catch (Exception $e) {
        echo 'PARSER ERROR: ' , $e->getMessage() , EOL;

        echo 'Parser Stack :-' , EOL;
        print_r($tokens);
        echo EOL;
    }

    if ($calculate) {
        try {
            $cellValue = $sheet->getCell($cell)->getCalculatedValue();
            echo 'Calculated Value is ' , $cellValue , EOL;

            echo 'Evaluation Log:' , EOL;
            print_r(PHPExcel_Calculation::getInstance()->debugLog);
            echo EOL;
        } catch (Exception $e) {
            echo 'CALCULATION ENGINE ERROR: ' , $e->getMessage() , EOL;

            echo 'Evaluation Log:' , EOL;
            print_r(PHPExcel_Calculation::getInstance()->debugLog);
            echo EOL;
        }
    }
}


$sheet = $objPHPExcel->getActiveSheet();
PHPExcel_Calculation::getInstance()->writeDebugLog = true;


testFormula($sheet,'C1');

From version 1.7.9

PHPExcel 1.7.9 modified the calculation engine from a singleton to a multiton (required to avoid clashes when working with multiple workbooks concurrently). As such, each workbook runs its own instance of the calc engine, and when making calls to

PHPExcel_Calculation::getInstance()

you now have to specify which instance you need to access. Each PHPExcel instance has an ID value, maintained in the PHPExcel object that it is used for this purpose, and you need to pass the PHPExcel object itself to the getInstance() method. So, if your PHPExcel workbook instance is in the variable $objPHPExcel, you'd need to use

PHPExcel_Calculation::getInstance($objPHPExcel)

to reference the correct calc engine instance when flushing the cache, or enabling the logger, or reading the log.

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

define('EOL', (PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

/** Include PHPExcel */
require_once '../PHPExcel/Classes/PHPExcel.php';


// Create new PHPExcel object
echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();

// Add some data
echo date('H:i:s') , " Add some data" , EOL;
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 5)
            ->setCellValue('B1', 6)
            ->setCellValue('C1', "=A1-B1");
            
// Rename worksheet
echo date('H:i:s') , " Rename worksheet" , EOL;
$objPHPExcel->getActiveSheet()->setTitle('Simple');


function testFormula($sheet,$cell) {
    $formulaValue = $sheet->getCell($cell)->getValue();
    echo 'Formula Value is' , $formulaValue , PHP_EOL;
    $expectedValue = $sheet->getCell($cell)->getOldCalculatedValue();
    echo 'Expected Value is '  , 
        ((!is_null($expectedValue)) ? 
            $expectedValue : 
            'UNKNOWN'
        ) , 
        PHP_EOL;

    $calculate = false;
    try {
        $tokens = PHPExcel_Calculation::getInstance(
            $sheet->getParent()
        )->parseFormula(
            $formulaValue,
            $sheet->getCell($cell)
        );
        echo 'Parser Stack :-' , PHP_EOL;
        print_r($tokens);
        echo PHP_EOL;
        $calculate = true;
    } catch (Exception $e) {
        echo 'PARSER ERROR: ' , $e->getMessage() , PHP_EOL;

        echo 'Parser Stack :-' , PHP_EOL;
        print_r($tokens);
        echo PHP_EOL;
    }

    if ($calculate) {
        PHPExcel_Calculation::getInstance(
            $sheet->getParent()
        )->getDebugLog()
        ->setWriteDebugLog(true);
        try {
            $cellValue = $sheet->getCell($cell)->getCalculatedValue();
            echo 'Calculated Value is ' , $cellValue , PHP_EOL;

            echo 'Evaluation Log:' , PHP_EOL;
            print_r(
                PHPExcel_Calculation::getInstance(
                    $sheet->getParent()
                )->getDebugLog()
                ->getLog()
            );
            echo PHP_EOL;
        } catch (Exception $e) {
            echo 'CALCULATION ENGINE ERROR: ' , $e->getMessage() , PHP_EOL;

            echo 'Evaluation Log:' , PHP_EOL;
            print_r(
                PHPExcel_Calculation::getInstance(
                    $sheet->getParent()
                )->getDebugLog()
                ->getLog()
            );
            echo PHP_EOL;
        }
    }
}


$sheet = $objPHPExcel->getActiveSheet();
testFormula($sheet,'A2');