How to Use Maatwebsite Excel in Laravel for Importing & Exporting Data

Hello Artisan,

This blog post will show how to implement import and export functionality in laravel applications using Maatwebsite Excel package.
So let's get started with an introduction.

Introduction

In Laravel, handling Excel files efficiently is crucial for data management tasks such as importing bulk records, generating reports, and exporting data. The Maatwebsite Excel package simplifies working with Excel files in Laravel applications. In this guide, we'll explore how to use this package to import and export data in Excel, with a small product-management application. Where we will have a list of products and each product belongs to a category.

First, we will set up our laravel project, and add products and categories, with the help of a seeder which gives us some test data.

Step 1: Install and setup laravel project

laravel new product-management

During installation, you will be prompted with questions to set up your application. You can choose the options that best suit your preferences.
I have selected no blade file, pest for testing, and MySQL database.

Step 2: Create model, migration, factory, and seeder for Category

php artisan make:model Category -msf

Step 3: Create a model, migration, factory, seeder, and Controller for the Product.

php artisan make:model Product -mcsf

Step 4: Next step is to define migration and the relationship between Category and Products.

• Open a migration file for Category and add this code

Schema::create('categories', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->text('description')->nullable();
            $table->timestamps();
        });

• Open a migration file for Product and add the below code

 Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->text('description')->nullable();
            $table->decimal('price', 10, 2);
            $table->unsignedBigInteger('category_id');
            $table->foreign('category_id')->references('id')->on('categories')->onDelete('cascade');
            $table->timestamps();
        });

Now open a model files and add the following code:

Product.php

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = ['name', 'description', 'price', 'category_id'];

    /**
     * Get list of categories.
     *
     * @return BelongsTo
     */
    public function category(): BelongsTo
    {
        return $this->belongsTo(Category::class);
    }

Category.php

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = ['name', 'description'];

    /**
     * Get list of products.
     *
     * @return HasMany
     */
    public function products(): HasMany
    {
        return $this->hasMany(Product::class);
    }

Step 5: Now we will create and add test data with the help of the seeder.

• CategoryFactory.php

   return [
            'name' => $this->faker->word,
            'description' => $this->faker->sentence,
        ];

• ProductFactory.php

return [
            'name' => $this->faker->word,
            'description' => $this->faker->sentence,
            'price' => $this->faker->randomFloat(2, 5, 100),
            'category_id' => Category::factory(),
        ];

• CategorySeeder.php

Category::factory(5)->create(); // Creates 5 random categories

• ProductSeeder.php

Category::all()->each(function ($category) {
            Product::factory(5)->create(['category_id' => $category->id]); // Each category gets 5 products
        });

Now add these seeder files to DatabaseSeeder.php to generate test data in our database within the run method.

 $this->call([
            CategorySeeder::class,
            ProductSeeder::class,
        ]);

Step 6: Now run the migration command

php artisan migrate --seed

Our basic and required setup is done, now we will start with how to use Maatwebsite

Step 7: Install Maatwebsite Excel Package

To install the package, run the following command:

composer require maatwebsite/excel

After installation, the package automatically registers the service provider and facade. Now we need to publish its configuration file.

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

Step 8: Creating an Import Class

php artisan make:import ProductsImport --model=Product

This will create app/Imports/ProductsImport.php. Modify it as follows:

namespace App\Imports;

use App\Models\Product;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class ProductsImport implements ToModel, WithHeadingRow
{
    /**
     * @param array $row
     *
     * @return \Illuminate\Database\Eloquent\Product|null
     */
    public function model(array $row)
    {
        // Find category by id
        $category = \App\Models\Category::where('id', $row['category_id'])->first();

        if (!$category) {
            throw new \Exception('Invalid category: ' . $row['category_id']);
        }

        return new Product([
            'name'  => $row['name'],
            'description' => $row['description'],
            'price' => is_numeric($row['price']) ? floatval($row['price']) : 0,
            'category_id' => $category->id,
        ]);
    }
}

Step 9: Creating an Export Class

To export data as an Excel file, create an export class:

php artisan make:export ProductsExport --model=Product

Modify app/Exports/ProductsExport.php:

namespace App\Exports;

use App\Models\Product;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithEvents;

