Import and Export Excel in Laravel: A Step-by-Step Guide

Introduction to Export excel in Laravel:

In this tutorial, we will walk you through the process of building a Laravel web application that allows importing and exporting Excel data. We will cover every step, from creating the project to setting up routes, controllers, models, and views. By the end of this article, you will have a fully functional application that can efficiently handle Excel data.

Key Features:

1. Easy Project Setup: Quickly create a new Laravel project and get started with Excel data handling.
2. Seamless Database Integration: Create and manage your database table effortlessly with migrations.
3. Excel Import: Enable smooth importing of Excel data into your application with a custom import class.
4. Excel Export: Effortlessly export your database data to Excel files using a dedicated export class.
5. Dependency Management: Install and manage the necessary maatwebsite/excel package for Excel support.
6. Robust Controller: Create a dedicated controller to handle the import and export functionality.
7. Data Model: Define a model to interact with the database and streamline data operations.
8. User-friendly Views: Design intuitive views for users to upload and download Excel data.
9. Easy Routing: Set up routes to connect controller methods to views seamlessly.
10. Step-by-Step Guide: Follow a comprehensive step-by-step guide to master Excel data handling in Laravel.

Prerequisites:

1. Basic Knowledge of PHP: Familiarity with PHP programming is essential for understanding Laravel’s concepts and syntax.
2. Laravel Framework: Ensure you have Laravel installed on your system to follow along with the project setup.
3. Composer: Make sure you have Composer installed to manage the project’s dependencies effectively.
4. Database Setup: Set up and configure a database (e.g., MySQL, PostgreSQL) for storing imported data.
5. PHP Spreadsheet Extension: Verify that the PHP Spreadsheet extension (phpoffice/phpspreadsheet) is installed and enabled.
6. Laravel Artisan: Familiarity with Laravel’s command-line interface (Artisan) for running commands and generating code.
7. Web Server: Have a local development environment with a web server (e.g., Apache, Nginx) to run the Laravel application.
8. HTML, CSS, and JavaScript: Basic knowledge of front-end technologies to design and interact with the application’s views.

With these prerequisites, you’ll be ready to dive into building the Excel import and export functionality in Laravel.

Step 1: Create a New Laravel Project

To start, open your terminal and execute the following command to create a new Laravel project:

composer create-project laravel/laravel excel-import-export-example
cd excel-import-export-example

Step 2: Create a Database Table

Next, create a new database table for storing employee data. For this example, let’s create a table called employees with columns: id, name, email, and phone. You can use a database tool like phpMyAdmin or execute a migration to create the table:

php artisan make:migration create_employees_table --create=employees

Step 3: Run the Migration

Run the migration command to create the employees table in the database:

php artisan migrate

Step 4: Install Dependencies for Excel Handling

To enable Excel import and export, we’ll use the maatwebsite/excel package. Install it using Composer:

composer require maatwebsite/excel

After installing the package, register the service provider and facade by adding the following lines in the config/app.php file:

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

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

Step 5: Create an Import Class

Create an import class that handles importing Excel data into the employees table:

php artisan make:import EmployeeImportClass

<?php

namespace App\Imports;

use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use App\Models\Employee; // Import the Employee model

class ExcelImportClass implements ToCollection
{
    /**
    * @param Collection $collection
    */
    public function collection(Collection $rows)
    {
        // Initialize a variable to keep track of the row index
        $rowIndex = 0;

        foreach ($rows as $row) {
            // Skip the first row
            if ($rowIndex == 0) {
                $rowIndex++;
                continue;
            }
            Employee::create([
                'name' => $row[0], 
                'email' => $row[1], 
                'phone' => $row[2], 
            ]);

            $rowIndex++;
        }
    }
}

Step 6: Create an Export Class

Create an export class that handles exporting employees data to an Excel file:

php artisan make:export EmployeeExportClass --model=App\Models\Employee

<?php

namespace App\Exports;

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

class ExcelExportClass implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return Employee::all();
    }
}

Step 7: Create a Controller

Generate a controller to handle the import and export functionality:

php artisan make:controller ExcelImportExportController

<?php

namespace App\Http\Controllers;

use Maatwebsite\Excel\Facades\Excel;
use App\Imports\ExcelImportClass;
use App\Exports\ExcelExportClass;
use Illuminate\Http\Request;
use App\Models\Employee;
use Illuminate\Support\Facades\Session; 

class ExcelImportController extends Controller
{
    public function importForm()
    {
        $data['employees'] = Employee::all();
        return view('upload',$data);
    }

