Convert xlsx to csv with calculated value

Topics: Developer Forum
Jun 15, 2012 at 10:56 AM

Hello,

 

I try to convert xlsx file into csv using PHPExcel_IOFactory::createReader et PHPExcel_IOFactory::createWriter.

I have a lot of cells which contains calculated value to ohther cells in other sheet. So some of cell contains a formula with an other cell, and this other cell contains a formula with an other cell.

When I try to convert this file I have this error : PHP Fatal error:  Call to a member function cellExists() on a non-object in phpexcel/PHPExcel/Calculation.php on line 3209

Can PHPExcel to this work ? Thanks

Coordinator
Jun 15, 2012 at 11:34 AM

It can, but somewhere in your formulas there's a reference to an invalid cell... without knowing the details of your workbook, I can only second guess. Do any of the formulae reference cells in a worksheet that doesn't exist, isn't being loaded, or is in an external workbook?

And please try using the latest 1.7.7 version of PHPExcel, it has a number of bugfixes in the calculation engine.

Jun 15, 2012 at 2:24 PM

Thank you for your reply, it's as you said, My cell reference an external book ! So this problem is resolved.

But I have an other problem. I have a cell "B2" which has a formula like this "=1-(B3/B1)" and when I get him with $cell->getCalculatedValue() I have "#VALUE!".

I don't know why I don't have the good value.

Thank you for your help

Coordinator
Jun 15, 2012 at 2:33 PM

What's in cells B3 and B1? particularly B1 if it could lead to a divide by zero?

 

Jun 15, 2012 at 2:33 PM

Some cell has value in Excel but no value whe, I retrieve them with getCalculatedValue()

Jun 15, 2012 at 2:37 PM

it's very complex because of :

B3 : refer to Sheet1!D1 and Sheet1!D1 refer to Sheet2!A1

B1 : same thing

Coordinator
Jun 16, 2012 at 1:17 PM

When trying to debug the calculation engine, I use the following:

function testFormula($sheet,$cell) {
    $formulaValue = $sheet->getCell($cell)->getValue();
    echo '<b>'.$cell.' Value is </b>'.$formulaValue."<br />\n";
    $expectedValue = $sheet->getCell($cell)->getOldCalculatedValue();
    echo '<b>'.$cell.' Expected Value is </b>'.((!is_null($expectedValue)) ? $expectedValue : 'UNKNOWN')."<br />\n";

    $calculate = false;
    try {
        $tokens = PHPExcel_Calculation::getInstance()->parseFormula($formulaValue,$sheet->getCell($cell));
        echo '<b>Parser Stack :-</b><pre>';
        print_r($tokens);
        echo '</pre>';
        $calculate = true;
    } catch (Exception $e) {
        echo "PARSER ERROR: ".$e->getMessage()."<br />\n";
        echo '<b>Parser Stack :-</b><pre>';
        print_r($tokens);
        echo '</pre>';
    }
    if ($calculate) {
        try {
            $cellValue = $sheet->getCell($cell)->getCalculatedValue();
            echo '<b>'.$cell.' Calculated Value is </b>'.$cellValue."<br />\n";
            echo '<h3>Evaluation Log:</h3><pre>';
            print_r(PHPExcel_Calculation::getInstance()->debugLog);
            echo '</pre>';
        } catch (Exception $e) {
            echo "CALCULATION ENGINE ERROR: ".$e->getMessage()."<br />\n";
            echo '<h3>Evaluation Log:</h3><pre>';
            print_r(PHPExcel_Calculation::getInstance()->debugLog);
            echo '</pre>';
        }
    }
}

$sheet = $objPHPExcel->getActiveSheet();
PHPExcel_Calculation::getInstance()->writeDebugLog = true;

testFormula($sheet,'A4');

This displays a full parser log showing every step of the evaluation

 

Jun 17, 2012 at 10:11 PM

Thank I will check !

For exemple if C2 = B2 + D2 with B2 = 2 and D2 = '', is this work ? because I have cell woth '' value with are used for calculation formula in other cells.

Thank you

Coordinator
Jun 17, 2012 at 10:48 PM
Edited Jun 17, 2012 at 10:49 PM

2 + "" will give a #VALUE! error, just as it will in MS Excel, because an empty string is not a valid numeric value; likewise 2 + NULL will also give a #VALUE! error.

However, 2 + an empty cell will give a result of 2, as it will in MS Excel

 

 

Jun 18, 2012 at 9:01 AM

Ok, it's why I get #VALUE! error ! But I don't understand why MS Excel shows the good result and doesn't show #VALUE! error ? So it's can be an evolved for PHPExcel ?