Search

Step-by-Step Tutorial: Importing and Exporting Excel and CSV Files in Laravel

  • Share this:
Step-by-Step Tutorial: Importing and Exporting Excel and CSV Files in Laravel

Introduction

Laravel provides built-in support for importing and exporting data from and to Excel and CSV files. This can be useful when working with large datasets or when you need to migrate data between different systems.

In this tutorial, we'll cover the following topics:

  • Setting up a new Laravel project
  • Installing and configuring the Maatwebsite Excel package
  • Importing data from an Excel or CSV file
  • Exporting data to an Excel or CSV file

 

Setting up a new Laravel project

To get started, we'll create a new Laravel project using the following command:
composer create-project --prefer-dist laravel/laravel myproject

This will create a new Laravel project in a directory called myproject.

Installing and configuring the Maatwebsite Excel package

Next, we'll install the Maatwebsite Excel package, which provides support for working with Excel and CSV files in Laravel. To install the package, run the following command in your project directory:

composer require maatwebsite/excel

Once the package is installed, we need to publish the configuration file using the following command:

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

This will create a new file called config/excel.php, which we can use to configure the package.


Importing data from an Excel or CSV file

To import data from an Excel or CSV file, we'll create a new controller and a new view. First, let's create the controller using the following command:

php artisan make:controller ImportController

This will create a new file called app/Http/Controllers/ImportController.php, which we can use to handle the import process.

In the ImportController class, we'll define two methods: index() and import().

 

<?php

    namespace App\Http\Controllers;

    use Illuminate\Http\Request;
    use Maatwebsite\Excel\Facades\Excel;
    use App\Imports\UsersImport;

    class ImportController extends Controller
    {
        public function index()
        {
            return view('import');
        }

        public function import(Request $request)
        {
            $file = $request->file('file');

            Excel::import(new UsersImport, $file);

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

In the index() method, we'll return a view called import.blade.php, which we'll create in a moment. This view will contain a form that allows users to upload an Excel or CSV file.

In the import() method, we'll retrieve the file from the request, and then use the Excel::import() method to import the data into our database. We're using an UsersImport class to handle the import process, which we'll create in a moment.

Next, let's create the import.blade.php view:

 

<!DOCTYPE html>
    <html>
    <head>
        <title>Import Excel or CSV file in Laravel</title>
    </head>
    <body>
        @if(Session::has('success'))
            <div>{{ Session::get('success') }}</div>
        @endif

        <form action="{{ route('import') }}" method="POST" enctype="multipart/form-data">
            @csrf
            <input type="file" name="file">
            <button type="submit">Import</button>
        </form>
    </body>
</html>

This view contains a form that allows users to upload an Excel or CSV file. When the form is submitted, it will be sent to

the import() method in the ImportController class, which will handle the import process.

Next, let's create the UsersImport class:

 

<?php

namespace App\Imports;

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

class UsersImport implements ToModel
{
    public function model(array $row)
    {
        return new User([
            'name' => $row[0],
            'email' => $row[1],
            'password' => bcrypt($row[2]),
        ]);
    }
}

 

 

In this class, we're implementing the ToModel interface, which defines a single method called model(). This method is called for each row in the Excel or CSV file, and it should return a new instance of the model that we want to import.

In this case, we're importing User models, so our model() method creates a new User instance using the data from the current row in the file. We're assuming that the first column contains the user's name, the second column contains their email address, and the third column contains their password.

 

Once we've defined our ImportController and UsersImport classes, we're ready to test the import process. To do this, we'll visit the /import URL in our browser, select an Excel or CSV file to upload, and then click the "Import" button.

If everything is working correctly, we should see a "Data imported successfully." message, indicating that the data has been successfully imported into our database.

Exporting data to an Excel or CSV file

Exporting data to an Excel or CSV file is very similar to importing data, except that we'll need to create a new controller method and view to handle the export process.

First, let's create a new controller method called export():

 

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\UsersExport;

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

In this method, we're using the Excel::download() method to generate a new Excel file containing our exported data. We're using a UsersExport class to handle the export process, which we'll create in a moment.

Next, let's create the UsersExport class:

 

 

<?php

namespace App\Exports;

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

class UsersExport implements FromCollection
{
    public function collection()
    {
        return User::all();
    }
}

 

In this class, we're implementing the FromCollection interface, which defines a single method called collection(). This method should return a collection of data that we want to export.

In this case, we're exporting User models, so our collection() method simply returns all of the User models from our database.

Once we've defined our ExportController and UsersExport classes, we're ready to test the export process. To do this, we'll visit a URL such as /export, which will trigger the export process and download an Excel file containing our exported data.

 

Conclusion

In this tutorial, we've seen how to import and export data from and to Excel and CSV files in Laravel using the Maatwebsite Excel package. By following the steps outlined in this tutorial, you should now have a good understanding of how to work with Excel and CSV files in Laravel.

 

 

 

 

 

 

 

 


Jahangir Alam

Jahangir Alam

I'm a full-stack website developer with a wealth of experience in creating beautiful and functional websites. I have a strong foundation in web development and am well-versed in a variety of programming languages, including HTML, CSS, JavaScript, and PHP.
With over 3 years of experience in the industry, I have worked on a wide range of projects, from small startup websites to large enterprise-level applications. He has a proven track record of delivering high-quality results, on time and within budget.
I am comfortable working with various web development frameworks such as React, Angular, and Vue.js. He has strong knowledge of the latest web development trends and best practices, which he uses to create responsive and user-friendly websites.