Laravel 5.6 – Import Export excel file using Maatwebsite

Maatwebsite Laravel Excel is a popular package for importing and exporting data to and from excel or csv file. I will explain step by stem using this package in our laravel application. When you are working with large datasets, you need to import or export data from your database to external file. For this, Maatwebsite Laravel Excel is a perfect package. From the official documentation, Laravel Excel features are:

• Easily export collections to Excel
• Export queries with automatic chunking for better performance
• Queue exports for better performance
• Easily export Blade views to Excel

Let’s get started integrating.

Installing Package

You can install the package using Composer by running the below command on your terminal.

composer require maatwebsite/excel

This will download the package and PhpSpreadSheet in our application. Now, add ServiceProvider in App/Config/app.php

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

Similarly, add Alias in the same file in alias array section.

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

After these settings are saved, publish the configuration files. To do this, run the below command.

php artisan vendor:publish

Create Countries Table and Models

We will create a migration file to create a table named ‘countries’. To create it, run the following command in your terminal:

php artisan make:migration create_countries_table –create='countries'

It creates a migration file under database/migrations. Open it and make some changes in up() function to fit according to our needs. The changed file looks like:

public function up()
    {
        Schema::create('countries', function (Blueprint $table) {
            $table->increments('id');

            $table->string('country');
            $table->string('country_code');

            $table->timestamps();
        });
    }
 

Now, run the migration to create table. For this, run the following command.

php artisan migrate

After creating table, let’s create Model for this table. Run the following command:

php artisan make:model Models/Country

This will create a Country Model inside App/Models/ directory. Open the model and make fields of table fillable as like below:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Country extends Model
{
    protected $fillable = ['country, country_code'];
}

Create Route
Add some routes for uploading csv file and for downloading files. Simply add the below routes in your routes/web.php file.

Route::get('import-export-excel', 'ExcelController@getExcel');
Route::post('import-excel', 'ExcelController@postExcel');
Route::get('download-excel/{type}', 'ExcelController@downloadExcel');

Create Controller

Now, create a controller file for processing our application logic. All our importing and exporting functionalities will be handled with this controller file named ExcelController.php. To create it, run the following command:

php artisan make:controller ExcelController

We have to make some changes to this controller. Final controller file looks like below:

hasFile('import_file')){
            $path = $request->file('import_file')->getRealPath();
            $data = Excel::load($path)->get();

            if($data->count()){
                foreach ($data as $key => $value) {
                    $arr[] = [
                                'country'      => $value->Country
                                'country_code' => $value->CountryCode,
                            ];
                }

                if( ! empty($arr)){
                    DB::table('products')->insert($arr);
                }
            }
        }

        return back()->with(['arr' => $arr]);
    }

    public function downloadExcel($type)
    {
        $data = Country::get()->toArray();

        return Excel::create('country', function($excel) use ($data) {
            $excel->sheet('mySheet', function($sheet) use ($data)
            {
                $sheet->fromArray($data);
            });
        })->download($type);
    }

}

Create View

We have successfully setup our controller, now let’s create view to upload and download file. Create a new default template as default.blade.php inresources/views/layouts

    
        
        
        

        

        
        
        

    
    
        
        


            @yield('content')

        

    


After this, lets create our view for uploading and downloading file. I will name this excel.bade.php in resources/views/. After uploading our data from file to database, it will display list of data inserted to database below form.
@extends('layouts.default')

@section('content')

    

        Download Excel xls
        Download Excel xlsx
        Download CSV

        

            {{ csrf_field() }}
            
            

            Import File
        
        

        @if(session()->has('arr'))
            
            @foreach($arr as $key => $value)
                
            @endforeach
            

            

                    
 {{ $value['country_code'] }}: 
                    
{{ $value['country'] }} 

                
        @endif
    

@endsection

Now, our application is ready to import any excel file and export database to any excel file. If you need countries excel file, you can download from  this link.

Hope you find it helpful  .