Import And Export Excel In Laravel

I would like to share with you how to export and import Excel spreadsheets or CSV files to databases in the Laravel 5.7 framework. I will show you a step-by-step example of importing CSV or excel files and exporting CSV or excel files using maatwebsite/excel composer package.

In this example, we will use maatwebsite/excel composer package for import and export task. maatwebsite/excel provides an easy way to import and export using a database model. maatwebsite/excel updated version and they provide a great way to import and export data from the database, so first follow a few steps to get an example.

Step 1 : Install Laravel 5.7 Project

In first step, we will install Laravel 5.7 application using bellow command, So open your terminal OR command prompt and run bellow command:

composer create-project --prefer-dist laravel/laravel blog

Step 2: Install Maatwebsite Package

In this step, we need to install Maatwebsite package via the Composer package manager

composer require maatwebsite/excel

Open the config/app.php file and add the service provider and aliase.

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

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

Then you have to also make publish a configuration file by using the following command to create a new config file named "config/excel.php".
 

php artisan vendor:publish

Step 3: Create Dummy Records

In this step, we have to require "users" table with some dummy records, so we can simply import and export. So first you have to run default migration that provided by laravel using following command:

php artisan migrate

php artisan tinker

factory(App\User::class, 20)->create();

Step 4: Add Routes

we need to create a route of import-export file. so open your "routes/web.php" file and add the following route.

Route::get('export', 'ImportExportController@exportExcel')->name('export');
Route::get('importExportView', 'ImportExportController@importExportView');
Route::post('import', 'ImportExportController@importExcel')->name('import');

Step 5: Create Import Class

The maatwebsite 3 version provides a way to build an import class and we have to use in the controller. So it would be a great way to create a new Import class.

So you have to run the following command and change the following code on that file:

php artisan make:import UsersImport --model=User

It will be create UsersImport Class in this path app/Imports/UsersImport.php

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

Step 6: Create Export Class

The maatwebsite 3 version provides a way to built an export class and we have to use in the controller. So it would be a great way to create a new export class.

So you have to run the following command and change the following code on that file:

php artisan make:export UsersExport --model=User

It will create UsersImport Class in this path app/Exports/UsersExports.php

namespace App\Exports;
  
use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;
  
class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

Step 7: Create Controller

In this step, now we should create a new controller as ImportExportControllerin this path "app/Http/Controllers/ImportExportController.php".

This controller will manage all importExportView, export, and import requests and return a response, so put below content in the controller file.

namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Exports\UsersExport;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
  
class ImportExportController extends Controller
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function importExportView()
    {
       return view('import');
    }
   
    /**
    * @return \Illuminate\Support\Collection
    */
    public function exportExcel() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
   
    /**
    * @return \Illuminate\Support\Collection
    */
    public function importExcel() 
    {
        Excel::import(new UsersImport,request()->file('file'));
           
        return back();
    }
}

Step 8: Create Blade File

In the Last step, let's create import.blade.php "resources/views/import.blade.php" for layout and we will write design code here and put the following code:

<!DOCTYPE html>
<html>
<head>
    <title>Import And Export Excel In Laravel 5.7 | Eng.Mahmoud Abd Elhalim</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">
            Import And Export Excel In Laravel 5.7 | Eng.Mahmoud Abd Elhalim
        </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>
<div class="container text-center">
    <a class="" href="http://mahmoudabdelhalim.com/">Eng| Mahmoud Abd Elhalim</a>
</div>
</body>
</html>

Thanks, and enjoy...