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.

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

Include them in your bootstrap.js to be compiled.

resources/js/bootstrap.js
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.

resources/views/book/index.blade.php
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'}, ], });

End Result:

image
image
Laravel Excel

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

Terminal
composer require maatwebsite/excel

Create a new route for the export:

routes/web.php
Route::get('books/download', '[email protected]')->name('books.download');

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

app\Exports\BooksExport.php
<?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:

app/Http/Controllers/BookController.php
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.

resources/views/book/index.blade.php
<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.