How to Make Excel Data Import Feature in Laravel 8

Admin・ 6 Oktober 2021

10 min read ・ 3649 views

Laravel Excel Import - In an administrative system, the presence of an excel import feature is needed. This is because to facilitate the work of an admin or worker in inputting reports or data in a system. To create an excel import feature in a system is very easy. There are many available packages that can be used. Especially if the system is made with the laravel framework, we can use the laravel excel package.

In this article, we will discuss how to make the import excel feature in laravel with the laravel excel package. In this experiment, we will start from scratch or start by installing the latest laravel (laravel version 8).

Alright, here are the steps on how to make the import excel feature in laravel 8 with the laravel excel package:

Step 1: Install Laravel

composer create-project laravel/laravel importexcel

The first step is to install or create a new laravel project in the folder we want. Install laravel using composer with command as above in terminal. After the process is complete, we will get a new folder or laravel project folder in the directory or folder we want.

Step 2: Install laravel excel package

composer require maatwebsite/excel

Second, we have to install the package that we will use to create the Excel import feature in Laravel. Run the command as above to install it.

Step 3: Create New Database

We need a database to hold the table that contains the imported data. Therefore, please create a new database first in xampp, laragon, or others.

*Don't forget to match DB_DATABASE in the .env file with the name of the new database that has been created.

Step 4: Migrate

php artisan migrate

We need a table to accommodate the data that will be imported later, therefore we need to migrate the files in the migration folder to the newly created database. Run the php artisan migrate command to migrate the tables.

Step 5: Create Import Class

php artisan make:import UsersImport --model=User

To be able to use the package from laravel excel, we need an import class. For that, please run the command above in the terminal. Running the above command will create a UsersImport.php file that is linked to the User.php model.

Then next we need to edit a bit in the UsersImport.php file. Open the UsersImport.php file which is located in the App\Imports folder and according to the code it becomes as below.

<?php
namespace App\Imports;
use App\Models\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
class UsersImport implements ToModel
{
    /**
     * @param array $row
     *
     * @return User|null
     */
    public function model(array $row)
    {
        return new User([
           'name'     => $row[0],
           'email'    => $row[1], 
           'password' => Hash::make($row[2]),
        ]);
    }
}

In this experiment, we create an Excel import feature in Laravel for user data management. Therefore, if you look at the arrangement of the code above, there is App\Models\User and the return points to the new user. The data to be imported include name, email and password.

Step 6: Setup Route

In order for the import excel feature to work perfectly, we need a route like the one below.

<?php
use Illuminate\Support\Facades\Route;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
use App\Models\User;
/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/
Route::get('/', function () {
    $user = User::all();
    return view('welcome',['user'=>$user]);
});
Route::post('/', function () {
    Excel::import(new UsersImport, request()->file('file'));
    return back();
});

If we look at the code above, we will use Laravel's default homepage route to display user data in a table and add an upload form on the same page.

And since we're calling import on the route instead of the controller, we need to add code like the one below that's included in the overall code sample in the web.php file above.

use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
use App\Models\User;

Step 7: Edit welcome.blade.php

Because we are using Laravel's default homepage to display user data and upload form, therefore we need to edit the welcome.blade.php file. Replace all the code in the welcome.blade.php file to be as below.

