How to Import and Export to Excel in Laravel

This tutorial helps you understand how to comfortably import export excel or CSV file to Database with Laravel 8.

If you want to create easy import and export, excel file functionality, this laravel maatwebsite/excel tutorial is best for you.

At the end of this tutorial, you will be able to download or import excel & CSV file directly from the database in laravel application, respectively.

Generically, we will follow all the imperative that are needed to build a general laravel application. We will go from point a to b, something like creating or importing data to xls or CSV.

Laravel 8 Import Export Excel & CSV File to Database Example

Preferably, we will use a maatwebsite/excel composer plugin for exporting and importing data, most importantly, to interact with the database.

I will share the working pattern with you. Ideally, how should you import-export and download the excel & CSV file from the database using the maatwebsite/excel composer plugin. Follow the given below steps that will take you to the learning dimension.

Download Laravel Application
Compose Database Connection
Install Excel (maatwebsite) Pacakage
Generate Fake Records and Migrate
Construct Route
Make Import Class
Construct Export Class
Create and Prepare Controller
Write Down Blade View
Summary

Download Laravel Application

Let’s evoke this tutorial with the imperative step; installing a brand new laravel application offers us a key to opening the many opportunities. We can create the app from the beginning, build multiple features with it.

You can skip this step if you’ve already installed the app. Otherwise, put the command into effect to download the sacred canon.

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

BashCopy

Later on, after the project installation, execute the command to enter the project directory.

cd laravel-excel-csv

BashCopy

Compose Database Connection

Make the consensus between laravel app and MySQL database, append the following code in .env file.

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

.propertiesCopy

Install Excel (maatwebsite) Pacakage

Commonly, to complete our foundational work, we require a third-party package. Ideally, we are talking about the Laravel-Excel plugin by Maatwebsite. It provides the robust mechanism to deal with Excel exports and imports in Laravel. In response, It has got the immense love of artisan’s on GitHub.

Run command to install the package.

composer require maatwebsite/excel

BashCopy

Register Plugin’s Service in Providers & Aliases

You can have the following code placed inside the config/app.php file.

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

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

],

PHPCopy

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

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

BashCopy

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

Generate Fake Records, Migrate Table

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

BashCopy

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

php artisan tinker

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

BashCopy

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

Construct Route

Usually, routing in laravel is the foundational mechanism that interprets the URI endpoint and conjugates it into parameters to shape which module or controller is associated.

Define 3 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');

PHPCopy

Make Import Class

The maatwebsite module offers an imperative method to develop an import class. Obviously, it should be used along with the laravel controller, and i believe you already know this has been the best way to generate a new import class.

Execute the below command:

php artisan make:import UsersImport --model=User

BashCopy

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])
        ]);
    }
}

PHPCopy

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();
    }
}

PHPCopy

Create and Prepare Controller

Now, we have reached an essential step in this tutorial. We will evoke this step by creating a controller. Altogether all the logic goes in here to manage the import and export file such as Excel and CSV.

Invoke the command to generate UserController.

php artisan make:controller UserController

BashCopy

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');
    }    
}

PHPCopy

Write Down Blade View

Ultimately, we have reached the last step. In general, here we need to formulate the view for handling importing and exporting through the frontend.

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>

PHPCopy

We have followed every step, respectively, and consecutively, now its time to run the app to test what we build so far.

php artisan serve

BashCopy

Here is the endpoint that you can finally test:

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

MarkupCopy

Summary

So this was it, we have completed the tutorial. In this tutorial, we threw light on importing-exporting and downloading the Excel & CSV file from the database with the maatwebsite/excel composer package. You can also check the documentation of the plugin that we assimilated in this tutorial.

You can download the full code of this tutorial from GitHub. I hope you must have liked this tutorial, we covered the basic functionality but good for getting started

Source: https://www.positronx.io/laravel-import-expert-excel-and-csv-file-tutorial-with-example/

Laravel 8 Import Export Excel & CSV File to Database Example

Table of Contents

Download Laravel Application

Compose Database Connection

Install Excel (maatwebsite) Pacakage

Register Plugin’s Service in Providers & Aliases

Generate Fake Records, Migrate Table

Construct Route

Make Import Class

Construct Export Class

Create and Prepare Controller

Write Down Blade View

Summary