Import, Export from Excel to Database in Laravel 5.8, 5.7, 5.6.

• by BH
• Laravel
• 2019-06-01
• ....

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.

1. Install Laravel

   First, let 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

   Open composer.json and add Maatwebsite/excel package as shown below

   "require": {
       ....
       "maatwebsite/excel": "~2.1.3"
   },

   run composer update to install required packages in your laravel application.

   composer update

   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
4. 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'];
   }

5. 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.

Download Demo Excel File