Laravel 7/6 Import Export Excel, Csv to Database Example

Laravel 7/6 Import Export Excel, Csv to Database Example

In this article, you will learn how to import export excel or CSV large data from the database in laravel Framework using maatwebsite version 3 package with example. There have been many new changes in version 3 of the new maatwebsite package. It has made very simple and easy to use with laravel application for import-export data to the database.

For some time we want to export or import thousands of records to our database.Maatwebsite offers many features for exporting & importing thousands of records in an excel sheet.

Laravel Import Export CSV, Excel Data Into Database

Follow the below steps, you can easily import and export CSV & Excel files in laravel projects:

  • Install Laravel Fresh Project
  • Set database Credentials In .env File
  • Install Maatwebsite Package
  • Add Fake Records In Database
  • Create Controller
  • Add Routes
  • Create Import Export Classes
  • Create Blade View
  • Start Development Server

1. Install Laravel Fresh Project

We need to install Laravel fresh application using below command, Open your command prompt and run the below command :

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

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

2. Set database Credentials In .env File

Next step, you have downloaded the fresh new setup of laravel in your system. So set the database credentials in your application. Let’s open your project .env file and set the database credentials here.

 DB_CONNECTION=mysql 
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=here your database name here
DB_USERNAME=here database username here
DB_PASSWORD=here database password here

3. Install Maatwebsite Package

We need to install Maatwebsite package, Open command prompt(CLI) and run the below command :

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,

],

After successfully Install & Configurtion Maatwesite Package, Use the below command and publish the package :

php artisan vendor:publish

This command will create a new config file named “config/excel.php”.

4. Add Fake Records

Now we need to add fake records in our datatabase, so we can simply import and export. First of run the migration command it will create some basics table in our database.

php artisan migrate

After that we need to run below command to add fake records in users table :

If you found any query builder error in command prompt go to  => app\Providers\AppServiceProvider.php and put the below code here :

 use Illuminate\Support\Facades\Schema;
 
public function boot()
{
    Schema::defaultStringLength(191);
}
php artisan tinker 

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

5. Create Controller

We need to create a new controller. The controller manages all import-export methods, open your terminal and run the below command.

php artisan make:controller TestController

This command will create our TestController, Go to app/Http/Controllers/TestController.php and put the below code here :

<?php
   
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Exports\ExportUsers;
use App\Imports\ImportUsers;
use Maatwebsite\Excel\Facades\Excel;
  
class TestController extends Controller
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function importExport()
    {
       return view('import');
    }
   
    /**
    * @return \Illuminate\Support\Collection
    */
    public function export() 
    {
        return Excel::download(new ExportUsers, 'users.xlsx');
    }
   
    /**
    * @return \Illuminate\Support\Collection
    */
    public function import() 
    {
        Excel::import(new ImportUsers, request()->file('file'));
           
        return back();
    }
}

6. Add Routes

Here, we will create routes for import-export data to our database. Open your routes/web.php and create the below routes :

Route::get('import-export', 'TestController@importExport');
Route::post('import', 'TestController@import');
Route::get('export', 'TestController@export');

7. Create Import Exports Classes

Now we need to create an import class and use this class to our TestController. Open the Command Prompt and run the below command :

php artisan make:import ImportUsers --model=User

Go to the app/Imports/ImportUsers.php

<?php
  
namespace App\Imports;
  
use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
  
class ImportUsers implements ToModel
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        return new User([
            'name'     => $row[0],
            'email'    => $row[1], 
        ]);
    }
}

Now we need to create an import class and use this class to our TestController. Open the Command Prompt and run the below command :

php artisan make:export ExportUsers --model=User

app/Exports/ExportUsers.php

<?php
  
namespace App\Exports;
  
use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;
  
class ExportUsers implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::get();
    }
}

8. Create Blade File

Next we will create import.blade.php. Go to app/resource/views and create import.blade.php, put the below code here :

<!DOCTYPE html>
<html>
<head>
    <title>Laravel 6 Import Export Excel to database - Tuts Make</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 mt-4">
        <div class="card-header">
            Laravel 6 Import Export Excel to database - Tuts Make
        </div>
        <div class="card-body">
            <form action="{{ url('import') }}" method="POST" name="importform" 
               enctype="multipart/form-data">
                {{ csrf_token() }}
                <input type="file" name="file" class="form-control">
                <br>
                <a class="btn btn-info" href="{{ url('export') }}"> 
                 Export File</a>
                <button class="btn btn-success">Import File</button>
            </form>
        </div>
    </div>
</div>
   
</body>
</html>

9. Run Development Server

In this step, we will use the php artisan serve command . It will start your server locally

php artisan serve

If you want to run the project diffrent port so use this below command

php artisan serve --port=8080

Now we are ready to run our example so run bellow command to quick run.

http://localhost:8000/import-export

Conclusion

In this article, We have successfully use the import-export excel data to the database using the maatwebsite package. our examples run quickly.

If you have any questions or thoughts to share, use the comment form below to reach us.

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

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