How to Import and Export Excel and CSV Files in Laravel 9

Laravel 9 is a popular PHP framework that makes it easy to build robust and scalable web applications. One of the important features of any web application is the ability to import and export data in different formats. This can be particularly useful when you need to transfer data from one system to another, or when you need to store data for later use. In this tutorial, we’ll look at how to import and export Excel and CSV files in Laravel 9 using the Maatwebsite\Excel package.

The Maatwebsite\Excel package is a popular package that makes it easy to work with Excel and CSV files in Laravel. It provides a simple API that you can use to import and export data with just a few lines of code. This package also supports multiple file formats, so you can choose the format that works best for your needs.

Step 1: Install the Package

To get started, you need to install the Maatwebsite\Excel package. You can install it using composer:

composer require maatwebsite/excel

Step 2: Create a Model

In this step, you need to create a model that represents the data you want to import and export. For example, if you want to import and export products, you can create a Product model.

php artisan make:model Product

Step 3: Create a Migration

Next, you need to create a migration that creates the products table in your database. You can use the following command to create a migration:

php artisan make:migration create_products_table

If you would like to generate a database migration when you generate the model, you may use the --migration or -m option in step 2

Then, open the created migration file and add the following code:

public function up()
{
    Schema::create('products', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->text('description');
        $table->double('price');
        $table->timestamps();
    });
}

Finally, run the following command to apply the migration:

php artisan migrate

Step 4: Create a Controller

Now, you need to create a controller that will handle the import and export operations. You can create a controller using the following command:

php artisan make:controller ProductController

Step 5: Import Data

In this step, you’ll add the code that imports the data from the Excel or CSV file into the products table in your database. Open the ProductController file and add the following code:

//...
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use App\Models\Product;

class ProductController extends Controller
{
    public function index()
    {
        return view('import');
    }
    //...
    public function import(Request $request)
    {
        $file = $request->file('file');
        Excel::import(new ProductsImport, $file);
        return back()->with('success', 'Products imported successfully.');
    }
}

In this code, you first retrieve the uploaded file from the request object. Then, you import data to ProductsImport using the import method of the Excel façade. This class extends the Maatwebsite\Excel\Concerns\ToModel interface, which provides the necessary methods for importing data from an Excel or CSV file into a database table.

php artisan make:import ProductsImport --model=Product

This will create a new file named ProductsImport.php in the app/Imports directory. The --model=Product option specifies the name of the model class that will be used to store the imported data.

use Maatwebsite\Excel\Concerns\ToModel;
use Illuminate\Support\Facades\Hash;
use App\Models\Product;

class ProductsImport implements ToModel
{
    public function model(array $row)
    {
        return new Product([
            'name' => $row[0],
            'description' => $row[1],
            'price' => $row[2],
        ]);
    }
}

This code implements the ProductsImport class and implements the model method, which takes an array of data for each row as an argument and returns a new Product model instance populated with the data from that row.

Step 6: Export Data

In this step, you’ll add the code that exports the data from the products table in your database to an Excel or CSV file. Open the ProductController file and add the following code:

//....
class ProductController extends Controller
{
    //...
    public function export()
    {
        return Excel::download(new ProductsExport, 'products.xlsx');
    }
}

In this code, you use the download method of the Excel facade to download the data from the ProductsExport class. The ProductsExport class is responsible for preparing the data that needs to be exported. To create this class, you can use the following command:

php artisan make:export ProductsExport --model=Product

This command will create a new export class in the app/Exports directory. Open this file and add the following code:

use Maatwebsite\Excel\Concerns\FromCollection;
use App\Product;
class ProductsExport implements FromCollection
{
    public function collection()
    {
        return Product::all();
    }
}

In this code, you implement the FromCollection interface, which requires you to implement the collection method. In this method, you return the collection of all products using the all method of the Product model.

Step 7: Add Routes

Finally, you need to add routes for the import and export operations. Open the routes/web.php file and add the following code:

Route::get('/import', [ProductController::class,"index"]);
Route::post('/import', [ProductController::class,"import"]);
Route::get('/export', [ProductController::class,"export"]);

In this code, you add two routes for the import operation, one for the GET request and one for the POST request. The GET request is used to display the form for uploading the file, and the POST request is used to handle the file upload and import the data. You also add a route for the export operation, which returns the Excel or CSV file with the data.

Step 8: Create the View

Finally, you need to create a view that will have the form to import the file and a button to export the data.

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

<a href="{{ url('/export') }}">Export</a>

That’s it! You now have a complete Laravel 9 application that can import and export Excel and CSV files. You can customize this code as per your requirements. You can add validation to the imported data, and you can also add a confirmation message before exporting the data.

Conclusion

In this tutorial, you learned how to import and export Excel and CSV files in Laravel 9 using the Maatwebsite\Excel package. This package provides a simple and easy-to-use API that makes it possible to import and export data with just a few lines of code. You can use this package to handle data transfer between different systems or to store data for later use. With the help of this package, you can simplify your data import and export process and improve the efficiency of your web application.I hope this tutorial helps you in your Laravel projects.Happy coding!