    public function import(Request $request)
    {
        $request->validate([
            'excel_file' => 'required|mimes:xlsx,xls|max:2048', // Validate the file type and size
        ]);

        if ($request->hasFile('excel_file')) {
            $filePath = $request->file('excel_file')->store('temp'); // Store the uploaded file temporarily

            Excel::import(new ExcelImportClass, storage_path('app/' . $filePath));

            // Optionally, you can now delete the temporary file:
            unlink(storage_path('app/' . $filePath));

            return redirect()->back()->with('success', 'Excel file imported successfully!');
        }

        return redirect()->back()->with('error', 'No file was uploaded.');
    }

    public function download()
    {
        $employees = Employee::all();
    
        return Excel::download(new ExcelExportClass($employees), 'employees.xlsx');
    }
    
}

Step 8: Create a Model

Create a model for the employees table to interact with the database:

php artisan make:model Employee

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Employee extends Model
{
    protected $fillable = ['name', 'email', 'phone'];
    protected $table = 'employee_table';
}

Step 9: Design the Views

Create the views for the import and export functionality. Design an upload form in the upload.blade.php view for importing Excel data. Additionally, create a download button to export the data as an Excel file.

<!-- resources/views/upload.blade.php -->
<!DOCTYPE html>
<html>

<head>
    <title>Upload Excel</title>
    <link href="{{ asset('css/styles.css') }}" rel="stylesheet">
</head>

<body>
    <div class="container">
        <h2>Upload Excel File</h2>

        @if (Session::has('success'))
            <div class="success-message fade-out">{{ Session::get('success') }}</div>
        @endif
        @if (Session::has('error'))
        <div class="error-message">{{ Session::get('error') }}</div>
        @endif
        <form action="{{ route('import') }}" method="POST" enctype="multipart/form-data">
            @csrf
            <label class="custom-file-upload">
                Choose File
                <input type="file" name="excel_file" accept=".xlsx, .xls">
            </label>
            <button type="submit" class="custom-btn-upload">Upload</button>
            @if ($errors->has('excel_file'))
                <div class="error-message">{{ $errors->first('excel_file') }}</div>
            @endif
        </form>

        <p class="upload-message">Supported file formats: .xlsx, .xls</p>
          
        @if (count($employees) > 0)
            <h3>Inserted Data:   <a href="{{ route('download') }}" class="custom-file-download">Download Excel</a></h3>
            <table>
                <thead>
                    <tr>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Phone</th>
                    </tr>
                </thead>
                <tbody>
                    @foreach ($employees as $employee)
                        <tr>
                            <td>{{ $employee->name }}</td>
                            <td>{{ $employee->email }}</td>
                            <td>{{ $employee->phone }}</td>
                        </tr>
                    @endforeach
                </tbody>
            </table>
        @endif
    </div>

    <script>
        // Auto-hide the success message after 3 seconds
        setTimeout(() => {
            const successMessage = document.querySelector('.success-message');
            if (successMessage) {
                successMessage.style.display = 'none';
            }
        }, 3000);
    </script>
</body>

</html>

Step 10: Define Routes

Define the routes in the web.php file to link the controller methods to the views:

<?php

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

/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider and all of them will
| be assigned to the "web" middleware group. Make something great!
|
*/


Route::get('/upload', [ExcelImportController::class, 'importForm']);
Route::post('/import', [ExcelImportController::class,'import'])->name('import');
Route::get('/download', [ExcelImportController::class,'download'])->name('download');

Conclusion:

Congratulations! You’ve successfully built a Laravel web application capable of importing and exporting Excel data. By following each step, you now have a better understanding of handling Excel files in Laravel using the maatwebsite/excel package. Feel free to explore further and add more features to your application, such as data validation and handling larger Excel files. Happy coding!

Output:

---

Our Recommendation

• Top 30 Laravel Interview Questions: A comprehensive guide
• Middleware in Laravel: A Comprehensive Guide
• Laravel vs CodeIgniter: Framework Comparison
• How to Generate PDF Files Using Dompdf in Laravel
• Laravel Pagination Example customizations
• Laravel Relationship: Unlock power of Data with
• CRUD Operations in Laravel 9 – A comprehensive guide
• Laravel login Authentication in Laravel 9
• User Registration in Laravel Example: Simple Steps to Get Started
• Import and Export Excel in Laravel: A Step-by-Step Guide
• Laravel JWT Tutorial: A comprehensive guide
• Laravel Passport: A comprehensive guide in Laravel 10