class ProductsExport implements FromCollection, WithHeadings, WithEvents
{
    /**
     * @return \Illuminate\Support\Collection
     */
    public function collection()
    {
        $products = collect();

        // Retrieve all products and include the related category name using eager loading
        // Chunk the results to avoid memory issues as the dataset grows
        Product::query()->with('category')->chunk(100, function ($chunk) use ($products) {
            $chunk->each(function ($product) use ($products) {
                $products->push([
                    'ID' => $product->id,
                    'Name' => $product->name,
                    'Description' => $product->description,
                    'Price' => $product->price,
                    'Category' => $product->category->name,
                    'Created At' => $product->created_at->format('Y-m-d H:i:s'),
                    'Updated At' => $product->updated_at->format('Y-m-d H:i:s'),
                ]);
            });
        });

        return $products;
    }

    /**
     * Add custom headers
     *
     * @return array
     */
    public function headings(): array
    {
        return [
            'ID',
            'Name',
            'Description',
            'Price',
            'Category',
            'Created At',
            'Updated At',
        ];
    }

    /**
     * Apply styles to the headings
     *
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            \Maatwebsite\Excel\Events\AfterSheet::class => function (\Maatwebsite\Excel\Events\AfterSheet $event) {
                $headingCount = count($this->headings());
                $columnRange = 'A1:' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($headingCount) . '1';
                $event->sheet->getDelegate()->getStyle($columnRange)->getFont()->setBold(true);
            },
        ];
    }
}

Step 10: Add export and import methods in a controller

class ProductController extends Controller
{
    public function index()
    {
        $products = Product::all();
        return view('products.index', compact('products'));
    }

    /**
     * Export products to excel file
     *
     * @return \Illuminate\Http\Response
     */
    public function export()
    {
        return Excel::download(new ProductsExport, 'products.xlsx');
    }

    /**
     * Import products from excel file
     *
     * @param Request $request
     * @return \Illuminate\Http\RedirectResponse
     */
    public function import(Request $request)
    {
        $request->validate(['file' => 'required|mimes:xlsx,csv']);

        Excel::import(new ProductsImport, $request->file('file'));

        return back()->with('success', 'Products Imported Successfully!');
    }
}

Step 11: Define Routes

In routes/web.php, add routes for importing and exporting:

Route::get('/products', [ProductController::class, 'index'])->name('products.index');
Route::post('/products/import-users', [ProductController::class, 'import'])->name('products.import');
Route::get('/products/export-products', [ProductController::class, 'export'])->name('products.export');

Step 12: Create a simple blade form
In your Blade file (resources/views/products/index.blade.php), add:

<!DOCTYPE html>
<html lang="en">

    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta http-equiv="X-UA-Compatible" content="ie=edge">
        <title>Product Import & Export</title>
        <script src="https://cdn.tailwindcss.com"></script>
    </head>

    <body class="bg-gray-100 flex items-center justify-center h-screen">

        <div class="bg-white p-8 rounded-lg shadow-md w-96">
            <h2 class="text-xl font-semibold text-gray-700 text-center mb-4">Import & Export Products</h2>

            <!-- Import Form -->
            <form action="{{ route('products.import') }}" method="POST" enctype="multipart/form-data" class="space-y-4">
                @csrf
                <label class="block">
                    <span class="text-gray-700">Upload File</span>
                    <input type="file" name="file" required
                        class="mt-2 block w-full px-3 py-2 border rounded-lg text-gray-700 bg-gray-50">
                </label>

                <button type="submit"
                    class="w-full bg-blue-600 text-white py-2 rounded-lg hover:bg-blue-700 transition">
                    Import Products
                </button>
            </form>

            <!-- Export Button -->
            <div class="mt-4 text-center">
                <a href="{{ route('products.export') }}" class="text-blue-600 hover:underline">Export Products</a>
            </div>
        </div>

    </body>

</html>

Conclusion

Using Maatwebsite Excel in Laravel simplifies Excel imports and exports. Whether you're handling large datasets or need quick CSV/Excel downloads, this package is an essential tool for Laravel developers. Try it out in your Laravel projects and streamline your data operations!

You can view the code on GitHub.

Happy Reading
Happy Coding
🦄 ❤️