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

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.

 Import, Export data from an excel sheet to database
  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
  7. 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.