Laravel Import Export Excel file

For web applications, importing Excel sheet data into our database and exporting data from the database into an Excel sheet, is an important feature. Because of this feature, we can easily perform batch import/export data by the web application. It is difficult in other Frameworks but easiest in laravel 8. It gives Maatwebsite/excel package to easily import/export data. In this article, we learn about, how data is exported and imported.

Features:

• Effectively send out assortments to Excel.
• Send out questions with programmed piecing for better execution.
• Line sends out for better execution.
• Effectively send out Blade perspectives to Excel.
• Effectively import to accumulations.
• Peruse the Excel record in pieces.
• Handle the import embeds in clumps.

Steps to laravel Import Export excel data: We want to follow a few steps to import and export data easily. There are 9 easy steps to follow.

1. Install new laravel project 
2. Configure Database details and model
3. Install maatwebsite/excel package
4. Create Routes 
5. Create import class for import data 
6. Create an export class for export data 
7. Create controller 
8. Create blade / view files
9. Run laravel project 

1. Install new laravel project: In step first, create a new laravel project named laravel-excel. Use the below command to create a new laravel project.

composer create-project laravel/laravel excel

2. Configure Database details and make a model: In the second step, set up database configuration in the .env file in laravel 8.

PHP

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=8258
DB_DATABASE=laravel
DB_USERNAME=localhost
DB_PASSWORD=

Make model: Then make a new model named User model to connect with the user table in the database. In user, the table contains various fields like ID, name, phone, and email.

3. Install maatwebsite/excel Package: Now, using composer we install package name maatwebsite/excel to export and import data by using composer

composer require maatwebsite/excel 

After installing the package we want to register the plugin’s service in providers and aliases.

We want to write the following code into the config/app.php file:

PHP

<?php
    'providers' => [
      Maatwebsite\Excel\ExcelServiceProvider::class, ],
    'aliases' => [
      'Excel' =>
      Maatwebsite\Excel\Facades\Excel::class, ],
?>

To publish the above configuration execute the vendor:publish command:

php artisan vendor:publish –provider=”Maatwebsite\Excel\ExcelServiceProvider” –tag=config

This command is to create a new config file config/excel.php

4. Create Routes: Then we create routes to handle requests from import and export files.

PHP

<?php
    use App\Http\Controllers\UserController;
    Route::get('/file-import',[UserController::class,
            'importView'])->name('import-view');
    Route::post('/import',[UserController::class,
            'import'])->name('import');
    Route::get('/export-users',[UserController::class,
            'exportUsers'])->name('export-users');
?>

5. Create Import class import for import data: The package maatwebsite/excel provides you to build an import class file. Using the following command, we create an import class file.

php artisan make:import ImportUser --model=User

The file will create at the app/Imports/ImportUser.php path:

PHP

<?php
namespace App\Imports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\ToModel;
class ImportUser implements ToModel
{
   public function model(array $row)
   {
       return new User([
           'name' => $row[0],
           'email' => $row[1],
           'password' => bcrypt($row[2]),
       ]);
   }
}

6. Create an Export class for export data: The package maatwebsite/excel provides you to build an export class file. Using the following command, we create an export class file.

php artisan make:export ExportUser --model=User

The file will create at the app/Exports/ExportUser.php path:

PHP

<?php
    namespace App\Exports;
    use App\Models\User;
    use Maatwebsite\Excel\Concerns\FromCollection;

    class ExportUser implements FromCollection {
        public function collection()
        {
            return User::select('name','email')->get();
        }
    }
?>

7. Create Controller: To display data from the database, we need to create one controller. Using the below command we create a controller named UserController.

php artisan makes: controller UserController

The above command will create a controller file UserController.php at the path app/Http/controllers directory.

Write the below code into the UserController.php file:

PHP

<?php

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use App\Imports\ImportUser;
use App\Exports\ExportUser;
use App\Models\User;

class UserController extends Controller
{
    public function importView(Request $request){
        return view('importFile');
    }

    public function import(Request $request){
        Excel::import(new ImportUser,
                      $request->file('file')->store('files'));
        return redirect()->back();
    }

    public function exportUsers(Request $request){
        return Excel::download(new ExportUser, 'users.xlsx');
    }
 }
?>

8. Create Blade/View file: To handle export and import at frontend of a web application, we need to create a view or blade file. Create a blade file at resources/views/importFile.blade.php:

HTML

<!DOCTYPE html>
<html>

<head>
    <title> Import and Export Excel data to database Using Laravel 5.8 </title>
    <link rel="stylesheet"
        href=
"https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
</head>

<body>
    <h6> Import and Export Excel data to
           database Using Laravel 5.8
    </h6>
    <div class="container">
        <div class="card bg-light mt-3">
            <div class="card-header">
                Import and Export Excel data
                  to database Using Laravel 5.8
            </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>

9. Run laravel project: Run the below command on command prompt and check http://localhost:8000/file-import in the browser:

php artisan serve

Output:

Laravel Import Export