How to download pdf, excel, csv or print data from tables in laravel
Dallington Asingwire
Posted on September 10, 2021
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
Then run the command below to publish configuration and asset files (optional).
php artisan vendor:publish
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,
],
Next, create a datatable service class e.g UsersDataTable using the following command;
php artisan datatables:make UsersDataTable
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');
}
}
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');
}
}
In your routes file web.php, add route as shown below;
Route::get('/users/ajax',
[UserManagementController::class,
'getUsers'])->name('get-users');
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>
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>
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
Note: Thank you for reading this tutorial, follow me to keep posted.
Posted on September 10, 2021
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
November 29, 2024