Error when exporting Excel using Maatwebsite / Laravel-Excel

Question

The excel export works well up to 10000 records but above that it returns me the error: "127.0.0.1 is Currently Unable to Handle this request. HTTP ERROR 500" Someone’s had that problem before?

public function getUsersRegion($region){
        $region = is_array($region) ? $region : array($region);
        $users = DB::table("users")
                    ->join("regions", "users.region", "=", "regions.id")
                    ->select("users.id" , "users.avatar", "users.first_name", "users.last_name", "users.email", "regions.name", "users.ref_code", "users.banned")
                    ->whereIn("users.region", $region)
                    ->orderBy("users.id", "desc")                   
                    ->get();
            return $users;
    }

    public function export($region, $format) {
        $retorno = $this->getUsersRegion($region);
        Excel::create('Painel Gerenciador de Conteudo', function($excel) use ($retorno, $format){
            $excel->sheet('Painel Gerenciador de Conteudo', function($sheet) use ($retorno, $format){
                $sheet->loadView('exports.export')->with('retorno',$retorno)->with('format',$format);
            });
        })->export($format);
    }

Answer 1

There is often a 500 error followed by a blank page. Unfortunately, this Laravel excel generation library cannot behave very well when the amount of data generated is too large.

I would suggest you use some functions that reduce the size of the record load that is done in memory, such as the method chunk.

I’ll show you an implementation I made, you can adapt to your need:

    $query = Usuario::where('status', '=', 1);

    return Excel::create("relatorio-usuarios-ativos", function ($excel) use($query) {

        $excel->setTitle('Usuários')->sheet('usuarios', function ($sheet) use($query) {

            $sheet->appendRow([
                'ID', 
                'NOME',
                'IDADE',
            ]);

            // Os dados são carregados de 50 em 50, para não sobrecarregar a memória
            $query->chunk(50, function ($usuarios) use ($sheet) {

                foreach ($usuarios as $usuario) {

                    $sheet->appendRow([
                       $usuario->id,
                       $usuario->nome,
                       $usuario->idade,
                    ]);                        
                }
            });

            $sheet->row(1, function ($row) {
                $row->setFontColor('#ffffff')->setBackground('#00458B');
            });
        });

    })->download('xlsx');

See above that I avoided using loadView, because in addition to memory already used in the query and generation of Excel, you would be forcing your server to parse also this view to mount Excel.

If all above does not solve the problem, I advise using CSV, which is native to PHP and the generation is much faster, carrying a larger volume of data, with low memory cost.