PHPExcel doesn't evaluate formula

Question

I'm trying to evaluate the following formular in my XSLX workbook with PHPExcel 1.7.9: =IF($H$7=1,$F$13,$H$227*$J$227)

Before I have to update some cells in the workbook and then I use this line to calculate the value:

$sheet->getCell('L228')->getCalculatedValue();

However, PHPExcel seems not to update the cell which contains the formula correctly. It throws out some notice messages like the following but doesn't update the cell's calculated value:

Notice: Undefined offset: 2 in D:\my-path\hkc\Classes\PHPExcel\Calculation.php on line 2852

There's no exception and it seems that getCalculatedValue() returns the old cached value calculated by MS Excel. As pointed out below, I've activated debug logging of the calculation engine but it seems to be empty:

PHPExcel_CalcEngine_Logger Object
(
    [_writeDebugLog:PHPExcel_CalcEngine_Logger:private] => 
    [_echoDebugLog:PHPExcel_CalcEngine_Logger:private] => 
    [_debugLog:PHPExcel_CalcEngine_Logger:private] => Array
        (
        )

    [_cellStack:PHPExcel_CalcEngine_Logger:private] => PHPExcel_CalcEngine_CyclicReferenceStack Object
        (
            [_stack:PHPExcel_CalcEngine_CyclicReferenceStack:private] => Array
                (
                )

        )

)

I've tried the following steps to solve the problem:

Deactivate calculation cache: PHPExcel_Calculation::getInstance()->setCalculationCacheEnabled(FALSE);
Activate debug mode of calculation engine: This is described in How to handle exception with PhpExcel? and especially http://phpexcel.codeplex.com/discussions/233047
However, I had to modify the code of the referenced pages slightly - maybe something has changed in PHPExcel 1.7.9?
Activate debug: PHPExcel_Calculation::getInstance()->writeDebugLog = true;
Get debug log: print_r(PHPExcel_Calculation::getInstance()->getDebugLog());

I don't get it why the actual log of the engine is empty? Either the engine stops working before it writes out any entries or something is wrong with my debugging configuration?

However, the I've tried to evaluate the same formula with Java and Apache POI - it worked! Unfortunately I've to use PHP for the current project.

Please help me! Maybe somebody knows what's wrong with the formula evaluation or at least can give me some hints how to activate debugging properly?

Thanks!

Mark Baker Mark Baker · Accepted Answer · 2013-12-05T09:16:30

Not an answer yet, but too long to be written a comment:

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 is $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.

re. the specifics of your error: Do any of the cells referenced by your IF formula cell reference other formulae containing CUBE functions or the SUMIFS function? Those are the only functions that I'm aware of that might trigger this particular error

EDIT

Example of refactoring a SUMIFS() function

=SUMIFS(A2:A3, B2:B3, E1, C2:C3, F1)

which sums A2:A3 if B2:B3 meets the E1 criteria, and C2:C3 meets the F1 criteria.

This can be refactored as

=SUMPRODUCT(A2:A3 * (B2:B3=E1) * (C2:C3=F1))

PHPExcel doesn't evaluate formula

2 Answers