CODEWITHSUNDEEP
phpphpexcel
iswinky
iswinky

Reputation: 1969

PHPExcel formula causing false column identifier

Slightly strange problem I'm having. Whilst looping through rows and columns in an excel spreadsheet, if a cell has a formula, it treats that as a different column. For example:

$c = 0;
foreach ($objWorksheet->getRowIterator() as $row) {
    $c++;
    foreach ($row->getCellIterator() as $cell) {
        $cellValue = trim($cell->getCalculatedValue());
        if ($cell->getColumn() == "N" || $cell->getColumn() == "O") {
            var_dump("ROW: $c . Get column: " . $cell->getColumn());
            var_dump("ROW: $c . Cell value: " . $cellValue);
        }
    }
}

There's a header, and one row in a spreadsheet I'm using for testing. Based on the if statement, it should only var_dump data from column "N" and "O", but it is infact outputting 9 additional times. The spreadsheet contains 9 cells that have formula in them, containing conditionals for "N" and "O". Output:

string(46) "ROW: 2 . Get column: N"
string(48) "ROW: 2 . Cell value: TEST N COLUMN"
string(46) "ROW: 2 . Get column: O"
string(53) "ROW: 2 . Cell value: TEST O COLUMN"
string(46) "ROW: 2 . Get column: O"
string(45) "ROW: 2 . Cell value: "
string(46) "ROW: 2 . Get column: O"
string(59) "ROW: 2 . Cell value: "
string(46) "ROW: 2 . Get column: O"
string(45) "ROW: 2 . Cell value: "
string(46) "ROW: 2 . Get column: O"
string(45) "ROW: 2 . Cell value: "
string(46) "ROW: 2 . Get column: O"
string(45) "ROW: 2 . Cell value: "
string(46) "ROW: 2 . Get column: O"
string(45) "ROW: 2 . Cell value: "
string(46) "ROW: 2 . Get column: O"
string(45) "ROW: 2 . Cell value: "
string(46) "ROW: 2 . Get column: O"
string(45) "ROW: 2 . Cell value: "
string(46) "ROW: 2 . Get column: O"
string(45) "ROW: 2 . Cell value: "

I'm not too familiar with Excel, but the formula in the 9 columns that seem to be outputted is something like this: 

=IF(AF2="YES", N2&O2&"FR/BL"," ")

So as you can see column "N2" and "O2" are being used, but it's causing issues with the getColumn function in PHPExcel. The columns with this formula are AG, AJ, AM, AP, AS, AV, AY, BB, BE, but I don't think this should matter.

Help please!

Update

If I copy and paste the row but paste only the values, it works. However, $cellValue uses getCalculatedValue anyway, so I don't understand why this doesn't solve the problem....hmm

Upvotes: 0

Views: 408

Answers (1)

iswinky
iswinky

Reputation: 1969

Bugfix to ensure that current cell is maintained when executing formula calculations

https://github.com/PHPOffice/PHPExcel/commit/87be8d3d8e1c503a6422fee585e5fc5193c66978

Updating to "dev-develop" branch using composer fixed the issue.

Upvotes: 0

Related Questions