Laravel Import Export Excel & Csv from Database

Share Me
  •  
  •  
  •  
  • 1
  •  
  •  
  •  
  •  
  •  
  •  
  •  
    1
    Share

Today now I\in this tutorial, we will learn about how to import export excel or csv file in laravel application. Here i will write a simple tutorial of laravel  by using maatwebsite/excel package. It is very simple and also very easy to use with laravel application for import or export data to the database.

So now in this tutorial i will use maatwebsite/excel composer package for import and export data to our database. maatwebsite/excel provide us very easy way to import and also export by using database model.

Here i will show how to simple create import data to csv, xls file and also i will import data to database by using csv file in our laravel application. maatwebsite/excel is a updated version 3 and here they are provide a great way to import or export data from database. So just first follow few step to get a best example.

Step 1: Install Laravel Project

At first, we need to download the laravel fresh setup. By use this command we can download laravel project setup :

composer create-project --prefer-dist laravel/laravel blog
Step 2: Setup Database

After the successfully install laravel 8 Application, then please go to your project .env file and set up database credential and move next step :

DB_CONNECTION=mysql 
DB_HOST=127.0.0.1 
DB_PORT=3306 
DB_DATABASE=here your database name 
DB_USERNAME=here database username
DB_PASSWORD=here database password 
Step 3: Install maatwebsite/excel Package

So now in this step,we need to install maatwebsite/excel package in manager. Now by use define command in your terminal:

composer require maatwebsite/excel

Then now open config/app.php file and add service provider and aliase.

config/app.php

'providers' => [

    ....

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

	....

    'Excel' => Maatwebsite\Excel\Facades\Excel::class,
],
Step 4: Create Dummy Records

So we need to require “users” table also with some dummy records, so we can simply import and export. By use bellow the command.

php artisan migrate

Now after that we need to run following command to generate dummy users:

php artisan tinker
factory(App\User::class, 20)->create();
Step 5: Make Route

So now we can create to route in web.php file.

routes/web.php

//ImportExportController
Route::get('importExportView', 'ImportExportController@importExportView');
Route::get('export', 'ImportExportController@export')->name('export');
Route::post('import', 'ImportExportController@import')->name('import');
Step 6: Create Import Class

Now in maatwebsite 3 version provide a way to built import class and also we have to use in controller. So now it would be great way to create new Import class. just bellow this command in your terminal.

php artisan make:import UsersImport --model=User

app/Imports/UsersImport.php

<?php
   
namespace App\Imports;
   
use App\Model\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
    
class UsersImport implements ToModel, WithHeadingRow
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        return new User([
            'name'     => $row['name'],
            'email'    => $row['email'], 
            'password' => \Hash::make($row['password']),
        ]);
    }
}
Step 7: Create Export Class

So maatwebsite 3 version provide the best way to built export class and also we have to use in controller. So now it would be great way to create a new Export class. just bellow this command in your terminal.

php artisan make:export UsersExport --model=User

app/Exports/UsersExport.php

<?php
  
namespace App\Exports;
  
use App\Model\User;
use Maatwebsite\Excel\Concerns\FromCollection;
  
class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}
Step 8: Create Controller

Now we need to create a controller as name ImportExportController. by use this command in terminal.

php artisan make:controller ImportExportController

Now this controller will manage all importExportView, So export and import request and then return response, so put bellow content in controller file:

app/Http/Controllers/ImportExportController.php

<?php
   
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Exports\UsersExport;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
  
class MyController extends Controller
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function importExportView()
    {
       return view('import');
    }
   
    /**
    * @return \Illuminate\Support\Collection
    */
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
   
    /**
    * @return \Illuminate\Support\Collection
    */
    public function import() 
    {
        Excel::import(new UsersImport,request()->file('file'));
           
        return back();
    }
}
Step 9: Create View File

Now in this step you need to create blade view file.

resources/views/import.blade.php

<!DOCTYPE html>
<html>
<head>
    <title>Laravel 6 Import Export Excel to database Example - codingspoint.com</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
</head>
<body>
   
<div class="container">
    <div class="card bg-light mt-3">
        <div class="card-header">
            Laravel 6 Import Export Excel to database Example - codingspoint.com
        </div>
        <div class="card-body">
            <form action="{{ route('import') }}" method="POST" enctype="multipart/form-data">
                @csrf
                <input type="file" name="file" class="form-control">
                <br>
                <button class="btn btn-success">Import User Data</button>
                <a class="btn btn-warning" href="{{ route('export') }}">Export User Data</a>
            </form>
        </div>
    </div>
</div>
   
</body>
</html>

Step 10: Run Development Server

you can run to laravel project in your teminal.bellow command

php artisan serve

Now you can open bellow URL on your browser:

http://localhost:8000/importExportView

Read Also : Laravel 8 Eloquent Global Scope Tutorial Example

I hope it will help you. Also you can follow us on Facebook

About Shahriar Sagor

My name is Shahriar sagor. I'm a developer. I live in Bangladesh and I love to write tutorials and tips that will help to other Developer's. I am a big fan of PHP, Javascript, JQuery, Laravel, Codeigniter, VueJS, AngularJS and Bootstrap from the early stage.

View all posts by Shahriar Sagor →