Laravel6 - Excel and CSV import export using maatwebsite/excel

We will share with you in this article how to excel and CSV import-export using maatwebsite/excel laravel package. maatwebsite/excel is a one of the best laravel packages. you need to any time in your laravel application any data store excel or CSV from database and any database data download fron excel and csv formate. you can be done this task or functionality in your laravel application help of maatwebsite/excel package.

Excel / CSV import and export is very common functionality in any application. when you have large data and you want to insert it in database then it can help a lot. as well you want back up database data into the excel or CSV file. both of functionality you can be done with maatwebsite/excel laravel package

What is new maatwebsite v.3

Here are version 3.0 many changes you can see. if you used an old version of maatwebsite/excel before then you can know all. ALL Laravel Excel 2.* methods are deprecated and will not be able to use in 3.0.

• Excel::load() is removed and replaced by Excel::import($yourImport)
• Excel::create() is removed and replaced by Excel::download/Excel::store($yourExport)
• Excel::create()->string('xlsx') is removed an replaced by Excel::raw($yourExport, Excel::XLSX)
• 3.0 provides no convenience methods for styling, you are encouraged to use PhpSpreadsheets native methods.

If you before never used or done Excel and CSV import-export functionality in laravel don't worry here we will share with you all step by step. just the following steps.

In this demo example e are create one transactions table and we are export this table data into excel or CSV file and also import excel file data from the database. our table looks like this.

| id | name_on_card | card_no | exp_month | exp_year | cvv |
|----|--------------|---------|-----------|----------|-----|
|    |              |         |           |          |     |
|    |              |         |           |          |     |
|    |              |         |           |          |     |

Before implementing excel/CSV import-export functionality in laravel6 help of maatwebsite/excel package. check the following requirement.

Requirements

• PHP: ^7.0
• Laravel: ^5.5
• PhpSpreadsheet: ^1.6
• PHP extension php_zip enabled
• PHP extension php_xml enabled
• PHP extension php_gd2 enabled

Preview

Step - 1 Package Installation

First, we need to install maatwebsite/excel package in laravel application help of the following composer command.

composer require maatwebsite/excel

Step - 2 Package Configuration

The Maatwebsite\Excel\ExcelServiceProvider is auto-discovered and registered by default.

If you want to register it yourself, add the ServiceProvider in config/app.php:

'providers' => [
    /*
     * Package Service Providers...
     */
    Maatwebsite\Excel\ExcelServiceProvider::class,
]

The Excel facade is also auto-discovered.

If you want to add it manually, add the Facade in config/app.php:

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

To publish the config, run the vendor publish command:

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

This will create a new config file named config/excel.php. you can also change the default setting in this file and set new one options on your own requirements.

Step - 3 Create Migration

Now, we need to create a migration for transactions the table. run the following command in your terminal.

php artisan make:migration create_transactions_tbl

After running this command, then open that created file that will be created on database/migrations a folder. just open it and put the following code into that migration file.

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class Transactions extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('transactions', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name_on_card');
            $table->string('card_no');
            $table->string('exp_month');
            $table->string('exp_year');
            $table->string('cvv');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('transactions');
    }
}

and then run php artisan migrate commands in your terminal. then your transactions table will be created into your database, which you set into in your project .env file.

Step - 4 Create Model

After, create trasactions table, then you need to create a model. simple run the following command in your terminal

php artisan make:model Transaction

After, hit this command app/Transaction.php file will be created automatically. open it and make the following changes into Transaction.php file.

app/Transaction.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Transaction extends Model
{
    protected $table = 'transactions';
    protected $guarded = array();
}

Step - 5 Create Routes

After installing package and configure all the set up then the next things is make following three routes in routes/web.php file.

// Route for view/blade file.
Route::get('importExportView', 'MaatwebsiteController@importExportView')->name('importExportView');
// Route for export/download tabledata to .csv, .xls or .xlsx
Route::get('exportExcel/{type}', 'MaatwebsiteController@exportExcel')->name('exportExcel');
// Route for import excel data to database.
Route::post('importExcel', 'MaatwebsiteController@importExcel')->name('importExcel');

Step - 6 Create Import Class

