Laravel Excel import-export
https://ift.tt/2RbEUAi
Importing data from Excel sheet into our database and export data from the application into an Excel sheet are most often asked feature for a web application. These features enable us to perform batch import/export data by our web application. In Laravel, we can make excel import/export features easily with laravel maatwebsite/excel package. If you are looking for a step by step tutorial on how you can make excel import/export features in your application then it’s for you. In this post, I’ll show you a step by step guide for making excel import/export features in Laravel by using maatwebsite/excel laravel package with validation.
Steps for Laravel Excel Import-Export
- Install a new Laravel Framework instance
- Setup the database config & model.
- Install Laravel maatwebsite/excel package
- Make an Import class for import data.
- Make an Export class for export data
- Make a controller for handle excel data import or export
Install a new Laravel Framework instance
The first step, we need a fresh Laravel Framework instance. If you have already an application installed then skip this step. Let’s do that by composer command.
composer create-project laravel/laravel laravel-import-export
Setup the database config & model
In this step, we’ll set up our database configuration in the .env file.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel-import-export
DB_USERNAME=root
DB_PASSWORD=
Now make a model. Here we’ll make a contact model for our contacts table. Our contacts table contains id, name, phone, email fields.
<?php namespace App;
use Illuminate\Database\Eloquent\Model;
class Contact extends Model {
public $timestamps = false;
protected $fillable = ['name', 'phone', 'email'];
}
Install Laravel maatwebsite/excel package
Now install the Laravel maatwebsite/excel version 3.1 package via composer command.
composer require maatwebsite/excelThis package has auto-discovery features so you don’t need to add the service provider manually.
Make an Import class for import data.
Now we have to make an Import class for importing data into our database from Excel sheet via our contact model. The maatwebsite/excel package provides useful command to make the class easily. Let’s do that.
php artisan make:import ContactsImport --model=Contactwith this command, a Contact import class will create in the app/Imports directory.
<?php
namespace App\Imports;
use App\Contact;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
class ContactsImport implements ToModel
{
/**
* @param array $row
*
* @return Contact|null
*/
public function model(array $row)
{
return new Contact([
'name' => $row[0],
'phone' => $row[1],
'email' => $row[2]
]);
}
}
Make an Export class for export data
For export data from our database to Excel sheet, we need to make an Export class via command line.
php artisan make:export ContactsExport --model=ContactBy this command, a class file will create in app/Exports directory.
<?php
namespace App\Exports;
use App\Contact;
use Maatwebsite\Excel\Concerns\FromCollection;
class ContactsExport implements FromCollection
{
public function collection()
{
return Contact::all();
}
}
Make a controller for handle excel data import or export
Now the final part where we’ll handle how we’ll export or import data and what will be the logic goes to. Let’s make a controller by artisan make command.
php artisan make:controller ContactController
Handle Excel data import
To import data from excel sheet, make an import method inside the contact controller. In this import method, we’ll validate our request file and code for import the excel sheet data.
<?php
use App\Imports\ContactsImport;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Controllers\Controller;
class ContactsController extends Controller
{
public function import(Request $request)
{
$this->validate($request, [
'file' => 'required|file|mimes:xls,xlsx'
]);
$file = $request->file('file');
Excel::import(new ContactsImport, $file);
return redirect()->back()->with('success', 'All data successfully imported!');
}
}
Handle Excel data export
With the data export feature, we can export our table data into an excel sheet very easily. For that, make an export method inside the contact controller and do code for model data export into an excel sheet.
<?php
namespace App\Http\Controllers;
use App\Exports\ContactsExport;
use Maatwebsite\Excel\Facades\Excel;
class ContactController extends Controller
{
public function export()
{
return Excel::download(new ContactsExport, 'contacts.xlsx');
}
}
Hopefully, this step by step tutorial post will help you to add excel data import/export features in your laravel application. If this post helpful to you then please share with others so that they get helped.
programming
via Laravel News Links https://ift.tt/2dvygAJ
April 3, 2020 at 08:00AM
Recently I was looking for some simple way to analyze and create graphs from data stored in MySQL tables. I know Grafana can also plot graphs from MySQL tables, but somehow it felt like overkill and you are not using the traditional SQL syntax there. So I was searching for another solution, and it actually took me a while to find it. I might have used the wrong keywords when searching, or the SEO is not the best for this site/project, but in the end, I found 
