Laravel & DataTable - CSV, Excel & PDF Export

This is 4th part of the Laravel & DataTable mini series, today let's explore how to export your data from your DataTable to either CSV, Excel or PDF using the built in method or with Laravel Excel .

Index

0. Setting up DataTable in Laravel
1. Passing Data
2. Filtering / Ordering
3. CSV, Excel & PDF Export

Note that you can do the setup needed following step 0-2.

The Built In Method

We would need to include the buttons, jsZip and pdfMake packages using npm.

npm i datatables.net-buttons
npm i jszip
npm i pdfmake

Include them in your bootstrap.js to be compiled.

window.JSZip = require('jszip');

    require('bootstrap');
    require('datatables.net-bs4');
    require('datatables.net-buttons/js/dataTables.buttons');
    require('datatables.net-buttons/js/buttons.flash');
    require('datatables.net-buttons/js/buttons.html5');
    require('datatables.net-buttons/js/buttons.print');
    require('datatables.net-buttons/js/buttons.colVis');
    
    window.pdfMake = require('pdfmake/build/pdfmake');
    window.pdfFonts = require('pdfmake/build/vfs_fonts');
    pdfMake.vfs = pdfFonts.pdfMake.vfs;

Execute npm run dev.

Initialiase your DataTable as below, adding the dom and buttons parameter.

let table = $('#book-table').DataTable({
      dom: 'Bfrtip',
      buttons: [
        'copy', 'csv', 'excel', 'pdf', 'print'
      ],
      processing: true,
      language: {
        processing: '<span>Processing</span>',
      },
      serverSide: true,
      ajax: {
        url: '{{ route('books.query') }}',
        method: 'get',
        data: function (d) {
          d.start_date = $('#start-date').val();
          d.end_date = $('#end-date').val();
        }
      },
      search: {
        caseInsensitive: false,
      },
      columns: [
        {data: 'name'},
        {data: 'author'},
        {data: 'published_at'},
      ],
    });

Laravel Excel

As usual, first of all we need to install the package using composer.

composer require maatwebsite/excel

Create a new route for the export:

Route::get('books/download', 'BookController@download')->name('books.download');

Create the export file, which will control how the data is being retrieved or what columns should be shown.

<?php

namespace App\Exports;

use App\Book;
use Maatwebsite\Excel\Concerns\FromCollection;

class BooksExport implements FromCollection
{
    public function collection()
    {
        return Book::all();
    }
}

In your controller we will bridge them together as below:

use Maatwebsite\Excel\Facades\Excel;
use App\Exports\BooksExport;

 //...
 
public function download()
{
   return Excel::download(new BooksExport, 'books.xlsx');
}

Finally we will add a new button to trigger the download.

<button id="btn-filter" class="btn btn-primary">Filter</button>
<a id="btn-download" href="{{ route('books.download') }}" class="btn btn-outline-primary">Download</a>

The package is very flexible and modular, so if you need more manipulation to the data or sheets, do head over to Laravel Excel's Export page to find out more.