Proposed changes to load()/save() options for Readers/Writers

[MarkBaker]

Before version 1.19.0, if you wanted to control the elements that were loaded or saved (such as readDataOnly, readEmptyCells, or including Charts), it was necessary to call a setter method on an instantiated Reader or Writer, e.g.

$fileName = 'mySpreadsheetFile';

$reader = IOFactory::createReaderForFile($fileName);
$reader->setIncludeCharts(true);
$spreadsheet = $reader->load($fileName);

Version 1.19.0 introduced flags to the load() or save() call for a Reader or Writer as an alternative to having to call those getters and setters.

$fileName = 'mySpreadsheetFile';

$reader = IOFactory::createReaderForFile($fileName);
$spreadsheet = $reader->load($fileName, $reader::LOAD_WITH_CHARTS);

Or directly in the IOFactory load() method

$fileName = 'mySpreadsheetFile';

$spreadsheet = IOFactory::load($fileName, IReader::LOAD_WITH_CHARTS);

although this is currently limited to the LOAD_WITH_CHARTS option.

For the version 2.0 release, I'm considering dropping all of those getters and setters as a bc-break, and switching purely to load/save flags; but also extending the scope of those flags to provide more controls (particularly for the load) combining multiple flags in a manner similar to the way PHP itself allows combining multiple flags for sort() or file_put_contents() e.g.

$fileName = 'mySpreadsheetFile';

$reader = IOFactory::createReaderForFile($fileName);
$spreadsheet = $reader->load(
    $fileName,
    $reader::IGNORE_DATA_VALIDATIONS | $reader::IGNORE_EMPTY_CELLS | $reader::IGNORE_IMAGES
);

This applies only to options that are boolean in nature, so specifying to load only one worksheet from the file, or apply a read filter would still require using setter methods.

[MarkBaker]

Loader Flags would include (but aren't limited to)

• LOAD_WITH_CHARTS (already exists)
• LOAD_WITH_IMAGES
• LOAD_DATA_VALIDATIONS
• LOAD_CONDITIONAL_FORMATTING
• LOAD_WITH_COMMENTS
• IGNORE_CHARTS
• IGNORE_IMAGES
• IGNORE_DATA_VALIDATIONS
• IGNORE_CONDITIONAL_FORMATTING
• IGNORE_EMPTY_CELLS
• IGNORE_COMMENTS

[omgslinux]

I've just been aware of this. I need to ignore empty (null) cells. I've found that currently are forced to $nullValue in rangeToArray function, with no chance to ignore (without modifying some parts of the logic). I wonder if you can do anything at current mainstream branch, regardless any formal solution for next major branch. Also, this would save memory and processing. Anyway, my suggestion is to have a setter, but not limited to it, for any of the options you mention.

[MarkBaker]

If you choose to use rangeToArray() on a loaded spreadsheet, then PhpSpreadsheet will create an entry in that array for every cell, otherwise it could give an array with different numbers of entries for each row, and that would probably create a lot more issues being raised by developers who couldn't identify cell addresses from their entries in that array. And using rangeToArray() will always be memory intensive, because arrays are memory intensive in PHP.

A better alternative is to use the iterators that PhpSpreadsheet provides instead.

[omgslinux]

I chose toArray() because is what I saw in the samples. If there's already a way to skip empty cells, please let me know.

[MarkBaker]

Looping through cells

The iterators also allow you to specify start/end row/column in the constructor, so you can specify a range like B10:F50 like:

$worksheet = $spreadsheet->getActiveSheet();

echo '<table>' . PHP_EOL;
foreach ($worksheet->getRowIterator(10,50) as $row) {
    echo '<tr>' . PHP_EOL;
    $cellIterator = $row->getCellIterator('B', 'F');
    $cellIterator->setIterateOnlyExistingCells(false); // This loops through all cells,
                                                       //    even if a cell value is not set.
                                                       // For 'TRUE', we loop through cells
                                                       //    only when their value is set.
                                                       // If this method is not called,
                                                       //    the default value is 'false'.
    foreach ($cellIterator as $cell) {
        echo '<td>' .
             $cell->getValue() .
             '</td>' . PHP_EOL;
    }
    echo '</tr>' . PHP_EOL;
}
echo '</table>' . PHP_EOL;

[omgslinux]

Sorry for the delay. Your suggestion effectively avoided empty cells/rows according to params. Now, regarding the topic, I wonder if you've thought about a scenario where xls(x) (or whatever) file(s) are just read (i.e., no phpoffice writes) and maybe just load the "reader components" only (if ever possible) to save some memory and, eventually, increase speed.

[MarkBaker]

I wonder if you've thought about a scenario where xls(x) (or whatever) file(s) are just read (i.e., no phpoffice writes) and maybe just load the "reader components" only (if ever possible) to save some memory and, eventually, increase speed.

I'm not sure I follow what you mean? What do you mean by "just read"? If you read an Xls file, then only Xls Reader components are loaded (and only if/when needed); PhpSpreadsheet doesn't load all the Readers. If you don't write a file, then Writers are never loaded. That's all handled by the autoloading. What scenarios am I missing?

This proposal is all about Reader (and Writer) options, to control what is read from a spreadsheet file: you can already tell the Reader to load/not load charts, and to read/ignore empty cells.