In this example we will discuss about import excel file In laravel framework PHP.
Follow the below step to import :
- Download the dependecy using composer for Import and export excel file.
composer require maatwebsite/excel
You may visit //packagist.org/packages/maatwebsite/excel for more details.
- Add providers and aliases in config/app.php
'providers' => [ /* * Laravel Framework Service Providers... */ ......, ......, Maatwebsite\Excel\ExcelServiceProvider::class, ] 'aliases' => [ ......., -------, 'Excel' => Maatwebsite\Excel\Facades\Excel::class, ]
- Now publish the changes using vendor:publish.
php artisan vendor:publish
- Make migration and migrate it using command.
- Add routes
/*Excel import export*/ Route::get('export', 'ImportExportController@export')->name('export'); Route::get('importExportView', 'ImportExportController@importExportView'); Route::post('import', 'ImportExportController@import')->name('import');
- Create Import and Export class using import/export command
Note: This command avaialble only if you download dependecy successfully using composer(step 1)
php artisan make:export BulkExport --model=Bulk
- In
app/Emports/BulkExport.php file
namespace App\Exports; use App\Bulk; use Maatwebsite\Excel\Concerns\FromQuery; use Maatwebsite\Excel\Concerns\WithHeadings; class BulkExport implements FromQuery,WithHeadings { /** * @return \Illuminate\Support\Collection */ // use Exportable; public function headings(): array { return [ 'Id', 'name', 'email', 'createdAt', 'updatedAt', ]; } public function query() { return Bulk::query(); /*you can use condition in query to get required result return Bulk::query()->whereRaw('id > 5');*/ } public function map($bulk): array { return [ $bulk->id, $bulk->name, $bulk->email, Date::dateTimeToExcel($bulk->created_at), Date::dateTimeToExcel($bulk->updated_at), ]; } }
- In Bulk.php Model
<?php namespace App; use Illuminate\Database\Eloquent\Model; class Bulk extends Model { protected $table = 'bulk'; protected $fillable = [ 'name', 'email', ]; }
- create ImportExportController
php artisan make:controller ImportExportController
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\Exports\BulkExport; use App\Imports\BulkImport; use Maatwebsite\Excel\Facades\Excel; class ImportExportController extends Controller { /** * */ public function importExportView() { return view('importexport'); } public function export() { return Excel::download(new BulkExport, 'bulkData.xlsx'); } }
- Create view file importexport.blade.php
<!DOCTYPE html> <html> <head> <title>Import Export Example</title> <link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" /> </head> <body> <div class="container"> <div class="card bg-light mt-3"> <div class="card-header"> Import Export Example </div> <div class="card-body"> <form action="{{ route('import') }}" method="POST" enctype="multipart/form-data"> {{ csrf_field() }} <input type="file" name="file" class="form-control"> <br> <button class="btn btn-success">Import Bulk Data</button> <a class="btn btn-warning" href="{{ route('export') }}">Export Bulk Data</a> </form> </div> </div> </div> </body> </html>
Exporting and importing Excel, CSV and OpenOffice
stylesheets using Eloquent Collections and Query Builders in Laravel (5.* and 4.*). Author: Simone Todaro Installation Use composer to download the package: composer require cyber-duck/laravel-excel
Register the service provider in config/app.php by adding this line to providers array. 'providers' => [
Cyberduck\LaravelExcel\ExcelLegacyServiceProvider::class,
], Register the service provider in config/app.php by adding this line to providers array. 'providers' => [
Cyberduck\LaravelExcel\ExcelServiceProvider::class,
], No need to register anything, since it used package auto discovery feature in Laravel 5.5. Add to your controller.Laravel Excel
It's based on box/spout.
Contributors: Clément Blanco
Made with ❤️ by
Cyber-Duck Ltd
Export Excel
Import Excel
Different formatsInstallation
Laravel 4.x
Laravel < 5.5
Laravel > 5.5
Export Excel
Generate and download an excel file
In your controler function, create a new excel file from an Eloquent collection.
$excel = Exporter::make('Excel'); $excel->load($yourCollection); return $excel->stream($yourFileName);
The exporter class is fluent, so you can also write
return Exporter::make('Excel')->load($yourCollection)->stream($yourFileName);
The exporter class supports Query builder objects as well
$query = DB:table('table')->select('col1','col2'); $excel = Exporter::make('Excel'); $excel->loadQuery($query); return $excel->stream($yourFileName);
If you deal with big tables, you can set the chunk size to minimise the memory usage
$query = DB:table('table')->select('col1','col2'); $excel = Exporter::make('Excel'); $excel->loadQuery($query); $excel->setChunk(1000); return $excel->stream($yourFileName);
Generate and save an excel file
To save the excel file on the server, use the save method.
return $excel->save($yourFileNameWithPath);
Advanced usage
By default, every element of the Collection becomes a row and every unprotected field of the Model becomes a cell.
No headers row is printed.
To change this behaviour, create a class extending Cyberduck\LaravelExcel\Contract\SerialiserInterface, implement the methods getHeaderRow() and getData(Model $data) and set this class on the excel object usint setSerialiser().
$serialiser = new CustomSerialiser(); $excel = Exporter::make('Excel'); $excel->load($collection); $excel->setSerialiser($serialiser); return $excel->stream($yourFileName);
getHeaderRow() must return an array of string where every element is a cell of the first row. To not print the header row, simply return a void array [].
getData(Model $data) must return an array of string, and every elements is a
cell.
Example
namespace App\Serialisers; use Illuminate\Database\Eloquent\Model; use Cyberduck\LaravelExcel\Contract\SerialiserInterface; class ExampleSerialiser implements SerialiserInterface { public function getData($data) { $row = []; $row[] = $data->field1; $row[] = $data->relationship->field2; return $row; } public function getHeaderRow() { return [ 'Field 1', 'Field 2 (from a relationship)' ]; } }
then set the serialiser before saving the file the collection.
$collection = Exporter::make('Excel')->load($yourCollection)->setSerialiser(new ExampleSerialiser)->stream($yourFileName);
Import Excel
Add
to your controller.
In your controler function, import an excel file.
$excel = Importer::make('Excel'); $excel->load($filepath); $collection = $excel->getCollection(); //dd($collection)
The importer class is fluent, then you can also write
return Importer::make('Excel')->load($filepath)->getCollection();
Advanced usage
By default, every row of the first sheet of the excel file becomes an array and the final result is wraped in a Collection (Illuminate\Support\Collection).
To import a different sheet, use setSheet($sheet)
$excel = Importer::make('Excel'); $excel->load($filepath); $excel->setSheet($sheetNumber); $collection = $excel->getCollection(); //dd($collection)
To import each row in an Eloquent model, create a class extending Cyberduck\LaravelExcel\Contract\ParserInterface and implement the methods transform($row).
Example
namespace App\Parsers; use App\Models\YourModel; use Cyberduck\LaravelExcel\Contract\ParserInterface; class ExampleParser implements ParserInterface { public function transform($row, $header) { $model = new YourModel(); $model->field1 = $row[0]; $model->field2 = $row[1]; // We can manunipulate the data before returning the object $model->field3 = new \Carbon($row[2]); return $model; } }
then set the parser before creating the collection.
$collection = Importer::make('Excel')->load($filepath)->setParser(new ExampleParser)->getCollection();
Different formats
The package supports ODS and CSV files.
ODS
$exporter = Exporter::make('OpenOffice'); $importer = Importer::make('OpenOffice');
CSV
$exporter = Exporter::make('Csv'); $importer = Importer::make('Csv');