Laravel Import Export Data as Excel or CSV File Example

Laravel Import Export Data in Excel or CSV file Example; In this tutorial we are going to share how to import or export data as excel or csv file with example. This article explain a better example to understand how to comfortably import export excel or CSV file to Database with Laravel application.

Here is the step-by-step guideline of Laravel 9 export data as excel file using maatwebsite/excel package for exporting data. Using the maatwebsite pacakge we can download or import excel & CSV file directly from the database in laravel application, respectively. You can use export data as excel in laravel 5, laravel 6, laravel 7, laravel 8 or laravel 9 version.

Laravel 9 Export Data as Excel File with Example

Here is the few steps to follow for export or import data as excel or csv file in laravel application.

• Step 1: Install Laravel Application
• Step 2: Connect Database to App
• Step 3: Install maatwebsite/excel package
• Step 4: Generate Fake Records
• Step 5: Create Routes
• Step 6: Make Import Class
• Step 7: Create Export Class
• Step 8: Create Controller
• Step 9: Create Blade View

Install Laravel Application

First of all, download or install laravel 9 new setup. So, open the terminal and type the following command to install the new laravel 9 app into your machine:

composer create-project laravel/laravel laravel-excel-csv

Execute the command to enter the project directory.

cd laravel-excel-csv

Setup Database Connection

Setup database with your downloaded/installed laravel app. So, you need to find .env file and setup database details as following:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel
DB_USERNAME=root
DB_PASSWORD=

Install maatwebsite/excel package

To install maatwebsite/excel, run the below-mentioned command

composer require maatwebsite/excel.

With the help of this package, we can export data into an excel file.

Now open config/app.php and add service provider and alias.

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

Execute the vendor, publish command, and publish the config.

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

This will formulate a new config file as config/excel.php.

Generate Fake Records

Often, this step consists of two sub-steps. In the first one, we migrate the User table. Laravel comes with the User model and migration with default values, and we can use it and migrate to the database.

php artisan migrate

Once the migration is completed, then execute the command to generate the fake records.

php artisan tinker
User::factory()->count(50)->create();
exit

Eventually, the above command has created the dummy data in our database.

Define routes

Define routes in routes/web.php that handle the import and export for Excel and CSV files.

<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\UserController;
/*
|--------------------------------------------------------------------------
| 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('file-import-export', [UserController::class, 'fileImportExport']);
Route::post('file-import', [UserController::class, 'fileImport'])->name('file-import');
Route::get('file-export', [UserController::class, 'fileExport'])->name('file-export');

Create an Export Class

In this section, we will create an export class and define the model to which it is connected. The maatwebsite package offers a way for building an export class so that we can further use it in the controller.

Run the below command for the same.

php artisan make:import UsersImport --model=User

Place the following code inside the app/Imports/UsersImport.php file.

<?php
namespace App\Imports;
use App\Models\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
class UsersImport 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],
            'password' => Hash::make($row[2])
        ]);
    }
}

Construct Export Class

The maatwebsite module provides an essential method to construct an export class. Preferably, it needs to get along with the laravel controller, and i know it doesn’t sound vague.

Run the following command in your terminal:

php artisan make:export UsersExport --model=User

BashCopy

Here is the final code that is conjugated in app/Exports/UsersExport.php.

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

Create Controller

Invoke the command to generate UserController.

php artisan make:controller UserController

Place the following code in the app/Http/Controllers/UserController.php file.

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use App\Imports\UsersImport;
use App\Exports\UsersExport;
class UserController extends Controller
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function fileImportExport()
    {
       return view('file-import');
    }
   
    /**
    * @return \Illuminate\Support\Collection
    */
    public function fileImport(Request $request) 
    {
        Excel::import(new UsersImport, $request->file('file')->store('temp'));
        return back();
    }
    /**
    * @return \Illuminate\Support\Collection
    */
    public function fileExport() 
    {
        return Excel::download(new UsersExport, 'users-collection.xlsx');
    }    
}

Create Blade Files

Create a resources/views/file-import.blade.php file to set up the view. Place the following code inside the blade view file:

<!DOCTYPE html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Import Export Excel & CSV to Database in Laravel 7</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
</head>
<body>
    <div class="container mt-5 text-center">
        <h2 class="mb-4">
            Laravel 7 Import and Export CSV & Excel to Database Example
        </h2>
        <form action="{{ route('file-import') }}" method="POST" enctype="multipart/form-data">
            @csrf
            <div class="form-group mb-4" style="max-width: 500px; margin: 0 auto;">
                <div class="custom-file text-left">
                    <input type="file" name="file" class="custom-file-input" id="customFile">
                    <label class="custom-file-label" for="customFile">Choose file</label>
                </div>
            </div>
            <button class="btn btn-primary">Import data</button>
            <a class="btn btn-success" href="{{ route('file-export') }}">Export data</a>
        </form>
    </div>
</body>
</html>

Run the Dev Server

The last section of the tutorial- laravel 9 export data as excel file is to run the app. Now it’s time to run our demo. Run the below command.

php artisan serve

Here is the endpoint that you can finally test:

http://localhost:8000/file-import-export

So, I hope the tutorial of laravel 9 export data as excel file was helpful to you.