Laravel 7/6 Import Export Excel & Csv from Database

10-Apr-2023

.

Admin

Laravel 7/6 Import Export Excel & Csv from Database

Hi Guys,

In this tutorial, we will learn how to import export excel & csv file in laravel 7/6. i written simple tutorial of laravel 7/6 maatwebsite/excel.It has made very simple and easy to use with laravel application for import-export data to the database.

In this tutorial we will use maatwebsite/excel composer package for import and export task. maatwebsite/excel provide easy way to import and export using database model.

We will simple create import data to csv, xls file and also we can import data to database using csv file in laravel 6 application.maatwebsite/excel updated version 3 and they provide great way to import export data from database, so first follow few step to get example.

Step 1: Install Laravel Project


First, you need to download the laravel fresh setup. Use this command then download laravel project setup :

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

Step 2: Setup Database

After successfully install laravel 6 Application, Go to your project .env file and set up database credential and move next step :

DB_CONNECTION=mysql

DB_HOST=127.0.0.1

DB_PORT=3306

DB_DATABASE=here your database name

DB_USERNAME=here database username

DB_PASSWORD=here database password

Step 3: Install maatwebsite/excel Package

In this step,you can install maatwebsite/excel package in manager.then define command in your terminal:

composer require maatwebsite/excel

Now open config/app.php file and add service provider and aliase.

config/app.php

'providers' => [

....

Maatwebsite\Excel\ExcelServiceProvider::class,

],

'aliases' => [

....

'Excel' => Maatwebsite\Excel\Facades\Excel::class,

],

Step 4: Create Dummy Records

we have to require "users" table with some dummy records, so we can simply import and export.bellow the command.

php artisan migrate

After that we need to run following command to generate dummy users:

php artisan tinker

factory(App\User::class, 20)->create();

Step 5: Make Route

you can create to route in web.php file.

routes/web.php

//ImportExportController

Route::get('importExportView', 'ImportExportController@importExportView');

Route::get('export', 'ImportExportController@export')->name('export');

Route::post('import', 'ImportExportController@import')->name('import');

Step 6: Create Import Class

In maatwebsite 3 version provide way to built import class and we have to use in controller. So it would be great way to create new Import class.bellow this command in your terminal.

php artisan make:import UsersImport --model=User

app/Imports/UsersImport.php

<?php

namespace App\Imports;

use App\User;

use Maatwebsite\Excel\Concerns\ToModel;

use Maatwebsite\Excel\Concerns\WithHeadingRow;

class UsersImport implements ToModel, WithHeadingRow

{

/**

* @param array $row

*

* @return \Illuminate\Database\Eloquent\Model|null

*/

public function model(array $row)

{

return new User([

'name' => $row['name'],

'email' => $row['email'],

'password' => \Hash::make($row['password']),

]);

}

}

Step 7: Create Export Class

maatwebsite 3 version provide way to built export class and we have to use in controller. So it would be great way to create new Export class.bellow this command in your terminal.

php artisan make:export UsersExport --model=User

app/Exports/UsersExport.php

<?php

namespace App\Exports;

use App\User;

use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection

{

/**

* @return \Illuminate\Support\Collection

*/

public function collection()

{

return User::all();

}

}

Step 8: Create Controller

you need to create a controller name ImportExportController.this command use to your terminal.

php artisan make:controller ImportExportController

this controller will manage all importExportView, export and import request and return response, so put bellow content in controller file:

app/Http/Controllers/ImportExportController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Exports\UsersExport;

use App\Imports\UsersImport;

use Maatwebsite\Excel\Facades\Excel;

class MyController extends Controller

{

/**

* @return \Illuminate\Support\Collection

*/

public function importExportView()

{

return view('import');

}

/**

* @return \Illuminate\Support\Collection

*/

public function export()

{

return Excel::download(new UsersExport, 'users.xlsx');

}

/**

* @return \Illuminate\Support\Collection

*/

public function import()

{

Excel::import(new UsersImport,request()->file('file'));

return back();

}

}

Step 9: Create View File

In this step you need to create blade view file.

resources/views/import.blade.php

<!DOCTYPE html>

<html>

<head>

<title>Laravel 6 Import Export Excel to database Example - nicesnippets.com</title>

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />

</head>

<body>

<div class="container">

<div class="card bg-light mt-3">

<div class="card-header">

Laravel 6 Import Export Excel to database Example - nicesnippets.com

</div>

<div class="card-body">

<form action="{{ route('import') }}" method="POST" enctype="multipart/form-data">

@csrf

<input type="file" name="file" class="form-control">

<br>

<button class="btn btn-success">Import User Data</button>

<a class="btn btn-warning" href="{{ route('export') }}">Export User Data</a>

</form>

</div>

</div>

</div>

</body>

</html>

Step 10: Run Development Server

you can run to laravel project in your teminal.bellow command

php artisan serve

Now you can open bellow URL on your browser:

http://localhost:8000/importExportView

It will help you....

#Laravel 7

#Laravel

#Laravel 6