In your admin side, you need to download data in excel format. You also want to import data in database from the excel file. Excel export and import is basic requirement in the admin panel.
Maatwebsite/Laravel-Excel is simple and easy to implement Laravel package. In this article, we will share you how you can export and import data in excel format. We will create Laravel application from the scratch and implement excel export and import.
We will go through step by step. So let's get started below step by step:
- Create fresh Laravel application
- Create database migration
- Install Maatwebsite/Laravel-Excel Package
- Create routes and controller class.
- Create excel export/import classes file.
- Create blade file
Step: 1. Create Laravel application
In the first step, we will create new Laravel application using bellow command, So open your Terminal or command prompt and run bellow command:
composer create-project laravel/laravel excel
Step 2. Database migration
In your .env file, set the database name and MySQL users data.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=excel
DB_USERNAME=root
DB_PASSWORD=root
Laravel default have migration for users table. So we will use users data for excel export and import. Run the migration command to run create the table in database.
php artisan migrate
Step: 3. Install Maatwebsite/Laravel-Excel Package
In the third step, we will install Maatwebsite/Laravel-Excel package using below command.
composer require maatwebsite/excel
Now open config/app.php file and register package class in $providers array.
'providers' => [
...
Maatwebsite\Excel\ExcelServiceProvider::class,
]
In the same file add the class in $aliases array.
'aliases' => [
...
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
]
Now publish the package config file. This will add config/excel.php file. You can change the settings for the excel from this file.
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Step: 4. Create routes and controller class.
Now we need to create routes of import export file. so open your routes/web.php file and add below routes.
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\ExcelController;
Route::get('excel/view', [ExcelController::class, 'index'])->name('index');
Route::get('excel/export', [ExcelController::class, 'export'])->name('export');
Route::post('excel/import', [ExcelController::class, 'import'])->name('import');
Now create the controller to handle the routes.
php artisan make:controller ExcelController
This will create controller file at app/Http/Controllers/ExcelController.php. Open the file add the methods as we have defined in route file.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Exports\UsersExport;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
class ExcelController extends Controller
{
/**
* @return \Illuminate\Support\Collection
*/
public function index()
{
return view('index');
}
/**
* @return \Illuminate\Support\Collection
*/
public function export()
{
return Excel::download(new UsersExport, 'users.xlsx');
}
/**
* @return \Illuminate\Support\Collection
*/
public function import()
{
Excel::import(new UsersImport,request()->file('excel'));
return back();
}
}
Step: 5. Create excel export/import classes file.
We have defined controller file. From the controller we will call excel class and make export and import. So we need to create export and import class.
Create import class using below command.
php artisan make:import UsersImport --model=User
Open app/Imports/UsersImport.php and add the below codes.
<?php
namespace App\Imports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\ToModel;
class UsersImport implements ToModel
{
/**
* @param array $data
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $data)
{
return new User([
'name' => $data[0],
'email' => $data[1],
'password' => \Hash::make($data[2]),
]);
}
}
Same way run command for export
php artisan make:export UsersExport --model=User
<?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();
}
}
Step: 6. Create blade file.
In this last step we will create view file where we will export and import excel file.
Create resources/views/index.blade.php file and add the HTML code.
<!DOCTYPE html>
<html>
<head>
<title>Excel Export/Import</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
</head>
<body>
<div class="container">
<div class="row mt-3">
<div class="col-12">
<form action="{{ route('import') }}" method="post" enctype="multipart/form-data">
@csrf
<div class="mb-3">
<label for="excel" class="form-label"></label>
<input type="file" name="excel" class="form-control">
</div>
<button class="btn btn-success">Import User Data</button>
<a class="btn btn-warning" href="{{ route('export') }}">Export User Data</a>
</form>
</div>
</div>
</div>
</body>
</html>
Now run the artisan command to start Laravel application.
php artisan serve
Open your browser and go to https://localhost:8000/excel/view You can simply upload the data and import.
Thanks for giving time in reading article. I hope you liked this article and will help you.
