Import Excel file to a MYSQL database with Laravel

by Salitha Chathuranga August 14, 2018 1 Comments

Hello guys! After a long time..Isn't it? Today I came with another important article on Laravel. This is about handling Excel files with Laravel. We can upload some data records to a MYSQL database, which are stored in an excel file. Then we can download the excel file including information stored in the database. Are you feeling crazy? Don't worry!
Let me to start the work! I will explain step by step..

Step 1 - Create new laravel project

composer create-project laravel/laravel LaravelExcel

Step 2 - Migrate database

Open the project in a text editor and edit database configuration files.

.env file


config/database.php file


Before database migration, we have something to do to avoid migration errors... In your project folder, open Http/Providers/AppServiceProvider.php file and replace its code with this.

<?php

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Schema;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        Schema::defaultStringLength(191);
    }

    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }
}

Now open the database/migrations folder in the project. Then Delete the migration built for password resets. Edit the migration reserved for users. We don't need it. Replace this code.

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

Open a cmd and type this command to migrate database.
php composer require "maatwebsite/excel:~2.1.0" migrate

Now your database tables have been created successfully. Go to PhpMyadmin and see the database.
Select the DB table users and then select insert option. Insert some data into the table.

Step 3 - Install Laravel Excel Library into the project

Now it's time to install the Excel package. Open a terminal/cmd and go into the project folder. Type the below command and hit enter.
composer require "maatwebsite/excel:~2.1.0"

Then open config/app.php file to do further configurations.
Place this code in providers section
Maatwebsite\Excel\ExcelServiceProvider::class,

Place this code in aliases section
'Excel' => Maatwebsite\Excel\Facades\Excel::class,

Step 4 - Create excel controller

Open the cmd and type the command to create a controller called UserController.
php artisan make:controller ImportController

Add the index function to load the with users included in the database.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\User;
use PDF;

class UserController extends Controller
{
    public function index()
    {
     $users = User::all();
     return view('welcome', ['users' => $users]);
    }
}

Step 5 - Add Routes

Open routes/web.php and add this code lines.

Route::get('/', 'Importcontroller@index');

Route::post('/importExcel', 'Importcontroller@importExcel');

Route::get('/downloadExcel/{type}', 'Importcontroller@downloadExcel');


Step 6 - Modify the view code

Edit the welcome blade file in resources/views to integrate the styles and design. I use Bootstrap 4 to add styles.

<html lang="en">
<head>
    <title>Laravel 5.6 Import Export to Excel and csv Example - ItSolutionStuff.com</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
<div class="container" style="margin-top: 50px;">
    <h1>Import Excel file to database using Laravel 5.6 </h1>
    <hr>
    <form action="{{ url('importExcel') }}" class="form-horizontal" method="post" enctype="multipart/form-data">
        @csrf

        @if ($errors->any())
            <div class="alert alert-danger alert-dismissible fade show" role="alert">
                <ul>
                    @foreach ($errors->all() as $error)
                        {{ $error }}
                    @endforeach
                </ul>
                <button type="button" class="close" data-dismiss="alert" aria-label="Close">
                    <span aria-hidden="true">×</span>
                </button>
            </div>
        @endif

        @if (Session::has('success'))
            <div class="alert alert-success alert-dismissible fade show" role="alert">
                {{ Session::get('success') }}
                <button type="button" class="close" data-dismiss="alert" aria-label="Close">
                    <span aria-hidden="true">×</span>
                </button>
            </div>
        @endif

        <input type="file" name="import_file" class="form-control"><br>
        <button class="btn btn-primary">Import File</button>
        <br><br>
    </form>
    <hr>
    <br><h1>Export data from database into an Excel file using Laravel 5.6 </h1><br>
    <a href="{{ url('downloadExcel/xls') }}"><button class="btn btn-success">Download Excel xls</button></a>
    <a href="{{ url('downloadExcel/xlsx') }}"><button class="btn btn-success">Download Excel xlsx</button></a>
    <a href="{{ url('downloadExcel/csv') }}"><button class="btn btn-success">Download CSV</button></a>
</div>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.js"></script>
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js" integrity="sha384-ZMP7rVo3mIykV+2+9J3UJ46jBk0WLaUAdn689aCwoqbBJiSnjAK/l8WvCWPIPm49" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
</body>
</html>

Open cmd or terminal and type php artisan serve. Then open web browser and type localhost:8000 to run the project. You will get this output in your browser.


Here I have implemented the UI for downloading the Excel sheet also. So as the next step I will modify the controller to satisfy both these upload and download tasks.

Step 7 - Modify the controller 


<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\User;
use PDF;

class UserController extends Controller
{
    public function import() {
        $users = User::all();
        return view('index', ['users' => $users]);
    }

    public function importExcel(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[] = ['name' => $value->name, 'email' => $value->email];
            }

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

        return back()->with('success', 'Records are saved to database.');
    }

    public function downloadExcel($type)
    {
        $data = User::get()->toArray();

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

Our work is completed!

I have created the downloadExcel function to download the excel sheet (extensions with .xls/.xlsx) or csv formatted file incluing the data stored in the database.
Now try to prepare an excel sheet to include some data records for users table.

NOTE:
You must prepare it with the same name fields in the database. For each column, provide the same name you provided to the database table!

Try this and give feedback. I'm looking forward for your comments...
.
Good Luck guys! Happy coding with Laravel!




1 Comments

  1. AnonymousJuly 26, 2023 at 6:20 PM

    Nice blog post. Thank you for sharing this with us. I have also a blog about Convert CSV to MySQL

    ReplyDelete