Laravel 9 Import Export Excel & CSV File Example - TechvBlogs

Laravel 9 Import Export Excel & CSV File Example

Throughout this tutorial, you will learn how to easily import and export Excel and CSV files in the Laravel 9 application while communicating with the PHP MySQL database using Maatwebsite/Laravel-Excel package.


Suresh Ramani - Author - TechvBlogs
Suresh Ramani
 

5 months ago

Laravel Excel is designed at being a Laravel-flavoured PhpSpreadsheet. It is a manageable and elegant wrapper around PhpSpreadsheet to simplify exports and imports. PhpSpreadsheet is a php based library that enables you to read and write different spreadsheet file formats, like Excel and LibreOffice Calc. Laravel Excel has the following features:

  • Easily export collections to Excel.
  • Export queries with automatic chunking for better performance.
  • Queue exports for better performance.
  • Easily export Blade views to Excel.
  • Easily import to collections.
  • Read the Excel file in chunks.
  • Handle the import inserts in batches.

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 files directly from the database in laravel application.

Requirements

  • PHP: ^8.0|^8.1
  • Laravel: 9.0
  • PhpSpreadsheet: ^1.15
  • PHP extension php_zip enabled
  • PHP extension php_xml enabled
  • PHP extension php_gd2 enabled
  • PHP extension php_iconv enabled
  • PHP extension php_simplexml enabled
  • PHP extension php_xmlreader enabled
  • PHP extension php_zlib enabled

Step 1: Install Laravel Project

First, open Terminal and run the following command to create a fresh laravel project:

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

or, if you have installed the Laravel Installer as a global composer dependency:

laravel new laravel-excel

Step 2: Configure Database Details

After, Installation Go to the project root directory, open .env file, and set database detail as follow:

DB_CONNECTION=mysql 
DB_HOST=127.0.0.1 
DB_PORT=3306 
DB_DATABASE=<DATABASE NAME>
DB_USERNAME=<DATABASE USERNAME>
DB_PASSWORD=<DATABASE PASSWORD>

Read Also: Laravel 9 CRUD Example Tutorial for Beginners

Step 3: Install maatwebsite/excel package

You can install Laravel Excel via composer. You've to run this command for the installation.

composer require maatwebsite/excel

If composer require fails on Laravel 9 because of the simple-cache dependency, you will have to specify the psr/simple-cache version as ^2.0 in your composer.json to satisfy the PhpSpreadsheet dependency. You can install both at the same time as:

composer require psr/simple-cache:^2.0 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 the command, and publish the config.

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

This will create a new config file named config/excel.php.

Step 4: Generate Fake Data and Migrate Table

In the First step, We migrate the user table. After migration run successfully We moved to the second step.

php artisan migrate

In the Second Step, We generate the fake record. Here We use tinker to generate the fake records. You can use a different method as of your requirement.

php artisan tinker

After Opening the tinker, you need to run this command to generate the fake records in our database.

User::factory()->count(100)->create();

Step 5: Create a Routes

In this step, We will add a route to handle requests for import and export files.

use App\Http\Controllers\UserController;

Route::get('/file-import',[UserController::class,'importView'])->name('import-view');
Route::post('/import',[UserController::class,'import'])->name('import');
Route::get('/export-users',[UserController::class,'exportUsers'])->name('export-users');

Step 6: Create Import Class

Maatwebsite provides a way to build an import class and we have to use it in the controller. So it would be a great way to create a new Import class. So you have to run the following command and change the following code on that file:

php artisan make:import ImportUser --model=User

app/Imports/ImportUser.php

<?php

namespace App\Imports;

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

class ImportUser 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' => bcrypt($row[2]),
        ]);
    }
}

Here you can see map CSV or excel column value to our Eloquent Model. You need to format that CSV or excel column as you map in your import class.

Read Also: How to Install MongoDB on Ubuntu 20.04

Step 7: Create Export Class

Maatwebsite provides a way to build an export class and we have to use it in the controller. So it would be a great way to create a new export class. So you have to run the following command and change the following code on that file:

php artisan make:export ExportUser --model=User

app/Exports/ExportUser.php

<?php

namespace App\Exports;

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

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

Step 8: Create Controller

Next, We have to create a controller to display a form to upload CSV or excel file records. Let's Create a controller named UserController using the command given below:

php artisan make:controller UserController

Once the above command execute, it will create a controller file UserController.php in the app/Http/Controllers directory. Open the UserController.php file and put this code into that file.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use App\Imports\ImportUser;
use App\Exports\ExportUser;
use App\Models\User;

