Laravel6 - Excel and CSV import export using maatwebsite/excel
We will share with you in this article how to excel and CSV import-export using maatwebsite/excel laravel package. maatwebsite/excel is a one of the best laravel packages. you need to any time in your laravel application any data store excel or CSV from database and any database data download fron excel and csv formate. you can be done this task or functionality in your laravel application help of maatwebsite/excel package.
Excel / CSV import and export is very common functionality in any application. when you have large data and you want to insert it in database then it can help a lot. as well you want back up database data into the excel or CSV file. both of functionality you can be done with maatwebsite/excel laravel package
What is new maatwebsite v.3
Here are version 3.0 many changes you can see. if you used an old version of maatwebsite/excel
before then you can know all. ALL Laravel Excel 2.* methods are deprecated and will not be able to use in 3.0.
Excel::load()
is removed and replaced byExcel::import($yourImport)
Excel::create()
is removed and replaced byExcel::download/Excel::store($yourExport)
Excel::create()->string('xlsx')
is removed an replaced byExcel::raw($yourExport, Excel::XLSX)
- 3.0 provides no convenience methods for styling, you are encouraged to use PhpSpreadsheets native methods.
If you before never used or done Excel and CSV import-export functionality in laravel don't worry here we will share with you all step by step. just the following steps.
In this demo example e are create one transactions
table and we are export this table data into excel or CSV file and also import excel file data from the database. our table looks like this.
| id | name_on_card | card_no | exp_month | exp_year | cvv |
|----|--------------|---------|-----------|----------|-----|
| | | | | | |
| | | | | | |
| | | | | | |
Before implementing excel/CSV import-export functionality in laravel6 help of maatwebsite/excel package. check the following requirement.
Requirements
- PHP:
^7.0
- Laravel:
^5.5
- PhpSpreadsheet:
^1.6
- PHP extension
php_zip
enabled - PHP extension
php_xml
enabled - PHP extension
php_gd2
enabled
Preview
Step - 1 Package Installation
First, we need to install maatwebsite/excel
package in laravel application help of the following composer command.
composer require maatwebsite/excel
Step - 2 Package Configuration
The Maatwebsite\Excel\ExcelServiceProvider
is auto-discovered and registered by default.
If you want to register it yourself, add the ServiceProvider in config/app.php
:
'providers' => [
/*
* Package Service Providers...
*/
Maatwebsite\Excel\ExcelServiceProvider::class,
]
The Excel
facade is also auto-discovered.
If you want to add it manually, add the Facade in config/app.php
:
'aliases' => [
...
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
]
To publish the config, run the vendor publish command:
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"
This will create a new config file named config/excel.php
. you can also change the default setting in this file and set new one options on your own requirements.
Step - 3 Create Migration
Now, we need to create a migration for transactions
the table. run the following command in your terminal.
php artisan make:migration create_transactions_tbl
After running this command, then open that created file that will be created on database/migrations
a folder. just open it and put the following code into that migration file.
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class Transactions extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('transactions', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name_on_card');
$table->string('card_no');
$table->string('exp_month');
$table->string('exp_year');
$table->string('cvv');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('transactions');
}
}
and then run php artisan migrate
commands in your terminal. then your transactions
table will be created into your database, which you set into in your project .env
file.
Step - 4 Create Model
After, create trasactions
table, then you need to create a model. simple run the following command in your terminal
php artisan make:model Transaction
After, hit this command app/Transaction.php
file will be created automatically. open it and make the following changes into Transaction.php
file.
app/Transaction.php
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Transaction extends Model
{
protected $table = 'transactions';
protected $guarded = array();
}
Step - 5 Create Routes
After installing package and configure all the set up then the next things is make following three routes in routes/web.php
file.
// Route for view/blade file.
Route::get('importExportView', 'MaatwebsiteController@importExportView')->name('importExportView');
// Route for export/download tabledata to .csv, .xls or .xlsx
Route::get('exportExcel/{type}', 'MaatwebsiteController@exportExcel')->name('exportExcel');
// Route for import excel data to database.
Route::post('importExcel', 'MaatwebsiteController@importExcel')->name('importExcel');
Step - 6 Create Import Class
maatwebsite 3 version provides a way to built import class and we have to use it in the controller. So it would be a great way to create a new Import class. So you have to run following command and change following code on that file:
php artisan make:import TransactionsImport --model=Transaction
Excel demo screenshot for import
app/Imports/TransactionsImport.php
<?php
namespace App\Imports;
use App\Transaction;
use Maatwebsite\Excel\Concerns\ToModel;
class TransactionsImport implements ToModel
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new Transaction([
'name_on_card' => $row[0],
'card_no' => $row[1],
'exp_month' => $row[2],
'exp_year' => $row[3],
'cvv' => $row[4],
]);
}
}
Step - 7 Create Export Class
maatwebsite 3 versions provides a way to built export class and we have to use it in the controller. So it would be a great way to create a new Export class. So you have to run following command and change following code on that file:
php artisan make:export TransactionsExport --model=Transaction
app/Exports/TransactionsExport.php
<?php
namespace App\Exports;
use App\Transaction;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\FromCollection;
class TransactionsExport implements FromCollection, WithHeadings, WithMapping
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return Transaction::all();
}
public function headings(): array
{
return [
'Name On Card',
'Card No.',
'Exp Month',
'Exp. Year',
'CVV',
];
}
public function map($transaction): array
{
return [
$transaction->name_on_card,
'XXXXXXXXXXXX' . substr($transaction->card_no, -4, 4),
$transaction->exp_month,
$transaction->exp_year,
$transaction->cvv,
];
}
}
Step - 8 Create Controller
After, done above two routes then we need to create MaatwebsiteController.php
controller file help of following artisan command.
php artisan make:controller MaatwebsiteController
After running this command your MaatwebsiteController.php
file automatic created on app/Http/Controllers
the folder. just open it and write the following code into that file.
app/Http/Controllers/MaatwebsiteController.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Exports\TransactionsExport;
use App\Imports\TransactionsImport;
use Maatwebsite\Excel\Facades\Excel;
class MaatwebsiteController extends Controller
{
/**
* @return \Illuminate\Support\Collection
*/
public function importExportView()
{
return view('maatwebsiteexcel');
}
/**
* @return \Illuminate\Support\Collection
*/
public function exportExcel($type)
{
return Excel::download(new TransactionsExport, 'transactions.'.$type);
}
/**
* @return \Illuminate\Support\Collection
*/
public function importExcel(Request $request)
{
Excel::import(new TransactionsImport,$request->import_file);
return back();
}
}
Step - 9 Create Blade File
After done the controller file then we create one laravel blade HTML file that simple design. Now we are creating one simple blade file in resources/views/maatwebsiteexcel.blade.php
file and here we are making a very simple HTML layout for import and export excel or CSV.
resources/views/maatwebsiteexcel.blade.php
@extends('layouts.app')
@section('content')
<div class="container">
@if($message = Session::get('success'))
<div class="alert alert-info alert-dismissible fade in" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Success!</strong> {{ $message }}
</div>
@endif
{!! Session::forget('success') !!}
<br />
<a href="{{ URL::to('exportExcel/xls') }}"><button class="btn btn-success">Download Excel xls</button></a>
<a href="{{ URL::to('exportExcel/xlsx') }}"><button class="btn btn-success">Download Excel xlsx</button></a>
<a href="{{ URL::to('exportExcel/csv') }}"><button class="btn btn-success">Download CSV</button></a>
<form style="border: 4px solid #a1a1a1;margin-top: 15px;padding: 10px;" action="{{ route('importExcel') }}" class="form-horizontal" method="post" enctype="multipart/form-data">
{{ csrf_field() }}
<input type="file" name="import_file" />
<button class="btn btn-primary">Import File</button>
</form>
</div>
@endsection
Conclusion
As you can see, Excel import-export is very easy to use in laravel application help of maatwebsite/excel
package.
We hope these tutorials help everyone. if you have any issues or questions reagarding excel import export so please comment below. Thanks..
Copyright 2023 HackTheStuff