<!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/css2?family=Nunito:wght@400;600;700&display=swap" rel="stylesheet">
        <!-- Styles -->
        <style>
            /*! normalize.css v8.0.1 | MIT License | github.com/necolas/normalize.css */html{line-height:1.15;-webkit-text-size-adjust:100%}body{margin:0}a{background-color:transparent}[hidden]{display:none}html{font-family:system-ui,-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Helvetica Neue,Arial,Noto Sans,sans-serif,Apple Color Emoji,Segoe UI Emoji,Segoe UI Symbol,Noto Color Emoji;line-height:1.5}*,:after,:before{box-sizing:border-box;border:0 solid #e2e8f0}a{color:inherit;text-decoration:inherit}svg,video{display:block;vertical-align:middle}video{max-width:100%;height:auto}.bg-white{--bg-opacity:1;background-color:#fff;background-color:rgba(255,255,255,var(--bg-opacity))}.bg-gray-100{--bg-opacity:1;background-color:#f7fafc;background-color:rgba(247,250,252,var(--bg-opacity))}.border-gray-200{--border-opacity:1;border-color:#edf2f7;border-color:rgba(237,242,247,var(--border-opacity))}.border-t{border-top-width:1px}.flex{display:flex}.grid{display:grid}.hidden{display:none}.items-center{align-items:center}.justify-center{justify-content:center}.font-semibold{font-weight:600}.h-5{height:1.25rem}.h-8{height:2rem}.h-16{height:4rem}.text-sm{font-size:.875rem}.text-lg{font-size:1.125rem}.leading-7{line-height:1.75rem}.mx-auto{margin-left:auto;margin-right:auto}.ml-1{margin-left:.25rem}.mt-2{margin-top:.5rem}.mr-2{margin-right:.5rem}.ml-2{margin-left:.5rem}.mt-4{margin-top:1rem}.ml-4{margin-left:1rem}.mt-8{margin-top:2rem}.ml-12{margin-left:3rem}.-mt-px{margin-top:-1px}.max-w-6xl{max-width:72rem}.min-h-screen{min-height:100vh}.overflow-hidden{overflow:hidden}.p-6{padding:1.5rem}.py-4{padding-top:1rem;padding-bottom:1rem}.px-6{padding-left:1.5rem;padding-right:1.5rem}.pt-8{padding-top:2rem}.fixed{position:fixed}.relative{position:relative}.top-0{top:0}.right-0{right:0}.shadow{box-shadow:0 1px 3px 0 rgba(0,0,0,.1),0 1px 2px 0 rgba(0,0,0,.06)}.text-center{text-align:center}.text-gray-200{--text-opacity:1;color:#edf2f7;color:rgba(237,242,247,var(--text-opacity))}.text-gray-300{--text-opacity:1;color:#e2e8f0;color:rgba(226,232,240,var(--text-opacity))}.text-gray-400{--text-opacity:1;color:#cbd5e0;color:rgba(203,213,224,var(--text-opacity))}.text-gray-500{--text-opacity:1;color:#a0aec0;color:rgba(160,174,192,var(--text-opacity))}.text-gray-600{--text-opacity:1;color:#718096;color:rgba(113,128,150,var(--text-opacity))}.text-gray-700{--text-opacity:1;color:#4a5568;color:rgba(74,85,104,var(--text-opacity))}.text-gray-900{--text-opacity:1;color:#1a202c;color:rgba(26,32,44,var(--text-opacity))}.underline{text-decoration:underline}.antialiased{-webkit-font-smoothing:antialiased;-moz-osx-font-smoothing:grayscale}.w-5{width:1.25rem}.w-8{width:2rem}.w-auto{width:auto}.grid-cols-1{grid-template-columns:repeat(1,minmax(0,1fr))}@media (min-width:640px){.sm\:rounded-lg{border-radius:.5rem}.sm\:block{display:block}.sm\:items-center{align-items:center}.sm\:justify-start{justify-content:flex-start}.sm\:justify-between{justify-content:space-between}.sm\:h-20{height:5rem}.sm\:ml-0{margin-left:0}.sm\:px-6{padding-left:1.5rem;padding-right:1.5rem}.sm\:pt-0{padding-top:0}.sm\:text-left{text-align:left}.sm\:text-right{text-align:right}}@media (min-width:768px){.md\:border-t-0{border-top-width:0}.md\:border-l{border-left-width:1px}.md\:grid-cols-2{grid-template-columns:repeat(2,minmax(0,1fr))}}@media (min-width:1024px){.lg\:px-8{padding-left:2rem;padding-right:2rem}}@media (prefers-color-scheme:dark){.dark\:bg-gray-800{--bg-opacity:1;background-color:#2d3748;background-color:rgba(45,55,72,var(--bg-opacity))}.dark\:bg-gray-900{--bg-opacity:1;background-color:#1a202c;background-color:rgba(26,32,44,var(--bg-opacity))}.dark\:border-gray-700{--border-opacity:1;border-color:#4a5568;border-color:rgba(74,85,104,var(--border-opacity))}.dark\:text-white{--text-opacity:1;color:#fff;color:rgba(255,255,255,var(--text-opacity))}.dark\:text-gray-400{--text-opacity:1;color:#cbd5e0;color:rgba(203,213,224,var(--text-opacity))}}
        </style>
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-BmbxuPwQa2lc/FVzBcNJ7UAyJxM6wuqIj61tLrc4wSX0szH/Ev+nYRRuWlolflfl" crossorigin="anonymous">
        <style>
            body {
            font-family: 'Nunito';
            }
        </style>
    </head>
    <body class="antialiased">
        <div class="relative flex items-top justify-center min-h-screen bg-gray-100 sm:items-center sm:pt-0">
            <div class="container">
                <div class="row">
                    <form action="/" method="post" enctype="multipart/form-data">
                        @csrf
                        <div class="input-group mb-3">
                            <input type="file" name="file" class="form-control" placeholder="Recipient's username" aria-label="Recipient's username" aria-describedby="button-addon2">
                            <button class="btn btn-primary" type="submit" id="button-addon2">Import</button>
                        </div>
                    </form>
                    <table class="table table-striped table-hover">
                        <thead>
                            <tr>
                                <th scope="col">#</th>
                                <th scope="col">Name</th>
                                <th scope="col">Email</th>
                                <th scope="col">Password</th>
                            </tr>
                        </thead>
                        <tbody>
                            @php
                            $no = 0;
                            @endphp
                            @forelse ($user as $user)
                            <tr>
                                <th scope="row">{{ ++$no }}</th>
                                <td>{{ $user->name }}</td>
                                <td>{{ $user->email }}</td>
                                <td>{{ $user->password }}</td>
                            </tr>
                            @empty
                            <td colspan="4" class="table-active text-center">Tidak Ada Data</td>
                            @endforelse
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </body>
</html>

Testing

After following the steps to create the Laravel Excel import feature above, now comes the testing step. First, prepare the excel file that will be imported later. This excel file contains user data according to what we are currently making, which is to create an excel import feature in Laravel for user data management. The excel format that must be created can be seen as shown below.

After the excel file is ready, now please run the project server with php artisan serve.

Here we do not have user data at all, therefore we can add it with the import feature. Click the form file, find the excel file that has been prepared and then click the import button.

Succeed. We have successfully added user data with the Excel import feature in Laravel using the Laravel Excel package.

Conclusion 

In this article we have tried how to make the import excel feature in laravel using the laravel excel package and at the end we have also tested by trying to create an excel file that contains user data including name, email and password then try to upload the excel file that has been created it in the import form. And the results of this experiment we have succeeded in importing excel in the laravel project. So this article has discussed how to easily make the import excel feature in Laravel 8 using the Laravel Excel package, which may only take about 5 minutes during trial practice. If you have criticism, suggestions, input or anything you want to discuss, please write them in the comment form below.