Excel Export and Import in Laravel 8

  1561 views   4 months ago Laravel

In your admin side, you need to download data in excel format. You also want to import data in database from the excel file. Excel export and import is basic requirement in the admin panel.

Maatwebsite/Laravel-Excel is simple and easy to implement Laravel package. In this article, we will share you how you can export and import data in excel format. We will create Laravel application from the scratch and implement excel export and import.

We will go through step by step. So let's get started below step by step:

  • Create fresh Laravel application
  • Create database migration
  • Install Maatwebsite/Laravel-Excel Package
  • Create routes and controller class.
  • Create excel export/import classes file.
  • Create blade file
     

Step: 1. Create Laravel application

In the first step, we will create new Laravel application using bellow command, So open your Terminal or command prompt and run bellow command:

composer create-project laravel/laravel excel

Step 2. Database migration

In your .env file, set the database name and MySQL users data.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=excel
DB_USERNAME=root
DB_PASSWORD=root

Laravel default have migration for users table. So we will use users data for excel export and import. Run the migration command to run create the table in database.

php artisan migrate

Step: 3. Install Maatwebsite/Laravel-Excel Package

In the third step, we will install Maatwebsite/Laravel-Excel package using below command.

composer require maatwebsite/excel

Now open config/app.php file and register package class in $providers array.

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

In the same file add the class in $aliases array.

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

Now publish the package config file. This will add config/excel.php file. You can change the settings for the excel from this file.

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

Step: 4. Create routes and controller class.

Now  we need to create routes of import export file. so open your routes/web.php file and add below routes.

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\ExcelController;

Route::get('excel/view', [ExcelController::class, 'index'])->name('index');
Route::get('excel/export', [ExcelController::class, 'export'])->name('export');
Route::post('excel/import', [ExcelController::class, 'import'])->name('import');

Now create the controller to handle the routes.

php artisan make:controller ExcelController

This will create controller file at app/Http/Controllers/ExcelController.php. Open the file add the methods as we have defined in route file.

<?php
   
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Exports\UsersExport;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
  
class ExcelController extends Controller
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function index()
    {
       return view('index');
    }
   
    /**
    * @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('excel'));
           
        return back();
    }
}

Step: 5. Create excel export/import classes file.

We have defined controller file. From the controller we will call excel class and make export and import. So we need to create export and import class.

Create import class using below command.

php artisan make:import UsersImport --model=User

Open app/Imports/UsersImport.php and add the below codes.

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

Same way run command for export

php artisan make:export UsersExport --model=User
<?php
  
namespace App\Exports;
  
use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;
  
class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

Step: 6. Create blade file.

In this last step we will create view file where we will export and import excel file.

Create resources/views/index.blade.php file and add the HTML code.

<!DOCTYPE html>
<html>
    <head>
        <title>Excel Export/Import</title>
        <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
    </head>
    <body>
        <div class="container">
            <div class="row mt-3">
                <div class="col-12">
                    <form action="{{ route('import') }}" method="post" enctype="multipart/form-data">
                        @csrf
                        <div class="mb-3">
                            <label for="excel" class="form-label"></label>
                            <input type="file" name="excel" class="form-control">
                        </div>
                        <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>

Now run the artisan command to start Laravel application.

php artisan serve

Open your browser and go to https://localhost:8000/excel/view You can simply upload the data and import.

Thanks for giving time in reading article. I hope you liked this article and will help you.

Author : Harsukh Makwana
Harsukh Makwana

Hi, My name is Harsukh Makwana. i have been work with many programming language like php, python, javascript, node, react, anguler, etc.. since last 5 year. if you have any issue or want me hire then contact me on [email protected]