Phpspreadsheet: How to avoid or remove blank, empty and null rows. (getHighestDataRow() return 1000)

[mesadhan]

This is:

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

What is the expected behavior?

$highestRow = $worksheet->getHighestDataRow();

By default count 1000 rows. including blank, empty and null rows.
How to avoid or remove blank, empty and null rows.

What is the current behavior?

Microsoft Excel Default Row 1000. When I am tring to get getHighestDataRow() then default it's return 1000 with blank or null value. But I like to remove blank space from or empty row.

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path);
$worksheet = $spreadsheet->getActiveSheet();

$highestRow = $worksheet->getHighestDataRow();      // Error: always return 1000 ?
$highestCol = $worksheet->getHighestDataColumn();

return $highestRow . ' : '. $highestCol;            // highestRow return 1000 by default
//return $data = $worksheet->rangeToArray('A1:' . $highestColumn . $highestRow, null, true, false, false);

Which versions of PhpSpreadsheet and PHP are affected?

PHP 7.2.9

[adriadrop]

Yes I would like to do the same, but seems to me only way now would be to iterate rows, check if they are empty and manually set highest row.

[adriadrop]

Currently getHighestRowAndColumn checks for records in this way

    public function getHighestRowAndColumn()
    {
        // Lookup highest column and highest row
        $col = ['A' => '1A'];
        $row = [1];
        foreach ($this->getCoordinates() as $coord) {
            sscanf($coord, '%[A-Z]%d', $c, $r);
            $row[$r] = $r;
            $col[$c] = strlen($c) . $c;
        }
        if (!empty($row)) {
            // Determine highest column and row
            $highestRow = max($row);
            $highestColumn = substr(max($col), 1);
        }
        return [
            'row' => $highestRow,
            'column' => $highestColumn,
        ];
    }

but this counts empty cells as records so you get in the end high number of entries even if they are empty.

[support[bot]]

This looks like a support question. Please ask your support questions on StackOverflow, or Gitter.
Thank you for your contributions.

[PowerKiKi]

Maybe try https://phpspreadsheet.readthedocs.io/en/develop/topics/reading-files/#reading-only-specific-columns-and-rows-from-a-file-read-filters

[betommy23]

Having exact same problem.
This shouldn't be a support question but should be like the original post, a bug or a feature request.
The functions getHighestDataColumn or getHighestDataRow count all empty or null cells in, which are not accurate results....

[MarkBaker]

This is arguable: a null value or an empty string is still a value, the cell still exists; a cell containing a formula can return an "empty" result, but is still a cell with a value.

And what constitutes an "empty" cell depends upon your use case; you might wish to include empty strings, but discount nulls, or to discount cells with formulae that return errors.

Without agreement on what constitutes an empty cell, it's difficult to know how this might be addressed. My best suggestion would be an extra flags argument to the getHighestDataColumn() and getHighestDataRow()` indicating what qualifies as an empty cell:

getHighestDataColumn(5, EXCLUDE_NULL | EXCLUDE_EMPTY_STRING);

[liondrow]

I also had a problem with empty rows and cells. The solution was pretty simple:
$reader->setReadDataOnly(true); $reader->setReadEmptyCells(false);

Perhaps it will help you