Laravel Laravel 5.6

Ultimate way to export data into Excel spreadsheet in Laravel 5.6 Framework

Ultimate way to export data into Excel spreadsheet in Laravel 5.6 Framework
Written by Yogesh Koli

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:

OR using composer:

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.


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

You should see message as showing below:

Next open create_posts_table migration file to add table columns

Let’s migrate the database to create posts table, execute following command from terminal   

If your database settings are correct then your should see following success message along with the list of migrated files:

Now 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:


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 

Next open up PostFactory.php file from database/factories directory and make changes to add Post fields along with using values from faker libraries:

 Now use tinker tool to execute model factory to store number of records into the database table, use following command to do that

The 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 

Next 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.


Designing – Listing records, Pagination and export Links:

Open welcome.blade.php file from resources/views folder and replace it’s script with below one:


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:

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:

Records Listing

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:

After completing the installation process use following command to publish the config file, this command will create new file called excel.php into config directory


Implement 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

Next open PostsController.php file from app/Http/Controllers directory and add new method under PostsController class, as showing below:

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

Now open welcome.blade.php file and update export links to reference above route:

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:

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:

Now in our post controller we can simple change export method allow using new Export Class called PostsQueryExport and pass the required argument value.


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:

Open PostsViewExport.php file from app/Exports directory and update according to the script showing below:

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:

Now you just needs to call this new export class into the PostsController’s export method:

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.


Video Tutorial

About the author

Yogesh Koli

Yogesh Koli is a software engineer & a Blogger lives in India. He's driven by an addiction to learning and a love for adventure. he has 5+ years of experience working with the front-end, back-end, web application development, and system design.

Leave a Reply

avatar

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
Notify of