Problem in dealing with exceptions

Topics: Developer Forum, Project Management Forum, User Forum
May 10, 2012 at 2:07 PM

hello,I am using xls file which contain very complex calculations and sheet throws a no of exception when using the phpExcel. To get rid of these exception I used old calculated  values. But now i m not getting the desired result as in excel file.

Am I missing something.

Coordinator
May 10, 2012 at 3:08 PM

Knowing what exceptions you were getting might make it easier to answer

May 10, 2012 at 3:17 PM

I am getting NUM ,REF exceptions... In excel file formula is generating correct results.. But when I input values using phpExcel and then get the output it is generating REF.

Coordinator
May 10, 2012 at 4:44 PM

NUM and REF are not exceptions, they're typically Excel error codes.... if you want help, please supply a bit more detail such as the formulae that give these results, and what you expect to get instead.

May 11, 2012 at 10:04 AM

Thanks for clarifying the answer.

I am getting the following exception:

Uncaught exception 'Exception' with message 'Cyclic Reference in Formula'

I am dealing with a excel sheet which contain very complex calculations. I can not write them all here. I am using an interface to send the values to specific cells of that excel sheet. and then getting  back the calculated value from the output cells.

 

use of getOldCalculatedValue() gets the correct values first Time I use it (I have no idea how)

The problem with getOldCalculatedValue() is that this is not the solution. also when i update my input values , output value does not change.

May 11, 2012 at 12:35 PM

now i checked deeply.. these are the formulas which are generating exceptions:

Fatal error: Uncaught exception 'Exception' with message 'STEAM!B30 -> STEAM!E57 -> STEAM!B56 -> STEAM!B38 -> STEAM!B89 -> internal error'

 

B30:=IF(G32=1,IF(E57<K33,E56,H57),IF(E45<B52,IF(AND(G30<>0,D55<>0),IF(G32=1,IF(E57<K33,E56,H57),IF(E57<E59,E56,H57)),IF(E45<B52,E56,E45)),IF(E45<B46,H57,E45)))

E57: ==IF(B44<B56,VLOOKUP(B70,CHART,4)*B57/4*SQRT(B62/(2*(29.9^2*VLOOKUP(B70,CHART,4)^4-0.8*B62*B57^2))),E38)

B56:=B38*SQRT(IF(E44=1,B52,1^(3/4)*B53^(1/4)*B52/E43^(3/4)/E44^(1/4))/E43)

B38: =VLOOKUP(B70,CHART,2)

B89: ==HLOOKUP($E$4,WLC2CV,VLOOKUP($B$4,WLC2CV,15))

May 11, 2012 at 2:19 PM
Edited May 11, 2012 at 2:20 PM

and there is warning generated, when i am handling the exception by assigning it old calculated values.

Warning (2): sqrt() expects parameter 1 to be double, string given [APP\vendors\Classes\PHPExcel\Calculation.php, line 3324]

May 12, 2012 at 5:57 AM

any clue..........................

Coordinator
May 13, 2012 at 5:14 PM

This thread explains cyclic formulae.

Without knowing the value being passed to sqrt() it's hard to say exactly why sqrt(0 is giving an error, but I'd suspect that it's the result of an error in another formula that results in (for example) a #REF! or similar Excel error value being passed to the sqrt() function.

 

Standard practise debugging formulae is to enable debugging in the calculation engine using:

PHPExcel_Calculation::getInstance()->writeDebugLog = true;

then catch any exceptions and then call

print_r(PHPExcel_Calculation::getInstance()->debugLog)

to dump a complete history of the calculation processing

 

May 14, 2012 at 12:26 PM
Edited May 14, 2012 at 2:47 PM

@mark thanks for ur support... but .. No browser is able to display the calculation processing history in my case :-(  every browser crashes during data fetching using    print_r(PHPExcel_Calculation::getInstance()->debugLog)


I have to start again... :-(