PHPExcel Get cells ignoring related sheets

Question

I have a .xlsx file with two sheets and I want to extract to PHP the second sheet, just like it is. The problem is that a column from this sheet is related to the first one, so the results I get when I print the extracted values, are some sort of a mix between the second sheet and the related line from the first one.

I'm not sure how can I show you the Excel, but I'll show the code hoping that someone had already a similar problem.

public function excel()
{
    error_reporting(E_ALL ^ E_NOTICE);

    #system variables
    header('Content-Type: text/html; charset=utf-8');
    set_time_limit(0);
    error_reporting(E_ALL);
    ini_set('display_errors', TRUE);
    ini_set('display_startup_errors', TRUE);
    ini_set('memory_limit', '128M');

    #includes
    include FCPATH.'application/libraries/phpexcel/PHPExcel.php';
    set_include_path(FCPATH.'application/libraries/phpexcel/PHPExcel/'); //INCLUDE DOS FICHEIROS DO PHPEXCEL
    require_once 'IOFactory.php';

    $inputFileName = BASE_DIR.'assets/misc/cc_v2.xlsx'; 

    //$reader = PHPExcel_IOFactory::createReaderForFile($inputFileName);
    $reader = PHPExcel_IOFactory::createReader('Excel2007');
    $reader->setReadDataOnly(true);
    $objPHPExcel = $reader->load($inputFileName);

    $objPHPExcel->setActiveSheetIndex(1);

    $objWorksheet = $objPHPExcel->getActiveSheet();
    $highestRow = $objWorksheet->getHighestRow(); 
    $highestColumn = $objWorksheet->getHighestColumn(); 

    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

    for ($row = 1; $row <= $highestRow; ++$row) 
    {
        for ($col = 0; $col <= $highestColumnIndex; ++$col) 
        {
            echo $objWorksheet->getCellByColumnAndRow($col, $row)->getOldCalculatedValue()."\n";
        }
        echo "<br>";
    }
}

This way I get a mix of both sheets because of their dependence. Already tried deleting the first sheet, but doing that causes the second sheet to have a column with broken references.

Thanks in advance.

EDIT:

Here is the formula on column's B cells that's causing the "problem" getting the cell's value by PHP:

=IF(ISNA(A8);"";VLOOKUP(A8;Clientes!$B:$C;2;FALSE))

"Clientes" is the name of the first sheet.

In conclusion, sheet 1 has a list of clients and sheet 2 have a list of all the expenses. Column B from sheet 2 is the name of the client, but when getting it by PHPExcel I get several values from sheet 1 instead of the value I can see on column B from sheet 2.

For example, cell B3 has the value "BPI" that's returned from the first sheet.

EDIT 2:

When running the code with getCalculatedValue() instead, I get the following error:

Fatal error: Uncaught exception 'PHPExcel_Calculation_Exception' with message 'CONTA!B2 -> Formula Error: An unexpected error occured' in /home/clientes/versions/v3/application/libraries/phpexcel/PHPExcel/Cell.php:300 Stack trace: #0 /home/clientes/versions/v3/application/controllers/pm_dashboard.php(269): PHPExcel_Cell->getCalculatedValue() #1 [internal function]: Pm_dashboard->excel('fyi', 'drKzj1ykfqUXXBV...') #2 /home/clientes/versions/v3/system/core/CodeIgniter.php(359): call_user_func_array(Array, Array) #3 /home/clientes/versions/v3/index.php(209): require_once('/home/clientes/...') #4 {main} thrown in /home/clientes/versions/v3/application/libraries/phpexcel/PHPExcel/Cell.php on line 300

Here is the if that contains line 300 of Cell.php:

if ($this->_dataType == PHPExcel_Cell_DataType::TYPE_FORMULA) {
        try {
            //echo 'Cell value for '.$this->getCoordinate().' is a formula: Calculating value'.PHP_EOL;
            $result = PHPExcel_Calculation::getInstance(
                $this->getWorksheet()->getParent()
            )->calculateCellValue($this,$resetLog);
            //echo $this->getCoordinate().' calculation result is '.$result.PHP_EOL;
            //  We don't yet handle array returns
            if (is_array($result)) {
                while (is_array($result)) {
                    $result = array_pop($result);
                }
            }
        } catch ( PHPExcel_Exception $ex ) {
            if (($ex->getMessage() === 'Unable to access External Workbook') && ($this->_calculatedValue !== NULL)) {
                //echo 'Returning fallback value of '.$this->_calculatedValue.' for cell '.$this->getCoordinate().PHP_EOL;
                return $this->_calculatedValue; // Fallback for calculations referencing external files.
            }
            //echo 'Calculation Exception: '.$ex->getMessage().PHP_EOL;
            $result = '#N/A';
            #NEXT IS LINE 300
            throw new PHPExcel_Calculation_Exception(
                $this->getWorksheet()->getTitle().'!'.$this->getCoordinate().' -> '.$ex->getMessage()
            );
        }

Answer 1

Ok, after looking at your formula:

=IF(ISNA(A8);"";VLOOKUP(A8;Clientes!$B:$C;2;FALSE))

I have noticed one single, but repeating thing: your arguments are separated by semicolon (;) while Excel function arguments are, as in any other language, separated by ,.

I think that formular should go:

=IF(ISNA(A8),"",VLOOKUP(A8,Clientes!$B:$C,2,FALSE))

Another thing: isn't VLOOPUP supposed to get a cell range as first argument? $B:$C is weird to me, but I am no Excel expert :)