php excel formula field returning 0

Topics: Developer Forum
May 23, 2014 at 6:58 AM
Edited May 23, 2014 at 7:05 AM
i am using **
PHPExcel_1.8.0_
**
just reading from a file and margin it to another field

all working fine but got problem with the formula fields .. its returns 0 for any formula field

my code can one tell me how to get the value rather than 0
$objReader = PHPExcel_IOFactory::createReaderForFile($full_file_withputh);
            $this->objPHPExcel = $objReader->load($full_file_withputh);
            $row_number=$this->opobj->start_row;
if(isset($this->opobj->dataset))   {unset($this->opobj->dataset);}
$this->opobj->dataset[$counter][$col_number]=$this->objPHPExcel->getActiveSheet(0)->getCellByColumnAndRow($col_number,$row_number)->getValue();
i have also tried
 $this->opobj->dataset[$counter][$col_number]=$this->objPHPExcel->getActiveSheet(0)->getCellByColumnAndRow($col_number,$row_number)->getCalculatedValue();

but it not worked
actually the function is coming from different sheet that may causes
Coordinator
May 23, 2014 at 7:07 AM
Edited May 23, 2014 at 7:31 AM
From that little information, it's impossible to tell you why. .... but if getValue() is returning a 0 as well, then it strongly suggests that there is no formula in that cell, otherwise getValue() would return the formula. A call of getDataType() against the cell should also return "f" if it contains a formula; if it doesn't then a call of getCalculatedValue() will return the same as a getValue() call.

So what is the actual formula that you're getting back from the cell?
May 23, 2014 at 11:01 AM
I can shay its a loop : of formula as below

='Planning Stage Mappings'!J3
J3 = IFERROR(I3,"")
I3 =IF(H3=0,"",H3)
H3 = VLOOKUP(G3,D:E,2,FALSE)
G3 = CONCATENATE(Data!B3,".",Data!AN3,".",Data!Z3)

now column B3 may contains a text or another function ...
Coordinator
May 23, 2014 at 11:25 AM
You can try debugging using the PHPExcel calculation engine debugger as shown in this gist

But I see a couple of potential problems:
  • There is one known bug in the VLOOKUP function that may affect the calculation
  • Row and Column references in formulae aren't fully supported yet (though ranges are), so you may need to modify H3 = VLOOKUP(G3,D:E,2,FALSE) to H3 = VLOOKUP(G3,D1:E1024,2,FALSE) in H3 (adjusting the range for your maximum row number)
May 27, 2014 at 8:57 AM
Hi thanks all for the support ....
as i have tried several ways and didn't get the result i am looking for ... so i have changed the the way of reading the file ... their i used php odbc for reading the file
        $excelFile = realpath($full_file_withputh);
        $excelDir = dirname($excelFile);
        $connection = odbc_connect("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=$excelFile;DefaultDir=$excelDir" , '', '');
        $result = odbc_exec ($connection, "select * from [sheet$] ");

        $row = odbc_fetch_array($result)
it by default takes the first row as Field name

and its worked