Hello, in this tutorial, I’m going to show how to import and export excel sheet with custom headers. I’ll use Laravel Excel 3.1 package for this tutorial.

Table of Contents

1. Install Laravel and Basic Configurations
2. Install Laravel Excel Package
3. Create Migration, Model and Controller
4. Insert Dummy/Fake Data
5. Create Laravel Excel Import Class
6. Create Laravel Excel Export Class
7. Create Functions in BookController
8. Make a View File
9. Register Web Routes
10. Run and Test Project

Step 1 : Install Laravel and Basic Configurations

Each Laravel project needs this thing. That’s why I have written an article on this topic. Please see this part from here: Install Laravel and Basic Configurations.

Step 2 : Install Laravel Excel Package

Let’s install the Laravel Excel means maatwebsite/excel package first using this artisan command:

composer require maatwebsite/excel

The Maatwebsite\Excel\ExcelServiceProvider is auto-discovered and registered by default.

Step 3 : Create Migration, Model and Controller

For testing purpose, we are going to create a table called books. To do database tasks, we are going to create a controller and model too. Run this command to create the three things at once:

php artisan make:model Book -mcr

# Modify Migration File:

Open the newly created migration file from database/migrations and update the up() method like this:

public function up()
{
    Schema::create('books', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name');
        $table->string('author');
        $table->timestamps();
    });
}

Run this below command to migrate the migration file:

php artisan migrate

# Modify Mode:

Open the Book model from app folder and add a $fillable array like this:

class Book extends Model
{
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name', 'author',
    ];
}

Step 4 : Insert Dummy/Fake Data

In this step, let’s insert a few dummy data in the books table. We have to create a Book factory. Lets’ create:

php artisan make:factory BookFactory --model=Book

Open the BookFactory.php from database/factories folder and paste the below code:

<?php

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

use App\Book;
use Faker\Generator as Faker;

$factory->define(Book::class, function (Faker $faker) {
    return [
        'name' => $faker->text(20),
        'author' => $faker->name(),
    ];
});

Now run this command to insert 5 fake data in the books table.

 # open tinker console
php artisan tinker
# insert fake data
factory(App\Book::class, 5)->create();

Step 5 : Create Laravel Excel Import Class

In Laravel Excel 3, they provide the way to built import class. It’s better than other ways.

php artisan make:import BooksImport --model=Book

Open BooksImport.php from app\Imports folder and create model like this:

<?php

namespace App\Imports;

use App\Book;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class BooksImport implements ToModel, WithHeadingRow
{
    /**
     * @param array $row
     *
     * @return \Illuminate\Database\Eloquent\Model|null
     */
    public function model(array $row)
    {
        return new Book([
            'name' => $row['name'],
            'author' => $row['author'],
        ]);
    }
}

Step 6 : Create Laravel Excel Export Class

In Laravel Excel 3, they also provide the way to built export class. We will use their way. So, let’s create an export class named BooksExport:

php artisan make:export BooksExport --model=Book

Open BooksExport.php from app\Exports folder and paste the below code:

<?php

namespace App\Exports;

use App\Book;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class BooksExport implements FromCollection, WithHeadings
{
    /**
     * @return \Illuminate\Support\Collection
     */
    public function collection()
    {
        return Book::all();
    }

    public function headings(): array
    {
        return [
            'ID',
            'Name',
            'Author',
            'Created At',
            'Updated At',
        ];
    }
}

Step 7 : Create Functions in BookController

Let’s create three functions called index, import and export. Index function to display all books, import to import data and export function to export data in excel format.

Open BookController from app\Http\Controllers and paste this code:

<?php

namespace App\Http\Controllers;

use App\Book;
use App\Exports\BooksExport;
use App\Imports\BooksImport;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;

class BookController extends Controller
{
    /**
     * Display a listing of the resource.
     */
    public function index()
    {
        $books = Book::all();

        return view('books')->with('books', $books);
    }

    /**
     * Import function
     */
    public function import(Request $request)
    {
        if ($request->file('imported_file')) {
            Excel::import(new BooksImport(), request()->file('imported_file'));
            return back();
        }
    }


    /**
     * Export function
     */
    public function export()
    {
        return Excel::download(new BooksExport(), 'books.xlsx');
    }

}

Step 8 : Make a View File

Go to resources/views directory and create a view file called books.blade.php. Then copy-paste this code:

<!doctype html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="csrf-token" value="{{ csrf_token() }}"/>
    <title>Laravel 6 Import Export Excel with Heading using Laravel Excel 3.1 - MyNotePaper</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body>
<div class="container">
    <div class="text-center" style="margin: 20px 0px 20px 0px;">
        <a href="https://shouts.dev/" target="_blank"><img src="https://i.imgur.com/hHZjfUq.png"></a><br>
        <span class="text-secondary">Laravel 6 Import Export Excel with Heading using Laravel Excel 3.1</span>
    </div>
    <br/>

    <div class="clearfix">
        <div class="float-left">
            <form class="form-inline" action="{{url('books/import')}}" method="post" enctype="multipart/form-data">
                {{csrf_field()}}
                <div class="form-group">
                    <div class="custom-file">
                        <input type="file" class="custom-file-input" name="imported_file"/>
                        <label class="custom-file-label">Choose file</label>
                    </div>
                </div>
                <button style="margin-left: 10px;" class="btn btn-info" type="submit">Import</button>
            </form>
        </div>
        <div class="float-right">
            <form action="{{url('books/export')}}" enctype="multipart/form-data">
                <button class="btn btn-dark" type="submit">Export</button>
            </form>
        </div>
    </div>
    <br/>

    @if(count($books))
        <table class="table table-bordered">
            <thead>
            <tr>
                <td>ID</td>
                <td>Name</td>
                <td>Author</td>
            </tr>
            </thead>
            @foreach($books as $book)
                <tr>
                    <td>{{$book->id}}</td>
                    <td>{{$book->name}}</td>
                    <td>{{$book->author}}</td>
                </tr>
            @endforeach
        </table>
    @endif

</div>

</body>
</html>

Step 9 : Register Web Routes

We are at the end. Let’s create three web routes:

Route::get('books', '[email protected]');
Route::post('books/import', '[email protected]');
Route::get('books/export', '[email protected]');

Step 10 : Run and Test Project

We have completed all the steps. Let run the project and visit books route.

Here’s my output:

After clicking on the Export button, an excel file called books.xlsx will automatically start to download. I’ve opened books.xlsx and saw like this:

Then I was trying to import the exported books.xlsx file and it was successful.

The tutorial is over. You can download this project from GitHub. Thank you. ?