In today’s post we will be discussing how we can import excel spreadsheet on database from Laravel. We will use Laravel Excel package by Maatwebsite for importing the excel file. You can use this case for Laravel 5.5+ version. When we have small amount of data to upload we can do it one by one. But when there is thousands of data to upload, it will take too much time. So in that case we can use excel file to upload.

Maatwebsite Requirements

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

Install Laravel Project

At first we need to create the project. So, open the windows terminal and redirect to the destination folder where you want to install the project. Run the following command to install.

laravel new laravelexcel

Now we have to set a application key of a random string. If you installed Laravel via Composer or the Laravel installer, this key has already been set for you. If the application key is not set, your user sessions and other encrypted data will not be secure! You can set the application key by running following command.

php artisan key:generate

SET UP DATABASE

On browser open localhost\phpmyadmin. Create a new database named laravelexcel.

CREATE DATABASE laravelexcel;

Open the file app\Providers\AppServiceProvider.php. Find the boot function and add the following code.

use Illuminate\Support\Facades\Schema; 

public function boot(){
    Schema::defaultStringLength(200);
}

Now go to .env file. Change the database credentials.

DB_DATABASE=laravelexcel
DB_USERNAME=root
DB_PASSWORD=null

Next, migrate two tables provided by Laravel Move to your cmd and run the following command.

php artisan migrate

It will place two tables in your database.

  1. users
  2. password_resets

Install Maatwebsite Package

To install the maatwebsite package, just run the following command.

composer require maatwebsite/excel

Open the config/app.php file and add the following the providers array.

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

Open the config/app.php file and add the following to the aliases array.

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

Now, publish the configuration files:

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

This will create a new config file named excel.php on config directory.

Import Class

Create an Import class inside app folder by using following artisan command

php artisan make:import UsersImport --model=User

Go to app\Imports\UserImport.php and add the following code on model function

use Illuminate\Support\Facades\Hash;

    
   public function model(array $row)
    {
        return new User([
            'name'     => $row[0],
            'email'    => $row[1],
            'password' => Hash::make($row[2]),
        ]);
    }

CREATE CONTROLLER & ROUTE

php artisan make:controller UserController

It will create one controller file called UserController.php.

We will register route in routes/web.php file. So let us do it.

Route::get('excel', 'UserController@index');
Route::post('importexcel', 'UserController@importexcel')->name('importexcel');

We will go to app/Http/Controllers/UserController.php

use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Controllers\Controller;

class UsersController extends Controller 
{

    public function index(){
        return view('users.index');
    }
    public function importexcel() 
    {
        Excel::import(new UsersImport(), request()->file('import_file'));
        
       return back()->with('success', 'Insert Record Successfuly');
    }
}

Create a View File

Create a directory users in the resources/views and inside the users directory create a index.blade.php file.

<!doctype html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <title>Laravel</title>

    <!-- Fonts -->
    <link href="https://fonts.googleapis.com/css?family=Nunito:200,600" rel="stylesheet" type="text/css">

</head>
<body>
<div class="flex-center position-ref full-height">
    
    <br>
    <h2>Upload Excel file</h2>
    <div class="content">

        <form style="border: 4px solid #a1a1a1; margin-top: 15px; padding:10px;" action="{{ url('importexcel') }}" class="form-horizontal" method="post" enctype="multipart/form-data" id="dataupload">
            @csrf
            <input type="file" name="import_file" id="importfile">
            <button class="btn btn-primary">Import File</button>
        </form>

    </div>
</div>
</body>
</html>

When you run your app on browser, it looks like

Upload excel in laravel

Now you can upload excel from laravel to database.

Comments

  1. great submit, very informative. I ponder why the opposite specialists of this sector don’t notice this. You should proceed your writing. I am confident, you’ve a huge readers’ base already!

Leave a Reply

Your email address will not be published. Required fields are marked *