Laravel 7 | How to import data in excel to databases | Inert Edit Delete

Step 1: Install Laravel 7 Project

Run this command to create a new Laravel project:

composer create-project --prefer-dist laravel/laravel blog

Step 2: Install Maatwebsite Excel Package

Install the Maatwebsite Excel package using Composer:

composer require maatwebsite/excel

Next, open the config/app.php file and add the service provider and alias:

'providers' => [
    Maatwebsite\Excel\ExcelServiceProvider::class,
],

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

Publish the configuration file:

php artisan vendor:publish

This creates the configuration file config/excel.php.

Step 3: Define Routes

In routes/web.php, add routes for importing, inserting, updating, and deleting records:

Route::get('/import_excel', 'ImportExcelController@index')->name('import_excel');
Route::post('/import_excel/import', 'ImportExcelController@import');
Route::get('import_excel/{importID}', 'ImportExcelController@importDelete')->name('importDelete');
Route::post('importInsert', 'ImportExcelController@importInsert')->name('importInsert');
Route::post('importUpdate', 'ImportExcelController@importUpdate')->name('importUpdate');

Step 4: Migration for Import Data

Create a migration for the import_excels table:

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

class CreateImportExcelModelsTable extends Migration
{
    public function up()
    {
        Schema::create('import_excels', function (Blueprint $table) {
            $table->id();
            $table->string('No')->nullable();
            $table->string('Name')->nullable();
            $table->string('Sex')->nullable();
            $table->string('Age')->nullable();
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('import_excels');
    }
}

Run the migration:

php artisan migrate

Step 5: Create the Blade View for Excel Import (import_excel.blade.php)

<!-- Add required Bootstrap and jQuery scripts -->
<div class="container">
    <!-- Your form for file upload -->
    <form method="post" enctype="multipart/form-data" action="{{ url('/import_excel/import') }}">
        {{ csrf_field() }}
        <input type="file" name="select_file" />
        <button type="submit" name="upload">Upload</button>
    </form>
</div>

Step 6: Create the Excel Import Class

Generate an export class:

php artisan make:export ExcelImport --model=User

In app/Imports/ExcelImport.php:

namespace App\Imports;

use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use DB;

class ExcelImport implements ToCollection
{
    public function collection(Collection $collection)
    {
        DB::table('import_excels')->truncate();
        foreach ($collection as $key => $value) {
            if ($key > 0) {
                DB::table('import_excels')->insert([
                    'No'   => $value[0],
                    'Name' => $value[1],
                    'Sex'  => $value[2],
                    'Age'  => $value[3],
                ]);
            }
        }
    }
}

Step 7: Create the Controller (ImportExcelController.php)

In app/Http/Controllers/ImportExcelController.php:

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;
use App\Imports\ExcelImport;
use Maatwebsite\Excel\Facades\Excel;
use Session;
use Auth;

class ImportExcelController extends Controller
{
    public function index()
    {
        $data = DB::table('import_excels')->orderBy('id', 'DESC')->get();
        return view('import_excel', compact('data'));
    }

    public function import(Request $request)
    {
        $request->validate([
            'select_file' => 'required|mimes:xls,xlsx'
        ]);
        $file = $request->select_file;
        Excel::import(new ExcelImport, $file);

        return redirect()->back()->with('success', 'Import successful!');
    }

    public function importInsert(Request $request)
    {
        $request->validate([
            'No' => 'required',
            'Name' => 'required',
            'Sex' => 'required',
            'Age' => 'required'
        ]);

        $data = DB::table("import_excels")->where('No', $request->No)->count();
        if ($data > 0) {
            return redirect()->back()->with('codesExists', "Code already exists.");
        } else {
            DB::table('import_excels')->insert($request->only(['No', 'Name', 'Sex', 'Age']));
            return redirect()->back()->with('importInsert', 'Insert successful!');
        }
    }

    public function importUpdate(Request $request)
    {
        DB::table('import_excels')->where('id', $request->idUpdate)->update($request->only(['No', 'Name', 'Sex', 'Age']));
        return redirect()->back()->with('importUpdate', 'Update successful!');
    }

    public function importDelete($importID)
    {
        DB::table('import_excels')->where('id', $importID)->delete();
        return redirect()->back()->with('importDelete', 'Delete successful!');
    }
}

Step 8: Finalizing the Frontend

Add modals for adding and updating records. Also, implement a table to display the imported data, with options for editing and deleting records.