Maatwebsite\Excel Doesn't Return Rows when Dataset is Large

I have built a reporting feature for my application in which users can dynamically build a query to feed into a Maatwebsite\Excel report. Everything works beautifully until some mysterious threshold is reached and then the report is created without any rows (headers still appear). I have tried:

Increasing and even removing my PHP memory_limit
Running the export in a queue
Using a custom chunk size

Regardless of the configuration, I'm not getting any errors and it doesn't seem to matter which fields are selected... at some point it just doesn't return data. Furthermore, if I run the same query directly in MySQL, I get results. The execution time for these failed reports is about 0.7 seconds when run in MySQL Workbench, which doesn't seem like a problem.

I have 87 fields available and if I pull them all in and incrementally apply filters for specific records, then I can get up to about 12 before it starts coming back empty. And just to emphasize my previous point, the raw SQL runs fine and returns thousands of rows. Furthermore, I can successfully export thousands of rows to Excel when I pull back, say, 10 fields. At some point, though, it just craps out. And it's all or nothing; it either correctly returns the full set of results or it returns only the column headers with blank rows... and no errors or any other indication that something broke.

I don't even know what to troubleshoot next, so any guidance will be very much appreciated!

rodrigo.pedra

4 years ago

Best Answer

Level 56

First, Excel has a hard line limit, a large one, but a bit more than 1 million lines.

Second, Laravel Excel uses PhpSpreadsheet under the hood, and don't take me wrong, they are my go to tool to export excel files as they make adding format and other features very easy, but as you already noticed, they are very memory intensive, so even for datasets large enough but not close to 1 million records you would need to give PHP lots of memory and execution time, which is hard to predict how much.

Third, these are my suggestions:

If CSV is ok, take a look at league/csv
- I use it for large datasets as you can stream the contents to a file, it is very lightweight on memory
If you need Excel, there is a Spatie package for that: spatie/simple-excel
- It is a wrapper around box/spout, which can export native Excel using generators (you don't need to write those yourself) to be memory lightweight
- It provides some formatting features, but not as easy, nor as extensive as Laravel Excel

Good Luck!

lbecket

4 years ago

Level 39

@rodrigo.pedra Thank you very much for the comprehensive explanation! I'm afraid that CSV is not a viable option in this situation since it's a requirement that the report contains a formatted header row.

After more digging and a little trial and error, I was able to determine that it's not the quantity of the data, per se, but rather the complexity of the underlying query that was causing me to run out of sort memory. A slight increase in my sort_buffer_size seems to have resolved the issue.

With that said, your second option sounds like a good approach if I come up against this limit again.

1 like

rodrigo.pedra

4 years ago

Level 56

@lbecket Great to know. Have a nice day =)

Please or to participate in this conversation.