Phpspreadsheet: 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[bot]]

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