Import, Export from Excel to Database in Laravel 5.8, 5.7, 5

This tutorial is created to Import, Export data from an excel sheet to database in Laravel Framework using maatwebsite/excel package. It provides an easy way to import and export using database model. It will work with Laravel 5.8, 5.7, 5.6.

Laravel Import, Export – Excel CSV

1. Install Laravel

First, let’s install Laravel using the following command (Make sure, You have installed composer in your PC). Click on Install Composer If you haven’t installed Composer on your PC.

composer create-project --prefer-dist laravel/laravel excelApplication

2. Configure Database Details

You need to create the database at MYSQL and then we need to connect that database to the Laravel project (You can also use phpmyadmin to create the database). Open the .env file inside Laravel project and add the database credentials as below.

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE='Your DB Name'
DB_USERNAME='Your DB UserName'
DB_PASSWORD='Your DB Password'

3. Install Maatwebsite/excel Package

Install Mattwebsite/excel package via the composer manager, use the terminal to run the command below.

composer require maatwebsite/excel

4. Specify the configuration

After installing maatwebsite/excel package, Go to config/app.php file and add the service provider and aliase.

config/app.php

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

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

Type the following command to publish the above configuration settings. If you’re prompt with provide tag files, Choose “Provider: Maatwebsite\Excel\ExcelServiceProvider” to publish.

php artisan vendor:publish

5. Create a database model and migration

In this step, we’re going to create a Data model and migration

php artisan make:model Data --migration

The migration will be created under “database/migrations”. Edit the file with the code below to create Data Table.

‘date’_create_data_table.php

<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateDataTable extends Migration
{

public function up()
{
    Schema::create('data', function (Blueprint $table) {
        $table->increments('id');
        $table->string('title');
        $table->text('description');
        $table->timestamps();
    });
}

public function down()
{
    Schema::dropIfExists('data');
}
}

Type the following command to run the migration

php artisan migrate

Open the generated model in “app/Data.php” Now, Write the schema inside Data.php file.

app/Data.php

<?php
namespace App;

use Illuminate\Database\Eloquent\Model;

class Data extends Model
{
    protected $fillable = ['title','description'];
}

6. Create DataController

Type the following command at your terminal to create DataController.php file in controllers

php artisan make:controller DataController

app/Http/Controllers/DataController.php

<?php
namespace App\Http\Controllers;

use App\Data;
use Excel;
use Illuminate\Http\Request;

class DataController extends Controller
{

    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        return view('index');
    }

    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function downloadData($type)
    {
        $data = Data::get()->toArray();

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

    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function importData(Request $request)
    {
        $request->validate([
            'import_file' => 'required'
        ]);

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

        if($data->count()){
            foreach ($data as $key => $value) {
                $arr[] = ['title' => $value->title, 'description' => $value->description];
            }

            if(!empty($arr)){
                Data::insert($arr);
            }
        }

        return back()->with('success', 'Insert Record successfully.');
    }
}

In this step, Go to ‘routes->web.php’ file, add the following line of code.

Route::get('/', 'DataController@index');
Route::get('downloadData/{type}', 'DataController@downloadData');
Route::post('importData', 'DataController@importData');

6. Create the view File

Create index.blade.php in resources/views folder and add the following code.

resources/views/index.blade.php

<html lang="en">
<head>
    <title>Laravel Import Export to Excel , CSV Example - bishrulhaq.com</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
</head>
<style>
    .container{
        margin-top: 10%;
    }
</style>
<body>
<div class="container">
    @if ($errors->any())
        <div class="alert alert-danger">
            <a href="#" class="close" data-dismiss="alert" aria-label="close">×</a>
            <ul>
                @foreach ($errors->all() as $error)
                    <li>{{ $error }}</li>
                @endforeach
            </ul>
        </div>
    @endif

    @if (Session::has('success'))
        <div class="alert alert-success">
            <a href="#" class="close" data-dismiss="alert" aria-label="close">×</a>
            <p>{{ Session::get('success') }}</p>
        </div>
    @endif
    <div class="card">
        <div class="card-body">
            <h5 class="card-title">Laravel Import Export to Excel , CSV Example</h5>
            <a href="{{ url('downloadData/xlsx') }}"><button class="btn btn-dark">Download Excel xlsx</button></a>
            <a href="{{ url('downloadData/xls') }}"><button class="btn btn-success">Download Excel xls</button></a>
            <a href="{{ url('downloadData/csv') }}"><button class="btn btn-info">Download CSV</button></a>

            <form style="border: 4px solid #a1a1a1;margin-top: 15px;padding: 10px;" action="{{ url('importData') }}" class="form-horizontal" method="post" enctype="multipart/form-data">
                @csrf

                <input type="file" name="import_file" />
                <button class="btn btn-primary">Import File</button>
            </form>

        </div>
    </div>
</div>
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
</body>
</html>

Hope this tutorial helped you! Feel free to drop your opinion at the comment section.