Exception thrown in getCalculatedValue() (cell.php)

Topics: Developer Forum
Jan 16, 2011 at 11:24 AM

I have an odd exception I cannot trace the root of, Using version 1.7.4: System runs nicely through part of the equation in the cell then just gives up throwing an exception.

Having played around quite a bit with this I wonder if its a problem recalculating the same cell value for a second time as the problem seems to occur when the system is about to recalc the Course_leadership_min for the second time in the final clause of the second IF underlined / bold below. Alternatively have I just hit the nesting limit or some stack problem?

Any thoughts anyone?

 

Mike Sharp

Details:

This is the equation:

=IF(C12<>0,((V2TM_lecture_points*E12+(((INT(C12/(V2TM_seminar_Split_count+(V2TM_seminar_Split_count/5)))+1))*V2TM_lecture_points*F12)+(C12*V2TM_individual_student_assessment_points)*G12)*(D12/Base_credit_value))+IF(B12<>0,(B12*(Course_Leadership_min+(((Course_Leadership_max-Course_Leadership_min)/Max_group_size_for_scale_calcs)*C12))),0),0)

and here is the output from the getCalculatedvalue() echo's

Cell J12 value is a f with a value of =IF(C12<>0,((V2TM_lecture_points*E12+(((INT(C12/(V2TM_seminar_Split_count+(V2TM_seminar_Split_count/5)))+1))*V2TM_lecture_points*F12)+(C12*V2TM_individual_student_assessment_points)*G12)*(D12/Base_credit_value))+IF(B12<>0,(B12*(Course_Leadership_min+(((Course_Leadership_max-Course_Leadership_min)/Max_group_size_for_scale_calcs)*C12))),0),0)

Cell value for J12 is a formula: Calculating value
Cell C12 value is a n with a value of 50
Cell value for C12 is not a formula: Returning data value of 50
Cell E12 value is a n with a value of 0.5
Cell value for E12 is not a formula: Returning data value of 0.5
Cell C12 value is a n with a value of 50
Cell value for C12 is not a formula: Returning data value of 50
Cell F12 value is a n with a value of 0.5
Cell value for F12 is not a formula: Returning data value of 0.5
Cell C12 value is a n with a value of 50
Cell value for C12 is not a formula: Returning data value of 50
Cell G12 value is a n with a value of 0.5
Cell value for G12 is not a formula: Returning data value of 0.5
Cell D12 value is a n with a value of 15
Cell value for D12 is not a formula: Returning data value of 15
Cell C26 value is a f with a value of =IF(B26<>1,D26,(INT(100*D26*ScaleFactor)/100))
Cell value for C26 is a formula: Calculating value
Cell B26 value is a n with a value of 0
Cell value for B26 is not a formula: Returning data value of 0
Cell D26 value is a n with a value of 30
Cell value for D26 is not a formula: Returning data value of 30
Cell D26 value is a n with a value of 30
Cell value for D26 is not a formula: Returning data value of 30
Cell B3 value is a n with a value of 10
Cell value for B3 is not a formula: Returning data value of 10
C26 calculation result is 30
Returning calculated value of 30 for cell C26
Cell B12 value is a n with a value of 1
Cell value for B12 is not a formula: Returning data value of 1
Cell B12 value is a n with a value of 1
Cell value for B12 is not a formula: Returning data value of 1
Cell C78 value is a f with a value of =IF(B78<>1,D78,(INT(100*D78*ScaleFactor)/100))
Cell value for C78 is a formula: Calculating value
Cell B78 value is a n with a value of 1
Cell value for B78 is not a formula: Returning data value of 1
Cell D78 value is a n with a value of 0.5
Cell value for D78 is not a formula: Returning data value of 0.5
Cell D78 value is a n with a value of 0.5
Cell value for D78 is not a formula: Returning data value of 0.5
Cell B3 value is a n with a value of 10
Cell value for B3 is not a formula: Returning data value of 10
C78 calculation result is 5
Returning calculated value of 5 for cell C78
Cell C79 value is a f with a value of =IF(B79<>1,D79,(INT(100*D79*ScaleFactor)/100))
Cell value for C79 is a formula: Calculating value
Cell B79 value is a n with a value of 1
Cell value for B79 is not a formula: Returning data value of 1
Cell D79 value is a n with a value of 2
Cell value for D79 is not a formula: Returning data value of 2
Cell D79 value is a n with a value of 2
Cell value for D79 is not a formula: Returning data value of 2
Cell B3 value is a n with a value of 10
Cell value for B3 is not a formula: Returning data value of 10
C79 calculation result is 20
Returning calculated value of 20 for cell C79
Cell C78 value is a f with a value of =IF(B78<>1,D78,(INT(100*D78*ScaleFactor)/100))
Cell value for C78 is a formula: Calculating value
C78 calculation result is 5
Returning calculated value of 5 for cell C78
Cell C12 value is a f with a value of =IF(B12<>1,D12,(INT(100*D12*ScaleFactor)/100))
Cell value for C12 is a formula: Calculating value
Cell B12 value is a n with a value of 0
Cell value for B12 is not a formula: Returning data value of 0
Cell D12 value is a n with a value of 200
Cell value for D12 is not a formula: Returning data value of 200
Cell D12 value is a n with a value of 200
Cell value for D12 is not a formula: Returning data value of 200
Cell B3 value is a n with a value of 10
Cell value for B3 is not a formula: Returning data value of 10
C12 calculation result is 200
Returning calculated value of 200 for cell C12
Cell C12 value is a n with a value of 50
Cell value for C12 is not a formula: Returning data value of 50
Calculation Exception: internal error

