How to import and export to excel and csv in laravel 5 using maatwebsite

Introduction


In this tutorial, we will learn how to export and import data to excel / csv sheet with maatwebsite.

Laravel Excel is intended at being Laravel-flavoured PhpSpreadsheet: a simple, but an elegant wrapper around PhpSpreadsheet with the goal of simplifying exports and imports.

PhpSpreadsheet is a library written in pure PHP and providing a set of classes that allow you to read from and to write to different spreadsheet file formats, like Excel and LibreOffice Calc.

I have already installed and setup my Laravel project. If you want to setup fresh installation of Laravel You can follow below link

Install and setup the Laravel project from scratch

Requirements


  1. Laravel ^5.5
  2. maatwebsite / laravel-excel
  3. PhpSpreadsheet ^1.6
  4. PHP Extension for excel
    1. PHP extension php_zip
    2. PHP extension php_xml
    3. PHP extension php_gd2

Maatwebsite/Laravel-excel package installation


Before we start to install maatwebsite/excel package. Install the following extensions that require the package.

sudo apt install php-xml
sudo apt install php7.0-gd
sudo apt install php7.0-mbstring
sudo apt install php-zip

To install maatwebsite excel package navigate to your project directory and enter the following command

composer require maatwebsite/excel

The above command will install maatwebsite/excel package as well as phpSpreadsheet. Next, we need to register the Maatwebsite\Excel\ExcelServiceProvider in the app.php file which can be located under your-project/config directory. To do so copy and paste the following code to the app.php file.

'providers' => [
    ...
    Maatwebsite\Excel\ExcelServiceProvider::class,
]

add the following facade in the same file app.php

'aliases' => [
    ...
    'Excel' => Maatwebsite\Excel\Facades\Excel::class,
]

Next, Enter the following command to publish the config file

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

This command will create a new file in config/Excel.php

 

Step 2. Create migration

For this tutorial, we will create a table posts to test the export and import of excel sheet.

Let's quickly create a migration. To do so enter the following command.

php artisan make:migration create_posts_table

The above command will create a migration file under database/migrations. Edit the file and the following code

#database/migrations/2019_10_22_create_posts_table

public function up()
{
    Schema::create('posts', function (Blueprint $table) {
        $table->increments('id');
        $table->string('title');
        $table->string('description');
    });
}

Next, run the following command to run the migration.

php artisan migrate

The above command will create a posts table in your database.

Now let's put some data in posts table using laravel factories. To do so create a factory class and a seeder class. Enter the below command to create

php artisan make:factory PostFactory
php artisan make:seeder PostSeeder

Now add the below code to PostFactory.

#database/factory/PostFactory.php

<?php

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

use App\Post;
use Faker\Generator as Faker;

$factory->define(Post::class, function (Faker $faker) {
    return [
        'title' => $faker->sentence,
        'description' => $faker->paragraph
    ];
});

Next, add the following code to PostSeeder.

#database/seeds/PostSeeder.php

public function run()
{
    factory('App\Post', 20)->create();
}

Next call the PostSeeder class to database seeder file

#database/seeds/DatabaseSeeder.php

public function run()
{
    $this->call(PostSeeder::class);
}

Finally, run the following command to seed (insert the dummy data) to the table.

php artisan db:seed

Export data using laravel excel

To export, the data let's quickly list all the posts. To do so we need to create a controller.

php artisan make:controller PostController

Now create two functions index to list all the posts and exportPost to export the post table.

#app/Http/Controllers/PostController.php

public function index()
{
    $posts = \App\Post::all();

    return view('posts', compact('posts'));
}

public function exportPost()
{
    //
}

For now, exportPost function is empty. we will come to this function later. Now create a view file to list all the posts.

#resources/views/posts.blade.php 

@extends('layouts.app')

@section('content')
<div class="container">
    <div class="row justify-content-center">
        <div class="col-md-4">
            <h2>Posts</h2>
        </div>
        <div class="col-md-4">
            <a href="/export-posts" class="float-right btn btn-danger">Export</a>
        </div>
        <div class="col-md-8">
            <table class="table">
                <thead>
                    <tr>
                        <th>Id</th>
                        <th>Title</th>
                        <th>Description</th>
                    </tr>
                </thead>
                <tbody>
                    @foreach($posts as $post)
                    <tr>
                        <td>{{ $post->id }}</td>
                        <td>{{ $post->title }}</td>
                        <td>{{ $post->description }}</td>
                    </tr>
                    @endforeach
                </tbody>
            </table>
        </div>
    </div>
</div>
@endsection

Now let's create a route for export-posts and to list the posts.

Route::get('/posts', 'HomeController@index');
Route::get('/export-posts', 'HomeController@exportPost');

Next, We'll create an export class with the following command.

php artisan make:export PostExport --model=Post

The above command will create a file under app/Export/PostExport.php. Now import the PostExport and Excel Facade in your controller. Copy and paste the following code above your PostController class.

#app/Http/Controllers/PostController.php

use App\Exports\PostExport;
use Maatwebsite\Excel\Facades\Excel;

Now replace the following code with exportPost function which we had left earlier.

#app/Http/Controllers/PostController.php

public function exportPost()
{
    return Excel::download(new PostExport, 'posts.xlsx');
}