Laravel 6.16 Import Export Excel to database Example

Laravel 6.16 Import & Export Excel to Database Example

In Laravel 6.16, you can easily import and export Excel files using the Maatwebsite Excel package. Below is a step-by-step guide on how to implement it.

1. Install Maatwebsite Excel Package

Run the following command to install the package:

composer require maatwebsite/excel

After installation, add the service provider and alias in config/app.php:

'providers' => [
    Maatwebsite\Excel\ExcelServiceProvider::class,
],

'aliases' => [
    'Excel' => Maatwebsite\Excel\Facades\Excel::class,
],

Then, publish the config file:

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

2. Configure Database and Model

Ensure your .env file is configured with database credentials:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your_database
DB_USERNAME=root
DB_PASSWORD=

Create a model and migration for the products table:

php artisan make:model Product -m

Modify database/migrations/xxxx_xx_xx_create_products_table.php:

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

Run migration:

php artisan migrate

3. Create Import & Export Classes

Create an Import Class

php artisan make:import ProductsImport --model=Product

Modify app/Imports/ProductsImport.php:

namespace App\Imports;

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

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

Create an Export Class

php artisan make:export ProductsExport --model=Product

Modify app/Exports/ProductsExport.php:

namespace App\Exports;

use App\Product;
use Maatwebsite\Excel\Concerns\FromCollection;

class ProductsExport implements FromCollection
{
    public function collection()
    {
        return Product::all();
    }
}

4. Create a Controller

php artisan make:controller ProductController

Modify app/Http/Controllers/ProductController.php:

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Imports\ProductsImport;
use App\Exports\ProductsExport;
use Maatwebsite\Excel\Facades\Excel;

class ProductController extends Controller
{
    public function importView()
    {
        return view('import');
    }

    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.');
    }

    public function export()
    {
        return Excel::download(new ProductsExport, 'products.xlsx');
    }
}

5. Create Blade View for Import

Create resources/views/import.blade.php:

<!DOCTYPE html>
<html>
<head>
    <title>Import & Export Excel</title>
</head>
<body>
    <h2>Import Products</h2>
    @if(session('success'))
        <p style="color: green;">{{ session('success') }}</p>
    @endif
    <form action="{{ route('import') }}" method="POST" enctype="multipart/form-data">
        @csrf
        <input type="file" name="file" required>
        <button type="submit">Import</button>
    </form>

    <h2>Export Products</h2>
    <a href="{{ route('export') }}">Download Excel</a>
</body>
</html>

6. Define Routes

Modify routes/web.php:

use App\Http\Controllers\ProductController;

Route::get('/import', [ProductController::class, 'importView']);
Route::post('/import', [ProductController::class, 'import'])->name('import');
Route::get('/export', [ProductController::class, 'export'])->name('export');

7. Run the Application

Start the Laravel server:

php artisan serve

Go to:
http://127.0.0.1:8000/import
Upload an Excel file with name, price, and description columns.

Click Download Excel to export data.

Conclusion

✅ Import and export Excel in Laravel 6.16 using Maatwebsite Excel.
✅ Eloquent ORM makes data handling easy.
✅ Validation & Security ensures smooth operation.

Souy Soeng