DEV Community

Dallington Asingwire
Dallington Asingwire

Posted on • Edited on

How to download pdf, excel, csv or print data from tables in laravel

When implementing some software systems, a requirement may arise when you want to print reports or export data either in excel, csv, pdf or even print via a printer. Well, in this tutorial I will guide you how to have this implemented in a Laravel application.

First of all, I assume you have already installed Laravel application on your machine. So we use yajra datatable plugin to have exportable datatables. Install this plugin using composer as shown below.

composer require yajra/laravel-datatables:^1.5
Enter fullscreen mode Exit fullscreen mode

Then run the command below to publish configuration and asset files (optional).

php artisan vendor:publish
Enter fullscreen mode Exit fullscreen mode

if you are using laravel and the version is below 5.5, add the following under providers array items in the config file (app/config/app.php)

'providers' => [
    // ...
    Yajra\DataTables\DataTablesServiceProvider::class,
],
Enter fullscreen mode Exit fullscreen mode

Next, create a datatable service class e.g UsersDataTable using the following command;

php artisan datatables:make UsersDataTable
Enter fullscreen mode Exit fullscreen mode

The above command will generate a datatable service class in the directory app/DataTables/UsersDataTable.php which I custom to have relevant data to display in the users table in blade file.

<?php

namespace App\DataTables;

use App\Models\User;
use Yajra\DataTables\Services\DataTable;
use Illuminate\Http\Request;
use Illuminate\Http\Response;

class UsersDataTable extends DataTable
{
    /**
     * Build DataTable class.
     *
     * @param mixed $query Results from query() method.
     * @return \Yajra\DataTables\DataTableAbstract
     */
    public function dataTable($query)
    {
        return datatables($query)
        ->order(function($query){
               $query->orderBy('created_at', 'desc');
        })->addIndexColumn()
        ->addColumn('action', function ($user) {      
      $btn = "<a href='javascript:void(0);' data-toggle='modal' 
            data-id=''.$user->id.'' data-original-title='Edit' 
            id='edit-user' data-target='#editUserModal'
              class='btn btn-primary edit-user pr-4'>
             <span class='fa fa-pencil'></span></a>";
           $btn .= '<a href="javascript:void(0);" id="view-user" 
           data-toggle="modal" data-original-title="View"
 data-target="#viewUserModal"
            data-id="'.$user->id.'" class="btn btn-info bolded">
           <i class="fa fa-eye" ></i></a>';
            $btn .= '<a href="javascript:void(0);" id="delete-user" 
            data-toggle="modal" data-original-title="Delete" 
data-target="#deleteUserModal"
             data-id="'.$user->id.'" class="btn btn-danger pr-4"">
            <span class="fa fa-trash" ></span></a>';
           return $btn;
        })->addColumn('checkbox', function ($user) {
              $checkBox = '<input type="checkbox" id="'.$user->id.'"/>';
             return $checkBox;
        })->rawColumns(['action', 'checkbox']);
    }

    /**
     * Get query source of dataTable.
     *
     * @param \App\Models\User $model
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function query(User $model)
    {
        return $model->newQuery()->select('*');
    }

    /**
     * Optional method if you want to use html builder.
     *
     * @return \Yajra\DataTables\Html\Builder
     */
    public function html()
    {
        return $this->builder()
                    ->setTableId('usersdatatable-table')
                    ->columns($this->getColumns())
                    ->minifiedAjax()
                    ->dom('Bfrtip')
                    ->orderBy(1)
                    ->buttons(
                        Button::make('create'),
                        Button::make('export'),
                        Button::make('print'),
                        Button::make('reset'),
                        Button::make('reload')
                    );
    }

    /**
     * Get columns.
     *
     * @return array
     */
    protected function getColumns()
    {
        return [
            Column::computed('action')
                  ->exportable(false)
                  ->printable(false)
                  ->width(60)
                  ->addClass('text-center'),
            Column::make('id'),
            Column::make('add your columns'),
            Column::make('created_at'),
            Column::make('updated_at'),
        ];
    }

    /**
     * Get filename for export.
     *
     * @return string
     */
    protected function filename()
    {
        return 'Users_' . date('YmdHis');
    }
}
Enter fullscreen mode Exit fullscreen mode

In the above class, there are 2 important methods i.e query and dataTable. query method uses the model User to return all users from the database while dataTable is used to make necessary adjustments on the data and add new columns for example action which has buttons like edit, delete and view on table data etc.
Then create a controller say UserController and make use of the datatable service as follows;

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\DataTables\UsersDataTable;

class UserController extends Controller
{
public function index(){
   return view('users');
}

