Laravel & DataTable - CSV, Excel & PDF Export

JC Tan avatar

JC Tan

Photo by Tyler Casey on Unsplash
Photo by Tyler Casey on Unsplash

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
  1. Setting up DataTable in Laravel
  2. Passing Data
  3. Filtering / Ordering
  4. 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.

JC Tan avatar
Written By

JC Tan

Published in PHP
Enjoyed the post?

Clap to support the author, help others find it, and make your opinion count.