import export to excel and csv using maatwebsite in laravel

By : PHPErrorCode October 11, 2017 464 Views laravel

Today I want you to Explain import Excel / CSV to store in to the database With which you also download Excel / CSV file in to database in laravel 4 and 5. For which we will be using maatwebsite/excel laravel 4-5 pakage then create and download xls, xlsx and csv file

Admins need to back up the data of a large website, in this example admin can every time importing and exporting in very large website(like e-commerce , ERP etc....) data in Xls, xlsx and csv formats.

In this simple example, you can easily importing and exporting data in Xls, xlsx and csv formats using laravel MetaWebsite / Excel package in your PHP laravel application. To him i am write here some basice step for using excel importing and exporting functionality in laravel 4 and 5 aplication.

Step 1: Install Package

copy and pest, below line to install maatwebsite/excel package

Laravel 5

composer require "maatwebsite/excel:~2.1.0"

Laravel 4

composer require "maatwebsite/excel:~1.3"

Step 2: Configuration

After installing package we are need to configure app.php file. Go to confige/app.php file and add some below line

providers

Maatwebsite\Excel\ExcelServiceProvider::class,

aliases

'Excel' => Maatwebsite\Excel\Facades\Excel::class,

Step 3: Publish the config settings

If you are using Laravel 5 then fire following command:

php artisan vendor:publish

If you are using Laravel 4 then fire following command:

php artisan config:publish maatwebsite/excel

Step 4: Creat Route

now we are create following Tree route

view file route

Route::get('Import_Export', 'ExcelController@index')->name('create.index');

Route for import excel data to mysql database

Route::post('Import_Export, 'ExcelController@importExcel')>name('importExcel');

Route for Download Excel file to .csv, .xls or .xlsx formats

Route::get('Download_Excel/{type}', 'ExcelController@downloadExcel')->name('downloadExcel');

Step 5: Create controller

Now Go to app/Http/Controllers/ folders in create ExcelController.php file look like.

<?php
namespace App\Http\Controllers;

use App\Http\Requests;
use Illuminate\Http\Request;

use Input;
use DB;
use Session;
use Excel;

use App\User;

class ExcelController extends Controller
{
    public function index()
    {
        return view('importExport');
    }

    public function importExcel(Request $request)
    {
        if($request->hasFile('import_file')){
            Excel::load($request->file('import_file')->getRealPath(), function ($reader) {
                foreach ($reader->toArray() as $key => $row) {
                    $data['name'] = $row['name'];
                    $data['email'] = $row['email'];
                    $data['mobile'] = $row['mobile'];

                    if(!empty($data)) {
                        DB::table('users')->insert($data);
                    }
                }
            });
        }

        Session::put('success', 'Youe file successfully import in database!!!');
        return back();
    }

    public function downloadExcel($type)
    {
        $user = User::select('users.*')->get();

        return Excel::create('PHPErrorCode', function($excel) use ($user) {
            $excel->sheet('mySheet', function($sheet) use ($user)
            {
                $sheet->fromArray($user);
            });
        })->download($type);
    }
}

Step :6 Create View file

create above controlle then after Go to resources/views then create importExport.blade.php file

<html lang="en">
<head>
	<title>import export to excel and csv using maatwebsite in laravel 5.5</title>
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" >
</head>
<body>
	<div class="container">
		<div class="well">
			<form action="{{ URL::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>
		<br>
		<div class="well">
			<a href="{{ URL::route('downloadExcel','xls') }}">
				<button class="btn btn-success">Download Excel xls</button>
			</a>
			<a href="{{ URL::route('downloadExcel','xlsx') }}">
				<button class="btn btn-success">Download Excel xlsx</button>
			</a>
			<a href="{{ URL::route('downloadExcel','csv') }}">
				<button class="btn btn-success">Download CSV</button>
			</a>
		</div>
	</div>
</body>
</html>

now we are ready to run this example code, and run bellow command in your commandpromt:

php artisan serve

Now you can open bellow URL on your browser:

http://localhost:8000/search

I hope it can help you....