Laravel 6 Import Export Excel with Heading using Laravel Excel 3.1
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
- Install Laravel and Basic Configurations
- Install Laravel Excel Package
- Create Migration, Model and Controller
- Insert Dummy/Fake Data
- Create Laravel Excel Import Class
- Create Laravel Excel Export Class
- Create Functions in BookController
- Make a View File
- Register Web Routes
- 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. ?Comment
Preview may take a few seconds to load.
Markdown Basics
Below you will find some common used markdown syntax. For a deeper dive in Markdown check out this Cheat Sheet
Bold & Italic
Italics *asterisks*
Bold **double asterisks**
Code
Inline Code
`backtick`Code Block```
Three back ticks and then enter your code blocks here.
```
Headers
# This is a Heading 1
## This is a Heading 2
### This is a Heading 3
Quotes
> type a greater than sign and start typing your quote.
Links
You can add links by adding text inside of [] and the link inside of (), like so:
Lists
To add a numbered list you can simply start with a number and a ., like so:
1. The first item in my list
For an unordered list, you can add a dash -, like so:
- The start of my list
Images
You can add images by selecting the image icon, which will upload and add an image to the editor, or you can manually add the image by adding an exclamation !, followed by the alt text inside of [], and the image URL inside of (), like so:
Dividers
To add a divider you can add three dashes or three asterisks:
--- or ***

Comments (0)