Cell::getCoordinate returns wrong value

[vlady777]

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

I have .xlsx file with some data (regular table with first row as table header, L-column is last with values) and trying to get coordinates of last cell of first row where value is not null.

What is the expected behavior?

Expeceted coordinates: L1

What is the current behavio

Current coordinates: M1

What are the steps to reproduce?

Itearate row cells, check cell value and stop on null value.

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$row = $spreadsheet->getActiveSheet()->getRowIterator()->current();
$lastCell = null;
foreach ($row->getCellIterator() as $cell) {
    if ($cell->getValue() !== null) {
        $lastCell = $cell;
    } else {
        break;
    }
}

var_dump($lastCell->getValue()); //
var_dump($lastCell->getCoordinate()); //
die;

Which versions of PhpSpreadsheet and PHP are affected?

"name": "phpoffice/phpspreadsheet",
"version": "dev-NumberFormatMask-Helpers",
"source": {
    "type": "git",
    "url": "https://github.com/PHPOffice/PhpSpreadsheet.git",
    "reference": "2430e916d674d423da19211f3597e3a53845d129"
}

PHP 8.1.1 (cli) (built: Dec 21 2021 19:46:50) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.1.1, Copyright (c) Zend Technologies
    with Zend OPcache v8.1.1, Copyright (c), by Zend Technologies
    with Xdebug v3.1.2, Copyright (c) 2002-2021, by Derick Rethans

[MarkBaker]

This is because any cell maintained in user code is actually a pointer to the last cell referenced in the cell collection. When you iterate over the cells, you're setting $lastCell as a pointer to the cell which contains a non-null value (L), but the loop continues resetting the last cell accessed to the cell in column M, checks that it is a null and breaks. Because $lastCell is actually a pointer to the last cell accessed, it now references the cell from column M.

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$row = $spreadsheet->getActiveSheet()->getRowIterator()->current();
$lastCellAddress = null;
foreach ($row->getCellIterator() as $cell) {
    if ($cell->getValue() !== null) {
        $lastCellAddress = $cell->getCoordinate();
    } else {
        break;
    }
}
lastCell = $spreadsheet->getActiveSheet()->getCell($lastCellAddress ?? 'A1' );

var_dump($lastCell->getValue()); //
var_dump($lastCell->getCoordinate()); //
die;

Alternatively, tell the CellIterator to only iterate cells that aren't empty:

$columnIterator = $row->getCellIterator();
$columnIterator ->setIterateOnlyExistingCells(true);
foreach ($columnIterator  as $cell) { ... }

[oleibman]

@MarkBaker, is this treatment for "any cell maintained in user code" also the explanation for issue #2262?

[MarkBaker]

@oleibman There's a good chance that's the case; the calculation engine takes great care to maintain (and restore if necessary) the active cell pointer; but note everything is necessarily as clean in doing so