CODEWITHSUNDEEP
phplaraveldatatableslaravel-excel
jeffjenx
jeffjenx

Reputation: 17487

Laravel DataTables large export

I've taken over a Laravel project that is using the Yajra DataTables package. The table renders fine and I was able to add a CSV/Excel export button that works fine for the most part.

The problem I'm running into is that our application outputs 10k records at times and when I try to export the full dataset to CSV, it winds up yielding a 500 Internal Server error with a blank page in the browser (empty response) after a short time.

I've tried updating all of the Laravel and Laravel DataTables packages and trying code from various forum posts to try to override the package's buildExcelFile() with logic that might chunk the processing, but I always wind up with the same result.

Original Method (source):

protected function buildExcelFile()
{
    /** @var \Maatwebsite\Excel\Excel $excel */
    $excel = app('excel');

    return $excel->create($this->getFilename(), function (LaravelExcelWriter $excel) {
        $excel->sheet('exported-data', function (LaravelExcelWorksheet $sheet) {
            $sheet->fromArray($this->getDataForExport());
        });
    });
}

The source uses another package called laravel-excel but I can't figure out how to update the logic so it processes in smaller chunks and actually works.

Can anyone help figure out the logic for exporting large result sets to CSV using the Yajra Laravel DataTables package?

Upvotes: 2

Views: 3251

Answers (2)

TheGeeky
TheGeeky

Reputation: 971

If anyone uses Laravel Excel 3.0+ the upper solution won't work. so you should override buildExcelFile() by using "Queued Export".

Or I did something else by using chunking also but by creating my trait and use CSV to do my work and this an example of mine.

Upvotes: 1

lidichamp
lidichamp

Reputation: 31

I had this issue also, I fixed it by chunking using these steps: I created a class called override which has a method that overrides the default buildExcelFile() method and extended all my datatables by this class.And this worked perfectly for me. Here is the code for my override class

namespace App\DataTables;
use Maatwebsite\Excel\Classes\LaravelExcelWorksheet;
use Maatwebsite\Excel\Writers\LaravelExcelWriter;
use Yajra\Datatables\Services\DataTable;
class Override extends DataTable
{
protected function buildExcelFile()
{
/** @var \Maatwebsite\Excel\Excel $excel */
$excel = app('excel');

return $excel->create($this->getFilename(), function (LaravelExcelWriter    $excel) {
    $excel->sheet('exported-data', function (LaravelExcelWorksheet $sheet) {
        $this->query()->chunk(100,function($modelInstance) use($sheet) {
        $modelAsArray = $modelInstance->toArray();
        foreach($modelAsArray as $model)
        $sheet->appendRow($model);
    });
  });
});}public function query()
{
    // this method is overwritten in my datatable class , and it's that query result that is chunked method buildExcelFile() above
 }
}

Then my Datatable classes look more like this

<?php

namespace App\DataTables;
use ...
class AssignDataTable extends Override
{
...

I hope this helps you.

Upvotes: 3

Related Questions