Laravel DataTables large export

Question

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?

Looking at the docs and source, the chunk option seems only available for import :( Try putting $this->getDataForExport() outside and passing it through with a use on each closure, It doesn't help that its in closures as its just going to chew up memory. You may need to think about doing it differently as when you got 20k rows its going to continue to break. — Lawrence Cherone
– Lawrence Cherone, Commented Dec 20, 2017 at 15:41
Thanks, @LawrenceCherone. I was trying to var_dump($this->getDataForExport()) and even that would yield the same 500 Server Error with a blank response. I even tried going deep into those methods from the package to see if I could figure out what needs to be improved, but was getting stuck often. Even the getDataForExport has closures for mapping the ajaxResponseData to the export columns and it's just a nightmare. I thought I was successful with native DataTables server-side processing for other projects, but figured I'd try to solve the Laravel before abandoning it. — jeffjenx
– jeffjenx, Commented Dec 20, 2017 at 16:14

Marleen · Accepted Answer · 2025-11-16 14:44:46Z

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.

TheGeeky · Accepted Answer · 2020-01-08 03:48:45Z

1

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.

answered Jan 8, 2020 at 3:48

TheGeeky

9711 gold badge13 silver badges35 bronze badges

Collectives™ on Stack Overflow

Laravel DataTables large export

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related