Table of Contents
Introduction:
Exporting Data is extremely essential need of every Software you develop and when it comes to excel spreadsheet exporting it becomes a bit complex to implement, in this article I am going to provide perfect solution on this problem and will make it very easy for you.
After reading this article you will find simple way to implement this feature into your Laravel project.
Before going to start working on the solution you need to have a Laravel project ready, you can also use your existing Laravel project.
1. Project Setup
You can skip this step if you already have your Laravel project up and running.
Create New Project:
$ laravel new project-nameOR using composer:
$ composer create-project --prefer-dist laravel/laravel project-name "5.6.*"Database Settings
Open MySql and create new database for this tutorial.
Now go ahead and open your project into your favourite code editor and update .env file for database connectivity settings as showing below.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=ADD-DATABASE-NAME-HERE
DB_USERNAME=root
DB_PASSWORD=2. Database Migration and Load Test Records
Create Migration and Model:
Next we are going to generate Laravel Eloquent model and database migration file to create new database table, using php artisan commands.
Open your terminal/command prompt and navigate to project directory and execute following command
$ php artisan make:model Post -mYou should see message as showing below:
Model created successfully.
Created Migration: 2018_08_07_143410_create_posts_tableNext open create_posts_table migration file to add table columns
public function up()
{
Schema::create('posts', function (Blueprint $table) {
$table->increments('id');
$table->string('title');
$table->text('body');
$table->timestamps();
});
}Let’s migrate the database to create posts table, execute following command from terminal
$ php artisan migrateIf your database settings are correct then your should see following success message along with the list of migrated files:
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated: 2014_10_12_000000_create_users_table
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated: 2014_10_12_100000_create_password_resets_table
Migrating: 2018_08_07_143410_create_posts_table
Migrated: 2018_08_07_143410_create_posts_tableNow will have to update our Post eloquent model.
Open Post.php model to add fillable attribute, as we know Laravel framework by default provides protection against mass-assignment, so will have to specify our fillable attributes so that we can insert records into the table.
Post.php:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
protected $fillable = [
'title', 'body'
];
}Load Test Records:
To Export data will need some records into the database, so in this step will load up few records using Factories, I am intentionally using model factory here just get you understanding of using it.
Go ahead and use following command to generate new factory for our Post model
$ php artisan make:factory PostFactory --model=PostNext open up PostFactory.php file from database/factories directory and make changes to add Post fields along with using values from faker libraries:
<?php
use Faker\Generator as Faker;
$factory->define(App\Post::class, function (Faker $faker) {
return [
'title' => $faker->text(45),
'body' => $faker->paragraph
];
});Now use tinker tool to execute model factory to store number of records into the database table, use following command to do that
$ php artisan tinker
>>> factory(App\Post::class, 50)->create();
>>> exitThe above command should have 50 records inserted into the database, you can open mysql database to verify.
Now we are ready to use those records for our export operation.
3. Design Export View
Create Controller:
Let’s design front end view, which will have all records listed along with export to excel buttons, to do that first let’s create new controller called PostController use this command to generate controller
$ php artisan make:controller PostsControllerNext open PostController.php file into editor and add new index method as showing below, which will fetch all records with pagination and it will return view.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Post;
class PostsController extends Controller
{
public function index()
{
$posts = Post::paginate(5);
return view('welcome', ['posts' => $posts]);
}
}Designing – Listing records, Pagination and export Links:
Open welcome.blade.php file from resources/views folder and replace it’s script with below one:
<!doctype html>
<html lang="{{ app()->getLocale() }}">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Laravel</title>
<!-- Fonts -->
<link href="https://fonts.googleapis.com/css?family=Raleway:100,600" rel="stylesheet" type="text/css">
<link href="css/app.css" rel="stylesheet" type="text/css">
<style>
body{
padding-top: 40px;
}
</style>
</head>
<body>
<div class="container">
<h1>
Laravel 5.6 and maatwebsite/excel 3.0 Export demo
</h1>
<div class="form-group">
<a href="#" class="btn btn-success">Export to .xlsx</a>
<a href="#" class="btn btn-primary">Export to .xls</a>
</div>
<table class="table table-striped table-bordered ">
<thead>
<tr>
<th>Id</th>
<th>Title</th>
<th>Body</th>
</tr>
</thead>
<tbody>
@foreach($posts as $post)
<tr>
<td>{{ $post->id }}</td>
<td>{{ $post->title }}</td>
<td>{{ $post->body }}</td>
</tr>
@endforeach
</tbody>
</table>
{{ $posts->links() }}
</div>
</body>
</html>Update default Route:
Basically we need to connect our PostController and it’s index method the default route so that whenever we load up the project it will show up all records.
Open web.php file and make changes into the default route as showing below:
<?php
Route::get('/', 'PostsController@index');Next let’s test project to have a look on view and see if everything is working or not, you should see output as showing below:
4. Export Records
This is the step were we are going to export the data into excel spreadsheet and to do that the first thing we required to install maatwebsite/excel package.
This is the most popular package in Laravel community. it performs really well when it comes handle excel export and import operations.
maatwebsite/excel Installation:
Before going to install this package make sure you have following requirements are ready and installed/enabled on your development environment
- PHP: ^7.1
- PhpSpreadsheet: ^1.2
- PHP extension php_zip enabled
- PHP extension php_xml enabled
- PHP extension php_gd2 enabled
Use following command to start the installation with composer:
$ composer require maatwebsite/excelAfter completing the installation process use following command to publish the config file, this command will create new file called excel.php into config directory
$ php artisan vendor:publishImplement Exporting:
There are several way to export records using maatwebsite excel, amongst them here I am going to demonstrate most important and usable ways to implement export feature.
Export from collection:
This is very simple and quick way to start exporting, do this first will have to create export class, use following command to generate new PostsExport class, also make sure to focus on command.
This command will generate new file under app/Exports directory called PostsExport.php
$ php artisan make:export PostsExport --model=PostNext open PostsController.php file from app/Http/Controllers directory and add new method under PostsController class, as showing below:
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Post;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\PostsExport;
class PostsController extends Controller
{
public function index()
{
$posts = Post::paginate(5);
return view('welcome', ['posts' => $posts]);
}
public function export($type)
{
return Excel::download(new PostsExport, 'posts.' . $type);
}
}If you focus on above class, we basically added new method called export and we are providing instance of PostsExport class to the Excel Facade.
Next add new route in web.php file to access export method from the view
Route::get('/export/{type}', 'PostsController@export');Now open welcome.blade.php file and update export links to reference above route:
<a href="/export/xlsx" class="btn btn-success">Export to .xlsx</a>
<a href="/export/xls" class="btn btn-primary">Export to .xls</a>Now you can go ahead and reload project and click on Export link, you should get excel spreadsheet downloaded into your system called posts.xlsx or xls depending on your chosen link.
Export with heading:
In the above example if you see we are getting records exported successfully but without column headings, if you want to add headings then you need to update PostsExport class little bit more, to implement WithHeading interface as showing below:
<?php
namespace App\Exports;
use App\Post;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class PostsExport implements FromCollection, WithHeadings
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return Post::all();
}
public function headings() : array{
return [
'Id',
'Title',
'Body',
'Created At',
'Updated At',
];
}
}Now if you try to click export link again you should get heading added to the excel spreadsheet columns.
Export From Query
While exporting we also needs to fetch records depending on a specific query, it might required to add certain conditional queries depending on the project.
In this example we are going see how we can export records along with query, here I have create another export class called PostsQueryExport and here is the example:
$ php artisan make:export PostsQueryExport --model=Post<?php
namespace App\Exports;
use App\Post;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithHeadings;
class PostsQueryExport implements FromQuery, WithHeadings
{
use Exportable;
public function __construct(int $year)
{
$this->year = $year;
}
public function query()
{
return Post::query()->select('id', 'title', 'body')->whereYear('created_at', $this->year);
}
public function headings() : array
{
return [
'Id',
'Title',
'Body',
];
}
}Now in our post controller we can simple change export method allow using new Export Class called PostsQueryExport and pass the required argument value.
public function export($type)
{
return Excel::download(new PostsQueryExport(2018), 'posts.' . $type);
}Export From View
We can also export data from our blade view, this is always good option for us to directly export data from the view. Specially when it comes to have excel description along with column headings, this feature is come very handy it is really flexible and easy to use.
Let see how we can use our blade template view to to export into excel spreadsheet.
First as always we need to export class, one more thing we can also modify our existing export class, but just to make thing simple to understand will create brand new class for demonstration.
Create new class:
$ php artisan make:export PostsViewExport --model=PostOpen PostsViewExport.php file from app/Exports directory and update according to the script showing below:
<?php
namespace App\Exports;
use App\Post;
use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;
class PostsViewExport implements FromView
{
public function view() : View
{
return view('exports.posts', [
'posts' => Post::all()
]);
}
}As you can see in the above exporter we are calling a view called posts from exports folder which is not exists yet so will have to create one, go ahead and create new view under resources/views/exports/ called posts.blade.php
Open posts.blade.php file and add following html script:
<table>
<thead>
<tr>
<th>Title</th>
<th>Body</th>
</tr>
</thead>
<tbody>
@foreach($posts as $post)
<tr>
<td>{{ $post->title }}</td>
<td>{{ $post->body }}</td>
</tr>
@endforeach
</tbody>
</table>Now you just needs to call this new export class into the PostsController’s export method:
public function export($type)
{
return Excel::download(new PostsViewExport, 'posts.' . $type);
}We are done, if you try to run the demo again and click export you will see the excel spreadsheet being downloaded, the export class will convert HTML table into excel spreadsheet.
how to export selected posts from index page.
Say i selected only 1,2 and 3
and export only data related to above.
I think exporting using the just pure php code is more simpler and with just few line of codes.