Laravel 10 import export to/from excel.

In this article, I will explain how to import and export to/from excel in laravel 10.

Sometimes you need to export some amount of data to excel for some analysis or other purpose or you may want to import a custom list from excel into your laravel 10 projects. 

Fortunately, laravel and its packages made it easy for laravel developers to overcome this purpose. For this article, I am going to use the maatwebsite/excel package. I will explain that, how to install and work with this package. You should follow the steps I mentioned below carefully.

Step1: Install Laravel

In this article, we will have our fresh laravel 10 project to work with laravel 10 import export to / from excel. So, let’s install our new project.

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

Step 2: install maatwebsite/excel package

At this step, we will install the maatwebsite/excel package using composer for our project. To install this package open your terminal into your project root directory and use the bellow command to install

composer require maatwebsite/excel

Step 3: Configure your database configuration

Firstly create a database by the name of excel_laravel or whatever name you want.

Then configure your laravel database configurations as you can see below.

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

Step 4: Create Some Dummy Records

To work with excel we need to have some records, here we will use some dummy records that laravel 10 will generate using the below command.

php artisan tinker
User::factory()->count(15)->create()

The above command will create 15 random and dummy records for our users table with the help of the laravel factory facade.

Step 5: Create a maatwebsite/excel import class.

In the latest version of the maatwebsite/excel package which is 3 currently, we are able to create import or export classes to use in the laravel controller. One good thing about that is, that can customize our excel cells before importing or exporting in the laravel 10 controller. So let’s see how to create these classes.

php artisan make:import UsersImport--model=User

The above command will create a php file in the “app/Imports” directory, go to this directory and open the “UsersImport.php” file and paste the following in it.

app/Imports/UserImports.php

<?php

namespace App\Imports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Hash;

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 6: Create a maatwebsite/excel export class.

In order to create export class, write below command to create a maatwebsite export class

php artisan make:export UsersExport --model=User

The above command will create a php file in the “app/Exports” directory by the name UsersExport.php. open the file and paste the following command in it.

<?php

namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class UsersExport implements FromCollection, WithHeadings
{
    /**
     * @return \Illuminate\Support\Collection
     */
    public function collection()
    {
        return User::select("id", "name", "email")->get();
    }

    /**
     * Write code on Method
     *
     * @return response()
     */
    public function headings(): array
    {
        return ["ID", "Name", "Email"];
    }
}

Step 7: Creating Controller

At this step, we need to create a controller by the name of UsersController with 3 methods including index(), import(), and export(). To create a controller we need to run the following artisan command.

php artisan make:controller UsersController

This command will create a controller by the name of UsersController with index() method in “app/Http/Controllers” directory. Open the UsersController and paste the following command into it.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Exports\UsersExport;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
use App\Models\User;

class UserController extends Controller
{
    /**
     * @return \Illuminate\Support\Collection
     */
    public function index()
    {
        $users = User::get();

        return view('users', compact('users'));
    }

    /**
     * @return \Illuminate\Support\Collection
     */
    public function export()
    {
        return Excel::download(new UsersExport, 'users_list.xlsx');
    }

    /**
     * @return \Illuminate\Support\Collection
     */
    public function import()
    {
        $users =  Excel::import(new UsersImport, request()->file('file'));
        echo $users;
        die;
        return back();
    }
}

Step 8: Create Routes

We need to have 3 routes, which are index, users-export, users-import. Index route for listing our recores and displaying a from which we can select our excel file to import.

Go to your project directory and open web.php file and paste the below codes into it.

<?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::controller(UserController::class)->group(function () {
    Route::get('users', 'index');
    Route::get('users-export', 'export')->name('users.export');
    Route::post('users-import', 'import')->name('users.import');
});

Step 9: Create Views

At this step, go to the “resources/views” directory. And create a file by the name of users.blade.php and paste the below codes into it.

<!DOCTYPE html>
<html>

<head>
 <title>Laravel 10 Import Export Excel to Database Example - ItSolutionStuff.com</title>
 <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet">
</head>

<body>

 <div class="container">
  <div class="card bg-light mt-3">
   <div class="card-header">
   Users List
   </div>
   <div class="card-body">
   

    <table class="table table-bordered mt-3">
     <tr>
      <th colspan="3">
       List Of Users

 <form class="float-end" action="{{ route('users.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>
    </form>
       <a class="btn btn-primary float-end" href="{{ route('users.export') }}">Export User Data</a>

      </th>
     </tr>
     <tr>
      <th>ID</th>
      <th>Name</th>
      <th>Email</th>
     </tr>
     @foreach($users as $user)
     <tr>
      <td>{{ $user->id }}</td>
      <td>{{ $user->name }}</td>
      <td>{{ $user->email }}</td>
     </tr>
     @endforeach
    </table>

   </div>
  </div>
 </div>

</body>

</html>

Now start your server on localhost or anywhere else and type the below address in the browser URL.

http://localhost:8000/users

you may want to read laravel 10 crud example