Laravel Export and Import Excel using maatwebsite package

This article goes into a detailed explanation of basic excel export and import operation in Laravel with the database table. For this, we will create a sample database table along with it’s model on which excel operations will be performed.

Before we jump into the implementation make sure you have following ready.

• Laravel Project setup

Alright, let’s dive into the steps.

First, we have to install maatwebsite/excel package that we are going to use for our export and import.

The following are the few requirements of the package. Make sure your system’s PHP installation have these extensions enabled.

Requirements

• PHP extension php_zip enabled
• PHP extension php_xml enabled
• PHP extension php_gd2 enabled

Installation

First, we need to have maatwebsite/excel package included in our Laravel Application.

For this run the following command in your terminal / command-line

composer require maatwebsite/excel

This will download the package and also phpoffice/phpspreadsheet package on which this package depends on.

This package comes along with a configuration file so that you can override the default configuration provided by the package. To publish the config file, run the vendor publish command

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

This will create a new config file named config/excel.php

This is all about installation, In the next step, we will get our sample database table and Model ready.

Generate Model and Database Table

Once we are done with the installation, we should be able to perform excel operation in our project. Lets create a sample database model say Excel using

php artisan make:model Excel -m

This will create a model Excel.php under directory App, and will also create a migration file along with it.

Let’s go ahead and modify the migration file to add a new column named ‘name‘

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('excels', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->timestamps();
        });
    }

Since we are creating a column ‘name’ in the table, we allow this column to be filled by Eloquent, add this add a fillable property in your Excel modal.

class Excel extends Model
{
    protected $fillable = [
        'name',
    ];
}

Let’s migrate the table

php artisan migrate

Export excel data from the database table

Exporting your excel directly from the database is very easy using this package. You just need to create an Export class and specify which model you are looking to export

php artisan make:export ExcelExport --model=Excel

This command will create the ExcelExport class in the directory app / Exports

class ExcelExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    use Exportable;
    public function collection()
    {
        return Excel::all();
    }
}

We can now use ExcelExport class to process the exports.

To demonstrate Let’s create a controller named ExcelController with following methods

<?php

namespace App\Http\Controllers;

use App\Exports\ExcelExport;
use App\Imports\ExcelImport;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Http\Request;


class ExcelController extends Controller 
{
    public function create() 
    {
        return view('excel.excel');
    }
    
    public function export() 
    {
        return Excel::download(new ExcelExport, 'excel.xlsx');

    }

}

create method will return a view with download button. And the export method will process the excel download using ExcelExport class.

Let’s add entries in our route file to back these methods.

Route::get('excel-export-import-demo', 'ExcelController@create');
Route::post('export', 'ExcelController@export');

Here the source code of view file.

@extends('layouts.app')

@section('content')
    <div class="container">
        <div class="row justify-content-center">
        <div class="card">
       <div class="card-header">Export excel File Example</div>
        <form action="/export" method="post" enctype="multipart/form-data">
                @csrf            
            <button type="sumbit" class="btn btn-primary"  />DownloadFile</button>
            </form>
            </div>
            
            <div class="card">
                <div class="card-header">import excel File Example</div>

                <div class="card-body">
                    @if ($message = Session::get('success'))

                        <div class="alert alert-success alert-block">

                            <button type="button" class="close" data-dismiss="alert">×</button>

                            <strong>{{ $message }}</strong>
                            
                        </div>

                    @endif
                   
                    @if (count($errors) > 0)
                        <div class="alert alert-danger">
                            <strong>Whoops!</strong> There were some problems with your input.<br><br>
                            <ul>
                                @foreach ($errors->all() as $error)
                                    <li>{{ $error }}</li>
                                @endforeach
                            </ul>
                        </div>
                    @endif


                        <form action="/import" method="post" enctype="multipart/form-data">
                            @csrf
                            <div class="form-group">
                                <input type="file" class="form-control-file" name="fileToUpload" id="exampleInputFile" aria-describedby="fileHelp">                              
                            </div>
                            <button type="submit" class="btn btn-primary">Import File</button>
                        </form>
                </div>
            </div>
        </div>
    </div>
@endsection

Now if you hit the url /excel-export-import-demo then you should see the below page.

On click of download, data from excel table will be downloaded in excel.xlsx file in download folder.

Import excel data into database table

php artisan make:import ExcelImport --model=Excel

This will create ExcelImport class in directory App > Import. Modify this file so it contains following code

<?php

namespace App\Imports;

use App\Excel;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;

class ExcelImport implements ToModel
{
    /**
     * @param array $row
     *
     * @return Excel|null
     */
    public function model(array $row)
    {
        return new Excel([
           'name'     => $row[1],
           
        ]);
    }
}

We are ready to import data from excel to a database table.

Let’s add a Route and controller method for import.

Route::post('import/', 'ExcelController@import');

Now let’s add the supporting controller method import in ExcelController.php

        public function import(Request $request) 
    {
        $request->validate([
            'fileToUpload' => 'required|file|max:2048|mimes:xls,xlsx',
        ]);

        Excel::import(new ExcelImport, request()->file('fileToUpload'));
        
        return back()->with('success', 'Excel Imported, Download to see the imported data.');
    }

On click of import data from the selected file will be imported in the database.

Demo