Fatal error: Uncaught exception 'Exception' with message 'Individual worksheet!J12 -> internal error' in /home/sm11/public_html/BAWS/PHPExcel/Classes/PHPExcel/Cell.php:272 Stack trace: #0 /home/sm11/public_html/BAWS/class_lib.php(125): PHPExcel_Cell->getCalculatedValue() #1 /home/sm11/public_html/BAWS/class_lib.php(72): spreadsheetFileManager->transferSheet(Object(PHPExcel), '10047') #2 /home/sm11/public_html/BAWS/index.php(16): spreadsheetFileManager->uploadSheet('SED_10047_??? ...', 'Uploads/') #3 {main} thrown in /home/sm11/public_html/BAWS/PHPExcel/Classes/PHPExcel/Cell.php on line 272

Using version 1.7.4

Coordinator
Jan 17, 2011 at 1:13 PM

You'd normally only get this exception with a formula that hasn't parsed correctly, but it's the fallback error, where the parser can't identify what the problem actually is.

Having played around quite a bit with this I wonder if its a problem recalculating the same cell value for a second time as the problem seems to occur when the system is about to recalc the Course_leadership_min for the second time in the final clause of the second IF underlined / bold below. Alternatively have I just hit the nesting limit or some stack problem?

This wouldn't normally be an issue. The default setting is that PHPExcel will cache the calculation results for each cell, so subsequent requests that reference the same cell will read the cache rather than recalculate. There is a time limit for this cache, but I don't think that's likely to cause this problem.

Is it possible for you to try this with the latest SVN code? It provides better logging information within the Calculation Engine.

Jan 17, 2011 at 4:53 PM

Hi Mark.

Many thanks for the reply. Tried it with the latest version of cell.php and calculation.php as the full new version gives me other headaches. I restored the remmed out echo / print lines and added MWS at end of error call in calculation function to be sure it was the source of the error and by the looks of it the line 3300 is indeed where the problem is spotted:

3300 if ($stack->count() != 1) return $this->_raiseFormulaError("internal error MWS");

Here is the final portion of the output I get when I run it all:

....

Array ( [type] => Binary Operator [value] => / [reference] => )
Token is a binary operator
Array ( [type] => Cell Reference [value] => C12 [reference] => C12 )
Reference is a single Cell
$cellRef=C12 in current worksheet
Array ( [type] => Binary Operator [value] => * [reference] => )
Token is a binary operator
Array ( [type] => Binary Operator [value] => + [reference] => )
Token is a binary operator
Array ( [type] => Binary Operator [value] => * [reference] => )
Token is a binary operator
Array ( [type] => Value [value] => 0 [reference] => )
Array ( [type] => Operand Count for Function IF() [value] => 3 [reference] => )
Array ( [type] => Function [value] => IF( [reference] => )
Token is a function
Function IF expects 3 arguments
Arguments are: Array ( [2] => Array ( [12] => Array ( [B] => 1 ) ) [1] => #DIV/0! [0] => 0 )

Warning: call_user_func_array() expects parameter 1 to be a valid callback, class 'PHPExcel_Calculation_Logical' not found in /home/sm11/public_html/BAWS/PHPExcel/Classes/PHPExcel/Calculation.php on line 3259 [This warning appears often in the preceding text (not shown) seems not to damage the eventual outcome.]
Array ( [type] => Binary Operator [value] => + [reference] => )
Token is a binary operator
Array ( [type] => Value [value] => 0 [reference] => )
Array ( [type] => Operand Count for Function IF() [value] => 3 [reference] => )
Array ( [type] => Function [value] => IF( [reference] => )
Token is a function
Function IF expects 3 arguments
Arguments are: Array ( [2] => #REF! [1] => #REF! [0] => 0 )

Warning: call_user_func_array() expects parameter 1 to be a valid callback, class 'PHPExcel_Calculation_Logical' not found in /home/sm11/public_html/BAWS/PHPExcel/Classes/PHPExcel/Calculation.php on line 3259 [This warning appears often in the preceding text (not shown) seems not to damage the eventual outcome.]

Fatal error: Uncaught exception 'Exception' with message 'Individual worksheet!J12 -> internal error MWS' in /home/sm11/public_html/BAWS/PHPExcel/Classes/PHPExcel/Cell.php:284 Stack trace: #0 /home/sm11/public_html/BAWS/class_lib.php(130): PHPExcel_Cell->getCalculatedValue() #1 /home/sm11/public_html/BAWS/class_lib.php(72): spreadsheetFileManager->transferSheet(Object(PHPExcel), '10047') #2 /home/sm11/public_html/BAWS/index.php(16): spreadsheetFileManager->uploadSheet('SED_10047_Jim G...', 'Uploads/') #3 {main} thrown in /home/sm11/public_html/BAWS/PHPExcel/Classes/PHPExcel/Cell.php on line 284

Just to confirm the actual spreadsheet works fine so at least as far as Excel is concerned the equation is well formed enough, but there may be a wrinkle I have missed.

Not sure this helps any further thoughts?

Mike S