Add streaming mode for writing excel files

[jontro]

Currently PhpSpreadsheet buffers the xlsx until it's written out to a stream which will be a problem if the generated dataset is very large.

Apache POI has a streaming implementation (https://poi.apache.org/spreadsheet/) where it solves this by restricting the operations if using it in a streaming mode.

• Only a limited number of rows are accessible at a point in time.
• Sheet.clone() is not supported.
• Formula evaluation is not supported

Would it be possible to add something similar to PHPSpreadsheet?

[PowerKiKi]

Do you mean create a spreadsheet from scratch, in-memory, and while you add new cells/rows, some "previous cells" are written down on disk and deleted from memory ? Keeping the memory usage very low at all time ?

[jontro]

I was thinking that you could open a file pointer to php://output right away so the contents will start being served immediately.
I.e.

$objWriter = PHPExcel_IOFactory::createWriter('php://output', 'Excel2007');
$objPHPExcel = new PHPExcel($objWriter);
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $row++;
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, $result['column']);
}
...

For very large datasets php will run out of memory if it's kept in buffer all the time

[PowerKiKi]

This would most likely be a huge undertaking and Mark's input might be needed. Would you be willing to work on that?

[jontro]

I would absolutely be willing to help but I think it would have to be coordinated with multiple people. I have no real experience with working with the xlxs implementation details. What do you think? If it's a huge implementation change I wonder if it's really worth it, depending on how many library users would think this is useful.

Especially since the mode would limit other aspects of the api

[PowerKiKi]

I agree that having multiple person involved might be best. Unfortunately I won't have enough time for that myself. So if you can find others, or invest enough time by yourself, then go for it. Otherwise I guess we'll have to do without that feature.

@MarkBaker any opinion on the matter ?

[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.

[glenndavey83]

I could really use this right about now :/

[felansu]

I too my bro... 💃

[eselskas]

me too

[xkzl]

same :(

[ghost]

:(

[ahmed-bhs]

Up!

[PowerKiKi]

To clarify, this is very unlikely to ever happen in PhpSpreadsheet. Spreadsheet content is not linear content, all kind of things depends on all kind of other things that must all exist at the same time to be able to write a complete, correct XLSX file. If you want to stream-write an XLSX file, you would lose really a lot of features. The API would need to be severely limited. So much so that it would not be reasonable to have a single codebase to support both use-cases.

If you need this kind of thing, you better look for an alternative product. Maybe something like https://github.com/mk-j/PHP_XLSXWriter

But if performance is your utmost concern, then https://www.php.net/manual/en/function.fputcsv.php is your best friend.