The 'Reader::setReadEmptyCells' method has no effect on Xlsx reader

[phinor]

This is:

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

What is the expected behavior?

Empty cells will not be read from the spreadsheet and will not contribute to overall memory usage.

What is the current behavior?

Empty cells are loaded, contributing to overall memory utilisation.

What are the steps to reproduce?

In this example, I expect the two spreadsheets to have similar memory utilisation.

<?php

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

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter ($spreadsheet, "Xlsx");
$writer->save ("minimal.xlsx");

for ($row = 0; $row < 100; $row ++) {
    for ($column = 0; $column < 100; $column ++) {
        $spreadsheet->getActiveSheet ()->getCellByColumnAndRow ($column, $row)->setValue ("");
    }
}
$writer->save ("large.xlsx");

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setReadEmptyCells (false);

$memStart = memory_get_usage ();

$min = $reader->load ("minimal.xlsx");
$memMin = memory_get_usage ();
echo "Memory utilisation for minimal: " . ($memMin - $memStart) . "<br>";

$max = $reader->load ("large.xlsx");
$memMax = memory_get_usage ();
echo "Memory utilisation for large: " . ($memMax - $memMin) . "<br>";

for ($i = 0; $i < 5; $i ++) {
    $minAgain [] = $reader->load ("minimal.xlsx");
    $memMin = memory_get_usage ();
    echo "Memory utilisation for next minimal: " . ($memMin - $memMax) . "<br>";

    $maxAgain [] = $reader->load ("large.xlsx");
    $memMax = memory_get_usage ();
    echo "Memory utilisation for next large: " . ($memMax - $memMin) . "<br>";
}

Which versions of PhpSpreadsheet and PHP are affected?

Currently using PHP 7.0.3; PhpSpreadsheet 1.5.2

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[siarheipashkevich]

@phinor did you resolve your problem?

[phinor]

Yes. #835

[siarheipashkevich]

@phinor thanks

[synapt]

Was this possibly rolled back at some point or somehow not included into the composer builds?

I have now tested two Xlsx files, one generated in LibreOffice and another from MS Excel, neither are showing intended behavior from setReadEmptyCells(false) in either 1.8.1 or 1.9.0 (latest in composer).

Example code;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader;

$reader = IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(true);
$reader->setReadEmptyCells(false);
$spreadsheet = $reader->load($file)->getActiveSheet()->toArray();
var_dump($spreadsheet);

This does properly load the valid amount of rows, however it loads 1025 cells per row, populating all the empty ones as NULL. Do one of you have an Xlsx that you were using for testing that I can confirm against?

DavidNTV's comment at the bottom of Issue #468 may also be relevant to this but they never updated if they had resolved it.

Edit: I see that there's also a "getReadEmptyCells()" that actually seems like it would be more syntax accurate for 'reading', but it also does nothing seemingly.

Edit Edit: The only instance of checking for emptyCells in the Xlsx reader seems to be at line 745 (in 1.9.0 at least), and if I do a var_dump($value) after that line I indeed get what seems to be proper output (it only dumps what is effectively the populated columns), so somewhere after this it seems to be re-applying all the empty null columns I haven't found yet.

Also apologies I meant to mention with the first edit that I'm encountering this in the latest 1.10.1 release as well.

[devnix]

The combination of setReadDataOnly and setReadEmptyCells is working for me with rows, but it keeps me reading a full last empty column! You can try it with the following file:
Elenco-codici-e-denominazioni-al-31_12_2019.xlsx

[cosminimum]

I think it reproduced if you use ->toArray() after pulling the chunk.
Version: 1.12.0
Xlsx attached

If I can help with more infos, I'm here. Thanks
macheta import ibd (1).xlsx

LE: After some research, I discovered that ->toArray() added nulls back.
LE2: Because toArray() ignore chunk size and takes entire range from A1 to $maxCol . $maxRow