 public function getUsers(UsersDataTable $dataTable){
        return $dataTable->render('users');
 }
}
Enter fullscreen mode Exit fullscreen mode

In your routes file web.php, add route as shown below;

Route::get('/users/ajax', 
[UserManagementController::class,
     'getUsers'])->name('get-users');
Enter fullscreen mode Exit fullscreen mode

Now in your blade file say users.blade.php, assuming that your have an html table with code snippets as shown below;

 <table class="table table-bordered table-dark" id="users-table">
            <thead>
              <tr>
                <th></th>
                <th>No</th>
                <th>First Name</th>
                <th>Last Name</th>
                <th>User Name</th>
                <th>Role</th>
                <th>Email</th>
                <th>Action</th>
              </tr>
            </thead>
            <tbody>
            </tbody>
          </table>
Enter fullscreen mode Exit fullscreen mode

Add the script below in your blade file;

   <script>
  const ajaxUrl = @json(route('get-users'));
  const cat = 'users';
  const token = "{{ csrf_token() }}";
</script>
<script type="text/javascript">
  $(document).ready(function(){
     $.ajaxSetup({
          headers: {
              'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
           }
         });
    var table = $('#users-table');
    var title = "List of system users in the system";
    var columns = [0, 1, 2, 3];
    var dataColumns = [
         {data: 'checkbox', name:'checkbox'},
         {data: 'DT_RowIndex', name: 'DT_RowIndex', orderable: false,  searchable: false },
         {data: 'firstname', name:'firstname'},
         {data: 'lastname', name:'lastname'},
         {data: 'username', name:'username'},
         {data: 'role', name:'role'},
         {data: 'email', name:'email'},
         {data: 'action', name: 'action',orderable: false,searchable: false},
     ];
     makeDataTable(table, title, columns, dataColumns);
});


function makeDataTable(table, title, columnArray, dataColumns) {

     $(table).dataTable({
         dom:
             "<'row'<'col-sm-1'l><'col-sm-8
           pb-3 text-center'B><'col-sm-3'f>>" +
             "<'row'<'col-sm-12'tr>>" +
             "<'row'<'col-sm-5'i><'col-sm-7'p>>",
         processing: true,
         stateSave: true,
         pageLength:15,
         "lengthMenu": [ [10, 15, 25, 50, -1], [10, 15, 25, 50, "All"] ],
         buttons: [
             {
                 text: "<i></i> Select all",
                 className: "btn btn-primary
 btn-sm btn-select-all",
                 action: function(e, dt, node, config) {
                     selectAllCheckBoxes();
                 }
             },

             {
                 text: "<i></i> Deselect all",
                 className: "btn btn-info btn-sm",
                 action: function(e, dt, node, config) {
                     deselectAllCheckBoxes();
                 }
             },

             $.extend(
                 true,
                 {},
                 {
                     extend: "excelHtml5",
                     text: '<i class="fa fa-download "></i> Excel',
                     className: "btn btn-default btn-sm",
                     title: title,
                     exportOptions: {
                         columns: columnArray
                     }
                 }
             ),

             $.extend(
                 true,
                 {},
                 {
                     extend: "pdfHtml5",
                     text: '<i class="fa fa-download"></i> Pdf',
                     className: "btn btn-default btn-sm",
                     title: title,
                     exportOptions: {
                         columns: columnArray
                     }
                 }
             ),

             $.extend(
                 true,
                 {},
                 {
                     extend: "print",
                     exportOptions: {
                         columns: columnArray,
                         modifier: {
                             selected: null
                         }
                     },
                     text: '<i class="fa fa-save"></i> Print',
                     className: "btn btn-default btn-sm",
                     title: title
                 }
             ),

             {
                 text: "<i></i> Delete selected",
                 className: "btn btn-danger btn-sm btn-deselect-all",
                 action: function(e, dt, node, config) {
                     deleteSelectedRows(table);
                 }
             }
         ],
         ajax: ajaxUrl,
         columns: dataColumns,
         order: [[0, "asc"]]
     });

    }

});

</script>

Enter fullscreen mode Exit fullscreen mode

In the above script, i have created a reusable javascript function named makeDataTable which i use to custom the names of print buttons, pass parameters table(its id), title of the report or exportable data/file, columns(array containing column indexes of the table columns to be exported, may be you don't want to export action buttons) and dataColumns( the columns of data e.g firstname, lastname, username, email in otherwords actual details of the users etc).

Output is as shown in the image below
Alt Text
Note: Thank you for reading this tutorial, follow me to keep posted.

Top comments (1)

Collapse
 
afzalhaq61 profile image
Afzal Haq

how to customise pdf view in it.