maatwebsite 3 version provides a way to built import class and we have to use it in the controller. So it would be a great way to create a new Import class. So you have to run following command and change following code on that file:

php artisan make:import TransactionsImport --model=Transaction

Excel demo screenshot for import

app/Imports/TransactionsImport.php

<?php

namespace App\Imports;

use App\Transaction;
use Maatwebsite\Excel\Concerns\ToModel;

class TransactionsImport implements ToModel
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        return new Transaction([
            'name_on_card'     => $row[0],
            'card_no'    => $row[1], 
            'exp_month'    => $row[2], 
            'exp_year'    => $row[3], 
            'cvv'    => $row[4], 
        ]);
    }
}

Step - 7 Create Export Class

maatwebsite 3 versions provides a way to built export class and we have to use it in the controller. So it would be a great way to create a new Export class. So you have to run following command and change following code on that file:

php artisan make:export TransactionsExport --model=Transaction

app/Exports/TransactionsExport.php

<?php

namespace App\Exports;

use App\Transaction;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\FromCollection;

class TransactionsExport implements FromCollection, WithHeadings, WithMapping
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return Transaction::all();
    }

    public function headings(): array
    {
        return [
            'Name On Card',
            'Card No.',
            'Exp Month',
            'Exp. Year',
            'CVV',
        ];
    }

    public function map($transaction): array
    {
        return [
            $transaction->name_on_card,
            'XXXXXXXXXXXX' . substr($transaction->card_no, -4, 4),
            $transaction->exp_month,
            $transaction->exp_year,
            $transaction->cvv,
        ];
    }
}

Step - 8 Create Controller

After, done above two routes then we need to create MaatwebsiteController.php controller file help of following artisan command.

php artisan make:controller MaatwebsiteController

After running this command your MaatwebsiteController.php file automatic created on app/Http/Controllers the folder. just open it and write the following code into that file.

app/Http/Controllers/MaatwebsiteController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Exports\TransactionsExport;
use App\Imports\TransactionsImport;
use Maatwebsite\Excel\Facades\Excel;

class MaatwebsiteController extends Controller
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function importExportView()
    {
       return view('maatwebsiteexcel');
    }
   
    /**
    * @return \Illuminate\Support\Collection
    */
    public function exportExcel($type) 
    {
        return Excel::download(new TransactionsExport, 'transactions.'.$type);
    }
   
    /**
    * @return \Illuminate\Support\Collection
    */
    public function importExcel(Request $request) 
    {
        Excel::import(new TransactionsImport,$request->import_file);
           
        return back();
    }
}

Step - 9 Create Blade File

After done the controller file then we create one laravel blade HTML file that simple design. Now we are creating one simple blade file in resources/views/maatwebsiteexcel.blade.php file and here we are making a very simple HTML layout for import and export excel or CSV.

resources/views/maatwebsiteexcel.blade.php

@extends('layouts.app')

@section('content')
<div class="container">
	@if($message = Session::get('success'))
		<div class="alert alert-info alert-dismissible fade in" role="alert">
	      <button type="button" class="close" data-dismiss="alert" aria-label="Close">
	        <span aria-hidden="true">×</span>
	      </button>
	      <strong>Success!</strong> {{ $message }}
	    </div>
	@endif
	{!! Session::forget('success') !!}
	<br />
	<a href="{{ URL::to('exportExcel/xls') }}"><button class="btn btn-success">Download Excel xls</button></a>
	<a href="{{ URL::to('exportExcel/xlsx') }}"><button class="btn btn-success">Download Excel xlsx</button></a>
	<a href="{{ URL::to('exportExcel/csv') }}"><button class="btn btn-success">Download CSV</button></a>
	<form style="border: 4px solid #a1a1a1;margin-top: 15px;padding: 10px;" action="{{ route('importExcel') }}" class="form-horizontal" method="post" enctype="multipart/form-data">
		{{ csrf_field() }}
		<input type="file" name="import_file" />
		<button class="btn btn-primary">Import File</button>
	</form>
</div>
@endsection

Conclusion

As you can see, Excel import-export is very easy to use in laravel application help of maatwebsite/excel package.

We hope these tutorials help everyone. if you have any issues or questions reagarding excel import export so please comment below. Thanks..