Dev.to

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) {

Feb 23, 2025 - 12:18
 0
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:


 lang="en">

    
         charset="UTF-8">
         name="viewport" content="width=device-width, initial-scale=1.0">
         http-equiv="X-UA-Compatible" content="ie=edge">
        </span>Product Import <span class="err">&</span> Export<span class="nt">
        
    

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

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

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

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

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