Are you looking for import and export Excel files in Laravel? In the real world, sometimes we want to export records from the application. You might want users’ data for whatever reason – like CRM, marketing purpose, etc.

Similarly, you may require importing bulk users into your application. In both cases, you can have Excel files which are then processed for further operations. Here, I am taking users as an example. In your case, it can be anything else.

In this article, we study exporting and importing Excel files in your Laravel application.

Getting Started

While researching this topic, I came across a nice library called Fast Excel built for Laravel. The Fast Excel package gives support for xlsx, ods, and csv files.

Under the hood, this library uses the Spout package to read and write Spreadsheet files. The Spout package could be considered faster and memory-friendly.

To get started, head over to the terminal and install the Fast Excel package using the command:

composer require rap2hpoutre/fast-excel

As we are going to perform both import and export operations, add the below routes.

Route::get('/excel', 'ExcelController@index');
Route::post('/import', 'ExcelController@import');
Route::get('/export', 'ExcelController@export');

Next, run the command to create ExcelController.

php artisan make:controller ExcelController

Create a view called excel.blade.php and add the below code into it.

@if(session('success'))
    <strong>{{session('success')}}</strong>
@endif

<form method="post" action="{{ url('import') }}" enctype="multipart/form-data">
    <p>
        <input type="file" name="users" required />
    </p>
    <input type="submit" name="submit" value="Import Users">
    <p>
        <a href="{{ url('export') }}">Export Users</a>
    </p>
    @csrf
</form>

Add the methods mapped to the routes in the ExcelController. I am also including Fast Excel Facade and user model.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\User;
use Rap2hpoutre\FastExcel\FastExcel;

class ExcelController extends Controller
{
    public function index()
    {
        return view('excel');
    }

    public function import(Request $request)
    {
    }

    public function export()
    {
    }
}

We are done with the basic setup. Of course, you can adjust the above setup as per your project flow.

Export Excel in the Laravel

In the HTML, I called the export route for the Export Users. And when we click on it, the Excel file should be downloaded along with the user’s data. For this, add the code inside the export function.

public function export()
{
    return (new FastExcel(User::all()))->download('users.xlsx');
}

Now, when you export users you will get multiple columns. You can filter out the columns in the end result.

public function export()
{
    return (new FastExcel(User::all()))->download('users.xlsx', function ($user) {
        return [
            'Name' => $user->name,
            'Email' => $user->email,
        ];
    });
}

Here, I am exporting only the Name and Email columns to Excel. To set another file type, pass the name with extensions like users.csv or users.ods.

Import Excel in the Laravel

Let’s say you need to import bulk users into your Laravel application. For this, you have created a users.xlsx file that has Name and Email as a heading. Refer to the screenshot below.

Now, to browse this file we have already given a file input in the HTML. All need to do is receive this file on the server side, read the entries, and add them to the database. The import method will have the following code.

public function import(Request $request)
{
    $users = (new FastExcel)->import($request->file('users'), function ($line) {
        return User::create([
            'name' => $line['Name'],
            'email' => $line['Email']
        ]);
    });

    return redirect('excel')->with(['success' => "Users imported successfully."]);
}

The above code will insert all entries from Excel into the database. It can also add duplicate entries which we don’t want to. To handle this, let’s add a check for email and insert the entry only if an email does not exist in the database.

public function import(Request $request)
{
    $users = (new FastExcel)->import($request->file('users'), function ($line) {
        return User::firstOrCreate(
            ['email' => $line['Email']],
            ['name' => $line['Name']],
        );
    });

    return redirect('excel')->with(['success' => "Users imported successfully."]);
}

Import CSV

If you want to import CSV files, you can’t use the previous code directly. The CSV files first need to be stored on the server and then imported.

Create a symbolic link to the storage folder using the command:

php artisan storage:link

Then in the import method, we receive the file, store it, read the entries, and insert the records.

public function import(Request $request)
{
    $filename = $request->file('users')->getClientOriginalName();
    $request->file('users')->storeAs('public/csv', $filename);
    $path = public_path('storage/csv/'.$filename);

    $users = (new FastExcel)->import($path, function ($line) {
        return User::firstOrCreate(
            ['email' => $line['Email']],
            ['name' => $line['Name']],
        );
    });

    return redirect('excel')->with(['success' => "Users imported successfully."]);
}

I hope you understand the export and import of Excel files in Laravel. Please share your thoughts and suggestions in the comment section below.

Related Articles

• How to Read and Write Spreadsheet Files in PHP
• Stripe Payment Gateway Integration in Laravel
• Create Thumbnail in Laravel using Intervention Image Library

If you liked this article, then please subscribe to our YouTube Channel for video tutorials.