CellValue problem

Topics: Developer Forum, User Forum
Feb 6, 2012 at 8:30 AM

Hi all,

I have been looking for a few days and still haven't found an answer to my problem, so now I have registered to ask directly...
I have a formula that is not inserting into the spreadsheet and a couple of others that are causeing it to error..

the formula that is not inserting (the whole line of code) is...

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Col, $Row, '=IF(ISNA(VLOOKUP("HeadShot80",B2:I26,1,FALSE)="HeadShot80"),SUM(I2:I26),SUM(I2:I26)-(VLOOKUP("HeadShot80",B2:I26,8,FALSE)))');

 

The lines and formulas that are erroring are..
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Col, $Row, '=IF(J29="NOT REQUIRED",0,(C29*D29)+(D29*G29))');

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Col, $Row, '=IF((J29<>0),(((C29+G29)/F29)*J29),H29)');

 

Hopeing someone can help.  I have other formulas that are working fine with the $Col, $Row so I know these are referenced ok.

HeadShot80

Feb 6, 2012 at 8:36 AM

what is going wrong? are there errors in php? are there errors in excel? or is the data in the resulting spreadsheet not as you would have liked? what have you tried? Could you be a little more specific?

Feb 6, 2012 at 8:49 AM
Edited Feb 6, 2012 at 8:53 AM

hi borft,

this line is not inserting the value into excel

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Col, $Row, '=IF(ISNA(VLOOKUP("HeadShot80",B2:I26,1,FALSE)="HeadShot80"),SUM(I2:I26),SUM(I2:I26)-(VLOOKUP("HeadShot80",B2:I26,8,FALSE)))');

 

These line cause an error

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Col, $Row, '=IF((J29<>0),(((C29+G29)/F29)*J29),H29)');

Error appears in excel:-

----------------------------------------------------------

<br />
<b>Fatal error</b>: Uncaught exception 'Exception' with message 'GibCalc!H29 -&gt; GibCalc!J29 -&gt; GibCalc!J29 -&gt; Cyclic Reference in Formula' in /home/website/public_html/PHPExcel/Cell.php:288
Stack trace:
#0 /home/website/public_html/PHPExcel/Writer/Excel5/Worksheet.php(441): PHPExcel_Cell-&gt;getCalculatedValue()
#1 /home/website/public_html/PHPExcel/Writer/Excel5.php(171): PHPExcel_Writer_Excel5_Worksheet-&gt;close()
#2 /home/website/public_html/e107_files/downloads/download_xls2.php(907): PHPExcel_Writer_Excel5-&gt;save('php://output')
#3 {main}
  thrown in <b>/home/website/public_html/PHPExcel/Cell.php</b> on line <b>288</b><br />

-------------------------------------------------------------------------------

This line seams to have fixed itself (not sure what I did to fix it)
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Col, $Row, '=IF(J29="NOT REQUIRED",0,(C29*D29)+(D29*G29))');

Coordinator
Feb 6, 2012 at 8:14 PM
Edited Feb 6, 2012 at 8:14 PM

Cyclic references are when a formula in one cell references a cell containing another formula which contains a reference to the first cell, though the chain can be across several cells. MS Excel has two different ways of handling such formulas, as does PHPExcel. Try setting cell A1 in a worksheet to formula =B1+1, and cell B1 to a formula =A1+1; then change the calculation options to Enable Iterative Calculation and play with the Maximum Iterations Count (File/Options/Formulas/Calculation options in MS Excel 2010) to see the alternative effect.

The first (default) option is to throw an exception. This is what's happening here; and mirrors MS Excel's default option of displaying an error message if you try to enter a cyclic formula. To process in this way, use a try/catch block to identify such errors when a formula is calculated (such as when you save a file), and use your own error handling.

The second option, is to calculate the formula through 1 or more iterations of the cycle. To enable this, use:

PHPExcel_Calculation::getInstance()->cyclicFormulaCount=n;

where n is the number of iterations to allow ( n >= 1)

 

Feb 7, 2012 at 7:55 AM

Thanks Mark,

I am still relativly new to the php world and newer still to PHPExcel. 

What I am trying to do is simply convert an existing excel file which has minor updates to a few cells.  The above formulas work in the original excel file 

I am assuming that you are refering to the 2nd formula that is throwing the error. 

Is there anything that you can advise regarding the first formula that isn't being inserted into the cell?

In the mean time i'll see if I can find another way of composing the formula to get the result i need.

 

Thanks

Coordinator
Feb 7, 2012 at 10:54 AM

Simply set

PHPExcel_Calculation::getInstance()->cyclicFormulaCount=1;

before calling the Writer's save() method, and this should fix your problem.

Feb 7, 2012 at 1:02 PM

Fantastic, Thanks Mark,

 

As it turned out i did have a cyclic error (I forgot to incremt the columns enough)
I have also refined the formula too.

I must say though Mark, I probably would have taken a lot longer finding the error if it wasn't for that line.

 

Thanks all Problem resolved.

HeadShot