This post is also available in: English

Impor ekspor excel atau CSV dari database adalah syarat utama untuk proyek admin. Dalam tutorial ini, saya akan menunjukkan cara mengimpor file CSV atau excel dan mengekspor file CSV atau excel menggunakan maatwebsite/excel di aplikasi laravel 9.

Laravel 9 Impor Ekspor File Excel & CSV ke Database Contoh

Sebaiknya, kita akan menggunakan plugin maatwebsite/excel composer untuk mengekspor dan mengimpor data, yang terpenting, untuk berinteraksi dengan database.

Saya akan berbagi pola kerja dengan Anda. Idealnya, bagaimana seharusnya Anda mengimpor-ekspor dan mengunduh file excel & CSV dari database menggunakan plugin maatwebsite/excel composer.

Laravel 9 Import Export Excel & CSV File Tutorial

Install Laravel

composer create-project laravel/laravel laravel-excel-csv --prefer-dist

cd laravel-excel-csv

Compose Database Connection

Tambahkan kode berikut di file .env.

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

Install Excel (maatwebsite) Pacakage

composer require maatwebsite/excel

Generate Fake Records, Migrate Table

php artisan migrate

Setelah migrasi selesai, jalankan perintah di bawah ini.

php artisan tinker
User::factory()->count(50)->create();
exit

Construct Route

Tentukan 3 route di route/web.php yang menangani impor dan ekspor untuk file Excel dan CSV.

<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\UserController;
/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/
Route::get('file-import-export', [UserController::class, 'fileImportExport']);
Route::post('file-import', [UserController::class, 'fileImport'])->name('file-import');
Route::get('file-export', [UserController::class, 'fileExport'])->name('file-export');

Buat Impor Class

php artisan make:import UsersImport --model=User

Tempatkan kode berikut di dalam file app/Imports/UsersImport.php.

<?php
namespace App\Imports;
use App\Models\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
class UsersImport implements ToModel
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        return new User([
            'name'     => $row[0],
            'email'    => $row[1],
            'password' => Hash::make($row[2])
        ]);
    }
}

Construct Export Class

php artisan make:export UsersExport --model=User

Berikut adalah kode terakhir yang terkonjugasi di app/Exports/UsersExport.php.

<?php
namespace App\Exports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;
class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

Create and Prepare Controller

php artisan make:controller UserController

Tempatkan kode berikut di file app/Http/Controllers/UserController.php.

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use App\Imports\UsersImport;
use App\Exports\UsersExport;
class UserController extends Controller
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function fileImportExport()
    {
       return view('file-import');
    }
   
    /**
    * @return \Illuminate\Support\Collection
    */
    public function fileImport(Request $request) 
    {
        Excel::import(new UsersImport, $request->file('file')->store('temp'));
        return back();
    }
    /**
    * @return \Illuminate\Support\Collection
    */
    public function fileExport() 
    {
        return Excel::download(new UsersExport, 'users-collection.xlsx');
    }    
}

Buat Blade View

Buat file resources/views/file-import.blade.php untuk mengatur tampilan. Tempatkan kode berikut di dalam file tampilan blade:

<!DOCTYPE html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Import Export Excel & CSV to Database in Laravel 7</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
</head>
<body>
    <div class="container mt-5 text-center">
        <h2 class="mb-4">
            Laravel 7 Import and Export CSV & Excel to Database Example
        </h2>
        <form action="{{ route('file-import') }}" method="POST" enctype="multipart/form-data">
            @csrf
            <div class="form-group mb-4" style="max-width: 500px; margin: 0 auto;">
                <div class="custom-file text-left">
                    <input type="file" name="file" class="custom-file-input" id="customFile">
                    <label class="custom-file-label" for="customFile">Choose file</label>
                </div>
            </div>
            <button class="btn btn-primary">Import data</button>
            <a class="btn btn-success" href="{{ route('file-export') }}">Export data</a>
        </form>
    </div>
</body>
</html>

Setelah mengikuti setiap langkah, masing-masing, dan secara berurutan, sekarang saatnya menjalankan aplikasi untuk menguji apa yang kami bangun sejauh ini.

php artisan serve

http://localhost:8000/file-import-export