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
