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

Formula Value is =Input!C37
Expected Value is 0.5
Parser Stack :-
Array
(
    [0] => Array
        (
            [type] => Cell Reference
            [value] => Input!C37
            [reference] => Input!C37
        )

)

Calculated Value is 0.5
Evaluation Log:
Array
(
    [0] => Testing cache value for cell Executive Summary!D12
    [1] => Executive Summary!D12 => Evaluating Cell C37 in worksheet Input
    [2] => Executive Summary!D12 => Testing cache value for cell Input!C37
    [3] => Executive Summary!D12 -> Input!C37 => Evaluating Cell C9 in current worksheet
    [4] => Executive Summary!D12 -> Input!C37 => Evaluation Result for cell Input!C9 is a string with a value of "both cost and speed-to-market"
    [5] => Executive Summary!D12 -> Input!C37 => Evaluating Cell B6 in worksheet Params
    [6] => Executive Summary!D12 -> Input!C37 => Evaluation Result for cell Params!B6 in worksheet Params is a string with a value of "only cost (speed-to-market remains unchanged)"
    [7] => Executive Summary!D12 -> Input!C37 => Evaluating Cell D10 in worksheet Params
    [8] => Executive Summary!D12 -> Input!C37 => Testing cache value for cell Params!D10
    [9] => Executive Summary!D12 -> Input!C37 -> Params!D10 => Evaluating Cell A10 in current worksheet
    [10] => Executive Summary!D12 -> Input!C37 -> Params!D10 => Evaluation Result for cell Params!A10 is a floating point number with a value of 5
    [11] => Executive Summary!D12 -> Input!C37 -> Params!D10 => Evaluating 5 - 1
    [12] => Executive Summary!D12 -> Input!C37 -> Params!D10 => Evaluation Result is a floating point number with a value of 4
    [13] => Executive Summary!D12 -> Input!C37 -> Params!D10 => Evaluating Cell A10 in current worksheet
    [14] => Executive Summary!D12 -> Input!C37 -> Params!D10 => Evaluation Result for cell Params!A10 is a floating point number with a value of 5
    [15] => Executive Summary!D12 -> Input!C37 -> Params!D10 => Evaluating 5 - 1
    [16] => Executive Summary!D12 -> Input!C37 -> Params!D10 => Evaluation Result is a floating point number with a value of 4
    [17] => Executive Summary!D12 -> Input!C37 -> Params!D10 => Evaluating 4 / 4
    [18] => Executive Summary!D12 -> Input!C37 -> Params!D10 => Evaluation Result is a floating point number with a value of 1
    [19] => Executive Summary!D12 -> Input!C37 => Evaluation Result for cell Params!D10 in worksheet Params is a floating point number with a value of 1
    [20] => Executive Summary!D12 -> Input!C37 => Evaluating Range "Params!B6" : "Params!D10"
    [21] => Executive Summary!D12 -> Input!C37 => Testing cache value for cell Params!C6
    [22] => Executive Summary!D12 -> Input!C37 -> Params!C6 => Evaluating Cell D6 in current worksheet
    [23] => Executive Summary!D12 -> Input!C37 -> Params!C6 => Evaluation Result for cell Params!D6 is a floating point number with a value of 0
    [24] => Executive Summary!D12 -> Input!C37 -> Params!C6 => Evaluating 1 - 0
    [25] => Executive Summary!D12 -> Input!C37 -> Params!C6 => Evaluation Result is a floating point number with a value of 1
    [26] => Executive Summary!D12 -> Input!C37 => Testing cache value for cell Params!C7
    [27] => Executive Summary!D12 -> Input!C37 -> Params!C7 => Evaluating Cell D7 in current worksheet
    [28] => Executive Summary!D12 -> Input!C37 -> Params!C7 => Testing cache value for cell Params!D7
    [29] => Executive Summary!D12 -> Input!C37 -> Params!C7 -> Params!D7 => Evaluating Cell A7 in current worksheet
    [30] => Executive Summary!D12 -> Input!C37 -> Params!C7 -> Params!D7 => Evaluation Result for cell Params!A7 is a floating point number with a value of 2
    [31] => Executive Summary!D12 -> Input!C37 -> Params!C7 -> Params!D7 => Evaluating 2 - 1
    [32] => Executive Summary!D12 -> Input!C37 -> Params!C7 -> Params!D7 => Evaluation Result is a floating point number with a value of 1
    [33] => Executive Summary!D12 -> Input!C37 -> Params!C7 -> Params!D7 => Evaluating Cell A10 in current worksheet
    [34] => Executive Summary!D12 -> Input!C37 -> Params!C7 -> Params!D7 => Evaluation Result for cell Params!A10 is a floating point number with a value of 5
    [35] => Executive Summary!D12 -> Input!C37 -> Params!C7 -> Params!D7 => Evaluating 5 - 1
    [36] => Executive Summary!D12 -> Input!C37 -> Params!C7 -> Params!D7 => Evaluation Result is a floating point number with a value of 4
    [37] => Executive Summary!D12 -> Input!C37 -> Params!C7 -> Params!D7 => Evaluating 1 / 4
    [38] => Executive Summary!D12 -> Input!C37 -> Params!C7 -> Params!D7 => Evaluation Result is a floating point number with a value of 0.25
    [39] => Executive Summary!D12 -> Input!C37 -> Params!C7 => Evaluation Result for cell Params!D7 is a floating point number with a value of 0.25
    [40] => Executive Summary!D12 -> Input!C37 -> Params!C7 => Evaluating 1 - 0.25
    [41] => Executive Summary!D12 -> Input!C37 -> Params!C7 => Evaluation Result is a floating point number with a value of 0.75
    [42] => Executive Summary!D12 -> Input!C37 => Testing cache value for cell Params!C8
    [43] => Executive Summary!D12 -> Input!C37 -> Params!C8 => Evaluating Cell D8 in current worksheet
    [44] => Executive Summary!D12 -> Input!C37 -> Params!C8 => Testing cache value for cell Params!D8
    [45] => Executive Summary!D12 -> Input!C37 -> Params!C8 -> Params!D8 => Evaluating Cell A8 in current worksheet
    [46] => Executive Summary!D12 -> Input!C37 -> Params!C8 -> Params!D8 => Evaluation Result for cell Params!A8 is a floating point number with a value of 3
    [47] => Executive Summary!D12 -> Input!C37 -> Params!C8 -> Params!D8 => Evaluating 3 - 1
    [48] => Executive Summary!D12 -> Input!C37 -> Params!C8 -> Params!D8 => Evaluation Result is a floating point number with a value of 2
    [49] => Executive Summary!D12 -> Input!C37 -> Params!C8 -> Params!D8 => Evaluating Cell A10 in current worksheet
    [50] => Executive Summary!D12 -> Input!C37 -> Params!C8 -> Params!D8 => Evaluation Result for cell Params!A10 is a floating point number with a value of 5
    [51] => Executive Summary!D12 -> Input!C37 -> Params!C8 -> Params!D8 => Evaluating 5 - 1
    [52] => Executive Summary!D12 -> Input!C37 -> Params!C8 -> Params!D8 => Evaluation Result is a floating point number with a value of 4
    [53] => Executive Summary!D12 -> Input!C37 -> Params!C8 -> Params!D8 => Evaluating 2 / 4
    [54] => Executive Summary!D12 -> Input!C37 -> Params!C8 -> Params!D8 => Evaluation Result is a floating point number with a value of 0.5
    [55] => Executive Summary!D12 -> Input!C37 -> Params!C8 => Evaluation Result for cell Params!D8 is a floating point number with a value of 0.5
    [56] => Executive Summary!D12 -> Input!C37 -> Params!C8 => Evaluating 1 - 0.5
    [57] => Executive Summary!D12 -> Input!C37 -> Params!C8 => Evaluation Result is a floating point number with a value of 0.5
    [58] => Executive Summary!D12 -> Input!C37 => Testing cache value for cell Params!C9
    [59] => Executive Summary!D12 -> Input!C37 -> Params!C9 => Evaluating Cell D9 in current worksheet
    [60] => Executive Summary!D12 -> Input!C37 -> Params!C9 => Testing cache value for cell Params!D9
    [61] => Executive Summary!D12 -> Input!C37 -> Params!C9 -> Params!D9 => Evaluating Cell A9 in current worksheet
    [62] => Executive Summary!D12 -> Input!C37 -> Params!C9 -> Params!D9 => Evaluation Result for cell Params!A9 is a floating point number with a value of 4
    [63] => Executive Summary!D12 -> Input!C37 -> Params!C9 -> Params!D9 => Evaluating 4 - 1
    [64] => Executive Summary!D12 -> Input!C37 -> Params!C9 -> Params!D9 => Evaluation Result is a floating point number with a value of 3
    [65] => Executive Summary!D12 -> Input!C37 -> Params!C9 -> Params!D9 => Evaluating Cell A10 in current worksheet
    [66] => Executive Summary!D12 -> Input!C37 -> Params!C9 -> Params!D9 => Evaluation Result for cell Params!A10 is a floating point number with a value of 5
    [67] => Executive Summary!D12 -> Input!C37 -> Params!C9 -> Params!D9 => Evaluating 5 - 1
    [68] => Executive Summary!D12 -> Input!C37 -> Params!C9 -> Params!D9 => Evaluation Result is a floating point number with a value of 4
    [69] => Executive Summary!D12 -> Input!C37 -> Params!C9 -> Params!D9 => Evaluating 3 / 4
    [70] => Executive Summary!D12 -> Input!C37 -> Params!C9 -> Params!D9 => Evaluation Result is a floating point number with a value of 0.75
    [71] => Executive Summary!D12 -> Input!C37 -> Params!C9 => Evaluation Result for cell Params!D9 is a floating point number with a value of 0.75
    [72] => Executive Summary!D12 -> Input!C37 -> Params!C9 => Evaluating 1 - 0.75
    [73] => Executive Summary!D12 -> Input!C37 -> Params!C9 => Evaluation Result is a floating point number with a value of 0.25
    [74] => Executive Summary!D12 -> Input!C37 => Testing cache value for cell Params!C10
    [75] => Executive Summary!D12 -> Input!C37 -> Params!C10 => Evaluating Cell D10 in current worksheet
    [76] => Executive Summary!D12 -> Input!C37 -> Params!C10 => Testing cache value for cell Params!D10
    [77] => Executive Summary!D12 -> Input!C37 -> Params!C10 => Retrieving value for cell Params!D10 from cache
    [78] => Executive Summary!D12 -> Input!C37 -> Params!C10 => Evaluation Result for cell Params!D10 is a floating point number with a value of 1
    [79] => Executive Summary!D12 -> Input!C37 -> Params!C10 => Evaluating 1 - 1
    [80] => Executive Summary!D12 -> Input!C37 -> Params!C10 => Evaluation Result is a floating point number with a value of 0
    [81] => Executive Summary!D12 -> Input!C37 => Testing cache value for cell Params!D7
    [82] => Executive Summary!D12 -> Input!C37 => Retrieving value for cell Params!D7 from cache
    [83] => Executive Summary!D12 -> Input!C37 => Testing cache value for cell Params!D8
    [84] => Executive Summary!D12 -> Input!C37 => Retrieving value for cell Params!D8 from cache
    [85] => Executive Summary!D12 -> Input!C37 => Testing cache value for cell Params!D9
    [86] => Executive Summary!D12 -> Input!C37 => Retrieving value for cell Params!D9 from cache
    [87] => Executive Summary!D12 -> Input!C37 => Testing cache value for cell Params!D10
    [88] => Executive Summary!D12 -> Input!C37 => Retrieving value for cell Params!D10 from cache
    [89] => Executive Summary!D12 -> Input!C37 => Evaluating Function VLOOKUP() with 4 arguments
    [90] => Executive Summary!D12 -> Input!C37 => Evaluating VLOOKUP( "both cost and speed-to-market", { "only cost (speed-to-market remains unchanged)", 1, 0; "mainly cost", 0.75, 0.25; "both cost and speed-to-market", 0.5, 0.5; "mainly speed-to-market", 0.25, 0.75; "only speed-to-market", 0, 1 }, 2, FALSE )
    [91] => Executive Summary!D12 -> Input!C37 => Evaluation Result for VLOOKUP() function call is a floating point number with a value of 0.5
    [92] => Executive Summary!D12 => Evaluation Result for cell Input!C37 in worksheet Input is a floating point number with a value of 0.5
)

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

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.