Export large amount of data with Laravel Excel Export (version 3.1)

Question

I'm trying to export a large amount of data with Laravel Excel Export, version 3.1, but I always have a lack of memory (my limit is 512M).

In the export class:

class ExcelExport implements FromQuery
{
    use Exportable;


    public function query()
    {
        return DB::table('myTable')->orderBy('date');
    }

}

In the controller:

Excel::store(new ExcelExport(), 'myFile.xlsx');

From the official documentation I see the following:

"By using the FromQuery concern, we can prepare a query for an export. Behind the scenes this query is executed in chunks."

But it seems not to work as expected.

Is the use of Query Builder the problem?

Also, is there a way to set a chunk size?

I have tryied to use a limit clause in the query, like this:

public function query()
{
    return DB::table('myTable')->orderBy('date')->limit(1000);
}

but it doesn't work: it seems that the limit is not used.

I have tryied to catch the error in a try...catch block:

try{
    Excel::store(new ExcelExport(), 'myFile.xlsx');
}
catch(\Exception $e){
    Log::error($e->getMessage());
}

but, still, it doesn't catch any exception: all that I see is a 500 internal server error.

Can someone help me with that?

Thank you very much in advance.

You cant catch Memory Exceptions :) Do you have any text or blob column types i would leave them out of the export. In general when i have been in these cases, we have increased the memory usage. An idea could also be to run it as a command since usually the php cli does not have the same memory limits. — mrhn
– mrhn, Commented Feb 8, 2019 at 15:16
Thank you for answearing so quickly. I have no blob data, but I have no control on the amount of data, so I can export 1000 or 100000 records. When I read "Behind the scenes this query is executed in chunks", I expect that the memory limit is no more a problem, because I expect that the query is executed in chunk and appended to the file, but it seems not working in this way. I can't believe that 512M of memory are not enough to manage chunks of data. Is there a way to know/set the chunk size in some way? — user1512966
– user1512966, Commented Feb 8, 2019 at 15:35
Here is an article about exporting large amount of data to Excel with Laravel: dev.to/rap2hpoutre/… — rap-2-h
– rap-2-h, Commented Aug 30, 2019 at 10:40

piscator · Accepted Answer · 2020-02-07 07:48:29Z

1

You could try implicit export queueing.

use Illuminate\Contracts\Queue\ShouldQueue;

class ExcelExport implements FromQuery, ShouldQueue
{
    use Exportable;

    public function query()
    {
        return DB::table('myTable')->orderBy('date');
    }

}

Call your export like this:

(new ExcelExport)->store('myFile.xlsx');

In this way multiple jobs will be chained in order to chunk the process.

2 Comments

user1512966 Over a year ago

Thank you for the suggestion. Something is changed, now, but I have another error: "You cannot serialize or unserialize PDO instances".

Ahmad Elkenany Over a year ago

the problem You cannot serialize or unserialize PDO instances because the trait SerializesModels you cannot use that trait with DB

Collectives™ on Stack Overflow

Export large amount of data with Laravel Excel Export (version 3.1)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related