Friday 29 May 2020

Add CSV Excel Export Button in Laravel Yajra Datatable



This is one more post on Yajra Laravel Datatables package and in this post, we will learn How to add export buttons to Yajra Laravel Datatables package. If you are using Laravel framework as your web application development task. So, in Laravel framework if you want to use jQuery Datatable plugin for display data on web page in tabular format with searching of data, pagination and data sorting feature. For all those things you have to use Yajra Datatables package in Laravel framework. By using this package you can perform server-side processing of searching, sorting and pagination of data without writing any line of code.

So, If you want to export Yajra DataTable data in excel or csv file format, then you have to add csv and excel export button in your DataTable. In Laravel framework, Yajra Datatables package has provide DataTable Buttons plugin for add export button in your Laravel Datatable. After adding of export button, you can easily export your Laravel DataTable data to CSV file or Excel sheet file format without writing any line of code. So, If want to learn How to add export button in Yajra Laravel Datatable, then you have to follow below step. After following below steps you can easily integrate csv excel export button with your Yajra Laravel Datatable and you can simply export your datatable data in csv or excel file format.

  1. Download Latest Version of Laravel Framework
  2. Make MySql Database Connection
  3. Generate Fake Data
  4. Install Yajra DataTables Package
  5. Create DataTable Class
  6. Create Controller Class
  7. Create View Blade File
  8. Set Route
  9. Run Laravel Application



1 - Download Latest Version of Laravel Framework


First we have to download latest version of Laravel framework, So If you have use window OS then you have to go command prompt in which Composer application must be installed and then after write following command.


composer create-project --prefer-dist laravel/laravel export


This command will make export folder in define directory, download and install latest version of Laravel framework.

2 - Make MySql Database Connection


After downloading of Laravel framework, first want to make database connection. For this we have to open Laravel framework .env file and in that file we have to define Mysql database configuration.


DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=testing
DB_USERNAME=root
DB_PASSWORD=


3 - Generate Fake Data


After making of Mysql Database connection, now we want to make table in mysql databaes connection and then after we want to fill that table with fake data. For this we have to follow following steps.

  • Create Model Class
  • Migrate Table Defination to Database
  • Create Factory Class
  • Create Seed Class

Create Model Class


Here we will use Model class for perform database related operation. For this, we have to create model class file. For this we have to go command prompt and write following command.


php artisan make:model Export -m


This command will make Export.php model class file in app folder. In this file we have to define table column name which you can find below.

app/Export.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Export extends Model
{
    protected $fillable = ['firstName', 'lastName'];
}



Migrate Table Defination to Database


When we have create model class, then at that time that model class migration table file has been created in database/migrations folder. In that file, we have to define table column defination which you can seen below.


<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateExportsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('exports', function (Blueprint $table) {
            $table->id();
            $table->string('firstName');
            $table->string('lastName');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('exports');
    }
}



After define table defination, now we want to migrate this table defination to mysql database and create exports table. For this we have go to command prompt and write following command.


php artisan migrate


Above command will make exports table in Mysql database. So, this way we can create table in mysql database from Laravel our laravel application.

Create Factory Class


For generate fake data and fill mysql exports table with fill data, we have to create one factory class. This class will allowed you to build fake data for your modelds. For create model factories, we have to go command prompt and write following command.


php artisan make:factory ExportFactory


This command will create model factories with name ExportFactory.php in database/factories folder. In this file, first we have import our model class and then after we have to define in which mysql table column we have to define fake data column name which can find below.

database/factories/ExportFactory.php

<?php

/** @var \Illuminate\Database\Eloquent\Factory $factory */

use App\Model;
use Faker\Generator as Faker;
use App\Export;

$factory->define(App\Export::class, function (Faker $faker) {
    return [
        'firstName'  => $faker->firstName,
        'lastName'  => $faker->lastName
    ];
});



Create Seed Class


In Laravel Seed classes is used for seeding your database with test data. In Laravel framework all seed classes has been store in database/seeds folder. For create seed class, we have to command prompt and write following command.


php artisan make:seeder ExportTableSeeder


This command will create ExportTableSeeder.php seed class in database/seeds directory. In this class first we want to import our model class then after in this class you can find run() and under this method we can define how many number of fake data has been genrated by using model factory class. You can find source below it.

database/seeds/ExportTableSeeder.php

<?php

use Illuminate\Database\Seeder;
use App\Export;

class ExportTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $count = 500;
        factory(Export::class, $count)->create();

    }
}



After this we have to open database/seeds/DatabaseSeeder.php file which is already created under this database/seeds directory. So we have to open this file and under this file we can find run() method and under that method we have to called ExportTableSeeder class.

database/seeds/DatabaseSeeder.php

<?php

use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database.
     *
     * @return void
     */
    public function run()
    {
        // $this->call(UserSeeder::class);
        $this->call(ExportTableSeeder::class);
    }
}



After this for generate fake data, we have to go command prompt and write following command.


composer dump-autoload


This command will regenerates the list of all classed which we have included in this Laravel project. Running of this command is required for generate fake data. And lastly for generate fake data we have to run following command.


php artisan db:seed


This command will seed your test data and it will fill exports mysql table with fake data. So, this is complete step by step process for creating fake data in Laravel framework.




4 - Install Yajra DataTables Package & DataTables Buttons plugin


Here we have use Yajra Datatables package has been used for load data in jQuery Datatable plugin in Laravel framework. For use Yajra Datatable package, we have to install in Laravel framework. For this we have go to command prompt and write following command.


composer require yajra/laravel-datatables-oracle


This command will download and install in latest version of Yajra DataTable package in Laravel framework. Here we want to add export button in Yajra Laravel Datatable, for this we have to download and intall Datatable Button plugin. For this we have to go command prompt and write followng command.


