php - Getting #N/A for any calculations that reference a cell by name

394

I have an Excel file that consists of 8 Worksheets.

Throughout the Excel file, there are calculations that take place in various cells that reference other cells in other worksheet and refer to them by name only.

For example in Worksheet 2, I have a cell D12 and the formula in this cell is=CostIssue. CostIssue is a cell in Worksheet 1.

Using PHPExcel I managed to read the Excel file, manipulate some cells and then generate an HTML of it, but as I mentioned, in all the cells that reference named cells I am getting #N/A.

Any way to solve this or must I manually edit the entire file to reference these named cells in another way (i.e. by worksheet and cell number)?

Thanks

Update:

Some more info. Sheet 2, cell D12 contains=CostIssue CostIssue is the name of cell C37 in Sheet 1 which contains:=VLOOKUP($C$9,$Params.$B$6:$D$10,2,0)

600

Answer

Solution:

I've been looking at this, using the Calc Engine debugging function, and am still unclear about the actual problem.

Using cellD12 on worksheetExecutive Summary which contains the formula=Input!C37, and cellC37 on theInput worksheet contains the formula=VLOOKUP($C$9,Params!$B$6:$D$10,2,FALSE) which evaluates to0.5 (formatted as a percentage in the cells).

With the debug code

function testFormula($sheet, $cell) {
    $formulaValue = $sheet->getCell($cell)->getValue();
    echo 'Formula Value is ' , $formulaValue , PHP_EOL;
    $expectedValue = $sheet->getCell($cell)->getOldCalculatedValue();
    echo 'Expected Value is '  , ((!is_null($expectedValue)) ? $expectedValue : 'UNKNOWN') , PHP_EOL;


    $calculate = false;
    try {
        $tokens = PHPExcel_Calculation::getInstance($sheet->getParent())
            ->parseFormula($formulaValue, $sheet->getCell($cell));
        echo 'Parser Stack :-' , PHP_EOL;
        print_r($tokens);
        echo PHP_EOL;
        $calculate = true;
    } catch (Exception $e) {
        echo 'PARSER ERROR: ' , $e->getMessage() , PHP_EOL;

        echo 'Parser Stack :-' , PHP_EOL;
        print_r($tokens);
        echo PHP_EOL;
    }

    if ($calculate) {
        try {
            $cellValue = $sheet->getCell($cell)->getCalculatedValue();
            echo 'Calculated Value is ' , $cellValue , PHP_EOL;

            echo 'Evaluation Log:' , PHP_EOL;
            print_r(PHPExcel_Calculation::getInstance($sheet->getParent())
                ->getDebugLog()->getLog());
            echo PHP_EOL;
        } catch (Exception $e) {
            echo 'CALCULATION ENGINE ERROR: ' , $e->getMessage() , PHP_EOL;

            echo 'Evaluation Log:' , PHP_EOL;
            print_r(PHPExcel_Calculation::getInstance($sheet->getParent())
                ->getDebugLog()->getLog());
            echo PHP_EOL;
        }
    }
}


$sheet = $objPHPExcel->getSheetByName('Executive Summary');
PHPExcel_Calculation::getInstance($objPHPExcel)
    ->getDebugLog()->setWriteDebugLog(true);

testFormula($sheet,'D12');

I get the results

which is exactly what I would expect to see, with a correct result

Similarly, if I use thetoArray() method, I'm getting50% displayed in cell [12]['C']

EDIT

Can you ensure that you're running the latest develop branch code for PHPExcel, because there have been a couple of fixes to theVLOOKUP() function since the last production release

People are also looking for solutions to the problem: php - Laravel 5 geting 500 error in hosting

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question