Laravel 10 Import Export Excel & CSV File Tutorial

This tutorial helps you understand how to comfortably import export excel or CSV file to Database with Laravel.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 10 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

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

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

cd laravel-excel-csv

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=

If you are using MAMP local server in macOs; make sure to append UNIX_SOCKET and DB_SOCKET below database credentials in .env file.

UNIX_SOCKET=/Applications/MAMP/tmp/mysql/mysql.sock
DB_SOCKET=/Applications/MAMP/tmp/mysql/mysql.sock

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

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,

], 

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, 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

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 some data inside the 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');

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

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

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 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

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

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>

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

Here is the endpoint that you can finally test:

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

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.