Laravel-excel: [QUESTION] How to write XLSX file to disk in chunks to prevent memory issues

[Kyosfonica]

Versions

• PHP version: 7.0.32
• Laravel version: 5.5
• Package version: 3.1

Description

I need to export quite a lot of data to an excel file with some sheets inside. Currently what I am doing is writing my own CSV files chunking the query and writing it directly to storage. This way I can process all the information and get the data but I have to manually import it into Excel.

I tried this example:
https://laravel-excel.maatwebsite.nl/3.1/exports/multiple-sheets.html
But the memory gets exhausted quite easy and the excel file is not filled with data.

Is there a way to write the xlsx file in chunks of data?

I do not find any example of this. The file is always fully generated in memory and then stored into disk.

Can anyone help me?

Thanks

[fengerwoo]

同问，数据量一大就导出不了，如用户表的信息1万条就导不出了

[GlennM]

同问，数据量一大就导出不了，如用户表的信息1万条就导不出了

Please reply in English.

[GlennM]

@Kyosfonica Did you have a look at the Maatwebsite\Excel\Concerns\WithCustomQuerySize concern. It allows Exportables that implement the FromQuery concern, to provide their own custom query size. That might be helpful to you (there's no documentation about it yet, but Pull Requests are always welcome).

[Kyosfonica]

I'll take a look. Thanks

[molteber]

I'm sort of in the same boat, but the data is not presented in a way that it will be easy to use a FromQuery concern. The table is a key-value pair table and one line in the export might be up to 489 rows in the database.

Using the queue functionality, is there a way to yield this so it will prevent memory issues?

---

Edit:
version: 3.1
php: 7.2.13
laravel: 5.7

[patrickbrouwers]

Currently the only way to write in chunks is by using the FromQuery concern.

[patrickbrouwers]

An alternative to using FromQuery is to use FromIterator, you could create an iterator that creates those rows and doesn't keep it in memory.

[molteber]

Hmm, I'll give that a try one time. Thanks for the suggestion!

[gabbie-newell]

An alternative to using FromQuery is to use FromIterator, you could create an iterator that creates those rows and doesn't keep it in memory.

There is no documentation about FromIterator method. Could please someone explain me, how do you use FromIterator method with Queued Export? Thank you in advance!

PS. I've got same error when trying to export 200k+ raws in queue.

[milewski]

@patrickbrouwers FromIterator and queue does not work? seems like when used together an empty file is generated only with the headers