getCalculatedValue throw error Uncaught exception

Topics: Developer Forum
Oct 6, 2015 at 10:54 AM
I'm having a problem in one of xlsx file, that gives an error when I try to catch values of concrete cells.
First need to say that we have three different processes with PHPExcel and a spreadsheet in each of it. First and second works absoluty great, but third (which have a more complex spreadsheet, with tabs and lot of formulas, it's size is 700kb) is giving us a real pain.

The point is that I can write in spreadsheet I suppose all writed are ok (we haven't xmlwriter on server to test finnish document yet), but when I try to take values calculated with getCalculatedValue there are a number of cells that I cant retrieve it's value because throw this error:

Fatal error: Uncaught exception 'PHPExcel_Calculation_Exception' with message 'Consumos!FG6 -> Consumos!FG6 -> Consumos!FG6 -> MaxIrrad!A4 -> MaxIrrad!A4 -> MaxIrrad!A4 -> MaxIrrad!A4 -> MaxIrrad!A4 -> MaxIrrad!A4 -> MaxIrrad!A4 -> Consumos!FG6 -> Consumos!FG6 -> Consumos!FG6 -> Consumos!FG6 -> Invalid cell coordinate GH' in /var/www/html/clientes/generahogar/class/PHPExcel/Cell.php:300

The error says that there are not GH column but really exists, and the whole calculation in Microsoft excel directly are not showing any error...
It's interesting that this only happend when I try take some values, but another values (calculated with internal functions too) I can take it without problems, here some example of code:

$result1 = $objPHPExcel->getActiveSheet()->getCell('AO22')->getCalculatedValue();
$result2 = $objPHPExcel->getActiveSheet()->getCell('AC38')->getCalculatedValue();

If I execute only result1, all are fine. But if want to get result2 too this cause error. This is a short example, I can take more than 1 value, the problem are happening in few cells that I need, 4 or 5 cells I think.

I know this can be really concrete and difficult to fix for anybody that doesn't develop our project, but I wonder if it can be a more general problem that I guess and somebody can light my path, because I'm totally mired...

Any help would be appreciated. Thanks for your time
Best regards
Oct 6, 2015 at 11:28 AM
Edited Oct 6, 2015 at 11:29 AM
Knowing the actual formula might help diagnose the problem, but I'm guessing that you have a column reference to column GH somewhere in a formula....
PHPExcel's calculation engine supports cell ranges (e.g. 'A1:C10'), but column and row ranges (e.g. 'A:B' or '1:3') are not fully supported.

This gist might help you diagnose the problem more accurately
Oct 6, 2015 at 1:35 PM
Thank you for your answer.
This tool that you gave me is a better way to debug, i'm pretty sure that documentation contains it but I never see it before. Sorry

Now I can see some of things that I guess if it causing the problem. I think that all problems start on line 397 when program says:
Evaluating Range "Consumos!AC51" : "Consumos!AK52"
This is an example of bad support range that you said to me? in next example are the first lines where I found #N/A error value.

[357] => Consumos!A55 -> [...] -> Consumos!AB24 => Evaluating Range "Consumos!AC51" : "Consumos!AK52"
[358] => Consumos!A55 -> [...] -> Consumos!AB24 => Evaluating Function VLOOKUP() with 3 arguments
[359] => Consumos!A55 -> [...] -> Consumos!AB24 => Evaluating VLOOKUP( 48, { 12, "Panel solar policristalino LLGCP 150W-12V", 150, 12, 18.23, 8.23, 21.97, 8.56, 36; 24, "Panel solar policristalino LLGCP 260W-24V", 260, 24, 30.41, 8.55, 37.27, 8.74, 60 }, 2 )
[360] => Consumos!A55 -> [...] -> Consumos!AB24 => Evaluation Result for VLOOKUP() function call is a #N/A error
[361] => Consumos!A55 -> [...] -> Consumos!AC27 => Evaluation Result for cell Consumos!AB24 is a #N/A error

After that, I can see #N/A value in some parts of the log.

I think that whole GH is a bad range, because we are not indicating the number of cells?

You can see if you want full log in a pastebin

Can you confirm so that the problem is the support of the range that you told me? In this case are not another way that change formulas
Can I do anything to help as php developer that have not so much idea of spreadsheets?

best regards