class UserController extends Controller
{
    public function importView(Request $request){
        return view('importFile');
    }

    public function import(Request $request){
        Excel::import(new ImportUser, $request->file('file')->store('files'));
        return redirect()->back();
    }

    public function exportUsers(Request $request){
        return Excel::download(new ExportUser, 'users.xlsx');
    }
}

Step 9: Create Blade / View Files

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/importFile.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>Laravel 9 Import Export Excel & CSV File - TechvBlogs</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 9 Import Export Excel & CSV File - <a href="https://techvblogs.com/blog/laravel-9-import-export-excel-csv-file" target="_blank">TechvBlogs</a>
        </h2>
        <form action="{{ route('import') }}" method="POST" enctype="multipart/form-data">
            @csrf
            <div class="form-group mb-4">
                <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 Users</button>
            <a class="btn btn-success" href="{{ route('export-users') }}">Export Users</a>
        </form>
    </div>
</body>

</html>

Run Laravel Application

Lastly, we have to run the Laravel application, for this, we have to go to the command prompt, and write the following command:

php artisan serve

After executing this command, Open http://localhost:8000/file-import in your browser.

Thank you for reading this blog.

Read Also: How To Install Vue 3 in Laravel 9 From Scratch

If you want to manage your VPS / VM Server without touching the command line go and  Checkout this linkServerAvatar allows you to quickly set up WordPress or Custom PHP websites on VPS / VM in a  matter of minutes.  You can host multiple websites on a single VPS / VM, configure SSL certificates, and monitor the health of your server without ever touching the command line interface.

If you have any queries or doubts about this topic please feel free to contact us. We will try to reach you.

Comments (5)

Franck Tiomela Franck Tiomela 3 weeks ago

composer require psr/simple-cache:^2.0 maatwebsite/excel

Suresh Ramani Suresh Ramani 3 weeks ago

Thank you for pointing this out for Laravel 9.

hiu hiu 4 weeks ago

PS C:\Trainning\GIT\vuesax> composer require maatwebsite/excel Using version ^3.1 for maatwebsite/excel ./composer.json has been updated Running composer update maatwebsite/excel Loading composer repositories with package information Updating dependencies Your requirements could not be resolved to an installable set of packages.

Problem 1 - maatwebsite/excel[3.1.36, ..., 3.1.x-dev] require phpoffice/phpspreadsheet ^1.18 -> satisfiable by phpoffice/phpspreadsheet[1.18.0, ..., 1.25.2]. - maatwebsite/excel[3.1.0, ..., 3.1.25] require php ^7.0 -> your php version (8.1.6) does not satisfy that requirement. - maatwebsite/excel[3.1.26, ..., 3.1.35] require illuminate/support 5.8.*|^6.0|^7.0|^8.0 -> found illuminate/support[v5.8.0, ..., 5.8.x-dev, v6.0.0, ..., 6.x-dev, v7.0.0, ..., 7.x-dev, v8.0.0, ..., 8.x-dev] but these were not loaded, likely because it conflicts with another require. - phpoffice/phpspreadsheet[1.18.0, ..., 1.25.2] require ext-gd * -> it is missing from your system. Install or enable PHP's gd extension. - Root composer.json requires maatwebsite/excel ^3.1 -> satisfiable by maatwebsite/excel[3.1.0, ..., 3.1.x-dev].

To enable extensions, verify that they are enabled in your .ini files: - C:\xampp\php\php.ini You can also run php --ini in a terminal to see which files are used by PHP in CLI mode. Alternatively, you can run Composer with --ignore-platform-req=ext-gd to temporarily ignore these required extensions. You can also try re-running composer require with an explicit version constraint, e.g. "composer require maatwebsite/excel:*" to figure out if any version is installable, or "composer require maatwebsite/excel:^2.1" if you know which you need.

Installation failed, reverting ./composer.json and ./composer.lock to their original content.

Suresh Ramani Suresh Ramani 4 weeks ago

Please read the installation error. Also, Read the Requirements as I mentioned in the article.

Masaba Moses Masaba Moses 2 months ago

Thanks very much for this wonderful and straight forward tutorial. Just a quick inquiry on how i can include columns 7in my excel file. Thanks

ServerAvatar
Multiple sites on a single VPS/VM/Server Deploy PHP sites on cloud in minutes, simple as that!
ServerAvatar
Generate Free SSL Certificate - SSLForWeb

Generate Free SSL Certificate

  • 90-Day Certificates
  • SSL Monitoring
  • Wildcard Certificates
  • Multiple Domains
  • Certificate Management
  • Expiration Reminders

Comment


Note: All Input Fields are required.