Import Excel File in Laravel

PHPZAG Team Laravel, Tutorials

In our previous tutorial, you have learned how to export data to excel file using Laravel. In this tutorial, we will explain how to import excel file data using Laravel.

While developing web applications using Laravel, we sometimes need to implement excel spreadsheet file data import to insert bulk data into MySQL database and display.

We will use Maatwebsite package to implement excel data import. We will cover this tutorial step by step to install Maatwebsite package and implement excel file data import to the MySQL database using Laravel.

Also, read:

So let’s proceed with coding to implement excel file data import. We will have following file structure for this project.


  • import_excel_laravel
    • config
      • database.php
      • app.php
    • app
      • Http
        • Controllers
          • ImportExcel.php
    • Resources
      • views
        • import_excel.blade.php
    • Routes
      • web.php

Step1: Create Laravel Project

First we will create project import_excel_laravel by running following command with composure.

composer create-project — prefer-dist laravel/laravel import_excel_laravel

Step2: Create MySQL Table

We will create MySQL database table customer to insert the data from excel file and display it.

CREATE TABLE `customer` (
  `id` int(11) NOT NULL,
  `name` varchar(250) NOT NULL,
  `gender` varchar(30) NOT NULL,
  `City` varchar(250) NOT NULL,
  `Country` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ALTER TABLE `customer`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `customer`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

Step3: Make Database Connection

After MySQL database table create, we will make changes into config/database.php file to define database connection configuration. Here we have defined database as laravel and user as root with blank password.

...............
'mysql' => [
	'driver' => 'mysql',
	'url' => env('DATABASE_URL'),
	'host' => env('DB_HOST', '127.0.0.1'),
	'port' => env('DB_PORT', '3306'),
	'database' => env('DB_DATABASE', 'laravel'),
	'username' => env('DB_USERNAME', 'root'),
	'password' => env('DB_PASSWORD', ''),
	'unix_socket' => env('DB_SOCKET', ''),
	'charset' => 'utf8mb4',
	'collation' => 'utf8mb4_unicode_ci',
	'prefix' => '',
	'prefix_indexes' => true,
	'strict' => true,
	'engine' => null,
	'options' => extension_loaded('pdo_mysql') ? array_filter([
		PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
	]) : [],
],
...............

Step4: Use Maatwebsite Package

We need to install Maatwebsite package to import excel file data using Laravel. We will use following command to install this package.

composer require maatwebsite/excel

We will also need to make config/app.php file to register this package to our Laravel application. We will have to define providers and aliases.


<?php

return [

........

    'providers' => [

.......

        Maatwebsite\Excel\ExcelServiceProvider::class,

    ],

    'aliases' => [

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

    ],

];

Step5: Implement Controllers File

We will create controller file ImportExcel.php to handle http request to import data. In this file, we will handle excel file data import with Maatwebsite package and then handle to insert excel data to the MySQL database table.

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
use Excel;
class ImportExcel extends Controller {
    function index(){
     $customerData = DB::table('customer')->orderBy('id', 'DESC')->get();
     return view('import_excel', compact('customerData'));
    }
    function import(Request $request) {
     $this->validate($request, [
      'select_file'  => 'required|mimes:xls,xlsx'
     ]);
     $filePath = $request->file('select_file')->getRealPath();
     $customerData = Excel::load($filePath)->get();
     if($customerData->count() > 0) {
      foreach($customerData->toArray() as $key => $value) {
       foreach($value as $row){
        $insertData[] = array(
         'name'  => $row['name'],
         'gender'   => $row['gender'],         
         'city'    => $row['city'],        
         'country'   => $row['country']
        );
       }
      }
      if(!empty($insertData)){
       DB::table('customer')->insert($insertData);
      }
     }
     return back()->with('success', 'Excel Data Imported successfully.');
    }
}

Step6: Implement View File

We will create import_excel.blade.php file to implement file upload with POST form and display the imported data on page. 

<!DOCTYPE html>
<html>
 <head>
  <title>phpzag.com : Demo Import Excel File in Laravel</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
 </head>
 <body>
	<div role="navigation" class="navbar navbar-default navbar-static-top">
      <div class="container">
        <div class="navbar-header">
          <button data-target=".navbar-collapse" data-toggle="collapse" class="navbar-toggle" type="button">
            <span class="sr-only">Toggle navigation</span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
          </button>
          <a href="http://www.phpzag.com" class="navbar-brand">PHPZAG.COM</a>
        </div>
        <div class="navbar-collapse collapse">
          <ul class="nav navbar-nav">
            <li class="active"><a href="http://www.phpzag.com">Home</a></li>           
          </ul>         
        </div>
      </div>
    </div>
  <br />  
  <div class="container">
   <h2>Example: Import Excel File in Laravel</h2>
    <br />
   @if(count($errors) > 0)
    <div class="alert alert-danger">
     Upload Validation Error<br><br>
     <ul>
      @foreach($errors->all() as $error)
      <li>{{ $error }}</li>
      @endforeach
     </ul>
    </div>
   @endif
   @if($message = Session::get('success'))
   <div class="alert alert-success alert-block">
    <button type="button" class="close" data-dismiss="alert">×</button>
           <strong>{{ $message }}</strong>
   </div>
   @endif
   <form method="post" enctype="multipart/form-data" action="{{ url('/import_excel/import') }}">
    {{ csrf_field() }}
    <div class="form-group">
     <table class="table">
      <tr>
       <td width="40%" align="right"><label>Select File for Upload</label></td>
       <td width="30">
        <input type="file" name="select_file" />
       </td>
       <td width="30%" align="left">
        <input type="submit" name="upload" class="btn btn-primary" value="Upload">
       </td>
      </tr>
      <tr>
       <td width="40%" align="right"></td>
       <td width="30"><span class="text-muted">.xls, .xslx</span></td>
       <td width="30%" align="left"></td>
      </tr>
     </table>
    </div>
   </form>   
   <br />
   <div class="panel panel-default">
    <div class="panel-heading">
     <h3 class="panel-title">Customer Data</h3>
    </div>
    <div class="panel-body">
     <div class="table-responsive">
      <table class="table table-bordered table-striped">
       <tr>
        <th>Name</th>
        <th>Gender</th>        
        <th>City</th>       
        <th>Country</th>
       </tr>
       @foreach($customerData as $customer)
       <tr>
        <td>{{ $customer->name }}</td>
        <td>{{ $customer->gender }}</td>        
        <td>{{ $customer->city }}</td>       
        <td>{{ $customer->country }}</td>
       </tr>
       @endforeach
      </table>
     </div>
    </div>
   </div>
  </div>
 </body>
</html>

Step7: Set the Controller Route

We will also need to set the route of controller method in routes/web.php file.

<?php
.................
Route::get('/import_excel', 'ImportExcel@index');
Route::post('/import_excel/import', 'ImportExcel@import');

Step8: Run Laravel Application

Finally, we will run the following command to run our Laravel application.

php artisan serve

This command will run the Laravel application on following URL in the browser.


http://127.0.0.1:8000/import_excel

You may also like:

Leave a Reply

Your email address will not be published. Required fields are marked *