PHPExcel-公式中的错误循环参考 [英] PHPExcel - Error Cyclic Reference in Formula

问题描述

我不断收到此错误公式中的循环引用".

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

当我尝试下载我的Excel文件时.

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..

这是我的excel文件: http://www.2shared.com/document/SPtnvq6e/excel.html

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..

推荐答案

循环公式类似于(例如)单元格A1包含公式= B1 + 1，而单元格B1包含= A1 + 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.

这在Excel中可能是有效的，尽管默认行为是在遇到错误消息时会生成错误消息.但是，您可以更改此行为，以便Excel将通过指定的循环数或迭代次数来处理公式.

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支持两种行为:默认是生成错误消息，就像Excel一样.但是，如果将计算引擎的$ cyclicFormulaCount属性设置为大于0的值，它将模拟Excel可以表现出的第二种行为. 在最简单的层次上:

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;

将抑制循环错误并允许执行基本的循环计算，将$ cyclicFormulaCount设置为更高的值将允许通过多个循环或迭代(为您指定的值)来计算公式.

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).

getOldCalculatedValue()方法检索由MS Excel本身最后执行的计算结果.这可能是正确的，但不能保证(例如:如果Excel本身已禁止公式计算).

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).

或者，可以防止PHPExcel在保存文件之前计算公式:

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

$objWriter->setPreCalculateFormulas(FALSE);

编辑

从1.7.9版开始，已对计算引擎进行了修改，以便每个工作簿文件都有一个计算引擎实例，因此有必要指出需要为其设置CycleFormulaCount的实例.

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.

而不是

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

您将使用

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

这篇关于PHPExcel-公式中的错误循环参考的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！