Import and Export Excel Files using Laravel-Excel

In this tutorial, we'll explore how to implement Excel file import and export functionality in a Laravel application using the laravel-excel package. This package simplifies the process of working with Excel files by providing a set of convenient features.

Prerequisites

Before we begin, ensure you have the following prerequisites:

• A Laravel project set up on your local development environment.
• Composer installed to manage PHP dependencies.

Step 1: Install the laravel-excel Package

Open your terminal and navigate to your Laravel project's root directory. Run the following command to install the laravel-excel package using Composer:

composer require maatwebsite/excel

Step 2: Publish Configuration

After installing the package, you need to publish its configuration files. Run the following Artisan command to do so:

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

This command will copy the necessary configuration files to the config directory of your Laravel project.

Step 3: Generate Excel Import and Export Classes

To handle the import and export processes, we'll create separate classes using the make:export and make:import Artisan commands. For this example, let's assume we're working with a User model.

Generate an export class by running the following command:

php artisan make:export UsersExport --model=User

Next, generate an import class using the following command:

php artisan make:import UsersImport --model=User

These commands will create UsersExport.php and UsersImport.php files in the app/Exports and app/Imports directories, respectively.

Step 4: Configure Export and Import Classes

Open the generated UsersExport and UsersImport classes located in the app/Exports and app/Imports directories. These classes define how data will be formatted for export and import operations. You can customize these classes to match your application's requirements. For instance, you can specify the columns to export or handle data validation during import.

Step 5: Create Controllers

To handle the export and import actions, let's create two separate controllers. Run the following Artisan commands to generate the controllers:

php artisan make:controller ExcelExportController
php artisan make:controller ExcelImportController

These commands will create ExcelExportController.php and ExcelImportController.php files in the app/Http/Controllers directory.

Step 6: Implement Export Functionality

In the ExcelExportController, we'll define a method to handle the export action. Open the ExcelExportController.php file and add the following code:

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

public function export()
{
    return Excel::download(new UsersExport, 'users.xlsx');
}

This method uses the Excel facade to initiate the export process. It will generate an Excel file named users.xlsx containing the exported data.

Step 7: Implement Import Functionality

In the ExcelImportController, let's define a method to handle the import action. Open the ExcelImportController.php file and add the following code:

use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;

public function import(Request $request)
{
    $file = $request->file('file');
    Excel::import(new UsersImport, $file);

    return redirect()->back()->with('success', 'Users imported successfully.');
}

In this method, we use the uploaded Excel file to perform the import using the Excel facade. After the import is complete, we redirect back to the previous page with a success message.

Step 8: Define Routes

To access the export and import actions, we need to define appropriate routes. Open the routes/web.php file and add the following route definitions:

Route::get('/export', 'ExcelExportController@export');
Route::post('/import', 'ExcelImportController@import');

These routes will map the export and import actions to the respective controller methods.

Sure, here's a continuation of the tutorial with more details on creating views for the Excel import and export functionality using Laravel-Excel:

Step 9: Create Views

Now that we have set up the controllers, routes, and functionality, it's time to create the views that will allow users to interact with the export and import actions.

Create Export View

1. Create a new Blade view file named export.blade.php inside the resources/views/excel directory. This view will contain a button to trigger the export process.

2. Add the following code to the export.blade.php file:

@extends('layouts.app')

@section('content')
    <div class="container">
        <h2>Export Users to Excel</h2>
        <form action="{{ url('/export') }}">
            <button type="submit" class="btn btn-primary">Export Users</button>
        </form>
    </div>
@endsection

In this view, we create a form with a button that, when clicked, will initiate the export action by sending a GET request to the /export route.

Create Import View

1. Similarly, create another Blade view file named import.blade.php inside the resources/views/excel directory. This view will contain a form for uploading an Excel file and triggering the import process.

2. Add the following code to the import.blade.php file:

@extends('layouts.app')

@section('content')
    <div class="container">
        <h2>Import Users from Excel</h2>
        @if(Session::has('success'))
            <div class="alert alert-success">{{ Session::get('success') }}</div>
        @endif
        <form action="{{ url('/import') }}" method="POST" enctype="multipart/form-data">
            @csrf
            <div class="form-group">
                <label for="file">Choose Excel File</label>
                <input type="file" name="file" class="form-control">
            </div>
            <button type="submit" class="btn btn-primary">Import Users</button>
        </form>
    </div>
@endsection

Here, we create a form that allows users to select and upload an Excel file. The form's action points to the /import route, which will trigger the import action. We also display a success message if the import is successful.

Update Layout

Make sure you have a layouts/app.blade.php file that serves as the base layout for your views. This layout should include the necessary HTML structure, CSS, and JavaScript links that your application uses.

With the export and import views created, your users can now navigate to these views and interact with the export and import functionalities.

Certainly! Here's the content formatted in MDX (Markdown with JSX) format:

Step 10: Write Tests for Import and Export

Now that you have implemented the import and export functionality, it's crucial to ensure that they work correctly. Writing tests will help you catch any issues that might arise in the future. Let's create tests to validate the import and export processes.

1. Open the terminal and navigate to your project directory.

2. Run the following command to generate a new test class for the Excel import and export:

php artisan make:test ExcelImportExportTest

3. Open the generated test file located at tests/Feature/ExcelImportExportTest.php.

4. Replace the content of the file with the following code:

// Import necessary namespaces
namespace Tests\Feature;

use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Foundation\Testing\WithFaker;
use Illuminate\Http\UploadedFile;
use Maatwebsite\Excel\Facades\Excel;
use Tests\TestCase;

class ExcelImportExportTest extends TestCase
{
    use RefreshDatabase;

    /** @test */
    public function can_export_users_to_excel()
    {
        $this->seed(UserSeeder::class);

        $response = $this->get('/export');

        $response->assertStatus(200);
        $response->assertSeeText('Export Users');

        $response->assertSee('<form', false);
        $response->assertSee('<button', false);

        $response->assertSeeText('Export Users');
    }

    /** @test */
    public function can_import_users_from_excel()
    {
        $file = new UploadedFile(storage_path('app/public/test_users.xlsx'), 'test_users.xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', null, true);

        $response = $this->post('/import', ['file' => $file]);

        $response->assertStatus(302);
        $response->assertSessionHas('success', 'Users imported successfully.');

        $this->assertDatabaseCount('users', 5); // Adjust the count based on your test data

        // Additional assertions on imported data if needed
    }
}

5. Ensure that you have the necessary test data and seeding set up in your project. Replace the UserSeeder::class with the appropriate seeder class and adjust the user count as needed.

6. Save the file and close it.

7. Open the terminal and run the tests using the following command:

php artisan test

The tests will run, and you should see the output indicating whether the tests passed or failed. These tests will help you ensure that your Excel import and export functionality works correctly and can help catch any issues that may arise during development.

With these tests in place, you can confidently make changes to your import and export functionality in the future, knowing that your tests will catch any regressions or unexpected behavior.

Conclusion

Congratulations! You've successfully implemented and tested the Laravel Excel import and export functionality in your Laravel application.