DEV Community

Export 10M+ rows in XLSX with Laravel without memory issues

Rap2h on August 30, 2019

TL;DR: This post introduce FastExcel for Laravel, gives a quick overview over PHP generators and finally shows how to use both to generate Excel fi...
Read full post
Collapse
 
inquisitivestha profile image
inquisitive-stha •

Looks like the cursor does not work with eager loading for example, If we need to fetch data from multiple modal:

function reviewsGenerator($property_id) {
    foreach (Review::with(['model1','model1.model2','model1.model2.model3'])
                 ->orderBy('created_at','desc')
                 ->cursor() as $review) { //with cursor it returns only the base model1 i.e no model2, no model3
//                        yield $review;
                    dd($review)
    }
}
Enter fullscreen mode Exit fullscreen mode

What would be your recommendation to deal with this? How could I export such data which would obviously be huge.

Collapse
 
batistacte profile image
batistacte •

You can use database views

Collapse
 
reachrama profile image
reachrama •

Hi Raphael,
I was trying to download the xls, in browser. But i am not able to do it. Are is there any other parameter we need to pass to achieve?

return (new FastExcel($dumpdata))->download(DIR.'file.xlsx');

Collapse
 
mengdodo profile image
mengdodo •

thanks!

Collapse
 
marcosteodoro profile image
Marcos Teodoro •

I already used the Spout package in an application in my work, and that solved a lot of problems that we faced related with memory! Thanks for the post =]

Collapse
 
dfedev profile image
Javerleo •

I was trying to use this very promising package.
The problem is, even the basic user's download is not working. No file gets downloaded to browser.
I'm calling the class from Livewire component.
Some advice?

Collapse
 
rahulprgrmr profile image
Rahul Raveendran •

Showing Error: Can use "yield from" only with arrays and Traversables. Can you help?

Collapse
 
rap2hpoutre profile image
Rap2h • • Edited

The method I wrote does not work with model, thank you for your feedback! I fixed the article using cursor which is simpler!

foreach (User::cursor() as $user) {
    yield $user;
}
Enter fullscreen mode Exit fullscreen mode

Still you could chunk by larger slices manually, like in this code:

// Generator function
function getUsersOneByOne() {
    // build your chunks as you want (200 chunks of 10 in this example)
    for ($i = 0; $i < 200; $i++) {
        $users = DB::table('users')->skip($i * 10)->take(10)->get();
        // Yield user one by one
        foreach($users as $user) {
            yield $user;
        }
    }
}

// Export consumes only a few MB
(new FastExcel(getUsersOneByOne()))->export('test.xlsx');
Enter fullscreen mode Exit fullscreen mode

Thank you!!

Collapse
 
otsabi94 profile image
otsabi94 •

i want to import 10 M rows any idee please