PHPEXCEL getCalculatedValue或getFormattedValue返回致命错误 [英] PHPEXCEL getCalculatedValue or getFormattedValue returns fatal error

问题描述

我有一个MIME类型为"application/octet-stream; charset = binary"且扩展名为".xlsx"的文件. PHPExcel_IOFactory :: identify($ this-> file)返回'Excel2007'.我这样创建我的阅读器:

I have a file with mime-type "application/octet-stream; charset=binary" and extension ".xlsx". PHPExcel_IOFactory::identify($this->file) returns 'Excel2007'. I create my reader like this:

$this->objectReader = PHPExcel_IOFactory::createReaderForFile($this->file);

当我尝试使用公式获取单元格值时，我只能通过两种方式做到这一点:

When I try to get a cell value with a formula I can only do it in two ways:

1)$sheet->getCell($columns[$i].$row->getRowIndex())->getValue() 
//return the actual formula as a string
2)$sheet->getCell($columns[$i].$row->getRowIndex())->getOldCalculatedValue() 
//returns the oldCalculated value which is not always corret (for example the value in the cell is 17.4% and it returns 0.17)

当我转储$ sheet-> getCell($ columns [$ i].$ row-> getRowIndex())对象时，输出从以下开始:

When I dump the $sheet->getCell($columns[$i].$row->getRowIndex()) object the output starts with the fallowing :

object(PHPExcel_Cell)[142370]
  private '_value' => string '=IFERROR(IF(C$6="yes",IF(ISBLANK(INDIRECT(ADDRESS($AN15,MATCH($B$6,INDIRECT("'"&C$7&"'!"&$AN$9-1&":"&$AN$9-1),0),,,C$7),TRUE)),"",INDIRECT(ADDRESS($AN15,MATCH($B$6,INDIRECT("'"&C$7&"'!"&$AN$9-1&":"&$AN$9-1),0),,,C$7),TRUE)),""),"")' (length=231)
  private '_calculatedValue' => float 0.1744360902255639
  private '_dataType' => string 'f' (length=1)
  private '_parent' =>...

有正确的值，但我无法访问它... 当我尝试使用getFormattedValue()或getCalculatedValue()获取单元格值时，输出是令人讨厌的致命错误:

The correct value is there but I can't access it... When I try to get the cell value with getFormattedValue() or with getCalculatedValue() the output is the fallowing fatal error:

Catchable fatal error: Argument 2 passed to PHPExcel_Calculation_LookupRef::INDIRECT() must be an instance of PHPExcel_Cell, boolean given

关于如何解决此问题的任何想法?我真的被困住了，不知道.谢谢.

Any idea of how to solve this problem ? I am really stuck and have no idea. Thank you.

推荐答案

对

$sheet->getCell($columns[$i].$row->getRowIndex())->getValue() 

将返回任何包含公式的单元格的实际公式

will return the actual formula for any cell that contains a formula

转储单元格对象时看到的值_calculatedValue是可以通过调用来获取的值

The value _calculatedValue that you can see when you dump the cell object is the one that you can retrieve using the call to

$sheet->getCell($columns[$i].$row->getRowIndex())->getOldCalculatedValue()

在这种情况下，您说这是不正确的.....您错了，这是正确的:17.4% 是 区别在于，应用%格式掩码会在MS Excel中显示实际单元格值乘以100

In this case, you say it's incorrect..... you're wrong, it is correct: 17.4% is 0.174... the difference is that applying a % format mask displays the actual cell value in MS Excel multiplied by 100

致电时

$sheet->getCell($columns[$i].$row->getRowIndex())->getCalculatedValue()

或

$sheet->getCell($columns[$i].$row->getRowIndex())->getFormattedValue()

PHPExcel尝试执行该单元格的公式并重新计算该值.如果这引发异常，则可以通过启用计算引擎的调试模式来调试计算引擎，以查看错误原因，如要点

PHPExcel tries to execute the formula for the cell and recalculate the value. If this is throwing an exception, you can debug the calculation engine to see where it is going wrong by enabling the calculation engine's debug mode, as described in this gist

这篇关于PHPEXCEL getCalculatedValue或getFormattedValue返回致命错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！