Error exporting Excel using Maatwebsite / Laravel-Excel

Question

Error exporting Excel using Maatwebsite / Laravel-Excel

Navigation

#1 by (1 votes)

1

Exporting to excel works well for 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 " Has anyone had this problem?

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);
    }

excel laravel

asked by anonymous 22.08.2018 / 15:53

1 answer

Problem with Php and mysql - Simple login Split query result into columns

score 1 · Accepted Answer

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

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

I will show an implementation that I have 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 the memory already used in the query and in the Excel generation, you would be forcing your server to also parse that view to mount Excel.

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