composer require yajra/laravel-datatables-buttons


This command will download and install DataTables Button plugin of Yajra Datatable package. By using this plugin we can add export button Yajra DataTables for export data for Datatable. Now we want to yajra datatable package service in Laravel framework. For this we have to open config/app.php file and under this file in provider array we have to define this two package service, which you can see below.

config/app.php

<?php

return [

    'providers' => [
      
     ---------------
        
        Yajra\DataTables\DataTablesServiceProvider::class,
        Yajra\DataTables\ButtonsServiceProvider::class,

      ---------------

    ],
];



After defining of service, now we want to publish DataTable Button service, for this we have go to command prompt and write following command.


php artisan vendor:publish --tag=datatables-buttons


This command will publish datatables buttons plugin service in Laravel. Now we can use datatables buttons in Laravel framework for add export button Datatable.

5 - Create DataTable Class


In this tutorial, we will use DataTable class for load data in jQuery Datatable plugin and add export button in DataTable for export data in CSV or Excel file. For this we have to create DataTable class, for this we have go to command prompt and write following command.


php artisan datatables:make ExportDataTable


This command will make DataTables/ExportDataTable.php class file in app directory. So, we have to open this file, and in this class file we have to import model class. In this class file, we can see following method.

dataTable($query) - This method will build DataTable class.
query(ExportDataTable $model) - This method is used for get the query source of DataTable. In this method we have define data fetch query.
html() - This method will build your html code and Ajax jQuery script for initialize jQuery Datatable plugin. In this method we can define which export button you want to add in Datatable.
getColumns() - In this method we have to define Mysql table column name.
filename() - This method is used for get the file name for export.

app/DataTables/ExportDataTable.php

<?php

namespace App\DataTables;

use App\Export;
use App\DataTables\ExportDataTable;
use Yajra\DataTables\Html\Button;
use Yajra\DataTables\Html\Column;
use Yajra\DataTables\Html\Editor\Editor;
use Yajra\DataTables\Html\Editor\Fields;
use Yajra\DataTables\Services\DataTable;

class ExportDataTable extends DataTable
{
    /**
     * Build DataTable class.
     *
     * @param mixed $query Results from query() method.
     * @return \Yajra\DataTables\DataTableAbstract
     */
    public function dataTable($query)
    {
        return datatables()
            ->eloquent($query);
    }

    /**
     * Get query source of dataTable.
     *
     * @param \App\ExportDataTable $model
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function query(ExportDataTable $model)
    {
        //return $model->newQuery();
        $data = Export::select();
        return $this->applyScopes($data);
    }

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

    /**
     * Get columns.
     *
     * @return array
     */
    protected function getColumns()
    {
        return [
            Column::make('id'),
            Column::make('firstName'),
            Column::make('lastName'),
            Column::make('created_at'),
            Column::make('updated_at'),
        ];
    }

    /**
     * Get filename for export.
     *
     * @return string
     */
    protected function filename()
    {
        return 'Export_' . date('YmdHis');
    }
}



6 - Create Controller Class


In Laravel framework for handle http request, we have to ceate Controller class. In Laravel framework controller class file has been store in app/Http/Controllers follder. For create controller class file, we have to go command prompt and write following command.


php artisan make:controller ExportController


This command will create ExportController.php controller class file. In this file, first we want to import ExportDataTable.php class and then after we have to make index method which will render ExportDataTable class data and load in export.blade.php view file.

app/Http/Controllers/ExportController.php

<?php

namespace App\Http\Controllers;

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

class ExportController extends Controller
{
    public function index(ExportDataTable $dataTable)
    {
     return $dataTable->render('export');
    }
}



7 - Create View Blade File


For display output in Laravel framework, it has been used blade templating engine for display HTML output in browser. In Laravel framework view files has been store in resources/views folder. In this directory we have already create export.blade.php file. This file will display Mysql table data in DataTable by using Yajra Datatable package. And with data you can also find export csv or excel button also.

resources/views/export.blade.php

<html>
  <head>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Add CSV Excel Export Button in Yajra Laravel Datatable</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
    <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>  
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.0.3/css/buttons.dataTables.min.css">
    <script src="https://cdn.datatables.net/buttons/1.0.3/js/dataTables.buttons.min.js"></script>
    <script src="{{ asset('vendor/datatables/buttons.server-side.js') }}"></script>
  </head>
  <body>
    <div class="container">    
      <br />
      <h3 align="center">Add CSV Excel Export Button in Yajra Laravel Datatable</h3>
      <br />
      <div class="table-responsive">
        <div class="panel panel-default">
          <div class="panel-heading">Sample Data</div>
          <div class="panel-body">
            
            {!! $dataTable->table() !!}

            {!! $dataTable->scripts() !!}
          </div>
        </div>        
      </div>
      <br />
      <br />
    </div>
  </body>
</html>



8 - Set Route


In next we want to set the route of controller class method. For set route we have to open routes/web.php file and in this file we have to define route which you can find below.

routes/web.php


Route::resource('export', 'ExportController');



9 - Run Laravel Application


Now all set and we are ready to check output in browser. But before check output in browser, we have to first start Laravel application server. For this we have go to command prompt and write following command.


php artisan serve


This command will start Laravel server and provide base url of Laravel application. For test above code we have to write following url.


http://127.0.0.1:8000/export


So this is step by step processing of How to add export button in Laravel Yajra DataTable package. In this tutorial, we have first learn How to generate fake data in Laravel and then after we have seen how to load data in Datatable in Laravel framework by using Yajra Datatable package and then after we have seen how can we export Yajra DataTable data in CSV or Excel file format by using Yajra DataTable buttons plugin. So If you have found this tutorial helpful to you then do not forget to share it.

4 comments: