Import and Export CSV and Excel File in Laravel 8

Most of the time you work on any E-commerce application or any kind of ERP. In that case, sometimes, you require to import export excel and CSV files for the data. For the E-commerce application, you want to add bulk products at the same time. So, the manual entry of the products and it’s relevant data will take a long time to insert into the database. Similarly, on the other hand, if want to download any report like daily/weekly/monthly sales report, then you can export data to excel or CSV. Today, I will show you, how you can implement the functionality of import and export in Laravel. At the end of this post, you will be learning to import and export excel and CSV files in Laravel 8. So, let’s begin without wasting the time.

Prerequisites

I am assuming you are ready to create a Laravel 8 project with the required configurations.

• PHP >= 7.3
• MySQL (version > 5)
• Apache/Nginx Server
• VS Code Editor
• Composer

Hence, let’s create a new project by using composer.

Create Project For Import Export Excel and CSV

For creating the project, you can use command line or terminal. Now, just open it and create the new project by hitting the below command.

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

After hitting the command, it will install the Laravel 8 inside the export-csv folder.

After creating the import export project, you will have to create a database.

How to Generate PDF File in Laravel 8 Using DOM PDF

Create a Database in MySQL

For the database management, I am using MySQL here. So, I am creating a new database by hitting the below command.

CREATE DATABASE laravel_import_export;

Here, a new database has been created.

After creating the database, let’s open the project in your favourite editor. Now, change the database credentials available in the .env file.

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

Here, I have configured the database credentials for this project. You will have to change the credentials accordingly.

In the next step, you will have to install a package inside the project for import export excel. So, let’s do that.

RESTful APIs For Todo App Using Passport Auth in Laravel 8

Install Import Export Package in Laravel 8

If you will search the package for excel then you will have the package lists as showing below. Here, we will install the first one that is maatwebsite/excel.

composer require maatwebsite/excel

The above command will install the excel package in our project.

After installing the package, you will require to add the providers and alias of this package.

How to Implement Invisible reCAPTCHA in Laravel 8

Configure Provider and Alias of Excel Package

For configuration of this package, you will need to register its provider in the config/app.php file. Also, you will have to create an alias. So, just put the below configuration in providers array and aliases array.

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

In the next step, we will migrate the database. So, just hit the migrate command as showing below. Please keep in mind, we are not going to create a new migration file. We will work on default migration that is for the users.

php artisan migrate

The above command will migrate the tables into the database.

After migrating the tables, let’s put some dummy records there.

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

The above tinker command will create 50 dummy records for the user.

let’s move to the implementation of import export excel file in Laravel.

Create Authentication in Laravel 8 Using Laravel Breeze

Make Export Class in Laravel 8

For exporting the data to Excel or CSV, we required to have an export class. So, let’s creat it using the below command.

php artisan make:export UsersExport

The above command will create an export class inside the app/Exports directory.

After creating the class, let’s add the below snippet there.

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

    /**
     * Return Headings for the exported data
     *
     * @return array
     */
    public function headings(): array
    {
        return [
            'Id', 'Name', 'Email', 'Email Verified', 'Created At', 'Updated At'
        ];
    }
}

Let me explain the above code.

• In the collection() method, I have fetched the users data from the database. The users list will be exported here.
• The next function is for displaying the headings for each row in the Excel/CSV file. If you don’t want the headings in your downloaded excel file then no need to put these headers.

Make Import Class in Laravel

Here, we will import an excel file and after reading the rows data from excel, we will insert it into the table. But, firstly, we will have to create an Import class as showing below.

php artisan make:import UsersImport --model=Models/User

The above command will create an Import class. Here, I have specified the model for which this import will connected directly.

Once the import class is created, let’s put the functionality to import the data to the users table.

<?php

namespace App\Imports;

use App\Models\User;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Concerns\ToModel;

class UsersImport implements ToModel
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        return new User([
           'name' => $row[1],
           'email' => $row[2],
           'password' => ''
        ]);
    }
}

Let me do the quick explanation for the above code.

• In the model function, it will take inputs as an array from the controller. (We will create the controller in the next step).
• After that it is pushing the data to the User model. In the User.php (model), we have already the fillable property by default.

After creating the functionality of export import class, we will create a controller file.

User Authentication in Laravel 8 Using UI Auth Package

Create a Controller to Import Export Excel File

Here, you will require a controller for putting the functionalities. So, create a controller by hitting the below command.

php artisan make:controller UserController

After creating the controller, let’s add some code there. Firstly, I will implement the functionality of export excel file.

<?php

namespace App\Http\Controllers;

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

class UserController extends Controller
{
    /**
     * Return view with data
     *
     * @return void
     */
    public function index()
    {
        $users = User::all();
        return view('users', compact('users'));
    }

    /**
     * Export data to Excel/CSV
     *
     * @return void
     */
    public function export()
    {
        return Excel::download(new UsersExport, 'users.xls');
    }

    /**
     * Import User View
     *
     * @return void
     */
    public function importUser()
    {
        return view('import');
    }

    /**
     * Import User data through sheet
     *
     * @return void
     */
    public function import(Request $request)
    {
        Excel::import(new UsersImport, $request->file('file'));
        return back()->with('success', 'Users imported successfully');
    }
}

In the above controller, I have created two functions-

• In the first function, I have fetched all users through the User model and returned it to the users view. (I will create the view in the next step)
• The second function is to export the data which are coming through the UsersExport class file. Here, the UsersExport class has invoked and it passed the data in the download form. Also, here, I have put the file name with the extension that will be downloaded. So, here, I have put the file name as users.xls. It means the users list will be downloaded in the form of an excel file.

How to Configure PHPMailer in Laravel 8 For Sending Email

Add Routes For Excel Export

Add the below routes in the web.php file.

<?php

use App\Http\Controllers\UserController;
use Illuminate\Support\Facades\Route;

/*
|--------------------------------------------------------------------------
| 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!
|
*/

// export routes
Route::get("users", [UserController::class, "index"]);
Route::get('export', [UserController::class, "export"])->name("export");


// import routes
Route::get("import", [UserController::class, "importUser"]);
Route::post("import", [UserController::class, "import"])->name('import');

After creating the above routes, we will create a view.

Create a View

Here, I will create two views. The first view will be for displaying the data. Also, it will contain a button to export the data to excel. These are the following views-

• users.blade.php
• import.blade.php

So, in the users.blade.php just add the below snippet.

<!doctype html>
<html lang="en">
  <head>
    <title>Laravel 8 Import Export Excel</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
  </head>
  <body>
      <div class="container py-4">
          <div class="row">
              <div class="col-xl-12 text-right">
                  <a href="{{ route('export') }}" class="btn btn-primary">Export to Excel/CSV</a>
              </div>
          </div>

          <div class="card mt-4">
              <div class="card-header">
                <h5 class="card-title font-weight-bold">Export/Import Tutorial in Laravel 8</h5>
              </div>
              <div class="card-body">
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th>Id</th>
                            <th>Name</th>
                            <th>Email</th>
                        </tr>
                    </thead>

                    <tbody>
                      @forelse ($users as $user)
                          <tr>
                              <td>{{ $user->id }}</td>
                              <td>{{ $user->name }}</td>
                              <td>{{ $user->email }}</td>
                          </tr>
                      @empty

                      @endforelse
                    </tbody>
                </table>
              </div>
          </div>
      </div>
  </body>
</html>

Dynamic Email Configuration in Laravel 8 For Sending Email

After putting the above snippet, you can test the result. Firstly, start with exporting.

Export Excel File – Result

Now, you can test the application. When you will open the URL http://localhost:8000/users, you will have the below result.

Here, in the above screenshot, there is a button to export data to excel/csv. When you will click on that, it will download the excel file as showing in the result.

Import Excel File in Laravel 8 – Result

Now, coming to the import part. In the import.blade.php, add the below snippet.

<!doctype html>
<html lang="en">
  <head>
    <title>Import Excel Sheet in Laravel 8</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
  </head>
  <body>
      <div class="container py-5">
          <div class="row">
              <div class="col-xl-6 m-auto">
                    <form action="{{ route('import') }}" method="POST" enctype="multipart/form-data">
                        @csrf
                        <div class="card">
                            @if(Session::has('success'))
                                <div class="alert alert-success alert-dismissible">
                                    <button type="button" class="close" data-dismiss="alert">&times;</button>
                                    {{ Session::get('success') }}
                                </div>

                            @elseif(Session::has('failed'))
                                <div class="alert alert-success alert-dismissible">
                                    <button type="button" class="close" data-dismiss="alert">&times;</button>
                                    {{ Session::get('success') }}
                                </div>
                            @endif

                            <div class="card-header">
                                <h5 class="card-title font-weight-bold">Import Excel/CSV</h5>
                            </div>

                            <div class="card-body">
                                <div class="form-group">
                                    <label for="file">Choose File</label>
                                    <input type="file" name="file" class="form-control">
                                </div>
                            </div>

                            <div class="card-footer">
                                <button type="submit" class="btn btn-primary">Upload File</button>
                            </div>
                        </div>
                    </form>
              </div>
          </div>
      </div>
  </body>
</html>

Now, see the result. The above snippet will generate the below output. So, just choose the excel file and hit the upload button.

After hitting the button, you will see the below result. Here, I got the success response. That means, the sheet has been uploaded and data has been inserted into the users table.

Conclusion

Finally, we completed the functionality of export and import of Excel and CSV file in Laravel. This is quite simple to make a download with the excel file. Also, you can set the headers before exporting the data to excel and CSV. Similary, for importing the data using the excel sheet, it is easy to import and extract the rows. After extracting the rows, we inserted into the database table. So, that’s it for this import export excel tutorial in Laravel. If you stuck in any steps then don’t forget to put your comments below.