php - PHPExcel - Error Cyclic Reference in Formula

Question

I keep reciving this error "Cyclic Reference in Formula".

When I am trying to download my excel file.

Now when im deleting the '=' from formula's from my file

$this->exportObj->setActiveSheetIndex(0)
                                ->setCellValue(strtoupper($this->abc[$currentColumn]).$currentLine, (str_replace("=", "", $column)) );

I can download the file but ofcourse the formala won't work..

This is my excel file: http://www.2shared.com/document/SPtnvq6e/excel.html

What is wrong with my formulas there? I cant see anything that I'm doing wrong..

Answer 1

A cyclic formula is one like (for example) cell A1 contains the formula =B1+1 while cell B1 contains =A1+1, ie. where executing the formula results in a chain of calculations that loops back on itself.

This can be valid in Excel, though the default behaviour is to generate an error message when it is encountered. However, you can change this behaviour so that Excel will process the formula through a specified number of cycles or iterations.

PHPExcel supports both behaviours: the default is to generate the error message, as Excel does. But if you set the calculations engine's $cyclicFormulaCount property to a value greater than 0, it will emulate the second behaviour that Excel can exhibit. At its simplest level:

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

will suppress cyclic errors and allow a basic cyclic calculation to be executed, setting $cyclicFormulaCount to higher values will allow the formulae to be calculated through several cycles or iterations (to the value that you specify).

The getOldCalculatedValue() method retrieves the result of a calculation as last executed by MS Excel itself. This may be correct, but is not guaranteed (eg: if formula calculation has been suppressed in Excel itself).

Alternatively, you can prevent PHPExcel from calculating formulas before saving the file:

$objWriter->setPreCalculateFormulas(FALSE);

EDIT

Since version 1.7.9 the Calculation engine has been modified so that there is one calculation engine instance for each workbook file, so it is necessary to indicate which instance you need to set the cyclicFormulaCount for.

Rather than

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

you would use

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