Import Excel File in Laravel

If you are using Laravel Framework for your web development, and if you are beginner in Laravel. Then this post will help you to learn something new in Laravel. Because in this post you can find how to import Excel spreadsheet data and insert into mysql database in Laravel. Here for importing Excel file data here we will use Laravel Maatwebsite package. By using this package we can easily import data from Excel sheet and insert into table in Laravel application.

In some of the businees houses in which there are many large number of data has been stored in Excel, and after storing that data, they want some application which helps them to store in web application database, that means import into web application database. Then at that time if your web application has been made in PHP Laravel framewor then this post will help you to make importing excel file data feature in Laravel application by using Maatwebsite package.

In this post we will see or learn how can we import any type of Excel speadsheet in .xls, .xlsx or CSV data imported into Mysql database in Laravel. For communicate excel file data in Laravel, here have use Maatwebsite Laravel Excel package, this package will help to communicate excel file data in Laravel application. Below you can find complete step by step process of how to use Laravel maatwebsite package for import Excel sheet data into database in Laravel.

See Also

• Ajax jQuery Load More Data in Laravel
• Laravel Date Range Search using Ajax jQuery
• Make and Download Zip File using Codeigniter
• Uploading Image in CKEDITOR with PHP
• Codeingniter Export Mysql Data to CSV File

Step 1 - Create Table

First, we have to create table in Mysql Database, so run following SQL script, it will make tbl_customer table in your database.

--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_customer`
--

CREATE TABLE `tbl_customer` (
  `CustomerID` int(11) NOT NULL,
  `CustomerName` varchar(250) NOT NULL,
  `Gender` varchar(30) NOT NULL,
  `Address` text NOT NULL,
  `City` varchar(250) NOT NULL,
  `PostalCode` varchar(30) NOT NULL,
  `Country` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Indexes for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
  ADD PRIMARY KEY (`CustomerID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
  MODIFY `CustomerID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=146;

Step 2 - Mysql Database connection in Laravel

After this you have to make database connection. For this first you have to open database.php file from config. And in this file you have to define your database configuration.

<?php

return [


    'default' => env('DB_CONNECTION', 'mysql'),

...........

        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'testing'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

.............

];

After this you have to open .env file, and in this file also you have to define Mysql database configuration also.

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=testing
DB_USERNAME=root
DB_PASSWORD=

Step 3 - Download Maatwebsite Package

If you want to import excel file data in Laravel, you have to first download Maatwebsite package, this package will communicate with Excel spreadsheet data. First for download package, you have to go to command prompt and write following command.

composer require maatwebsite/excel

This command will download this package in your Laravel working folder. After this we have to register this package in our Laravel application. For this we have to go to config/app.php file. And in this file you have to define providers and aliases.

<?php

return [

........

    'providers' => [

.......

        Maatwebsite\Excel\ExcelServiceProvider::class,

    ],

    'aliases' => [

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

    ],

];

This way we can register Maatwebsite package in Laravel application, now we can use this package for importing excel file data.

Step 4 - Controllers (ImportExcelController.php)

Now we have to make controller for handle http request for import data. In this controller we have use two use statement. First use DB is used for do mysql database operation, and second use Excel is for Maatwebsite package for import excel sheet data. In this controller, we have make two method.

index() - This is root method of this class, in this method it will fetch data from customer table and that data will be load in import_blade.php file in table format.

import() - This method has request for import excel file data. In this method first it has validate excel file format. If selected file other than excel sheet then it will return validation error. But suppose selected file is excel then it will proceed for import data. For import data here it has called Excel package class which has get data from excel file and convert into PHP array and then after insert into customer table. After successfully import of data it will return success message.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;
use Excel;

class ImportExcelController extends Controller
{
    function index()
    {
     $data = DB::table('tbl_customer')->orderBy('CustomerID', 'DESC')->get();
     return view('import_excel', compact('data'));
    }

    function import(Request $request)
    {
     $this->validate($request, [
      'select_file'  => 'required|mimes:xls,xlsx'
     ]);

     $path = $request->file('select_file')->getRealPath();

     $data = Excel::load($path)->get();

     if($data->count() > 0)
     {
      foreach($data->toArray() as $key => $value)
      {
       foreach($value as $row)
       {
        $insert_data[] = array(
         'CustomerName'  => $row['customer_name'],
         'Gender'   => $row['gender'],
         'Address'   => $row['address'],
         'City'    => $row['city'],
         'PostalCode'  => $row['postal_code'],
         'Country'   => $row['country']
        );
       }
      }

      if(!empty($insert_data))
      {
       DB::table('tbl_customer')->insert($insert_data);
      }
     }
     return back()->with('success', 'Excel Data Imported successfully.');
    }
}

Step 5 - View File (import_excel.blade.php)

This file has been load by index() method of ImportExcelController, On this file we have make form for select excel file from local computer for import data. Below form it will display tbl_customer table data. And above form we have define for display validation error message and success message.

<!DOCTYPE html>
<html>
 <head>
  <title>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>
  <br />
  
  <div class="container">
   <h3 align="center">Import Excel File in Laravel</h3>
    <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>Customer Name</th>
        <th>Gender</th>
        <th>Address</th>
        <th>City</th>
        <th>Postal Code</th>
        <th>Country</th>
       </tr>
       @foreach($data as $row)
       <tr>
        <td>{{ $row->CustomerName }}</td>
        <td>{{ $row->Gender }}</td>
        <td>{{ $row->Address }}</td>
        <td>{{ $row->City }}</td>
        <td>{{ $row->PostalCode }}</td>
        <td>{{ $row->Country }}</td>
       </tr>
       @endforeach
      </table>
     </div>
    </div>
   </div>
  </div>
 </body>
</html>

Step 6 - Set Route

After this we have to set the route of controller method. For this we have to open to routes/web.php file. In this file we can define route.

<?php

.......

Route::get('/import_excel', 'ImportExcelController@index');
Route::post('/import_excel/import', 'ImportExcelController@import');

Step 7 - Run Laravel Application

Lastly, we have to run Laravel application, for this we have to go to command prompt, and write following command.

php artisan serve

This command will Laravel application, it will return base url of Laravel application. Now for this application, we have to write following url in browser.

http://127.0.0.1:8000/import_excel

Above is the complete process of How to make import excel file data feature in Laravel using Maatwebsite Package.